RMAN Restore from Tape to Diff Host DBName on ASM

A little bit busy this week … my boss ask me to validate the RMAN tape backup if we can really restore it.

Note : restore database validate is not an option 🙂

Source and target are both clustered database.

Source DB: CNCXXU
Source Server : ncxxdsmwu01/ncxxdsmwu02

Target DB: NCXX2PT
Target Server : ncxxdsgtu21/ncxxdsgtu22

1. Shutdown the target database and delete all files in ASM
2. Start the database in nomount and restore the spfile.
– spfile and controlfile backup is going to disk.
– no rman catalog

        export ORACLE_SID=NCXX2PT1
        $ echo $ORACLE_SID
        NCXX2PT1
        $ rman target /
        
        Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 17 03:29:22 2016
        
        Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
        
        connected to target database (not started)
        
        RMAN> startup force nomount; <<-- required when restoring spfile from backup
        
        startup failed: ORA-01078: failure in processing system parameters
        LRM-00109: could not open parameter file '/optware/oracle/11.2.0.4/db_1/dbs/initNCXX2PT1.ora' <<-- you can ignore this
        
        starting Oracle instance without parameter file for retrieval of spfile
        Oracle instance started
        
        Total System Global Area    1068937216 bytes
        
        Fixed Size                     2260088 bytes
        Variable Size                364905352 bytes
        Database Buffers             692060160 bytes
        Redo Buffers                   9711616 bytes
        
        RMAN>

	RMAN>  restore spfile to pfile '/tmp/xxx.ora' from '/backup/TapeBackupValidation/CNCXXU1/CNCXXU_c-1304770496-20151231-01.rman';
	
	Starting restore at 14-JAN-16
	using target database control file instead of recovery catalog
	allocated channel: ORA_DISK_1
	channel ORA_DISK_1: SID=1765 device type=DISK
	
	channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/TapeBackupValidation/CNCXXU1/CNCXXU_c-1304770496-20151231-01.rman
	channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
	Finished restore at 14-JAN-16
	
	RMAN> shutdown immediate;
	
	Oracle instance shut down
	
	RMAN> exit

Make sure to edit the /tmp/xxx.ora change anything related to source database.
-change the db_unique_name to the name of target 
-set the cluster database to false.

        SQL> startup nomount;
	ORACLE instance started.
	
	Total System Global Area 3.2068E+10 bytes
	Fixed Size                  2269072 bytes
	Variable Size            5301600368 bytes
	Database Buffers         2.6709E+10 bytes
	Redo Buffers               55242752 bytes
	SQL>

	SQL> create spfile='+DATA/NCXX2PT/spfileNCXX2PT.ora' from pfile='/tmp/xxx.ora';
	
	File created.
	
	SQL> shutdown immediate;
	ORA-01507: database not mounted
	ORACLE instance shut down.
	
Before starting create the $ORACLE_HOME/dbs/init.ora make sure it points to the spfile in ASM.
	

	SQL> startup nomount;
	ORACLE instance started.
	
	Total System Global Area 3.2068E+10 bytes
	Fixed Size                  2269072 bytes
	Variable Size            5301600368 bytes
	Database Buffers         2.6709E+10 bytes
	Redo Buffers               55242752 bytes
	SQL>

3.Restore the controlfile and mount the database.

	RMAN> restore controlfile from '/backup/TapeBackupValidation/CNCXXU1/CNCXXU_1304770496_ctl_logs_only_20160114.0000_513951.rman';
	
	Starting restore at 14-JAN-16
	using channel ORA_DISK_1
	
	channel ORA_DISK_1: restoring control file
	channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
	output file name=+DATA/ncxx2pt/controlfile/control_01.ctl
	output file name=+FRA/ncxx2pt/controlfile/control_02.ctl
	Finished restore at 14-JAN-16
	
	RMAN> mount database;
	
	database mounted
	released channel: ORA_DISK_1
	
	RMAN>

    connected to target database: CNCXXU (DBID=1304770496, not open)
    RMAN>

