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:
SUCCESSI 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/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-10So 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.
0 comments:
Post a Comment