As a consultant I constantly speak with my customers, and among a big number of them I noticed that the speed and flexibility of all database provisioning activities generate huge concern.

Hence I decide to describe on this post few Oracle Multitenant options to resolve those problems.

If production is the most critical environment to maintain, it is definetly not the one generating the greatest efforts in term of provisioning. The applications are more and more complex, and require continuous delivery;  to satify those needs the infrastructure has few provisioning challengers to overcome.

Now with the Oracle version 12.2 and the Mutitenet option, the DBaaS model becomes simpler than ever.

 

Clone PDB

The Clone PDB operation has been enhanced from Cold to Hot Clone. This improvement requires the usage of  PDB Local Undo. The Hot Clone is now the default method and can be devided in three major steps:

1. PDB source datafile copy, because the PDB remains open in read/write at this stage the cloned datafiles are physically inconsistent (fuzzy data files).
2. The Redo Log entries generated on the source PDB during the copy are applied to the targed PDB. This step makes the source and target PDBs two exact physical copies.
3. Because the Redo Log entries coming from the source PDB contain committed and uncommitted transactions, to make the target PDB transactionally consistent, the undo entries of all uncommitted transations must be applied.

 

The command below shows how to clone a PDB open in read/write:

Create Pluggable Database ERP_Hot_Clone from ERP;

 

Refreshable PDB

Refreshable PDB leverages the Hot Clone PDB capability, creating an initial copy of the source PDB refreshed over the time at scheduled interval or on-demand.

To better understand the possible use cases, the graphical example below covers the development’s request to have every morning a copy of production data.

 

 

How to create a Refreshable PDB

Syntax to create an automatic refreshable PDB:

Create Pluggable Database CRM_Test from CRM_Prod@db_link refresh mode every 720; -- (12H)

 

Syntax to create a manual PDB refresh:

Create Pluggable Database CRM_Test from CRM_Prod@db_link refresh mode manual;

 

After the clone the refreshable PDB should then be opened in read-only:

Alter Pluggable Database CRM_Test read only;

 

How to invoke a manual PDB refresh:

Alter Pluggable Database CRM_Test refresh;

 

Creation of the snapshot databases:

Create Pluggable Database CRM_TEST_Snap01 FROM CRM_Test
FILE_NAME_CONVERT = ('/u03/oradata/CDB122/CRM_Test/','/u03/oradata/CDB122/CRM_Test_Snap01/')
SNAPSHOT COPY;

 

 

---