4. Now list all the datafile and tempfiles recorded in controlfile. we need this files for our set new name command.

    RMAN> report schema;

    using target database control file instead of recovery catalog
    RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
    Report of database schema for database with db_unique_name NCXX2PT
    
    List of Permanent Datafiles
    ===========================
    File Size(MB) Tablespace           RB segs Datafile Name
    ---- -------- -------------------- ------- ------------------------
    1    0        SYSTEM               ***     +DATA/cncxxu/datafile/system.428.826288667
    2    0        SYSAUX               ***     +DATA/cncxxu/datafile/sysaux.309.826288671
    3    0        UNDOTBS1             ***     +DATA/cncxxu/datafile/undotbs1.314.826288679
    ........
    130  0        DATA_GBL             ***     +DATA/cncxxu/datafile/data_gbl.661.898694109
    131  0        DATA_GBL             ***     +DATA/cncxxu/datafile/data_gbl.662.898694265
    132  0        DATA_GBL             ***     +DATA/cncxxu/datafile/data_gbl.663.899755195
    
    List of Temporary Files
    =======================
    File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
    ---- -------- -------------------- ----------- --------------------
    1    2000     TEMP                 2000        +DATA/cncxxu/tempfile/temp.313.826288685
    2    100      TEMP                 9140        +DATA/cncxxu/tempfile/temp.458.836492973
    3    100      TEMP                 9140        +DATA/cncxxu/tempfile/temp.488.850751285
    4    2048     TEMP                 8192        +DATA/cncxxu/tempfile/temp.607.881844109
    5    30720    TEMP                 30720       +DATA/cncxxu/tempfile/temp.608.881854115
    
    RMAN> EXIT

5.Now let start the DB restore.. before doing the restore make sure to ask your netbackup team if redirected restore is allowed.

    RMAN>run {
    ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
    ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
    ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
    ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
    ALLOCATE CHANNEL ch04 TYPE 'SBT_TAPE';
    ALLOCATE CHANNEL ch05 TYPE 'SBT_TAPE';
    ALLOCATE CHANNEL ch06 TYPE 'SBT_TAPE';
    ALLOCATE CHANNEL ch07 TYPE 'SBT_TAPE';
    send 'NB_ORA_CLIENT=ncxxdsmwu01'; <<--- this is the source database server.
    send 'NB_ORA_SERV=ncxxm2400'; <<-- this the netbackup master server.
    set newname for datafile 1   to '+DATA';
    set newname for datafile 2   to '+DATA';
    .......
    set newname for datafile 130 to '+DATA';
    set newname for datafile 131 to '+DATA';
    set newname for datafile 132 to '+DATA';
    set newname for tempfile 1 to '+DATA';
    set newname for tempfile 2 to '+DATA';
    set newname for tempfile 3 to '+DATA';
    set newname for tempfile 4 to '+DATA';
    set newname for tempfile 5 to '+DATA';
    restore database;
    switch datafile all;
    recover database;
    }

