This discussion is archived
8 Replies Latest reply: Nov 29, 2012 8:17 AM by mseberg RSS

Recoverying a DB from full bkp in another host

KeenOnOracle Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points