Standby site complains for missing Archivelog even if the file is present

Problem Description

The Article explains the scenario where the standby complains that the Archives are missing, but the Archives are present in the standby location.

The version used below is 6.0 , we will also the modifications and related parameters to version 7.0 as well.

Dbvisit Standby Database Technology message from unix2db1dr
Message received from process: dbvisit ORAAPP
(Dbvisit Standby: 6.0.40.9840 Process id: 3670)
201301071301 - Unable to find log file after 13 times. MAX_TIMES_TRIED (4) exceeded.
Log sequence 14983 thread 1 still not on standby site.
Dbvisit Standby terminated.
Return code = 409

The archive file was present at the standby server and the permissions are and were OK. Each time when we tried to sync the standby by with dbvisit <DDC> it says a file was missing. We also notice that there was this arch_not_there.lck file. we removed it and started dbvisit again, with same outcome.

Solution

The issue here was that the log_archive_format in the standby database was NOT the same as for the primary.

So, you need to ensure that the log_archive_format Oracle parameter is the same on both the primary and standby databases. Ensure that the log_archive_format Oracle parameter is the same on both the primary and standby databases:

SQL> show parameter log_archive_format
NAME TYPE VALUE


log_archive_format string %t_%s_%R.log

Dbvisit recognises the following variables in the archive log format:

%S, %s, %T, %t, %R, %r, %d

The following variables can be used in the format:

%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread number, zero filled
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
%R resetlogs ID, zero filled
%d Database ID

Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros. An example of specifying the archive redo log filename format follows:

LOG_ARCHIVE_FORMAT = 'log%t_%s_%r.arc'

Changing the `log_archive_format`:

SQL> alter system set log_archive_format = '%t_%s_%R.log' scope=both;
alter system set log_archive_format = '%t_%s_%R.log' scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set log_archive_format = '%t_%s_%R.log' scope=spfile;
System altered.

Restart the database for it to take affect.

Ensure the log_archive_format is the same on both the primary AND standby database.

From Version 7.0.40 we have a improvement with respect to the archivelog format , below are the changes.

This is the format Dbvisit Standby will be using on the standby server inside the ARCHDEST location. Default format is "<thread>_<sequence>_<resetlogs_id>.arc" which is %t_%s_%r.arc. If required the new parameter STANDBY_ARCHIVELOG_NAME can be set in the DDC.

Example :

If you want to set the format so that the database name PROD is in the archive log format name, you can set the following in the DDC file where you append the database name as part of the format: STANDBY_ARCHIVELOG_NAME=PROD_%t_%s_%r.arc For Oracle 10g and above %t, %s and %r must be set, and if Oracle 9i is used %t and %s must be set

Mike Donovan July 05, 2013 14:45

We're here to help!

Changing the `log_archive_format`:

Comments