Converting a RAC database to single instance database – Oracle RAC 10g

This post will brief you about steps to convert a RAC database to single instance.
I don’t think this is supported by oracle as I was not able to find any document or metalink note ID which will provide the steps. So please do not try the same in production environment unless you have a conformation from Oracle Support.
However I tried the following steps in test instance an it worked for me.
1) Stop database using srvctl
srvctl stop database -d prod
2) Remove the database entry from crs
bash-3.00$ srvctl remove instance -d prod -i prod1
Remove instance prod1 from the database prod? (y/[n]) y
bash-3.00$ srvctl remove instance -d prod -i prod2
Remove instance prod2 from the database prod? (y/[n]) y
bash-3.00$ srvctl remove database -d prod
Remove the database prod? (y/[n]) y
bash-3.00$
3) Start the database on first instance
SQL> startup
ORACLE instance started.
Total System Global Area  838860800 bytes
Fixed Size                  1222168 bytes
Variable Size             188746216 bytes
Database Buffers          645922816 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> alter system set cluster_database_instances=1 scope=spfile;
System altered.
SQL> alter database disable thread 2;
Database altered.
4) Delete the unwanted thread and redo logfiles
SQL> select thread#, group# from v$log;
THREAD#     GROUP#
---------- ----------
1          1
1          2
1          3
2          4
2          5
2          6
6 rows selected.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
5) Drop the unwanted undo tablespace
SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.
6) Create pfile from spfile
SQL> create pfile from spfile;

File created.

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
7) Remove all references of second instance from the pfile and start the instance using pfile. Later you can again create spfile from pfile.

One Comment

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