PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Oracle Multitenant: Convert single instance PDB to RAC by plugging it into a RAC CDB

Prior to oracle database 12c, the procedures for converting from single-instance databases to Real Application Clusters involved following steps: Configure the control file attributes and move control file to shared storage Make the initialization parameter file entries for cluster-enabled environments and move SPfile to shared storage Create an undo tablespace for each additional instance Create redo threads for each additional

Prior to oracle database 12c, the procedures for converting from single-instance databases to Real Application Clusters involved following steps:

  1. Configure the control file attributes and move control file to shared storage
  2. Make the initialization parameter file entries for cluster-enabled environments and move SPfile to shared storage
  3. Create an undo tablespace for each additional instance
  4. Create redo threads for each additional instance.
  5. Create the Oracle password file on the additional nodes on which the cluster database will have an instance.
  6. Configure the net services for the database and instances
  7. Create the dictionary views needed for Oracle RAC databases
  8. Add the configuration for the Oracle RAC database and its instance-to-node mapping using SRVCTL
  9. Move online redo logs to shared storage
  10. Move data files to shared storage

With the introduction of multitenant architecture in Oracle database 12c, applications can be represented by pluggable databases within a container database (CDB). The number of active/configured instances of the PDB are decided at the CDB level. A PDB which is part of a single instance CDB will have only one instance whereas a PDB within a RAC CDB will have multiple instances. Hence, a single instance PDB can be quickly converted to RAC by unplugging it from single instance CDB and plugging it into a RAC multitenant container database (CDB). The control file, Undo tablespace, Redo logs, password file and SPfile of the destination RAC CDB would already be on shared storage. Hence, while plugging in the PDB, only following two activities need to be done:

– Create metadata describing the plugged-in PDB in the destination CDB
– Move the datafiles of the PDB to the shared storage (if they are not there already)

This procedure simplifies and speeds the process of converting a single instance database to RAC considerably.

To be able to employ this method, the source and target CDB platforms must meet the following requirements:

  • They must have the same endianness
  • They must have the same set of database options installed
  • The CDB that contained the unplugged PDB and the target CDB must have compatible character sets and national character sets

Now I will demonstrate the above procedure. Currently, I have one single instance CDB called cdbs having a PDB called pdbs. Also, in my 3 node oracle database 12c cluster , there is a 3-instance policy managed RAC CDB called cdb1 assigned to server pool ora.cdb1pool. Let’s say that I want to have my application which is represented by single instance PDB (pdbs) to run on 3 servers to facilitate high availability and load balancing. In order to meet my objective, I will unplug PDB pdbs from single instance CDB (cdbs) and plug it in 3-instance RAC CDB (cdb1) with name pdbrac so that the plugged-in PDB pdbrac will also have 3 instances. Also, since datafiles of PDB pdbs are currently on non-shared filesystem, I will move them to shared storage on ASM while plugging in.

Here is the demonstration:

Current scenario:

Nodes in cluster : 3
Names of nodes : host01, host02, host03
Source database:
Source single instance CDB : cdbs
Single instance PDB in cdbs : pdbs

Destination database:
Destination policy managed RAC CDB : cdb1
Assigned to serverpool : ora.cdb1pool
ACTIVE_SERVERS=host02 host03

Overview:

  1. Verify that source single instance CDB (cdbs) is Currently running on host01
  2. Verify that source single instance CDB (cdbs) has one PDB (pdbs) having non-ASM datafiles
  3. Verify that destination policy managed RAC CDB (cdb1)
    1. Has 3 instances configured on host01, host02 and host03
    2. Has been assigned to serverpool ora.cdb1pool and
    3. Is currently running on host02 and host03
  4. Verify that destination RAC CDB (cdb1) has currently only one PDB (pdb1)
  5. Unplug pluggable database pdbs from single instance CDB (cdbs)
  6. Plug pluggable database pdbs into RAC CDB cdb1
  7. Verify that multiple instances of Plugged-in PDB can be accessed

Implementation:

