RMAN Database Restore RAC – RAC

RMAN Database Restore from 2 Node RAC+ASM  TO  2 Node RAC+ASM

0. Environment

On Source Server

1. Backup database
2. Create pfile from spfile
3. Push backup files/pfile to target server

On Target Server

4. Edit pfile
5. Create Required Folders
6. Add the entry in oratab
7. Startup Nomount
8. Restore controlfile
9. Mount database
10. Catalog backup pieces
11. Restore/recover database
12. Change dbname/DBID uisng NID
13. Modify DB_NAME in init file and start the database
14. Add the cluster parameter
15. Start Instance 1
16. Verify spfile location
17. create instance1 and instance2 pfiles
18. Start the database with SQL*Plus
19. Add database to cluster
20. Add RAC instances
21. Start Database using SRVCTL
22. Stop/Start database using SRVCTL
23. Add Service
24. Add TNS entry for above service
25. Verify


0. Environment

Source: DB: w5005pr, RAC+ASM, Diskgroup: +DATA
Target: DB: br8dba , RAC+ASM, Diskgroup: +TEST

On Source Server
=================

Step 1: Backup database w5005pr – source

[oracle@rac1 ~]$ cat open0.rcv
run {
allocate channel t1 type disk;
backup incremental level 0 database format '/u01/share/backup/database_%d_%u_%s';
release channel t1;
}
sql 'alter system archive log current';
run {
allocate channel a1 type disk;
backup archivelog all format '/u01/share/backup/arch_%d_%u_%s';
release channel a1;
}
run {
allocate channel c1 type disk;
backup current controlfile format '/u01/share/backup/Control_%d_%u_%s';
release channel c1;
}
exit
[oracle@rac1 ~]$
[oracle@rac1 backup]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 5 20:04:42 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: W5005PR (DBID=4162356668)

RMAN> @open0.rcv

RMAN> run {
2> allocate channel t1 type disk;
3> backup incremental level 0 database format '/u01/share/backup/database_%d_%u_%s';
4> release channel t1;
5> }
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=158 instance=w5005pr1 device type=DISK

Starting backup at 05-OCT-16
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/w5005pr/datafile/system.256.904560853
input datafile file number=00002 name=+DATA/w5005pr/datafile/sysaux.257.904560853
input datafile file number=00007 name=+DATA/w5005pr/datafile/ggadmin.334.917027433
input datafile file number=00005 name=+DATA/w5005pr/datafile/undotbs2.264.904560981
input datafile file number=00003 name=+DATA/w5005pr/datafile/undotbs1.258.904560853
input datafile file number=00006 name=+DATA/w5005pr/datafile/undotbs3.265.904560981
input datafile file number=00004 name=+DATA/w5005pr/datafile/users.259.904560853
channel t1: starting piece 1 at 05-OCT-16
channel t1: finished piece 1 at 05-OCT-16
piece handle=/u01/share/backup/database_W5005PR_01rhken4_1 tag=TAG20161005T200451 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:35
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel t1: starting piece 1 at 05-OCT-16
channel t1: finished piece 1 at 05-OCT-16
piece handle=/u01/share/backup/database_W5005PR_02rhkeo7_2 tag=TAG20161005T200451 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-16

released channel: t1

RMAN> sql 'alter system archive log current';
sql statement: alter system archive log current

RMAN> run {
2> allocate channel a1 type disk;
3> backup archivelog all format '/u01/share/backup/arch_%d_%u_%s';
4> release channel a1;
5> }
allocated channel: a1
channel a1: SID=158 instance=w5005pr1 device type=DISK

Starting backup at 05-OCT-16
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=40 STAMP=916925161
..
..
input archived log thread=2 sequence=43 RECID=89 STAMP=924465936
channel a1: starting piece 1 at 05-OCT-16
channel a1: finished piece 1 at 05-OCT-16
piece handle=/u01/share/backup/arch_W5005PR_03rhkeoh_3 tag=TAG20161005T200536 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:07
Finished backup at 05-OCT-16

released channel: a1

RMAN> run {
2> allocate channel c1 type disk;
3> backup current controlfile format '/u01/share/backup/Control_%d_%u_%s';
4> release channel c1;
5> }
allocated channel: c1
channel c1: SID=158 instance=w5005pr1 device type=DISK

Starting backup at 05-OCT-16
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 05-OCT-16
channel c1: finished piece 1 at 05-OCT-16
piece handle=/u01/share/backup/Control_W5005PR_04rhkeop_4 tag=TAG20161005T200545 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-OCT-16

released channel: c1

RMAN> exit

Recovery Manager complete.
[oracle@rac1 backup]$


Step 2: Create pfile from spfile

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
W5005PR   READ WRITE

SQL> create pfile='/home/oracle/initbr8dba.ora' from spfile;

File created.

SQL>


Step 3: Push backup files/pfile to target server

Use scp or FTP 

On Target Server
=================

Step 4: Edit pfile initbr8dba.ora

[oracle@rac1 ~]$ cat initbr8dba.ora
*.audit_file_dest='/u01/app/oracle/admin/br8dba/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+TEST','+DATA'
*.db_block_size=8192
*.db_create_file_dest='+TEST'
*.db_domain=''
*.db_name='w5005pr' <---- 
*.DB_UNIQUE_NAME='br8dba' <-----
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='LOCATION=+TEST'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1262485504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'
[oracle@rac1 ~]$


Step 5: Create Required Folders

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/admin/br8dba/adump
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/br8dba/adump


Step 6: Add the entry in oratab

[oracle@rac1 ~]$ cat /etc/oratab | grep -i br8dba
br8dba:/u01/app/oracle/product/11.2.0.3/db_1:N
[oracle@rac1 ~]$


Step 7: Startup nomount

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [br8dba] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 5 21:01:13 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/initbr8dba.ora';
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             754974960 bytes
Database Buffers          503316480 bytes
Redo Buffers                8847360 bytes
SQL>
SQL> create spfile from pfile='/home/oracle/initbr8dba.ora';

File created.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             754974960 bytes
Database Buffers          503316480 bytes
Redo Buffers                8847360 bytes
SQL>

SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string   
/u01/app/oracle/product/11.2.0.3/db_1/dbs/spfilebr8dba.ora
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@rac1 ~]$


[oracle@rac1 ~]$ ps -ef | grep pmon
oracle    4085     1  0 19:14 ?        00:00:01 asm_pmon_+ASM1
oracle   17605     1  0 22:02 ?        00:00:00 ora_pmon_br8dba <---
oracle   17785 11921  0 22:02 pts/2    00:00:00 grep pmon
[oracle@rac1 ~]$


Step 8: Restore controlfile

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 5 22:03:39 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: W5005PR (not mounted)

RMAN> restore controlfile from '/u01/share/backup/database_W5005PR_02rhkeo7_2';

Starting restore at 05-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+TEST/br8dba/controlfile/current.257.924473029
output file name=+DATA/br8dba/controlfile/current.364.924473031
Finished restore at 05-OCT-16

RMAN>


Step 9: Mount database

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN>

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
W5005PR   MOUNTED

SQL>


Step 10: Catalog the backup pieces

RMAN> catalog start with '/u01/share/backup/';

searching for all files that match the pattern /u01/share/backup/

List of Files Unknown to the Database
=====================================
File Name: /u01/share/backup/database_W5005PR_02rhkeo7_2
File Name: /u01/share/backup/Control_W5005PR_04rhkeop_4
File Name: /u01/share/backup/arch_W5005PR_03rhkeoh_3

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/share/backup/database_W5005PR_02rhkeo7_2
File Name: /u01/share/backup/Control_W5005PR_04rhkeop_4
File Name: /u01/share/backup/arch_W5005PR_03rhkeoh_3

RMAN>


Step 11: Restore database

RMAN> list backup of archivelog all;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4       243.54M    DISK        00:00:00     05-OCT-16
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20161005T200536
        Piece Name: /u01/share/backup/arch_W5005PR_03rhkeoh_3

  List of Archived Logs in backup set 4
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    17      1424073    20-MAR-16 1444924    11-JUL-16
..
..
  2    42      1959744    05-OCT-16 1970631    05-OCT-16
  2    43      1970631    05-OCT-16 1970652    05-OCT-16 <-- 43

RMAN>

