Method Used: Create as a single instance standalone database in one node and then convert it into a RAC database running on a 2 node cluster.
Environment: Oracle 11g Grid Infrastructure, ASM, Oracle 11g RAC Database, 64 bit Oracle Linux Server 6.3, VMware Hypervisor ESXi 5.5
Existing RAC Setup: 2 nodes called "rac1" and "rac2", RAC database "babu" has 2 instances called "babu1" and "babu2" (currently shutdown). A shared disk / DISKGROUP to place the database files of the new database.
1) Start up the Cluster Resources in both nodes (if down)
In node 1, as "root" user
[oracle@rac1 ~]$ su root
Password:
[root@rac1 oracle]# . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[root@rac1 oracle]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
[root@rac1 oracle]# crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DSK_GRP_02.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac2
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE rac1
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE rac1
ora.babu.db
1 OFFLINE OFFLINE Instance Shutdown
2 OFFLINE OFFLINE Instance Shutdown
ora.cvu
1 ONLINE ONLINE rac1
ora.oc4j
1 ONLINE ONLINE rac1
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.scan1.vip
1 ONLINE ONLINE rac2
ora.scan2.vip
1 ONLINE ONLINE rac1
ora.scan3.vip
1 ONLINE ONLINE rac1
[root@rac1 oracle]# crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac1
ora....P_02.dg ora....up.type ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type ONLINE ONLINE rac2
ora....N2.lsnr ora....er.type ONLINE ONLINE rac1
ora....N3.lsnr ora....er.type ONLINE ONLINE rac1
ora.asm ora.asm.type ONLINE ONLINE rac1
ora.babu.db ora....se.type OFFLINE OFFLINE
ora.cvu ora.cvu.type ONLINE ONLINE rac1
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac1
ora.oc4j ora.oc4j.type ONLINE ONLINE rac1
ora.ons ora.ons.type ONLINE ONLINE rac1
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application OFFLINE OFFLINE
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application OFFLINE OFFLINE
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type ONLINE ONLINE rac2
ora.scan1.vip ora....ip.type ONLINE ONLINE rac2
ora.scan2.vip ora....ip.type ONLINE ONLINE rac1
ora.scan3.vip ora....ip.type ONLINE ONLINE rac1
Repeat above in node 2 as well
2) We will be using the new DISKGROUP "DSK_GRP_02" (that we created previously) for the new database, so lets make sure its available in both nodes.
In node 1 as "oracle" user
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysasm
SQL> ALTER DISKGROUP DSK_GRP_02 CHECK ALL;
Diskgroup altered.
SQL> ALTER DISKGROUP DSK_GRP_02 ADD DIRECTORY '+DSK_GRP_02/MANI';
Diskgroup altered
[oracle@rac1 ~]$ asmcmd ls -lt +DSK_GRP_02
Type Redund Striped Time Sys Name
N MANI/
In node 2 as "oracle" user
[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM2
The Oracle base has been set to /u01/app/oracle
[oracle@rac2 ~]$ asmcmd
ASMCMD> ls -l
State Type Rebal Name
MOUNTED EXTERN N DATA/
MOUNTED EXTERN N DSK_GRP_02/
3) In node 1 as "oracle" user, create necessary "oracle dump" directories, copy init.ora file from existing database (babu) and edit it:-
[oracle@rac1 ~]$ cd /u01/app/oracle/admin
[oracle@rac1 admin]$ mkdir mani
[oracle@rac1 admin]$ cd mani
[oracle@rac1 mani]$ mkdir adump
[oracle@rac1 mani]$ mkdir dpdump
[oracle@rac1 mani]$ mkdir hdump
Create "initmani1.ora" file by copying it from an existing database
[oracle@rac1 babu]$ cp /u01/app/oracle/admin/babu/pfile/init.ora.012014202025 $ORACLE_HOME/dbs/initmani1.ora
Content of edited "initmani1.ora" is as follows:-
audit_file_dest="/u01/app/oracle/admin/mani/adump"
audit_trail=none
# cluster_database=true
compatible=11.2.0.0.0
db_block_size=8192
db_create_file_dest="+DSK_GRP_02"
db_domain=localdomain
db_name="mani"
diagnostic_dest=/u01/app/oracle
dispatchers="(PROTOCOL=TCP) (SERVICE=maniXDB)"
memory_target=786432000
open_cursors=300
processes=150
remote_listener=rac-scan.localdomain:1521
remote_login_passwordfile=exclusive
sec_case_sensitive_logon=false
mani1.instance_number=1
mani2.instance_number=2
mani1.thread=1
mani2.thread=2
mani1.undo_tablespace=UNDOTBS1
mani2.undo_tablespace=UNDOTBS2
4) Create password file
[oracle@rac1 babu]$ echo $ORACLE_BASE $ORACLE_HOME $ORACLE_SID
/u01/app/oracle /u01/app/oracle/product/11.2.0/dbhome_1 babu
[oracle@rac1 dbs]$ export ORACLE_SID=mani1
[oracle@rac1 dbs]$ echo $ORACLE_SID
mani1
[oracle@rac1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle
[oracle@rac1 dbs]$ ls -l
total 32
-rw-rw---- 1 oracle oinstall 1544 Feb 15 10:25 hc_babu1.dat
-rw-r----- 1 oracle oinstall 35 Jan 1 20:20 initbabu1.ora
-rw-r--r-- 1 oracle oinstall 555 Feb 15 11:03 initmani1.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1536 Jan 1 20:16 orapwbabu1
-rw-r----- 1 oracle oinstall 1536 Feb 15 11:13 orapwmani1
5) Listener and TNS files
[oracle@rac1 admin]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
[oracle@rac1 admin]$ srvctl config listener
Name: LISTENER
Network: 1, Owner: oracle
Home:
End points: TCP:1521
[oracle@rac1 admin]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): rac2,rac1
[oracle@rac1 admin]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac1
[oracle@rac1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@rac1 admin]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-FEB-2014 11:31:46
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> stat
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 15-FEB-2014 18:24:01
Uptime 0 days 3 hr. 7 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.71)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.81)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$ more listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
Edit "tnsnames.ora" file
[oracle@rac1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@rac1 admin]$ more tnsnames.ora
BABU =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = babu.localdomain)
)
)
BABU1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.81)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = babu.localdomain)
(INSTANCE_NAME = babu1)
)
)
BABU2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.82)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = babu.localdomain)
(INSTANCE_NAME = babu2)
)
)
MANI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mani.localdomain)
)
)
MANI1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.81)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mani.localdomain)
(INSTANCE_NAME = mani1)
)
)
MANI2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.82)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mani.localdomain)
(INSTANCE_NAME = mani2)
)
)
6) In node 1 as "oracle" user, create the database
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? babu
The Oracle base has been set to /u01/app/oracle
[oracle@rac1 ~]$ echo $ORACLE_BASE $ORACLE_HOME $ORACLE_SID
/u01/app/oracle /u01/app/oracle/product/11.2.0/dbhome_1 babu
[oracle@rac1 ~]$ export ORACLE_SID=mani1
[oracle@rac1 ~]$ echo $ORACLE_BASE $ORACLE_HOME $ORACLE_SID
/u01/app/oracle /u01/app/oracle/product/11.2.0/dbhome_1 mani1
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2232472 bytes
Variable Size 524291944 bytes
Database Buffers 255852544 bytes
Redo Buffers 2621440 bytes
SQL> select * from V$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 mani1
rac1.localdomain
11.2.0.3.0 22-FEB-14 STARTED YES 0 STOPPED
ALLOSat NO ACTIVE UNKNOWN NORMAL NO
SQL> CREATE DATABASE "MANI"
MAXLOGFILES 192 MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE GROUP 1 SIZE 50M,
GROUP 2 SIZE 50M
DATAFILE SIZE 300M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS DATAFILE SIZE 50M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT 5M MAXSIZE 2048M
UNDO TABLESPACE UNDOTBS1 DATAFILE SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 2048M
USER SYS IDENTIFIED BY ORACLE
USER SYSTEM IDENTIFIED BY ORACLE
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
NOARCHIVELOG;
Database created.
Create UNDO tablespace and run below post database creation scripts:-
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 2048M;
SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catalog.sql
SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catproc.sql
7) Control Files and Log Files changes
SQL> select value from v$parameter where name ='control_files';
VALUE
--------------------------------------------------------------------------------
+DSK_GRP_02/mani/controlfile/current.256.839537095
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DSK_GRP_02/mani/controlfile/current.256.839537095
Add above value for the "control_files" in the initmani1.ora
vi /$ORACLE_HOME/dbs/initmani1.ora
control_files = '+DSK_GRP_02/mani/controlfile/current.256.839537095'
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DSK_GRP_02/mani/onlinelog/group_1.257.839537099
+DSK_GRP_02/mani/onlinelog/group_2.258.839537101
2 rows selected.
Thread 2 is for instance on 2nd node, see init parameter "mani2.thread=2"
SQL> alter database add logfile thread 2 group 3 size 50m, group 4 size 50m;
Database altered.
SQL> alter database enable public thread 2;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 13 52428800 512 1 NO INACTIVE 191937 22-FEB-14 199062 22-FEB-14
2 1 14 52428800 512 1 NO CURRENT 199062 22-FEB-14 2.8147E+14
3 2 1 52428800 512 1 NO CURRENT 209541 22-FEB-14 2.8147E+14 22-FEB-14
4 2 0 52428800 512 1 YES UNUSED 0 0
4 rows selected.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE +DSK_GRP_02/mani/onlinelog/group_1.257.839537099 NO
2 ONLINE +DSK_GRP_02/mani/onlinelog/group_2.258.839537101 NO
3 ONLINE +DSK_GRP_02/mani/onlinelog/group_3.265.839549313 NO
4 ONLINE +DSK_GRP_02/mani/onlinelog/group_4.266.839549319 NO
4 rows selected.
8) Convert standalone database to RAC database
SQL> show parameter cluster_database
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
SQL> show parameter instance
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string mani1
instance_number integer 1
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1
SQL> shutdown immediate;
Uncomment below init parameter and start up the database
cluster_database=true
SQL> startup
SQL> show parameter cluster_database
show parameter instance
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
SQL> show parameter instance
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 2
instance_groups string
instance_name string mani1
instance_number integer 1
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 2Create the dictionary views needed for Oracle RAC databases.
SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catclust.sql
9) Create spfile from pfile and make the database use it.
SQL> create spfile='+DSK_GRP_02/mani/spfilemani.ora' from pfile;
File created.
Shutdown, backup existing "initmani1.ora", update it to use the spfile we just created and startup the database.
SQL> shutdown immediate;
Database closed.
[oracle@rac1 dbs]$ ls -l
total 28
-rw-rw---- 1 oracle oinstall 1544 Feb 15 13:36 hc_babu1.dat
-rw-rw---- 1 oracle oinstall 1544 Feb 22 14:49 hc_mani1.dat
-rw-r----- 1 oracle oinstall 35 Jan 1 20:20 initbabu1.ora
-rw-r--r-- 1 oracle oinstall 655 Feb 22 14:26 initmani1.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1536 Jan 1 20:16 orapwbabu1
-rw-r----- 1 oracle oinstall 1536 Feb 15 11:13 orapwmani1
[oracle@rac1 dbs]$ mv /$ORACLE_HOME/dbs/initmani1.ora /$ORACLE_HOME/dbs/initmani1.ora.bkp
[oracle@rac1 dbs]$ vi /$ORACLE_HOME/dbs/initmani1.ora
SPFILE='+DSK_GRP_02/mani/spfilemani.ora'
[oracle@rac1 dbs]$ ls -l
total 32
-rw-rw---- 1 oracle oinstall 1544 Feb 15 13:36 hc_babu1.dat
-rw-rw---- 1 oracle oinstall 1544 Feb 22 14:49 hc_mani1.dat
-rw-r----- 1 oracle oinstall 35 Jan 1 20:20 initbabu1.ora
-rw-r--r-- 1 oracle oinstall 42 Feb 22 14:50 initmani1.ora
-rw-r--r-- 1 oracle oinstall 655 Feb 22 14:26 initmani1.ora.bkp
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1536 Jan 1 20:16 orapwbabu1
-rw-r----- 1 oracle oinstall 1536 Feb 15 11:13 orapwmani1
SQL> startup
Make sure the DB is using spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DSK_GRP_02/mani/spfilemani.ora
SQL> select value from v$parameter where name='spfile';
VALUE
----------------------------------------------------------------------------------------------------
+DSK_GRP_02/mani/spfilemani.ora
1 row selected.
10) Copy the init and password file to node 2
[oracle@rac1 dbs]$ scp $ORACLE_HOME/dbs/initmani1.ora rac2:$ORACLE_HOME/dbs/initmani2.ora
initmani1.ora 100% 42 0.0KB/s 00:00
[oracle@rac1 dbs]$ scp $ORACLE_HOME/dbs/orapwmani1 rac2:$ORACLE_HOME/dbs/orapwmani2
orapwmani1 100% 1536 1.5KB/s 00:00
Check if below files are present in node 2
[oracle@rac2 dbs]$ ls -l
total 24
-rw-rw---- 1 oracle oinstall 1544 Feb 22 14:57 hc_babu2.dat
-rw-r----- 1 oracle oinstall 35 Jan 1 20:20 initbabu2.ora
-rw-r--r-- 1 oracle oinstall 42 Feb 22 15:07 initmani2.ora
-rw-r--r-- 1 oracle oinstall 2851 Jan 1 19:53 init.ora
-rw-r----- 1 oracle oinstall 1536 Jan 1 20:16 orapwbabu2
-rw-r----- 1 oracle oinstall 1536 Feb 22 15:07 orapwmani2
11) In node 2 as "oracle" user, create necessary "oracle dump" directories and start up the database:-
[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? babu
The Oracle base has been set to /u01/app/oracle
[oracle@rac2 ~]$ cd /$ORACLE_BASE/admin
[oracle@rac2 admin]$ mkdir mani
[oracle@rac2 admin]$ cd mani
[oracle@rac2 mani]$ mkdir adump
[oracle@rac2 mani]$ mkdir dpdump
[oracle@rac2 mani]$ mkdir hdump
[oracle@rac2 ~]$ echo $ORACLE_BASE $ORACLE_HOME $ORACLE_SID
/u01/app/oracle /u01/app/oracle/product/11.2.0/dbhome_1 mani2
[oracle@rac2 ~]$ sqlplus / as sysdba
SQL> startup
SQL> col host_name for a20
SQL> col instance_name for a20
SQL> select instance_name,host_name from gv$instance;
INSTANCE_NAME HOST_NAME
-------------------- --------------------
mani2 rac2.localdomain
mani1 rac1.localdomain
Check the participating instances:-
SQL> select * from v$active_instances;
INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
1 rac1.localdomain:mani1
2 rac2.localdomain:mani2
Also check the participating instances from node1:-
SQL> select * from v$active_instances;
INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
1 rac1.localdomain:mani1
2 rac2.localdomain:mani2
2 rows selected.
SQL> col instance_name for a20
SQL> col host_name for a20
SQL> select instance_name,host_name from gv$instance;
INSTANCE_NAME HOST_NAME
-------------------- --------------------
mani1 rac1.localdomain
mani2 rac2.localdomain
2 rows selected.
12) Add the new database in the cluster resource
- Shutdown the new database (both database instances)
- Cluster Resources in both nodes should be up and running
Perform the following from node1:-
[oracle@rac1 ~]$ su root
Password:
[root@rac1 oracle]# . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[root@rac1 oracle]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? babu
The Oracle base has been set to /u01/app/oracle
[oracle@rac1 ~]$ export ORACLE_SID=mani1
[oracle@rac1 ~]$ echo $ORACLE_SID $ORACLE_HOME $ORACLE_BASE
mani1 /u01/app/oracle/product/11.2.0/dbhome_1 /u01/app/oracle
[oracle@rac1 ~]$ srvctl add database -d mani -o $ORACLE_HOME -m localdomain
[oracle@rac1 ~]$ srvctl add instance -d mani -i mani1 -n rac1
[oracle@rac1 ~]$ srvctl add instance -d mani -i mani2 -n rac2
Check the status of new database:-
[root@rac1 oracle]# crs_stat -t -v
Name Type R/RA F/FT Target State Host
----------------------------------------------------------------------
ora.DATA.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora....P_02.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type 0/5 0/ ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac2
ora....N2.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac1
ora....N3.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac1
ora.asm ora.asm.type 0/5 0/ ONLINE ONLINE rac1
ora.babu.db ora....se.type 0/2 0/1 OFFLINE OFFLINE
ora.cvu ora.cvu.type 0/5 0/0 ONLINE ONLINE rac1
ora.gsd ora.gsd.type 0/5 0/ OFFLINE OFFLINE
ora.mani.db ora....se.type 0/2 0/1 OFFLINE OFFLINE
ora....network ora....rk.type 0/5 0/ ONLINE ONLINE rac1
ora.oc4j ora.oc4j.type 0/1 0/2 ONLINE ONLINE rac1
ora.ons ora.ons.type 0/3 0/ ONLINE ONLINE rac1
ora....SM1.asm application 0/5 0/0 ONLINE ONLINE rac1
ora....C1.lsnr application 0/5 0/0 ONLINE ONLINE rac1
ora.rac1.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac1.ons application 0/3 0/0 ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac1
ora....SM2.asm application 0/5 0/0 ONLINE ONLINE rac2
ora....C2.lsnr application 0/5 0/0 ONLINE ONLINE rac2
ora.rac2.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac2.ons application 0/3 0/0 ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac2
ora.scan1.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac2
ora.scan2.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac1
ora.scan3.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac1
[oracle@rac1 ~]$ srvctl status database -d mani
Instance mani1 is not running on node rac1
Instance mani2 is not running on node rac2
[oracle@rac1 ~]$ srvctl status asm -n rac1
ASM is running on rac1
[oracle@rac1 ~]$ srvctl status asm -n rac2
ASM is running on rac2
[oracle@rac1 ~]$ srvctl status instance -d mani -i mani1
Instance mani1 is not running on node rac1
[oracle@rac1 ~]$ srvctl status instance -d mani -i mani2
Instance mani2 is not running on node rac2
[oracle@rac1 ~]$ srvctl config database -d mani
Database unique name: mani
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile:
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: mani
Database instances: mani1,mani2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@rac1 ~]$ lsnrctl stat
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-FEB-2014 08:34:32
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 18-FEB-2014 18:15:50
Uptime 0 days 0 hr. 18 min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.71)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.81)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
The command completed successfully
13) Start the new database:-
[root@rac1 oracle]# srvstl start database -d mani
Again, check the status of new database:-
[root@rac1 oracle]# crs_stat -t -v
Name Type R/RA F/FT Target State Host
----------------------------------------------------------------------
ora.DATA.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora....P_02.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type 0/5 0/ ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac2
ora....N2.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac1
ora....N3.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac1
ora.asm ora.asm.type 0/5 0/ ONLINE ONLINE rac1
ora.babu.db ora....se.type 0/2 0/1 OFFLINE OFFLINE
ora.cvu ora.cvu.type 0/5 0/0 ONLINE ONLINE rac1
ora.gsd ora.gsd.type 0/5 0/ OFFLINE OFFLINE
ora.mani.db ora....se.type 0/2 0/1 ONLINE ONLINE rac1
ora....network ora....rk.type 0/5 0/ ONLINE ONLINE rac1
ora.oc4j ora.oc4j.type 0/1 0/2 ONLINE ONLINE rac1
ora.ons ora.ons.type 0/3 0/ ONLINE ONLINE rac1
ora....SM1.asm application 0/5 0/0 ONLINE ONLINE rac1
ora....C1.lsnr application 0/5 0/0 ONLINE ONLINE rac1
ora.rac1.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac1.ons application 0/3 0/0 ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac1
ora....SM2.asm application 0/5 0/0 ONLINE ONLINE rac2
ora....C2.lsnr application 0/5 0/0 ONLINE ONLINE rac2
ora.rac2.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac2.ons application 0/3 0/0 ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac2
ora.scan1.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac2
ora.scan2.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac1
ora.scan3.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac1
[oracle@rac1 ~]$ srvctl status database -d mani
Instance mani1 is running on node rac1
Instance mani2 is running on node rac2
[oracle@rac1 ~]$ srvctl status instance -d mani -i mani1
Instance mani1 is running on node rac1
[oracle@rac1 ~]$ srvctl status instance -d mani -i mani2
Instance mani2 is running on node rac2
[oracle@rac1 ~]$ lsnrctl stat
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-FEB-2014 08:50:10
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 18-FEB-2014 18:15:50
Uptime 0 days 0 hr. 34 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.71)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.81)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "mani.localdomain" has 1 instance(s).
Instance "mani1", status READY, has 1 handler(s) for this service...
Service "maniXDB.localdomain" has 1 instance(s).
Instance "mani1", status READY, has 1 handler(s) for this service...
The command completed successfully
Log into the databases and check the status
[oracle@rac1 ~]$ export ORACLE_SID=mani1 ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@rac1 ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@rac1 ~]$ echo $ORACLE_SID $ORACLE_HOME $PATH
mani1 /u01/app/oracle/product/11.2.0/dbhome_1 /usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> select * from v$active_instances;
INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
1 rac1.localdomain:mani1
2 rac2.localdomain:mani2
SQL> col host_name for a25
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- -------------------------
mani1 rac1.localdomain
SQL> conn sys/*****@mani2 as sysdba
Connected.
SQL> select * from v$active_instances;
INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
1 rac1.localdomain:mani1
2 rac2.localdomain:mani2
SQL> col host_name for a25
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- -------------------------
mani2 rac2.localdomain
14) Shutdown the new database
[oracle@rac1 ~]$ export ORACLE_SID=mani1 ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@rac1 ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@rac1 ~]$ srvctl stop database -d mani
[oracle@rac1 ~]$ srvctl status database -d mani
Instance mani1 is not running on node rac1
Instance mani2 is not running on node rac2
[oracle@rac1 ~]$ srvctl status instance -d mani -i mani1
Instance mani1 is not running on node rac1
[oracle@rac1 ~]$ srvctl status instance -d mani -i mani2
Instance mani2 is not running on node rac2
[oracle@rac1 ~]$ srvctl status instance -d mani -n rac1
Instance mani1 is not running on node rac1
[oracle@rac1 ~]$ su root
Password:
[root@rac1 oracle]# . oraenv
ORACLE_SID = [mani1] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[root@rac1 oracle]# crs_stat -t -v
Name Type R/RA F/FT Target State Host
----------------------------------------------------------------------
ora.DATA.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora....P_02.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type 0/5 0/ ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac2
ora....N2.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac1
ora....N3.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac1
ora.asm ora.asm.type 0/5 0/ ONLINE ONLINE rac1
ora.babu.db ora....se.type 0/2 0/1 OFFLINE OFFLINE
ora.cvu ora.cvu.type 0/5 0/0 ONLINE ONLINE rac1
ora.gsd ora.gsd.type 0/5 0/ OFFLINE OFFLINE
ora.mani.db ora....se.type 0/2 0/1 OFFLINE OFFLINE
ora....network ora....rk.type 0/5 0/ ONLINE ONLINE rac1
ora.oc4j ora.oc4j.type 0/1 0/2 ONLINE ONLINE rac1
ora.ons ora.ons.type 0/3 0/ ONLINE ONLINE rac1
ora....SM1.asm application 0/5 0/0 ONLINE ONLINE rac1
ora....C1.lsnr application 0/5 0/0 ONLINE ONLINE rac1
ora.rac1.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac1.ons application 0/3 0/0 ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac1
ora....SM2.asm application 0/5 0/0 ONLINE ONLINE rac2
ora....C2.lsnr application 0/5 0/0 ONLINE ONLINE rac2
ora.rac2.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac2.ons application 0/3 0/0 ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac2
ora.scan1.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac2
ora.scan2.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac1
ora.scan3.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac1
15) Check the alert log to see of everything looks okay for the new database "mani"
export ORACLE_SID=mani1 ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
[oracle@rac1 ~]$ tail -70 /u01/app/oracle/diag/rdbms/mani/mani1/trace/alert_mani1.log
The End...
1 comment:
I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle 11g RAC Admin, kindly contact us http://www.maxmunus.com/contact
MaxMunus Offer World Class Virtual Instructor led training on Oracle 11g RAC Admin. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
For Demo Contact us:
Name : Arunkumar U
Email : arun@maxmunus.com
Skype id: training_maxmunus
Contact No.-+91-9738507310
Company Website –http://www.maxmunus.com
Post a Comment