Convert Single Instance Oracle Database to Oracle RAC

Mehmet Salih Deveci November 1, 2020 1 Comment

Hi,

I will explain How to Convert Single Instance Oracle Database to Oracle RAC in this post.

 

Convert Single Instance to Oracle RAC

Sometimes you may need to convert Single Instance to RAC.

 

 

To convert Single Instance Oracle database to Oracle RAC, you have to have Oracle RAC, If you want to install Oracle RAC, you can read the following post to learn how to install Oracle 19c RAC.

Oracle 19c Real Application Cluster (RAC) Step by Step Installation on Full Rack Exadata X7

 

Now If you have installed Oracle RAC software, then you can start to Convert Single Instance to Oracle RAC.

 

Convert Single Instance to Oracle RAC Steps

You should perform the following steps to Convert Single instance Oracle database to Oracle RAC.

  1.  Create the New Undo Tablespace for the Second Node
  2.  Add Logfile for the Second Nodes
  3.  Enable the Second Thread.
  4.  Add new RAC parameters into Parameter File ( Pfile )
  5.  Modify the Bash Profiles and parameter files on two node.
  6.  Add Database and Instance into Clusterware, then Startup Instances

 

Create Undo Tablespace

Step-1: Create the New Undo Tablespace for the Second Node

You should create the New Undo Tablespace for the Second Node, because each Oracle Instance needs its Undo tablespace. You can create the  Undo tablespace as follows.

create undo tablespace UNDOTBS2 datafile '+DATATEST' size 4096M;


SQL> create undo tablespace UNDOTBS2 datafile '+DATATEST' size 4096M;

Tablespace created.

 

Add Online Logfile

 

Step-2: Add Logfile for the Second Nodes

You should add the New Logfile group for the Second Node ( thread 2 ), because each Oracle Instance needs its Online Log File. You can create the  Undo tablespace as follows.

alter database add logfile thread 2 group 13 ('+DATATEST') size 50m reuse;


[MSDBA]/home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 1 19:02:24 2020
Version 19.8.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> alter database add logfile thread 2 group 13 ('+DATATEST') size 50m reuse;

Database altered.

SQL> alter database add logfile thread 2 group 14 ('+DATATEST') size 50m reuse;

Database altered.

SQL> alter database add logfile thread 2 group 15 ('+DATATEST') size 50m reuse;

Database altered.

SQL>

 

 

 

 

 

 

Step-3:  Enable the Second Thread

You should enable the Second thread and Second Instance for the Same Oracle database as follows.

 

SQL> alter database enable public thread 2;

Database altered.

SQL>

 

 

Oracle RAC Parameters – cluster_database, instance_number,thread,remote_listener

 

Step-4: Add new RAC parameters into Parameter File ( Pfile )

Normally If you are using the Single instance, then cluster_database parameter is False by default.

 

To convert Single Instance Oracle database to Oracle RAC and multiple Instance, you need to use cluster_database=true.

 

And other Cluster parameters are as follows. You should change according to your Instance_NAME. In our example MSDBA is my db_name, so First Instance Name will be MSDBA1 and second Instance name will be MSDBA2.

 

Create the pfile from spfile, then add the following lines into pfile ( parameter file )

 

SQL> create pfile='/home/oracle/pfileMSDBA.ora' from spfile;

[MSDBA]/home/oracle $ vi pfileMSDBA.ora

 

*.cluster_database=true
*.remote_listener='msdb-scan:1521'
MSDBA1.instance_number=1
MSDBA2.instance_number=2
MSDBA1.thread=1
MSDBA2.thread=2
MSDBA1.undo_tablespace='UNDOTBS1'
MSDBA2.undo_tablespace='UNDOTBS2'

 

 

 

 

Step-5: Modify the Bash Profiles and parameter files on two node.

 

 

Once you added the above Cluster parameters, then save the new pfile and startup the Oracle instance with new pfile as follows.

[MSDBA]/home/oracle $ sqlplus / as sysdba
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> 
[MSDBA]/home/oracle $ vi bash_profile_msdba 
[MSDBA]/home/oracle $ 
[MSDBA]/home/oracle $ 
[MSDBA]/home/oracle $ . bash_profile_msdba 
[MSDBA2]/home/oracle $ 
[MSDBA2]/home/oracle $ 
[MSDBA2]/home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 1 19:08:34 2020
Version 19.8.0.0.0

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

Connected to an idle instance.

SQL> startup nomount pfile=pfileMSDBA.ora
ORACLE instance started.

Total System Global Area 1.0771E+10 bytes
Fixed Size 12446800 bytes
Variable Size 3321888768 bytes
Database Buffers 7348420608 bytes
Redo Buffers 88215552 bytes
SQL>

 

 

 

 

