8 Replies Latest reply: Nov 29, 2012 10:17 AM by mseberg RSS

    Recoverying a DB from full bkp in another host

    KeenOnOracle
      Hi Friends

      I got a simple question but I'm not being successfully on this procedure
      I have a incremental Level0 Backup containing all Database. I have this backup set stored on a remote host, for security.

      Now, my production instance has failed due to hw problems. I lost all my DB.

      How can I recover the DB from this level0 full bkp in another machine?

      They both had exactly the same architecture , so versions and DB versions

      11.2.0.3.0 Windows Server

      Tks for any tips
        • 1. Re: Recoverying a DB from full bkp in another host
          mseberg
          Hello;

          Yes you can. You don't even need a catalog to do this. ( You probably need a dummy parameter file )

          For example you can script this like this :
          CONNECT TARGET / 
          STARTUP NOMOUNT;
          
           
          RUN
           {
             ALLOCATE CHANNEL c1 DEVICE TYPE disk format '/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03';
             RESTORE CONTROLFILE from '/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncnnf_TAG20120903T190059_84bkn5kq_.bkp';
             ALTER DATABASE MOUNT;  
          
             RESTORE DATABASE;
             RECOVER DATABASE;
             release channel c1;
           }
          ALTER DATABASE OPEN RESETLOGS; 
           
           
          exit
          EOF
           
          Great example here without the new host :

          http://hemantoracledba.blogspot.com/2009/04/rman-backup-and-recovery-for-loss-of.html

          Another example :

          http://mmmustafa123.blogspot.com/2011/11/restore-rman-full-backup-to-different_22.html

          Old but good :

          http://shahiddba.blogspot.com/2012/05/rman-disaster-recovery-from-scratch.html

          Best Regards

          mseberg
          • 2. Re: Recoverying a DB from full bkp in another host
            KeenOnOracle
            mseberg,
            tks a lot, friend.

            I'll now get deeper and deeper on testing your advise.
            Tomorrow I post back the results. Gotta lot of work and a big DB to restore now!!!

            Cheers
            • 3. Re: Recoverying a DB from full bkp in another host
              Shivananda Rao
              Hi Fernando,

              You can refer this http://shivanandarao.wordpress.com/2012/04/19/duplicating-primary-database-to-a-new-host-without-connecting-to-the-primary-database-in-oracle-10g11g/

              Hope it helps you !!
              • 4. Re: Recoverying a DB from full bkp in another host
                KeenOnOracle
                Hi Shivananda Rao ...

                tks for your and any other tips.

                I got stuck in at the end of the process.
                See below:

                RMAN> restore controlfile from 'c:\ti\contol.ctl';
                Starting restore at 29-NOV-12
                using channel ORA_DISK_1

                channel ORA_DISK_1: copied control file copy
                output file name=C:\APP\ADMINISTRATOR\ORADATA\IZZOA\CONTROL01.CTL
                output file name=C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\IZZOA\CONTROL02.CTL

                Finished restore at 29-NOV-12

                RMAN> alter database mount;
                database mounted

                released channel: ORA_DISK_1

                RMAN> crosscheck backup;
                Starting implicit crosscheck backup at 29-NOV-12
                allocated channel: ORA_DISK_1
                channel ORA_DISK_1: SID=18 device type=DISK
                Crosschecked 9 objects
                Finished implicit crosscheck backup at 29-NOV-12

                Starting implicit crosscheck copy at 29-NOV-12
                using channel ORA_DISK_1
                Crosschecked 2 objects
                Finished implicit crosscheck copy at 29-NOV-12

                searching for all files in the recovery area
                cataloging files...
                no files cataloged

                using channel ORA_DISK_1
                crosschecked backup piece: found to be 'EXPIRED'
                backup piece handle=\\177.71.192.222\BACKUP$\0JNOD0V9_1_1.BAK RECID=4 STAMP=797344746
                crosschecked backup piece: found to be 'EXPIRED'
                backup piece handle=\\177.71.192.222\BACKUP$\0LNOD1HJ_1_1.BAK RECID=6 STAMP=797345333
                crosschecked backup piece: found to be 'EXPIRED'
                backup piece handle=\\177.71.197.49\TESTE$\1SNQS4HJ_1_1.BAK RECID=37 STAMP=799937076
                crosschecked backup piece: found to be 'EXPIRED'
                backup piece handle=\\177.71.197.49\TESTE$\1VNQUC0D_1_1.BAK RECID=39 STAMP=800010254
                crosschecked backup piece: found to be 'EXPIRED'
                backup piece handle=C:\APP\ADMINISTRADOR\FAST_RECOVERY_AREA\IZZOA\AUTOBACKUP\2012_11_22\O1_MF_S_800010425_8BW1TSC6_.BKP RECID=40 STAMP=800010425
                crosschecked backup piece: found to be 'EXPIRED'
                backup piece handle=\\177.71.197.49\TESTE$\22NRC035_1_1.BAK RECID=41 STAMP=800456806
                crosschecked backup piece: found to be 'EXPIRED'
                backup piece handle=\\177.71.197.49\TESTE$\25NRER60_1_1.BAK RECID=42 STAMP=800550086
                crosschecked backup piece: found to be 'EXPIRED'
                backup piece handle=\\177.71.197.49\TESTE$\28NREU92_1_1.BAK RECID=43 STAMP=800553252
                crosschecked backup piece: found to be 'EXPIRED'
                backup piece handle=C:\APP\ADMINISTRADOR\FAST_RECOVERY_AREA\IZZOA\AUTOBACKUP\2012_11_28\O1_MF_S_800554065_8CDNQL98_.BKP RECID=44 STAMP=800554066
                Crosschecked 9 objects

                RMAN> delete expired backup;
                using channel ORA_DISK_1
                List of Backup Pieces
                BP Key BS Key Pc# Cp# Status Device Type Piece Name
                ------- ------- --- --- ----------- ----------- ----------
                4 4 1 1 EXPIRED DISK \\177.71.192.222\BACKUP$\0JNOD0V9_1_1.BAK
                6 6 1 1 EXPIRED DISK \\177.71.192.222\BACKUP$\0LNOD1HJ_1_1.BAK
                37 37 1 1 EXPIRED DISK \\177.71.197.49\TESTE$\1SNQS4HJ_1_1.BAK
                39 39 1 1 EXPIRED DISK \\177.71.197.49\TESTE$\1VNQUC0D_1_1.BAK
                40 40 1 1 EXPIRED DISK C:\APP\ADMINISTRADOR\FAST_RECOVERY_AREA\IZZOA\AUTOBACKUP\2012_11_22\O1_MF_S_800010425_8BW1TSC6_.BKP
                41 41 1 1 EXPIRED DISK \\177.71.197.49\TESTE$\22NRC035_1_1.BAK
                42 42 1 1 EXPIRED DISK \\177.71.197.49\TESTE$\25NRER60_1_1.BAK
                43 43 1 1 EXPIRED DISK \\177.71.197.49\TESTE$\28NREU92_1_1.BAK
                44 44 1 1 EXPIRED DISK C:\APP\ADMINISTRADOR\FAST_RECOVERY_AREA\IZZOA\AUTOBACKUP\2012_11_28\O1_MF_S_800554065_8CDNQL98_.BKP

                Do you really want to delete the above objects (enter YES or NO)? yes
                deleted backup piece
                backup piece handle=\\177.71.192.222\BACKUP$\0JNOD0V9_1_1.BAK RECID=4 STAMP=797344746
                deleted backup piece
                backup piece handle=\\177.71.192.222\BACKUP$\0LNOD1HJ_1_1.BAK RECID=6 STAMP=797345333
                deleted backup piece
                backup piece handle=\\177.71.197.49\TESTE$\1SNQS4HJ_1_1.BAK RECID=37 STAMP=799937076
                deleted backup piece
                backup piece handle=\\177.71.197.49\TESTE$\1VNQUC0D_1_1.BAK RECID=39 STAMP=800010254
                deleted backup piece
                backup piece handle=C:\APP\ADMINISTRADOR\FAST_RECOVERY_AREA\IZZOA\AUTOBACKUP\2012_11_22\O1_MF_S_800010425_8BW1TSC6_.BKP RECID=40 STAMP=800010425
                deleted backup piece
                backup piece handle=\\177.71.197.49\TESTE$\22NRC035_1_1.BAK RECID=41 STAMP=800456806
                deleted backup piece
                backup piece handle=\\177.71.197.49\TESTE$\25NRER60_1_1.BAK RECID=42 STAMP=800550086
                deleted backup piece
                backup piece handle=\\177.71.197.49\TESTE$\28NREU92_1_1.BAK RECID=43 STAMP=800553252
                deleted backup piece
                backup piece handle=C:\APP\ADMINISTRADOR\FAST_RECOVERY_AREA\IZZOA\AUTOBACKUP\2012_11_28\O1_MF_S_800554065_8CDNQL98_.BKP RECID=44 STAMP=800554066
                Deleted 9 EXPIRED objects


                RMAN> catalog backuppiece 'c:\ti\28NREU92_1_1.BAK';

                cataloged backup piece
                backup piece handle=C:\TI\28NREU92_1_1.BAK RECID=45 STAMP=800627098

                RMAN> RESTORE DATABASE;

                Starting restore at 29-NOV-12
                using channel ORA_DISK_1

                channel ORA_DISK_1: starting datafile backup set restore
                channel ORA_DISK_1: specifying datafile(s) to restore from backup set
                channel ORA_DISK_1: restoring datafile 00001 to C:\APP\ADMINISTRADOR\ORADATA\IZZOA\SYSTEM01.DBF
                channel ORA_DISK_1: restoring datafile 00002 to C:\APP\ADMINISTRADOR\ORADATA\IZZOA\SYSAUX01.DBF
                channel ORA_DISK_1: restoring datafile 00003 to C:\APP\ADMINISTRADOR\ORADATA\IZZOA\UNDOTBS01.DBF
                channel ORA_DISK_1: restoring datafile 00004 to C:\APP\ADMINISTRADOR\ORADATA\IZZOA\USERS01.DBF
                channel ORA_DISK_1: restoring datafile 00005 to C:\APP\ADMINISTRADOR\ORADATA\IZZOA\INDX.DBF
                channel ORA_DISK_1: restoring datafile 00006 to C:\APP\ADMINISTRADOR\ORADATA\IZZOA\FOTOS.DBF
                channel ORA_DISK_1: restoring datafile 00007 to C:\APP\ADMINISTRADOR\ORADATA\IZZOA\DOCS.DBF
                channel ORA_DISK_1: restoring datafile 00008 to C:\APP\ADMINISTRADOR\ORADATA\IZZOA\BB.DBF
                channel ORA_DISK_1: restoring datafile 00009 to C:\APP\ADMINISTRADOR\ORADATA\IZZOA\PB.DBF
                channel ORA_DISK_1: restoring datafile 00010 to C:\APP\ADMINISTRADOR\ORADATA\IZZOA\RB.DBF
                channel ORA_DISK_1: restoring datafile 00011 to C:\TESTETABLE.DBF
                channel ORA_DISK_1: reading from backup piece C:\TI\28NREU92_1_1.BAK
                channel ORA_DISK_1: piece handle=C:\TI\28NREU92_1_1.BAK tag=TAG20121128T155410
                channel ORA_DISK_1: restored backup piece 1
                channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
                Finished restore at 29-NOV-12

                RMAN> recover database;

                Starting recover at 29-NOV-12
                using channel ORA_DISK_1

                starting media recovery

                unable to find archived log
                archived log thread=1 sequence=790
                RMAN-00571: ===========================================================
                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                RMAN-00571: ===========================================================
                RMAN-03002: failure of recover command at 11/29/2012 12:27:12
                RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq
                uence 790 and starting SCN of 5097339

                RMAN> alter database open resetlogs;

                RMAN-00571: ===========================================================
                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                RMAN-00571: ===========================================================
                RMAN-03002: failure of alter db command at 11/29/2012 12:29:10
                ORA-01194: file 1 needs more recovery to be consistent
                ORA-01110: data file 1: 'C:\APP\ADMINISTRADOR\ORADATA\IZZOA\SYSTEM01.DBF'


                RMAN is asking for a archive log that I dont have anymore. It might had be generated after the backup level 0 I'm trying to recover. But, I dont want it, I want to open the DB without having this content that actually I dont have.
                Does not matter the data lost after tha level 0 backup.

                Is that possible?

                Even copying the archives still the same error, but the idea is to not need them to open the DB.

                tks a lot
                • 5. Re: Recoverying a DB from full bkp in another host
                  mseberg
                  You can try this :
                  recover database until cancel using backup controlfile;
                  /*Then enter "auto"*/
                  auto
                  alter database open resetlogs;
                  OR
                  RMAN> run {
                   set until sequence <your_sequence_number>;
                   recover database;
                   }
                  If you are missing archive you may have a problem that cannot be solved.

                  Best Regards

                  mseberg

                  Edited by: mseberg on Nov 29, 2012 8:56 AM
                  • 6. Re: Recoverying a DB from full bkp in another host
                    Rajesh.Rathod
                    Hi,

                    how you are sure that 'c:\ti\contol.ctl'; this is your latest backup controlfile ?

                    kindly get latest controlfile backup for recover.
                    • 7. Re: Recoverying a DB from full bkp in another host
                      KeenOnOracle
                      Friends...

                      Below are the results.

                      recover database until cancel using backup controlfile;

                      RMAN-00571: ===========================================================
                      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                      RMAN-00571: ===========================================================
                      RMAN-00558: error encountered while parsing input commands
                      RMAN-01009: syntax error: found "cancel": expecting one of: "scn, sequence, time
                      "
                      RMAN-01007: at line 1 column 24 file: standard input

                      RMAN> recover database until scn 5097339;

                      Starting recover at 29-NOV-12
                      using channel ORA_DISK_1

                      starting media recovery
                      Oracle Error:
                      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: 'C:\APP\ADMINISTRADOR\ORADATA\IZZOA\SYSTEM01.DBF'

                      media recovery complete, elapsed time: 00:00:00

                      Finished recover at 29-NOV-12

                      RMAN> alter database open resetlogs;

                      RMAN-00571: ===========================================================
                      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                      RMAN-00571: ===========================================================
                      RMAN-03002: failure of alter db command at 11/29/2012 14:02:06
                      ORA-01194: file 1 needs more recovery to be consistent
                      ORA-01110: data file 1: 'C:\APP\ADMINISTRADOR\ORADATA\IZZOA\SYSTEM01.DBF'


                      the bkp did not work well?

                      tks
                      • 8. Re: Recoverying a DB from full bkp in another host
                        mseberg
                        Hello again;

                        You can try to run Data Recovery Advisor


                        In RMAN run these commands : ( In the order shown )

                        RMAN> list failure;

                        RMAN> advise failure;

                        RMAN> spool log to rmanrecover.out

                        RMAN> repair failure preview;


                        Then check the spool file and see what if says.

                        Best Regards

                        mseberg