RMAN Database Restore from 2 Node RAC+ASM TO 2 Node RAC+ASM
On Source 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
Source: DB: w5005pr, RAC+ASM, Diskgroup: +DATA Target: DB: DBA Learning Hub , 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=) 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. input datafile file number=00002 name=+DATA/w5005pr/datafile/sysaux. input datafile file number=00007 name=+DATA/w5005pr/datafile/ggadmin. input datafile file number=00005 name=+DATA/w5005pr/datafile/undotbs1 input datafile file number=00003 name=+DATA/w5005pr/datafile/undotbs3 input datafile file number=00006 name=+DATA/w5005pr/datafile/undotbs1 input datafile file number=00004 name=+DATA/w5005pr/datafile/users. 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=TAGT200451 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=TAGT200451 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= .. .. input archived log thread=2 sequence=43 RECID=89 STAMP= 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=TAGT200536 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=TAGT200545 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/initDBA Learning Hub.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 initDBA Learning Hub.ora
[oracle@rac1 ~]$ cat initDBA Learning Hub.ora *.audit_file_dest='/u01/app/oracle/admin/DBA Learning Hub/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='DBA Learning Hub' <----- *.diagnostic_dest='/u01/app/oracle' *.log_archive_dest_1='LOCATION=+TEST' *.log_archive_format='%t_%s_%r.dbf' *.memory_target= *.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/DBA Learning Hub/adump [oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/DBA Learning Hub/adump
Step 6: Add the entry in oratab
[oracle@rac1 ~]$ cat /etc/oratab | grep -i DBA Learning Hub
DBA Learning Hub:/u01/app/oracle/product/11.2.0.3/db_1:N
[oracle@rac1 ~]$
[oracle@rac1 ~]$ . oraenv ORACLE_SID = [DBA Learning Hub] ? 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/initDBA Learning Hub.ora'; ORACLE instance started. Total System Global Area bytes Fixed Size 2227984 bytes Variable Size bytes Database Buffers bytes Redo Buffers 8847360 bytes SQL> SQL> create spfile from pfile='/home/oracle/initDBA Learning Hub.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 bytes Fixed Size 2227984 bytes Variable Size bytes Database Buffers 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/spfileDBA Learning Hub.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_DBA Learning Hub <--- oracle 1 0 22:02 pts/2 00:00:00 grep pmon [oracle@rac1 ~]$
[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/DBA Learning Hub/controlfile/current.
output file name=+DATA/DBA Learning Hub/controlfile/current.
Finished restore at 05-OCT-16
RMAN>
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>
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: TAGT200536 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- 11-JUL-16 .. .. 2 42 1959744 05-OCT- 05-OCT-16 2 43 1970631 05-OCT- 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_1' ' to ''+TEST''" ; SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_9' ' to ''+TEST''" ; SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_5' ' to ''+TEST''" ; SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_5' ' 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_1' 14> ' 15> to ''+TEST''" ; 16> 17> SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_9' 18> ' 19> to ''+TEST''" ; 20> 21> SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_5' 22> ' 23> to ''+TEST''" ; 24> 25> SQL "ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_5' 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_1''to ''+TEST'' sql statement: ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_9''to ''+TEST'' sql statement: ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_5''to ''+TEST'' sql statement: ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_5''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=TAGT200451 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= file name=+TEST/DBA Learning Hub/datafile/system. datafile 2 switched to datafile copy input datafile copy RECID=9 STAMP= file name=+TEST/DBA Learning Hub/datafile/sysaux. datafile 3 switched to datafile copy input datafile copy RECID=10 STAMP= file name=+TEST/DBA Learning Hub/datafile/undotbs5 datafile 4 switched to datafile copy input datafile copy RECID=11 STAMP= file name=+TEST/DBA Learning Hub/datafile/users. datafile 5 switched to datafile copy input datafile copy RECID=12 STAMP= file name=+TEST/DBA Learning Hub/datafile/undotbs5 datafile 6 switched to datafile copy input datafile copy RECID=13 STAMP= file name=+TEST/DBA Learning Hub/datafile/undotbs5 datafile 7 switched to datafile copy input datafile copy RECID=14 STAMP= file name=+TEST/DBA Learning Hub/datafile/ggadmin. 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=TAGT200536 channel d1: restored backup piece 1 channel d1: restore complete, elapsed time: 00:00:03 archived log file name=+TEST/DBA Learning Hub/archivelog/2016_10_05/thread_1_seq_3 thread=1 sequence=38 archived log file name=+TEST/DBA Learning Hub/archivelog/2016_10_05/thread_2_seq_3 thread=2 sequence=42 archived log file name=+TEST/DBA Learning Hub/archivelog/2016_10_05/thread_1_seq_3 thread=1 sequence=39 archived log file name=+TEST/DBA Learning Hub/archivelog/2016_10_05/thread_2_seq_3 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/DBA Learning Hub/onlinelog/group_5 +TEST/DBA Learning Hub/onlinelog/group_3 +TEST/DBA Learning Hub/onlinelog/group_5 +TEST/DBA Learning Hub/onlinelog/group_5 SQL> select name, open_mode, dbid from v$database; NAME OPEN_MODE DBID --------- -------------------- ---------- W5005PR READ WRITE 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 bytes Fixed Size 2227984 bytes Variable Size bytes Database Buffers 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=DBA Learning Hub 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=) Connected to server version 11.2.0 Control Files in database: +TEST/DBA Learning Hub/controlfile/current. +DATA/DBA Learning Hub/controlfile/current. Change database ID and database name W5005PR to DBA Learning Hub? (Y/[N]) => Y Proceeding with operation Changing database ID from to Changing database name from W5005PR to DBA Learning Hub Control File +TEST/DBA Learning Hub/controlfile/current. - modified Control File +DATA/DBA Learning Hub/controlfile/current. - modified Datafile +TEST/DBA Learning Hub/datafile/system. - dbid changed, wrote new name Datafile +TEST/DBA Learning Hub/datafile/sysaux. - dbid changed, wrote new name Datafile +TEST/DBA Learning Hub/datafile/undotbs - dbid changed, wrote new name Datafile +TEST/DBA Learning Hub/datafile/users. - dbid changed, wrote new name Datafile +TEST/DBA Learning Hub/datafile/undotbs - dbid changed, wrote new name Datafile +TEST/DBA Learning Hub/datafile/undotbs - dbid changed, wrote new name Datafile +TEST/DBA Learning Hub/datafile/ggadmin. - dbid changed, wrote new name Datafile +TEST/DBA Learning Hub/tempfile/temp. - dbid changed, wrote new name Control File +TEST/DBA Learning Hub/controlfile/current. - dbid changed, wrote new name Control File +DATA/DBA Learning Hub/controlfile/current. - dbid changed, wrote new name Instance shut down Database name changed to DBA Learning Hub. Modify parameter file and generate a new password file before restarting. Database ID for database DBA Learning Hub changed to . 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 = [DBA Learning Hub] ? 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 bytes Fixed Size 2227984 bytes Variable Size bytes Database Buffers bytes Redo Buffers 8847360 bytes SQL> SQL> alter system set db_name='DBA Learning Hub' 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 bytes Fixed Size 2227984 bytes Variable Size bytes Database Buffers 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 --------- -------------------- ---------- DBA Learning Hub READ WRITE SQL> SQL> SELECT NAME FROM V$TEMPFILE; NAME -------------------------------------------------------------------------------- +TEST/DBA Learning Hub/tempfile/temp. SQL>
Step 14: Add the cluster parameter
[oracle@rac1 ~]$ cp initDBA Learning Hub.ora initDBA Learning Hub.ora_bkp [oracle@rac1 ~]$ SQL> create pfile='/home/oracle/initDBA Learning Hub.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 initDBA Learning Hub.ora DBA Learning Hub.__db_cache_size= DBA Learning Hub.__java_pool_size= DBA Learning Hub.__large_pool_size= DBA Learning Hub.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment DBA Learning Hub.__pga_aggregate_target= DBA Learning Hub.__sga_target= DBA Learning Hub.__shared_io_pool_size=0 DBA Learning Hub.__shared_pool_size= DBA Learning Hub.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/DBA Learning Hub/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='+TEST/DBA Learning Hub/controlfile/current.','+DATA/DBA Learning Hub/controlfile/current.'#Restore Controlfile *.db_block_size=8192 *.db_create_file_dest='+TEST' *.db_domain='' *.db_name='DBA Learning Hub' <--- *.diagnostic_dest='/u01/app/oracle' *.log_archive_dest_1='LOCATION=+TEST' *.log_archive_format='%t_%s_%r.dbf' *.memory_target= *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='exclusive' DBA Learning Hub2.thread=2 DBA Learning Hub1.thread=1 DBA Learning Hub2.instance_number=2 DBA Learning Hub1.instance_number=1 DBA Learning Hub2.undo_tablespace='UNDOTBS2' DBA Learning Hub1.undo_tablespace='UNDOTBS1' *.remote_listener='rac-scan:1521' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DBA Learning HubprXDB)' *.cluster_database=true [oracle@rac1 ~]$
Step 15: Start Instance 1 (DBA Learning Hub1)
[oracle@rac1 ~]$ export ORACLE_SID=DBA Learning Hub1 SQL> startup nomount pfile='/home/oracle/initDBA Learning Hub.ora'; ORACLE instance started. Total System Global Area bytes Fixed Size 2227984 bytes Variable Size bytes Database Buffers bytes Redo Buffers 8847360 bytes SQL> create spfile='+TEST' from pfile='/home/oracle/initDBA Learning Hub.ora'; File created. SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL>
Step 16: Verify spfile location
ASMCMD> cd +test/DBA Learning Hub/parameterfile
ASMCMD> ls -l
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE OCT 05 23:00:00 Y spfile.
ASMCMD>
Step 17: create instance1 and instance2 pfiles
on node 1 [oracle@rac1 ~]$ cd $ORACLE_HOME/dbs [oracle@rac1 dbs]$ cat initDBA Learning Hub1.ora SPFILE='+TEST/DBA Learning Hub/parameterfile/spfile.' [oracle@rac1 dbs]$ [oracle@rac1 dbs]$ rm spfileDBA Learning Hub* [oracle@rac1 dbs]$ On node 2 [oracle@rac2 dbs]$ cat initDBA Learning Hub2.ora SPFILE='+TEST/DBA Learning Hub/parameterfile/spfile.' [oracle@rac2 dbs]$ [oracle@rac2 dbs]$ rm spfileDBA Learning Hub* rm: cannot remove `spfileDBA Learning Hub*': 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] ? DBA Learning Hub1 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 bytes Fixed Size 2227984 bytes Variable Size bytes Database Buffers bytes Redo Buffers 8847360 bytes SQL> SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +TEST/DBA Learning Hub/parameterfile/spfile. SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> select name, open_mode from gv$database; NAME OPEN_MODE --------- -------------------- DBA Learning Hub READ WRITE SQL> On Node 2 [oracle@rac2 ~]$ . oraenv ORACLE_SID = [DBA Learning Hub2] ? 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 bytes Fixed Size 2227984 bytes Variable Size bytes Database Buffers bytes Redo Buffers 8847360 bytes SQL> SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +TEST/DBA Learning Hub/parameterfile/spfile. SQL> SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> select name, open_mode from gv$database; NAME OPEN_MODE --------- -------------------- DBA Learning Hub READ WRITE DBA Learning Hub READ WRITE SQL> SQL> select MEMBER from gv$logfile; MEMBER -------------------------------------------------------------------------------- +TEST/DBA Learning Hub/onlinelog/group_5 +TEST/DBA Learning Hub/onlinelog/group_3 +TEST/DBA Learning Hub/onlinelog/group_5 +TEST/DBA Learning Hub/onlinelog/group_5 +TEST/DBA Learning Hub/onlinelog/group_5 +TEST/DBA Learning Hub/onlinelog/group_3 +TEST/DBA Learning Hub/onlinelog/group_5 +TEST/DBA Learning Hub/onlinelog/group_5
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 DBA Learning Hub -o /u01/app/oracle/product/11.2.0.3/db_1 [oracle@rac1 ~]$ [oracle@rac1 ~]$ srvctl config database -d DBA Learning Hub -a Database unique name: DBA Learning Hub 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: DBA Learning Hub Database instances: <----- Empty here Disk Groups: <--- Empty here Mount point paths: Services: Type: RAC Database is enabled Database is administrator managed [oracle@rac1 ~]$
[oracle@rac1 ~]$ which srvctl /u01/app/oracle/product/11.2.0.3/db_1/bin/srvctl [oracle@rac1 ~]$ srvctl add instance -d DBA Learning Hub -i DBA Learning Hub1 -n rac1 [oracle@rac1 ~]$ srvctl add instance -d DBA Learning Hub -i DBA Learning Hub2 -n rac2 [oracle@rac1 ~]$ srvctl config database -d DBA Learning Hub -a Database unique name: DBA Learning Hub 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: DBA Learning Hub Database instances: DBA Learning Hub1,DBA Learning Hub2 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 --------- -------------------- DBA Learning Hub READ WRITE DBA Learning Hub READ WRITE [oracle@rac1 ~]$ srvctl start database -d DBA Learning Hub [oracle@rac1 ~]$ srvctl status database -d DBA Learning Hub Instance DBA Learning Hub1 is running on node rac1 Instance DBA Learning Hub2 is running on node rac2 [oracle@rac1 ~]$ [oracle@rac1 ~]$ crsctl stat res -t | grep -i DBA Learning Hub* ora.DBA Learning Hub.db [oracle@rac1 ~]$ [oracle@rac1 ~]$ crsctl stat res ora.DBA Learning Hub.db -t -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.DBA Learning Hub.db 1 ONLINE ONLINE rac1 Open 2 ONLINE ONLINE rac2 Open [oracle@rac1 ~]$ [oracle@rac1 ~]$ srvctl config database -d DBA Learning Hub -a Database unique name: DBA Learning Hub 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: DBA Learning Hub Database instances: DBA Learning Hub1,DBA Learning Hub2 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 DBA Learning Hub [oracle@rac1 ~]$ srvctl status database -d DBA Learning Hub Instance DBA Learning Hub1 is not running on node rac1 Instance DBA Learning Hub2 is not running on node rac2 [oracle@rac1 ~]$ srvctl start database -d DBA Learning Hub [oracle@rac1 ~]$ srvctl status database -d DBA Learning Hub Instance DBA Learning Hub1 is running on node rac1 Instance DBA Learning Hub2 is running on node rac2 [oracle@rac1 ~]$ srvctl config database -d DBA Learning Hub -a Database unique name: DBA Learning Hub 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: DBA Learning Hub Database instances: DBA Learning Hub1,DBA Learning Hub2 Disk Groups: TEST,DATA <--- Mount point paths: Services: Type: RAC Database is enabled Database is administrator managed [oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl add service -d DBA Learning Hub -s DBA Learning Hub_SRV -r DBA Learning Hub1 -a DBA Learning Hub2 [oracle@rac1 ~]$ srvctl start service -d DBA Learning Hub -s DBA Learning Hub_SRV [oracle@rac1 ~]$ srvctl status service -d DBA Learning Hub -s DBA Learning Hub_SRV Service DBA Learning Hub_SRV is running on instance(s) DBA Learning Hub1 [oracle@rac1 ~]$
Step 24: Add TNS entry for above service
[oracle@rac1 admin]$ tnsping DBA Learning Hub
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 = DBA Learning Hub_SRV)))
OK (20 msec)
[oracle@rac1 admin]$
Service register on all scan listeners and local listener of node1 [oracle@rac1 ~]$ lsnrctl status LISTENER | grep -i DBA Learning Hub_SRV Service "DBA Learning Hub_SRV" has 1 instance(s). [oracle@rac1 ~]$ lsnrctl status LISTENER_SCAN2 | grep -i DBA Learning Hub_SRV Service "DBA Learning Hub_SRV" has 1 instance(s). [oracle@rac1 ~]$ lsnrctl status LISTENER_SCAN3 | grep -i DBA Learning Hub_SRV Service "DBA Learning Hub_SRV" has 1 instance(s). [oracle@rac1 ~]$ on node 2 [oracle@rac2 ~]$ lsnrctl status LISTENER_SCAN1 | grep -i DBA Learning Hub_SRV Service "DBA Learning Hub_SRV" has 1 instance(s). [oracle@rac2 ~]$ [oracle@rac1 ~]$ sqlplus scott/tiger@DBA Learning Hub 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.