Convert Oracle RAC to single instance

출처 : http://fdsblog.tistory.com/entry/Convert-Oracle-RAC-to-single-instance

Oracle 관련 자료를 보면 Single Instacne를 RAC로 전환하는 자료는 Oracle 관련 사이트에 잘 정리된 자료가 많지만, RAC 환경에서 Single instance로의 전환 자료는 찾아 보기가 힘드네요.
아래에 정리가 잘 되어 있네요.

My last post about Turn on/off RAC basically is to implement Converting/Migrating Oracle RAC to Single instance.  I try to find a way to do this in Google but it seem no result regarding this. You can find thousand of article on converting Single Instance to Oracle RAC but not the other way around. Why?

You'll find a Doc. on Metalink with step by step instructions on "How to convert a
single instance Database to RAC", but not the other way round.
The analyst's response from Oracle is "We cannot convert a RAC database into a Single
instance database, that is the reason why you didnt find any steps.
It is not supported.

Why Oracle mentioned it is not supported? It does not makes any sense that you cannot convert from RAC to non-RAC environment, unless Oracle does not want their customers to go away from RAC.
Anyway…it’s all about $$$$$.
So, based on my knowledge on how Oracle RAC work, I’ve successfully done it.

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 mydb # 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
# /oracle/product/10.2.0/db/bin/localconfig delete
# /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