In 11g, we introduced the new feature Snapshot Standby Database for Data Guard. In my current Data Guard course, one of the attendees asked, 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. I answered that this is mot likely possible, but I did not try yet. Now I did 🙂
DGMGRL> convert database physt to snapshot standby; Converting database "physt" to a Snapshot Standby database, please wait... Database "physt" converted successfully
There is my Snapshot Standby Database. You may reproduce the Data Guard Configuration in place here with the Whitepaper 11g Data Guard on the Command Line from my Downloads page.
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 (very similar as described in the Whitepaper for the physt Database). Then I start it into NOMOUNT:
uhesse $ sqlplus sys/oracle@clon 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/oracle@physt connected to target database: PRIMA (DBID=1970640575) RMAN> connect auxiliary sys/oracle@clon 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/oracle@physt 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/oracle@physt connected to target database: PRIMA (DBID=1970640575) RMAN> connect auxiliary sys/oracle@clon 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/oracle@clon as sysdba
Connected.
SQL> select name,dbid,database_role from v$database;
NAME DBID DATABASE_ROLE
--------- ---------- ----------------
CLON 2202079427 PRIMARY
SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> select name,dbid,database_role from v$database;
NAME DBID DATABASE_ROLE
--------- ---------- ----------------
PRIMA 1970640575 PHYSICAL STANDBY
SQL> connect sys/oracle@prima 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.
#1 von jony am Juni 2, 2011 - 04:28
Hi
I have tried just like you tried to clone db from snapshot standby
I have succeeded upto half .
In my case it clone controlfile and while running memory script
It gives a messive error messeges in alert log saying
ARC3: Cannot archive online log based on backup controlfile
It finishes upto
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
and suddenly gives lot of messges which fill up fs where my alert log is.
Dont know how to solve.
Any clue.
#2 von Uwe Hesse am Juni 3, 2011 - 08:43
Check the LOG_ARCHIVE_DEST_1 parameter of the clone database. Also, check the *CONVERT* parameters of the clone database.
#3 von varsha am April 30, 2013 - 00:03
Thanks a ton. It helped me a lot.
#4 von Uwe Hesse am April 30, 2013 - 09:27
Thank YOU, varsha, for the nice feedback 🙂
#5 von Mary am Dezember 26, 2019 - 21:45
Hi there, not sure if you’re still monitoring this old thread, but i hope so. I’ve been using this method to refresh test/dev databases for quite awhile, in databases with version 12.1.0.2. We recently upgraded our databases to 19.5 and i can no longer get this to work. Once the duplicate database command is kicked off and it tries to start the first piece of creating the control file, the error „RMAN-05513: cannot duplicate, control file is not current or standby“ is thrown and the duplication aborts. I’ve done a lot of searching and have been unable to find any answers other than suggestions to restart the database. Using sql to check the status of the controlfile also returns „current“, so I’m thinking the error is misleading me. I’m wondering if something changed from 12.1 to 12.2 or if this is some sort of but?
#6 von BartOwl am Oktober 29, 2020 - 15:17
I don’t know whether you Mary managed to solve the problem with RMAN-05513, but if not, there is a bug and corresponding patch here: https://support.oracle.com/epmos/faces/DocContentDisplay?id=2713608.1
#7 von Mary Wagner am Dezember 6, 2020 - 18:20
Thanks so much BartOwl! I had given up on being able to use this method again, I ended up changing my refresh from standby script to turn off redo apply before and turn it back on after i executed the duplicate command, but this did not have the affect i intended, as it applyed all of the redo logs to the target database that had been transported to the standby during the recover part of the duplication. The snapshot method was perfect when it worked as no activity from the primary was applied during the duplication. Thanks again!
#8 von Alok am Januar 27, 2021 - 16:53
Nice method, easier, if standby is put in RO mode and normal syntax of duplicate from active also would work.