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;
