Logminer Not Finding Archive Logs When They Are Only Present In The Secondary Archive Destination. (Doc ID 1367643.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 22-Jan-2014***

Symptoms

This occurs when all the required archive logs reside in a secondary location instead of the primary archive log destination.

This worked in 10.2.0.3 and seems not to work any longer since 10.2.0.4 and later (eg. 11.2.0.2).

Example:
col name form a20
col value form a50
select name,value from v$spparameter
where name in ('log_archive_dest_1','log_archive_dest_2');

NAME                 VALUE
-------------------- ----------------------------------
log_archive_dest_1   LOCATION=/u03/archive1
log_archive_dest_2   LOCATION=/u03/archive2 <==


-- Checking log_archive_dest_1
$ ls -al /u03/archive1/*.arc
no files found

-- Checking log_archive_dest_2
$ ls -al /u03/archive2/*.arc

-rw-r--r-- 1 oracle oinstall 5670 May 29 10:33 TEST_1_80_741125345.arc
-rw-r--r-- 1 oracle oinstall 5670 May 29 10:52 TEST_1_81_741125345.arc
-rw-r--r-- 1 oracle oinstall 5670 May 29 11:21 TEST_1_80_741125345.arc



col name form a80
select FIRST_CHANGE#, NEXT_CHANGE#, name from v$archived_log;


FIRST_CHANGE# NEXT_CHANGE# NAME
------------- ------------ ---------------------------------------
960859        962668       /u03/archive2/TEST_1_80_741125345.arc
962668        962713       /u03/archive2/TEST_1_81_741125345.arc
962713        964171       /u03/archive2/TEST_1_82_741125345.arc


EXECUTE DBMS_LOGMNR.START_LOGMNR( -
                    STARTSCN => 960859, -
                    ENDSCN   => 964171, -
                    OPTIONS  => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + -
                    DBMS_LOGMNR.CONTINUOUS_MINE + -
                    DBMS_LOGMNR.DDL_DICT_TRACKING);

ERROR at line 1:
ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1

 

Changes

Upgrade to 10.2.0.4 or later (10.2.0.5, 11.1.x, 11.2.x).

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms