Summary of Steps to Convert RAC to Non-RAC Setup:
There are several scenarios for this situation:
1. Converting RAC instances to non-RAC instances permanently without keeping the Oracle Clusterware.
2. Converting RAC instances to non-RAC instances permanently, but still keeping the Oracle Clusterware.
3. Converting RAC instances to non-RAC instances temporarily, so the production DB can continue running while troubleshooting of RAC issues is in progress.
In all cases, the Clusterware should be shutdown on other nodes to avoid conflicts of the operations.
1. Converting RAC instances to non-RAC instances permanently without keeping the Oracle Clusterware.
a.) Shutdown clusterware on ALL nodes with root user.
b.) Run rootdelete and rootdeinstall with root user.
c.) Run installer and remove the Clusterware home with crs user.
d.) Install a new single instance home with oracle user. Also a separate ASM home if preferred.
e.) As oracle user, remove the listener using netca from the OLD home. Create a new local listener using netca from the NEW home. The listener will not listen to VIP anymore. Change existing tnsnames.ora files on server and/or clients to use host IP instead of VIP.
f.) Configure ASM using dbca from the new home with oracle user. Follow the instruction from dbca to create non-RAC CSS using “localconfig add” with root user.
g.) With oracle user, copy the pfile/spfile from the old DB home to the new DB home, remove all the parameters for other instances in the pfile/spfile.
— Remove cluster_database and cluster_database_instances parameters.
— Remove undo_tablespace parameter for the other instances.
— Remove remote_listener and local_listener parameters if present.
h.) Startup new listener and ASM with oracle user. Make sure ASM diskgroups are mounted.
i.) With oracle user, startup the database in mount stage and execute
alter database disable thread
;
alter database open;
j.) After opening database you can drop the redolog groups and/or tablespaces which are for other instances.
k.) With oracle user, modify the ORACLE_HOME on /etc/oratab. And remove instance_number and thread parameters in the pfile/spfile.
l.) With oracle user, run installer to remove the OLD ORACLE_HOME.
2. Converting RAC instances to non-RAC instances permanently, but still keeping the Oracle Clusterware.
In this scenario, it is also recommended to just install a Single Instance home and then start ASM and Database instances from the new home. (If preferred, a separate single-instance ASM home can be installed.) So, the inventory can be in sync of the changes, and this could prevent related problems in the future.
a.) Leave the Oracle Clusterware as it is.
b.) With oracle user, install a new SI home (runInstaller gives you option to install RAC enabled home or SI home). Also a separate ASM home if preferred.
c.) With root user, stop Clusterware on all node except the current node. Stop DB/ASM instances and remove instance registries in the OCR using the srvctl from the old home with crs user.
$ srvctl remove instance -d -i
$ srvctl remove database -d
$ srvctl remove asm -n [-i ]
d.) With oracle user, remove listeners using netca from the old home.
e.) With oracle user, create a new listener using netca from the new home. If the listener will not listen to VIP anymore, change existing tnsnames.ora files on server and/or clients to use host IP instead of VIP.
f.) With oracle user, configure single instance ASM using dbca from the new home.
g.) Convert and start DB instance from the new home. See step g to l in scenario #1.
h.) If preferred, register DB to the OCR.
3. Converting RAC instances to non-RAC instances temporarily, so the production DB can continue running while troubleshooting of RAC issues is in progress.
** Please note that while staying in this transition status, please DO NOT apply any RDBMS patch without first converting back to RAC.
a.) Shutdown all instances including ASM and DB instances in RAC environment on ALL nodes with oracle user.
b.) Shutdown all the listeners on ALL nodes with oracle user.
c.) With oracle user, relink Oracle executable with rac_off option. (For both ASM and DB homes)
$ make -f ins_rdbms.mk rac_off
$ make -f ins_rdbms.mk ioracle
d.) With oracle user, remove all parameters for other instances in the pfile/spfile.
— Remove cluster_database and cluster_database_instances parameters. (For both ASM and DB)
— Remove undo_tablespace parameter for the other instances. (For DB only)
e.) With oracle user, startup listener and ASM. Make sure ASM diskgroups are mounted.
f.) With oracle user, startup the database in mount stage and execute
alter database disable thread ;
alter database open;
g.) After opening database you can drop the redolog groups and/or tablespaces which are for other instances.
h.) Disable autostart of ASM/DB/Clusterware. (Re-enable them after fixing the CRS/RAC issue)
$ srvctl modify database -d -y manual
$ srvctl disable asm -n
# crsctl disable crs
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Ref:
How to Convert RAC ASM/DB instances to non-RAC ASM/DB instances (Doc ID 759868.1)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1) Verify database running status on all nodes.
[oracle@host01 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node host01
Instance RAC2 is running on node host02
2) Stop database using srvctl
[oracle@host01 ~]$ srvctl stop database -d RAC
3) Remove the database entry from crs
[oracle@host01 ~]$ srvctl remove instance -d RAC -i RAC2
Remove instance RAC2 from the database RAC? (y/[n]) y
[oracle@host01 ~]$ srvctl remove instance -d RAC -i RAC1
Remove instance RAC1 from the database RAC? (y/[n]) y
4) Start the database on first instance
[oracle@host01 ~]$ dba
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Sep 16 20:01:54 2011Copyright (c) 1982, 2008, Oracle. All rights reserved.Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2.6724E+10 bytes
Fixed Size 2160272 bytes
Variable Size 1.4764E+10 bytes
Database Buffers 1.1811E+10 bytes
Redo Buffers 146423808 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.
5) Delete the unwanted thread and redo logfiles
SQL> select thread#, group# from v$log order by 1;
THREAD# GROUP#
———- ———-
1 5
1 6
1 18
1 16
1 14
1 13
1 10
2 12
2 15
2 11
2 17
THREAD# GROUP#
———- ———-
2 9
2 8
2 7
2 19
15 rows selected.
SQL> alter database drop logfile group 12;
Database altered.
SQL> alter database drop logfile group 15;
Database altered.
SQL> alter database drop logfile group 11;
Database altered.
SQL> alter database drop logfile group 17;
Database altered.
SQL> alter database drop logfile group 9;
Database altered.
SQL> alter database drop logfile group 8;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
SQL> alter database drop logfile group 19;
Database altered.
SQL> select thread#, group# from v$log order by 1;
THREAD# GROUP#
———- ———-
1 5
1 6
1 10
1 18
1 14
1 16
1 13
7 rows selected.
SQL>
6) Drop the unwanted undo tablespace
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
7) Create pfile from spfile
SQL> create pfile from spfile;
File created.
SQL> shut immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options[oracle@host01 ~]$
8) Remove all references of second instance from the pfile and start the instance using pfile. Later you can again create spfile from pfile.
— removed all RAC2 references.
9) Startup the database and make sure all look good.
[oracle@host01 dbs]$ dba
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Sep 16 20:09:28 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 2.6724E+10 bytes
Fixed Size 2160272 bytes
Variable Size 1.4764E+10 bytes
Database Buffers 1.1811E+10 bytes
Redo Buffers 146423808 bytes
Database mounted.
Database opened.
SQL>
