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: Rampant TechPress author
Jeff Hunter has these great notes for
detecting gaps in your archived redo log for Data Guard. He notes:
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
28
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 28;
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
/u02/oraarchive/TESTDB/arch_t1_s28.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';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s28.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;
For more expert tips for troubleshooting Oracle Data
Guard, I recommend the great book "Oracle
Data Guard" by Bipul Kumar.
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
and include the URL for the page.
