Working environment
single-instance database 10.2.0.5 running on CentOS 5.5
ORACLE_HOME=/app/oracle/product/10.2.0/db
ORACLE_SID=rac10g
Datafiles, control files, redo logs are located on local file system: /app/oracle/oradata/rac10g
Steps to convert single-instance to RAC
Step 1) install clusterware 10.2.0.5 on all nodes of the cluster
Refer to instructions in chapter 4 and 5 of “Oracle Database Oracle Clusterware and Oracle Real Application Clusters Installation Guide”. You can also follow a detailed article “Building an Inexpensive Oracle RAC 10gR2 on Linux – (RHEL 5.3 / iSCSI) by Jeff Hunter
The version of clusterware has to be equal to or high than rdbms version(10.2.0.5). Patching clusterware to lastest patchset. Refer to metalink patch 8202632.
Step 2) install rdbms 10.2.0.5 rac version (software only) to all nodes of the cluster.
you need to install it to a different directory than the original ORACLE_HOME.
the new ORACLE_HOME is located at
/app/oracle/product/10.2.0/racdb
refer to patch 8202632 for patching to 10205.
Step 3) create ASM instance from new ORACLE_HOME and mount shared disk groups DATA and FRA.
Step 4) Make a full backup of single instance database.
Step 5) Move single-instance database’s datafiles, tempfiles, control files and redo logs from local file system to clustered ASM.
refer to another note “Move database from local file system to shared ASM diskgroups”
Afer completing this step
control files
+DATA/rac10g/controlfile/current.258.760834963
+DATA/rac10g/controlfile/current.257.760834965
+FRA/rac10g/controlfile/current.256.760834967
datafiles and tempfiles
+DATA/rac10g/datafile/system.256.760835015
+DATA/rac10g/datafile/undotbs1.259.760835071
+DATA/rac10g/datafile/sysaux.260.760835115
+DATA/rac10g/datafile/users.261.760835151
+DATA/rac10g/tempfile/temp.263.760835669
redo logs
GROUP# MEMBER
---------- --------------------------------------------
1 +DATA/rac10g/onlinelog/group_1.264.760836295
1 +FRA/rac10g/onlinelog/group_1.257.760836301
2 +FRA/rac10g/onlinelog/group_2.258.760837077
2 +DATA/rac10g/onlinelog/group_2.265.760837071
3 +FRA/rac10g/onlinelog/group_3.259.760837185
3 +DATA/rac10g/onlinelog/group_3.266.760837179
spfile
+DATA/rac10g/parameterfile/spfilerac11g.ora
pfile
spfile='+DATA/rac10g/parameterfile/spfilerac11g.ora'
Step 6) create a new pfile with entries about all rac instances
SQL> create pfile='/tmp/initrac1g.ora' from spfile;
Insert these lines into /tmp/initrac10g.ora:
*.cluster_database = TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
<SID1>.undo_tablespace=undotbs (undo tablespace which already exists)
<SID1>.instance_name=<SID1>
<SID1>.instance_number=1
<SID1>.thread=1
<SID1>.local_listener=<LISTENERNAME>_<SID>
<SID1>.remote_listener=LISTENERS_<DB_UNIQUE_NAME>
<SID2>.instance_name=<SID2>
<SID2>.instance_number=2
<SID2>.local_listener=<LISTENERNAME>_<SID>
<SID2>.remote_listener=LISTENERS_<DB_UNIQUE_NAME>
<SID2>.thread=2
<SID2>.undo_tablespace=UNDOTBS2
<SID2>.cluster_database = TRUE
<SID2>.cluster_database_instances = 2
in my case it has such entires
*.cluster_database = TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
rac10g1.undo_tablespace=UNDOTBS1
rac10g1.instance_name=rac10g1
rac10g1.instance_number=1
rac10g1.thread=1
rac10g1.local_listener=listener_rac10g1
rac10g1.remote_listener=listeners_rac10g
rac10g2.instance_name=rac10g2
rac10g2.instance_number=2
rac10g2.local_listener=listener_rac10g2
rac10g2.remote_listener=listeners_rac10g
rac10g2.thread=2
rac10g2.undo_tablespace=UNDOTBS2
rac10g2.cluster_database = TRUE
rac10g2.cluster_database_instances = 2
Step 7) create new spfile in shared ASM storage and a pfile with reference of spfile in new ORACLE_HOME
export ORACLE_SID=rac10g1
sqlplus / as sysdba
SQL> create spfile='+DATA/rac10g/parameterfile/spfilerac11g.ora' from pfile='/tmp/initrac10g.ora';
In the new ORACLE_HOME/dbs create a pfile ‘initrac10g1.ora’ containing a reference to spfile
[oracle@guang ~]$ export ORACLE_HOME=/app/oracle/product/10.2.0/racdb
[oracle@guang ~]$ cd $ORACLE_HOME/dbs
[oracle@guang dbs]$ vi initrac10g1.ora
spfile='+DATA/rac10g/parameterfile/spfilerac11g.ora'
Step 8 ) create password file for instance 1 in new ORACLE_HOME
[oracle@guang dbs]$ orapwd file=orapwrac10g1 password=oracle
Step 9) Set up cluster listeners and tnsnames entries for local_listener and remote_listener.
each cluster node is configured with a listener LISTENER_NODE
listener.ora on node 1
LISTENER_GUANG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = guang-vip.localdomain)(PORT = 1522)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1522)(IP = FIRST))
)
)
SID_LIST_LISTENER_GUANG =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /app/oracle/product/10.2.0/racdb)
(PROGRAM = extproc)
)
)
listener.ora on node 2
LISTENER_YANGMEI =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yangmei-vip.localdomain)(PORT = 1522)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1522)(IP = FIRST))
)
)
SID_LIST_LISTENER_YANGMEI =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /app/oracle/product/10.2.0/racdb)
(PROGRAM = extproc)
)
)
tnsnames.ora on node 1
# connection to database rac10g
rac10g =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = guang-vip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = yangmei-vip)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = rac10g)
)
)
# connection to only instance 1
rac10g1 =
(description=
(address=(protocol=tcp)(host=guang-vip)(port=1521))
(connect_data=
(service_name=rac10g)
(instance_name=rac10g1)))
# connection to only instance 2
rac10g2 =
(description=
(address=(protocol=tcp)(host=yangmei-vip)(port=1521))
(connect_data=
(service_name=rac10g)
(instance_name=rac10g2)))
# setting for local_listener of instance 1
listener_rac10g1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=guang-vip)(PORT=1522)))
# Setting for remote_listener of instance 2
listeners_rac10g =
(address_list=
(address=(protocol=tcp)(host=guang-vip)(port=1522))
(address=(protocol=tcp)(host=yangmei-vip)(port=1522)))
tnsnames.ora on node 2
# connection to database rac10g
rac10g =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = guang-vip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = yangmei-vip)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = rac10g)
)
)
# connection to only instance 1
rac10g1 =
(description=
(address=(protocol=tcp)(host=guang-vip)(port=1521))
(connect_data=
(service_name=rac10g)
(instance_name=rac10g1)))
# connection to only instance 2
rac10g2 =
(description=
(address=(protocol=tcp)(host=yangmei-vip)(port=1521))
(connect_data=
(service_name=rac10g)
(instance_name=rac10g2)))
# setting for local_listener of instance 2
listener_rac10g2 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=yangmei-vip)(PORT=1522)))
# Setting for remote_listener of instance 2
listeners_rac10g =
(address_list=
(address=(protocol=tcp)(host=guang-vip)(port=1522))
(address=(protocol=tcp)(host=yangmei-vip)(port=1522)))
NOTE: the values for local_listener and remote_listener must match settings in step 6!
For syntax of setting local_listener and remote_listener, refer to Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Installation Guide in Chapter 9 Understanding the Oracle Real Application Clusters Installed Configuration
Step 10) Start instance 1 in mount state
startup instance 1 using pfile created in step 7.
export ORACLE_SID=rac10g1
export ORACLE_HOME=/app/oracle/product/10.2.0./racdb
sqlplus / as sysdba
SQL> startup mount
Step 11) Add redo log thread for intance 2
Currently it has 3 redo log groups of thread 1, 2 members each as:
select l.group#, l.thread#, member, l.bytes, l.status from v$log l, v$logfile lf where lf.group# = l.group# order by group#;
GROUP# THREAD# MEMBER BYTES STATUS
---------- ---------- -------------------------------------------------- ---------- ----------------
1 1 +DATA/rac10g/onlinelog/group_1.264.760836295 52428800 INACTIVE
1 1 +FRA/rac10g/onlinelog/group_1.257.760836301 52428800 INACTIVE
2 1 +FRA/rac10g/onlinelog/group_2.258.760837077 52428800 CURRENT
2 1 +DATA/rac10g/onlinelog/group_2.265.760837071 52428800 CURRENT
3 1 +FRA/rac10g/onlinelog/group_3.259.760837185 52428800 INACTIVE
3 1 +DATA/rac10g/onlinelog/group_3.266.760837179 52428800 INACTIVE
6 rows selected.
I will add 3 groups for instance 2
alter database add logfile thread 2
group 4 ('+DATA',
'+FRA') size 50M,
group 5 ('+DATA',
'+FRA') size 50M,
group 6 ('+DATA',
'+FRA') size 50M;
# alter database enable public thread 2;
Step 12) open the database and enable redo log thread 2
alter database open;
alter database enable public thread 2;
Step 13) create a new undo tablespace for the second instance
create undo tablespace undotbs2 datafile '+DATA' size 100M;
The undo tablespace name ‘UNDOTBS2’ must match setting of <SID2>.undo_tablespace in step 6.
Step 14) create cluster specific view
sql> spool create_cluser_view.log
sql> @?/rdbms/admin/catclust.sql
sql> spool off
Step 15) add instance to /etc/oratab on node 1 and node 2
on node 1:
rac10g1:/app/oracle/product/10.2.0/racdb:N
on node 2
rac10g2:/app/oracle/product/10.2.0/racdb:N
set oracle environment in node 2
. .oraenv
Step 16) create a pfile in $ORACLE_HOME/dbs on node 2
vi initrac10g2.ora
spfile='+DATA/rac10g/parameterfile/spfilerac11g.ora'
Step 17) create password file in $ORACLE_HOME/dbs on node 2
orapwd file=orapwrac10g2 password=oracle
Step 18) create directories on node 2 for trace, audit, dump files same as in node 1
## on node 1
tree -d /app/oracle/admin/rac10g
/app/oracle/admin/rac10g
|-- adump
|-- bdump
|-- cdump
|-- dpdump
|-- pfile
`-- udump
## on node 2
cd /app/oracle/admin
mkdir rac10g
cd rac10g
mkdir adump bdump cdump dpdump pfile udump
Step 19) start up the second instance
. oraenv
ORACLE_SID = [rac10g2]
sqlplus / as sysdba
SQL> startup
Step 20) register database and instances to clusterware
on one of the nodes
srvctl add database -d rac10g -o /app/oracle/product/10.2.0/racdb -p '+DATA/rac10g/parameterfile/spfilerac11g.ora'
srvctl add instance -d rac10g -i rac10g1 -n guang
srvctl add instance -d rac10g -i rac10g2 -n yangmei
-- add ASM dependency to intances
srvctl modify instance -d rac10g -i rac10g1 -s +ASM1
srvctl modify instance -d rac10g -i rac10g2 -s +ASM2