Forgot your password?    
+ Reply to Thread
Results 1 to 3 of 3

Thread: RMAN All major Restoration and Recovery Scenarios

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427

    RMAN All major Restoration and Recovery Scenarios

    1. Complete Closed Database Recovery.

    - It is assumed that your control files are still accessible.
    - You have a backup, done for example with backup database plus archivelog;

    i) Your first step is to make sure that the target database is shut down:
    $ sqlplus “/ as SYSDBA”
    SQL> shutdown abort;
    ORACLE instance shut down.

    ii) Next, you need to start up your target database in mount mode.

    - RMAN cannot restore datafiles unless the database is at least in mount mode, because RMAN needs to be able to access the control file to determine which backup sets are necessary to recover the database.

    - If the control file isn't available, you have to recover it first. Issue the STARTUP MOUNT command shown in the following example to mount the database:

    SQL> startup mount;
    Oracle instance started.
    Database mounted.

    iii) Use RMAN to restore the database and recover the database.
    To use RMAN, connect to the target database:
    $ rman target / catalog rman/rman@rmancat

    - When the restore command is executed, RMAN will automatically go to its last good backup set and restore the datafiles to the state they were in when that backup set was created.

    - When restoring database files, RMAN reads the datafile header and makes the determination as to whether the file needs to be restored. The recovery is done by allocating a channel for I/O and then issuing the RMAN restore database command.

    you don't need to allocate a channel explicitly. Instead, you can use the default channel mode:

    RMAN> restore database;
    RMAN> recover database;
    SQL> alter database open;


    2. System tablespace is missing

    In this case complete recovery is performed, only the system tablespace is missing, so the database can be opened without resetlogs option.

    $ rman target /
    RMAN> startup mount;
    RMAN> restore tablespace SYSTEM;
    RMAN> recover tablespace SYSTEM;
    RMAN> alter database open;




    3. Complete Open Database Recovery. Non system tablespace is missing, database is up

    $ rman target /
    RMAN> sql ‘alter tablespace <tbs> offline immediate’ ;
    RMAN> restore tablespace <tbs> ;
    RMAN> recover tablespace <tbs> ;
    RMAN> sql ‘alter tablespace <tbs> online’ ;

    To restore/recover only datafile(s)
    $ rman target /

    RMAN>. sql 'alter database datafile <file#> offline';
    RMAN> restore datafile <file#>;
    RMAN> recover datafile <file#>;
    RMAN> sql 'alter database datafile <file#> online' ;

    Note: datafile_name(within single quotes) can also be used instead of file#




    4.Complete Open Database Recovery (when the database is initially closed). Non system tablespace is missing

    A user datafile is reported missing when trying to startup the database. The datafile can be turned offline and the database started up. Restore and recovery are performed using Rman. After recovery is performed the datafile can be turned online again.

    sqlplus “/ as sysdba “
    startup mount
    alter database datafile <file#> offline;
    alter database open;
    exit;

    $rman target /
    RMAN> restore datafile <file#>;
    RMAN> recover datafile <file#>;
    RMAN> sql 'alter tablespace <tablespace_name> online';

    Note: datafile_name(within single quotes) can also be used instead of file#




    5.To restore a tablespace to a new location


    $ rman target / catalog rman/rman@rcat

    Take the tablespace offline.
    Specify an accessible location to which you can restore the damaged datafile for the offline tablespace.

    Restore the datafile to the new location.
    Switch the restored datafile so that the control file considers it the current datafile.
    To restore the datafiles for tablespace USERS to a new location on disk:

    run {
    allocate channel ch1 type disk;
    sql 'ALTER TABLESPACE USERS OFFLINE IMMEDIATE';
    set newname for datafile '/disk1/oracle/users_1.dbf' to '/disk2/oracle/users_1.dbf';
    restore tablespace users;
    # make the control file recognize the restored file as current
    switch datafile all;
    }

    RMAN> recover tablespace USERS;
    RMAN> sql 'alter tablespace USERS online';



    6. Recovery of a Datafile that has no backups (database is up)

    If a non system datafile that was not backed up since the last backup, is missing, recovery can be performed if all archived logs since the creation of the missing datafile exist. Since the database is up you can check the tablespace name and put it offline. The option offline immediate is used to avoid that the update of the datafile header.
    Pre requisites: All relevant archived logs.

    $ rman target /
    RMAN> sql ‘alter database datafile <file#> offline’;
    RMAN> restore datafile <file#> ;
    -- no need to create a blank file, restore command takes care of that.
    RMAN> recover datafile <file#>;
    RMAN> sql 'alter database datafile <file#> online';

    Note: datafile_name(within single quotes) can also be used instead of file#




    7. Control File Recovery

    Case-1 – Autobackup is available

    Always multiplex your controlfiles. If you loose only one controlfile you can replace it with the one you have in place, and startup the Database. If both controlfiles are missing, the database will crash.
    Pre requisites: A backup of your controlfile and all relevant archived logs. When using Rman alway set configuration parameter autobackup of controlfile to ON.

    rman target /
    RMAN> startup nomount;
    RMAN> restore controlfile from autobackup;
    RMAN> alter database mount;
    RMAN> recover database;
    RMAN> alter database open resetlogs;

    Make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

    Case-2 – Autobackup is not available but controlfile backupset is available

    rman target /
    RMAN> startup nomount;
    RMAN> restore controlfile from <backupset_location>;
    RMAN> alter database mount;
    RMAN> restore database; --required if datafile(s) have been added after the backup
    RMAN> recover database;
    RMAN> alter database open resetlogs;

    Make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

    Case-3 – If no backup is available, create the controlfile manually using script and then recover as given above.

    Note: RMAN automatically searches in specific locations for online and archived redo logs during recovery that are not recorded in the RMAN repository, and catalogs any that it finds. RMAN attempts to find a valid archived log in any of the current archiving destinations with the current log format. The current format is specified in the initialization parameter file used to start the instance (or all instances in a Real Application Clusters installation). Similarly, RMAN attempts to find the online redo logs by using the filenames as specified in the control file.




    8. Incomplete Recovery, Until time/sequence/scn

    Incomplete recovery may be necessary when the database crashes and needs to be recovered, and in the recovery process you find that an archived log is missing. In this case recovery can only be made until the sequence before the one that is missing. Another scenario for incomplete recovery occurs when an important object was dropped or incorrect data was committed on it. In this case recovery needs to be performed until before the object was dropped.
    Pre requisites: A full closed or open database backup and archived logs, the time or sequence that the 'until' recovery needs to be performed.

    If the database is open, shutdown it to perform full restore.

    shutdown abort
    startup nomount
    =============================
    $ rman target / rcvcat rman/rman@rcat
    RMAN> run {
    set until time "to_date('2012/01/23 16:00:00',
    'YYYY/MM/DD HH14:MI:SS')";
    allocate channel d1 type disk;
    restore controlfile to '/tmp/cf';
    replicate controlfile from '/tmp/cf';
    sql 'alter database mount';
    restore database;
    recover database;
    }

    Make a new complete backup, as the database is opened in new incarnation and previous archive logs are not relevant.




    9. Recovering After the Loss of All Members of an Online Redo Log Group

    If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.

    If the damaged log group is inactive, then it is not needed for crash recovery; if it is active, then it is needed for crash recovery.
    SQL> startup mount

    Case-1 If the group is INACTIVE
    Then it is not needed for crash recovery
    Clear the archived or unarchived group. (For archive status, check in v$log)

    1.1 Clearing Inactive, Archived Redo

    alter database clear logfile group 1 ;
    alter database open ;

    1.2 Clearing Inactive, Not-Yet-Archived Redo

    alter database clear unarchived logfile group 1 ;
    OR
    (If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. )

    alter database clear unarchived logfile group 1 unrecoverable datafile;

    Take a complete backup of database.

    And now open database:
    alter database open ;

    Case-2 If the group is ACTIVE

    Restore backup and perform an incomplete recovery.
    And open database using resetlogs
    alter database open resetlogs;

    Case-3 If the group is CURRENT

    Restore backup and perform an incomplete recovery.
    And open database using resetlogs
    alter database open resetlogs;




    10. Restoring database to new host from RMAN backup

    1) Restore database backup and archive log backup(if hot) to target server.
    2) Copy ORACLE_HOME from source to target if its not already there.
    3) If you dont have a controlfile backup which was taken after the cold backup then take a control file backup on source.

    RMAN> backup current controlfile to '<path/filename.ctl>';
    or
    SQL> alter database backup controlfile to '<path/filename.ctl>';

    4) Copy this controlfile backup to target node

    5) On target:
    Create pfile or copy it from target and change following parameters:

    IFILE
    *_DUMP_DEST
    LOG_ARCHIVE_DEST
    CONTROL_FILES

    $ export NLS_DATE_FORMAT=”yyyy-mm-dd hh24:mi”
    $ rman target /
    RMAN> sql ‘create spfile from pfile’ ;
    RMAN> startup nomount ;
    RMAN> restore controlfile from '<PATH>/filename.ctl>' ;
    RMAN> alter database mount ;
    RMAN> list backup ; - Note the scn number or time of the backup you want to restore

    $ rman target /
    RMAN> restore database until time ‘<date/time>’ ;
    OR
    RMAN> restore database until scn <scn_number> ;
    OR
    RMAN> restore database from tag ‘<tag_name>’ ;

    And now…
    RMAN> recover database;
    RMAN> alter database open resetlogs ;

    Note: Above method can also be used where you want to restore database from old backups instead of latest one.



    11. Restoring backups from tape.

    Use the following steps to restore backups from tape.

    $ export NLS_DATE_FORMAT=”yyyy-mm-dd hh24:mi”

    RMAN> list backup ; -- Note the scn or time of the backup you want to restore.

    RMAN> run{
    allocate channel t1 type 'SBT_TAPE' parms="ENV=(NB_ORA_CLIENT=ibm5003bk)";
    restore database until scn <scn_number>; --scn number as in list backup output
    recover database ;
    }

    Notes:
    1) until scn can be used with recover command as well for incomplete recovery.
    Other option is to use set until within run block just before restore.
    2) from tag ‘<tag_name>’ can also be used (instead of until clause) with restore command

  2. #2
    Thanks Sir....

  3. #3

    How to recover and open the database if the archive log required for recovery is miss

    Last week, I had one interesting recovery scenario at client side; we had to recover one of our development databases from old backup.
    As part of recovery process, our restore went fine and also were able to re-create controlfile. During recovery, it asked for Archive logs. We checked with our Unix team for required archivelogs and found out they don’t have required archive logs.

    It was critical for us to recover database because of some project deadline.

    Error:

    SQL> recover database until cancel using backup controlfile;
    ORA-00279: change 9867098396261 generated at 03/21/2008 13:37:44 needed for
    thread 1
    ORA-00289: suggestion : /arcredo/XSCLFY/log1_648355446_2093.arc
    ORA-00280: change 9867098396261 for thread 1 is in sequence #2093

    Specify log: {=suggested | filename | AUTO | CANCEL}
    cancel
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01195: online backup of file 1 needs more recovery to be consistent
    ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’
    ORA-01112: media recovery not started

    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-01195: online backup of file 1 needs more recovery to be consistent
    ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’

    After doing some research, I found out one hidden parameter (_ALLOW_RESETLOGS_CORRUPTION=TRUE) will allow us to open database even though it’s not properly recovered.

    We forced open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE. It allows us to open database but instance crashed immediately after open. I checked the alert.log file and found out we have undo tablespace corruption.

    Alert log shows below error

    Errors in file /u01/XSCLFYDB/admin/XSCLFY/udump/xsclfy_ora_9225.trc:
    ORA-00600: internal error code, arguments: [4194], [17], [9], [], [], [], [], []
    Tue Mar 25 12:45:55 2008
    Errors in file /u01/XSCLFYDB/admin/XSCLFY/bdump/xsclfy_smon_24975.trc:
    ORA-00600: internal error code, arguments: [4193], [53085], [50433], [], [], [], [], []
    Doing block recovery for file 433 block 13525
    Block recovery from logseq 2, block 31 to scn 9867098416340

    To resolve undo corruption issue, I changed undo_management to “Manual” in init.ora. Now it allowed us to open database successfully. Once database was up and running, I created new undo tablespace and dropped old corrupted undo tablespace. I changed back the undo_management to “Auto” and undo_tablespace to “NewUndoTablespace”.

    It resolved our issue and database was up and running without any issue.

    _ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated.

    As per Oracle Metalink, there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all. Before you try this option, ensure that you have a good and valid backup of the current database.Solution:

    1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
    2) Startup Mount
    3) Recover database
    4) Alter database open resetlogs.
    5) reset undo_management to “manual” in init.ora file.
    6) startup database
    7) Create new undo tablespace
    changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”
    9) Bounce database.

    Regards,
    Saurabh Uniyal

+ Reply to Thread

Similar Threads

  1. RECOVERY SCENARIO: Complete loss of all database files including SPFILE using RMAN
    By devesh_ocp in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 04-25-2012, 10:26 AM
  2. Major Oracle Database Init Parameters 10G
    By Hemant in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 11-12-2011, 01:13 PM
  3. Srvctl Major Options and Usage in Oracle RAC
    By Hemant in forum RAC Concepts and Administration
    Replies: 0
    Last Post: 11-04-2011, 06:14 PM
  4. Backup & Restoration of Control Files using RMAN
    By sandeep.negi27 in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 1
    Last Post: 11-03-2011, 01:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics