Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How to Detect Oracle dataguard redo log gaps ? and Solving

Question:  I have a Data Guard set-up and I seem to have some missing archived redo logs, with gaps in the log sequence.  I tried querying v$archive_gap, but I don't understand how to formally resolve gaps in my redo logs for Data Guard.

Answer: 
Archive redo log gaps are simply a range of archived redo logs that were created at a time when the standby database was not available to receive them.

 These archive redo log gaps occur most often during network outages where the standby database fails to receive the redo logs that are transported from the primary database. When the network connectivity is restored, Data Guard's automatic transmission of redo data from the primary to the standby database should resume.

Every 60 seconds the primary database does a "heartbeat" poll all of its Data Guard standby databases to check for gaps ion the transported archived redo logs.

We start detecting gaps in the redo logs by querying the v$archive_gap view:
SQL> select * from v$archive_gap;

THREAD#  LOW_SEQUENCE#  HIGH_SEQUENCE#
-------- -------------- --------------
1        24             27
From the output above, the physical standby database is currently missing logs from sequence 24 to sequence 28 for thread 1. Note that this view only returns the next gap that is currently blocking managed recovery from continuing.

After resolving the identified gap and starting managed recovery, the DBA should query the V$ARCHIVE_GAP view again on the physical standby database to determine the next (if any) gap sequence. This process should be repeated until there are no more gaps.

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:
SELECT name
FROM v$archived_log
WHERE thread# = 1
AND dest_id = 1
AND sequence# BETWEEN 24 and 27;

NAME
--------------------------------------
/u02/oraarchive/TESTDB/arch_t1_s24.dbf
/u02/oraarchive/TESTDB/arch_t1_s25.dbf
/u02/oraarchive/TESTDB/arch_t1_s26.dbf
/u02/oraarchive/TESTDB/arch_t1_s27.dbf
Next, we can 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:
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s24.dbf';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s25.dbf';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s26.dbf';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s27.dbf';
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:
SQL> alter database recover managed standby database disconnect from session;

Comments

Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

Ora2PG - Oracle/MySQL to Postgres DB migration Version 20.0

PostgreSQL Introduction