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.
2 thoughts on “Converting a RAC database to single instance database – Oracle RAC 10g”
Excellent tip for remove RAC database manually.Thanks buddy keep it up.
How did you associate the undo tablespace to the thread you are removing?