In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Tuesday, August 31, 2010

ORA-00283: ORA-00314: ORA-00312: Error and solution

While trying to restore controlfile from backup , while recovery i got the error

ORA-00283: recovery session canceled due to errors
ORA-00314: log 2 of thread 1, expected sequence# 2 doesn't match 11
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jay/redo02.log'

Reason:

In any case, the ArchiveLog must be backed up. Else, a RESTORE alone cannot do a RECOVER.

If your database backup did not include the ArchiveLogs, then the backup you created does not have the Redo information that Oracle must apply to the Database Backup. That is why you got the "unknown log'.
Also, if the controlfile backup is before the archivelog backup, the controlfile, even when restored, is not aware of the archivelogs in the backup created subsequent to it.
RMAN can still do a RECOVER , implicitly using the "BACKUP CONTROLFILE" and doing a rollforward but it needs to have to restore the ArchiveLog first -- and the information about which Backupset contains the ArchiveLog is not available to it. You would need to CATALOG the ArchiveLog BackupSet and then restore the archivelogs from there.
(If you use an RMAN Recovery Catalog database, then of course, the Catalog has information about the ArchiveLogs and the BackupSets containing the ArchiveLogs so RMAN queries the Catalog to identify the BackupSets and extracts the necessary ArchiveLogs from the Backupsets).

Solution:
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13
SQL> ! rm -rf /u01/app/oracle/oradata/jay/control*

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             188744384 bytes
Database Buffers          251658240 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info


[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 31 11:44:44 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: jay (not mounted)

RMAN> restore controlfile from '/u01/control01.ctl';

Starting restore at 31-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=/u01/app/oracle/oradata/jay/control01.ctl
output filename=/u01/app/oracle/oradata/jay/control02.ctl
output filename=/u01/app/oracle/oradata/jay/control03.ctl
Finished restore at 31-AUG-10

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/31/2010 11:45:45
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/31/2010 11:45:51
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/jay/system01.dbf'
ORA-01207: file is more recent than control file - old control file

RMAN> recover database;

Starting recover at 31-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/31/2010 11:46:04
RMAN-06094: datafile 1 must be restored

RMAN> restore database;

Starting restore at 31-AUG-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/jay/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/jay/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/jay/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/jay/1mlmljvq_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/jay/1mlmljvq_1_1 tag=TAG20100830T185905
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/jay/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/jay/1vlmncgc_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/jay/1vlmncgc_1_1 tag=TAG20100831T110340
channel ORA_DISK_1: restore complete, elapsed time: 00:01:37
Finished restore at 31-AUG-10

RMAN> recover database;

Starting recover at 31-AUG-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_2_67qdks9h_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_3_67qfn02l_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_4_67qfn0cq_.arc
archive log thread 1 sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_5_67qfmzj5_.arc
archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_1_67qgpfo4_.arc
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s357q5_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s357v5_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_4_67s357hd_.arc
archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s44djv_.arc
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s44dr5_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s44dwq_.arc
archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s4jbd0_.arc
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s4jc79_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s4jgmp_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_4_67s4jgsl_.arc
archive log thread 1 sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_5_67s4jmr8_.arc
archive log thread 1 sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_6_67s4jmvt_.arc
archive log thread 1 sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_7_67s4jrxw_.arc
archive log thread 1 sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_8_67s4js5b_.arc
archive log thread 1 sequence 9 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_9_67s633xq_.arc
archive log thread 1 sequence 10 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_10_67s6340m_.arc
archive log thread 1 sequence 11 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_11_67s633qq_.arc
archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s6698c_.arc
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s66btj_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s66jb0_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_2_67qdks9h_.arc thread=1 sequence=2
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_3_67qfn02l_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_4_67qfn0cq_.arc thread=1 sequence=4
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_5_67qfmzj5_.arc thread=1 sequence=5
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_1_67qgpfo4_.arc thread=1 sequence=1
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s357q5_.arc thread=1 sequence=2
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s357v5_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_4_67s357hd_.arc thread=1 sequence=4
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s44djv_.arc thread=1 sequence=1
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s44dr5_.arc thread=1 sequence=2
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s44dwq_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s4jbd0_.arc thread=1 sequence=1
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s4jc79_.arc thread=1 sequence=2
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s4jgmp_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_4_67s4jgsl_.arc thread=1 sequence=4
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_5_67s4jmr8_.arc thread=1 sequence=5
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_6_67s4jmvt_.arc thread=1 sequence=6
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_7_67s4jrxw_.arc thread=1 sequence=7
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_8_67s4js5b_.arc thread=1 sequence=8
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_9_67s633xq_.arc thread=1 sequence=9
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_10_67s6340m_.arc thread=1 sequence=10
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_11_67s633qq_.arc thread=1 sequence=11
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s6698c_.arc thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/31/2010 11:53:18
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s6698c_.arc'
ORA-00283: recovery session canceled due to errors
ORA-00314: log 2 of thread 1, expected sequence# 2 doesn't match 11
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jay/redo02.log'

RMAN> exit


Recovery Manager complete.

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             188744384 bytes
Database Buffers          251658240 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/jay/system01.dbf'


SQL> alter database clear logfile '/u01/app/oracle/oradata/jay/redo02.log'; 
Database altered.


SQL> alter database open; 
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/jay/system01.dbf'


SQL>  recover database until cancel;
ORA-00279: change 696770 generated at 08/31/2010 11:23:13 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_%u_.arc
ORA-00280: change 696770 for thread 1 is in sequence #2


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

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

No comments:

free counters
 
Share/Bookmark