Convert Single Instance Database to Oracle RAC One Node Database / RAC 1 node database

I had the recent need to convert a single instance DB to a RAC 1-node. I haven't seen many documents
which talks about the steps involved As most docs focus on conversion from single instance to a regular RAC.

In the past, I have used DBCA to create a 1-Node RAC and always used the "Generate database creation scripts" at the end. Then I have had manually edited the scripts generated to suit my purpose and this approach has served me very well as allowing dbca to create a DB directly has caused many unnecessary schemas to creep in.

I drove into the scripts and to my best abilities, tried to reproduce what it does and have written the below blog.


Briefly here are the steps to convert a single instance DB to 1 Node RAC.

Before you start, ensure
+++++++++++++++++++++

I assume you just have single instance init.ora parameters enabled.

No RAC init.ora parameters at this point should be enabled especially CLUSTER_DATABASE.

You might want to consider setting the below init.ora parameters to valid ASM disk group

db_create_file_dest="+DATA_DEV"
db_recovery_file_dest="+FRA_DEV"

Please make sure that your control files, data files, redo logs are already in ASM before you perform these steps.



I also recommend that you use init.ora file which internally points to a spfile as shown below.

Also make sure, you have x init.ora files (where x is the number of nodes)in each node's ORACLE_HOME/dbs directory
with the naming convention init<DB Name>_1.ora, init<DB Name>_2.ora, init<DB Name>_3.ora etc

For example,since my setup is a 2-Node RAC, I have 2 init.ora files with the name init<DB Name>_1.ora and init<DB Name>_2.ora in
ORACLE_HOME/dbs directory of both the RAC nodes.

$ cat initTESTDB_1.ora
SPFILE='+DATA_DEV/TESTDB/spfileTESTDB.ora'          


$ cat initTESTDB_2.ora
SPFILE='+DATA_DEV/TESTDB/spfileTESTDB.ora'          





My Oracle Home is /u02/app/oracle/product/11.2.0.4/db_1 and DB Name is TESTDB.

The Grid Home is /u02/app/11.2.0/grid .


1)Create the password file if it does not exist (recommended)  on 1 node and copy it to other nodes.
Please note that there is NO suffix to password file name.

/u02/app/oracle/product/11.2.0.4/db_1/bin/orapwd file=/u02/app/oracle/product/11.2.0.4/db_1/dbs/orapwTESTDB force=y

Make the following change in your init.ora file(If using spfile perform an alter system set)
remote_login_passwordfile=exclusive

Please shutdown the DB at this point.



2)This can be skipped if already performed(please note dbca automatically does it if you have ever created a DB on this
OH in the past) but again no harm in doing this again.

/u02/app/11.2.0/grid/bin/setasmgidwrap o=/u02/app/oracle/product/11.2.0.4/db_1/bin/oracle

3)Add the database to CRS.

/u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl add database -d TESTDB -o /u02/app/oracle/product/11.2.0.4/db_1 -p +DATA_DEV/TESTDB/spfileTESTDB.ora -n TESTDB -c RACONENODE -e node1,node2 -i TESTDB -a "DATA_DEV,FRA_DEV"

Please pay particular attention to
(a)The option "-c RACONENODE"
(b)The places which will need you to plug in your ASM disk group.In my case , I have two of them called DATA_DEV,FRA_DEV

(4) Add a service.
/u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl add service -s TESTDB.gautham.com -d TESTDB -P BASIC

(5)Disable the database
/u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl disable database -d TESTDB

(6)Startup the database normally via

sqlplus '/ as sysdba'
startup

(7) Add the redo log groups for thread 2.

I assume that GROUP 1 and GROUP 2 are the only 2 groups in the single instance DB belonging to thread 1.By default, thread 1 should already exist. If there
are more than 2 groups in thread 1, then change the group name below accordingly .

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3  SIZE 250m,
 GROUP 4  SIZE 250m;

ALTER DATABASE ENABLE PUBLIC THREAD 2;

Repeat it with THREAD 3 , if it is a 3 node RAC and so on.


(8) There is no need to add a 2nd Tablespace as this is a 1-Node RAC and only 1 instance will be acive at any point.

(9) Set the 2 parameters in the spfile to


ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;
ALTER SYSTEM SET remote_listener='<scan name>:1521' scope=spfile ;


(10)Shutdown the DB via a normal "shutdown immediate" command.

(11) /u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl enable database -d TESTDB;
(12) /u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl start database -d TESTDB;