/*
[oracle@rac1 ~]$ cat rman_recovery.rcv
run
{
# allocate a channel to the tape device
 ALLOCATE CHANNEL d1 DEVICE TYPE disk;
# rename the datafiles and online redo logs
set newname for datafile 1 to '+TEST';
set newname for datafile 2 to '+TEST';
set newname for datafile 3 to '+TEST';
set newname for datafile 4 to '+TEST';
set newname for datafile 5 to '+TEST';
set newname for datafile 6 to '+TEST';
set newname for datafile 7 to '+TEST';
SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_2.262.904560961'
'
to ''+TEST''" ;

SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_1.261.904560959'
'
to ''+TEST''" ;

SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_3.268.904561065'
'
to ''+TEST''" ;

SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_4.269.904561065'
'
to ''+TEST''" ;
# Do a SET UNTIL to prevent recovery of the online logs
SET UNTIL SEQUENCE 44; <-- 43 + 1
# restore the database and switch the datafile names
RESTORE DATABASE;
SWITCH DATAFILE ALL;
# recover the database
 RECOVER DATABASE;
}
[oracle@rac1 ~]$

*/

RMAN> @rman_recovery.rcv

RMAN> run
2> {
3> # allocate a channel to the tape device
4>  ALLOCATE CHANNEL d1 DEVICE TYPE disk;
5> # rename the datafiles and online redo logs
6> set newname for datafile 1 to '+TEST';
7> set newname for datafile 2 to '+TEST';
8> set newname for datafile 3 to '+TEST';
9> set newname for datafile 4 to '+TEST';
10> set newname for datafile 5 to '+TEST';
11> set newname for datafile 6 to '+TEST';
12> set newname for datafile 7 to '+TEST';
13> SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_2.262.904560961'
14> '
15> to ''+TEST''" ;
16>
17> SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_1.261.904560959'
18> '
19> to ''+TEST''" ;
20>
21> SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_3.268.904561065'
22> '
23> to ''+TEST''" ;
24>
25> SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_4.269.904561065'
26> '
27> to ''+TEST''" ;
28> # Do a SET UNTIL to prevent recovery of the online logs
29> SET UNTIL SEQUENCE 44;
30> # restore the database and switch the datafile names
31> RESTORE DATABASE;
32> SWITCH DATAFILE ALL;
33> # recover the database
34>  RECOVER DATABASE;
35> }
allocated channel: d1
channel d1: SID=140 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_2.262.904560961''to ''+TEST''

sql statement: ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_1.261.904560959''to ''+TEST''

sql statement: ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_3.268.904561065''to ''+TEST''

sql statement: ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_4.269.904561065''to ''+TEST''

executing command: SET until clause

Starting restore at 05-OCT-16

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00001 to +TEST
channel d1: restoring datafile 00002 to +TEST
channel d1: restoring datafile 00003 to +TEST
channel d1: restoring datafile 00004 to +TEST
channel d1: restoring datafile 00005 to +TEST
channel d1: restoring datafile 00006 to +TEST
channel d1: restoring datafile 00007 to +TEST
channel d1: reading from backup piece /u01/share/backup/database_W5005PR_01rhken4_1
channel d1: piece handle=/u01/share/backup/database_W5005PR_01rhken4_1 tag=TAG20161005T200451
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:35
Finished restore at 05-OCT-16

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=924476289 file name=+TEST/br8dba/datafile/system.256.924476253
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=924476289 file name=+TEST/br8dba/datafile/sysaux.258.924476255
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=924476289 file name=+TEST/br8dba/datafile/undotbs1.260.924476255
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=924476289 file name=+TEST/br8dba/datafile/users.259.924476255
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=924476289 file name=+TEST/br8dba/datafile/undotbs2.263.924476255
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=924476289 file name=+TEST/br8dba/datafile/undotbs3.262.924476255
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=924476289 file name=+TEST/br8dba/datafile/ggadmin.261.924476255

Starting recover at 05-OCT-16

starting media recovery

channel d1: starting archived log restore to default destination
channel d1: restoring archived log
archived log thread=2 sequence=42
channel d1: restoring archived log
archived log thread=1 sequence=38
channel d1: restoring archived log
archived log thread=1 sequence=39
channel d1: restoring archived log
archived log thread=2 sequence=43
channel d1: reading from backup piece /u01/share/backup/arch_W5005PR_03rhkeoh_3
channel d1: piece handle=/u01/share/backup/arch_W5005PR_03rhkeoh_3 tag=TAG20161005T200536
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:03
archived log file name=+TEST/br8dba/archivelog/2016_10_05/thread_1_seq_38.277.924476293 thread=1 sequence=38
archived log file name=+TEST/br8dba/archivelog/2016_10_05/thread_2_seq_42.276.924476293 thread=2 sequence=42
archived log file name=+TEST/br8dba/archivelog/2016_10_05/thread_1_seq_39.275.924476293 thread=1 sequence=39
archived log file name=+TEST/br8dba/archivelog/2016_10_05/thread_2_seq_43.274.924476293 thread=2 sequence=43
unable to find archived log
archived log thread=1 sequence=40
released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/05/2016 22:58:14
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 40 and starting SCN of 1970648

