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… Continue Reading →

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

[grid@host03 ~]$ srvctl status database -d cdbs
Instance cdbs is running on node host01
CDBS> sho parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdbs

CDBS> sho parameter cluster_database

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1

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

[oracle@host01 ~]$ lsnrctl stat

...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.9.201.246)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 2 handler(s) for this service...
Service "cdbs" has 1 instance(s).
  Instance "cdbs", status READY, has 1 handler(s) for this service...
Service "cdbsXDB" has 1 instance(s).
  Instance "cdbs", status READY, has 1 handler(s) for this service...
Service "pdbs" has 1 instance(s).
  Instance "cdbs", status READY, has 1 handler(s) for this service...
The command completed successfully

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

CDBS> sho pdbs
   CON_ID  CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        2  PDB$SEED                       READ ONLY  NO
        3  PDBS                           READ WRITE YES

CDBS> alter session set container=pdbs;

Session altered.
CDBS> select name from v$datafile;

NAME
  --------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdbs/undotbs01.dbf
/u01/app/oracle/oradata/cdbs/pdbs/system01.dbf
/u01/app/oracle/oradata/cdbs/pdbs/sysaux01.dbf
/u01/app/oracle/oradata/cdbs/pdbs/pdbs_users01.dbf

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
[grid@host03 ~]$ srvctl status database -d cdb1
Instance cdb1_1 is running on node host02
Instance cdb1_2 is running on node host03
Database cdb1 is not running on node host01

[grid@host03 ~]$ crsctl status serverpool
NAME=Free
ACTIVE_SERVERS=

NAME=Generic
ACTIVE_SERVERS=host01

NAME=ora.cdb1pool
ACTIVE_SERVERS=host02 host03

NAME=ora.cdbs
ACTIVE_SERVERS=host01

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

[oracle@host02 ~]$ lsnrctl stat
...
Services Summary...
Service "+ASM" has 1 instance(s).
 Instance "+ASM3", status READY, has 2 handler(s) for this service...
Service "cdb1" has 1 instance(s).
 Instance "cdb1_1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
 Instance "cdb1_1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
 Instance "cdb1_1", status READY, has 1 handler(s) for this service...
The command completed successfully

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

[oracle@host03 ~]$ lsnrctl stat
Services Summary...
Service "+ASM" has 1 instance(s).
 Instance "+ASM2", status READY, has 2 handler(s) for this service...
Service "cdb1" has 1 instance(s).
 Instance "cdb1_2", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
 Instance "cdb1_2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "cdb1_2", status READY, has 1 handler(s) for this service...
The command completed successfully

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

CDB1> sho parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb1
CDB1> sho parameter cluster
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     3
cluster_interconnects                string
CDB1> sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                            MOUNTED

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

  [oracle@host01 ~]$ scp /u01/app/oracle/oradata/pdbs.xml host02:/u01/app/oracle/oradata/pdbs.xml
pdbs.xml 
                                    100% 3372     3.3KB/s   00:00

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

[oracle@host02 ~]$ ps -ef |grep pmon
oracle    8525  6894  0 16:54 pts/2    00:00:00 grep pmon
grid     17855     1  0 09:08 ?        00:00:06 asm_pmon_+ASM3
oracle   20871     1  0 09:09 ?        00:00:07 ora_pmon_cdb1_1

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

[oracle@host02 ~]$ export ORACLE_SID=cdb1_1

[oracle@host02 ~]$ sqlplus / as sysdba
          CDB1>set serveroutput on

    DECLARE
       compatible BOOLEAN := FALSE;
    BEGIN  
       compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
            pdb_descr_file => '/u01/app/oracle/oradata/pdbs.xml', pdb_name=>'PDBS');
       if compatible then
          DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? YES');
       else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? NO');
       end if;
    END;
    /

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

CDB1>  col cause for a10
              col name for a10
              col message for a35 word_wrapped
              select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='PDBS';

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------

PDBS       OPTION     WARNING   Database option RAC mismatch: PDB   PENDING
                                installed version NULL. CDB
                                installed version 12.1.0.1.0.

PDBS       Parameter  WARNING   CDB parameter sga_target mismatch:  PENDING
                                Previous 394264576 Current
                                662700032

PDBS       Parameter  WARNING   CDB parameter pga_aggregate_target  PENDING
                                mismatch: Previous 131072000
                                Current 220200960

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

 CDB1> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                               MOUNTED
         4 PDBRAC                         MOUNTED

    CDB1>alter pluggable database PDBrac open;

     CDB1>  sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDBRAC                         READ WRITE YES

CDB1> alter session set container=pdbrac;

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

PDBRAC1>  select name from v$datafile;

 NAME
--------------------------------------------------
+DATA/CDB1/DATAFILE/undotbs1.261.853323067
+DATA/system01.dbf
+DATA/sysaux01.dbf
+DATA/pdbs_users01.dbf

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

SQL> conn system/oracle@host03:1521/pdbrac
Connected.
SQL> sho con_name
CON_NAME
------------------------------
PDBRAC

SQL> sho parameter instance_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string cdb1_2

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

SQL> conn system/oracle@host02:1521/pdbrac
Connected.
SQL> sho con_name

CON_NAME
------------------------------
PDBRAC
SQL> sho parameter instance_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string cdb1_1

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.