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.
- Create the New Undo Tablespace for the Second Node
- Add Logfile for the Second Nodes
- Enable the Second Thread.
- Add new RAC parameters into Parameter File ( Pfile )
- Modify the Bash Profiles and parameter files on two node.
- 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 )