Oracle Multitenant : Super-fast Provisioning of Standby Databases

In this article, I will demonstrate how to quickly create a standby database for a non-multitenant container database (NCDB) by plugging it as a pluggable database (PDB) into primary CDB (BOSTON). This method tremendously simplifies and speeds up the procedure to create a standby database without the need to set various initialization parameters or take backup or configure tnsnames.ora entries… Continue Reading →

In this article, I will demonstrate how to quickly create a standby database for a non-multitenant container database (NCDB) by plugging it as a pluggable database (PDB) into primary CDB (BOSTON). This method tremendously simplifies and speeds up the procedure to create a standby database without the need to set various initialization parameters or take backup or configure tnsnames.ora entries etc.

Oracle Database 12c introduces Multitenant Architecture; in this new architecture, a multitenant container database (CDB) can hold many pluggable databases (PDBs), which are standardized and self-contained databases. An administrator looks after the multitenant container database, while application code connects to one pluggable database as in conventional Pre-12c Oracle Database. This architecture makes it easy to rapidly provision and clone PDBs for various purposes. You can clone a pluggable database within the same CDB or to another CDB. The PDBs can also be rapidly moved across the containers by unplugging from one container and plugging into another container. This gives the flexibility of creating new patched or upgraded containers and selectively unplugging PDB from older container and plugging into a new patched or upgraded container. Moreover, DBAs can leverage the new multitenant functionality for existing conventional databases (non-CDBs) by plugging them as PDBs into CDBs without any changes to any associated applications. In addition, Oracle Multitenant is fully compatible with other Oracle Database options, including Oracle Real Application Clusters and Active Data Guard.

Data Guard manages one or more synchronized copies of a primary database by sending and applying redo logs from primary to standby(s). Since redo logs are managed for the CDB as a whole, in a data guard configuration for a CDB, redo logs applied at the container level will protect each PDB in it from outages. Moreover, the role (primary / standby) is associated with the entire CDB and not with individual pluggable databases (PDBs).Hence, if a non-CDB is plugged-in as a PDB into a CDB in primary role, redo application on its standby CDB(s) will cause a corresponding standby PDB to be created and synchronized. This in turn simply means  that in order to quickly create a standby database for an existing database (non-CDB), all you need to do is  plug-in the non-CDB as a PDB into a CDB which already has a standby configured for it.

Here’s how it’s done:

Current scenario:

Non-CDB to be plugged in   : ncdb
Target CDB (primary)      : boston
Host for primary           : host01
Target PDB  (standby)      : london
Host for standby           : host03

Overview of the steps:

  1. View current dataguard configuration for CDB
  2. Connect to non-CDB (ncdb) and use DBMS_PDB.DESCRIBE to create an XML file describing the database
  3. Shut down non-CDB (ncdb)
  4. Check that non-CDB(ncdb) can be plugged into Primary CDB(boston)
  5. Create required directories to hold datafiles for the new PDB (ncdb) on primary and standby hosts (host01 and host03)
  6. Copy datafiles for the new PDB (ncdb) to standby host (host03)
  7. Plug-in Non-CDB (ncdb) as PDB into primary CDB(boston)
  8. Open newly created PDB (ncdb) on standby CDB (London) – opens in read only mode

Implementation:

1. View current dataguard configuration for multitenant container database

DGMGRL> show configuration;
Configuration - drsolution
   Protection Mode: MaxPerformance
   Databases:
   boston   - Primary database
      bostonfs - Far Sync
         london   - Physical standby database
      london2  - Logical standby database (disabled)
      londonfs - Far Sync (inactive)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

2. Connect to non-multitenant container database and use DBMS_PDB.DESCRIBE to create an XML file describing the database.

NCDB>sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      ncdb

Check that it is a non CDB

NCDB>select name, cdb from v$database;

NAME      CDB
--------- ---
NCDB      NO

NCDB>select instance_name from v$instance;

INSTANCE_NAME
----------------
ncdb

Find out names of datafiles for NCDB (needed later for FILE_NAME_CONVERT)

NCDB>select name from v$datafile;

NAME
----------------------------------------------------------------
/u01/app/oracle/oradata/ncdb/system01.dbf
/u01/app/oracle/oradata/ncdb/sysaux01.dbf
/u01/app/oracle/oradata/ncdb/undotbs01.dbf
/u01/app/oracle/oradata/ncdb/users01.dbf

Get the database in a consistent state and then run DBMS_PDB.DESCRIBE to create an XML file to describe the database.

NCDB>shutdown immediate;
startup mount;
alter database open read only;

NCDB>exec dbms_pdb.describe (PDB_DESCR_FILE=>'/u01/app/oracle/oradata/ncdb/ncdb.xml');

PL/SQL procedure successfully completed.

NCDB>ho ls -l /u01/app/oracle/oradata/ncdb/ncdb.xml
-rw-r--r-- 1 oracle oinstall 3918 Feb 16 15:15 
/u01/app/oracle/oradata/ncdb/ncdb.xml

3. Shut down non-CDB (ncdb)

