In 11g, Oracle introduced the new feature Snapshot Standby Database for Data Guard. whether it is possible to create a Database Clone out of such a Snapshot Standby Database. Maybe you want to preserve the changes that have been done during the time, your Physical Standby Database was turned into a READ-WRITE opened (Test-)Database that this is most likely possible 
DGMGRL> convert database physt to snapshot standby; Converting database "physt" to a Snapshot Standby database, please wait... Database "physt" converted successfully
DGMGRL> show configuration Configuration - myconf Protection Mode: MaxPerformance Databases: prima - Primary database physt - Snapshot standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
I continue to prepare a Clone Database by creating an spfile and a password file for it. Then I start it into NOMOUNT:
uhesse $ sqlplus sys/[email protected] as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 21 17:00:26 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 313860096 bytes Fixed Size 1336232 bytes Variable Size 209718360 bytes Database Buffers 96468992 bytes Redo Buffers 6336512 bytes
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
The version in use is 11gR2, but it should work with 11gR1 the same way. Now RMAN is used to create the Clone from the Snapshot Standby:
RMAN> connect target sys/[email protected] connected to target database: PRIMA (DBID=1970640575) RMAN> connect auxiliary sys/[email protected] connected to auxiliary database: CLON (not mounted) RMAN> duplicate target database to clon from active database; Starting Duplicate Db at 21-DEC-10 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=96 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 12/21/2010 17:03:19 RMAN-05541: no archived logs found in target database RMAN> exit
Oops! I need Archivelogs on the Snapshot Standby. Note that they are in the same directory but with a different Resetlogs Identifier as the Archivelogs created when the Database was a Physical Standby Database.
uhesse $ sqlplus sys/[email protected] as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 21 17:03:49 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter system switch logfile; System altered. SQL> select name from v$archived_log; NAME -------------------------------------------------------------------------------- /home/oracle/physt/archive/1_12_738347903.dbf /home/oracle/physt/archive/1_8_738347903.dbf /home/oracle/physt/archive/1_7_738347903.dbf /home/oracle/physt/archive/1_9_738347903.dbf /home/oracle/physt/archive/1_11_738347903.dbf /home/oracle/physt/archive/1_10_738347903.dbf /home/oracle/physt/archive/1_13_738347903.dbf /home/oracle/physt/archive/1_14_738347903.dbf /home/oracle/physt/archive/1_15_738347903.dbf /home/oracle/physt/archive/1_1_738349098.dbf 10 rows selected.
Again we go with the duplicate:
RMAN> connect target sys/[email protected] connected to target database: PRIMA (DBID=1970640575) RMAN> connect auxiliary sys/[email protected] connected to auxiliary database: CLON (not mounted) RMAN> duplicate target database to clon from active database; Starting Duplicate Db at 21-DEC-10 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=95 device type=DISK contents of Memory Script: { sql clone "alter system set db_name = ''PRIMA'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''CLON'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount backup as copy current controlfile auxiliary format '/home/oracle/clon/control01.ctl'; alter clone database mount; } executing Memory Script sql statement: alter system set db_name = ''PRIMA'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''CLON'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 313860096 bytes Fixed Size 1336232 bytes Variable Size 209718360 bytes Database Buffers 96468992 bytes Redo Buffers 6336512 bytes Starting backup at 21-DEC-10 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 device type=DISK channel ORA_DISK_1: starting datafile copy copying current control file output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_physt.f tag=TAG20101221T172429 RECID=6 STAMP=738350670 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 21-DEC-10 database mounted contents of Memory Script: { set newname for datafile 1 to "/home/oracle/clon/system01.dbf"; set newname for datafile 2 to "/home/oracle/clon/sysaux01.dbf"; set newname for datafile 3 to "/home/oracle/clon/undotbs01.dbf"; set newname for datafile 4 to "/home/oracle/clon/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/home/oracle/clon/system01.dbf" datafile 2 auxiliary format "/home/oracle/clon/sysaux01.dbf" datafile 3 auxiliary format "/home/oracle/clon/undotbs01.dbf" datafile 4 auxiliary format "/home/oracle/clon/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 21-DEC-10 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/home/oracle/physt/system01.dbf output file name=/home/oracle/clon/system01.dbf tag=TAG20101221T172436 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=/home/oracle/physt/sysaux01.dbf output file name=/home/oracle/clon/sysaux01.dbf tag=TAG20101221T172436 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/home/oracle/physt/undotbs01.dbf output file name=/home/oracle/clon/undotbs01.dbf tag=TAG20101221T172436 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/home/oracle/physt/users01.dbf output file name=/home/oracle/clon/users01.dbf tag=TAG20101221T172436 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 21-DEC-10 sql statement: alter system archive log current contents of Memory Script: { backup as copy reuse archivelog like "/home/oracle/physt/archive/1_3_738349098.dbf" auxiliary format "/home/oracle/clon/archive/1_3_738349098.dbf" ; catalog clone archivelog "/home/oracle/clon/archive/1_3_738349098.dbf"; switch clone datafile all; } executing Memory Script Starting backup at 21-DEC-10 using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log copy input archived log thread=1 sequence=3 RECID=12 STAMP=738350751 output file name=/home/oracle/clon/archive/1_3_738349098.dbf RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:15 Finished backup at 21-DEC-10 cataloged archived log archived log file name=/home/oracle/clon/archive/1_3_738349098.dbf RECID=12 STAMP=738350766 datafile 1 switched to datafile copy input datafile copy RECID=6 STAMP=738350766 file name=/home/oracle/clon/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=7 STAMP=738350767 file name=/home/oracle/clon/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=8 STAMP=738350767 file name=/home/oracle/clon/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=9 STAMP=738350767 file name=/home/oracle/clon/users01.dbf contents of Memory Script: { set until scn 226204; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 21-DEC-10 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=95 device type=DISK starting media recovery archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/clon/archive/1_3_738349098.dbf archived log file name=/home/oracle/clon/archive/1_3_738349098.dbf thread=1 sequence=3 media recovery complete, elapsed time: 00:00:00 Finished recover at 21-DEC-10 contents of Memory Script: { shutdown clone immediate; startup clone nomount; sql clone "alter system set db_name = ''CLON'' 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 313860096 bytes Fixed Size 1336232 bytes Variable Size 209718360 bytes Database Buffers 96468992 bytes Redo Buffers 6336512 bytes sql statement: alter system set db_name = ''CLON'' 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 313860096 bytes Fixed Size 1336232 bytes Variable Size 209718360 bytes Database Buffers 96468992 bytes Redo Buffers 6336512 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLON" RESETLOGS ARCHIVELOG MAXLOGFILES 26 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '/home/oracle/clon/log_g1m1.rdo' ) SIZE 100 M REUSE, GROUP 2 ( '/home/oracle/clon/log_g2m1.rdo' ) SIZE 100 M REUSE DATAFILE '/home/oracle/clon/system01.dbf' CHARACTER SET WE8ISO8859P1 contents of Memory Script: { set newname for tempfile 1 to "/home/oracle/clon/temp01.dbt"; switch clone tempfile all; catalog clone datafilecopy "/home/oracle/clon/sysaux01.dbf", "/home/oracle/clon/undotbs01.dbf", "/home/oracle/clon/users01.dbf"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /home/oracle/clon/temp01.dbt in control file cataloged datafile copy datafile copy file name=/home/oracle/clon/sysaux01.dbf RECID=1 STAMP=738350787 cataloged datafile copy datafile copy file name=/home/oracle/clon/undotbs01.dbf RECID=2 STAMP=738350787 cataloged datafile copy datafile copy file name=/home/oracle/clon/users01.dbf RECID=3 STAMP=738350787 datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=738350787 file name=/home/oracle/clon/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=738350787 file name=/home/oracle/clon/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=738350787 file name=/home/oracle/clon/users01.dbf contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 21-DEC-10
So this was pretty much the same as with an ordinary Clone from a Production Database – as expected. Now we turn the Snapshot Standby back into Physical Standby, thereby losing all the changes that might be done there by using Flashback Database internally. But the Clone Database will remain, now being an independent Database with another Name and DBID than the Primary resp. Snapshot Standby.
DGMGRL> convert database physt to physical standby; Converting database "physt" to a Physical Standby database, please wait... Operation requires shutdown of instance "physt" on database "physt" Shutting down instance "physt"... Database closed. Database dismounted. ORACLE instance shut down. Operation requires startup of instance "physt" on database "physt" Starting instance "physt"... ORACLE instance started. Database mounted. Continuing to convert database "physt" ... Operation requires shutdown of instance "physt" on database "physt" Shutting down instance "physt"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "physt" on database "physt" Starting instance "physt"... ORACLE instance started. Database mounted. Database "physt" converted successfully
DGMGRL> show configuration Configuration - myconf Protection Mode: MaxPerformance Databases: prima - Primary database physt - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
SQL> connect sys/[email protected] as sysdba Connected.
SQL> select name,dbid,database_role from v$database; NAME DBID DATABASE_ROLE --------- ---------- ---------------- CLON 2202079427 PRIMARY SQL> connect sys/[email protected] as sysdba Connected.
SQL> select name,dbid,database_role from v$database; NAME DBID DATABASE_ROLE --------- ---------- ---------------- PRIMA 1970640575 PHYSICAL STANDBY SQL> connect sys/[email protected] as sysdba Connected.
SQL> select name,dbid,database_role from v$database; NAME DBID DATABASE_ROLE --------- ---------- ---------------- PRIMA 1970640575 PRIMARY
Conclusion: With 11g, we can very comfortably transform our Physical Standby into a fully changeable Snapshot Standby, clone that modified Database into an autonomous Database to keep the changes and afterwards transform the Snapshot Standby back into Physical Standby. During the whole action, Redo Protocol from the Primary Database can still be received – but not applied – on the Standby Site.
