Convert Single Instance Databases to RAC

Convert Single Instance Databases to RAC

1.1 Overview

We have Oracle grid infrastructure 11.2.0.3 with ASM installed but we most databases running as standalones instead of RAC databases. Recently we decide to convert all databases to RAC databases. The database version is 11.2.0.3. They are already running on the RAC home and on the grid cluster.

Oracle provide 4 ways to convert a standalone database to a RAC database: DBCA, manual, RConfig and OEM.

With DBCA, we can backup the database as a template and then create a RAC database with the template. This way is simple but the long down time is not acceptable in most environments so we quickly excluded as a choice.

I found the manual way is most reliable. You know exactly what each step does. It has the minimum downtime. The drawback is you have to do the manual steps for each database. If you have 100 database, you might not like to repeat the steps again, again and again.

The RConfig is the most applicable way. All we need to do is to create an xml file and run a command. The benefit is once you create the first xml file, you can reuse it with very minimum modification. Sometimes you only need to modify the name of the database. You can run multiple rconfig at the same time.

OEM may appears like the easiest way as simple as a few clicks. But it turned out that I never make it to work. I actually never spend the time to fix the issue. It's not worth the time to troubleshoot OEM. I just went with RConfig as it's easy enough.

In this article, we provides 2 samples. First one with manual steps and the second one use RConfig. Our assumption is that the database already running in the RAC home. If your database is not currently in the RAC home, you need to move it there first. You can do it with RMAN or datapump, or manually copy the files and recreate the control file. If your database is not in the same version, you might want to upgrade or patch to the same patch level before you move your database.

1.2 Manual Conversion of Standalone database to RAC

1.      Create a temporary PFILE from the SPFILE and add cluster parameters.

CREATE PFILE='/u03/oracle/wangm/pfile' from spfile;

Edit the pfile created and add the following RAC related parameters:

cluster_database_instances=2

cluster_database=true

E91DEV1.instance_number=1

E91DEV2.instance_number=2

E91DEV1.thread=1

E91DEV2.thread=2

E91DEV1.undo_tablespace='PSUNDO01'

E91DEV2.undo_tablespace='PSUNDO02'

E91DEV1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=devclstr1-vip)(PORT=1521))'

E91DEV2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=devclstr2-vip)(PORT=1521))'

Note you need to delete the two parameters:

undo_tablespace='PSUNDO01'

local_listener=’ DEVCLSTR1_LOCAL’

1.      Create additional UNDO tablespace.

create undo tablespace psundo02 datafile '+DATA';

1.      Create redo threads and enable the second thread.

E91DEV> alter database add logfile thread 2 group 5 ('+DATA','+FLASH') size 128m;

Database altered.

E91DEV> alter database add logfile thread 2 group 6 ('+DATA','+FLASH') size 128m;

Database altered.

E91DEV> alter database add logfile thread 2 group 7 ('+DATA','+FLASH') size 128m;

Database altered.

E91DEV> alter database add logfile thread 2 group 8 ('+DATA','+FLASH') size 128m;

Database altered.

E91DEV> alter database enable public thread 2;

Database altered.

E91DEV>

1.      Copy the Oracle password file and init file from the initial node, or from the node from which you are working, to the corresponding location on the additional nodes on which the cluster database will have an instance. Replace the ORACLE_SID name in each password file appropriately for each additional instance.

devclstr1-/u03/oracle/wangm>cd $ORACLE_HOME/dbs

devclstr1-/u01/app/oracle/product/11.2/dbs>mv orapwE91DEV orapwE91DEV1

devclstr1-/u01/app/oracle/product/11.2/dbs>mv initE91DEV.ora initE91DEV1.ora

devclstr1-/u01/app/oracle/product/11.2/dbs>scp -p orapwE91DEV1 devclstr2:/u01/app/oracle/product/11.2/dbs/orapwE91DEV2

orapwE91DEV1 100% 2560 2.5KB/s 00:00