Below is the log of my rman session… but need to remove some to shorten it.

    RMAN> run {
    ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
    2> 3> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
    4> ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
    5> ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
    6> ALLOCATE CHANNEL ch04 TYPE 'SBT_TAPE';
    7> ALLOCATE CHANNEL ch05 TYPE 'SBT_TAPE';
    8> ALLOCATE CHANNEL ch06 TYPE 'SBT_TAPE';
    9> ALLOCATE CHANNEL ch07 TYPE 'SBT_TAPE';
    10> send 'NB_ORA_CLIENT=ncxxdsmwu01';
    11> send 'NB_ORA_SERV=crebm2400';
    12> set newname for datafile 1   to '+DATA';
    13> set newname for datafile 2   to '+DATA';
    14> set newname for datafile 3   to '+DATA';
    .......
    140> 141> set newname for datafile 130 to '+DATA';
    142> set newname for datafile 131 to '+DATA';
    143> set newname for datafile 132 to '+DATA';
    144> set newname for tempfile 1 to '+DATA';
    set newname for tempfile 2 to '+DATA';
    set newname for tempfile 3 to '+DATA';
    set newname for tempfile 4 to '+DATA';
    set newname for tempfile 5 to '+DATA';
    restore database;
    switch datafile all;
    recover database;145> 146> 147> 148> 149> 150> 151>
    152> }

    allocated channel: ch00
    channel ch00: SID=1489 device type=SBT_TAPE
    channel ch00: Veritas NetBackup for Oracle - Release 7.5 (2012091610)

    ....
    allocated channel: ch07
    channel ch07: SID=337 device type=SBT_TAPE
    channel ch07: Veritas NetBackup for Oracle - Release 7.5 (2012091610)

    sent command to channel: ch00
    sent command to channel: ch01
    sent command to channel: ch02
    sent command to channel: ch03
    sent command to channel: ch04
    sent command to channel: ch05
    sent command to channel: ch06
    sent command to channel: ch07

    executing command: SET NEWNAME
    ......

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting restore at 14-JAN-16
    Starting implicit crosscheck backup at 14-JAN-16
    Crosschecked 96 objects
    Finished implicit crosscheck backup at 14-JAN-16

    Starting implicit crosscheck copy at 14-JAN-16
    Crosschecked 2 objects
    Finished implicit crosscheck copy at 14-JAN-16

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


    channel ch00: starting datafile backup set restore
    channel ch00: specifying datafile(s) to restore from backup set
    channel ch00: restoring datafile 00003 to +DATA
    ......
    channel ch07: restoring datafile 00106 to +DATA
    channel ch07: restoring datafile 00131 to +DATA
    channel ch07: reading from backup piece 11qr1k8t_1_1
    channel ch00: piece handle=12qr1k8t_1_1 tag=ONLINE_SBT_10-01-16_16:44
    channel ch00: restored backup piece 1
    channel ch00: restore complete, elapsed time: 04:04:31
    channel ch01: piece handle=0sqr1k8s_1_1 tag=ONLINE_SBT_10-01-16_16:44
    channel ch01: restored backup piece 1
    channel ch01: restore complete, elapsed time: 04:17:51
    channel ch02: piece handle=0uqr1k8s_1_1 tag=ONLINE_SBT_10-01-16_16:44
    channel ch02: restored backup piece 1
    channel ch02: restore complete, elapsed time: 04:18:11
    channel ch03: piece handle=0tqr1k8s_1_1 tag=ONLINE_SBT_10-01-16_16:44
    channel ch03: restored backup piece 1
    channel ch03: restore complete, elapsed time: 04:20:31
    channel ch04: piece handle=0rqr1k8s_1_1 tag=ONLINE_SBT_10-01-16_16:44
    channel ch04: restored backup piece 1
    channel ch04: restore complete, elapsed time: 04:21:31
    channel ch05: piece handle=0vqr1k8t_1_1 tag=ONLINE_SBT_10-01-16_16:44
    channel ch05: restored backup piece 1
    channel ch05: restore complete, elapsed time: 04:27:11
    channel ch06: piece handle=10qr1k8t_1_1 tag=ONLINE_SBT_10-01-16_16:44
    channel ch06: restored backup piece 1
    channel ch06: restore complete, elapsed time: 04:27:31
    channel ch07: piece handle=11qr1k8t_1_1 tag=ONLINE_SBT_10-01-16_16:44
    channel ch07: restored backup piece 1
    channel ch07: restore complete, elapsed time: 04:28:41
    Finished restore at 15-JAN-16

    datafile 1 switched to datafile copy
    input datafile copy RECID=141 STAMP=901154168 file name=+DATA/ncxx2pt/datafile/system.526.901152599
    datafile 2 switched to datafile copy
    input datafile copy RECID=142 STAMP=901154169 file name=+DATA/ncxx2pt/datafile/sysaux.282.901150313
    datafile 3 switched to datafile copy
    input datafile copy RECID=143 STAMP=901154169 file name=+DATA/ncxx2pt/datafile/undotbs1.712.901150235
    ......
    datafile 131 switched to datafile copy
    input datafile copy RECID=271 STAMP=901154186 file name=+DATA/ncxx2pt/datafile/data_gbl.550.901139407
    datafile 132 switched to datafile copy
    input datafile copy RECID=272 STAMP=901154186 file name=+DATA/ncxx2pt/datafile/data_gbl.511.901139407

    Starting recover at 15-JAN-16
    channel ch00: starting incremental datafile backup set restore
    channel ch00: specifying datafile(s) to restore from backup set
    destination for restore of datafile 00009: +DATA/ncxx2pt/datafile/patrol_tbsp.484.901153935
    destination for restore of datafile 00017: +DATA/ncxx2pt/datafile/index_xxx.690.901151049
    destination for restore of datafile 00030: +DATA/ncxx2pt/datafile/data_gbl.319.901146803
    .....
    destination for restore of datafile 00102: +DATA/ncxx2pt/datafile/data_gbl.590.901146047
    destination for restore of datafile 00103: +DATA/ncxx2pt/datafile/data_rgn.459.901139421
    destination for restore of datafile 00111: +DATA/ncxx2pt/datafile/data_rgn.330.901139425
    destination for restore of datafile 00119: +DATA/ncxx2pt/datafile/opdb_data.648.901139401
    channel ch07: reading from backup piece 4nqr7q79_1_1
    channel ch05: piece handle=4jqr7q78_1_1 tag=INC_SBT_13-01-16_01:03
    channel ch05: restored backup piece 1
    ....
    channel ch04: restore complete, elapsed time: 00:51:16
    channel ch06: piece handle=4iqr7q78_1_1 tag=INC_SBT_13-01-16_01:03
    channel ch06: restored backup piece 1
    channel ch06: restore complete, elapsed time: 00:51:26
    channel ch07: piece handle=4nqr7q79_1_1 tag=INC_SBT_13-01-16_01:03
    channel ch07: restored backup piece 1
    channel ch07: restore complete, elapsed time: 00:53:46

    starting media recovery


    channel ch01: restore complete, elapsed time: 00:01:50
    archived log file name=+FRA/ncxx2pt/archivelog/2016_01_15/thread_1_seq_39508.423.901161905 thread=1 sequence=39508
    archived log file name=+FRA/ncxx2pt/archivelog/2016_01_15/thread_2_seq_32487.3136.901161905 thread=2 sequence=32487
    archived log file name=+FRA/ncxx2pt/archivelog/2016_01_15/thread_1_seq_39509.3035.901161905 thread=1 sequence=39509
    archived log file name=+FRA/ncxx2pt/archivelog/2016_01_15/thread_2_seq_32488.3273.901161907 thread=2 sequence=32488
    archived log file name=+FRA/ncxx2pt/archivelog/2016_01_15/thread_1_seq_39510.1440.901161905 thread=1 sequence=39510
    unable to find archived log
    archived log thread=1 sequence=39511
    released channel: ch00
    released channel: ch01
    released channel: ch02
    released channel: ch03
    released channel: ch04
    released channel: ch05
    released channel: ch06
    released channel: ch07
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 01/15/2016 02:45:29
    RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 39511 and starting SCN of 10882048837006

    RMAN> exit

