In versions prior to 12c if one of the standby database goes out of sync with respect to the primary database we have to perform the below steps in order to make the standby in sync with primary.
• Check the current SCN of the standby database from where recovery needs to be done.• Take incremental backup of primary database from the SCN obtained from the standby database.• Copy the backup from primary to the standby and catalog the backups.• Cancel the MRP of standby database and apply the backup from the primary• Create a new control file for the standby database from the primary and mount the standby using the new control file.
• Start the managed recovery of the standby database
From 12c, the method remains the same, but it has been made easier. We can RECOVER DATABASE USING SERVICE NAME to sync the standby database with the primary.The command does the same as described above. In the background it creates incremental backups containing the changes in the primary database. All the changes done to the datafile on the primary database are included in the incremental backup starting with the SCN in the standby from where we need recovery and then the backup will be transferred over the network to the standby location and the incremental backup will be applied to the standby database.Let's say below are the primary and standby database details:Primary :
DB_UNIQUE_NAME=ORCL
Service name can be selected by giving the below query.
NAME TYPE VALUE------------------ ------------ --------------service_names string ORCL
Standby :DB_UNIQUE_NAME=ORCLSBY
NAME TYPE VALUE------------------ ------------ --------------service_names string ORCLSBY
There are few prerequisites that needs to be checked before we start the activity.
• There should be Oracle net connectivity between standby and primary which can be done by adding the TNS entry in the tnsnames.ora file of the standby database.
• This only work for oracle 12c or higher version of the database so the COMPATIBLE parameter should be set to 12 or the higher version.
• Check the space on the standby database filesystem. Make sure we have enough space on the server.Place the standby database in mount state.
SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 1.52875E+10 bytesFixed Size 8796438 bytesVariable Size 4630514240 bytesDatabase Buffers 9193914368 bytesRedo Buffers 104153088 bytesDatabase mounted.
Check if Managed Recovery Process(MRP0) is running on the standby.
select recovery_mode from v$archive_dest_status where recovery_mode like 'MANAGED%';Cancel Managed Recovery Process on standby.
recover managed standby database cancel;Check SCN of the Standby database. This will be used in later steps.
Set numwidth 50
SQL> select current_scn from v$database;
CURRENT_SCN
--------------
10301523591238
Connect to RMAN on standby database.
rman target /Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 13 07:57:54 2021Version 19.9.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCLSBY(DBID=341160335, not open)
Execute below command to recover standby database.
RMAN> recover database from service ORCL noredo using compressed backupset;
At this point the standby will be in sync with primary but the control file of the standby database contains the old values of SCN. To complete the synchronization, we need to restore the control file from primary database.
RMAN> shutdown immediate;database dismountedOracle instance shut downRMAN> startup nomount;connected to target database (not started)Oracle instance startedTotal System Global Area 34327068776 bytesFixed Size 30150760 bytesVariable Size 9193914368 bytesDatabase Buffers 24964497408 bytesRedo Buffers 138506240 bytesRMAN> restore standby controlfile from service ORCL;RMAN> alter database mount;released channel: ORA_DISK_1Statement processedRMAN> report schema;
The REPORT SCHEMA command lists and displays information about the database files.
If the directory structure of the primary and standby database is different you will get the below alert while performing the above steps.RMAN-06139: WARNING: control file is not current for REPORT SCHEMAReport of database schema for database with db_unique_name ORCLSBYPerform the below steps if you see above error:
RMAN> Catalog start with '<DATAFILE LOCATION ON PRIMARY>';RMAN> SWITCH DATABASE TO COPY;RMAN> RECOVER DATABASE;
If we try to open the database now and start the managed process, we might get the below alert in the alert.logRFS[4]: No standby redo logfiles available for thread 1To fix this we need to drop the standby redo logs and recreate them.
Check Current Redo Logs
SQL> select thread#,group#,sequence#,status, bytes/1024/1024 mb from v$standby_log;THREAD# GROUP# SEQUENCE# STATUS MB---------- ------ ---------- ---------- ------1 4 0 UNASSIGNED 10241 5 734869 ACTIVE 10241 6 0 UNASSIGNED 1024SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------<Location>/redo04.log<Location>/redo05.log<Location>/redo06.log3 rows selected.
Add new Logfile Groups
SQL> alter database add logfile group 7 ('<LOCATION>/redo07.log') size 1g, group 8 ('<LOCATION>/redo08.log') size 1g, group 9 ('<LOCATION>/redo09.log') size 1g;Database altered.SQL> select thread#,group#,sequence#,status, bytes/1024/1024 mb from v$standby_log;THREAD# GROUP# SEQUENCE# STATUS MB---------- ------ ---------- ---------- ------1 4 0 UNASSIGNED 10241 5 734869 ACTIVE 10241 6 0 UNASSIGNED 10241 7 0 UNASSIGNED 10241 8 0 UNASSIGNED 10241 9 0 UNASSIGNED 10246 rows selected.
Switch Logfile to New GroupsMake some redo log switching until you see Log Writer Process (LGWR) is working on the new redo.
SQL> alter system switch logfile;System altered.SQL> /System altered.SQL> /System altered.SQL> select group#, status, bytes/1024/1024 mb from v$log;GROUP# STATUS MB---------- ---------------- ------------------4 INACTIVE 10245 INACTIVE 10246 INACTIVE 10247 CURRENT 10248 INACTIVE 10249 INACTIVE 10246 rows selected.
Force a CheckPoint and drop old logfile groups. An ACTIVE or CURRENT redo log group cannot be dropped.
SQL> alter system checkpoint;System altered.SQL> select group#, status, bytes/1024/1024 mb from v$log;GROUP# STATUS MB---------- ---------------- -------------------4 INACTIVE 10245 INACTIVE 10246 INACTIVE 10247 CURRENT 10248 INACTIVE 10249 INACTIVE 10246 rows selected.
Drop Group 4,5,6
alter database drop logfile group 4, group 5, group 6;
Remove Redo Log FilesBe careful, don't remove online redo logs accidentally.
$ ls -lrttotal 3299364-rw-r----- 1 oracle dba 523 Mar 30 18:22 redo04.log-rw-r----- 1 oracle dba 532 Mar 30 18:22 redo05.log-rw-r----- 1 oracle dba 524 Mar 30 18:23 redo06.log-rw-r----- 1 oracle dba 524 Mar 30 18:23 redo07.log-rw-r----- 1 oracle dba 524 Mar 30 18:23 redo08.log-rw-r----- 1 oracle dba 106 Mar 30 18:25 redo09.log$ rm redo04.log redo05.log redo06.logYou have new mail in /var/spool/mail/oracle$ ls -lrttotal 3145752-rw-r----- 1 oracle dba 524 Mar 30 18:23 redo07.log-rw-r----- 1 oracle dba 524 Mar 30 18:23 redo08.log-rw-r----- 1 oracle dba 106 Mar 30 18:25 redo09.log
Open the standby database in read only mode and start the recovery managed process.
SQL> ALTER DATABASE OPEN READ ONLY;SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Check sync status of standby database with primary.
SELECT floor(((sysdate-max(NEXT_TIME))*24*60*60)/3600)|| ' Hour(s) ' ||floor((((sysdate-max(NEXT_TIME))*24*60*60) -floor(((sysdate-max(NEXT_TIME))*24*60*60)/3600)*3600)/60) || ' Minute(s) ' ||round((((sysdate-max(NEXT_TIME))*24*60*60) -floor(((sysdate-max(NEXT_TIME))*24*60*60)/3600)*3600 -(floor((((sysdate-max(NEXT_TIME))*24*60*60) -floor(((sysdate-max(NEXT_TIME))*24*60*60)/3600)*3600)/60)*60) ))|| ' Second(s) '"Standby ARCHIVELOG GAP/LAG" from gv$archived_log where applied ='YES';
Comments
Post a Comment