Tuesday, November 15, 2011

RAC-ify an Oracle Database in Ten Steps - Convert a single-instance database into RAC

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.

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

SQL> shutdown immediate ;
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


4 comments:

  1. Thankyou Jay for the information. It helped me to convert my single instance in 11.2.0.2 to convert to RAC successfully

    ReplyDelete
  2. Good Write Up.
    Did you miss stop and remove database in Step 9 before adding the database and instance?

    ReplyDelete
  3. Here the problem is the database got registerd as single instance you can
    verify it using srvctl status database -d db_name

    You will see TYPE : Single

    You can follow below steps
    1) Remove database
    Remove the database from cluster using srvctl
    srvctl remove database -d HRP
    2) Add database back
    While adding you need to specify -dbtype RAC


    Reverify database status using srvctl and make is sure Type is RAC

    ReplyDelete