Performing a database clone using a Data Guard physical standby database

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_.arc                                        100%  108MB  27.0MB/s   00:04

o1_mf_1_1314_7ryx9660_.arc                                        100%  387KB 387.0KB/s   00:00

o1_mf_1_1315_7ryy5322_.arc                                        100% 9807KB   9.6MB/s   00:00

o1_mf_1_1316_7ryyjztx_.arc                                        100%  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