How to use standby backup to restore primary db
–Propose: Backup db from physical standby db by using RMAN, recove datafile of primary standby db.
–enviroment:
Three machines:
Machine A: 172.16.100.29 Linux ES3
Machine B: 172.16.100.21 Linux ES3
Machine C: 172.16.100.31 Linux ES3
Three machines:
Machine A: 172.16.100.29 Linux ES3
Machine B: 172.16.100.21 Linux ES3
Machine C: 172.16.100.31 Linux ES3
–step1. build Machine A as primary db and Machine B as standby db
–create another db on Machine C
Machine DB_TNSNAME
A STD1.29
B STD1.21
C STD1.31
Machine DB_TNSNAME
A STD1.29
B STD1.21
C STD1.31
–step2. create recovery catalog on Machine C
Machine C:
SQL> conn / as sysdba
SQL> create tablespace rcat datafile ‘/opt/oracle/oradata/rcat01.dbf’ size 50M;
SQL> create user rcat identified by rcat default tablespace rcat temporary tablesapce temp;
SQL> grant connect, resource, recovery_catalog_owner to rcat;
Machine C:
SQL> conn / as sysdba
SQL> create tablespace rcat datafile ‘/opt/oracle/oradata/rcat01.dbf’ size 50M;
SQL> create user rcat identified by rcat default tablespace rcat temporary tablesapce temp;
SQL> grant connect, resource, recovery_catalog_owner to rcat;
Machine A:
rman catalog=rcat/rcat@STD1.31
RMAN> create catalog tablespace rcat;
RMAN> connect target sys/oracle@STD1.29
RMAN> register database;
RMAN> configure channel 1 device type disk format ‘/opt/oracle/rman/std1_%U’;
RMAN> exit;
–step3.backup standby database
Machine C:
rman catalog=rcat/rcat@STD1.31 target=sys/oracle@STD1.21
RMAN> backup database;
RMAN> exit;
rman catalog=rcat/rcat@STD1.31
RMAN> create catalog tablespace rcat;
RMAN> connect target sys/oracle@STD1.29
RMAN> register database;
RMAN> configure channel 1 device type disk format ‘/opt/oracle/rman/std1_%U’;
RMAN> exit;
–step3.backup standby database
Machine C:
rman catalog=rcat/rcat@STD1.31 target=sys/oracle@STD1.21
RMAN> backup database;
RMAN> exit;
–step4. test recover on primary datafile
–move system tablespace’s file to another space
Machine A:
SQL> shutdown immediate
mv /opt/oracle/oradata/std1/system01.dbf /tmp/system01.dbf
SQL> startup mount
–move system tablespace’s file to another space
Machine A:
SQL> shutdown immediate
mv /opt/oracle/oradata/std1/system01.dbf /tmp/system01.dbf
SQL> startup mount
–step5. restore by rman
Machine C:
copy rman backup files from Machine B to Machine A , using the same directory
Machine C:
copy rman backup files from Machine B to Machine A , using the same directory
–set NLS_CHARACTERSET value to be the same as Machine A
export NLS_CHARACTERSET=AL32UTF8
rman catalog=rcat/rcat@STD1.31 target=sys/oracle@STD1.29
RMAN> restore datafile 1;
RMAN> recover datafile 1;
RMAN> exit
export NLS_CHARACTERSET=AL32UTF8
rman catalog=rcat/rcat@STD1.31 target=sys/oracle@STD1.29
RMAN> restore datafile 1;
RMAN> recover datafile 1;
RMAN> exit
–step6.startup primary database
SQL> alter database startup;
–step7.backup from standby database and recover the primary database without redo logs
Machine C:
–1.backup datafiles at standby database
rman catalog=rcat/rcat@STD1_31 target=sys/oracle@STD1_21
RMAN> backup database;
…….
channel ORA_DISK_1: starting piece 1 at 05-AUG-05
channel ORA_DISK_1: finished piece 1 at 05-AUG-05
piece handle=/opt/oracle/rman/std1_06gradfv_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:17
Finished backup at 05-AUG-05
SQL> alter database startup;
–step7.backup from standby database and recover the primary database without redo logs
Machine C:
–1.backup datafiles at standby database
rman catalog=rcat/rcat@STD1_31 target=sys/oracle@STD1_21
RMAN> backup database;
…….
channel ORA_DISK_1: starting piece 1 at 05-AUG-05
channel ORA_DISK_1: finished piece 1 at 05-AUG-05
piece handle=/opt/oracle/rman/std1_06gradfv_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:17
Finished backup at 05-AUG-05
RMAN-06497: WARNING: controlfile is not current, controlfile autobackup skipped
RMAN> exit
shell> rman catalog=rcat/rcat@STD1_31 target=sys/oracle@STD1_29
–2.backup control file from primary db
RMAN> backup current controlfile;
shell> rman catalog=rcat/rcat@STD1_31 target=sys/oracle@STD1_29
–2.backup control file from primary db
RMAN> backup current controlfile;
Starting backup at 05-AUG-05
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
channel ORA_DISK_1: starting piece 1 at 05-AUG-05
channel ORA_DISK_1: finished piece 1 at 05-AUG-05
piece handle=/opt/oracle/rman/std1_03grae2o_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-AUG-05
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
channel ORA_DISK_1: starting piece 1 at 05-AUG-05
channel ORA_DISK_1: finished piece 1 at 05-AUG-05
piece handle=/opt/oracle/rman/std1_03grae2o_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-AUG-05
–3.backup spfile from primary db
RMAN> backup spfile;
RMAN> backup spfile;
Starting backup at 05-AUG-05
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 05-AUG-05
channel ORA_DISK_1: finished piece 1 at 05-AUG-05
piece handle=/opt/oracle/rman/std1_04grae3f_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-AUG-05
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 05-AUG-05
channel ORA_DISK_1: finished piece 1 at 05-AUG-05
piece handle=/opt/oracle/rman/std1_04grae3f_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-AUG-05
RMAN> exit
–4.delete datafile, control files and spfile from primary db
— copy the backup datafile from Machine B to Machine A
Machine A:
lsnrctl stop
mkdir /tmp/orabak
mv product/9.2.0/dbs/spfilestd1.ora /tmp/orabak/
mv oradata/std1/*.ctl /tmp/orabak/
mv oradata/std1/*.dbf /tmp/orabak/
mv oradata/std1/redo*.log /tmp/orabak/
— copy the backup datafile from Machine B to Machine A
Machine A:
lsnrctl stop
mkdir /tmp/orabak
mv product/9.2.0/dbs/spfilestd1.ora /tmp/orabak/
mv oradata/std1/*.ctl /tmp/orabak/
mv oradata/std1/*.dbf /tmp/orabak/
mv oradata/std1/redo*.log /tmp/orabak/
sftp 172.16.100.21
Connecting to 172.16.100.21…
oracle@172.16.100.21’s password:
sftp> get /opt/oracle/rman/std1_06gradfv_1_1 /opt/oracle/rman/std1_06gradfv_1_1
std1_06gradfv_1_1 100% 231MB 3.3MB/s 01:08
sftp> exit
lsnrctl start
Connecting to 172.16.100.21…
oracle@172.16.100.21’s password:
sftp> get /opt/oracle/rman/std1_06gradfv_1_1 /opt/oracle/rman/std1_06gradfv_1_1
std1_06gradfv_1_1 100% 231MB 3.3MB/s 01:08
sftp> exit
lsnrctl start
Machine C:
–5.get the dbid from standby db
rman catalog=rcat/rcat@std1_31 target=sys/oracle@std1_21
–5.get the dbid from standby db
rman catalog=rcat/rcat@std1_31 target=sys/oracle@std1_21
Recovery Manager: Release 9.2.0.6.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: STD1 (DBID=3008965527)
connected to recovery catalog database
connected to recovery catalog database
RMAN> exit
–the dbid id 3008965527
–6.startup primary db with no spfile
rman catalog=rcat/rcat@std1_31 target=sys/oracle@std1_29
Recovery Manager: Release 9.2.0.6.0 – Production
–the dbid id 3008965527
–6.startup primary db with no spfile
rman catalog=rcat/rcat@std1_31 target=sys/oracle@std1_29
Recovery Manager: Release 9.2.0.6.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
connected to recovery catalog database
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/opt/oracle/product/9.2.0/dbs/initstd1.ora’
LRM-00109: could not open parameter file ‘/opt/oracle/product/9.2.0/dbs/initstd1.ora’
trying to start the Oracle instance without parameter files …
Oracle instance started
Oracle instance started
Total System Global Area 97588624 bytes
Fixed Size 451984 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
RMAN> set dbid=3008965527
executing command: SET DBID
–7.using datafile backup to recove spfile
RMAN> restore spfile;
RMAN> restore spfile;
Starting restore at 05-AUG-05
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=/opt/oracle/product/9.2.0/dbs/spfilestd1.ora
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_06gradfv_1_1 tag=TAG20050805T100107 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 05-AUG-05
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=/opt/oracle/product/9.2.0/dbs/spfilestd1.ora
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_06gradfv_1_1 tag=TAG20050805T100107 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 05-AUG-05
–8.using backup controlfile(std1_03grae2o_1_1) to recover controlfile
RMAN> restore controlfile;
RMAN> restore controlfile;
Starting restore at 05-AUG-05
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring controlfile
output filename=/opt/oracle/product/9.2.0/dbs/cntrlstd1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_03grae2o_1_1 tag=TAG20050805T100743 params=NULL
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/opt/oracle/product/9.2.0/dbs/cntrlstd1.dbf
Finished restore at 05-AUG-05
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring controlfile
output filename=/opt/oracle/product/9.2.0/dbs/cntrlstd1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_03grae2o_1_1 tag=TAG20050805T100743 params=NULL
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/opt/oracle/product/9.2.0/dbs/cntrlstd1.dbf
Finished restore at 05-AUG-05
RMAN> shutdown immediate
Oracle instance shut down
RMAN> exit
–9.restart primary database
Machine A:
sqlplus /nolog
SQL> conn / as sysdba
SQL> startup nomount
SQL> show parameter control_files
Machine A:
sqlplus /nolog
SQL> conn / as sysdba
SQL> startup nomount
SQL> show parameter control_files
NAME TYPE VALUE
———————————— ———– ——————————
control_files string /opt/oracle/oradata/physical_s
td1.ctl
SQL> host
———————————— ———– ——————————
control_files string /opt/oracle/oradata/physical_s
td1.ctl
SQL> host
mv /opt/oracle/product/9.2.0/dbs/cntrlstd1.dbf /opt/oracle/oradata/physical_std1.ctl
exit
SQL> alter database mount;
Database altered.
–10.restore primary database
Machine C:
rman catalog=rcat/rcat@std1_31 target=sys/oracle@std1_29
RMAN> restore database;
Machine C:
rman catalog=rcat/rcat@std1_31 target=sys/oracle@std1_29
RMAN> restore database;
Starting restore at 05-AUG-05
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/std1/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/std1/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/std1/indx01.dbf
restoring datafile 00004 to /opt/oracle/oradata/std1/tools01.dbf
restoring datafile 00005 to /opt/oracle/oradata/std1/users01.dbf
restoring datafile 00006 to /opt/oracle/oradata/std1/logmnrts.dbf
restoring datafile 00007 to /opt/oracle/oradata/std1/newlogminer.dbf
restoring datafile 00008 to /opt/oracle/oradata/std1/logmnrts_3.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_06gradfv_1_1 tag=TAG20050805T100107 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 05-AUG-05
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/std1/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/std1/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/std1/indx01.dbf
restoring datafile 00004 to /opt/oracle/oradata/std1/tools01.dbf
restoring datafile 00005 to /opt/oracle/oradata/std1/users01.dbf
restoring datafile 00006 to /opt/oracle/oradata/std1/logmnrts.dbf
restoring datafile 00007 to /opt/oracle/oradata/std1/newlogminer.dbf
restoring datafile 00008 to /opt/oracle/oradata/std1/logmnrts_3.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_06gradfv_1_1 tag=TAG20050805T100107 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 05-AUG-05
–11.recover database
RMAN> recover database;
RMAN> recover database;
Starting recover at 05-AUG-05
using channel ORA_DISK_1
using channel ORA_DISK_1
starting media recovery
unable to find archive log
archive log thread=1 sequence=14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/05/2005 10:52:13
RMAN-06054: media recovery requesting unknown log: thread 1 scn 252823
archive log thread=1 sequence=14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/05/2005 10:52:13
RMAN-06054: media recovery requesting unknown log: thread 1 scn 252823
Machine A:
SQL> SELECT max(next_change#) FROM v$archived_log WHERE THREAD#=1 AND dest_id=1;
MAX(NEXT_CHANGE#)
—————–
252823
—————–
252823
SQL> SELECT SEQUENCE# FROM v$archived_log WHERE next_change#=252823 AND dest_id=1
2 ;
2 ;
SEQUENCE#
———-
13
———-
13
SQL> SELECT first_change#, archived, status FROM v$log WHERE THREAD#=1 ORDER BY First_change#;
FIRST_CHANGE# ARC STATUS
————- — —————-
249961 YES INACTIVE
250066 YES INACTIVE
252823 NO CURRENT
————- — —————-
249961 YES INACTIVE
250066 YES INACTIVE
252823 NO CURRENT
–that mean the change after 252823 is not archived, it’s lost in redo log,
— so we only can recove until 252823
— so we only can recove until 252823
RMAN> recover database until sequence 13 thread 1;
Starting recover at 05-AUG-05
using channel ORA_DISK_1
using channel ORA_DISK_1
starting media recovery
media recovery complete
media recovery complete
Finished recover at 05-AUG-05
–Appendix scenario:backup and recover the primary database with redo logs
–1.backup primary database
Machine C:
rman catalog=rcat/rcat@std1_31 target=sys/oracle@std1_29
–Appendix scenario:backup and recover the primary database with redo logs
–1.backup primary database
Machine C:
rman catalog=rcat/rcat@std1_31 target=sys/oracle@std1_29
Recovery Manager: Release 9.2.0.6.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: STD1 (DBID=3008965527)
connected to recovery catalog database
connected to recovery catalog database
RMAN> backup database;
Starting backup at 05-AUG-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=17 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=/opt/oracle/oradata/std1/system01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/std1/undotbs01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/std1/indx01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/std1/users01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/std1/logmnrts.dbf
input datafile fno=00007 name=/opt/oracle/oradata/std1/newlogminer.dbf
input datafile fno=00008 name=/opt/oracle/oradata/std1/logmnrts_3.dbf
input datafile fno=00004 name=/opt/oracle/oradata/std1/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 05-AUG-05
channel ORA_DISK_1: finished piece 1 at 05-AUG-05
piece handle=/opt/oracle/rman/std1_05grapd8_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:37
Finished backup at 05-AUG-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=17 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=/opt/oracle/oradata/std1/system01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/std1/undotbs01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/std1/indx01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/std1/users01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/std1/logmnrts.dbf
input datafile fno=00007 name=/opt/oracle/oradata/std1/newlogminer.dbf
input datafile fno=00008 name=/opt/oracle/oradata/std1/logmnrts_3.dbf
input datafile fno=00004 name=/opt/oracle/oradata/std1/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 05-AUG-05
channel ORA_DISK_1: finished piece 1 at 05-AUG-05
piece handle=/opt/oracle/rman/std1_05grapd8_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:37
Finished backup at 05-AUG-05
RMAN> exit
Machine A:
SQL> shutdown immediate
SQL> exit
SQL> shutdown immediate
SQL> exit
mv /opt/oracle/product/9.2.0/dbs/spfilestd1.ora /tmp/orabak/
mv /opt/oracle/oradata/physical_std1.ctl /tmp/orabak/
mv /opt/oracle/oradata/std1/*.dbf /tmp/orabak/
–remain the redo logs
ls -l /opt/oracle/oradata/std1/*
-rw-r—– 1 oracle dba 10486272 Aug 5 13:25 /opt/oracle/oradata/std1/redo01.log
-rw-r—– 1 oracle dba 10486272 Aug 5 13:13 /opt/oracle/oradata/std1/redo02.log
-rw-r—– 1 oracle dba 10486272 Aug 5 13:10 /opt/oracle/oradata/std1/redo03.log
mv /opt/oracle/oradata/physical_std1.ctl /tmp/orabak/
mv /opt/oracle/oradata/std1/*.dbf /tmp/orabak/
–remain the redo logs
ls -l /opt/oracle/oradata/std1/*
-rw-r—– 1 oracle dba 10486272 Aug 5 13:25 /opt/oracle/oradata/std1/redo01.log
-rw-r—– 1 oracle dba 10486272 Aug 5 13:13 /opt/oracle/oradata/std1/redo02.log
-rw-r—– 1 oracle dba 10486272 Aug 5 13:10 /opt/oracle/oradata/std1/redo03.log
Machine C:
rman catalog=rcat/rcat@std1_31 target=sys/oracle@std1_29
rman catalog=rcat/rcat@std1_31 target=sys/oracle@std1_29
Recovery Manager: Release 9.2.0.6.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
connected to recovery catalog database
RMAN> set dbid=3008965527
RMAN> startup nomount
RMAN> restore spfile;
Starting restore at 05-AUG-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=/opt/oracle/product/9.2.0/dbs/spfilestd1.ora
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_05grapd8_1_1 tag=TAG20050805T132104 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 05-AUG-05
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=/opt/oracle/product/9.2.0/dbs/spfilestd1.ora
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_05grapd8_1_1 tag=TAG20050805T132104 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 05-AUG-05
RMAN> restore controlfile;
Starting restore at 05-AUG-05
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring controlfile
output filename=/opt/oracle/product/9.2.0/dbs/cntrlstd1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_05grapd8_1_1 tag=TAG20050805T132104 params=NULL
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/opt/oracle/product/9.2.0/dbs/cntrlstd1.dbf
Finished restore at 05-AUG-05
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring controlfile
output filename=/opt/oracle/product/9.2.0/dbs/cntrlstd1.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_05grapd8_1_1 tag=TAG20050805T132104 params=NULL
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/opt/oracle/product/9.2.0/dbs/cntrlstd1.dbf
Finished restore at 05-AUG-05
RMAN> shutdown immediate
—
SQL> startup nomount
SQL> show parameter control_files
SQL> startup nomount
SQL> show parameter control_files
NAME TYPE VALUE
———————————— ———– ——————————
control_files string /opt/oracle/oradata/physical_s
td1.ctl
SQL> host
mv /opt/oracle/product/9.2.0/dbs/cntrlstd1.dbf /opt/oracle/oradata/physical_std1.ctl
exit
SQL> alter database mount;
———————————— ———– ——————————
control_files string /opt/oracle/oradata/physical_s
td1.ctl
SQL> host
mv /opt/oracle/product/9.2.0/dbs/cntrlstd1.dbf /opt/oracle/oradata/physical_std1.ctl
exit
SQL> alter database mount;
RMAN> restore database;
Starting restore at 05-AUG-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/std1/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/std1/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/std1/indx01.dbf
restoring datafile 00004 to /opt/oracle/oradata/std1/tools01.dbf
restoring datafile 00005 to /opt/oracle/oradata/std1/users01.dbf
restoring datafile 00006 to /opt/oracle/oradata/std1/logmnrts.dbf
restoring datafile 00007 to /opt/oracle/oradata/std1/newlogminer.dbf
restoring datafile 00008 to /opt/oracle/oradata/std1/logmnrts_3.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_05grapd8_1_1 tag=TAG20050805T132104 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 05-AUG-05
channel ORA_DISK_1: sid=14 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/std1/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/std1/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/std1/indx01.dbf
restoring datafile 00004 to /opt/oracle/oradata/std1/tools01.dbf
restoring datafile 00005 to /opt/oracle/oradata/std1/users01.dbf
restoring datafile 00006 to /opt/oracle/oradata/std1/logmnrts.dbf
restoring datafile 00007 to /opt/oracle/oradata/std1/newlogminer.dbf
restoring datafile 00008 to /opt/oracle/oradata/std1/logmnrts_3.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std1_05grapd8_1_1 tag=TAG20050805T132104 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 05-AUG-05
RMAN> recover database;
Starting recover at 05-AUG-05
using channel ORA_DISK_1
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 8 is already on disk as file /opt/oracle/oradata/std1/redo01.log
archive log filename=/opt/oracle/oradata/std1/redo01.log thread=1 sequence=8
media recovery complete
Finished recover at 05-AUG-05
archive log filename=/opt/oracle/oradata/std1/redo01.log thread=1 sequence=8
media recovery complete
Finished recover at 05-AUG-05
RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
QUESTION and ANSWERS
great article – this is the first article I have seen on how to use the standby database to recover the primary. thank you! I do have two questions: 1) is it possible to do this if you are not using a recovery catalog? If so, how would the steps differ? 2) why in step 7 isn’t the control file on the standby current. I would think that if you are using the standby to recover the primary that this would be the current control file. Most likely, your primary has probably been down for a while and datafiles could have been added to your standby. At least this is the situation I am in and I am trying to figure out how to restore the primary from the standby without shutting down the standby. Any advice would be greatly appreciated.
1)Yes, it’s ok without catalog. But you will need to catalog the backupfiles to the primary db control file.
In my example, use this
rman> catalog start with ‘/opt/oracle/rman/std1’;
2)The standby crontolfile is a standby controlfile on the point view of primary. That’s why rman says it is not a current controlfile. A current cotrolfile is always the primary db controlfile. After you active the standby db, its controlfile is current.
You do not need to stop the standby db to do recover. If you have questions, I’d like to help.
In my example, use this
rman> catalog start with ‘/opt/oracle/rman/std1’;
2)The standby crontolfile is a standby controlfile on the point view of primary. That’s why rman says it is not a current controlfile. A current cotrolfile is always the primary db controlfile. After you active the standby db, its controlfile is current.
You do not need to stop the standby db to do recover. If you have questions, I’d like to help.
At the end we open the primary database with resetlogs, what will be the status of standby database? do you rebuild the standby database. Incase of all control/dbf/redo files lost, best option would be to failover to standby database and rebuild the standby. as the downtime will be very less.
starting with 11g control file can be taken backup from standby database, rman doesn’t throw RMAN-06497: WARNING: controlfile is not current, controlfile autobackup skipped error.
starting with 11g control file can be taken backup from standby database, rman doesn’t throw RMAN-06497: WARNING: controlfile is not current, controlfile autobackup skipped error.
1. The standby database can sync with primary database automatically if you use dataguard. Dataguard will know and recover standby pass the resetlog point.
2. Agree. In case of primary fail, failover to standby is the standard solution to recover db. This post just provides another view.
2. Agree. In case of primary fail, failover to standby is the standard solution to recover db. This post just provides another view.
If I do not have primary controlfile backup, can we restore the standby controlfile backup to primary?
Also if db_unique_name is DBPRI on primary and dba_unique_name is DBSTBY on standby , is it not going to be an issue if we restore from standby backup as datafile headers may have db_unique_name details.
Also if db_unique_name is DBPRI on primary and dba_unique_name is DBSTBY on standby , is it not going to be an issue if we restore from standby backup as datafile headers may have db_unique_name details.
1. yes, you can restore the standby controlfile.
2. db_unique_name is not a problem. I don’t think it’s in datafile headers. Actually, you can change it in initial file. It’s at instance level.
2. db_unique_name is not a problem. I don’t think it’s in datafile headers. Actually, you can change it in initial file. It’s at instance level.
When backuping my standby database i also backup its controlfile (standby controlfile) …. but i can’t use it to restore on my primary database. i got the following error :
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-06023: no backup or copy of datafile 10 found to restore
are you sure i can use the backup of standby controlfile to restore my primary database? i yes how to do it? Thx again
yes. The error means you don’t have backup of datafile# 10. You’d better backup the controlfile and datafiles at the same time.