6.Now open the database in resetlogs..

    SQL> select name from v$database;

    NAME
    ---------
    CNCXXU

    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-00344: unable to re-create online log '+DATA/cncxxu/onlinelog/group_1a'
    ORA-17502: ksfdcre:4 Failed to create file +DATA/cncxxu/onlinelog/group_1a
    ORA-15173: entry 'onlinelog' does not exist in directory 'cncxxu'


    SQL>

    SQL> select member from v$logfile;
    MEMBER
    --------------------------------------------------------------------------------
    +DATA/cncxxu/onlinelog/group_1a
    +FRA/cncxxu/onlinelog/group_1b
    +DATA/cncxxu/onlinelog/group_2a
    +FRA/cncxxu/onlinelog/group_2b
    +DATA/cncxxu/onlinelog/group_3a
    +FRA/cncxxu/onlinelog/group_3b
    +DATA/cncxxu/onlinelog/group_4a
    +FRA/cncxxu/onlinelog/group_4b
    +DATA/cncxxu/onlinelog/group_5a
    +FRA/cncxxu/onlinelog/group_5b
    +DATA/cncxxu/onlinelog/group_6a
    +FRA/cncxxu/onlinelog/group_6b
    +DATA/cncxxu/onlinelog/group_7a
    +FRA/cncxxu/onlinelog/group_7b
    +DATA/cncxxu/onlinelog/group_8a
    +FRA/cncxxu/onlinelog/group_8b

    16 rows selected.

    SQL>

    alter database rename file '+DATA/cncxxu/onlinelog/group_1a' to '+DATA/ncxx2pt/onlinelog/group_1a';
    alter database rename file '+FRA/cncxxu/onlinelog/group_1b' to '+FRA/ncxx2pt/onlinelog/group_1b';
    alter database rename file '+DATA/cncxxu/onlinelog/group_2a' to '+DATA/ncxx2pt/onlinelog/group_2a';
    alter database rename file '+FRA/cncxxu/onlinelog/group_2b' to '+FRA/ncxx2pt/onlinelog/group_2b';
    alter database rename file '+DATA/cncxxu/onlinelog/group_3a' to '+DATA/ncxx2pt/onlinelog/group_3a';
    alter database rename file '+FRA/cncxxu/onlinelog/group_3b' to '+FRA/ncxx2pt/onlinelog/group_3b';
    alter database rename file '+DATA/cncxxu/onlinelog/group_4a' to '+DATA/ncxx2pt/onlinelog/group_4a';
    alter database rename file '+FRA/cncxxu/onlinelog/group_4b' to '+FRA/ncxx2pt/onlinelog/group_4b';
    alter database rename file '+DATA/cncxxu/onlinelog/group_5a' to '+DATA/ncxx2pt/onlinelog/group_5a';
    alter database rename file '+FRA/cncxxu/onlinelog/group_5b' to '+FRA/ncxx2pt/onlinelog/group_5b';
    alter database rename file '+DATA/cncxxu/onlinelog/group_6a' to '+DATA/ncxx2pt/onlinelog/group_6a';
    alter database rename file '+FRA/cncxxu/onlinelog/group_6b' to '+FRA/ncxx2pt/onlinelog/group_6b';
    alter database rename file '+DATA/cncxxu/onlinelog/group_7a' to '+DATA/ncxx2pt/onlinelog/group_7a';
    alter database rename file '+FRA/cncxxu/onlinelog/group_7b' to '+FRA/ncxx2pt/onlinelog/group_7b';
    alter database rename file '+DATA/cncxxu/onlinelog/group_8a' to '+DATA/ncxx2pt/onlinelog/group_8a';
    alter database rename file '+FRA/cncxxu/onlinelog/group_8b' to '+FRA/ncxx2pt/onlinelog/group_8b';

    SQL>  select member from v$logfile
      2  ;

    MEMBER
    --------------------------------------------------------------------------------
    +DATA/ncxx2pt/onlinelog/group_1a
    +FRA/ncxx2pt/onlinelog/group_1b
    +DATA/ncxx2pt/onlinelog/group_2a
    +FRA/ncxx2pt/onlinelog/group_2b
    +DATA/ncxx2pt/onlinelog/group_3a
    +FRA/ncxx2pt/onlinelog/group_3b
    +DATA/ncxx2pt/onlinelog/group_4a
    +FRA/ncxx2pt/onlinelog/group_4b
    +DATA/ncxx2pt/onlinelog/group_5a
    +FRA/ncxx2pt/onlinelog/group_5b
    +DATA/ncxx2pt/onlinelog/group_6a
    +FRA/ncxx2pt/onlinelog/group_6b
    +DATA/ncxx2pt/onlinelog/group_7a
    +FRA/ncxx2pt/onlinelog/group_7b
    +DATA/ncxx2pt/onlinelog/group_8a
    +FRA/ncxx2pt/onlinelog/group_8b

    16 rows selected.

    SQL> 

    SQL>  alter database open resetlogs;
     alter database open resetlogs
    *
    ERROR at line 1:
    ORA-00392: log 4 of thread 1 is being cleared, operation not allowed
    ORA-00312: online log 4 thread 1: '+DATA/ncxx2pt/onlinelog/group_4a'
    ORA-00312: online log 4 thread 1: '+FRA/ncxx2pt/onlinelog/group_4b'


    SQL> 

    SQL> select group#,thread#,status from v$log;

        GROUP#    THREAD# STATUS
    ---------- ---------- ----------------
             1          1 CLEARING
             2          1 CLEARING
             3          1 CLEARING
             4          1 CLEARING_CURRENT
             5          2 CLEARING
             6          2 CLEARING_CURRENT
             7          2 CLEARING
             8          2 CLEARING

    8 rows selected.

    SQL> alter database clear logfile group 4;

    Database altered.

    SQL>  alter database clear logfile group 6;

    Database altered.

    SQL> select group#,thread#,status from v$log;

        GROUP#    THREAD# STATUS
    ---------- ---------- ----------------
             1          1 CLEARING
             2          1 CLEARING
             3          1 CLEARING
             4          1 CURRENT
             5          2 CLEARING
             6          2 CURRENT
             7          2 CLEARING
             8          2 CLEARING

    8 rows selected.

    SQL>

    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-19751: could not create the change tracking file
    ORA-19750: change tracking file: '+DATA/cncxxu/datafile/cncxxu1.bct.01.dbf'
    ORA-17502: ksfdcre:4 Failed to create file
    +DATA/cncxxu/datafile/cncxxu1.bct.01.dbf
    ORA-15173: entry 'datafile' does not exist in directory 'cncxxu'
    ORA-17503: ksfdopn:2 Failed to open file
    +DATA/cncxxu/datafile/cncxxu1.bct.01.dbf
    ORA-15173: entry 'datafile' does not exist in directory 'cncxxu'


    SQL> ALTER DATABASE disable  BLOCK CHANGE TRACKING;

    Database altered.

    SQL>

    SQL> alter database open;

    Database altered.

    SQL> 
    Instance                Host                                Startup              Database                     Shutdown Active
    Name / Number  Thread # Name                         Status Time                 Status   Archiver Logins?    Pending? State  Version
    ------------- --------- ---------------------------- ------ -------------------- -------- -------- ---------- -------- ------ -----------------
    NCXX2PT1 (1)          1 ncxxdsgtu21                  OPEN   14-JAN-2016 07:19:17 ACTIVE   STARTED  ALLOWED    NO       NORMAL 11.2.0.4.0

    SQL>

