Converting a single-instance database to RAC Manually

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

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: