Oracle DBA FAQ

Oracle RAC interview questions, Oracle Dataguard, ASM, CRS, Oracle wait events, Performance Tuning


    HowTo Restore RAC Database to Single Instance On Another Node

    Share

    Admin
    Admin

    Posts : 17
    Join date : 2010-02-08

    HowTo Restore RAC Database to Single Instance On Another Node

    Post  Admin on Thu Feb 11, 2010 4:46 am

    Take RMAN backup of the production RAC database..

    RMAN> run{
    allocate channel c1 type disk format '/tmp/%U';
    backup database;
    backup archivelog all;
    }

    - Create a PFILE for the single instance database using the production RAC parameter file

    And modify the parameters %dest, control_files, log_archive_dest_1, %convert, cluster_database_instances, cluster_database etc.. for undo_tablespace, mention any one undo tablespace name

    - Copy the backup pieces and the modified INIT.ORA file to the new host to same mount point.

    - Use the pfile created above to STARTUP NOMOUNT the database on the new host

    $ sqlplus "/ as sysdba"

    SQL> startup nomount;

    $ rman target / nocatalog

    RMAN> restore controlfile from '/tmp/< backup piece name of controlfile auto backup>';

    RMAN> alter database mount;

    - Determine the recovery point.

    RMAN> list backup of archivelog all;

    Check the last archive sequence for all redo threads and select the archive sequence having LEAST "Next SCN" among them.

    - Having determined the point upto which media recovery should run, start the restore/recovery using:

    RMAN> run {
    set until sequence < sequence# from above> thread < thread# >;
    restore database;
    recover database;
    }

    SQL> alter database open resetlogs;

    If open database fail with error ORA-38856
    then, Set the following parameter in the init.ora file:

    _no_recovery_through_resetlogs=TRUE

    Then open with resetlogs.
    Once the database has opened, removed this hidden parameter.

    - Once the database is opened successfully, you may remove the redolog groups for redo threads of other instances.

    SQL> select THREAD#, STATUS, ENABLED
    2 from v$thread;

    THREAD# STATUS ENABLED
    ---------- ------ --------
    1 OPEN PUBLIC
    2 CLOSED PRIVATE

    SQL> select group# from v$log where THREAD#=2;

    GROUP#
    ----------
    4
    5
    6

    SQL> alter database disable thread 2;

    Database altered.

    SQL> alter database clear unarchived logfile group 4; ( repeat for 4 to 6)

    Database altered.

    SQL> alter database drop logfile group 4; ( repeat for 4 to 6)

    Database altered.

    - Now you can remove the undo tablespaces of other instances.

    SQL> show parameter undo;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management string AUTO
    undo_retention integer 900
    undo_tablespace string UNDOTBS1

    SQL> select tablespace_name from dba_tablespaces where contents='UNDO';

    TABLESPACE_NAME
    ------------------------------
    UNDOTBS1
    UNDOTBS2

    SQL> drop tablespace UNDOTBS2 including contents and datafiles;

    Tablespace dropped.

      Current date/time is Fri May 26, 2017 8:50 am