devclstr1-/u01/app/oracle/product/11.2/dbs>scp -p initE91DEV1.ora devclstr2:/u01/app/oracle/product/11.2/dbs/initE91DEV2.ora

initE91DEV1.ora

1.      Create the SPFILE from the updated PFILE.

E91DEV> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

E91DEV> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

devclstr1-/u03/oracle/wangm>export ORACLE_SID=E91DEV1

devclstr1-/u03/oracle/wangm>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 25 15:52:16 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

none> create spfile='+DATA/E91DEV/spfileE91DEV.ora' from pfile='/u03/oracle/wangm/pfile';

File created.

none> startup

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size 2230072 bytes

Variable Size 654313672 bytes

Database Buffers 1442840576 bytes

Redo Buffers 38502400 bytes

Database mounted.

Database opened.

none>

1.      On the local node, run catclust.sql. This command creates the dictionary views needed for Oracle RAC databases. For example:

SQL> start ?/rdbms/admin/catclust.sql

1.      Add the configuration for the Oracle RAC database and its instance-to-node mapping using SRVCTL.

devclstr1-/u03/oracle/wangm>srvctl add database -d E91DEV -o /u01/app/oracle/product/11.2

devclstr1-/u03/oracle/wangm>srvctl add instance -d E91DEV -i E91DEV1 -n devclstr1

devclstr1-/u03/oracle/wangm>srvctl add instance -d E91DEV -i E91DEV2 -n devclstr2

devclstr1-/u03/oracle/wangm>

1.      Start the database using SRVCTL.

devclstr1-/u03/oracle/wangm>srvctl start database -d E91DEV

devclstr1-/u03/oracle/wangm>srvctl status database -d E91DEV

Instance E91DEV1 is running on node devclstr1

Instance E91DEV2 is running on node devclstr2

devclstr1-/u03/oracle/wangm>

After starting the database with SRVCTL, your conversion process is complete and, for example, you can run the following SQL statement to see the status of all the instances in your Oracle RAC database:

E91DEV> select * from v$active_instances;

INST_NUMBER INST_NAME

----------- ------------------------------------------------------------

1 devclstr1:E91DEV1

2 devclstr2:E91DEV2

E91DEV>

1.3 Converting with RConfig

1.      As the user, copy the file $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml to a working directory and edit the file.

Here is the file E91TRP.xml after modification.

<?xml version="1.0" encoding="UTF-8"?>

<n:RConfig xmlns:n="http://www.oracle.com/rconfig"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://www.oracle.com/rconfig rconfig.xsd">

<n:ConvertToRAC>

<!-- Verify does a precheck to ensure all pre-requisites are met, before the conversion is attempted. Allowable values are: YES|NO|ONLY -->

<n:Convert verify="ONLY">

<!--Specify current OracleHome of non-rac database for SourceDBHome -->

<n:SourceDBHome>/u01/app/oracle/product/11.2</n:SourceDBHome>

<!--Specify OracleHome where the rac database should be configured. It can be same as SourceDBHome -->

<n:TargetDBHome>/u01/app/oracle/product/11.2</n:TargetDBHome>

<!--Specify SID of non-rac database and credential. User with sysdba role is required to perform conversion -->

<n:SourceDBInfo SID="E91TPR">

<n:Credentials>

<n:User>sys</n:User>

<n:Password>password</n:Password>

<n:Role>sysdba</n:Role>

</n:Credentials>

</n:SourceDBInfo>

<!--Specify the list of nodes that should have rac instances running for the Admin Managed Cluster Database. LocalNode should be the first node in this nodelist. --> <n:NodeList>

<n:Node name="devclstr3"/>

<n:Node name="devclstr2"/>

</n:NodeList>

<!--Specify RacOneNode along with servicename to convert database to RACOne Node -->

<!--n:RacOneNode servicename="salesrac1service"/-->

<!--Instance Prefix tag is optional starting with 11.2. If left empty, it is derived from db_unique_name.-->

<n:InstancePrefix></n:InstancePrefix>

