Re: RMAN level 0 complains it can't find archivelogs

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Tue, 27 Mar 2007 20:11:30 +0100
Message-ID: <46096C62.1080301@dial.pipex.com>

Andy Kent wrote:

>>> I'm putting together an RMAN strategy on 10g under Linux, temporarily
>>> doing everything to disk while we prove the principles. I want to re-
>>> start a fresh cycle of backups with a brand new full backup (10.2 on
>>> Linux). I scheduled a full backup from EM checking the "Use as the
>>> base of an incremental backup strategy" box and it generated the
>>> following command:
>>> backup incremental level 0 cumulative device type disk tag ...
>>> The job failed because a week-old archive log is no longer on disk
>>> (we'd had several successful incrementals since then), which surprised
>>> me given the box I'd checked. What do I have to do differently so it
>>> will just start with a clean slate and not try to find archivelogs?
>>> Why does EM generate the keyword "cumulative"?  - it seems redundant
>>> in the context of a level 0... ?
>>> Thanks

> Is there some way that can be circumvented with CROSSCHECK / DELETE
> EXPIRED or similar, or is the reference to the logs outside the scope
> of RMAN? I've implemented a recovery catalog since the missing
> archivelogs were created, don't know if that makes a difference.
>
> To save me doing a lengthy manual trawl (I know, I know, but the first
> hits I got don't say much), can you tell me: is alter database open
> resetlogs part of a more complex procedure or do I simply issue that
> statement?
>
> Thanks
>
> Andy

I suspect Sybrand or I have got the wrong end of the stick. I read your post as saying, in effect.

I've used the EM interface to RMAN to generate a new incremental backup cycle, but the first backup failed because it couldn't backup an archive log necessary for recovery at the moment.

If this is correct, and the log has really gone forever - perhaps deleted by an os script, then yes you can rectify this in much the manner you suggest.

SQL> ;
1* select name from v$archived_log
SQL> / NAME

/usr/oracle/product/10.2.0/db_1/flash_recovery_area/NL102/archivelog/2006_11_07/o1_mf_1_2_2o20k6m0_.arc /usr/oracle/product/10.2.0/db_1/flash_recovery_area/NL102/archivelog/2007_01_03/o1_mf_1_3_2spn7d1d_.arc <snip output>
/usr/oracle/product/10.2.0/db_1/flash_recovery_area/NL102/archivelog/2007_03_27/o1_mf_1_42_30lojkqr_.arc

41 rows selected.

SQL> host rm -rf
/usr/oracle/product/10.2.0/db_1/flash_recovery_area/NL102/archivelog/*

SQL> exit

then the rman session

RMAN> backup incremental level 0 cumulative device type disk tag "test" database plus archivelog;

Starting backup at 27-MAR-07
current log archived
using target database control file instead of recovery catalog allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=637 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03002: failure of backup plus archivelog command at 03/27/2007 19:58:24 RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file
/usr/oracle/product/10.2.0/db_1/flash_recovery_area/NL102/archivelog/2006_11_07/o1_mf_1_2_2o20k6m0_.arc ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory Additional information: 3

RMAN RMAN> RUN {

2>  CROSSCHECK ARCHIVELOG ALL;
3>  DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
4> }

resolves the issue

the output from the above ends ...

archive log
filename=/usr/oracle/product/10.2.0/db_1/flash_recovery_area/NL102/archivelog/2007_03_27/o1_mf_1_42_30lojkqr_.arc recid=41 stamp=618345380
Deleted 41 EXPIRED objects

Then if we retry
RMAN> backup incremental level 0 cumulative device type disk tag "test" DATABASE plus archivelog;

Starting backup at 27-MAR-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=43 recid=42 stamp=618350302 input archive log thread=1 sequence=44 recid=43 stamp=618350677 channel ORA_DISK_1: starting piece 1 at 27-MAR-07 channel ORA_DISK_1: finished piece 1 at 27-MAR-07 piece
handle=/usr/oracle/product/10.2.0/db_1/flash_recovery_area/NL102/backupset/2007_03_27/o1_mf_annnn_TEST_30ltp79x_.bkp tag=TEST comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 Finished backup at 27-MAR-07

Starting backup at 27-MAR-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001
name=/usr/oracle/product/10.2.0/db_1/oradata/NL102/datafile/o1_mf_system_2o1ycwrq_.dbf input datafile fno=00003
name=/usr/oracle/product/10.2.0/db_1/oradata/NL102/datafile/o1_mf_sysaux_2o1ycww6_.dbf input datafile fno=00005
name=/usr/oracle/product/10.2.0/db_1/oradata/NL102/datafile/o1_mf_example_2o1yhwkx_.dbf input datafile fno=00002
name=/usr/oracle/product/10.2.0/db_1/oradata/NL102/datafile/o1_mf_undotbs1_2o1ycx4p_.dbf input datafile fno=00004
name=/usr/oracle/product/10.2.0/db_1/oradata/NL102/datafile/o1_mf_users_2o1ycx8z_.dbf channel ORA_DISK_1: starting piece 1 at 27-MAR-07 channel ORA_DISK_1: finished piece 1 at 27-MAR-07 piece
handle=/usr/oracle/product/10.2.0/db_1/flash_recovery_area/NL102/backupset/2007_03_27/o1_mf_nnnd0_TEST_30ltpfff_.bkp tag=TEST comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting incremental level 0 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 27-MAR-07 channel ORA_DISK_1: finished piece 1 at 27-MAR-07 piece
handle=/usr/oracle/product/10.2.0/db_1/flash_recovery_area/NL102/backupset/2007_03_27/o1_mf_ncsn0_TEST_30ltscrb_.bkp tag=TEST comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 27-MAR-07

Starting backup at 27-MAR-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=45 recid=44 stamp=618350781 channel ORA_DISK_1: starting piece 1 at 27-MAR-07 channel ORA_DISK_1: finished piece 1 at 27-MAR-07 piece
handle=/usr/oracle/product/10.2.0/db_1/flash_recovery_area/NL102/backupset/2007_03_27/o1_mf_annnn_TEST_30ltsghf_.bkp tag=TEST comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 27-MAR-07

RMAN> ALTER DATABASE OPEN RESETLOGS is something that you issue ONLY after incomplete recovery - not I think your situation. Indeed in current releases it won't work in the normal course of events and for good reason.

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 27 20:09:09 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 448790528 bytes

Fixed Size                  1219928 bytes
Variable Size             125829800 bytes
Database Buffers          318767104 bytes
Redo Buffers                2973696 bytes

Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info/services

Received on Tue Mar 27 2007 - 14:11:30 CDT