📋Steps in this guide1/1

Resetlogs on Primary Database where Standby in place

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 … Continue reading Resetlogs on Primary where Standby in place →

oracle clusteringintermediate

by OracleDba

12 views

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. 1. Environment Primary Standby 2. Verify Sync On PRIMARY On Standby 3. Enable Flashback 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. 5. Verify Sync 6. Re-verify sync again after some time On Primary 7. Verify INCARNATION# 8. Verify Alert logfile 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/

Code/Command (click line numbers to comment):

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

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

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>

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>

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>

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>

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> a
lter system switch logfile;
System altered.

SQL> /

System altered.

SQL> /

System altered.

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                      6                     6          0
1                      6                     6          0

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                      6                    42        -36
SQL>

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>

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>

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>

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>

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
<----------

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!