This situation was once occurred to me, that time I created a new Standby Database by dropping the last one. That time there was no time to test but then I test same situation on my test environment by creating somehow same situation.
So lets begin, to create that situation follow the procedure,
- To create a gap, shut down the standby database first so there won't be any connectivity between standby and primary. But before shutting down the standby get the current_scn value so that we can take an incremental backup of primary from that SCN.
[oracle@oracleasm2 ~]$ rlwrap sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 19 11:03:13 2016 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> select current_scn from v$database; CURRENT_SCN ----------- 208741 SQL> shut immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.
- Now that there is no way to our standby will be able to apply anything to recover. So now we just have to create a gap so that we can resolve that gap.
[oracle@oracleasm1 ~]$ . oraenv ORACLE_SID = [testdb01] ? testdb01 The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle [oracle@oracleasm1 ~]$ rlwrap sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 19 10:40:50 2016 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> select username,default_tablespace from dba_users; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ OUTLN SYSTEM SYS SYSTEM SYSTEM SYSTEM DIP SYSTEM ORACLE_OCM SYSTEM APPQOSSYS SYSAUX DBSNMP SYSAUX 7 rows selected. SQL> create user akg identified by akg; User created. SQL> create tablespace akg 2 datafile '/u01/app/oracle/oradata/testdb01/akg01.dbf' size 30M; Tablespace created. SQL> alter user akg default tablespace akg quota unlimited on akg; User altered. SQL> grant create table, create session to akg; Grant succeeded. SQL> conn akg/akg Connected. SQL> CREATE TABLE countries (country_id CHAR(2), country_name VARCHAR2(40), region_id NUMBER); Table created. SQL> BEGIN 2 INSERT INTO countries VALUES ('IT','Italy',1); 3 INSERT INTO countries VALUES ('JP','Japan',3); 4 INSERT INTO countries VALUES ('US','United States of America',2); 5 INSERT INTO countries VALUES ('CA','Canada',2); 6 INSERT INTO countries VALUES ('CN','China',3); 7 INSERT INTO countries VALUES ('IN','India',3); 8 INSERT INTO countries VALUES ('AU','Australia',3); 9 INSERT INTO countries VALUES ('ZW','Zimbabwe',4); 10 INSERT INTO countries VALUES ('SG','Singapore',3); 11 INSERT INTO countries VALUES ('UK','United Kingdom',1); 12 INSERT INTO countries VALUES ('FR','France',1); 13 INSERT INTO countries VALUES ('DE','Germany',1); 14 INSERT INTO countries VALUES ('ZM','Zambia',4); 15 INSERT INTO countries VALUES ('EG','Egypt',4); 16 INSERT INTO countries VALUES ('BR','Brazil',2); 17 INSERT INTO countries VALUES ('CH','Switzerland',1); 18 INSERT INTO countries VALUES ('NL','Netherlands',1); 19 INSERT INTO countries VALUES ('MX','Mexico',2); 20 INSERT INTO countries VALUES ('KW','Kuwait',4); 21 INSERT INTO countries VALUES ('IL','Israel',4); 22 INSERT INTO countries VALUES ('DK','Denmark',1); 23 INSERT INTO countries VALUES ('HK','HongKong',3); 24 INSERT INTO countries VALUES ('NG','Nigeria',4); 25 INSERT INTO countries VALUES ('AR','Argentina',2); 26 INSERT INTO countries VALUES ('BE','Belgium',1); 27 END; 28 / PL/SQL procedure successfully completed. SQL> select count(*) from countries; COUNT(*) ---------- 25 SQL> CREATE TABLE regions(region_id NUMBER,region_name VARCHAR2(25)); Table created. SQL> BEGIN 2 INSERT INTO regions VALUES (1,'Europe'); 3 INSERT INTO regions VALUES (2,'Americas'); 4 INSERT INTO regions VALUES (3,'Asia'); 5 INSERT INTO regions VALUES (4,'Middle East and Africa'); 6 END; 7 / PL/SQL procedure successfully completed. SQL> select count(9) from regions; COUNT(9) ---------- 4 SQL> commit; Commit complete. SQL> conn / as sysdba Connected. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> select current_scn, database_role from v$database; CURRENT_SCN DATABASE_ROLE ----------- ---------------- 211216 PRIMARY
- Now note that our primary database's current_scn is 211216, and 208741 on our standby side. Now take an incremental backup from the current_scn value on standby database along with current controlfile. After taking backup shutdown the primary database also. (to be on safer side so that recovery won't start automatically.) And get the backup piece no of our backed up controlfile.
[oracle@oracleasm1 ~]$ rlwrap rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 19 11:05:56 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> run 2> { 3> allocate channel 'back_incr_1' device type disk format '/home/oracle/Desktop/%u'; 4> backup incremental from scn 208741 database; 5> backup current controlfile for standby; 6> } using target database control file instead of recovery catalog allocated channel: back_incr_1 channel back_incr_1: SID=34 device type=DISK Starting backup at 19-APR-16 backup will be obsolete on date 26-APR-16 archived logs will not be kept or backed up channel back_incr_1: starting full datafile backup set channel back_incr_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/testdb01/sys01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/testdb01/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/testdb01/undotbs1.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/testdb01/akg01.dbf channel back_incr_1: starting piece 1 at 19-APR-16 channel back_incr_1: finished piece 1 at 19-APR-16 piece handle=/home/oracle/Desktop/09r3dv1s tag=TAG20160419T111300 comment=NONE channel back_incr_1: backup set complete, elapsed time: 00:00:26 backup will be obsolete on date 26-APR-16 archived logs will not be kept or backed up channel back_incr_1: starting full datafile backup set channel back_incr_1: specifying datafile(s) in backup set including current control file in backup set channel back_incr_1: starting piece 1 at 19-APR-16 channel back_incr_1: finished piece 1 at 19-APR-16 piece handle=/home/oracle/Desktop/0ar3dv2m tag=TAG20160419T111300 comment=NONE channel back_incr_1: backup set complete, elapsed time: 00:00:01 Finished backup at 19-APR-16 Starting backup at 19-APR-16 channel back_incr_1: starting full datafile backup set channel back_incr_1: specifying datafile(s) in backup set including standby control file in backup set channel back_incr_1: starting piece 1 at 19-APR-16 channel back_incr_1: finished piece 1 at 19-APR-16 piece handle=/home/oracle/Desktop/0br3dv2q tag=TAG20160419T111330 comment=NONE channel back_incr_1: backup set complete, elapsed time: 00:00:01 Finished backup at 19-APR-16 released channel: back_incr_1 RMAN> list backup of controlfile; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 6 Incr 7.52M DISK 00:00:02 19-APR-16 BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20160419T111300 Piece Name: /home/oracle/Desktop/0ar3dv2m Keep: NOLOGS Until: 26-APR-16 Control File Included: Ckp SCN: 211918 Ckp time: 19-APR-16 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 7 Full 7.52M DISK 00:00:01 19-APR-16 BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20160419T111330 Piece Name: /home/oracle/Desktop/0br3dv2q Standby Control File Included: Ckp SCN: 211935 Ckp time: 19-APR-16 RMAN> exit Recovery Manager complete.
- Now send the backup pieces to machine where our standby is.
[oracle@oracleasm1 ~]$ cd /home/oracle/Desktop/ [oracle@oracleasm1 Desktop]$ ls 09r3dv1s 0ar3dv2m 0br3dv2q [oracle@oracleasm1 Desktop]$ scp 09r3dv1s 0ar3dv2m 0br3dv2q 192.168.56.102:/home/oracle/Desktop/ oracle@192.168.56.102's password: 09r3dv1s 100% 5152KB 5.0MB/s 00:00 0ar3dv2m 100% 7712KB 7.5MB/s 00:01 0br3dv2q 100% 7712KB 7.5MB/s 00:00
- Now that we have increment backups with current controlfile, now log in as target from RMAN on standby machine and run the following run block. This block will,
- Start the standby database in nomount
- Restore the controlfile from backup
- Mount the database
- Catalog the backups
- Restore the datafile (its because we created a tablespace in primary, if you didn't create tablespace just skip the command from line no. 7)
- Recover the database.
[oracle@oracleasm2 ~]$ rlwrap rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 19 11:41:15 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> run 2> { 3> startup nomount 4> restore standby controlfile from '/home/oracle/Desktop/0br3dv2q'; 5> alter database mount; 6> catalog start with '/home/oracle/Desktop/' noprompt; 7> restore datafile 4; 8> recover database noredo; 9> } Oracle instance started Total System Global Area 523108352 bytes Fixed Size 1337632 bytes Variable Size 192939744 bytes Database Buffers 322961408 bytes Redo Buffers 5869568 bytes Starting restore at 19-APR-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/testdr01/control01.ctl Finished restore at 19-APR-16 database mounted released channel: ORA_DISK_1 Starting implicit crosscheck backup at 19-APR-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK Crosschecked 2 objects Finished implicit crosscheck backup at 19-APR-16 Starting implicit crosscheck copy at 19-APR-16 using channel ORA_DISK_1 Crosschecked 2 objects Finished implicit crosscheck copy at 19-APR-16 searching for all files in the recovery area cataloging files... no files cataloged searching for all files that match the pattern /home/oracle/Desktop/ List of Files Unknown to the Database ===================================== File Name: /home/oracle/Desktop/0br3dv2q File Name: /home/oracle/Desktop/unknown/autobackup/o1_mf_s_909410282_ck6l8nnz_.bkp File Name: /home/oracle/Desktop/unknown/o1_mf_nnndf_TAG20160417T135736_ck6l7rrh_.bkp cataloging files... cataloging done List of Cataloged Files ======================= File Name: /home/oracle/Desktop/0br3dv2q List of Files Which Where Not Cataloged ======================================= File Name: /home/oracle/Desktop/unknown/autobackup/o1_mf_s_909410282_ck6l8nnz_.bkp RMAN-07518: Reason: Foreign database file DBID: 1540500546 Database Name: DB01 File Name: /home/oracle/Desktop/unknown/o1_mf_nnndf_TAG20160417T135736_ck6l7rrh_.bkp RMAN-07518: Reason: Foreign database file DBID: 1540500546 Database Name: DB01 Starting restore at 19-APR-16 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 00004 to /u01/app/oracle/oradata/testdr01/akg01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/Desktop/09r3dv1s channel ORA_DISK_1: piece handle=/home/oracle/Desktop/09r3dv1s tag=TAG20160419T111300 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 19-APR-16 Starting recover at 19-APR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /u01/app/oracle/oradata/testdr01/sys01.dbf destination for restore of datafile 00002: /u01/app/oracle/oradata/testdr01/sysaux01.dbf destination for restore of datafile 00003: /u01/app/oracle/oradata/testdr01/undotbs1.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/Desktop/09r3dv1s channel ORA_DISK_1: piece handle=/home/oracle/Desktop/09r3dv1s tag=TAG20160419T111300 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished recover at 19-APR-16 RMAN> exit Recovery Manager complete.
Excellent bro. Thanq
ReplyDelete