<!-- Listener details are no longer needed starting 11.2. Database is registered with default listener and SCAN listener running from Oracle Grid Infrastructure home. -->

<!--Specify the type of storage to be used by rac database. Allowable values are CFS|ASM. The non-rac database should have same storage type. ASM credentials are no needed for conversion. -->

<n:SharedStorage type="ASM">

<!--Specify Database Area Location to be configured for rac database.If this field is left empty, current storage will be used for rac database. For CFS, this field will have directory path. -->

<n:TargetDatabaseArea></n:TargetDatabaseArea>

<!--Specify Fast Recovery Area to be configured for rac database. If this field is left empty, current recovery area of non-rac database will be configured for rac database. If current database is not using recovery Area, the resulting rac database will not have a recovery area. -->

<n:TargetFlashRecoveryArea></n:TargetFlashRecoveryArea>

</n:SharedStorage>

</n:Convert>

</n:ConvertToRAC>

</n:RConfig>

1.      Run :

First we set the verify in the xml to “ONLY” to make sure we can convert.

<n:Convert verify="ONLY">

devclstr1-/u03/oracle/wangm>rconfig E91TPR.xml

<?xml version="1.0" ?>

<RConfig version="1.1" >

<ConvertToRAC>

<Convert>

<Response>

<Result code="0" >

Operation Succeeded

</Result>

</Response>

<ReturnValue type="object">

There is no return value for this step </ReturnValue>

</Convert>

</ConvertToRAC></RConfig>

devclstr1-/u03/oracle/wangm>

Then we set it to “YES” to perform the convert.

<n:Convert verify="YES">

devclstr1-/u03/oracle/wangm>rconfig E91TPR.xml

Converting Database "E91TPR" to Cluster Database. Target Oracle Home: /u01/app/oracle/product/11.2. Database Role: PRIMARY.

Setting Data Files and Control Files

Adding Database Instances

Adding Redo Logs

Enabling threads for all Database Instances

Setting TEMP tablespace

Adding UNDO tablespaces

Adding Trace files

Setting Fast Recovery Area

Updating Oratab

Creating Password file(s)

Configuring Listeners

Configuring related CRS resources

Starting Cluster Database

<?xml version="1.0" ?>

<RConfig version="1.1" >

<ConvertToRAC>

<Convert>

<Response>

<Result code="0" >

Operation Succeeded

</Result>

</Response>

<ReturnValue type="object">

<Oracle_Home>

/u01/app/oracle/product/11.2

</Oracle_Home>

<Database type="ADMIN_MANAGED" >

<InstanceList>

<Instance SID="E91TPR1" Node="devclstr1" >

</Instance>

<Instance SID="E91TPR2" Node="devclstr2" >

</Instance>

</InstanceList>

</Database> </ReturnValue>

</Convert>

</ConvertToRAC></RConfig>

devclstr1-/u03/oracle/wangm>

1.4 Post Conversion steps.

1) Update crontab if applicable.

2) Remove and add the database target in OEM.

3) Modify local listener setting.

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=devclstr1-vip)(PORT=1521))' sid='E91TPR1';

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=devclstr2-vip)(PORT=1521))' sid='E91TPR2';

4) If using RConfig, the tool automatically name the new undo tablespace to UNDOTBS2. To change it, do the following.

sys@E91TPR2> alter tablespace UNDOTBS2 rename to PSUNDO02;

Tablespace altered.

sys@E91TPR2> alter system set undo_tablespace=PSUNDO02 sid='E91TPR2';

System altered.

sys@E91TPR2> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 10800

undo_tablespace string PSUNDO02

sys@E91TPR2>

1.5 Notes

1) Oracle supplies two sample xml files, one for Admin managed and one for policy managed. Since we are using the Admin managed xml here. The policy managed one is similiar.

2) We can create a new undo table space with desired name, swith the undo_tablespace parameter and then drop the undo tablespace created by RConfig. I've noticed if the psundo02 table space exists before running RConfig, then it's smart enough to use it without creating the default undo tablespace.