7.Until now my DBNAME still same as the source. so i’m recreating controlfile so that all information is new.

    SQL> alter database backup controlfile to trace;

    Database altered.

    SQL> 

from the alert log check the something like below.
    alter database backup controlfile to trace
    Backup controlfile written to trace file /optware/oracle/diag/rdbms/ncxx2pt/NCXX2PT1/trace/NCXX2PT1_ora_4517.trc
    Completed: alter database backup controlfile to trace

    SQL> alter system set db_name='NCXX2PT' scope=spfile;
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL>

    SQL> startup nomount;
    ORACLE instance started.

    Total System Global Area 3.2068E+10 bytes
    Fixed Size                  2269072 bytes
    Variable Size            5301600368 bytes
    Database Buffers         2.6709E+10 bytes
    Redo Buffers               55242752 bytes
   

    SQL> !vi createnewctlfile.sql

this is the content of my createnewctlfile.sql

    CREATE CONTROLFILE REUSE set DATABASE "NCXX2PT" RESETLOGS FORCE LOGGING ARCHIVELOG
        MAXLOGFILES 32
        MAXLOGMEMBERS 2
        MAXDATAFILES 1000
        MAXINSTANCES 10
        MAXLOGHISTORY 7594
    LOGFILE
      GROUP 1 (
        '+DATA/ncxx2pt/onlinelog/group_1a',
        '+FRA/ncxx2pt/onlinelog/group_1b'
      ) SIZE 1024M BLOCKSIZE 512,
      GROUP 2 (
        '+DATA/ncxx2pt/onlinelog/group_2a',
        '+FRA/ncxx2pt/onlinelog/group_2b'
      ) SIZE 1024M BLOCKSIZE 512,
      GROUP 3 (
        '+DATA/ncxx2pt/onlinelog/group_3a',
        '+FRA/ncxx2pt/onlinelog/group_3b'
      ) SIZE 1024M BLOCKSIZE 512,
      GROUP 4 (
        '+DATA/ncxx2pt/onlinelog/group_4a',
        '+FRA/ncxx2pt/onlinelog/group_4b'
      ) SIZE 1024M BLOCKSIZE 512
    -- STANDBY LOGFILE
    DATAFILE
      '+DATA/ncxx2pt/datafile/system.526.901152599',
      '+DATA/ncxx2pt/datafile/sysaux.282.901150313',
      '+DATA/ncxx2pt/datafile/undotbs1.712.901150235',
      '+DATA/ncxx2pt/datafile/undotbs2.449.901151051',