Now Create spfile from pfile for the both Instance ( Global Spfile ), then you can startup the both Instance.

SQL> create spfile='+DATATEST/MSDBA/spfileMSDBABA.ora' from pfile='/home/oracle/pfileMSDBA.ora';

File created.

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> 
SQL> 
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[MSDBA2]/home/oracle $

 

 

Change pfile name to the new pfile name from initMSDBA.ora to initMSDBA2.ora.

[MSDBA2]/home/oracle $ cd $ORACLE_HOME
[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $ mv initMSDBA.ora initMSDBA2.ora
[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $ 
[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $

 

 

Now remove the old parameters in the parameter file ( pfile ), and write only spfile adress as follows.

spfile='+DATATEST/MSDBA/spfileMSDBA.ora'

 

 

add the above lines into initMSDBA2.ora and save it.

[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $ vi initMSDBA2.ora 
[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $

 

 

Now you can startup the second node as follows.

[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $ 
[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 1 19:28:12 2020
Version 19.8.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1.0771E+10 bytes
Fixed Size 12446800 bytes
Variable Size 3321888768 bytes
Database Buffers 7348420608 bytes
Redo Buffers 88215552 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0


[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $

 

 

 

Transfer the pfile and bash profile to First node also.

[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $ scp initMSDBA2.ora msdbadm01:/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initMSDBA1.ora
initMSDBA2.ora 100% 41 81.1KB/s 00:00 
[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $ 
[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $ 
[MSDBA2]/u01/app/oracle/product/19.0.0/dbhome_1/dbs $ cd
[MSDBA2]/home/oracle $ 
[MSDBA2]/home/oracle $ scp bash_profile_msdba msdbadm01:/home/oracle/
bash_profile_msdba 100% 333 742.3KB/s 00:00 
[MSDBA2]/home/oracle $ 
[MSDBA2]/home/oracle $

 

Now go to first node and Change only Instance Name and pfile name with the new Instance Name.

[MSDBA2]/home/oracle $ ssh msdbadm01

 

 

 

 

 

 

Rename the Oracle Sid from MSDBA to MSDBA1 in the bash profile.

[MSDBA]/home/oracle $ vi bash_profile_msdba

 

Set the Bash Profile.

[MSDBA]/home/oracle $ . bash_profile_msdba 
[MSDBA1]/home/oracle $ 
[MSDBA1]/home/oracle $

 

 

 

Now you can startup the First Node also as follows.

[MSDBA1]/home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 1 19:30:51 2020
Version 19.8.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1.0771E+10 bytes
Fixed Size 12446800 bytes
Variable Size 3321888768 bytes
Database Buffers 7348420608 bytes
Redo Buffers 88215552 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[MSDBA1]/home/oracle $

 

Now two nodes ( instances )  are running for the Same database.

 

Srvctl Add Database | Srvctl add instance

Step-6: Add Database and Instance into Clusterware, then Startup Instances

You should add database and instances into Clusterware using Srvctl commands as follows.

[MSDBA1]/home/oracle $ srvctl add database -d MSDBA -o /u01/app/oracle/product/19.0.0/dbhome_1
[MSDBA1]/home/oracle $ srvctl add instance -d MSDBA -i MSDBA1 -n msdbadm01
[MSDBA1]/home/oracle $ srvctl add instance -d MSDBA -i MSDBA2 -n msdbadm02
[MSDBA1]/home/oracle $

 

 

 

 

 

 

Srvctl stop database | srvctl start database

Now you can try to shutdown the both instance with srvctl stop database command as follows, then you can startup the both instance with srvctl start database command as follows.

[MSDBA1]/home/oracle $ . bash_profile_msdba
[MSDBA1]/home/oracle $ 
[MSDBA1]/home/oracle $ 
[MSDBA1]/home/oracle $ srvctl stop database -d MSDBA
PRCC-1016 : MSDBA was already stopped
[MSDBA1]/home/oracle $ 
[MSDBA1]/home/oracle $ 
[MSDBA1]/home/oracle $ srvctl start database -d MSDBA 
[MSDBA1]/home/oracle $

 

 

Do you want to learn Oracle RAC Architecture,  then read the following articles.

What is the Oracle RAC and Architecture of Real Application Cluster

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

One comment

  1. Kasetty Sadhanand
    December 23, 2020 at 6:39 am

    Thank you for share the information , on EBS environment also same procedure we can use for convert ASM non RAC (CDB) database to RAC(CDB) asm

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Powered by IT Tutorial | Designed by SysDBASoft IT
IT Tutorial © Copyright 2024, All Rights Reserved