RMAN> **end-of-file**

RMAN> exit


Recovery Manager complete.
[oracle@rac1 ~]$


SQL>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open RESETLOGS;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+TEST/br8dba/onlinelog/group_2.272.924476365
+TEST/br8dba/onlinelog/group_1.273.924476363
+TEST/br8dba/onlinelog/group_3.271.924476365
+TEST/br8dba/onlinelog/group_4.270.924476365

SQL> select name, open_mode, dbid from v$database;

NAME      OPEN_MODE                  DBID
--------- -------------------- ----------
W5005PR   READ WRITE           4162356668

SQL>


Step 12: Change Database name and DB ID

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

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             754974960 bytes
Database Buffers          503316480 bytes
Redo Buffers                8847360 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

[oracle@rac1 ~]$ nid target=sys dbname=br8dba

DBNEWID: Release 11.2.0.3.0 - Production on Wed Oct 5 23:05:38 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:
Connected to database W5005PR (DBID=4162356668)

Connected to server version 11.2.0

Control Files in database:
    +TEST/br8dba/controlfile/current.257.924476123
    +DATA/br8dba/controlfile/current.364.924475123

Change database ID and database name W5005PR to BR8DBA? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 4162356668 to 1853744391
Changing database name from W5005PR to BR8DBA
    Control File +TEST/br8dba/controlfile/current.257.924476123 - modified
    Control File +DATA/br8dba/controlfile/current.364.924475123 - modified
    Datafile +TEST/br8dba/datafile/system.256.92447625 - dbid changed, wrote new name
    Datafile +TEST/br8dba/datafile/sysaux.258.92447625 - dbid changed, wrote new name
    Datafile +TEST/br8dba/datafile/undotbs1.260.92447625 - dbid changed, wrote new name
    Datafile +TEST/br8dba/datafile/users.259.92447625 - dbid changed, wrote new name
    Datafile +TEST/br8dba/datafile/undotbs2.263.92447625 - dbid changed, wrote new name
    Datafile +TEST/br8dba/datafile/undotbs3.262.92447625 - dbid changed, wrote new name
    Datafile +TEST/br8dba/datafile/ggadmin.261.92447625 - dbid changed, wrote new name
    Datafile +TEST/br8dba/tempfile/temp.269.92447636 - dbid changed, wrote new name
    Control File +TEST/br8dba/controlfile/current.257.924476123 - dbid changed, wrote new name
    Control File +DATA/br8dba/controlfile/current.364.924475123 - dbid changed, wrote new name
    Instance shut down

Database name changed to BR8DBA.
Modify parameter file and generate a new password file before restarting.
Database ID for database BR8DBA changed to 1853744391.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

[oracle@rac1 ~]$

Please note database will go down automatically


Step 13: Modify DB_NAME in init file and start the database

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [br8dba] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 5 23:08:17 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             754974960 bytes
Database Buffers          503316480 bytes
Redo Buffers                8847360 bytes
SQL>
SQL> alter system set db_name='br8dba' scope=spfile;

System altered.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             754974960 bytes
Database Buffers          503316480 bytes
Redo Buffers                8847360 bytes
Database mounted.
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open RESETLOGS;

Database altered.

SQL> select name, open_mode, dbid from v$database;

NAME      OPEN_MODE                  DBID
--------- -------------------- ----------
BR8DBA    READ WRITE           1853744391

SQL>

SQL>  SELECT NAME FROM V$TEMPFILE;

NAME
--------------------------------------------------------------------------------
+TEST/br8dba/tempfile/temp.269.924476367

SQL>


Step 14: Add the cluster parameter

[oracle@rac1 ~]$ cp initbr8dba.ora initbr8dba.ora_bkp
[oracle@rac1 ~]$
SQL> create pfile='/home/oracle/initbr8dba.ora' from spfile;

File created.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 ~]$

