Written by Syed Jaffar Hussain

SUMMARY

Oracle 12c Multitenancy (MT) provides a solid platform to consolidate multiple databases onto a single container database (CDB) for better database management, also to maximize returns on investment. Each individual non-CDB database typically requires a separate Data Guard configuration. In contrast, on Multitenant environment, you only have to configure Data Guard at the root container level, and all actions performed at the PDB level in the primary database are captured and applied at the standby database. This article will explain the Data Guard impact on Multitenant environment, how to defer and enable the data guard protection of a particular PDB using the new 12.1.0.2 STANDBYS clause.

ADG Setup and impact on Multitenant Environment

CDB and ADG Configuration

Data Guard fully supports Multitenant environment. The core procedure to configure Data Guard environment remains identical on non-CDB and CDB environments. There is no significant difference in the steps, except the DG is configured and role transaction is executed at the root container level, not on the individual PDB levels on Multitenant environment.

Refer to Gavin Soorma’s blog post to learn how to configure Data Guard on Multitenant environment:

http://gavinsoorma.com/2013/11/creating-an-oracle-12c-data-guard-active-standby-database/

Whenever a new PDB is created, cloned or migrated from a non-CDB, the same is replicated at standby database too. For example, when we create a new PDB at the primary site, this PDB is automatically added at the standby site, we can confirm the same referring the standby database alert.log file, as shown below:

Recovery created pluggable database SMSPDB
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file +DATAC1/SMSSDB/4014E3D88DDD859FE05317A2000A089F/DATAFILE/system.4384.926607859 from +DATAC1/SMSSDB/38F2AF8A2AF74727E05316A2000A9D1C/DATAFILE/system.3910.925074393
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file +DATAC1/SMSSDB/4014E3D88DDD859FE05317A2000A089F/DATAFILE/sysaux.4385.926607861 from +DATAC1/SMSSDB/38F2AF8A2AF74727E05316A2000A9D1C/DATAFILE/sysaux.3937.925074329

When queried the status, the newly added PDB will shows as MOUNTED on the physical standby database:

SQL> SHOW PDBS

CON_ID     CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SMSPDB                         MOUNTED

In an Active Data Guard configuration, you can open the PDB in READ ONLY mode, using the following command:

SQL> alter pluggable database SMSPDB open read only;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SMSPDB                         READ ONLY  NO
 

When cloning a PDB from the same primary database of no active data guard configuration, the physical standby database doesn’t copy the files from the source location to the new standby location, besides, it also expects to have all the data files of the new PDB pre-exists and accessible at the standby location to continue with the redo log apply and normal operations. If this criteria is not met, the media recovery process will be stopped at the physical standby database and will not proceed further until the problem is resolved. To overcome from this scenario, you have to copy the source PDB data files from primary location to the standby location and restart the recovery process manually. Scott has neatly explained about this scenario with the examples at his blog:

https://oracledb101.wordpress.com/2015/03/02/creating-pdbs-in-a-dataguard-enviroment/

Gavin Soorma in his blog explained the procedure to unplug a PDB from no data guard container and plug in into the CBD with no active data guard configuration setup:

http://gavinsoorma.com/2013/11/plugging-an-oracle-12c-pluggable-database-into-a-data-guard-container-database/

If the above scenario is performed in an active database configuration, physical standby database copies the files automatically when you clone a PDB of the same CDB. Scott, demonstrated the same in his blog in part 2:

https://oracledb101.wordpress.com/2015/03/10/creating-pdbs-in-a-dataguard-enviroment-part-ii/

From the preceding explanations and examples, we have learned how to deal with no active data guard environment while cloning or plugging a PDB. What if you are run into the following requirements?

  • A short-lived test PDB requirement which doesn’t require data guard protection for the testing period.
  • There is no sufficient space available at standby database to host this database.
  • Perhaps the new PDB doesn’t really require high level of protection (data guard).

