You will find number of articles on My Oracle Support (metalink) and Google for converting single instance database to RAC but its hard to find the documents to convert RAC to single instance.
I will present two methods to convert the RAC to single instance.
i. Converting RAC to single instance on same machine.
ii. Copying the RAC database to another machine and converting that to Single Instance
Converting RAC to single instance on same machine
This is based on Oracle 10G Release 2 and assumes:
1. Oracle RAC running with cluster file system
2. You have basic knowledge about Oracle RAC
Test Server:
OS : Red Hat Enterprise Linux Server release 5.4
Database Version : 10.2.0.4
File system: OCFS2
1. Stop database and CRS on both node $ srvctl stop database -d RACDB
# crsctl stop crs
2. Turn Off RAC
SQL> startup
ORA-29702 error occurred in Cluster Group Service operation
Relink with the RAC OFF. $ cd $ORACLE_HOME/rdbms/lib
$ /usr/ccs/bin/make -f ins_rdbms.mk rac_off
Relinking oracle $ make -f ins_rdbms.mk ioracle
## OR , both working fine $ cd $ORACLE_HOME/bin
$ relink oracle
If ASM Instance Exist, run below command as root # /u01/oracle/product/10.2.0/db/bin/localconfig delete
# /u01/oracle/product/10.2.0/db/bin/localconfig add
3. Parameter(Pfile/spfile) & database changes SQL> startup
SQL> alter database disable thread 2;
SQL> alter system set remote_listener='';
3a. Remove unwanted logfile SQL> select thread#, group# from v$log;
SQL> alter database drop logfile group 3;
SQL> alter database drop logfile group 4;
3b. Remove unwanted tablespace SQL> drop tablespace UNDOTBS2 including contents and datafiles;
3c. Rename instance name. SQL> alter system set instance_name=<new_name> scope=spfile;
SQL> shutdown immediate
SQL> startup
- Change your ORACLE_SID environment
4. Run $ORA_CRS_HOME/install/rootdelete.sh on both node
- This will stop and remove all CRS startup related file
5. Remove $ORA_CRS_HOME binary using Clusterware OUI installer
- Ignore any error if 2nd node already down
- rm -rf $ORA_CRS_HOME
6. Modify listener file $ vi $ORACLE_HOME/network/admin/listener.ora
6a. Modify tnsname file $ vi $ORACLE_HOME/network/admin/tnsnames.ora
That’s it. You have successfully converted your RAC database to Single Instance on same machine.
Note: You can convert your single instance DB to RAC again by following metalink note:747457.1
Copying the RAC database to another machine and converting that to Single Instance
You often need to copy your production database to test environment to reproduce any defect or to have same replica of your production environment. This way you will required both RAC and single instance setup available in parallel.
I assume that you have following things available on your system.
Database Version : 10.2.0.4
File system: OCFS2 (I will update the document for ASM based RAC database migration in few days)
This will be updated shortly InshaAllah……………….