[oracle@rac1 ~]$ cat initbr8dba.ora
br8dba.__db_cache_size=503316480
br8dba.__java_pool_size=16777216
br8dba.__large_pool_size=16777216
br8dba.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
br8dba.__pga_aggregate_target=520093696
br8dba.__sga_target=754974720
br8dba.__shared_io_pool_size=0
br8dba.__shared_pool_size=201326592
br8dba.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/br8dba/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+TEST/br8dba/controlfile/current.257.924476123','+DATA/br8dba/controlfile/current.364.924475123'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+TEST'
*.db_domain=''
*.db_name='br8dba' <--- 
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='LOCATION=+TEST'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1262485504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
br8dba2.thread=2
br8dba1.thread=1
br8dba2.instance_number=2
br8dba1.instance_number=1
br8dba2.undo_tablespace='UNDOTBS2'
br8dba1.undo_tablespace='UNDOTBS1'
*.remote_listener='rac-scan:1521'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=br8dbaprXDB)'
*.cluster_database=true
[oracle@rac1 ~]$


Step 15: Start Instance 1 (br8dba1)

[oracle@rac1 ~]$ export ORACLE_SID=br8dba1

SQL> startup nomount pfile='/home/oracle/initbr8dba.ora';
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             822083824 bytes
Database Buffers          436207616 bytes
Redo Buffers                8847360 bytes
SQL> create spfile='+TEST' from pfile='/home/oracle/initbr8dba.ora';

File created.

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


Step 16: Verify spfile location

ASMCMD> cd +test/br8dba/parameterfile
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   OCT 05 23:00:00  Y    spfile.267.924478703
ASMCMD>


Step 17: create instance1 and instance2 pfiles

on node 1

