Mani's Blog

March 15, 2011

>Convert Oracle RAC instance to Single Non-RAC instace.

Filed under: Oracle — mani @ 2:23 pm
Tags:

>

I was in a situation,  we need to get rid of SAN storage dependency on all our QA environment.  We had one DB environment with RAC setup.   I had to convert RAC instance into single instance and move the Database to SSD storage from SAN.

This post will brief you about steps to convert a RAC database to single instance.

I don’t think this method 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 this in production environment unless you have a conformation from Oracle Support.
However I tried the following steps in QA instance an it worked for me.1) Verify database running status on all nodes.
[oracle@m120319 ~]$ srvctl status database -d XNB1RAC
Instance XNB1RAC1 is running on node m120319
Instance XNB1RAC2 is running on node m12031a

2) Stop database using srvctl
[oracle@m120319 ~]$ srvctl stop database -d XNB1RAC
3) Remove the database entry from crs
[oracle@m120319 ~]$ srvctl remove instance -d XNB1RAC -i XNB1RAC2 
Remove instance XNB1RAC2 from the database XNB1RAC? (y/[n]) y 
[oracle@m120319 ~]$ srvctl remove instance -d XNB1RAC -i XNB1RAC1

Remove instance XNB1RAC1 from the database XNB1RAC? (y/[n]) y
4) Start the database on first instance
[oracle@m120319 ~]$ dba

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 9 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 bytesVariable Size   1.4764E+10 bytesDatabase Buffers  1.1811E+10 bytesRedo Buffers    146423808 bytesDatabase 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
2 ;

Database altered.

SQL> alter database drop logfile group 8;

Database altered.

SQL> alter database drop logfile group 7
2 ;

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@m120319 ~]$
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 XNB1RAC2 references.

9) Startup the database and make sure all look good.
[oracle@m120319 dbs]$ dba

SQL*Plus: Release 11.1.0.7.0 – Production on Wed Mar 9 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>
SQL>
SQL>

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: