Performing a database clone using a Data Guard physical standby database
A common DBA task is to perform regular clones and database refreshes of the production database for the purpose of setting up training or test or development environments.
If we are having a physical standby Data Guard environment, then we can easily offload the potentially I/O and CPU intensive backup process required for creating these clone or duplicate databases to the standby site.
Here are a few examples of using the physical Standby database in a Data Guard environment to create a clone of the primary production database.
In the first example we use RMAN to perform the backup and restore and in the second example we are using OS commands to just copy files ONLINE from Standby host to the target host.
Note that in 11g, we can take the backup of the control file from the Standby database. In 10g, we have to take the backup of the controlfile from the primary database.
Metalink states that:
From 11G onwards controlfile backups are interchangible. This is applicable for Physical standby only.
For example the controlfile backup taken on standby(the control file type is STANDBY) can be restored directly on primary and the controlfile type automatically converted as CURRENT.
Method 1) Using RMAN to take a backup from Standby Database (same DB_NAME)
On Standby
SQL> recover managed standby database cancel;
Media recovery complete.
[oracle@kens-orasql-001-dev oracle]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 18 11:15:00 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HDESK11G (DBID=1866661199, not open)
RMAN> backup as compressed backupset database plus archivelog format '/u01/app/oracle/bkp_db.%U';
Starting backup at 18-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1300 RECID=376 STAMP=780913484
input archived log thread=1 sequence=1301 RECID=377 STAMP=780913486
input archived log thread=1 sequence=1302 RECID=379 STAMP=780913792
input archived log thread=1 sequence=1303 RECID=378 STAMP=780913792
input archived log thread=1 sequence=1304 RECID=380 STAMP=780913805
input archived log thread=1 sequence=1305 RECID=381 STAMP=780913812
input archived log thread=1 sequence=1306 RECID=382 STAMP=780913822
input archived log thread=1 sequence=1307 RECID=383 STAMP=780914242
input archived log thread=1 sequence=1308 RECID=384 STAMP=780914245
input archived log thread=1 sequence=1309 RECID=386 STAMP=780914858
input archived log thread=1 sequence=1310 RECID=385 STAMP=780914858
input archived log thread=1 sequence=1311 RECID=387 STAMP=780914870
input archived log thread=1 sequence=1312 RECID=388 STAMP=780914872
channel ORA_DISK_1: starting piece 1 at 18-APR-12
channel ORA_DISK_1: finished piece 1 at 18-APR-12
piece handle=/u01/app/oracle/bkp_db.0pn8nrhf_1_1 tag=TAG20120418T105335 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 18-APR-12
Starting backup at 18-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/oradata/HDESK11G/hdesk_data01.dbf
input datafile file number=00001 name=/u01/oradata/HDESK11G/system01.dbf
input datafile file number=00002 name=/u01/oradata/HDESK11G/sysaux01.dbf
input datafile file number=00003 name=/u01/oradata/HDESK11G/undotbs101.dbf
input datafile file number=00007 name=/u01/oradata/HDESK11G/common_data01.dbf
input datafile file number=00004 name=/u01/oradata/HDESK11G/users01.dbf
input datafile file number=00008 name=/u01/oradata/HDESK11G/testme01.dbf
input datafile file number=00005 name=/u01/oradata/HDESK11G/example01.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-12
channel ORA_DISK_1: finished piece 1 at 18-APR-12
piece handle=/u01/oradata/HDESK11G/FRA/HDESK11G2/backupset/2012_04_18/o1_mf_nnndf_TAG20120418T105342_7rwc5pf4_.bkp tag=TAG20120418T105342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:15
Finished backup at 18-APR-12
Starting backup at 18-APR-12
using channel ORA_DISK_1
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 18-APR-12
Starting Control File and SPFILE Autobackup at 18-APR-12
piece handle=/u01/oradata/HDESK11G/FRA/HDESK11G2/autobackup/2012_04_18/o1_mf_s_780922291_7rwchkow_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-12
Now copy the RMAN backup pieces from the Standby site to the Target site. Note that if the directory structure where we have taken the backup on standby differs on the target server where we are performing the restore, then we have to use the CATALOG command to register those backups in the controlfile.
On Target
RMAN> restore controlfile from '/u01/app/oracle/ o1_mf_s_780922291_7rwchkow_.bkp’;
Starting restore at 18-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/HDESK11G/control01.ctl
output file name=/u01/app/oracle/oradata/HDESK11G/control02.ctl
Finished restore at 18-APR-12
RMAN> alter database mount;
RMAN> catalog backuppiece '/u01/app/oracle/o1_mf_nnndf_TAG20120418T105342_7rwc5pf4_.bkp';
using target database control file instead of recovery catalog
cataloged backup piece
backup piece handle=/u01/app/oracle/o1_mf_nnndf_TAG20120418T105342_7rwc5pf4_.bkp RECID=9 STAMP=780923733
RMAN> restore database;
Starting restore at 18-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/HDESK11G/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/HDESK11G/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/HDESK11G/undotbs101.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/HDESK11G/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/HDESK11G/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/HDESK11G/hdesk_data01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/HDESK11G/common_data01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/HDESK11G/testme01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oradata/HDESK11G/FRA/HDESK11G2/backupset/2012_04_18/o1_mf_nnndf_TAG20120418T105342_7rwc5pf4_.bkp
channel ORA_DISK_1: errors found reading piece handle=/u01/oradata/HDESK11G/FRA/HDESK11G2/backupset/2012_04_18/o1_mf_nnndf_TAG20120418T105342_7rwc5pf4_.bkp
channel ORA_DISK_1: failover to piece handle=/u01/app/oracle/o1_mf_nnndf_TAG20120418T105342_7rwc5pf4_.bkp tag=TAG20120418T105342
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:35
Finished restore at 18-APR-12
RMAN> recover database noredo;
Starting recover at 18-APR-12
using channel ORA_DISK_1
Finished recover at 18-APR-12
RMAN> alter database open resetlogs;
FAILED HERE !!
Alert log showed:
LGWR: Primary database is in MAXIMUM AVAILABILITY mode
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LGWR: Minimum of 1 LGWR standby database required
Errors in file /u01/app/oracle/diag/rdbms/hdesk11g/HDESK11G/trace/HDESK11G_lgwr_7926.trc:
ORA-16072: a minimum of one standby database destination is required
Errors in file /u01/app/oracle/diag/rdbms/hdesk11g/HDESK11G/trace/HDESK11G_lgwr_7926.trc:
ORA-16072: a minimum of one standby database destination is required
LGWR (ospid: 7926): terminating the instance due to error 16072
Wed Apr 18 11:40:30 2012
ARC1 started with pid=19, OS id=8008
Instance terminated by LGWR, pid = 7926
This error was because in the original environment Standby Database was running in MAXIMUM AVAILABILITY mode and it needs a valid destination for LOG_ARCHIVE_DEST_2. Changed it to maximum performance even though there is no Data Guard involved in the restored database. But control file which was restored belonged to a Data Guard Primary database (even though we took it from Standby) – this is the difference in 11g.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 553649000 bytes
Database Buffers 1577058304 bytes
Redo Buffers 4964352 bytes
Database mounted.
SQL> alter database set standby database to maximize performance;
Database altered.
SQL> alter database open;
Database altered.
SQL> select name from v$database;
NAME
------
HDESK11G
Method 2) Using Online ‘Hot’ Operating System level copy of database files of the standby database (Changing DB_NAME)
On Primary
SQL> alter database backup controlfile to trace;
Copy trace file to target site and edit as required
SQL> alter database begin backup;
Database altered.
Create a test table as SYSTEM (just to test the process)
SQL> conn system/
Connected.
SQL> create table test_me
2 as select * from all_objects;
Table created.
SQL> alter system switch logfile; > doing this because we are using MAXIMUM PERFORMANCE mode
System altered.
ON STANDBY
scp all the data files to the target server
[oracle@kens-orasql-002 HDESK11G]$ scp -rp *.dbf oracle@kens-orasql-001-dev:/u01/app/oracle/oradata/CLONEDB
ON PRIMARY
SQL> alter database end backup;
Database altered.
SQL> alter system switch logfile;
System altered.
ON STANDBY
scp all the archivelog files generated while copy in progress. Ensure last archive log file is present after the alter database end backup command is issues on primary
[oracle@kens-orasql-002 2012_04_19]$ scp -rp *.arch oracle@kens-orasql-001-dev:/u01/app/oracle/oradata/CLONEDB/arch
oracle@kens-orasql-001-dev's password:
o1_mf_1_1313_7rywxhl0_.arc100% 108MB 27.0MB/s 00:04
o1_mf_1_1314_7ryx9660_.arc100% 387KB 387.0KB/s 00:00
o1_mf_1_1315_7ryy5322_.arc 100% 9807KB 9.6MB/s 00:00
o1_mf_1_1316_7ryyjztx_.arc100% 359KB 358.5KB/s 00:00
ON TARGET
Create the controlfile
Startup nomount – note init.ora file is having new database name
[oracle@kens-orasql-001-dev dbs]$ export ORACLE_SID=CLONEDB
[oracle@kens-orasql-001-dev dbs]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 19 10:48:47 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 553649000 bytes
Database Buffers 1577058304 bytes
Redo Buffers 4964352 bytes
SQL> @crectl
Control file created.
Apply the archive log files and recover the database
SQL> !ls -lrt /u01/app/oracle/oradata/CLONEDB/arch/
total 843700
-rw-r----- 1 oracle oinstall 113127424 Apr 19 10:08 o1_mf_1_1313_7rywxhl0_.arc
-rw-r----- 1 oracle oinstall 396288 Apr 19 10:15 o1_mf_1_1314_7ryx9660_.arc
-rw-r----- 1 oracle oinstall 10042368 Apr 19 10:29 o1_mf_1_1315_7ryy5322_.arc
-rw-r----- 1 oracle oinstall 367104 Apr 19 10:36 o1_mf_1_1316_7ryyjztx_.arc
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 23286334 generated at 04/18/2012 10:51:31 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/CLONEDB/arch/1_1313_764155537.arch
ORA-00280: change 23286334 for thread 1 is in sequence #1313
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/CLONEDB/arch/o1_mf_1_1313_7rywxhl0_.arc
ORA-00279: change 23395781 generated at 04/19/2012 10:08:47 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/CLONEDB/arch/1_1314_764155537.arch
ORA-00280: change 23395781 for thread 1 is in sequence #1314
ORA-00278: log file
'/u01/app/oracle/oradata/CLONEDB/arch/o1_mf_1_1313_7rywxhl0_.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/CLONEDB/arch/o1_mf_1_1314_7ryx9660_.arc
ORA-00279: change 23396254 generated at 04/19/2012 10:15:02 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/CLONEDB/arch/1_1315_764155537.arch
ORA-00280: change 23396254 for thread 1 is in sequence #1315
ORA-00278: log file
'/u01/app/oracle/oradata/CLONEDB/arch/o1_mf_1_1314_7ryx9660_.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/CLONEDB/arch/o1_mf_1_1315_7ryy5322_.arc
ORA-00279: change 23397491 generated at 04/19/2012 10:29:55 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/CLONEDB/arch/1_1316_764155537.arch
ORA-00280: change 23397491 for thread 1 is in sequence #1316
ORA-00278: log file
'/u01/app/oracle/oradata/CLONEDB/arch/o1_mf_1_1315_7ryy5322_.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/CLONEDB/arch/o1_mf_1_1316_7ryyjztx_.arc
ORA-00279: change 23397950 generated at 04/19/2012 10:36:15 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/CLONEDB/arch/1_1317_764155537.arch
ORA-00280: change 23397950 for thread 1 is in sequence #1317
ORA-00278: log file
'/u01/app/oracle/oradata/CLONEDB/arch/o1_mf_1_1316_7ryyjztx_.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$database;
NAME
------
CLONEDB
SQL> select name from v$datafile;
NAME
------
/u01/app/oracle/oradata/CLONEDB/system01.dbf
/u01/app/oracle/oradata/CLONEDB/sysaux01.dbf
/u01/app/oracle/oradata/CLONEDB/undotbs101.dbf
/u01/app/oracle/oradata/CLONEDB/users01.dbf
/u01/app/oracle/oradata/CLONEDB/example01.dbf
/u01/app/oracle/oradata/CLONEDB/hdesk_data01.dbf
/u01/app/oracle/oradata/CLONEDB/common_data01.dbf
/u01/app/oracle/oradata/CLONEDB/testme01.dbf
8 rows selected.
SQL> select member from v$logfile;
MEMBER
------
/u01/app/oracle/oradata/CLONEDB/redo03.log
/u01/app/oracle/oradata/CLONEDB/redo02.log
/u01/app/oracle/oradata/CLONEDB/redo01.log
Check the table we created on primary is also present on clone database
SQL> conn system
Enter password:
Connected.
SQL> select count(*) from test_me;
COUNT(*)
------
74864