Tuesday, 29 November 2016

Oracle Multitenant : Super-fast Provisioning of Standby Databases

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