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

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

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

Check that it is a non CDB

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

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

3. Shut down non-CDB (ncdb)

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

         

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)

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.

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

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

Open the newly created PDB (NCDB).

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

Access the newly created PDB ncdb and run the script 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.

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

Check that the status of NCDB changes to NORMAL now.

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

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