I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Saturday, May 10, 2008

Recovery with an old controlfile/recover database using backup controlfile

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:

Aman Sharma said...

Khurram,
Very nice! Good explanation.Your blog is now in my blog list :-).
Cheers
Aman....

Anonymous said...

Excellent, excellent, thank you very much !!!

Att: pentiumonce

pcora said...

Thanks Khurram. This helped.

Followers

About Me

My photo
Melbourne, Victoria, Australia