Assuming that you have successfully installed Grid Infrastructure 11gR2, Oracle Universal Installer (OUI) probably offers the best approach to create a new RAC-enabled database and instances on all available nodes. What if you already have an existing single-instance database that you want to RAC-ify? Should you use OUI? Yes, you can, but OUI option may not work for you as it didn't for us. With OUI, you need to create a template and then create a new RAC-enabled database based on the template. Oracle e-Biz Suite also suggests using a script to RAC-ify a single-instance database. We didn't use this script either. Instead, we used a manual approach as described below. It worked quite well for us. Downtime was quite minimal as well.
This example assumes that your data files, control files, redo log files, temp files and spfile are already on ASM. Of course, underlying grid infrastructure is successfully installed and configured.
This example shows how to convert a single-instance database with HRP instance into RAC-enabled database with HRP1 and HRP2 instances.
This example shows how to convert a single-instance database with HRP instance into RAC-enabled database with HRP1 and HRP2 instances.
Step 1: Create a 2nd Redo Log Thread
SQL> select group#, thread#, member from v$logfile ;
GROUP# THREAD# STATUS
---------- ---------- ----------------
4 1 CURRENT
5 1 INACTIVE
6 1 INACTIVE
SQL> alter database add logfile thread 2 group 1 size 10m ;
Database altered.
SQL> alter database add logfile thread 2 group 2 size 10m ;
Database altered.
SQL> alter database add logfile thread 2 group 3 size 10m ;
Database altered.
SQL> select group#, thread#, status from v$log ;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 2 UNUSED
2 2 UNUSED
3 2 UNUSED
4 1 CURRENT
5 1 INACTIVE
6 1 INACTIVE
Step 2: Create a 2nd undo tablespace
SQL> create undo tablespace undotbs2
2 datafile '+DATA_HRP' size 50M ;
Tablespace created.
SQL> select file_name from dba_data_files ;
FILE_NAME
--------------------------------------------------------------------------------
+DATA_HRP/hrp/datafile/users.282.758901127
+DATA_HRP/hrp/datafile/undotbs1.279.758901123
+DATA_HRP/hrp/datafile/sysaux.278.758901117
+DATA_HRP/hrp/datafile/system.277.758901101
+DATA_HRP/hrp/datafile/test.280.758901125
+DATA_HRP/hrp/datafile/undotbs2.273.758911621
6 rows selected.
Step 3: Update RAC specific parameters
ALTER DATABASE ENABLE THREAD 2;
ALTER SYSTEM SET THREAD=1 SCOPE=SPFILE SID='HRP1';
ALTER SYSTEM SET THREAD=2 SCOPE=SPFILE SID='HRP2';
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1 SCOPE=SPFILE SID='HRP1';
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=SPFILE SID='HRP2';
ALTER SYSTEM SET INSTANCE_NUMBER=1 SCOPE=SPFILE SID='HRP1';
ALTER SYSTEM SET INSTANCE_NUMBER=2 SCOPE=SPFILE SID='HRP2';
Step 4: Rename Instance on Node 1 to HRP1
[oracle@racnode1 dbs]$ cat initHRP.ora
SPFILE='+DATA_HRP/hrp/spfilehrp.ora'
[oracle@racnode1 dbs]$ cp initHRP.ora initHRP1.ora
Step 5: Start HRP1 Instance on Node 1
[oracle@racnode1 dbs]$ export ORACLE_SID=HRP1
[oracle@racnode1 dbs]$ sqlplus / as sysdba
SQL> startup ;
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2226832 bytes
Variable Size 402654576 bytes
Database Buffers 1157627904 bytes
Redo Buffers 7499776 bytes
Database mounted.
Database opened.
SQL> select name, value from v$parameter where isdefault ='FALSE' order by 1;
NAME VALUE
------------------------------ --------------------------------------------------
audit_file_dest /u05/app/oracle/admin/HRP/adump
audit_trail DB
compatible 11.2.0.0.0
control_files +DATA_HRP/hrp/controlfile/control01.ctl
db_block_size 8192
db_create_file_dest +DATA_HRP
db_domain
db_name HRP
diagnostic_dest /u05/app/oracle
dispatchers (PROTOCOL=TCP) (SERVICE=HRPXDB)
instance_number 1
open_cursors 300
pga_aggregate_target 524288000
processes 150
remote_login_passwordfile EXCLUSIVE
sga_target 1577058304
spfile +DATA_HRP/hrp/spfilehrp.ora
thread 1
undo_tablespace UNDOTBS1
Step 6: Start HRP1 Instance on Node 1 as RAC Instance
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET CLUSTER_DATABASE_INSTANCES=2 SCOPE=SPFILE;
System altered.
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2226832 bytes
Variable Size 402654576 bytes
Database Buffers 1157627904 bytes
Redo Buffers 7499776 bytes
Database mounted.
Database opened.
Step 7: Run catclust
SQL> start ?/rdbms/admin/catclust.sql
Step 8: Start HRP Instance on Node 2
[oracle@racnode2 dbs]$ cat initHRP2.ora
SPFILE='+DATA_HRP/hrp/spfileHRP.ora'
[oracle@racnode2 hdump]$ sqlplus / as sysdba
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2226832 bytes
Variable Size 469763440 bytes
Database Buffers 1090519040 bytes
Redo Buffers 7499776 bytes
SQL> alter database mount ;
Database altered.
SQL> alter database open ;
Database altered.
Step 9: Manage both instances via srvctl
[oracle@racnode1 Scripts]$ srvctl remove database -d HRP
PRKO-3141 : Database HRP could not be removed because it was running
[oracle@racnode1 Scripts]$ srvctl add database -d HRP -o $ORACLE_HOME
PRCS-1007 : Server pool HRP already exists
PRCR-1086 : server pool ora.HRP is already registered
[oracle@racnode1 Scripts]$ srvctl add instance -d HRP -i HRP1 -n racnode1
PRCD-1022 : Cannot add a second instance to a single instance database
[oracle@racnode1 Scripts]$ srvctl add database -d HRP -o $ORACLE_HOME
[oracle@racnode1 Scripts]$ srvctl add instance -d HRP -i HRP1 -n racnode1
[oracle@racnode1 Scripts]$ srvctl add instance -d HRP -i HRP2 -n racnode2
[oracle@racnode1 Scripts]$ srvctl start database -d HRP
Step 10: Verify
SQL> select * from gv$instance ;
INST_ID 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 1 HRP1
racnode1
11.2.0.2.0 12-AUG-11 OPEN YES 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
2 2 HRP2
racnode2
11.2.0.2.0 12-AUG-11 OPEN YES 2 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
[oracle@racnode1 Scripts]$ srvctl config database -d HRP
Database unique name: HRP
Database name:
Oracle home: /u05/app/oracle/db/11.2.0.2
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: HRP
Database instances: HRP1,HRP2
Disk Groups: DATA_HRP
Mount point paths:
Services:
Type: RAC
Database is administrator managed
