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

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 -- Remove instances then the database

$ srvctl remove instance -d prod -i prod1
Remove instance prod1 from the database prod? (y/[n]) y
$ srvctl remove instance -d prod -i prod2
Remove instance prod2 from the database prod? (y/[n]) y
$ srvctl remove database -d prod
Remove the database prod? (y/[n]) y

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.