12 Replies Latest reply: Nov 25, 2008 11:07 PM by 595435 RSS

    recover database using backup controlfile

    595435
      I m working on TEST machine.

      1. I have taken HOT BACKUP.
      2. added tablespace TEST (datafile testing.dbf)
      3. deleted all controlfiles.
      4. copied from backup.
      5. recover database using backup controlfile until cancel;

      6. After that I m getting below error.

      SQL> shutdown immediate;
      ORA-01109: database not open


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

      Total System Global Area 167772160 bytes
      Fixed Size 1247876 bytes
      Variable Size 83887484 bytes
      Database Buffers 75497472 bytes
      Redo Buffers 7139328 bytes
      Database mounted.
      ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


      SQL> alter database open resetlogs;
      alter database open resetlogs
      *
      ERROR at line 1:
      ORA-01152: file 1 was not restored from a sufficiently old backup
      ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'


      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-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF'
      ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
      ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF'


      SQL>
        • 1. Re: recover database using backup controlfile
          Satish Kandi
          The file name given by you
          2. added tablespace TEST (datafile testing.dbf)
          and the file name indicated by Oracle are not the same.
          ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\*TESTING1.DBF*'
          • 2. Re: recover database using backup controlfile
            595435
            The file I created is testing1.dbf (sorry..it was typing mistake, i didnt write 1) File is still available but at different location ( I have not deleted it permanently ).

            But my scenario is.
            I have taken a backup, and added new tablespace, after that my control files are lost then?
            • 3. Re: recover database using backup controlfile
              Satish Kandi
              File is still available but at different location ( I have not deleted it permanently ).
              You need to place the same at correct location.
              I have taken a backup, and added new tablespace, after that my control files are lost then?
              If your database was in archive log mode and you are using backup controlfile, you should be able to recover the database to the last change available in online redo logs.
              • 4. Re: recover database using backup controlfile
                Anand...
                Hi..

                >

                I have taken a backup, and added new tablespace, after that my control files are lost then?

                >

                Place the backup of the contorlfile at the correct location.All the datafiles must also be ate the correct/same location as it was before the loss of the controlfile. Then use RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL. It will prompt for the archivelogs, which you need to provide, and your archivelog will be having the information, that you has created a new tablespace, it will recover it, and when all the available archive logs and redo logs are applied, cancel the recovery and open the database with resetlogs option.


                Anand
                • 5. Re: recover database using backup controlfile
                  595435
                  Dear Satish, In case If my file lost then? I m just experimenting. Yeah.. my DB is in archivelog mode.
                  • 6. Re: recover database using backup controlfile
                    595435
                    I did the same.

                    SQL> recover database using backup controlfile until cancel;
                    ORA-00279: change 651512539 generated at 11/25/2008 10:27:34 needed for thread 1
                    ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_5_1_671639012.ARC
                    ORA-00280: change 651512539 for thread 1 is in sequence #5


                    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                    D:\oracle\product\10.2.0\oradata\rock\REDO05.LOG
                    ORA-00310: archived log contains sequence 4; sequence 5 required
                    ORA-00334: archived log: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO05.LOG'


                    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                    ORA-01152: file 1 was not restored from a sufficiently old backup
                    ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'


                    SQL> D:\oracle\product\10.2.0\oradata\rock\REDO06.LOG
                    SP2-0734: unknown command beginning "D:\oracle\..." - rest of line ignored.
                    SQL> recover database using backup controlfile until cancel;
                    ORA-00279: change 651512539 generated at 11/25/2008 10:27:34 needed for thread 1
                    ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_5_1_671639012.ARC
                    ORA-00280: change 651512539 for thread 1 is in sequence #5


                    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                    D:\oracle\product\10.2.0\oradata\rock\REDO06.LOG
                    ORA-00310: archived log contains sequence 3; sequence 5 required
                    ORA-00334: archived log: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO06.LOG'


                    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                    ORA-01152: file 1 was not restored from a sufficiently old backup
                    ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'


                    SQL> recover database using backup controlfile until cancel;
                    ORA-00279: change 651512539 generated at 11/25/2008 10:27:34 needed for thread 1
                    ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_5_1_671639012.ARC
                    ORA-00280: change 651512539 for thread 1 is in sequence #5


                    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                    D:\oracle\product\10.2.0\oradata\rock\REDO04.LOG
                    ORA-00283: recovery session canceled due to errors
                    ORA-01244: unnamed datafile(s) added to control file by media recovery
                    ORA-01110: data file 8: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TEST01.DBF'


                    ORA-01112: media recovery not started


                    SQL> recover database using backup controlfile;
                    ORA-00283: recovery session canceled due to errors
                    ORA-01111: name for data file 8 is unknown - rename to correct file
                    ORA-01110: data file 8: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008'
                    ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
                    ORA-01111: name for data file 8 is unknown - rename to correct file
                    ORA-01110: data file 8: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008'


                    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 until cancel;
                    ORA-00283: recovery session canceled due to errors
                    ORA-01111: name for data file 8 is unknown - rename to correct file
                    ORA-01110: data file 8: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008'
                    ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
                    ORA-01111: name for data file 8 is unknown - rename to correct file
                    ORA-01110: data file 8: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008'

                    SQL> alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' to 'D:\oracle\product\10.2.0\oradata\rock\TESTING1.DBF';
                    alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' to 'D:\oracle\product\10.2.0\oradata\rock\TESTING1.DBF'
                    *
                    ERROR at line 1:
                    ORA-01511: error in renaming log/data files
                    ORA-01523: cannot rename data file to 'D:\oracle\product\10.2.0\oradata\rock\TESTING1.DBF' - file already part of database

                    SQL> alter database open;
                    alter database open
                    *
                    ERROR at line 1:
                    ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


                    SQL> alter database open resetlogs;
                    alter database open resetlogs
                    *
                    ERROR at line 1:
                    ORA-01152: file 1 was not restored from a sufficiently old backup
                    ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'


                    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 until cancel;
                    ORA-00283: recovery session canceled due to errors
                    ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF'
                    ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
                    ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF'


                    SQL> recover database using backup controlfile;
                    ORA-00283: recovery session canceled due to errors
                    ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF'
                    ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
                    ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF'

                    SQL> shutdown immediate;
                    ORA-01109: database not open


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

                    Total System Global Area 167772160 bytes
                    Fixed Size 1247876 bytes
                    Variable Size 83887484 bytes
                    Database Buffers 75497472 bytes
                    Redo Buffers 7139328 bytes
                    Database mounted.
                    ORA-01122: database file 1 failed verification check
                    ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'
                    ORA-01207: file is more recent than control file - old control file
                    • 7. Re: recover database using backup controlfile
                      Randy Amor
                      $ rman target /

                      Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 25 17:29:24 2008

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

                      connected to target database: RANDY10G (DBID=3413520730)

                      RMAN> backup database;

                      Starting backup at 25-NOV-08
                      using channel ORA_DISK_1
                      channel ORA_DISK_1: starting full datafile backupset
                      channel ORA_DISK_1: specifying datafile(s) in backupset
                      input datafile fno=00001 name=/u01/app/oradata/randy10g/system01.dbf
                      input datafile fno=00003 name=/u01/app/oradata/randy10g/sysaux01.dbf
                      input datafile fno=00005 name=/u01/app/oradata/randy10g/example01.dbf
                      input datafile fno=00002 name=/u01/app/oradata/randy10g/undotbs01.dbf
                      input datafile fno=00004 name=/u01/app/oradata/randy10g/users01.dbf
                      input datafile fno=00006 name=/u01/app/oradata/randy10g/outlines01.dbf
                      channel ORA_DISK_1: starting piece 1 at 25-NOV-08
                      channel ORA_DISK_1: finished piece 1 at 25-NOV-08
                      piece handle=/u01/app/flash_recovery_area/RANDY10G/backupset/2008_11_25/o1_mf_nnndf_TAG20081125T181711_4lqn57jr_.bkp tag=TAG20081125T181711 comment=NONE
                      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
                      Finished backup at 25-NOV-08

                      Starting Control File and SPFILE Autobackup at 25-NOV-08
                      piece handle=/u01/app/flash_recovery_area/RANDY10G/autobackup/2008_11_25/o1_mf_s_671739660_4lqnddr0_.bkp comment=NONE
                      Finished Control File and SPFILE Autobackup at 25-NOV-08

                      SQL> create tablespace test datafile '/u01/app/oradata/randy10g/test01.dbf' size 2m;

                      Tablespace created.

                      $ mv test01.dbf test01.dbf.bak
                      $ mv control01.ctl control01.ctl.bak
                      $ mv control02.ctl control02.ctl.bak
                      $ mv control03.ctl control03.ctl.bak

                      SQL> shutdown abort
                      ORACLE instance shut down.

                      $ rman target /

                      Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 25 17:58:44 2008

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

                      connected to target database (not started)

                      RMAN> set dbid=3413520730

                      executing command: SET DBID

                      RMAN> startup nomount;

                      Oracle instance started

                      Total System Global Area 205520896 bytes

                      Fixed Size 1218532 bytes
                      Variable Size 79693852 bytes
                      Database Buffers 121634816 bytes
                      Redo Buffers 2973696 bytes

                      RMAN> restore controlfile from autobackup;

                      Starting restore at 25-NOV-08
                      using target database control file instead of recovery catalog
                      allocated channel: ORA_DISK_1
                      channel ORA_DISK_1: sid=156 devtype=DISK

                      recovery area destination: /u01/app/flash_recovery_area
                      database name (or database unique name) used for search: RANDY10G
                      channel ORA_DISK_1: autobackup found in the recovery area
                      channel ORA_DISK_1: autobackup found: /u01/app/flash_recovery_area/RANDY10G/autobackup/2008_11_25/o1_mf_s_671737741_4lqljfjy_.bkp
                      channel ORA_DISK_1: control file restore from autobackup complete
                      output filename=/u01/app/oradata/randy10g/control01.ctl
                      output filename=/u01/app/oradata/randy10g/control02.ctl
                      output filename=/u01/app/oradata/randy10g/control03.ctl
                      Finished restore at 25-NOV-08

                      SQL> alter database create datafile '/u01/app/oradata/randy10g/test01.dbf';

                      Database altered.

                      SQL> recover database using backup controlfile;
                      ORA-00279: change 999804 generated at 11/25/2008 18:39:15 needed for thread 1
                      ORA-00289: suggestion :
                      /u01/app/flash_recovery_area/RANDY10G/archivelog/2008_11_25/o1_mf_1_1_%u_.arc
                      ORA-00280: change 999804 for thread 1 is in sequence #1

                      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                      auto
                      ORA-00308: cannot open archived log
                      '/u01/app/flash_recovery_area/RANDY10G/archivelog/2008_11_25/o1_mf_1_1_%u_.arc'
                      ORA-27037: unable to obtain file status
                      Linux Error: 2: No such file or directory
                      Additional information: 3

                      ORA-00308: cannot open archived log
                      '/u01/app/flash_recovery_area/RANDY10G/archivelog/2008_11_25/o1_mf_1_1_%u_.arc'
                      ORA-27037: unable to obtain file status
                      Linux Error: 2: No such file or directory
                      Additional information: 3

                      SQL> recover database using backup controlfile;
                      ORA-00279: change 999804 generated at 11/25/2008 18:39:15 needed for thread 1
                      ORA-00289: suggestion :
                      /u01/app/flash_recovery_area/RANDY10G/archivelog/2008_11_25/o1_mf_1_1_%u_.arc
                      ORA-00280: change 999804 for thread 1 is in sequence #1

                      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                      /u01/app/oradata/randy10g/redo01.log
                      Log applied.
                      Media recovery complete.

                      SQL> alter database open;
                      alter database open
                      *
                      ERROR at line 1:
                      ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

                      SQL> alter database open resetlogs;

                      Database altered.

                      This is my test, the heightlight portion is very important. It creates a reference in control file for the new datafile.
                      • 8. Re: recover database using backup controlfile
                        Enrique Orbegozo
                        ORA-01244: unnamed datafile(s) added to control file by media recovery
                        ORA-01110: data file 8: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TEST01.DBF'
                        . . .
                        SQL> alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' to 'D:\oracle\product\10.2.0\oradata\rock\TESTING1.DBF';
                        alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' to 'D:\oracle\product\10.2.0\oradata\rock\TESTING1.DBF'
                        *
                        ERROR at line 1:
                        ORA-01511: error in renaming log/data files
                        ORA-01523: cannot rename data file to 'D:\oracle\product\10.2.0\oradata\rock\TESTING1.DBF' - file already part of database
                        . . .
                        SQL> recover database using backup controlfile until cancel;
                        ORA-00283: recovery session canceled due to errors
                        ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF'
                        ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
                        >

                        It's confusing, datafile 8 is "D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TEST01.DBF", datafile 4 is "D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF", but you're trying to rename datafile 8 using the name of datafile 4.

                        Enrique
                        • 9. Re: recover database using backup controlfile
                          595435
                          Thanks Randy, I m doing User Managed Backup & Recovery at my TEST Database. The answer is helpfull, If one has Configured RMAN.
                          Well... I have restored back my datafile and controlfile.
                          • 10. Re: recover database using backup controlfile
                            595435
                            Thanks Enrique, your answer is really helpful. I was confused between test and testing filenames. But I have successfuuly renamed the file, Now i m getting another error. Check below.

                            SQL> startup
                            ORACLE instance started.

                            Total System Global Area 167772160 bytes
                            Fixed Size 1247876 bytes
                            Variable Size 83887484 bytes
                            Database Buffers 75497472 bytes
                            Redo Buffers 7139328 bytes
                            Database mounted.
                            ORA-01122: database file 1 failed verification check
                            ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'
                            ORA-01207: file is more recent than control file - old control file


                            SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
                            ORA-00279: change 651507313 generated at 11/24/2008 14:55:59 needed for thread 1
                            ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_4_1_671639012.ARC
                            ORA-00280: change 651507313 for thread 1 is in sequence #4


                            Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

                            ORA-00279: change 651512539 generated at 11/25/2008 10:27:34 needed for thread 1
                            ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_5_1_671639012.ARC
                            ORA-00280: change 651512539 for thread 1 is in sequence #5
                            ORA-00278: log file 'D:\ORACLE\ARCHIVE_ROCK\ARC_4_1_671639012.ARC' no longer needed for this recovery


                            Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

                            ORA-00283: recovery session canceled due to errors
                            ORA-01244: unnamed datafile(s) added to control file by media recovery
                            ORA-01110: data file 8: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TEST01.DBF'


                            ORA-01112: media recovery not started


                            SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
                            ORA-00283: recovery session canceled due to errors
                            ORA-01111: name for data file 8 is unknown - rename to correct file
                            ORA-01110: data file 8: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008'
                            ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
                            ORA-01111: name for data file 8 is unknown - rename to correct file
                            ORA-01110: data file 8: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008'


                            SQL> alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' to 'D:\oracle\product\10.2.0\oradata\rock\test01.dbf';

                            Database altered.

                            SQL> alter database open resetlogs;
                            alter database open resetlogs
                            *
                            ERROR at line 1:
                            ORA-01152: file 1 was not restored from a sufficiently old backup
                            ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'
                            • 11. Re: recover database using backup controlfile
                              Enrique Orbegozo
                              OK, when you're recovering the database, the process is stopped because of the new datafile, after you rename the datafile you need to restart the recovery so it continues from the point it stopped.

                              Enrique
                              • 12. Re: recover database using backup controlfile
                                595435
                                SQL> select open_mode from v$database;

                                OPEN_MODE
                                ----------
                                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-00279: change 651515163 generated at 11/25/2008 10:38:30 needed for thread
                                1
                                ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_5_1_671639012.ARC
                                ORA-00280: change 651515163 for thread 1 is in sequence #5


                                Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                                auto
                                ORA-00279: change 651516322 generated at 11/25/2008 16:54:21 needed for thread
                                1
                                ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_6_1_671639012.ARC
                                ORA-00280: change 651516322 for thread 1 is in sequence #6
                                ORA-00278: log file 'D:\ORACLE\ARCHIVE_ROCK\ARC_5_1_671639012.ARC' no longer
                                needed for this recovery


                                ORA-00279: change 651519526 generated at 11/25/2008 17:07:17 needed for thread
                                1
                                ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_7_1_671639012.ARC
                                ORA-00280: change 651519526 for thread 1 is in sequence #7
                                ORA-00278: log file 'D:\ORACLE\ARCHIVE_ROCK\ARC_6_1_671639012.ARC' no longer
                                needed for this recovery


                                ORA-00279: change 651522763 generated at 11/25/2008 17:34:41 needed for thread
                                1
                                ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_8_1_671639012.ARC
                                ORA-00280: change 651522763 for thread 1 is in sequence #8
                                ORA-00278: log file 'D:\ORACLE\ARCHIVE_ROCK\ARC_7_1_671639012.ARC' no longer
                                needed for this recovery


                                ORA-00308: cannot open archived log
                                'D:\ORACLE\ARCHIVE_ROCK\ARC_8_1_671639012.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.



                                SQL>
                                SQL> recover database using backup controlfile until cancel;
                                ORA-00279: change 651522763 generated at 11/25/2008 17:34:41 needed for thread
                                1
                                ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_8_1_671639012.ARC
                                ORA-00280: change 651522763 for thread 1 is in sequence #8


                                Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                                cancel;
                                ORA-00308: cannot open archived log 'cancel;'
                                ORA-27041: unable to open file
                                OSD-04002: unable to open file
                                O/S-Error: (OS 2) The system cannot find the file specified.


                                Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                                cancel
                                ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                                ORA-01152: file 1 was not restored from a sufficiently old backup
                                ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'


                                ORA-01112: media recovery not started


                                SQL> alter database open resetlogs;
                                alter database open resetlogs
                                *
                                ERROR at line 1:
                                ORA-01152: file 1 was not restored from a sufficiently old backup
                                ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'

                                I checked in the ArchiveLog folder and didnt find  D:\ORACLE\ARCHIVE_ROCK\ARC_8_1_671639012.ARC the archive log file when I queried ( v$log, v$logfile and v$log_history) I got to know that One group is current (v$log) and sequence # 8 is not generated yet (log_history). Now Instead of cancel or auto option I will give redolog filename and It will get required sequence number.

                                SQL> recover database using backup controlfile;
                                ORA-00279: change 651522763 generated at 11/25/2008 17:34:41 needed for thread
                                1
                                ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_8_1_671639012.ARC
                                ORA-00280: change 651522763 for thread 1 is in sequence #8


                                Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO05.LOG
                                ORA-00310: archived log contains sequence 7; sequence 8 required
                                ORA-00334: archived log: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO05.LOG'


                                SQL> recover database using backup controlfile;
                                ORA-00279: change 651522763 generated at 11/25/2008 17:34:41 needed for thread
                                1
                                ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_8_1_671639012.ARC
                                ORA-00280: change 651522763 for thread 1 is in sequence #8


                                Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO06.LOG
                                ORA-00310: archived log contains sequence 6; sequence 8 required
                                ORA-00334: archived log: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO06.LOG'


                                SQL> recover database using backup controlfile;
                                ORA-00279: change 651522763 generated at 11/25/2008 17:34:41 needed for thread
                                1
                                ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_8_1_671639012.ARC
                                ORA-00280: change 651522763 for thread 1 is in sequence #8


                                Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO04.LOG
                                Log applied.
                                Media recovery complete.
                                SQL> alter database open resetlogs;

                                Database altered.

                                SQL>