Verify that source single instance CDB (cdbs) is currently running on host01

Verify that services of single instance PDB pdbs and CDB cdbs are registered with the listener on host01

Verify that source single instance CDB (cdbs) has one PDB (pdbs) having non-ASM datafiles

Verify that destination policy managed RAC CDB (cdb1)

  • Has 3 instances configured on host01, host02, and host03,
  • Has been assigned to serverpool ora.cdb1pool, and
  • Is currently running on host02 and host03

Verify that services of RAC PDB pdb1 and CDB cdb1 are registered with the listener on host02 and instance cdb1_1 is running on host02

Verify that services of PDB pdb1 and CDB cdb1 are registered with the listener on host03 and instance cdb1_2 is running on host03

Verify that destination RAC CDB (cdb1) has currently only one PDB (PDB1)

Unplug pluggable database pdbs from single instance CDB (cdbs)

To unplug a PDB, you first close it and then generate an XML manifest file. The XML file contains information about the names and the full paths of the tablespaces, as well as data files of the unplugged PDB. The information will be used by the plugging operation.

Use SQL*Plus to close the PDB before it can be unplugged.

SQL> conn sys/oracle@cdbs as sysdba
CDBS> alter pluggable database pdbs close immediate;

Unplug the closed PDB and then specify the path and name of the XML file.

CDBS>alter pluggable database pdbs unplug into '/u01/app/oracle/oradata/pdbs.xml';

Drop the closed PDB and keep the data files.

CDBS>drop pluggable database pdbs keep datafiles;

Verify that unplugged PDB is no longer part of cdbs

CDBS>select pdb_name, status from cdb_pdbs where pdb_name in ('PDBS');
no rows selected

Plug pluggable database PDBS into RAC CDB CDB1

This command should be issued from the node where target database instance is running.

Since cdb1 instance is running on host02, copy the xml file from host01 to host02

Find out the name of the instance of destination RAC CDB cdb1 running on host02

Make sure that the to-be-plugged-in PDB (pdbs) is compatible with the new host CDB (cdb1).

Check the Compatibility of the Unplugged PDB (pdbs) with the target CDB (cdb1)

Since these are warnings, we can continue.

Copy datafiles of PDB pdbs from host01 to host02 in same location as host01

[oracle@host02 ~]$ mkdir -p /u01/app/oracle/oradata/cdbs/pdbs

[oracle@host02 ~]$ scp host01:/u01/app/oracle/oradata/cdbs/pdbs/* /u01/app/oracle/oradata/cdbs/pdbs/

Plug the PDB (pdbs) into destination CDB (cdb1) with name pdbrac and copy the datafiles to DATA diskgroup on shared storage

CDB1L> create pluggable database pdbrac using '/u01/app/oracle/oradata/pdbs.xml'
copy
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdbs/pdbs', '+DATA');

Verify that PDB (pdbs) has been successfully plugged into into target CDB (cdb1) with name pdbrac

Verify that datafiles for plugged in PDB (pdbrac) have been copied to DATA diskgroup on shared storage

Open all instances of plugged-in PDB pdbrac

SQL> alter pluggable database pdbrac open instances=all;

Verify that multiple instances of Plugged-in PDB can be accessed

Connect to instance of PDB pdbrac running on host03 and verify that it belongs to instance cdb1_2 of CDB cdb1

Connect to instance of PDB pdbrac running on host02 and verify that it belongs to instance cdb1_1 of CDB cdb1

Conclusion

Oracle multitenant architecture reduces the number of tasks to be performed when converting a single instance database to RAC as SPfile, controlfile, undo tablespace(s), Redo logs, password file and instance to node mapping belongs to CDB rather than to each individual PDB. Moreover, conventional pre-12c methods of conversion required RAC and the standalone environments to be using the same oracle release. But in case of oracle database 12c multitenant architecture, this restriction is not there. The destination RAC CDB can be running a different oracle software version and as a result upgrading of the application can be carried it along with its conversion to RAC.