Recreate Physical Standby Database after Recovery of Primary Database
In Oracle 11g it is possible
to create a Physical Standby Database from the Primary Database using RMAN.
Furthermore, the same procedure can be adopted in an existing Data Guard environment
to recreate the Physical Standby Database following recovery of the Primary Database.
A number of reasons may
influence your decision to recreate the Standby Database, these include:
- Point in time Recovery (PITR) of Primary Database
- The standby (DR) site had to be rebuilt due to hardware failure
- The network link was down for a long period, resulting in a huge
backlog of archive logs waiting to be shipped and applied on the Standby Database.
All the above have one thing
in common; data synchronisation required between Primary and Standby. In terms
of restoration time, it may be advantageous to recreate the Standby Database
from the Primary. This is easily accomplished using RMAN’s Oracle 11g new
feature;
“duplicate target
database for standby from active database”.
For this exercise, the
assumptions are as follows:
- CRS, RDBMS & ASM Oracle Homes are intact on standby nodes
- Original password file exists on the standby nodes
[$ORACLE_HOME/dbs]
- Original TNS configuration files exist on Primary and Standby
nodes
- CRS nodeapps and ASM instances are online on both Standby nodes
- Working network exists between Primary and Standby nodes
- The Primary Database can be opened by at least 1 instance
- The Primary Database unique name is PRD
- The Standby Database unique name is PRDSBY
In this example, both Physical
and Standby databases are 2 node RAC using ASM shared storage.
Delete Standby Database Files From ASM (if existing)
1. Ensure the Standby Database is shutdown (if available). As
oracle user logon to Standby Database Node A and type following command:
ps –ef | grep pmon | grep –v grep
If text similar to the
following is returned by the above command, shutdown the database.
oracle
20844 1 0 Feb16
? 00:00:07 ora_pmon_PRDSBY1
To shutdown the Standby Database
issue the following command as oracle user:
srvctl stop database -d PRDSBY
2. Logon to Standby Database Node A as oracle user and set ASM
environment variables as follows:
. oraenv
ORACLE_SID = [PRDSBY1] ? +ASM1
3. Delete the database files from ASM +DATA diskgroup using
ASMCMD utility:
asmcmd rm ’+DATA/PRDSBY/CONTROLFILE/*’
asmcmd rm ’+DATA/PRDSBY/DATAFILE/*’
asmcmd rm ’+DATA/PRDSBY/ONLINELOG/*’
asmcmd rm ’+DATA/PRDSBY/TEMPFILE/*’
asmcmd rm ’+DATA/PRDSBY/PARAMETERFILE/*’
4. Delete the database files from ASM +FLASH diskgroup using
ASMCMD utility:
asmcmd rm ’+FLASH/PRDSBY/CONTROLFILE/*’
asmcmd rm ’+FLASH/PRDSBY/BACKUPSET/*’
asmcmd rm ’+FLASH/PRDSBY/ONLINELOG/*’
Tip:
RMAN will not overwrite database files on the Standby, they must be
removed first.
ASM will not allow the deletion of files if the database is open (files are
accessed)
ASM will implicitly remove empty sub-directories, so only need to delete
the files.
Recreate Standby Database
1. Logon to Standby Database Node A as oracle user and set
Oracle environment variables as follows:
. oraenv
ORACLE_SID = [PRDSBY1] ? PRDSBY1
2. Create a controlfile for the
Standby Database from the Primary Database. As the oracle user, logon to
Primary node A and execute the following commands:
srvctl stop database –d PRD
export ORACLE_SID=PRD1
sqlplus '/as sysdba'
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
'/tmp/PRDSBY.ctl';
SQL> ALTER DATABASE OPEN;
SQL> exit
3. As the oracle user, copy the
control file to the Standby site node A:
scp /tmp/PRDSBY.ctl stby-nodea-vip:/tmp/PRDSBY.ctl
4.
Temporarily
defer the shipping of logs to the Standby nodes. As oracle user, logon to Primary
node A and execute the following commands:
export ORACLE_SID=PRD1
sqlplus '/as sysdba'
SQL> alter system set
log_archive_dest_state_2=defer scope=memory sid='*';
5. As the oracle user,
logon to Standby node A and create a temporary database initialisation file as follows:
vi /tmp/initPRDSBY.ora
db_name=PRD
remote_login_passwordfile=EXCLUSIVE
compatible=11.1.0
6. As the oracle user,
logon to Standby node A and startup nomount Standby
instance 1 using the following commands:
export ORACLE_SID='PRDSBY1'
sqlplus '/ as sysdba'
startup nomount
pfile='/tmp/initPRDSBY.ora'
7. On Standby Node A, rename
any existing PFILE in $ORACLE_HOME/dbs
(default location for database initialisation files)
mv
$ORACLE_HOME/dbs/initPRDSBY1.ora $ORACLE_HOME/dbs/initPRDSBY1.bak
Tip: The original
$ORACLE_HOME/dbs/initPRDSBY1.ora
file
contains the following text:
SPFILE='+DATA/PRDSBY/spfilePRDSBY.ora'.
This
currently does not exist on the shared ASM storage. Therefore rename the file
to prevent RMAN using it to start the database instance. Otherwise RMAN will
fail to the following error stack:
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of Duplicate Db command at 02/24/2010 08:43:43
RMAN-03015:
error occurred in stored script Memory Script
RMAN-03009:
failure of backup command on prmy2 channel at 02/24/2010 08:43:43
ORA-17628:
Oracle error 19505 returned by remote Oracle server
ORA-19505 error is “failed to identify file”.
8. Ensure the Standby node A
has a similar SID List configured in $ORACLE_HOME/network/admin/listener.ora file to that shown below, and restart the
database listener if necessary:
SID_LIST_LISTENER_SBY_NODEA
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=PRDSBY_DGMGRL)
(SID_NAME = PRDSBY1)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
)
)
9. Ensure the Primary node A
has a similar TNS names entry in $ORACLE_HOME/network/admin/tnsnames.ora file to that shown below:
STBY_PRD_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stby-nodea-vip)(PORT =
1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRDSBY_DGMGRL)
)
)
10. As oracle user, logon to
Primary node A and test the connection to the Standby instance:
sqlplus sys/<password>@STBY_PRD_DGMGRL
as sysdba
11. If connection succeeds, use
RMAN to create a Physical Standby Database from the active PRD (Primary)
Database.
As oracle user, connect to the Primary Database using RMAN:
export ORACLE_SID=PRD1
rman target /
auxiliary sys/<password>@STBY_PRD_DGMGRL
12. Then execute the following
RMAN script to duplicate the Primary Database across the network to the
Standby:
run {
allocate
channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type
disk;
duplicate target database for standby
from active database
spfile
set
db_unique_name='PRDSBY'
set
control_files='/tmp/PRDSBY.ctl'
set
instance_number='1'
set
audit_file_dest='/u01/app/oracle/admin/PRD/adump'
set
remote_listener='LISTENERS_PRDSBY'
nofilenamecheck;
}
exit
See
example output in Appendix B of this document.
13. Once RMAN completes the operation, logon to Standby node A as oracle user and shutdown
the Standby Database as follows:
export ORACLE_SID=PRDSBY1
sqlplus '/as sysdba'
SQL>
14. Then startup mount the Standby Instance 1, create
SPFILE on ASM shared storage from temporary PFILE (see example in Appendix A) and
shutdown the Instance:
SQL>
startup mount pfile='/tmp/initPRDSBY1.ora'
SQL>
create spfile='+DATA/PRDSBY/spfilePRDSBY.ora' from pfile='/tmp/initPRDSBY1.ora';
SQL>
shutdown
SQL>
exit
15. Now rename the SPFILE
created by RMAN to prevent Oracle using it to start the Standby instance. As
oracle user execute the following command on Standby Node A:
mv $ORACLE_HOME/dbs/spfilePRDSBY1.ora
$ORACLE_HOME/dbs/spfilePRDSBY1.bak
16. As oracle user logon to Standby nodes A and B and
check the database initialisation file contains the following.
Tip: Remember to rename the PFILE back to original name on
Node A.
Node A
mv
$ORACLE_HOME/dbs/initPRDSBY1.bak $ORACLE_HOME/dbs/initPRDSBY1.ora
cat
$ORACLE_HOME/dbs/initPRDSBY1.ora
SPFILE='+DATA/PRDSBY/spfilePRDSBY.ora'
Node B
cat
$ORACLE_HOME/dbs/initPRDSBY2.ora
SPFILE='+DATA/PRDSBY/spfilePRDSBY.ora'
17. Startup nomount the
Standby Database Instance again:
export ORACLE_SID=PRDSBY1
sqlplus '/as sysdba'
SQL> startup
nomount
18. Then change the
value of the control_files Standby Database initialisation parameter to
‘+DATA’,’ +FLASH’:
SQL> alter system
set control_files=’+DATA’,’+FLASH’ scope=spfile;
SQL> exit
19. Remaining on Standby node A, use RMAN to restore the Physical
Standby Database controlfile to ASM shared storage [+DATA and +FLASH
diskgroups]:
rman
target /
restore
controlfile from ’/tmp/PRDSBY.ctl’
exit
20. From Standby node A shutdown the Standby Database as SYSDBA:
export ORACLE_SID=PRDSBY1
sqlplus '/as sysdba'
SQL> shutdown
SQL> exit
21. Then startup Standby Database cluster using srvctl
utility:
srvctl start
database -d PRDSBY
srvctl
status database –d PRDSBY
22. From Standby node 1, place
the Standby Database into Automatic Managed Recovery mode, known as starting
the Managed Recovery Process (MRP):
export ORACLE_SID=PRDSBY1
sqlplus '/as sysdba'
SQL>
alter database recover managed standby database disconnect from session;
SQL> exit
23. Logon to Primary Database
instance1 as SYSDBA and re-enable log shipping for Data Guard as follows:
export ORACLE_SID=PRD1
sqlplus '/as sysdba'
SQL> alter system set log_archive_dest_state_2=enable
scope=memory sid='*';
SQL> alter system archive log current;
SQL> exit
24. After a short while (enough time for Standby Database
to synchronise with the Primary), stop the Managed Recovery Process as follows:
export ORACLE_SID=PRDSBY1
sqlplus '/as sysdba'
SQL>
alter database recover managed standby database cancel;
25. Then open the database and restart MRP:
SQL>
alter database open;
SQL>
alter database recover managed standby database disconnect from session;
SQL> exit
Tip: Only 1 Instance can run the Managed Recovery Process, otherwise the above alter database command
will fail to:
ORA-01153:
an incompatible media recovery is active
26. Logon Standby node 2 and open the database from
instance 2:
export ORACLE_SID=PRDSBY2
sqlplus '/as sysdba'
SQL>
alter database open;
SQL>
exit
27. Then start the Standby Database
services using srvctl:
srvctl start service –d PRDSBY –s PRD_ACTIVE
28. As oracle user, log back
onto Primary node A and start database instance 2 using srvctl:
srvctl start instance –d PRD –i PRD2
29. Then start the Primary Database
services using srvctl:
srvctl start service –d PRD –s PRD_ACTIVE
Check Data Guard is Shipping and Applying Logs on Standby Database
30. Execute the following SQL on
the Standby Database to verify that redo data is being received from the
Primary, then registered and applied on the Standby :
export ORACLE_SID=PRDSBY1
sqlplus '/as sysdba'
SQL> alter session set nls_date_format=’YYYY-MON-DD
HH24:MI:SS’;
SQL>
SELECT SEQUENCE#, THREAD#, FIRST_TIME, NEXT_TIME, APPLIED
FROM V$ARCHIVED_LOG
ORDER BY THREAD#, SEQUENCE#;
Example:
SEQUENCE#
THREAD# FIRST_TIME
NEXT_TIME APPLIED
---------- ---------- --------------------
----------------------------
1 1 2009-MAR-30 18:11:04 2009-MAR-30
18:14:30 YES
2 1 2009-MAR-30 18:14:30 2009-MAR-31
13:49:18 YES
3 1 2009-MAR-31 14:33:18 2009-MAR-31
14:33:28 YES
4 1 2009-MAR-31 14:33:28 2009-MAR-31
15:28:26 YES
5 1 2009-MAR-31 15:45:44 2009-MAR-31
15:45:50 YES
6 1 2009-MAR-31 15:45:50 2009-MAR-31
15:53:17 YES
7 1 2009-MAR-31 15:53:17 2009-MAR-31
16:00:37 YES
31. Also check the Standby Database instance 1 alert log
for errors:
tail –f /u01/app/oracle/diag/rdbms/PRDSBY/PRDSBY1/trace/alert_PRDSBY1.log
What if my terminal session times-out before the RMAN clone completes?
Depending on the size of your
Primary Database and the speed of your network, it is possible that your
terminal session (such as PuTTy) times-out whilst executing the RMAN clone
script.
Tip: Of course, the time out issue can be completely avoided
by running the RMAN clone commands within a shell script in the background. See
Appendix C for an example script.
E.g.
To run the script in the
background:
nohup RMAN_clone_script.ksh
&
To view the output during execution:
tail –f nohup.out
Should the time-out occur
during the foreground execution, RMAN will continue to copy the datafiles from
the Primary site to the Standby site. However, RMAN will be unable to conduct
the post datafile copy operations which include switching to the datafile copy
on the Standby site.
Let’s take a closer look at
the datafile names and what has to done before the Physical Standby database
can be opened.
Below is an extract from the
Physical Standby instance 1 alert log when attempting to start the Managed
Recovery Process (MRP):
alert_PRDSBY1.ora
-----------------
Completed: ALTER
DATABASE MOUNT
Tue May 04 18:04:28
2010
alter database
recover managed standby database disconnect from session
Attempt to start
background Managed Standby Recovery process (PRDSBY1)
Tue May 04 18:04:28
2010
MRP0 started with
pid=36, OS id=2762
MRP0: Background
Managed Standby Recovery process started (PRDSBY1)
Fast Parallel Media
Recovery enabled
Tue May 04 18:04:33
2010
Managed Standby
Recovery not using Real Time Apply
Tue May 04 18:04:33
2010
Errors in file
/u01/app/oracle/diag/rdbms/prdsby/PRDSBY1/trace/PRDSBY1_dbw0_1357.trc:
ORA-01157: cannot
identify/lock data file 1 - see DBWR trace file
ORA-01110: data file
1: '+DATA/PRD/datafile/system.263.701555551'
ORA-17503: ksfdopn:2
Failed to open file +DATA/PRD/datafile/system.263.701555551
ORA-15012: ASM file
'+DATA/PRD/datafile/system.263.701555551' does not exist
Note that Oracle cannot
identify data file 1 that supports the System tablespace.
When we query v$datafile from the mounted instance we see all files have zero
bytes:
sqlplus / as sysdba
SQL> select
file#, bytes/(1024*1024) mbytes from v$datafile;
FILE#
MBYTES
----------
----------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
This is because the datafiles
need to be renamed to match those in the Standby control file (used to start
the instance). The original datafile and the output filenames can be found in
the stdout from the RMAN clone script. (See Appendix B)
E.g.
channel prmy4:
starting datafile copy
input datafile file
number=00001 name=+DATA/prd/datafile/system.263.701555551
output file
name=+DATA/PRDSBY/datafile/system.280.718100033
Another method to obtain the
required datafile names is to look in ASM on the Standby site at the physical
files, using ASM command line utility asmcmd:
$ . oraenv
ORACLE_SID = [PRDSBY1] ? +ASM1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/asm is
/u01/app/oracle
$ asmcmd
ASMCMD> cd data/prdsby/datafile
ASMCMD> ls
PRODUCTION_ARCHIVE_DATA.287.718097165
PRODUCTION_DATA.262.718097165
PRODUCTION_INDEX.286.718097167
PRODUCTION_LOB.266.718104189
SNMP_DATA.276.718104929
SNMP_INDEX.277.718105127
STREAMS.294.718105201
SYBASE_DATA.275.718104775
SYBASE_INDEX.295.718105287
SYSAUX.268.718103803
SYSTEM.280.718100033
UNDOTBS1.285.718097167
UNDOTBS2.292.718102707
USERS.278.718105353
ASMCMD> exit
Steps to rename datafiles on Physical Standby database
- Before we can rename the datafiles on the Standby site, the standby_file_management database initialisation parameter must be set to
MANUAL:
$ . oraenv
ORACLE_SID = [+ASM1] ? PRDSBY1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is
/u01/app/oracle
sqlplus / as sysdba
SQL> alter system
set standby_file_management=MANUAL scope=spfile sid='*';
System altered.
SQL> shutdown
..
SQL> startup
mount
- Now rename the datafiles.
E.g.
alter database
rename file '+DATA/prd/datafile/production_data.293.702848931' to '+DATA/prdsby/datafile/production_data.262.718097165';
alter database
rename file '+DATA/prd/datafile/production_archive_data.296.702848955' to
'+DATA/prdsby/datafile/production_archive_data.287.718097165';
alter database
rename file '+DATA/prd/datafile/production_index.294.702848943' to '+DATA/prdsby/datafile/production_index.286.718097167';
alter database
rename file '+DATA/prd/datafile/undotbs1.288.701555555' to '+DATA/prdsby/datafile/undotbs1.285.718097167';
alter database
rename file '+DATA/prd/datafile/system.263.701555551' to '+DATA/prdsby/datafile/system.280.718100033';
alter database
rename file '+DATA/prd/datafile/undotbs2.286.701555565' to '+DATA/prdsby/datafile/undotbs2.292.718102707';
alter database
rename file '+DATA/prd/datafile/sysaux.289.701555553' to '+DATA/prdsby/datafile/sysaux.268.718103803';
alter database
rename file '+DATA/prd/datafile/production_lob.295.702848949' to '+DATA/prdsby/datafile/production_lob.266.718104189';
alter database
rename file '+DATA/prd/datafile/sybase_data.291.702848847' to '+DATA/prdsby/datafile/sybase_data.275.718104775';
alter database
rename file '+DATA/prd/datafile/snmp_data.297.703094083' to '+DATA/prdsby/datafile/snmp_data.276.718104929';
alter database
rename file '+DATA/prd/datafile/snmp_index.298.703094085' to '+DATA/prdsby/datafile/snmp_index.277.718105127';
alter database
rename file '+DATA/prd/datafile/streams.290.701571837' to '+DATA/prdsby/datafile/streams.294.718105201';
alter database
rename file '+DATA/prd/datafile/sybase_index.292.702848849' to '+DATA/prdsby/datafile/sybase_index.295.718105287';
alter database
rename file '+DATA/prd/datafile/users.285.701555565' to '+DATA/prdsby/datafile/users.278.718105353';
- Set the standby_file_management database initialisation parameter back to AUTO:
SQL> alter system
set standby_file_management=AUTO scope=both sid='*';
System altered.
- Query v$datafile to see if Oracle can access the files:
SQL> select
file#, bytes/(1024*1024) mbytes from v$datafile;
FILE#
MBYTES
----------
----------
1
6280
2
1095.125
3
8300
4
3165
5
6
7
8
9
149052
10
115054.75
11
1024
12 136777.063
13
259.125
14
139.875
- Yes, we have filesizes! Now start the MRP on Physical Standby
database instance 1:
SQL> alter
database recover managed standby database disconnect from session;
Database altered.
- To open the database read-only, the MRP has to be stopped,
database opened and MRP restarted. This is dependant on the Primary and
Standby databases being synchronised:
SQL> ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; -- stop MRP
Database altered.
SQL> ALTER
DATABASE OPEN; -- Open DB Read Only
Database altered.
SQL> alter
database recover managed standby database disconnect from session;
Database altered.
- You do not need to rename the database tempfile as this is
implicitly created by Oracle on instance startup, as seen in the Physical
Standby instance 1 alert log:
alert_PRDSBY1.ora
-----------------
ORA-01186: file 201
failed verification tests
ORA-01157: cannot
identify/lock data file 201 - see DBWR trace file
ORA-01110: data file
201: '+DATA'
File 201 not
verified due to error ORA-01157
Dictionary check
complete
Re-creating tempfile
+DATA as +DATA/prdsby/tempfile/temp.301.718189579
Another Gotcha!
Despite successfully cloning
your Primary Database using RMAN, it is possible that when you attempt to start
the Managed Recovery Process (MRP) on the Physical Standby, it fails to the
following error seen in the database instance alert log:
ORA-19909:
datafile 1 belongs to an orphan incarnation
This is due to the Primary Database having
only 1 incarnation, while the Standby has many, as seen below:
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database
Incarnations
DB Key Inc Key DB Name DB ID
STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1
PRD
2067014306 PARENT
1 29-OCT-09
3 3
PRD
2067014306 ORPHAN 1385720549
28-APR-10
2 2
PRD
2067014306 CURRENT 13173291621 28-JUN-10
The ORA-19909 error can occur
if the RMAN clone has been performed before, or the Standby Database has been
flashed back.
Solution
The
solution is to find out the CURRENT incarnation from the Primary Database and reset
the Standby Database incarnation to match, using the following RMAN command:
reset
database to incarnation <key>
then perform Recovery.
E.g.
On the Standby Site:
rman target=/
RMAN > reset database to incarnation 1
RMAN > exit
sqlplus / as sysdba
SQL> alter database recover managed standby database disconnect from session;
Appendix A
Standby Database
Initialisation Parameter File
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/PRD/adump'
*.audit_trail='db'
*.cluster_database_instances=2
*.cluster_database=TRUE
*.compatible='11.1.0.0.0'
*.control_files='/tmp/PRDSBY.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='PRD'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=822486237184
*.db_unique_name='PRDSBY'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=PRDSBYXDB)'
*.fal_client='PRDSBY'
*.fal_server='PRD'
PRD2.instance_number=2
PRD1.instance_number=1
PRDSBY1.instance_number=1
PRDSBY2.instance_number=2
PRDSBY1.local_listener='LISTENER_PRDSBY1'
PRDSBY2.local_listener='LISTENER_PRDSBY2'
*.log_archive_config='SEND,
RECEIVE, DG_CONFIG=(PRD,PRDSBY)'
*.log_archive_dest_1='LOCATION=+FLASH
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRDSBY'
*.log_archive_dest_2='SERVICE=PRD
LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRD'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='prd_arch_%t_%s_%r.log'
*.log_archive_max_processes=4
*.max_dump_file_size='100M'
*.memory_target=21474836480
*.nls_language='ENGLISH'
*.nls_territory='UNITED
KINGDOM'
*.open_cursors=300
*.processes=1000
*.remote_listener='LISTENERS_PRDSBY'
*.remote_login_passwordfile='exclusive'
*.service_names=''
*.sessions=1105
*.standby_file_management='AUTO'
*.streams_pool_size=536870912
PRD2.thread=2
PRD1.thread=1
*.undo_retention=3600
PRD1.undo_tablespace='UNDOTBS1'
PRD2.undo_tablespace='UNDOTBS2'
Appendix B
Successful completion of RMAN
duplicate database script
Recovery Manager:
Release 11.1.0.7.0 - Production on Wed Feb 24 09:05:53 2010
Copyright (c) 1982,
2007, Oracle. All rights reserved.
connected to target
database: PRD (DBID=2066975734)
connected to
auxiliary database: PRD (not mounted)
RMAN> 2> 3>
4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15>
using target
database control file instead of recovery catalog
allocated channel:
prmy1
channel prmy1:
SID=1040 instance=PRD1 device type=DISK
allocated channel:
prmy2
channel prmy2:
SID=1035 instance=PRD1 device type=DISK
allocated channel:
prmy3
channel prmy3:
SID=1034 instance=PRD1 device type=DISK
allocated channel:
prmy4
channel prmy4:
SID=1033 instance=PRD1 device type=DISK
allocated channel:
stby
channel stby: SID=93
device type=DISK
Starting Duplicate
Db at 24-02-2010 09:06:00
contents of Memory
Script:
{
backup as copy reuse
file
'/u01/app/oracle/product/11.1.0/db_1/dbs/orapwPRD1' auxiliary format
'/u01/app/oracle/product/11.1.0/db_1/dbs/orapwPRDSBY1' file
'+DATA/prd/spfileprd.ora' auxiliary format
'/u01/app/oracle/product/11.1.0/db_1/dbs/spfilePRDSBY1.ora' ;
sql clone "alter system set spfile=
''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilePRDSBY1.ora''";
}
executing Memory
Script
Starting backup at
24-02-2010 09:06:00
Finished backup at
24-02-2010 09:06:01
sql statement: alter
system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilePRDSBY1.ora''
contents of Memory
Script:
{
sql clone "alter system set db_unique_name =
''PRDSBY'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/tmp/PRDSBY.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set instance_number =
1 comment=
'''' scope=spfile";
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/PRD/adump'' comment=
'''' scope=spfile";
sql clone "alter system set remote_listener =
''LISTENERS_PRDSBY'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory
Script
sql statement: alter
system set db_unique_name = ''PRDSBY'' comment= '''' scope=spfile
sql statement: alter
system set control_files = ''/tmp/PRDSBY.ctl'' comment= ''''
scope=spfile
sql statement: alter
system set instance_number = 1 comment= '''' scope=spfile
sql statement: alter
system set audit_file_dest = ''/u01/app/oracle/admin/PRD/adump'' comment=
'''' scope=spfile
sql statement: alter
system set remote_listener = ''LISTENERS_PRDSBY'' comment= ''''
scope=spfile
Oracle instance shut
down
connected to
auxiliary database (not started)
Oracle instance
started
Total System Global
Area 1603411968 bytes
Fixed Size 2160112 bytes
Variable Size 855640592 bytes
Database
Buffers 738197504 bytes
Redo Buffers 7413760 bytes
allocated channel:
stby
channel stby:
SID=1077 instance=PRDSBY1 device type=DISK
contents of Memory
Script:
{
backup as copy current controlfile for
standby auxiliary format '/tmp/PRDSBY.ctl';
sql clone 'alter database mount standby
database';
}
executing Memory
Script
Starting backup at
24-02-2010 09:06:13
channel prmy1:
starting datafile copy
copying standby
control file
output file
name=/u01/app/oracle/product/11.1.0/db_1/dbs/snapcf_PRD1.f
tag=TAG20100224T090613 RECID=6 STAMP=711795975
channel prmy1:
datafile copy complete, elapsed time: 00:00:07
Finished backup at
24-02-2010 09:06:21
sql statement: alter
database mount standby database
RMAN-05529: WARNING:
DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group
only.
contents of Memory
Script:
{
set newname for tempfile 1 to
"+data";
switch clone tempfile all;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
set newname for datafile 4 to
"+data";
set newname for datafile 5 to
"+data";
set newname for datafile 6 to
"+data";
set newname for datafile 7 to
"+data";
set newname for datafile 8 to
"+data";
set newname for datafile 9 to
"+data";
set newname for datafile 10 to
"+data";
set newname for datafile 11 to
"+data";
set newname for datafile 12 to
"+data";
set newname for datafile 13 to
"+data";
set newname for datafile 14 to
"+data";
backup as copy reuse
datafile
1 auxiliary format
"+data" datafile
2 auxiliary format
"+data" datafile
3 auxiliary format
"+data" datafile
4 auxiliary format
"+data" datafile
5 auxiliary format
"+data" datafile
6 auxiliary format
"+data" datafile
7 auxiliary format
"+data" datafile
8 auxiliary format
"+data" datafile
9 auxiliary format
"+data" datafile
10 auxiliary format
"+data" datafile
11 auxiliary format
"+data" datafile
12 auxiliary format
"+data" datafile
13 auxiliary format
"+data" datafile
14 auxiliary format
"+data" ;
sql 'alter system archive log current';
}
executing Memory
Script
executing command:
SET NEWNAME
renamed tempfile 1
to +data in control file
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
executing command:
SET NEWNAME
Starting backup at
24-02-2010 09:06:26
channel prmy1:
starting datafile copy
input datafile file
number=00009 name=+DATA/prd/datafile/production_data.284.708792257
channel prmy2:
starting datafile copy
input datafile file
number=00010 name=+DATA/prd/datafile/production_index.283.708792273
channel prmy3:
starting datafile copy
input datafile file
number=00014 name=+DATA/prd/datafile/production_archive_data.285.708792293
channel prmy4:
starting datafile copy
input datafile file
number=00003 name=+DATA/prd/datafile/undotbs1.265.701517025
output file
name=+DATA/PRDSBY/datafile/undotbs1.273.711795991 tag=TAG20100224T090627
RECID=0 STAMP=0
channel prmy4:
datafile copy complete, elapsed time: 00:02:15
channel prmy4:
starting datafile copy
input datafile file
number=00001 name=+DATA/prd/datafile/system.263.701517017
output file
name=+DATA/PRDSBY/datafile/system.272.711796123 tag=TAG20100224T090627 RECID=0
STAMP=0
channel prmy4:
datafile copy complete, elapsed time: 00:02:25
channel prmy4:
starting datafile copy
input datafile file
number=00004 name=+DATA/prd/datafile/undotbs2.267.701517037
output file
name=+DATA/PRDSBY/datafile/undotbs2.271.711796269 tag=TAG20100224T090627
RECID=0 STAMP=0
channel prmy4:
datafile copy complete, elapsed time: 00:01:05
channel prmy4:
starting datafile copy
input datafile file
number=00002 name=+DATA/prd/datafile/sysaux.264.701517021
output file
name=+DATA/PRDSBY/datafile/sysaux.270.711796335 tag=TAG20100224T090627 RECID=0
STAMP=0
channel prmy4:
datafile copy complete, elapsed time: 00:00:56
channel prmy4:
starting datafile copy
input datafile file
number=00013 name=+DATA/prd/datafile/production_lob.282.708792283
output file
name=+DATA/PRDSBY/datafile/production_lob.269.711796391 tag=TAG20100224T090627
RECID=0 STAMP=0
channel prmy4:
datafile copy complete, elapsed time: 00:00:45
channel prmy4:
starting datafile copy
input datafile file
number=00007 name=+DATA/prd/datafile/sybase_data.280.708792111
output file
name=+DATA/PRDSBY/datafile/sybase_data.262.711796435 tag=TAG20100224T090627
RECID=0 STAMP=0
channel prmy4:
datafile copy complete, elapsed time: 00:00:25
channel prmy4:
starting datafile copy
input datafile file
number=00011 name=+DATA/prd/datafile/snmp_data.278.701874127
output file
name=+DATA/PRDSBY/datafile/snmp_data.261.711796461 tag=TAG20100224T090627
RECID=0 STAMP=0
channel prmy4:
datafile copy complete, elapsed time: 00:00:15
channel prmy4:
starting datafile copy
input datafile file
number=00006 name=+DATA/prd/datafile/streams.277.701534093
output file
name=+DATA/PRDSBY/datafile/streams.260.711796477 tag=TAG20100224T090627 RECID=0
STAMP=0
channel prmy4:
datafile copy complete, elapsed time: 00:00:15
channel prmy4: starting
datafile copy
input datafile file
number=00008 name=+DATA/prd/datafile/sybase_index.281.708792115
output file
name=+DATA/PRDSBY/datafile/sybase_index.259.711796491 tag=TAG20100224T090627
RECID=0 STAMP=0
channel prmy4:
datafile copy complete, elapsed time: 00:00:15
channel prmy4:
starting datafile copy
input datafile file
number=00012 name=+DATA/prd/datafile/snmp_index.279.701874129
output file
name=+DATA/PRDSBY/datafile/snmp_index.258.711796507 tag=TAG20100224T090627
RECID=0 STAMP=0
channel prmy4: datafile
copy complete, elapsed time: 00:00:07
channel prmy4:
starting datafile copy
input datafile file
number=00005 name=+DATA/prd/datafile/users.268.701517039
output file
name=+DATA/PRDSBY/datafile/users.257.711796513 tag=TAG20100224T090627 RECID=0
STAMP=0
channel prmy4:
datafile copy complete, elapsed time: 00:00:07
output file
name=+DATA/PRDSBY/datafile/production_archive_data.274.711795991
tag=TAG20100224T090627 RECID=0 STAMP=0
channel prmy3:
datafile copy complete, elapsed time: 00:29:31
output file
name=+DATA/PRDSBY/datafile/production_index.266.711795991
tag=TAG20100224T090627 RECID=0 STAMP=0
channel prmy2:
datafile copy complete, elapsed time: 00:47:21
output file
name=+DATA/PRDSBY/datafile/production_data.275.711795991 tag=TAG20100224T090627
RECID=0 STAMP=0
channel prmy1:
datafile copy complete, elapsed time: 00:52:21
Finished backup at
24-02-2010 09:58:48
sql statement: alter
system archive log current
contents of Memory
Script:
{
switch clone datafile all;
}
executing Memory
Script
datafile 1 switched
to datafile copy
input datafile copy
RECID=6 STAMP=711799130 file name=+DATA/PRDSBY/datafile/system.272.711796123
datafile 2 switched
to datafile copy
input datafile copy
RECID=7 STAMP=711799130 file name=+DATA/PRDSBY/datafile/sysaux.270.711796335
datafile 3 switched
to datafile copy
input datafile copy
RECID=8 STAMP=711799131 file name=+DATA/PRDSBY/datafile/undotbs1.273.711795991
datafile 4 switched
to datafile copy
input datafile copy
RECID=9 STAMP=711799131 file name=+DATA/PRDSBY/datafile/undotbs2.271.711796269
datafile 5 switched
to datafile copy
input datafile copy
RECID=10 STAMP=711799131 file name=+DATA/PRDSBY/datafile/users.257.711796513
datafile 6 switched
to datafile copy
input datafile copy
RECID=11 STAMP=711799131 file name=+DATA/PRDSBY/datafile/streams.260.711796477
datafile 7 switched
to datafile copy
input datafile copy
RECID=12 STAMP=711799131 file name=+DATA/PRDSBY/datafile/sybase_data.262.711796435
datafile 8 switched
to datafile copy
input datafile copy
RECID=13 STAMP=711799131 file name=+DATA/PRDSBY/datafile/sybase_index.259.711796491
datafile 9 switched
to datafile copy
input datafile copy
RECID=14 STAMP=711799131 file name=+DATA/PRDSBY/datafile/production_data.275.711795991
datafile 10 switched
to datafile copy
input datafile copy
RECID=15 STAMP=711799131 file name=+DATA/PRDSBY/datafile/production_index.266.711795991
datafile 11 switched
to datafile copy
input datafile copy
RECID=16 STAMP=711799131 file name=+DATA/PRDSBY/datafile/snmp_data.261.711796461
datafile 12 switched
to datafile copy
input datafile copy
RECID=17 STAMP=711799131 file name=+DATA/PRDSBY/datafile/snmp_index.258.711796507
datafile 13 switched
to datafile copy
input datafile copy
RECID=18 STAMP=711799131 file name=+DATA/PRDSBY/datafile/production_lob.269.711796391
datafile 14 switched
to datafile copy
input datafile copy
RECID=19 STAMP=711799131 file name=+DATA/PRDSBY/datafile/production_archive_data.274.711795991
Finished Duplicate
Db at 24-02-2010 09:58:51
released channel:
prmy1
released channel:
prmy2
released channel:
prmy3
released channel:
prmy4
released channel:
stby
RMAN>
Recovery Manager
complete.
Appendix C
Example shell script for
executing the RMAN clone (RMAN_clone_script.ksh)
#!/bin/ksh
export
ORACLE_SID=PRD1
export
ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
rman target /
auxiliary sys/<password>@STBY_PRD_DGMGRL <<EOF
run {
allocate
channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type
disk;
duplicate target database for standby
from active database
spfile
set
db_unique_name='PRDSBY'
set
control_files='/tmp/PRDSBY.ctl'
set
instance_number='1'
set
audit_file_dest='/u01/app/oracle/admin/PRD/adump'
set
remote_listener='LISTENERS_PRDSBY'
nofilenamecheck;
}
exit
EOF
_______________________________________________________________________________
Did you find the article useful?
Please provide your feedback by voting now.
If you have a comment or question, please complete and submit the form below.
_______________________________________________________________________________