Active Database Duplication in 11g

RMAN has the ability to duplicate, or clone, a database from a backup or from an active database. It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure.

Active database duplication requires that the auxiliary instance have a password file.  This is because the source database will be connecting directly to the auxiliary database, requiring a password file.  One other key is that you want to use the same SYSDBA password as the source database . Also the destination server requires static listener configuration in a "listener.ora" file. 

Active Database Duplication

Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication, with a few exceptions.First, and most obviously, you don't need a backup of the source system, but it does have to be in ARCHIVELOG mode.

Here i done my experiments in the same machine .

C:\Users\Admin>rman target sys/sys@db11g catalog rman/rman@catdb auxiliary sys/sys@clonedb

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 17 11:25:30 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB11G (DBID=305973962)

connected to recovery catalog database

connected to auxiliary database: CLONE11G (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO CLONE11G from active database;

Starting Duplicate Db at 17-JAN-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=189 device type=DISK

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''DB11G'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''CLONE11G'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   shutdown clone immediate;

   startup clone force nomount

   backup as copy current controlfile auxiliary format  'D:\DATA\CLONE11G\CONTROL01.CTL';

   restore clone controlfile to  'D:\DATA\CLONE11G\CONTROL02.CTL' from

 'D:\DATA\CLONE11G\CONTROL01.CTL';

   alter clone database mount;

}

executing Memory Script

sql statement: alter system set  db_name =  ''DB11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CLONE11G'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2177456 bytes

Variable Size                348128848 bytes

Database Buffers             176160768 bytes

Redo Buffers                   7995392 bytes

Starting backup at 17-JAN-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=160 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=C:\APP\ADMIN\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFDB11G.ORA tag=TAG20140117T112848 RECID=1 STAMP=837084529

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 17-JAN-14

Starting restore at 17-JAN-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=156 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 17-JAN-14

database mounted

contents of Memory Script:

