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
- Create a temporary PFILE from the SPFILE and add cluster parameters.
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’
- Create additional UNDO tablespace.
- Create redo threads and enable the second thread.
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>
- 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-/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
- Create the SPFILE from the updated PFILE.
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>
- On the local node, run catclust.sql. This command creates the dictionary views needed for Oracle RAC databases. For example:
- Add the configuration for the Oracle RAC database and its instance-to-node mapping using SRVCTL.
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>
- Start the database using SRVCTL.
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
- As the user, copy the file $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml to a working directory and edit the file.
<?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>
- Run :
<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.
