For one of the client, standby server went down. We had another standby server which was kept down for more than a month. Decision was taken to start the server and apply incremental SCN based backup on the standby database.
The standby was on ASM and the Primary on filesystem.Incremental backup was started from the SCN reported by below query
select min(fhscn) from x$kcvfh;
Once the backup completed, it was transferred to standby, standby was mounted (using the old controlfile), backups were cataloged and recovery performed using ‘recover database noredo’.
The recovery was going on, and was handed over to me. After the recovery completed I restored the latest controlfile from Primary and mounted the standby. At this point the controlfile was with the information of the filesystem as they were on the Primary side. The next step was to register everything we had on Standby side:
[oracle@oracle3:~ (db)]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 8 04:57:01 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ABCD (DBID=1463580380, not open) RMAN> catalog start with '+DATA/adbc_oracle3/DATAFILE/'; searching for all files that match the pattern +DATA/adbc_oracle3/DATAFILE/ List of Files Unknown to the Database ===================================== File Name: +data/adbc_oracle3/DATAFILE/TEST.256.844395985 File Name: +data/adbc_oracle3/DATAFILE/TEST.257.844397067 ................. ................. File Name: +data/adbc_oracle3/DATAFILE/TEST.416.865953683 Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: +data/adbc_oracle3/DATAFILE/TEST.256.844395985 File Name: +data/adbc_oracle3/DATAFILE/TEST.257.844397067 ................ ............... File Name: +data/adbc_oracle3/DATAFILE/TEST.416.865953683 RMAN> report schema; RMAN-06139: WARNING: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name ABCD_ORACLE3 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 0 SYSTEM *** /oradata/abcd_oracle2/datafile/o1_mf_system_825xkscr_.dbf 2 0 SYSAUX *** /oradata/abcd_oracle2/datafile/o1_mf_sysaux_825y451r_.dbf 3 0 TEST *** /oradata/abcd_oracle2/datafile/o1_mf_test_825s84mw_.dbf 4 0 TEST *** /oradata2/abcd_oracle2/datafile/o1_mf_test_8dr1v332_.dbf ................ ................ ................ 147 0 TEST *** /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hcrh_.dbf 148 0 TEST *** /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hdhf_.dbf 149 0 TEST *** /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hf6o_.dbf 150 0 TEST *** /oradata4/abcd_oracle2/datafile/o1_mf_test_b8k8hg1j_.dbf 151 0 TEST *** /oradata4/abcd_oracle2/datafile/o1_mf_test_bb318bhs_.dbf 152 0 TEST *** /oradata4/abcd_oracle2/datafile/o1_mf_test_bb318cff_.dbf 153 0 TEST_INDEX *** /oradata4/abcd_oracle2/datafile/o1_mf_test_index_bb318pmy_.dbf 154 0 TEST_NOLOGGING *** /oradata3/abcd_oracle2/datafile/o1_mf_test_nolog_bbm2s7vk_.dbf 155 0 TESTINDEX *** /oradata3/abcd_oracle2/datafile/o1_mf_test_index_bbm2z7nv_.dbf 156 0 PERFSTAT *** /oradata3/abcd_oracle2/datafile/o1_mf_perfstat_bbm312pf_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 3 15104 TEMP 32767 /oradata4/abcd_oracle2/datafile/o1_mf_temp_b633ppbr_.tmp 4 25600 TEMP 32767 /oradata4/abcd_oracle2/datafile/o1_mf_temp_b633ppcf_.tmp
After catalog completed, it was time to switch database to copy and it failed with below error
RMAN> switch database to copy; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of switch to copy command at 01/08/2015 05:00:25 RMAN-06571: datafile 151 does not have recoverable copy
After some analysis found, it was due to missing datafile on standby. The datafile was created on Primary after the standby was down and the recovery using the incremental backup was done with the older controlfile which had no information about the new datafiles.
Datafile # 151 – 156 were missing, so cataloged the backup pieces again as the controlfile was restored, and started restoring the datafile
RMAN> restore datafile 151; Starting restore at 08-JAN-15 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00151 to /oradata4/ABCD_ORACLE2/datafile/o1_mf_ct_bb318bhs_.dbf channel ORA_DISK_1: reading from backup piece +BACKUP/ABCD_ORACLE3/backupset/restore/incr_standby_5sps58rd_1_1 channel ORA_DISK_1: piece handle=+BACKUP/ABCD_ORACLE3/backupset/restore/incr_standby_5sps58rd_1_1 tag=TAG20150107T192825 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:19:05 Finished restore at 08-JAN-15 RMAN>
After the restoration completed, report schema showed
RMAN> report schema; RMAN-06139: WARNING: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name adbc_oracle3 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 0 SYSTEM *** /oradata/ABCD_ORACLE2/datafile/o1_mf_system_825xkscr_.dbf 2 0 SYSAUX *** /oradata/ABCD_ORACLE2/datafile/o1_mf_sysaux_825y451r_.dbf ................. 151 30720 TEST *** +DATA/adbc_oracle3/datafile/test.417.868424659 152 30720 TEST *** +DATA/adbc_oracle3/datafile/test.418.868424659 ................
Tried to perform “switch database to copy” which again failed with the same error “RMAN-06571: datafile 151 does not have recoverable copy” . At this point I though to use “switch datafile to copy” for which generated dynamic sql from primary and ran in to standby.
Generated switch command sql from Primary :- SQL> select 'switch datafile '||file#||' to copy;' from v$datafile;
[oracle@oracle3:~ (db)]$ vi swtch_copy.rman [oracle@oracle3:~ (db)]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 8 06:09:46 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ABCD (DBID=1463580380, not open) RMAN> @swtch_copy.rman RMAN> switch datafile 2 to copy; using target database control file instead of recovery catalog datafile 2 switched to datafile copy "+DATA/adbc_oracle3/datafile/sysaux.277.844418721" ............................ ........................... ........................... RMAN> switch datafile 149 to copy; datafile 149 switched to datafile copy "+DATA/adbc_oracle3/datafile/ct.415.865953681" RMAN> switch datafile 156 to copy; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of switch to copy command at 01/08/2015 06:28:54 RMAN-06571: datafile 156 does not have recoverable copy RMAN> RMAN> **end-of-file**
Performed ‘recover database noredo’ again and this time it was pretty quick and then tried recover standby database
[oracle@oracle3:~/working/anand (abcd)]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 8 06:31:38 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> recover standby database;
ORA-00279: change 38328244436 generated at 01/07/2015 19:28:32 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 38328244436 for thread 1 is in sequence #98501
Specify log: {=suggested | filename | AUTO | CANCEL}
^C
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/adbc_oracle3/datafile/system.357.844454911'
Did few changes in DGMGRL parameters and enabled standby configuration. After few hours, standby was insync with Primary. Stopped the MRP , dropped the standby
redo logfiles as they showed filesystem, and created on ASM. Opened the standby in read only mode and started the MRP.