....
      '+DATA/ncxx2pt/datafile/data_gbl.492.901139399',
      '+DATA/ncxx2pt/datafile/data_gbl.500.901139399',
      '+DATA/ncxx2pt/datafile/data_gbl.550.901139407',
      '+DATA/ncxx2pt/datafile/data_gbl.511.901139407'
    CHARACTER SET AL32UTF8
    ;

8.Now lets create the controlfile and resetlogs the database.

    SQL> @createnewctlfile.sql

    Control file created.
    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    Database altered.

    SQL> ALTER DATABASE ADD LOGFILE THREAD 2
      2    GROUP 5 (
      3      '+DATA/ncxx2pt/onlinelog/group_5a',
      4      '+FRA/ncxx2pt/onlinelog/group_5b'
      5    ) SIZE 1024M BLOCKSIZE 512 REUSE,
      6    GROUP 6 (
      7      '+DATA/ncxx2pt/onlinelog/group_6a',
      8      '+FRA/ncxx2pt/onlinelog/group_6b'
      9    ) SIZE 1024M BLOCKSIZE 512 REUSE,
     10    GROUP 7 (
     11      '+DATA/ncxx2pt/onlinelog/group_7a',
     12      '+FRA/ncxx2pt/onlinelog/group_7b'
     13    ) SIZE 1024M BLOCKSIZE 512 REUSE,
     14    GROUP 8 (
     15      '+DATA/ncxx2pt/onlinelog/group_8a',
     16      '+FRA/ncxx2pt/onlinelog/group_8b'
     17    ) SIZE 1024M BLOCKSIZE 512 REUSE;

    Database altered.

    SQL>

    SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
    ORA-00279: change 10882048857598 generated at 01/15/2016 03:07:45 needed for thread 1
    ORA-00289: suggestion : +FRA
    ORA-00280: change 10882048857598 for thread 1 is in sequence #1


    Specify log: {=suggested | filename | AUTO | CANCEL}
    cancel
    Media recovery cancelled.
    ``

    Database altered.

    SQL> 
    ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/ncxx2pt/tempfile/temp.491.901163059' REUSE;
    ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/ncxx2pt/tempfile/temp.509.901163059' REUSE;
    ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/ncxx2pt/tempfile/temp.505.901163057' REUSE;

    Tablespace altered.

    SQL> 
    ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/ncxx2pt/tempfile/temp.482.901163057' REUSE;
    ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/ncxx2pt/tempfile/temp.504.901163053' REUSE;
    Tablespace altered.

    SQL>
    Tablespace altered.

    SQL>
    Tablespace altered.

    SQL>

    Tablespace altered.

    SQL>