NCDB>shutdown immediate;
Exit

4. Check that non-cdb (ncdb) can be plugged into Primary CDB (boston)

BOSTON>conn sys/oracle@boston as sysdba
BOSTON>col name for a30
BOSTON>select name, CDB from v$database;
NAME                           CDB
------------------------------ ---
BOSTON                         YES

BOSTON>SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/u01/app/oracle/oradata/ncdb/ncdb.xml',
pdb_name => 'NCDB')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

NO

BOSTON>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='NCDB';

NAME     CAUSE          TYPE      MESSAGE                                                          STATUS
-------- ----------     --------- -----------------------------------                              ---------
NCDB     Non-CDB to PDB WARNING   PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING

         

Since we will run the script noncdb_to_pdb.sql later, we can continue.

5. Create required directories to hold datafiles for new PDB (ncdb) on primary and standby hosts (host01 and host03)

[oracle@host01 ~]$ mkdir -p /u01/app/oracle/oradata/boston/ncdb
[oracle@host03 ~]$ mkdir -p /u01/app/oracle/oradata/london/ncdb

6.Copy datafiles for new PDB (ncdb) to standby host (host03)

Since we will create a PDB from an XML file, copy the data files specified in the XML file to the standby database before plugging in the PDB at the primary database. Ensure that the files are copied to an appropriate location where they can be found by managed standby recovery.

[oracle@host01 ~]$ scp /u01/app/oracle/oradata/ncdb/system01.dbf host03:/u01/app/oracle/oradata/london/ncdb/system01.dbf

[oracle@host01 ~]$ scp /u01/app/oracle/oradata/ncdb/sysaux01.dbf host03:/u01/app/oracle/oradata/london/ncdb/sysaux01.dbf

[oracle@host01 ~]$ scp /u01/app/oracle/oradata/ncdb/undotbs01.dbf host03:/u01/app/oracle/oradata/london/ncdb/undotbs01.dbf

[oracle@host01 ~]$  scp /u01/app/oracle/oradata/ncdb/users01.dbf host03:/u01/app/oracle/oradata/london/ncdb/users01.dbf

7. Plug-in Non-CDB (ncdb) as PDB(ncdb) into primary CDB(boston)

BOSTON>CREATE PLUGGABLE DATABASE ncdb USING 
'/u01/app/oracle/oradata/ncdb/ncdb.xml'
COPY
file_name_convert=('/u01/app/oracle/oradata/ncdb','/u01/app/oracle/oradata/boston/ncdb');

Check that newly plugged-in PDB (NCDB) is in mounted state on primary

BOSTON>sho pdbs

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

Open the newly created PDB (NCDB).

BOSTON>alter pluggable database ncdb open;

Warning: PDB altered with errors.

This warning message is appearing as we have not run the script noncdb_to_pdb.sql yet. You can ignore this message as of now.

Check that status of newly created PDB NCDB is NEW

BOSTON>col pdb_name for a15
               select pdb_name, status from dba_pdbs where pdb_name = 'NCDB';
PDB_NAME        STATUS
--------------- -------------
NCDB            NEW

Access the newly created PDB ncdb and run the script noncdb_to_pdb.sql.

BOSTON>alter session set container=ncdb;

sho con_name

CON_NAME
------------------------------
NCDB

BOSTON>@?/rdbms/admin/noncdb_to_pdb.sql

The script will take some time and has lengthy output. But at the end it will leave your database in stage where it was when script was run. In our case PDB NCDB was open.

BOSTON>sho pdbs

CON_ID     CON_NAME                    OPEN MODE    RESTRICTED
---------- --------------------------- ----------   ----------
         2 PDB$SEED                    READ ONLY    NO
         3 DEV1                        READ WRITE   NO
         4 NCDB                     READ WRITE  NO

Verify that warnings for running the script noncdb_to_pdb.sql has been resolved.

BOSTON>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='NCDB';

NAME       CAUSE           TYPE      MESSAGE                                                            STATUS
-------    ----------      --------  -----------------------------------                                ---------
NCDB       Non-CDB to PDB  ERROR     PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.    RESOLVED

Check that the status of NCDB changes to NORMAL now.

BOSTON>col pdb_name for a30
BOSTON>select pdb_name, status from dba_pdbs;

PDB_NAME                       STATUS
------------------------------ -------------
DEV1                           NORMAL
PDB$SEED                       NORMAL
NCDB                           NORMAL

8. Open newly created PDB (ncdb) on standby CDB (London) – opens in read only mode

LONDON>sho pdbs

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

LONDON>alter pluggable database ncdb open;

   sho pdbs
   
   CON_ID  CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DEV1                           READ ONLY  NO
         4 NCDB                           READ ONLY  NO

Conclusion:

Multitenant architecture introduced in Oracle database 12c can be employed to quickly provision standby database for conventional database (Non-CDB) by plugging it as PDB into a CDB which has a standby CDB configured for it.

Reference:

https://docs.oracle.com/database/121/SBYDB/create_ps.htm#SBYDB5260