{

   set newname for datafile  1 to

 "D:\DATA\CLONE11G\SYSTEM01.DBF";

   set newname for datafile  2 to

 "D:\DATA\CLONE11G\SYSAUX01.DBF";

   set newname for datafile  3 to

 "D:\DATA\CLONE11G\UNDOTBS01.DBF";

   set newname for datafile  4 to

 "D:\DATA\CLONE11G\USERS01.DBF";

   backup as copy reuse

   datafile  1 auxiliary format

 "D:\DATA\CLONE11G\SYSTEM01.DBF"   datafile

 2 auxiliary format

 "D:\DATA\CLONE11G\SYSAUX01.DBF"   datafile

 3 auxiliary format

 "D:\DATA\CLONE11G\UNDOTBS01.DBF"   datafile

 4 auxiliary format

 "D:\DATA\CLONE11G\USERS01.DBF"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 17-JAN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=D:\DATA\DB11G\SYSTEM01.DBF

output file name=D:\DATA\CLONE11G\SYSTEM01.DBF tag=TAG20140117T112857

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=D:\DATA\DB11G\SYSAUX01.DBF

output file name=D:\DATA\CLONE11G\SYSAUX01.DBF tag=TAG20140117T112857

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=D:\DATA\DB11G\UNDOTBS01.DBF

output file name=D:\DATA\CLONE11G\UNDOTBS01.DBF tag=TAG20140117T112857

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=D:\DATA\DB11G\USERS01.DBF

output file name=D:\DATA\CLONE11G\USERS01.DBF tag=TAG20140117T112857

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 17-JAN-14

sql statement: alter system archive log current

contents of Memory Script:

{

   backup as copy reuse

   archivelog like  "D:\ARCHIVE2\ARC0000000022_0836304397.0001" auxiliary format

 "D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001"   ;

   catalog clone archivelog  "D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001";

   switch clone datafile all;

}

executing Memory Script

Starting backup at 17-JAN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=22 RECID=21 STAMP=837084582

output file name=D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001 RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 17-JAN-14

cataloged archived log

archived log file name=D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001 RECID=21 STAMP=837084585

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=837084585 file name=D:\DATA\CLONE11G\SYSTEM01.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=837084585 file name=D:\DATA\CLONE11G\SYSAUX01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=837084585 file name=D:\DATA\CLONE11G\UNDOTBS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=837084585 file name=D:\DATA\CLONE11G\USERS01.DBF

contents of Memory Script:

{

   set until scn  1453391;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 17-JAN-14

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001

archived log file name=D:\CLONE_ARCHIVE\ARC0000000022_0836304397.0001 thread=1 sequence=22

media recovery complete, elapsed time: 00:00:00

Finished recover at 17-JAN-14

contents of Memory Script:

{

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  db_name =

 ''CLONE11G'' comment=

 ''Reset to original value by RMAN'' scope=spfile";

   sql clone "alter system reset  db_unique_name scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2177456 bytes

Variable Size                348128848 bytes

Database Buffers             176160768 bytes

Redo Buffers                   7995392 bytes

sql statement: alter system set  db_name =  ''CLONE11G'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2177456 bytes

Variable Size                348128848 bytes

Database Buffers             176160768 bytes

Redo Buffers                   7995392 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE11G" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( 'D:\DATA\CLONE11G\REDO01.LOG' ) SIZE 50 M  REUSE,

  GROUP  2 ( 'D:\DATA\CLONE11G\REDO02.LOG' ) SIZE 50 M  REUSE,

  GROUP  3 ( 'D:\DATA\CLONE11G\REDO03.LOG' ) SIZE 50 M  REUSE

 DATAFILE

  'D:\DATA\CLONE11G\SYSTEM01.DBF'

 CHARACTER SET WE8MSWIN1252

contents of Memory Script:

{

   set newname for tempfile  1 to

 "D:\DATA\CLONE11G\TEMP01.DBF";

   switch clone tempfile all;

   catalog clone datafilecopy  "D:\DATA\CLONE11G\SYSAUX01.DBF",

 "D:\DATA\CLONE11G\UNDOTBS01.DBF",

 "D:\DATA\CLONE11G\USERS01.DBF";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\DATA\CLONE11G\TEMP01.DBF in control file

cataloged datafile copy

datafile copy file name=D:\DATA\CLONE11G\SYSAUX01.DBF RECID=1 STAMP=837084606

cataloged datafile copy

datafile copy file name=D:\DATA\CLONE11G\UNDOTBS01.DBF RECID=2 STAMP=837084606

cataloged datafile copy

datafile copy file name=D:\DATA\CLONE11G\USERS01.DBF RECID=3 STAMP=837084606

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=837084606 file name=D:\DATA\CLONE11G\SYSAUX01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=837084606 file name=D:\DATA\CLONE11G\UNDOTBS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=837084606 file name=D:\DATA\CLONE11G\USERS01.DBF

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 17-JAN-14

RMAN> exit

Note:- 

• Auxiliary instance must be available through Oracle Net if you are duplicating from an ACTIVE database.
• Rman automatically creates  a default server parameter file for the auxiliary instance if the  auxiliary instance was not started with a server parameter file.
• 
  

Retention Policy in rman REDUNDANCY or RECOVERY WINDOW , which one is effective ?

What is  Retention Policy in RMAN ?
Setting the RETENTION POLICY in RMAN keep track about the backup files and tells the report whether they are obsoleted and whether it is needed to backup the database or datafile.
Notes:- 

• If you have flash recovery configured then the database automatically deletes unnecessary files from the flash recovery area based on its internal disk quota rules. The disk quota rules are distinct from the backup retention policy rules, but the database will never delete files in violation of the retention policy to satisfy the disk quota.If you don't have flash recovery configured then the database does not delete any file even they are obsolete.

How to set retention policy in RMAN ? 

The configuration parameter RETENTION POLICY can be set by CONFIGURE RETENTION POLICY  TO .... 

What is the use of crosschek and obsolete command ?

A backup becomes obsolete based on retention policy, that it is not needed for recovery.A backup becomes expired only when RMAN perform CROSSCHECK and can't find the file. (A most common is file is deleted by OS). Obsolete means "not needed," whereas expired means "not found."

Issue the following RMAN command to crosscheck backup of an Oracle Database,

RMAN> crosscheck backup of database;

using channel ORA_DISK_1

crosschecked backup piece: found to be ‘EXPIRED’

backup piece handle=D:\STAGE\RACDEVD1_503\DATAFILE_BACKUP recid=79 stamp=475839783

crosschecked backup piece: found to be ‘EXPIRED’

backup piece handle=D:\STAGE\RACDEVD1_504\DATAFILE_BACKUP recid=137 stamp=475839784

crosschecked backup piece: found to be ‘AVAILABLE’

backup piece handle=D:\DGSTAGE\DPLCU5L5_1_1 recid=132 stamp=475839755

crosschecked backup piece: found to be ‘AVAILABLE’

backup piece handle=D:\DGSTAGE\DQLCU62F_1_1 recid=133 stamp=475839756Crosschecked 4 objects

As we see the RMAN crosscheck command compares the RMAN  catalog entries with the actual OS files and reports to locate "expired" or "obsolete" RMAN catalog entries.

Once a RMAN crosscheck identified expired, deleted  entries you can run an RMAN delete command to remove these entries to synchronize the RMAN catalog / controlfile  with the real database files:
RMAN> delete expired backup;
Identify which backups are obsolete and no longer needed for recovery

RMAN> REPORT OBSOLETE;
Delete obsolete backup information from RMAN repository.
RMAN> DELETE OBSOLETE;

How can we set retention policy in RMAN ?

There is two mutually exclusive options for setting a retention policy , redundancy and recovery window.

To set Retention policy to  redundancy 2 copies,

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

set Retention Policy to  Recovery Window of 7 days,

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Whenever retention policy is set to redundancy of 2 copies the rman at least retains latest 2 copies of each datafile. If I took 3rd backup of datafile 3 then 1st backup of datafile 3 become obsolete. 

If you specify a recovery window of 7 days. That means rman will not obsolete any backup needed for recovery to any point in the last seven days. Check. But what's the subtle detail?

Suppose you take a full backup on 1 Sep, then start taking incremental backups going forward. Now we're down to 10 Sep which puts that full backup 3 days earlier than the recovery window. However, it is still the most recent full backup and so is needed to recover into any time during the 7 day recovery window. Therefore, it will NOT be marked obsolete.

Think about what this does in the textbook backup routine of a full backup every 7 days, with incremental backups the other 6 days. We take a full backup on day 1. We take incremental backups on days 2 - 7. We take a full backup again on Day 8, and incrementals on days 9 - 14. 

Day  =  1  2  3  4  5  6  7  8  9 10 11 12 13 14

Bkup =  F  I   I   I  I   I   I  F   I   I   I   I   I    I

On day 14, our recovery window reaches back to day 7, and the only way to recover to day 7 is to use the full backup from day 1 and the incrementals from 2 through 7. So even though we have a 7 day recovery window, we actually have 14 days worth of backups that are yet to go obsolete. On day 15, we can obsolete and drop everything prior to the full backup on Day 8. Thus the Recovery Window retention policy doesn't just keep the last N days of the backup. It calculates the point-of-recovery (POR) value, accounting the current date and the number of the days mentioned in the retention policy. 

The default retention policy is REDUNDANCY = 1

you can disable the retention policy by setting,

RMAN>CONFIGURE RETENTION POLICY TO NONE;

If the retention policy is configured to NONE, then REPORT OBSOLETE and DELETE OBSOLETE do not consider any backups to be obsolete

Conclusion :- 
When having a rman retention policy based on REDUNDANCY is a bad idea ,if you intend to incomplete recover your database to a previous time in the past it's really a good idea to switch to a retention policy based on a "RECOVERY WINDOW" instead.