9.Now lets enable the cluster database since we are done.

    SQL> alter system set cluster_database=true scope=spfile;

    System altered.

    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> 

    $
    $srvctl start instance -d NCXX2PT

    $  srvctl status database -d NCXX2PT
    Instance NCXX2PT1 is running on node ncxxdsgtu21
    Instance NCXX2PT2 is running on node ncxxdsgtu22
    $ 

    SQL> select dbid, name from v$database;

          DBID NAME
    ---------- ---------
    1304770496 NCXX2PT   < 

    Instance                Host                                Startup              Database                     Shutdown Active
    Name / Number  Thread # Name                         Status Time                 Status   Archiver Logins?    Pending? State  Version
    ------------- --------- ---------------------------- ------ -------------------- -------- -------- ---------- -------- ------ -----------------
    NCXX2PT1 (1)          1 ncxxdsgtu21                  OPEN   15-JAN-2016 04:46:05 ACTIVE   STARTED  ALLOWED    NO       NORMAL 11.2.0.4.0
    NCXX2PT2 (2)          2 ncxxdsgtu22                  OPEN   15-JAN-2016 04:46:06 ACTIVE   STARTED  ALLOWED    NO       NORMAL 11.2.0.4.0

    SQL>

thanks for checking… let me know if you encounter any issue.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s