HOW TO CHECK WHETHER PHYSICAL STANDBY IS IN SYNC WITH THE PRIMARY OR NOT AND RESOLVED GAP ?

Updated: Feb 9

Step by Step Process to Resolve gap on the Standby database.

Summary 1. Check the name and status of database. 2. Check for GAP on standby 3. Check redo received on standby 4. Check redo applied on standby 5. Identify missing archive log files 6. Copy archive log files 7. Register archive log files with standby 8. Restart the managed recovery operations

*********************************************************************************************************

Step 1: Check the status of the database on both servers. On Primary Server.

SQL> select name, open_mode, database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ——— ———- —————- MYDB READ WRITE PRIMARY

SQL> set sqlprompt “

PRIMARY’@’_connect_identifier

>”

PRIMARY@MYDB

>

On Standby Server.

SQL> select name, open_mode, database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ——— ———- —————- MYDB MOUNTED PHYSICAL STANDBY

SQL> set sqlprompt “

SECONDARY’@’_connect_identifier

>”

STANDBY@MYDB

>

Step 2: Check for GAP on Standby

PRIMARY@MYDB>select

max(sequence#) from v$log_history;

MAX(SEQUENCE#) ————– 76921

STANDBY@MYDB>select

max(sequence#) from v$log_history; MAX(SEQUENCE#) ————– 76921

STANDBY@MYDB>SELECT

THREAD# “Thread”,SEQUENCE# “Last Sequence Generated” FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#) ORDER BY 1; Thread Last Sequence Generated ———- ———————– 1 76921

Step 3 & 4: Check redo received and applied on the standby.

STANDBY@MYDB

> 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 76922 20931 55991

Step 5: Identify the missing archive log file.

STANDBY@MYDB>SELECT

THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; no rows selected

—-If a found gap

Step 6: Copy missing archive log file After identifying a gap (as shown above), the DBA will need to query the primary database to locate the archived redo logs on the primary database. The following query assumes the local archive destination on the primary database is LOG_ARCHIVE_DEST_1:

PRIMARY@MYDB

> SELECT name FROM v$archived_log WHERE thread# = 1 AND dest_id = 1 AND sequence# BETWEEN 20931 and 76922; Output: /oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc 56027 rows selected.

Step 7: Register archive logfile with standby. Copy the above redo log files to the physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE … SQL statement on the physical standby database.

For example:

STANDBY@MYDB

> ALTER DATABASE REGISTER LOGFILE ‘/oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc’;

Step 8: Restart the managed recovery operations. — After the redo logs have been registered on the physical standby database, the DBA can restart the managed recovery operations. For example, to put the physical standby database into automatic recovery managed mode:

STANDBY@MYDB

> alter database recover managed standby database disconnect from session;

Join our Telegram group for a free session for new features and upcoming webinar.

(

https://t.me/dbahelp

)

0 views

ADDRESS :

LOCATION :

701, Linden, Rosalie Complex Khadakpada, Kalyan (W) Mumbai, Maharashtra 421301)

CONTACT US :

+919322846017  &   +919867493019

EMAIL: 

QUICK LINKS :

SUBSCRIBE HERE :

Don’t Miss a Lecture. Subscribe Today. 

telegram-512.png

© 2019 DBA Trainings. Proudly Managed By Pearl Organisation

  • Grey LinkedIn Icon
  • Grey YouTube Icon
  • Grey Twitter Icon
  • Grey Facebook Icon