Search This Blog

Thursday, November 23, 2006

RMAN-06054: media recovery requesting unknown log: thread x seq xxx lowscn xxxxxx


SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode
Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25

**********************************************************
SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database
Password:
connected to target database: ORCL (DBID=1134976990)

RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\SNCFORCL.ORA'; # default

****************************
RMAN> configure controlfile autobackup on;new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

*************************************

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> backup database plus archivelog;
5> }

allocated channel: c1channel c1: sid=200 devtype=DISKStarting backup at 23-NOV-06current log archivedStarting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=25 recid=1 stamp=607282901channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174141_2PC98Y2P_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:11
Finished backup at 23-NOV-06Starting backup at 23-NOV-06channel c1: starting full datafile backupsetchannel c1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:01:33Finished backup at 23-NOV-06Starting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=26 recid=2 stamp=607283007channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174328_2PC9D278_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:02Finished backup at 23-NOV-06Starting Control File and SPFILE Autobackup at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 23-NOV-06released channel: c1

********************************************************
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.dbf
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.ctl
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.log

**********************************************************
C:\>sqlplus "sys as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Nov 23 17:47:56 2006Copyright (c) 1982, 2004,
Oracle. All rights reserved.
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 171966464
bytesFixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes

ORA-00205: error in identifying controlfile, check alert log for more info

*************************************

SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database Password:connected to target database: orcl (not mounted)

**********************************************

RMAN> restore controlfile from autobackup;
Starting restore at 23-NOV-06using channel ORA_DISK_1recovery area destination: D:\oracle\product\10.1.0\flash_recovery_areadatabase name (or lock name space) used for search: ORCLchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPchannel ORA_DISK_1: controlfile restore from autobackup completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 23-NOV-06

****************************************

RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1

****************************************

RMAN> run
2> {
3> restore database;
4> recover database;
5> }

Starting restore at 23-NOV-06Starting implicit crosscheck backup at 23-NOV-06allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=214 devtype=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 23-NOV-06Starting implicit crosscheck copy at 23-NOV-06using channel ORA_DISK_1Finished implicit crosscheck copy at 23-NOV-06searching for all files in the recovery areacataloging files...cataloging done

List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPusing channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26unable to find archive logarchive log thread=1 sequence=27RMAN-00571: ===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/23/2006 17:53:44RMAN-06054: media recovery requesting unknown log: thread 1 seq 27 lowscn 434810

**************************************

Note : RMAN-06054: media recovery requesting unknown log: thread string seq string lowscn string Cause: Media recovery is requesting a log whose existance is not recorded in the recovery catalog or control file.Action: If a copy of the log is available, then add it to the recovery catalog and/or control file via a CATALOG command and then retry the RECOVER command. If not, then a point in time recovery up to the missing log is the only alternative and database can be opened using ALTER DATABASE OPEN RESETLOGS command.

******************************************************

RMAN> run
2> {
3> SET UNTIL SEQUENCE 27 THREAD 1;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> ALTER DATABASE OPEN RESETLOGS;
7> }

executing command: SET until clauseStarting restore at 23-NOV-06using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26media recovery completeFinished recover at 23-NOV-06database opened

*************************************

RMAN> QUIT

Recovery Manager complete.

SQL> select status from v$instance;
STATUS
------------
OPEN

SQL> archive log list
Database log mode Archive Mode
Automatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1

SQL> --take complete consistent bkp of ur database.

5 comments:

Anonymous said...

ASAK Mr Taj,
Your materail is really very helpful to everybody. Allaah bless you.

Jazak Allaah Khair

Mohammed Taj said...

Walekum us Salam,

Thank You!!!

Regards
Taj

Debjit Sengupta said...

Nice one Taj! It made an interesting reading. Keep up yr good work. I still got some queries related to the subject.

Anonymous said...

hi, new to the site, thanks.

Mohammed Taj said...

Thank you everyone


Taj