In this Post, We will see How enabling Flashback helps us in case of Primary Database resetlogs.
Here I am simulating a scenario of Point in Time Recovery.
My standby database is in sync with Primary Database and I have restored an old backup in Primary Database and doing incomplete media recovery.
Now of course as this is incomplete media recovery I need to open my database with resetlogs.
Step 1: recovery with until sequence clause.
[oracle@test1 oradata]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jun 17 04:10:26 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTDB (DBID=2756866105, not open) RMAN> recover database until sequence 160; Starting recover at 17-JUN-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=26 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 17-JUN-18
Step 2: Open Database with a resetlogs option :
[oracle@test1 oradata]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 17 04:10:43 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- TESTDB MOUNTED PRIMARY SQL> alter database open resetlogs; Database altered. SQL>
Step 3: I am trying to start recovery on a standby database.
SQL> recover managed standby database disconnect; Media recovery complete.
Now see what alert log says when I applied recovery :
ALTER DATABASE RECOVER managed standby database disconnect Attempt to start background Managed Standby Recovery process (testdb) Wed Jun 20 09:49:48 2018 MRP0 started with pid=57, OS id=9993 MRP0: Background Managed Standby Recovery process started (testdb) Serial Media Recovery started Managed Standby Recovery not using Real Time Apply Warning: Recovery target destination is in a sibling branch of the controlfile checkpoint. Recovery will only recover changes to datafiles. Datafile 1 (ckpscn 1706451) is orphaned on incarnation#=2 MRP0: Detected orphaned datafiles! Recovery will possibly be retried after flashback... Errors in file /u01/oracle/diag/rdbms/std_testdb/testdb/trace/testdb_mrp0_9993.trc: ORA-19909: datafile 1 belongs to an orphan incarnation ORA-01110: data file 1: '/u01/oracle/oradata/testdb/system01.dbf' Wed Jun 20 09:50:13 2018 MRP0: Background Media Recovery process shutdown (testdb) Wed Jun 20 09:50:14 2018 Completed: ALTER DATABASE RECOVER managed standby database disconnect
Alert log says MRP detected orphaned datafiles !!
Now here flashback will help us.
Find SCN that is 2 number less than resetlogs_change#.
1.On PRIMARY :
SQL> SELECT TO_CHAR(RESETLOGS_CHANGE#-2) FROM V$DATABASE; TO_CHAR(RESETLOGS_CHANGE#-2) ---------------------------------------- 1705736
2. Query the V$DATABASE to check current scn number.
SQL> SELECT TO_CHAR(current_scn) FROM v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 1706450
We can see here standby SCN is greater than Primary SCN.
3. Flashback database to SCN we have determined in Step 1.
SQL> FLASHBACK STANDBY DATABASE TO SCN 1705736; Flashback complete.
ALERT LOG at time of flashback database .
FLASHBACK STANDBY DATABASE TO SCN 1705736 All dispatchers and shared servers shutdown CLOSE: killing server sessions. CLOSE: all sessions shutdown successfully. Wed Jun 20 09:54:04 2018 SMON: disabling cache recovery Flashback Restore Start Flashback Restore Complete Flashback Media Recovery Start Wed Jun 20 09:54:06 2018 Setting recovery target incarnation to 2 Serial Media Recovery started Flashback Media Recovery Log /u01/arc/testdb/stdby/1_149_973537980.arc Flashback Media Recovery Log /u01/arc/testdb/stdby/1_150_973537980.arc Flashback Media Recovery Log /u01/arc/testdb/stdby/1_151_973537980.arc Flashback Media Recovery Log /u01/arc/testdb/stdby/1_152_973537980.arc Flashback Media Recovery Log /u01/arc/testdb/stdby/1_153_973537980.arc Flashback Media Recovery Log /u01/arc/testdb/stdby/1_154_973537980.arc Flashback Media Recovery Log /u01/arc/testdb/stdby/1_155_973537980.arc Flashback Media Recovery Log /u01/arc/testdb/stdby/1_156_973537980.arc Incomplete Recovery applied until change 1705737 time 06/16/2018 11:57:58 Flashback Media Recovery Complete Setting recovery target incarnation to 3 Completed: FLASHBACK STANDBY DATABASE TO SCN 1705736
4. Now we can start recovery on standby.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; Database altered.
Stay tuned for More articles on Oracle DataGuard
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel : https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
