Missing logs, Out-of SYNC (Physical standby)

Auto Sync, some time auto sync not happens result of which standby lag behinds, some steps below can make standby to sync with primary:


1.

On standby check

1. Select process,status from v$managed_standby; and this query should return mrp as process, if mrp is not coming means recovery process Is not active.

2. Try recover managed standby database cancel; and again try recover managed standby database using current logfile disconnect from session.

At primary check :

3. select current_scn from v$database;

4. rman > backup incremental from scn scn_number database format ‘/tmp/forSTANDBY_%U’ tag FORSTANDBY;

At standby :

Do recovery at standby as :

5. Rman nocatalog target /

6. RMAN> catalog backuppiece ‘/tmp/forSTANDBY_%U’;

7. RMAN> recover database noredo;

8. RMAN> delete backup tag ‘FORSTANDBY’;

9. Alter database managed standby database using current logfile disconnect from session;

10. Select process,status from v$managed_standby;

11. Select sequence#,applied from v$archived_log;

12. Select max(sequence#) from v$log and match if it is same as of primary;





2. Copy controlfile from primary to standby :

At primary :

1. RMAN>backup current controlfile for standby;

or

2. RMAN> backup current controlfile for standby format ‘/tmp/controflile.ctl’;

At standby :

3. RMAN>restore standby controlfile from ‘/tmp/controlfile.ctl’;

4. Sql> shutdown immediate

5. SQL> startup nomount

6. SQL> alter database mount standby database;

7. SQL> recover managed standby database disconnect from session;

8. Check everything running fine : if yes then perfect otherwise :

9. SQL>select name from v$datafile ; (check all datafiles )

10. RMAN> set newname for datafile “datafile_name” to “standby_location”; or

11. RMAN> catalog start with '+diskgroup//datafile/';

12. SQL> alter database add standby logfile group 4 ‘+standby_location’ size 50M; (add same no of standby logfiles as of online redo log files).

13. SQL>shutdown immediate

14. SQL>startup nomount

15. SQL>alter database mount standby database;

16. RMAN>recover database ; (check incarnation and scn should be same as of primary)

17. SQL>recover managed standby database using current logfile disconnect from session;

18. Check mrp is running or not.

19. Check log sequence# on both databases.



3. Register logfiles:

1. Locate the missing log file using SQL>SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

2. Copy all the missing log files from primary archive destination to standby archive destination;

3. SQL>Alter database register logfile ‘log_file_path’;

4. Now check status of mrp process.

Max Protection vs Max Availability (Data Guard)

1. Whether log_archive_dest_2 is in lgwr,affirm or sync mode ,no matters about the above parameter , by default the protection mode would be max performance.


2. Max Availability will behave like max performance mode when no standby archive destinations available.
3. Max Availability will remain in max availability mode when standby redo logs will not available , in this case archiver process will come into picture.

4. Max protection mode will shutdown automatically if no standby destinations available for log transfer.for this we need atleast 2 log_archive_dest in order to aviod primary instance shutdown;

5. Max protection mode will shutdown if lgwr,async and noaffirm is used.

6. Max availability mode will behave like max performance mode if lgwr,async aand noaffirm is used , also it will use archiver (arch) process instead of lgwr.

7. Max availability mode will use archiver process for atleast gurantee the sync .

8. Max performance will use lgwr incase of lgwr,async,noaffirm instead of arch (in max availability ).