With 12.1.0.2 release, the new CREATE PLUGGABLE DATABASE STANDBYS=NONE clause can be used to exclude the new PDB from the existing standby configuration, while continue protecting all the existing pluggable databases. When the STANDBYS clause is mentioned, the general structure of the new PDB is created on the standby database, however, all the data files belonging to the PDB are marked as OFFLINE/RECOVERY at standby database. Query the RECOVERY_STATUS column in V$PDBS view.

SQL> select CON_ID,NAME,RECOVERY_STATUS from v$pdbs;

    CON_ID NAME                           RECOVERY
---------- ------------------------------ --------
         2 PDB$SEED                       ENABLED
         3 SMSMRKT                        ENABLED

 

If the STANDBY clause used to create a new PDB, the RECOVERY_STATUS will appear as DISABLED and the data files status will be OFFLINE/RECOVERY status. When the data files are in OFFLINE/RECOVERY mode, the PDB can’t be opened in READ ONLY mode, though, other pluggable databases in the standby database can be operated normally.

Here is the example how to create a new PLUGGABLE DATABASE with STANDBYS clause:

SQL> create pluggable database smspdb2 from smspdb STANDBYS=NONE;

When you query RECOVERY_STATUS from v$pdbs, you see the new PDB in DISABLE state, as demonstrated below:

SQL> select CON_ID,NAME,RECOVERY_STATUS from v$pdbs;

    CON_ID NAME                           RECOVERY
---------- ------------------------------ --------
         2 PDB$SEED                       ENABLED
         3 SMSPDB                         ENABLED
         4 SMSPDB2                        DISABLED

You can also query the STATUS column in V$DATAFILE, which should appearing as SYSOFF. And the data files of the new PDB created with the STANDBY=NONE clause should have OFFLINE status in V$RECOVERY_FILE view of the physical standby database. An attempt to open the PDB in the standby database will result in ORA-01147 error.

When you want to enable the recovery (data guard configuration) of the PDB created with the STANDBY clause, you will have to copy the data file from the primary location to the standby location and enable the recovery at the pluggable database level.

Though enable the recovery required standby database restart, the good thing is that the instantiation can be performed online with RMAN (copying the files from the primary site to standby site), so that the PDB at the primary site is open and accessible.

Upon completion of the file copying, shutdown the standby database and startup in mount state, perform the following action on the standby database:

SQL> alter session set container=smspdb2;

SQL> ALTER PLUGGABLE DATABASE ENABLE RECOVERY;

Once you validate that the standby database recognized the data files and move forwarded with the media recovery, you may open the database and put the PDB in READ ONLY mode (active data guard state).

At any point in time, if you want to disable the recovery of a PDB, execute the below commands:

SQL> alter session set container=smspdb2;

SQL> ALTER PLUGGABLE DATABASE DISABLE RECOVERY;

REFERENCES

Below are some of refer references for documentation, blogs and articles:

MOS ID:

  • Making Use Deferred PDB Recovery and the STANDBYS=NONE Feature with Oracle Multitenant (Doc ID 1916648.1)
  • Data Guard Impact on Oracle Multitenant Environments (Doc ID 2049127.1)

 

Blogs:

http://gavinsoorma.com/2013/11/creating-an-oracle-12c-data-guard-active-standby-database/

http://gavinsoorma.com/2013/11/plugging-an-oracle-12c-pluggable-database-into-a-data-guard-container-database/

https://oracledb101.wordpress.com/2015/03/02/creating-pdbs-in-a-dataguard-enviroment/

https://oracledb101.wordpress.com/2015/03/10/creating-pdbs-in-a-dataguard-enviroment-part-ii/

 

CONCLUSION

This article explained the procedure to enable and disable data guard protection at the individual PDB level using the new STANDBYS=NONE clause introduced with 12.1.0.2 release. Also, you have learned how to manage data guard (active and regular) configuration on Oracle 12c Multitenant container database.