Resetlogs on Primary Database where Standby in place
Q: What will happen to standby database if primary database opened with resetlogs ???
Ans:
If Flashback database enabled on both primary and standby then no action required from DBA side. I have tested the same.
If FLASHBACK is NOT enabled on standby database and standby applied redo data past the new resetlogs SCN then recreate the standby database.
Table of Contents
___________________________________________________________________________________________________
1. Environment
2. Verify Sync
3. Enable Flashback
4. Simulate Resetlogs on PRIMARY
5. Verify Sync
6. Re-verify sync again after some time
7. Verify INCARNATION#
8. Verify Alert logfile
___________________________________________________________________________________________________
Primary
Platform : Linuxx86_64
Server Name : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version : Oracle 11.2.0.4
DB Name : DELL
DB_UNIQUE_NAME : DELL
Cluster : Non-RAC
Flashback : Enabled
DG Broker : Disabled
Standby
Platform : Linuxx86_64
Server Name : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version : Oracle 11.2.0.4
DB Name : DELL
DB_UNIQUE_NAME : DELL_DG
Cluster : Non-RAC
Flashback : Enabled
DG Broker : Disabled
On PRIMARY
SQL> select name, open_mode, database_role,db_unique_name from v$database; NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME --------- -------------------- ---------------- ------------------------------ DELL READ WRITE PRIMARY DELL SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 41 41 0 1 41 41 0 SQL>
On Standby
SQL> select name, open_mode, database_role,db_unique_name from v$database; NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME --------- -------------------- ---------------- ------------------------------ DELL READ ONLY PHYSICAL STANDBY DELL_DG SQL> SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 41 41 0 <---- SQL>
On PRIMARY
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES <----- SQL> show parameter flashback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 <--- SQL>
On Standby
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO <---- SQL> alter database flashback on; alter database flashback on * ERROR at line 1: ORA-01153: an incompatible media recovery is active SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES <----- SQL> show parameter flashback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 SQL> SQL> alter database recover managed standby database disconnect from session; Database altered. SQL>
4. Simulate Resetlogs on PRIMARY
DISCLAIMER: The open resetlogs command given below is just for learning purposes and should only be used on testing systems. I will not take any responsibility of any consequences or loss of data caused by this command.
SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 855642048 bytes Database Buffers 402653184 bytes Redo Buffers 8818688 bytes Database mounted. SQL> RECOVER DATABASE UNTIL CANCEL; Media recovery complete. SQL> alter database open resetlogs; Database altered. SQL> SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL>
On PRIMARY
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 6 6 0
1 6 6 0
SQL>
On Standby
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 6 42 -36
SQL>
6. Re-verify sync again after some time
On Primary
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 10 10 0 1 10 10 0 SQL> SQL> select count(*) from likki; COUNT(*) ---------- 0 SQL> insert into likki values (1); 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> commit; Commit complete. SQL> select count(*) from likki; COUNT(*) ---------- 3 <----- SQL> alter system switch logfile; System altered. SQL>
On Standby
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 10 10 0
SQL>
SQL> select count(*) from likki;
COUNT(*)
----------
0
SQL> select count(*) from likki;
COUNT(*)
----------
3 <----
SQL>
On Primary
SQL> select INCARNATION#,STATUS,FLASHBACK_DATABASE_ALLOWED from V$DATABASE_INCARNATION;
INCARNATION# STATUS FLASHBACK_DATABASE_ALLOWED
------------ ------- --------------------------
1 PARENT NO
2 PARENT YES
3 CURRENT YES <--------------------
SQL>
On Standby
SQL> select INCARNATION#,STATUS,FLASHBACK_DATABASE_ALLOWED from V$DATABASE_INCARNATION; INCARNATION# STATUS FLASHBACK_DATABASE_ALLOWED ------------ ------- -------------------------- 1 PARENT NO 2 PARENT YES 3 CURRENT YES <------ SQL>
On PRIMARY
Thu Dec 13 10:34:43 2018 ALTER DATABASE RECOVER DATABASE UNTIL CANCEL Media Recovery Start Serial Media Recovery started Media Recovery Not Required Completed: ALTER DATABASE RECOVER DATABASE UNTIL CANCEL Thu Dec 13 10:35:01 2018 alter database open resetlogs RESETLOGS after complete recovery through change 1023974 Archived Log entry 73 added for thread 1 sequence 43 ID 0xec56175a dest 1: Resetting resetlogs activation ID 3965065050 (0xec56175a) Thu Dec 13 10:35:02 2018 Setting recovery target incarnation to 3 <----------
On Standby
Thu Dec 13 09:55:12 2018 alter database flashback on Errors in file /u01/app/oracle/diag/rdbms/dell_dg/DELL_DG/trace/DELL_DG_ora_7762.trc: ORA-01153: an incompatible media recovery is active ORA-1153 signalled during: alter database flashback on... Thu Dec 13 09:55:28 2018 alter database recover managed standby database cancel Thu Dec 13 09:55:28 2018 MRP0: Background Media Recovery cancelled with status 16037 Errors in file /u01/app/oracle/diag/rdbms/dell_dg/DELL_DG/trace/DELL_DG_mrp0_8532.trc: ORA-16037: user requested cancel of managed recovery operation Recovery interrupted! MRP0: Background Media Recovery process shutdown (DELL_DG) Managed Standby Recovery Canceled (DELL_DG) Completed: alter database recover managed standby database cancel alter database flashback on Starting background process RVWR Thu Dec 13 09:55:32 2018 RVWR started with pid=24, OS id=11646 Allocated 4194304 bytes in shared pool for flashback generation buffer Flashback Database Enabled at SCN 1020556 Completed: alter database flashback on Thu Dec 13 10:35:04 2018 RFS[7]: Assigned to RFS process 13603 RFS[7]: New Archival REDO Branch: 994761301 Current: 994347858 RFS[7]: Selected log 6 for thread 1 sequence 1 dbid -329878705 branch 994761301 Thu Dec 13 10:35:04 2018 RFS[8]: Assigned to RFS process 13605 RFS[8]: Selected log 5 for thread 1 sequence 43 dbid -329878705 branch 994347858 A new recovery destination branch has been registered RFS[7]: Standby in the future of new recovery destinationBranch(resetlogs_id) 994761301 Incomplete Recovery SCN: 1020556 Resetlogs SCN: 1023975 Standby Became Primary SCN: 999031 Flashback database to SCN 999031 to follow new branch Flashback database to SCN 999031 to follow new branch RFS[7]: New Archival REDO Branch(resetlogs_id): 994761301 Prior: 994347858 RFS[7]: Archival Activation ID: 0xec5ca83b Current: 0xec56175a RFS[7]: Effect of primary database OPEN RESETLOGS RFS[7]: Incarnation entry added for Branch(resetlogs_id): 994761301 (DELL_DG) Thu Dec 13 10:35:04 2018 Setting recovery target incarnation to 3 <-------------- Thu Dec 13 10:35:04 2018 Archived Log entry 42 added for thread 1 sequence 1 ID 0xec5ca83b dest 1: Thu Dec 13 10:41:03 2018 RFS[9]: Assigned to RFS process 13660 RFS[9]: Opened log for thread 1 sequence 43 dbid -329878705 branch 994347858 Archived Log entry 43 added for thread 1 sequence 43 rlc 994347858 ID 0xec56175a dest 2: Thu Dec 13 10:41:03 2018 RFS[10]: Assigned to RFS process 13662 RFS[10]: Opened log for thread 1 sequence 2 dbid -329878705 branch 994761301 Archived Log entry 44 added for thread 1 sequence 2 rlc 994761301 ID 0xec5ca83b dest 2: RFS[9]: Selected log 6 for thread 1 sequence 3 dbid -329878705 branch 994761301 Thu Dec 13 10:41:05 2018 Primary database is in MAXIMUM PERFORMANCE mode RFS[11]: Assigned to RFS process 13667 RFS[11]: Selected log 7 for thread 1 sequence 4 dbid -329878705 branch 994761301 Thu Dec 13 10:41:05 2018 Archived Log entry 45 added for thread 1 sequence 3 ID 0xec5ca83b dest 1: Thu Dec 13 10:41:27 2018 Archived Log entry 46 added for thread 1 sequence 4 ID 0xec5ca83b dest 1: Thu Dec 13 10:41:28 2018 RFS[11]: Selected log 6 for thread 1 sequence 5 dbid -329878705 branch 994761301 Thu Dec 13 10:41:29 2018 Archived Log entry 47 added for thread 1 sequence 5 ID 0xec5ca83b dest 1: RFS[11]: Selected log 6 for thread 1 sequence 6 dbid -329878705 branch 994761301 Thu Dec 13 10:41:32 2018 Archived Log entry 48 added for thread 1 sequence 6 ID 0xec5ca83b dest 1: RFS[11]: Selected log 6 for thread 1 sequence 7 dbid -329878705 branch 994761301 Thu Dec 13 11:53:38 2018 alter database recover managed standby database disconnect from session Attempt to start background Managed Standby Recovery process (DELL_DG) Thu Dec 13 11:53:38 2018 MRP0 started with pid=29, OS id=16305 MRP0: Background Managed Standby Recovery process started (DELL_DG) Serial Media Recovery started Managed Standby Recovery not using Real Time Apply Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 1023974 Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Log /u02/oracle/archive/DELL_DG/1_43_994347858.dbf Completed: alter database recover managed standby database disconnect from session Media Recovery Log /u02/oracle/archive/DELL_DG/1_1_994761301.dbf Media Recovery Log /u02/oracle/archive/DELL_DG/1_2_994761301.dbf Media Recovery Log /u02/oracle/archive/DELL_DG/1_3_994761301.dbf Media Recovery Log /u02/oracle/archive/DELL_DG/1_4_994761301.dbf Media Recovery Log /u02/oracle/archive/DELL_DG/1_5_994761301.dbf Media Recovery Log /u02/oracle/archive/DELL_DG/1_6_994761301.dbf Media Recovery Waiting for thread 1 sequence 7 (in transit) alter database recover managed standby database disconnect from session Thu Dec 13 11:53:49 2018 ORA-1153 signalled during: alter database recover managed standby database disconnect from session... alter database recover managed standby database disconnect from session ORA-1153 signalled during: alter database recover managed standby database disconnect from session... Thu Dec 13 11:54:02 2018 Archived Log entry 49 added for thread 1 sequence 7 ID 0xec5ca83b dest 1: Thu Dec 13 11:54:02 2018 RFS[11]: Selected log 7 for thread 1 sequence 8 dbid -329878705 branch 994761301 Thu Dec 13 11:54:03 2018 alter database recover managed standby database disconnect from session Thu Dec 13 11:54:04 2018 Media Recovery Log /u02/oracle/archive/DELL_DG/1_7_994761301.dbf Media Recovery Waiting for thread 1 sequence 8 (in transit) Thu Dec 13 11:58:55 2018 Archived Log entry 50 added for thread 1 sequence 8 ID 0xec5ca83b dest 1: Thu Dec 13 11:58:55 2018 RFS[11]: Selected log 6 for thread 1 sequence 9 dbid -329878705 branch 994761301 Thu Dec 13 11:58:56 2018 Archived Log entry 51 added for thread 1 sequence 9 ID 0xec5ca83b dest 1: RFS[11]: Selected log 6 for thread 1 sequence 10 dbid -329878705 branch 994761301 Thu Dec 13 11:59:00 2018 Archived Log entry 52 added for thread 1 sequence 10 ID 0xec5ca83b dest 1: Thu Dec 13 11:59:00 2018 Media Recovery Log /u02/oracle/archive/DELL_DG/1_8_994761301.dbf RFS[11]: Selected log 6 for thread 1 sequence 11 dbid -329878705 branch 994761301 Media Recovery Log /u02/oracle/archive/DELL_DG/1_9_994761301.dbf Media Recovery Log /u02/oracle/archive/DELL_DG/1_10_994761301.dbf
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Reference:
Effect of doing PITR (point in time recovery) / Flashback on PRIMARY when Physical Standby in place ORA-19906 ORA-19909 ORA-01110 (Doc ID 824213.1)
http://oracleinaction.com/flashback-sby-after-resetlogs/
http://www.br8dba.com/failover-with-dg-broker-using-flashback-database/
http://www.br8dba.com/failover-with-no-dg-broker-using-flashback/
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com