Home Dataguard Register missing archive logs on a standby database

Register missing archive logs on a standby database

Sometimes we may end up with archive logs missing on a standby database. It can happen due to a variety of reasons. Here we discuss some scenarios where archive logs are missing at a standby database site and how to register it manually.

Redo never made it to standby

In a Dataguard environment, when there is an interruption to redo transport, gaps occur. When the situation causing the interruption clears, the redo transport service automatically detects gaps and resolves it by sending the missing redo. But, what if the primary database becomes unavailable or if the network between the databases is down? What if we have to manually fix the gap?

In this case, v$archived_log on the standby database does not have an entry for the log sequence. And, the archive log file does not exist in the archive destination (obvious). There is a reason I’m making this point. Continue reading and this will make more sense.

Redo made it to standby

Oracle Database Fetch Archive Log (FAL) client and server mechanism helps in situations when archived redo log files have already been received on the standby database and then goes missing or gets corrupted. It is important to set parameters like FAL_SERVER and FAL_CLIENT in a Dataguard environment so the fetch happens automatically. But, what if the redo made it to standby, and the standby database archived the redo – and then the archive log file got deleted, overwritten or corrupted – and for some reason, the fetch did not help? Perhaps it is configured incorrectly.

In this case, v$archived_log has an entry for the log sequence. But, the archive log file is missing in the archive destination. What if the archive log file in this location is required by another service (eg: log mining)?

Register missing archive log

The cases above were made to support the point that despite all the inherent auto mechanisms available with the Oracle Database, we can still end up in a situation where archive logs (required) are missing on a standby database archive log location.

Oracle Database has a way to fix this situation. Let’s look at an example.

Archive log – Physical file

In this example, the standby database is missing the log for thread 2 and sequence number 138745.

rac2db01 | ORCLDG1 | /mnt/ORCLDG/archivelog
> ls -ltr |grep -i 13874
-rw-r----- 1 oracle dba 1060559872 Feb 29 19:10 2_138740_912612392.dbf
-rw-r----- 1 oracle dba 1033980416 Feb 29 19:10 2_138742_912612392.dbf
-rw-r----- 1 oracle dba 1058210816 Feb 29 19:10 2_138741_912612392.dbf
-rw-r----- 1 oracle dba 1043898880 Feb 29 19:10 2_138743_912612392.dbf
-rw-r----- 1 oracle dba  960729600 Feb 29 19:10 2_138744_912612392.dbf
-rw-r----- 1 oracle dba 1000466944 Feb 29 19:10 2_138746_912612392.dbf
-rw-r----- 1 oracle dba 1024834048 Feb 29 19:10 2_138748_912612392.dbf
-rw-r----- 1 oracle dba 1031783424 Feb 29 19:10 2_138747_912612392.dbf
-rw-r----- 1 oracle dba 1059768832 Feb 29 19:11 2_138749_912612392.dbf

v$archived_log

Let’s check the v$archived_log view to see if the standby database instance is aware of this file in this location.

SQL> set lines 200
SQL> col NAME for a70
SQL> select name, thread#, sequence# from v$archived_log where thread#=2 and sequence# like '13874%' and name like '/mnt/ORCLDG/archivelog/2_13874%' order by thread#, sequence#;

NAME									  THREAD#  SEQUENCE#
---------------------------------------------------------------------- ---------- ----------
/mnt/ORCLDG/archivelog/2_138740_912612392.dbf					2     138740
/mnt/ORCLDG/archivelog/2_138741_912612392.dbf					2     138741
/mnt/ORCLDG/archivelog/2_138742_912612392.dbf					2     138742
/mnt/ORCLDG/archivelog/2_138743_912612392.dbf					2     138743
/mnt/ORCLDG/archivelog/2_138744_912612392.dbf					2     138744
/mnt/ORCLDG/archivelog/2_138746_912612392.dbf					2     138746
/mnt/ORCLDG/archivelog/2_138747_912612392.dbf					2     138747
/mnt/ORCLDG/archivelog/2_138748_912612392.dbf					2     138748
/mnt/ORCLDG/archivelog/2_138749_912612392.dbf					2     138749

9 rows selected.

The log record is missing since the standby database did not archive the log in this location.

Copy log from primary

You can manually copy the log from primary to standby. Use your preferred method.

