1 2 Previous Next 20 Replies Latest reply: Dec 30, 2012 10:56 AM by 951027 RSS

    recover database

    951027
      Hello all,,

      I have taken a backup using putting database in backup mode

      alter database begin backup
      copied files to new destination
      alter database end backup
      insert into employee values('abc');
      commit
      alter system archive log current;
      alter database backup controlfile to trace as 'c:\control01.sql'
      logged into new database

      created parameter file,

      startup nomount;

      edited C:\control01.sql
      and made directory changes, changed reuse to set
      @C:\control01.sql
      control file got created

      alter system set log_archive_dest_1='location=c:\archive';

      copied archive logs from primary databse to this location

      now i want to knw the difference between


      recover database , recover database until cancel & recover database using backup contorlfile until cancel


      Thanks
        • 1. Re: recover database
          Osama_Mustafa
          ecover database using backup contorlfile until cancel
          If all copies of the current control file are lost or damaged, then you must restore and mount a backup control file before you can perform recovery. When using a backup control file. Don't use the current controlfiles, use backed up control files instead.

          Review :
          http://web.njit.edu/info/limpid/DOC/backup.102/b14191/rcmrecov003.htm
          recover database until cancel
          Performs a incomplete recovery,Not all redo generated since the last backup is applied in a incomplete recovery. It is also called database point in time recovery (DBPITR).
          RECOVER DATABASE Command
          Restores and rolls forward a database to a particular point in time or to the end of the logs.


          http://ss64.com/ora/recover.html
          • 2. Re: recover database
            saurabh
            Recover Database

            It means your controlfile is old and you have restore your datafile from backup and your need to do a complete recovery.

            Recover Database until cancel

            It means your controlfile is old and you have restore your datafile and you have to do a incomplete recovery of your database.

            recover database using backup contorlfile until cancel

            if your controlfile and datafile are restored from backup and you need to perform and incomplete recovery of your database.
            • 3. Re: recover database
              951027
              so in the current scenario which would be best,

              as i said i copied data files to new database

              then took backup of controlfile


              and created the controlfile in new database

              then gone back to primary database and inserted some rows in a table

              commit;

              then

              alter system archive log current;

              copied archive logs to new databse


              now whic recover option shud i choose in order for the inserted rows to reflect in my new database???
              • 4. Re: recover database
                saurabh
                in this case you can use is
                recover database using backup controlfile until cancel;

                because if you try to do recover database it will give you error;
                • 5. Re: recover database
                  Osama_Mustafa
                  USE until cancel
                  • 6. Re: recover database
                    951027
                    so i would hit cancel

                    when the archive logs which i copied are finished
                    • 7. Re: recover database
                      saurabh
                      yes
                      • 8. Re: recover database
                        951027
                        great thanks guys it really helped,


                        i recoverd the database completely i cross checked the rows which i inserted after taking the backup and they are in the new database


                        guys just one more thing.

                        now both the databses hav same data......

                        again if i want to insert rows in primary database and do

                        alter system archive log current;

                        then cn i directly paste that archive log into second database archvive folder and do shutdown immediate
                        startup mount

                        recover database using backup controlfile until cancel

                        or is there any other step in between, bcoz im getting an error
                        SQL> set logsource 'E:\oracle\app\oradata\orcl1\archive';
                        SQL> recover database using backup controlfile until cancel;
                        ORA-00279: change 19957054 generated at 12/18/2012 14:18:50 needed for thread 1
                        ORA-00289: suggestion :
                        E:\ORACLE\APP\ORADATA\ORCL1\ARCHIVE\ARC0000000001_0802361927.0001
                        ORA-00280: change 19957054 for thread 1 is in sequence #1


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

                        ORA-00308: cannot open archived log
                        'E:\ORACLE\APP\ORADATA\ORCL1\ARCHIVE\*ARC0000000001_0802361927.0001'*
                        ORA-27041: unable to open file
                        OSD-04002: unable to open file
                        O/S-Error: (OS 2) The system cannot find the file specified.


                        this is not the one which i copied ARC0000000001_0802361927.0001' so y system is trying to find this entry
                        • 9. Re: recover database
                          ShishirTekadeR
                          Recover Database :==Controlfile is old and you have restore your datafile from backup and your need to do a complete recovery.

                          Recover Database until cancel :==Controlfile is old and you have restore your datafile and you have to do a incomplete recovery of your database.

                          recover database using backup contorlfile until cancel :== Controlfile and datafile are restored from backup and you need to perform and incomplete recovery of your database.

                          SQL> recover database using backup controlfile until cancel;
                          ORA-00279: change 19957054 generated at 12/18/2012 14:18:50 needed for thread 1
                          ORA-00289: suggestion :
                          E:\ORACLE\APP\ORADATA\ORCL1\ARCHIVE\ARC0000000001_0802361927.0001
                          ORA-00280: change 19957054 for thread 1 is in sequence #1
                          Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

                          ==>You must open the database with the RESETLOGS option after performing either complete or point-in-time recovery with a backup control file.
                          ==>If the online redo logs are inaccessible, then you must perform incomplete recovery to an SCN before the earliest SCN in the online redo logs. This limitation is necessary because RMAN does not back up online logs.
                          ==>During recovery, RMAN automatically searches for online and archived redo logs that are not recorded in the RMAN repository, and catalogs any that it finds so that it can use them in recovery.

                          *****************************************
                          Best Regards,
                          Shishir Tekade.
                          http://shishirtekade.blogspot.com
                          • 10. Re: recover database
                            Osama_Mustafa
                            948024 wrote:
                            great thanks guys it really helped,


                            i recoverd the database completely i cross checked the rows which i inserted after taking the backup and they are in the new database


                            guys just one more thing.

                            now both the databses hav same data......

                            again if i want to insert rows in primary database and do

                            alter system archive log current;

                            then cn i directly paste that archive log into second database archvive folder and do shutdown immediate
                            startup mount

                            recover database using backup controlfile until cancel

                            or is there any other step in between, bcoz im getting an error
                            SQL> set logsource 'E:\oracle\app\oradata\orcl1\archive';
                            SQL> recover database using backup controlfile until cancel;
                            ORA-00279: change 19957054 generated at 12/18/2012 14:18:50 needed for thread 1
                            ORA-00289: suggestion :
                            E:\ORACLE\APP\ORADATA\ORCL1\ARCHIVE\ARC0000000001_0802361927.0001
                            ORA-00280: change 19957054 for thread 1 is in sequence #1


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

                            ORA-00308: cannot open archived log
                            'E:\ORACLE\APP\ORADATA\ORCL1\ARCHIVE\*ARC0000000001_0802361927.0001'*
                            ORA-27041: unable to open file
                            OSD-04002: unable to open file
                            O/S-Error: (OS 2) The system cannot find the file specified.


                            this is not the one which i copied ARC0000000001_0802361927.0001' so y system is trying to find this entry
                            You archive should be located in E:\ORACLE\APP\ORADATA\ORCL1\ARCHIVE\ are you sure you given the right path
                            ORA-00308:cannot open archived log 'string'
                            Cause: The system cannot access a required archived redo log file.
                            Action:Check that the off line log exists, the storage device is online, and the archived file is in the correct location. Then attempt to continue recovery or restart the recovery session.
                            • 11. Re: recover database
                              951027
                              yes the path is correct, i cross checked but that particular file never existed
                              ARC0000000001_0802361927.0001
                              • 12. Re: recover database
                                hitgon
                                Oracle Database Recovery sample documents
                                http://gavinsoorma.com/recovery-scenarios/
                                • 13. Re: recover database
                                  951027
                                  please check my alert log and advice wht could be done


                                  ALTER DATABASE MOUNT
                                  2012-12-20 09:01:30.523000 +04:00
                                  Successful mount of redo thread 1, with mount id 783359846
                                  Database mounted in Exclusive Mode
                                  Lost write protection disabled
                                  Completed: ALTER DATABASE MOUNT
                                  2012-12-20 09:01:41.646000 +04:00
                                  ALTER DATABASE RECOVER database using backup controlfile until cancel
                                  Media Recovery Start
                                  started logmerger process
                                  WARNING! Recovering data file 1 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 2 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 3 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 4 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 5 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 6 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 7 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  Parallel Media Recovery started with 2 slaves
                                  ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ...
                                  2012-12-20 09:02:02.131000 +04:00
                                  ALTER DATABASE RECOVER CANCEL
                                  Errors in file d:\oracle\app\diag\rdbms\orcl2\orcl2\trace\orcl2_pr00_3736.trc:
                                  ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                                  ORA-01194: file 1 needs more recovery to be consistent
                                  ORA-01110: data file 1: 'D:\ORACLE\APP\ORADATA\ORCL2\SYSTEM01.DBF'
                                  Slave exiting with ORA-1547 exception
                                  Errors in file d:\oracle\app\diag\rdbms\orcl2\orcl2\trace\orcl2_pr00_3736.trc:
                                  ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                                  ORA-01194: file 1 needs more recovery to be consistent
                                  ORA-01110: data file 1: 'D:\ORACLE\APP\ORADATA\ORCL2\SYSTEM01.DBF'
                                  ORA-10879 signalled during: ALTER DATABASE RECOVER CANCEL ...
                                  ALTER DATABASE RECOVER CANCEL
                                  Media Recovery Canceled
                                  Completed: ALTER DATABASE RECOVER CANCEL
                                  2012-12-20 09:02:04.596000 +04:00
                                  Checker run found 8 new persistent data failures
                                  2012-12-20 09:02:11.492000 +04:00
                                  ALTER DATABASE RECOVER database
                                  Media Recovery Start
                                  started logmerger process
                                  Media Recovery failed with error 1610
                                  Slave exiting with ORA-283 exception
                                  Errors in file d:\oracle\app\diag\rdbms\orcl2\orcl2\trace\orcl2_pr00_3900.trc:
                                  ORA-00283: recovery session canceled due to errors
                                  ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
                                  Recovery Slave PR00 previously exited with exception 283
                                  ORA-283 signalled during: ALTER DATABASE RECOVER database ...
                                  2012-12-20 09:02:20.447000 +04:00
                                  ALTER DATABASE RECOVER database using backup controlfile
                                  Media Recovery Start
                                  started logmerger process
                                  WARNING! Recovering data file 1 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 2 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 3 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 4 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 5 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 6 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  WARNING! Recovering data file 7 from a fuzzy file. If not the current file
                                  it might be an online backup taken without entering the begin backup command.
                                  Parallel Media Recovery started with 2 slaves
                                  ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile ...
                                  2012-12-20 09:02:38.747000 +04:00
                                  ALTER DATABASE RECOVER CANCEL
                                  Media Recovery Canceled
                                  Completed: ALTER DATABASE RECOVER CANCEL
                                  2012-12-20 09:03:09.778000 +04:00
                                  ALTER SYSTEM ARCHIVE LOG
                                  2012-12-20 09:03:17.516000 +04:00
                                  alter database open
                                  Errors in file d:\oracle\app\diag\rdbms\orcl2\orcl2\trace\orcl2_ora_3704.trc:
                                  ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
                                  ORA-1589 signalled during: alter database open...
                                  2012-12-20 09:03:25.301000 +04:00
                                  alter database open noresetlogs
                                  ORA-1588 signalled during: alter database open noresetlogs...
                                  2012-12-20 09:03:33.523000 +04:00
                                  alter database open resetlogs
                                  ORA-1113 signalled during: alter database open resetlogs...
                                  • 14. Re: recover database
                                    Osama_Mustafa
                                    WARNING! Recovering data file 7 from a fuzzy file. If not the current file
                                    http://www.peasland.net/?p=16
                                    1 2 Previous Next