Recover standby database after primary resetlogs

Generally, when the main library needs to be restored and opened by open resetlogs, the standby needs to be redo to construct the main and standby synchronization architecture. But in some cases, it is not necessary to spend a lot of time. Only the flash back function of the standby needs to be turned on, the main and standby synchronization process can be restored in a relatively short time.

(1)
After the main library resetlogs, query the RESETLOGS_CHANGE# (SCN) at that time. If it is smaller than the standby SCN, the standby can be repaired directly by flashing back to RESETLOGS_CHANGE# (SCN).
(two)
Main database status query

SQL>  select dbid,current_scn,RESETLOGS_CHANGE#,flashback_on from v$database;

      DBID CURRENT_SCN RESETLOGS_CHANGE# FLASHBACK_ON
---------- ----------- ----------------- ------------------
1520403684     2109683           2108651 YES

Standby status query

# Synchronization
SQL> select max(sequence#),applied,archived from v$archived_log group by applied,archived;

MAX(SEQUENCE#) APPLIED   ARC
-------------- --------- ---
             6 IN-MEMORY YES
             5 YES       YES

SQL>  select dbid,current_scn,RESETLOGS_CHANGE#,flashback_on from v$database;

      DBID CURRENT_SCN RESETLOGS_CHANGE# FLASHBACK_ON
---------- ----------- ----------------- ------------------
1520403684     2110296           2108651 YES

(three)
Main library

# Backup database
RMAN> backup database;

#Create a restore point
SQL> create restore point BEFORE_UPDATE;

#Perform update operations
SQL>  delete  from  baiyang.t where id = 99;

6 rows deleted.

SQL> commit;

# Query the current database status
SQL> select dbid,current_scn,RESETLOGS_CHANGE#,flashback_on from v$database;

      DBID CURRENT_SCN RESETLOGS_CHANGE# FLASHBACK_ON
---------- ----------- ----------------- ------------------
1520403684     2110440           2108651 YES

# close database
SQL> shutdown immediate

# Restore the database
SQL> startup mount  

RMAN> 
run {
restore database;
recover database until restore point BEFORE_UPDATE;
}

# Open the database
SQL> alter database open RESETLOGS;

# View database status
SQL> select dbid,current_scn,RESETLOGS_CHANGE#,flashback_on from v$database;

      DBID CURRENT_SCN RESETLOGS_CHANGE# FLASHBACK_ON
---------- ----------- ----------------- ------------------
1520403684     2110715           ***2110399*** YES

# Query table data
SQL> select count(*) from  baiyang.t where id = 99;

  COUNT(*)
----------
         6

After a series of suffocating operations, the deleted data from the main database table baiyang.t came back
(four)
Reserve library

#View the current standby status. SCN Greater than main library RESETLOGS_CHANGE#The backup synchronization can be restored by flashback

SQL> select dbid,current_scn,RESETLOGS_CHANGE#,flashback_on from v$database;

      DBID CURRENT_SCN RESETLOGS_CHANGE# FLASHBACK_ON
---------- ----------- ----------------- ------------------
1520403684     ***2110533***           2108651 YES

# The real-time application log process has stopped, and you can see that the MRP0 process has stopped in the alarm log.

SQL> select max(sequence#),applied,archived from v$archived_log group by applied,archived;

MAX(SEQUENCE#) APPLIED   ARC
-------------- --------- ---
             7 YES       YES
             2 NO        YES
          

(five)
Standby, now flash back to the database

SQL> shutdown immediate
SQL> startup mount

# Flashback Database
RMAN> flashback database until scn 1922219;
SQL> alter database open;

# Start real-time applications
SQL> select max(sequence#),applied,archived from v$archived_log group by applied,archived;

MAX(SEQUENCE#) APPLIED   ARC
-------------- --------- ---
             3 IN-MEMORY YES
             7 YES       YES

(six)
If the standby SCN is smaller than the main resetlogs, do you need to flash back?
Unwanted.
Consider a case where the standby has stopped synchronizing before the main library fails, then the application process of the standby log can be restarted directly after the main library restores and resetlogs.

Tags: Database SQL rman

Posted on Tue, 22 Jan 2019 11:51:13 -0500 by 9999