The example below shows how to copy the logs from ASM using RMAN.

RMAN> copy archivelog '+RECO/orcl/archivelog/2020_02_29/thread_2_seq_138745.13497.1033672215' to '/tmp/2_138745_912612392.dbf';

Starting backup at 02-MAR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=124 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=220 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=622 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=1355 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=1265 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=2066 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=1773 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=8877 instance=ORCL1 device type=DISK
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=138745 RECID=23680 STAMP=1033672217
output file name=/tmp/2_138745_912612392.dbf RECID=26342 STAMP=1034009512
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:15
Finished backup at 02-MAR-20

Starting Control File and SPFILE Autobackup at 02-MAR-20
piece handle=+RECO/orcl/autobackup/2020_03_02/s_1034009517.5233.1034009539 comment=NONE
Finished Control File and SPFILE Autobackup at 02-MAR-20

RMAN> exit

I then used ‘scp’ to copy the file to the standby server, standby destination.

Register the copied logfile

SQL> ALTER DATABASE REGISTER LOGFILE '/mnt/ORCLDG/archivelog/2_138745_912612392.dbf';

Database altered.

Registered log

Verify the registered archive log record in v$archived_log. Now the standby database control file knows and has access to the missing archive log.

SQL> set lines 200
SQL> col NAME for a70
SQL> select name, thread#, sequence# from v$archived_log where thread#=2 and sequence# like '13874%' and name like '/mnt/ORCLDG/archivelog/2_13874%' order by thread#, sequence#;

NAME									  THREAD#  SEQUENCE#
---------------------------------------------------------------------- ---------- ----------
/mnt/ORCLDG/archivelog/2_138740_912612392.dbf					2     138740
/mnt/ORCLDG/archivelog/2_138741_912612392.dbf					2     138741
/mnt/ORCLDG/archivelog/2_138742_912612392.dbf					2     138742
/mnt/ORCLDG/archivelog/2_138743_912612392.dbf					2     138743
/mnt/ORCLDG/archivelog/2_138744_912612392.dbf					2     138744
/mnt/ORCLDG/archivelog/2_138745_912612392.dbf					2     138745
/mnt/ORCLDG/archivelog/2_138746_912612392.dbf					2     138746
/mnt/ORCLDG/archivelog/2_138747_912612392.dbf					2     138747
/mnt/ORCLDG/archivelog/2_138748_912612392.dbf					2     138748
/mnt/ORCLDG/archivelog/2_138749_912612392.dbf					2     138749

10 rows selected.

What if the control file already has a record for the log sequence you are trying to register? We will get an error like below.

SQL> ALTER DATABASE REGISTER LOGFILE '/mnt/ORCLDG/archivelog/2_138745_912612392.dbf';

Error report -
ORA-16089: archive log has already been registered
16089. 0000 -  "archive log has already been registered"
*Cause:    An attempt was made to register an archive log that already has
           a corresponding thread# and sequence# entry in the standby database
           control file.  Duplicate information is not permitted.
*Action:   Use the V$ARCHIVED_LOG fixed view to verify the archive log
           information.

In this situation, you can use the REPLACE option along with REGISTER.

SQL> ALTER DATABASE REGISTER OR REPLACE LOGFILE '/mnt/ORCLDG/archivelog/2_138745_912612392.dbf';

Database altered.

Hope this helps!

Also, read – Parameters like LOG_ARCHIVE_MIN_SUCCEED_DEST can be used along with the LOG_ARCHIVE_DEST_n parameter with the MANDATORY and other options to control the behavior of archiving.

Have you encountered a similar or different situation where you had to do a manual registration? Leave a comment so we can understand these situations and solutions better.

References:

Like this article? Here are some more Dataguard posts that may interest you! Thank you for reading.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest posts

Steps to remove OEM Agent

Recently I migrated all targets (Cluster, Databases, Instances, ASM, etc) and their jobs to a 13c OEM...

DBCA fails with ORA-27125 on 19c

Issue I recently encountered an issue where DBCA failed with ORA-27125 when creating a...

How to rename a PDB in 19c

Let's look at how to rename a PDB in 19c. The steps are the same as 12c....

How to change the ethernet switch password on Exadata

Let's look at how to log in and change the ethernet switch (adm) password on an Exadata...

You might also likeRELATED
Recommended to you