[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ cat initbr8dba1.ora
SPFILE='+TEST/br8dba/parameterfile/spfile.267.924478703'
[oracle@rac1 dbs]$

[oracle@rac1 dbs]$ rm spfilebr8dba*
[oracle@rac1 dbs]$


On node 2

[oracle@rac2 dbs]$ cat initbr8dba2.ora
SPFILE='+TEST/br8dba/parameterfile/spfile.267.924478703'
[oracle@rac2 dbs]$

[oracle@rac2 dbs]$ rm spfilebr8dba*
rm: cannot remove `spfilebr8dba*': No such file or directory
[oracle@rac2 dbs]$


Step 18: Start the database with SQL*Plus

On Node 1

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ? br8dba1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 5 23:56:18 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             822083824 bytes
Database Buffers          436207616 bytes
Redo Buffers                8847360 bytes
SQL>
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string   +TEST/br8dba/parameterfile/spfile.267.924478703
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name, open_mode from gv$database;

NAME      OPEN_MODE
--------- --------------------
BR8DBA    READ WRITE

SQL>



On Node 2

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [br8dba2] ?
The Oracle base has been set to /u01/app/oracle
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 5 23:59:58 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             822083824 bytes
Database Buffers          436207616 bytes
Redo Buffers                8847360 bytes
SQL>
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string 
+TEST/br8dba/parameterfile/spfile.267.924478703
SQL>
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name, open_mode from gv$database;

NAME      OPEN_MODE
--------- --------------------
BR8DBA    READ WRITE
BR8DBA    READ WRITE

SQL>

SQL> select MEMBER from gv$logfile;

MEMBER
--------------------------------------------------------------------------------
+TEST/br8dba/onlinelog/group_2.272.924477045
+TEST/br8dba/onlinelog/group_1.273.924477043
+TEST/br8dba/onlinelog/group_3.271.924477045
+TEST/br8dba/onlinelog/group_4.270.924477045
+TEST/br8dba/onlinelog/group_2.272.924477045
+TEST/br8dba/onlinelog/group_1.273.924477043
+TEST/br8dba/onlinelog/group_3.271.924477045
+TEST/br8dba/onlinelog/group_4.270.924477045


Step 19: ADD DATABASE TO CLUSTER

[oracle@rac1 ~]$ which srvctl
/u01/app/oracle/product/11.2.0.3/db_1/bin/srvctl
[oracle@rac1 ~]$ srvctl add database -d br8dba -o /u01/app/oracle/product/11.2.0.3/db_1
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl config database -d br8dba -a
Database unique name: br8dba
Database name:
Oracle home: /u01/app/oracle/product/11.2.0.3/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: br8dba
Database instances: <----- Empty here
Disk Groups: <--- Empty here
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
[oracle@rac1 ~]$


Step 20: ADD RAC INSTANCES

[oracle@rac1 ~]$ which srvctl
/u01/app/oracle/product/11.2.0.3/db_1/bin/srvctl
[oracle@rac1 ~]$ srvctl add instance -d br8dba -i br8dba1 -n rac1
[oracle@rac1 ~]$ srvctl add instance -d br8dba -i br8dba2 -n rac2
[oracle@rac1 ~]$ srvctl config database -d br8dba -a
Database unique name: br8dba
Database name:
Oracle home: /u01/app/oracle/product/11.2.0.3/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: br8dba
Database instances: br8dba1,br8dba2
Disk Groups: <--- Empty here
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
[oracle@rac1 ~]$


Step 21: Start Database using SRVCTL

Please note database already started with SQL*Plus

SQL> select name, open_mode from gv$database;

NAME      OPEN_MODE
--------- --------------------
BR8DBA    READ WRITE
BR8DBA    READ WRITE

[oracle@rac1 ~]$ srvctl start database -d br8dba

[oracle@rac1 ~]$ srvctl status database -d br8dba
Instance br8dba1 is running on node rac1
Instance br8dba2 is running on node rac2
[oracle@rac1 ~]$

[oracle@rac1 ~]$ crsctl stat res -t | grep -i br8dba*
ora.br8dba.db
[oracle@rac1 ~]$

[oracle@rac1 ~]$ crsctl stat res ora.br8dba.db -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.br8dba.db
      1        ONLINE  ONLINE       rac1                     Open
      2        ONLINE  ONLINE       rac2                     Open
[oracle@rac1 ~]$


[oracle@rac1 ~]$ srvctl config database -d br8dba -a
Database unique name: br8dba
Database name:
Oracle home: /u01/app/oracle/product/11.2.0.3/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: br8dba
Database instances: br8dba1,br8dba2
Disk Groups: <--- Still showing empty
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
[oracle@rac1 ~]$


Step 22: Stop/Start database using SRVCTL

[oracle@rac1 ~]$ srvctl stop database -d br8dba
[oracle@rac1 ~]$ srvctl status database -d br8dba
Instance br8dba1 is not running on node rac1
Instance br8dba2 is not running on node rac2
[oracle@rac1 ~]$ srvctl start database -d br8dba
[oracle@rac1 ~]$ srvctl status database -d br8dba
Instance br8dba1 is running on node rac1
Instance br8dba2 is running on node rac2
[oracle@rac1 ~]$ srvctl config database -d br8dba -a
Database unique name: br8dba
Database name:
Oracle home: /u01/app/oracle/product/11.2.0.3/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: br8dba
Database instances: br8dba1,br8dba2
Disk Groups: TEST,DATA <--- 
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
[oracle@rac1 ~]$


Step 23: Add Service

[oracle@rac1 ~]$ srvctl add service -d br8dba -s BR8DBA_SRV -r br8dba1 -a br8dba2
[oracle@rac1 ~]$ srvctl start service -d br8dba -s BR8DBA_SRV
[oracle@rac1 ~]$ srvctl status service -d br8dba -s BR8DBA_SRV
Service BR8DBA_SRV is running on instance(s) br8dba1
[oracle@rac1 ~]$


Step 24: Add TNS entry for above service

[oracle@rac1 admin]$ tnsping BR8DBA

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-OCT-2016 00:21:04

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8DBA_SRV)))
OK (20 msec)
[oracle@rac1 admin]$


Step 25: Verify

Service register on all scan listeners and local listener of node1

[oracle@rac1 ~]$ lsnrctl status LISTENER | grep -i BR8DBA_SRV
Service "BR8DBA_SRV" has 1 instance(s).
[oracle@rac1 ~]$ lsnrctl status LISTENER_SCAN2 | grep -i BR8DBA_SRV
Service "BR8DBA_SRV" has 1 instance(s).
[oracle@rac1 ~]$ lsnrctl status LISTENER_SCAN3 | grep -i BR8DBA_SRV
Service "BR8DBA_SRV" has 1 instance(s).
[oracle@rac1 ~]$

on node 2
[oracle@rac2 ~]$ lsnrctl status LISTENER_SCAN1 | grep -i BR8DBA_SRV
Service "BR8DBA_SRV" has 1 instance(s).
[oracle@rac2 ~]$


[oracle@rac1 ~]$ sqlplus scott/tiger@br8dba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 6 00:24:43 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>

Please create password file in $ORACLE_HOME/dbs/ for each instance

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.