Question :
I have a full backup wıth control file and archıvelogs which is taken by rman
yesterday.Today my db ıs crashed at the evenıng and control fıle ıs corrupted.
When I restore yesterdays controlfıle from backupset,and restore all yesterday's
datafiles,can I apply todays archivelogs and apply to these datafiles?
Do I lose any data in this scenerio?
Answer :
When you take online/hot backup then yours backup is inconsistent , upon recovery
oracle makes inconsistent backups consistent by applying all archived and online
redo logs ,oracle makes recovery by reading the earliest/oldest SCN in any of the
datafile headers and apply the changes from the logs back into the datafile.
yes indeed controlfile has repositry about alls backup stuff like database backup as
well archivelog backup.If you take backup of archivelog then it goes to control file
entry ,upon restoration activity oracle reads the controlfile to ask where
archivelog exist to be restored.If yours todays archivelogs are on disk not in
controlfile repositry then oracle will apply these todays archivelog regardless
controlfile knows or not,If the RMAN repository or controlfile indicates that no
copies of a needed log sequence number exist on disk, then RMAN looks in backups and
restores archived redo logs as needed to perform the media recovery,there is no
concern controlfile for applying archivelogs at all,if you have todays archivelogs
exist then you will lose data only which is in current redo log thats why
multiplexing redo log came into ours dbackup plan.
Question :
but control file is older it doesnt store the info of new archivelogs.How can I add?
Answer :
Contorolfile older issue raise when you have controlfile for yesterday and datafile
for today,if you restore yesterdays controlfle and restore the datafile from this
yesterday controlfile and then started recovery to this yesterday controlfile then
there would be no any issue to recover the database till before crash using todays
archivelogs.Here you have to remember one thing you will lose only data for current
redo log ,if you have redo log keep intact by multiplexing then you can recover this
multiplexed current redo log data also.
If yesterdays controlfile dont know todays database physical activity i.e adding
datafile then archivelog data for this todays datafile applying will raise the error
during recovery.
Here i am going to prove that yesterdays controlfile will not be an issue for
tracking the todays archivelog during recovery process and it will apply safely.
SQL> select sysdate from dual
2 /
SYSDATE
---------
19-APR-08
SQL> create table a as select * from all_objects
2 /
Table created.
SQL> create table b as select * from all_objects
2 /
Table created.
SQL> create table c as select * from all_objects
2 /
Table created.
SQL> create table d as select * from all_objects
2 /
Table created.
C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008_04_19>dir
Volume in drive C is khurram
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008
_04_19
04/19/2008 02:37 PM <DIR> .
04/19/2008 02:37 PM <DIR> ..
04/19/2008 02:36 PM 9,754,112 O1_MF_1_7_40MH8FC9_.ARC
04/19/2008 02:37 PM 9,753,088 O1_MF_1_8_40MHCDZ4_.ARC
2 File(s) 19,507,200 bytes
2 Dir(s) 58,626,342,912 bytes free
RMAN> run
2> {
3> allocate channel t1 type disk;
4> backup database;
5> release channel t1;
6> allocate channel t2 type disk;
7> backup archivelog all;
8> release channel t2;
9> }
released channel: ORA_DISK_1
allocated channel: t1
channel t1: sid=125 devtype=DISK
Starting backup at 19-APR-08
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSTEM01.DBF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSAUX01.DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\USERS01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\UNDOTBS01.DBF
channel t1: starting piece 1 at 19-APR-08
channel t1: finished piece 1 at 19-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2008_04_19\O1_MF_NNNDF_TAG20080419T144156_40MHMB71_.BKP comment=NONE
channel t1: backup set complete, elapsed time: 00:01:56
Finished backup at 19-APR-08
Starting Control File and SPFILE Autobackup at 19-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459435_40MHPXCT_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-08
released channel: t1
allocated channel: t2
channel t2: sid=125 devtype=DISK
Starting backup at 19-APR-08
current log archived
channel t2: starting archive log backupset
channel t2: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=102 stamp=652458975
input archive log thread=1 sequence=8 recid=103 stamp=652459070
input archive log thread=1 sequence=9 recid=104 stamp=652459454
channel t2: starting piece 1 at 19-APR-08
channel t2: finished piece 1 at 19-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2008_04_19\O1_MF_ANNNN_TAG20080419T144414_40MHQJNO_.BKP comment=NONE
channel t2: backup set complete, elapsed time: 00:00:08
Finished backup at 19-APR-08
Starting Control File and SPFILE Autobackup at 19-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459464_40MHQT1N_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-08
released channel: t2
SQL> conn sys/sys@orcl1 as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008_04_19>dir
Volume in drive C is khurram
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008
_04_19
04/19/2008 02:44 PM <DIR> .
04/19/2008 02:44 PM <DIR> ..
04/19/2008 02:36 PM 9,754,112 O1_MF_1_7_40MH8FC9_.ARC
04/19/2008 02:37 PM 9,753,088 O1_MF_1_8_40MHCDZ4_.ARC
04/19/2008 02:44 PM 2,507,776 O1_MF_1_9_40MHQFYX_.ARC
3 File(s) 22,014,976 bytes
2 Dir(s) 58,033,848,320 bytes free
SQL> conn sys/sys@orcl1 as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
---------------------------------------------------------------------
change the server date from 19-APR-2008 to 20-APR-2008
---------------------------------------------------------------------
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> select sysdate from dual
2 /
SYSDATE
---------
20-APR-08
SQL> conn scott/tiger@orcl1
Connected.
SQL> create table e as select * from all_objects
2 /
Table created.
SQL> create table f as select * from all_objects
2 /
Table created.
SQL> create table g as select * from all_objects
2 /
Table created.
SQL> create table h as select * from all_objects
2 /
Table created.
C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008_04_20>dir
Volume in drive C is khurram
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008
_04_20
04/20/2008 02:53 PM <DIR> .
04/20/2008 02:53 PM <DIR> ..
04/20/2008 02:51 PM 9,756,160 O1_MF_1_10_40P4KWQ8_.ARC
04/20/2008 02:52 PM 9,751,552 O1_MF_1_11_40P4MH79_.ARC
04/20/2008 02:53 PM 9,750,016 O1_MF_1_12_40P4NK9V_.ARC
3 File(s) 29,257,728 bytes
2 Dir(s) 57,978,466,304 bytes free
SQL> conn sys/sys@orcl1 as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
C:\oracle\product\10.1.0\oradata\orcl1>dir
Volume in drive C is khurram
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\orcl1
04/18/2008 06:31 PM <DIR> .
04/18/2008 06:31 PM <DIR> ..
04/20/2008 02:55 PM 2,965,504 CONTROL01.CTL
04/20/2008 02:55 PM 2,965,504 CONTROL02.CTL
04/20/2008 02:55 PM 2,965,504 CONTROL03.CTL
04/20/2008 02:55 PM 10,486,272 REDO01.LOG
04/20/2008 02:55 PM 10,486,272 REDO02.LOG
04/20/2008 02:55 PM 10,486,272 REDO03.LOG
04/20/2008 02:55 PM 251,666,432 SYSAUX01.DBF
04/20/2008 02:55 PM 461,381,632 SYSTEM01.DBF
04/20/2008 02:55 PM 26,222,592 UNDOTBS01.DBF
04/20/2008 02:55 PM 53,747,712 USERS01.DBF
10 File(s) 833,373,696 bytes
2 Dir(s) 57,978,585,088 bytes free
----------------------------------------------------------------------------
Delete alls database files
----------------------------------------------------------------------------
C:\oracle\product\10.1.0\oradata\orcl1>del *.*
C:\oracle\product\10.1.0\oradata\orcl1\*.*, Are you sure (Y/N)? Y
C:\oracle\product\10.1.0\oradata\orcl1>dir
Volume in drive C is khurram
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\orcl1
04/20/2008 02:56 PM <DIR> .
04/20/2008 02:56 PM <DIR> ..
0 File(s) 0 bytes
2 Dir(s) 58,811,949,056 bytes free
C:\oracle\product\10.1.0\oradata\orcl1>
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed 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
C:\>rman
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect target sys/sys@orcl1
connected to target database: orcl1 (not mounted)
RMAN> restore controlfile from autobackup
2> ;
Starting restore at 20-APR-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
recovery area destination: C:\oracle\product\10.1.0\flash_recovery_area
database name (or lock name space) used for search: ORCL1
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459464_40MHQT1N_.BKP
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\CONTROL03.CTL
Finished restore at 20-APR-08
RMAN> startup mount
database is already started
database mounted
RMAN> restore database
2> ;
ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 0
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038
ORA-06512: at line 1
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
Starting restore at 20-APR-08
Starting implicit crosscheck backup at 20-APR-08
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 20-APR-08
Starting implicit crosscheck copy at 20-APR-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-APR-08
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_10_40P4KWQ8_.ARC
File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_11_40P4MH79_.ARC
File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_12_40P4NK9V_.ARC
File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459464_40MHQT1N_.BKP
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 00001 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2008_04_19\O1_MF_NNNDF_TAG20080419T144156_40MHMB71_.BKP tag=TAG20080419T144156
channel ORA_DISK_1: restore complete
Finished restore at 20-APR-08
RMAN> recover database
2> ;
Starting recover at 20-APR-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 9 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_9_40MHQFYX_.ARC
archive log thread 1 sequence 10 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_10_40P4KWQ8_.ARC
archive log thread 1 sequence 11 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_11_40P4MH79_.ARC
archive log thread 1 sequence 12 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_12_40P4NK9V_.ARC
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_9_40MHQFYX_.ARC thread=1 sequence=9
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_10_40P4KWQ8_.ARC thread=1 sequence=10
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_11_40P4MH79_.ARC thread=1 sequence=11
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_12_40P4NK9V_.ARC thread=1 sequence=12
unable to find archive log
archive log thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/20/2008 15:01:51
RMAN-06054: media recovery requesting unknown log: thread 1 seq 13 lowscn 698325
RMAN> report obsolete
2> ;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 142 19-APR-08
Backup Piece 142 19-APR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459435_40MHPXCT_.BKP
Archive Log 102 19-APR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_7_40MH8FC9_.ARC
Archive Log 103 19-APR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_8_40MHCDZ4_.ARC
RMAN> alter database open resetlogs
2> ;
database opened
RMAN>
As you can see i have redundency 1 thats why at recovery these archivelogs are not
getting applied,nevertheless these 19th April archivlogs are no more required to
apply logs but as you can see 20th april logs are being applied.
when i connect to sql i got alls table a,b,c,d,e,f,g but not h cause h data was in
redolog and was not archived before smoking alls database files.
SQL> conn scott/tiger@orcl1
Connected.
SQL> desc a
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> desc b
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> desc c
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> desc d
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> desc e
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> desc f
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> desc g
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> desc h
ERROR:
ORA-04043: object h does not exist
You can see table a,b,c,d recoverd which is for 19th April data and e,f,g recoverd
cause 20th April archivelogs applied to 19th April backupset with 19th April
controlfile.
Question :
But as i have old controlfile then whats the use of recover database using backup
controlfile as i know recover database using backup controlfile is used when you
have old controlfile.
Answer :
Yes you are right but you are missing obvious that using old controlfile recovery by
using recover database using backup controlfile command is used when you have old
controlfile and datafiles are current.Lets see a scenario which will depictyou a
clear picture when to use recover database using backup controlfile.
Lets suppose i am taking cold backup daily ,my procedure is to shutdown the database
and then copy alls database file to somewhere safe daily at the end of day ,one day
i lost/corrupt the controlfile, i dont have current controlfile i have only
controlfile backup which is backed up as cold last day.In this case i have two
options either i create the controlfile by using the create controlfile command then
perform recovery if required and start up the database.Other option is to restore
the last cold backed up controlfile and recover database using backup controlfile.
If you try to recover database without using backup controlfile command then you
will get the error.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile
ORA-1122: database file 1 failed verification check
Cause: The information in this file (system01.dbf) is inconsistent with information from the control file.
Action: Make sure that the db files and control files are the correct files for this database.
ORA-1207 file is more recent than control file - old control file
Cause: The control file change sequence number in the data file is greater than the number in the control file. This
implies that the wrong control file is being used. Note that repeatedly causing this error can make it stop
happening without correcting the real problem. Every attempt to open the database will advance the control
file change sequence number until it is great enough.
Action: Use the current control file or do backup controlfile recovery to make the control file current. Be sure to
follow all restrictions on doing a backup controlfile recovery.
SQL> conn sys/sys 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 4
Next log sequence to archive 6
Current log sequence 6
SQL> select group#,members,status
2 from v$log
3 /
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 UNUSED
3 1 UNUSED
SQL>
SQL> conn scott/tiger
Connected.
SQL> create table x1 as select * from all_objects
2 /
Table created.
SQL> create table x2 as select * from all_objects
2 /
Table created.
SQL> conn sys/sys as sysdba
Connected.
SQL> alter system switch logfile
2 /
System altered.
SQL> select group#,members,status
2 from v$log
3 /
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 CURRENT
3 1 UNUSED
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Copy the database folder to somewhere safe place
C:\oracle\product\10.1.0\oradata>copy prod coldbackup
prod\CONTROL01.CTL
prod\CONTROL02.CTL
prod\CONTROL03.CTL
prod\REDO01.LOG
prod\REDO02.LOG
prod\REDO03.LOG
prod\SYSAUX01.DBF
prod\SYSTEM01.DBF
prod\TEMP01.DBF
prod\UNDOTBS01.DBF
prod\USERS01.DBF
11 file(s) copied.
C:\oracle\product\10.1.0\oradata>
Now startup the database.
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
You have taken cold backup, now perform some activity
SQL> select group#,members,status
2 from v$log
3 /
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 UNUSED
SQL> conn scott/tiger
Connected.
SQL>
SQL> create table x3 as select * from all_objects
2 /
Table created.
SQL> alter system switch logfile
2 /
System altered.
SQL> select group#,members,status
2 from v$log
3 /
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 ACTIVE
3 1 CURRENT
Now delete or corrupt the controlfile ,i am deleting here alls controlfile here
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
C:\oracle\product\10.1.0\oradata\Prod>del *.ctl
C:\oracle\product\10.1.0\oradata\Prod>dir *.ctl
Volume in drive C is khurram
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\Prod
File Not Found
Now startup the database
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed 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> shutdown abort
ORACLE instance shut down.
SQL>
Copy the controlfile from coldbackup to yours Prod database file folder.
C:\oracle\product\10.1.0\oradata>copy c:\oracle\product\10.1.0\oradata\coldbackup\*.ctl C:\oracle\product\10.1.0\oradata\prod
c:\oracle\product\10.1.0\oradata\coldbackup\CONTROL01.CTL
c:\oracle\product\10.1.0\oradata\coldbackup\CONTROL02.CTL
c:\oracle\product\10.1.0\oradata\coldbackup\CONTROL03.CTL
3 file(s) copied.
C:\oracle\product\10.1.0\oradata>
Now the controlfiles from cold backup to prod folder copied succesfully ,start the database
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile
SQL> recover database using backup controlfile until cancel
ORA-00279: change 333130 generated at 05/10/2008 16:42:05 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_10\O1_MF_1_2_%U_.ARC
ORA-00280: change 333130 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 334273 generated at 05/10/2008 16:48:51 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_10\O1_MF_1_3_%U_.ARC
ORA-00280: change 334273 for thread 1 is in sequence #3
ORA-00278: log file
'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_10\O1_MF_1_2_42C2X57F_.ARC' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\product\10.1.0\oradata\Prod\REDO03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs
2 /
Database altered.
Question :
But You showed me up without any new added datafile with old controlfile recovery
and yours scenario with cold backup ,my case is hot backup and i have new datafile
but old controlfile backup ,i lost old control file but datafiles exist on disk
presumably some data files are added in database file before losing controlfile and
old controlfile does not know about it how will i make recovery in this scenario
Answer :
The process would be the same in yours scenario as i mentioned above, you will have
to make recovery with "recover database using backup controlfile" when you have old
controlfile and new datafiles.Lets see here
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect target sys/sys@uat
connected to target database: UAT (DBID=2679299118)
RMAN> list backup of controlfile
2> ;
using target database controlfile instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 2M DISK 00:00:03 16-MAY-08
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20080516T002026
Piece Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\BACKUPSET\2008_05_16\O1_MF_NCSNF_TAG20080516T002026_42S3B7SW_.BKP
Controlfile Included: Ckp SCN: 408253 Ckp time: 16-MAY-08
SQL> conn sys/sys@uat as sysdba
Connected.
SQL> select ts#,name
2 from v$tablespace
3 /
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
4 USERS
3 TEMP
SQL> select ts#,file#,substr(name,1,50) name,status,enabled
2 from v$datafile
3 /
TS# FILE# NAME STATUS ENABLED
---------- ---------- -------------------------------------------------- ------- ----------
0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE
1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE
2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE
4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE
SQL> create tablespace newtablespace datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\newdatafile01.dbf' size 100M
2 /
Tablespace created.
make sure added tablespace datafile is online and enabled for read and write.
SQL> select ts#,name
2 from v$tablespace
3 /
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
4 USERS
3 TEMP
6 NEWTABLESPACE
6 rows selected.
SQL>
SQL> select ts#,file#,substr(name,1,50) name,status,enabled
2 from v$datafile
3 /
TS# FILE# NAME STATUS ENABLED
---------- ---------- -------------------------------------------------- ------- ----------
0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE
1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE
2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE
4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE
6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01 ONLINE READ WRITE
make default this newtablespace for the user scott
SQL> alter user scott default tablespace newtablespace
2 /
User altered.
SQL> conn scott/tiger@uat
Connected.
SQL> create table v1 as select * from all_objects
2 /
Table created.
SQL> create table v2 as select * from all_objects
2 /
Table created.
SQL> conn sys/sys@uat as sysdba
Connected.
SQL> alter system switch logfile
2 /
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Here i drop the controld files
C:\oracle\product\10.1.0\oradata\uat>dir *.ctl
Volume in drive C is khurram
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\uat
05/26/2008 02:10 PM 2,899,968 CONTROL01.CTL
05/26/2008 02:10 PM 2,899,968 CONTROL02.CTL
05/26/2008 02:10 PM 2,899,968 CONTROL03.CTL
3 File(s) 8,699,904 bytes
0 Dir(s) 43,394,301,952 bytes free
C:\oracle\product\10.1.0\oradata\uat>del *.ctl
C:\oracle\product\10.1.0\oradata\uat>dir *.ctl
Volume in drive C is khurram
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\uat
File Not Found
C:\>rman
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect target sys/sys@uat
connected to target database (not started)
RMAN> startup
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/26/2008 14:18:02
ORA-00205: error in identifying controlfile, check alert log for more info
RMAN> restore controlfile from 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT
\BACKUPSET\2008_05_16\O1_MF_NCSNF_TAG20080516T002026_42S3B7SW_.BKP'
2> ;
Starting restore at 26-MAY-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\CONTROL03.CTL
Finished restore at 26-MAY-08
RMAN>
you can see the controlfile restored for the date of 16th May,2008 and datafile are
for today dated 26th May ,2008 and one of datafile named newdatafile01.dbf has been
added today dated 26th MAy,2008,above restored controlfile doesnt aware about new
added data file newdatafile01.dbf which exist on disk.
C:\oracle\product\10.1.0\oradata\uat>dir *.dbf
Volume in drive C is khurram
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\uat
05/26/2008 02:10 PM 104,865,792 NEWDATAFILE01.DBF
05/26/2008 02:10 PM 262,152,192 SYSAUX01.DBF
05/26/2008 02:10 PM 461,381,632 SYSTEM01.DBF
05/25/2008 09:42 AM 20,979,712 TEMP01.DBF
05/26/2008 02:10 PM 26,222,592 UNDOTBS01.DBF
05/26/2008 02:10 PM 5,251,072 USERS01.DBF
6 File(s) 880,852,992 bytes
0 Dir(s) 43,392,053,248 bytes free
C:\oracle\product\10.1.0\oradata\uat>
RMAN> startup mount
database is already started
database mounted
RMAN>
SQL> select * from v$recover_file
2 /
no rows selected
now you have new old controlfile and new datafiles do
RMAN>recover database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/26/2008 14:43:26
ORA-01119: error in creating database file 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT
\NEWDATAFILE01.DBF'
ORA-27038: created file already exists
OSD-04010: option specified, file already exists
SQL> recover database
ORA-01153: an incompatible media recovery is active
an incompatible media recovery is active cause newdatafile01.dbf does not known by old controlfile.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'
SQL> conn sys/sys@uat as sysdba
Connected.
SQL> recover database
ORA-01153: an incompatible media recovery is active
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'
You can also check the name of datafile unnamed00005 from alert log file
Mon May 26 14:43:23 2008
alter database recover logfile 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC'
Media Recovery Log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC
File #5 added to control file as 'UNNAMED00005'. Originally created as:
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF'
Errors with log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC
SQL> select ts#,file#,substr(name,1,55) name,status,enabled
2 from v$datafile
3 /
TS# FILE# NAME STATUS ENABLED
---- ------ ------------------------------------------------- ------- ---------
0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE
1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE
2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE
4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE
6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF RECOVER READ WRITE
SQL> alter database rename file 'C:\WINDOWS\SYSTEM32\UNNAMED00005'
to 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF'
2 /
Database altered.
SQL> select ts#,file#,substr(name,1,55) name,status,enabled
2 from v$datafile
3 /
TS# FILE# NAME STATUS ENABLED
--- ----- ------------------------------------------------------ ------ ---------
0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE
1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE
2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE
4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE
6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF RECOVER READ WRITE
Try to recover the datafile at RMAN as well sqlplus
C:\>rman
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect target sys/sys@uat
connected to target database: UAT (DBID=2679299118)
RMAN> recover datafile 5
2> ;
Starting recover at 26-MAY-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/26/2008 15:12:30
RMAN-06067: RECOVER DATABASE required with a backup or created controlfile
SQL> recover datafile 5
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
As you can see the recovery should be done by using recover database using backup controlfile.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> select group#,status
2 from v$log
3 /
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
SQL> recover database using backup controlfile
ORA-00279: change 1011489 generated at 05/26/2008 12:51:02 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_%U_.ARC
ORA-00280: change 1011489 for thread 1 is in sequence #71
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1014306 generated at 05/26/2008 13:33:06 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_72_%U_.ARC
ORA-00280: change 1014306 for thread 1 is in sequence #72
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1016418 generated at 05/26/2008 14:00:33 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_73_%U_.ARC
ORA-00280: change 1016418 for thread 1 is in sequence #73
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_72_43NZ1NQ7_.ARC' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1017543 generated at 05/26/2008 14:08:47 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_74_%U_.ARC
ORA-00280: change 1017543 for thread 1 is in sequence #74
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_73_43NZJZG7_.ARC' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC
ORA-00280: change 1017771 for thread 1 is in sequence #75
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_74_43NZM4B6_.ARC' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Do recovery again at this time dont specify to apply the log at O1_MF_1_75_%U_.ARC ,only apply alls redo log file instead applying O1_MF_1_75_%U_.ARC.
SQL> recover database using backup controlfile
ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC
ORA-00280: change 1017771 for thread 1 is in sequence #75
Specify log: {=suggested | filename | AUTO | CANCEL}
C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO03.log<---------------------------
ORA-00310: archived log contains sequence 73; sequence 75 required
ORA-00334: archived log: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO03.LOG'
SQL> select ts#,file#,substr(name,1,55) name,status,enabled
2 from v$datafile
3 /
TS# FILE# NAME STATUS ENABLED
---- ----- -------------------------------------------------- ------- --------
0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE
1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE
2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE
4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE
6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF RECOVER READ WRITE
now use redo01.log file
SQL> recover database using backup controlfile
ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC
ORA-00280: change 1017771 for thread 1 is in sequence #75
Specify log: {=suggested | filename | AUTO | CANCEL}
C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO01.LOG<---------------------------
ORA-00310: archived log contains sequence 74; sequence 75 required
ORA-00334: archived log: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO01.LOG'
SQL> select ts#,file#,substr(name,1,55) name,status,enabled
2 from v$datafile
3 /
TS# FILE# NAME STATUS ENABLED
--- ------ ------------------------------------------------------- ------- --------
0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE
1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE
2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE
4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE
6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF RECOVER READ WRITE
now use redo02.log file
SQL> recover database using backup controlfile
ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC
ORA-00280: change 1017771 for thread 1 is in sequence #75
Specify log: {=suggested | filename | AUTO | CANCEL}
C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO02.LOG<------------------
Log applied.
Media recovery complete.
SQL> select ts#,file#,substr(name,1,55) name,status,enabled
2 from v$datafile
3 /
TS# FILE# NAME STATUS ENABLED
--- ----- ---------------------------------------------------- ------- ----------
0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE
1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE
2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE
4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE
6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF ONLINE READ WRITE
SQL> alter database open resetlogs
2 /
Database altered.
SQL> select ts#,file#,substr(name,1,55) name,status,enabled
2 from v$datafile
3 /
TS# FILE# NAME STATUS ENABLED
---- ----- ------------------------------------------------------- ------- ---------
0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE
1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE
2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE
4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE
6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF ONLINE READ WRITE
SQL> alter database open resetlogs
2 /
Database altered.
SQL> select ts#,file#,substr(name,1,55) name,status,enabled
2 from v$datafile
3 /
TS# FILE# NAME STATUS ENABLED
--- ----- -------------------------------------------------- ------- ----------
0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE
1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE
2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE
4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE
6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF ONLINE READ WRITE
SQL>
3 comments:
Khurram,
Very nice! Good explanation.Your blog is now in my blog list :-).
Cheers
Aman....
Excellent, excellent, thank you very much !!!
Att: pentiumonce
Thanks Khurram. This helped.
Post a Comment