LATEST TOPICS

Oracle 12c : Know and understand your PDB’s history

As we know, with Oracle 12c multi tenant architecture we can have multiple pluggable databases within a single container database. A pluggable database can be created in a container and if required can be unplugged from a container to be plugged in to a different container. You may want to know when a PDB was created, unplugged or plugged in a container. Oracle provides a view called [CDB/DBA]_PDB_HISTORY which can be queried to track the history (CREATION/UNPLUG/PLUG) of a pluggable database.

Although this seems straight forward to query the CDB_PDB_HISORY view to know PDB history, we need to understand what information to expect when we query CDB_PDB_HISORY. In this post, I will walk through a number of examples to understand the behaviour of the PDB history view CDB_PDB_HISORY.

To start with my demonstration, I have a CDB with a single PDB named CDB1_PDB_1 as shown below.

---//
---// Container with single PDB //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show pdbs

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

Let’s query the CDB_PDB_HISORY history view to know the history of the existing PDB CDB1_PDB_1.

---//
---// checking PDB history //---
---//
SQL> select CON_ID,PDB_NAME,PDB_DBID,OP_TIMESTAMP,OPERATION,CLONED_FROM_PDB_NAME,DB_NAME  from cdb_pdb_history order by 4;

no rows selected

What?? No historical information present for my pluggable database CDB1_PDB_1. Really ?? Well, not really. Did you notice that the pluggable database CDB1_PDB_1 is in closed (MOUNTED) state. Let’s open the pluggable database and query the history again.

---//
---// Opening pluggable database CDB1_PDB_1 //---
---//
SQL> alter pluggable database CDB1_PDB_1 open;

Pluggable database altered.

---//
---// checking PDB history //---
---//		 
SQL>  select CON_ID,PDB_NAME,PDB_DBID,OP_TIMESTAMP,OPERATION,CLONED_FROM_PDB_NAME,DB_NAME  from cdb_pdb_history order by 4;

    CON_ID PDB_NAME          PDB_DBID OP_TIMESTAMP         OPERATION        CLONED_FROM_PDB_NAME DB_NAME
---------- --------------- ---------- -------------------- ---------------- -------------------- ---------------
         3 CDB1_PDB_1      3578281016 14-MAR-2016 00:30:08 CREATE           PDB$SEED             ORPCDB1

As we can see, we are now able to view the PDB historical information by querying CDB_PDB_HISORY view. This implies that the historical information is not maintained in the CDB dictionary, rather it is maintained in the PDB’s dictionary.

With respect to historical information, we can derive from the result that the pluggable database CDB1_PDB_1 (PDB_NAME) was created (OPERATION) on 14-MAR-2016 (OP_TIMESTAMP) using the seed database PDB$SEED (CLONED_FROM_PDB_NAME) within the container database ORPCDB1 (DB_NAME).

Let’s go through a few more examples to have a clear understanding. In the following example, I am creating a new pluggable database in the container ORPCDB1 using the seed pluggable database.

---//
---// creating new pluggable database in container ORPCDB1 //---
---//
SQL> create pluggable database CDB1_PDB_2 admin user pdb_admin identified by oracle
  2  file_name_convert=('/data/oracle/orpcdb1/pdbseed/','/data/oracle/orpcdb1/cdb1_pdb_2/')
  3  ;

Pluggable database created.

Let’s see what information is stored in the historical view for this new pluggable database.

---//
---// checking PDB history //---
---// 
SQL>  select CON_ID,PDB_NAME,PDB_DBID,OP_TIMESTAMP,OPERATION,CLONED_FROM_PDB_NAME,DB_NAME  from cdb_pdb_history order by 4;

    CON_ID PDB_NAME          PDB_DBID OP_TIMESTAMP         OPERATION        CLONED_FROM_PDB_NAME DB_NAME
---------- --------------- ---------- -------------------- ---------------- -------------------- ---------------
         3 CDB1_PDB_1      3578281016 14-MAR-2016 00:30:08 CREATE           PDB$SEED             ORPCDB1

We are not seeing any information related to the new pluggable database CDB1_PDB_2. The reason is that the new pluggable database is not yet open (as shown below) and we must open the pluggable database to be able to query the PDB history

---//
---// pluggable database CDB1_PDB_2 is in closed state //---
---//
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB1_PDB_1                     READ WRITE NO
         4 CDB1_PDB_2                     MOUNTED

---//
---// open pluggable database CDB1_PDB_2 //---
---//
SQL> alter pluggable database CDB1_PDB_2 open ;

Pluggable database altered.

---//
---// query PDB history //---
---//		 
SQL>  select CON_ID,PDB_NAME,PDB_DBID,OP_TIMESTAMP,OPERATION,CLONED_FROM_PDB_NAME,DB_NAME  from cdb_pdb_history order by 4;

    CON_ID PDB_NAME          PDB_DBID OP_TIMESTAMP         OPERATION        CLONED_FROM_PDB_NAME DB_NAME
---------- --------------- ---------- -------------------- ---------------- -------------------- ---------------
         3 CDB1_PDB_1      3578281016 14-MAR-2016 00:30:08 CREATE           PDB$SEED             ORPCDB1
         4 CDB1_PDB_2      2205836611 15-APR-2016 23:03:46 CREATE           PDB$SEED             ORPCDB1

As expected, we are now able to check the history of new pluggable database CDB1_PDB_2. Let’s create another pluggable database (CDB1_PDB_3) by cloning it from CDB1_PDB_2.

---//
---// cloning CDB1_PDB_2 to CDB1_PDB_3 //---
---//
SQL> alter pluggable database CDB1_PDB_2 close;

Pluggable database altered.

SQL> alter pluggable database CDB1_PDB_2 open read only;

Pluggable database altered.

SQL> create pluggable database CDB1_PDB_3 from CDB1_PDB_2
  2  file_name_convert=('/data/oracle/orpcdb1/cdb1_pdb_2/','/data/oracle/orpcdb1/cdb1_pdb_3/')
  3  ;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB1_PDB_1                     READ WRITE NO
         4 CDB1_PDB_2                     READ ONLY  NO
         5 CDB1_PDB_3                     MOUNTED

---//
---// cloned PDB's history not yet available //---
---// 		 
SQL>  select CON_ID,PDB_NAME,PDB_DBID,OP_TIMESTAMP,OPERATION,CLONED_FROM_PDB_NAME,DB_NAME  from cdb_pdb_history order by 4;

    CON_ID PDB_NAME          PDB_DBID OP_TIMESTAMP         OPERATION        CLONED_FROM_PDB_NAME DB_NAME
---------- --------------- ---------- -------------------- ---------------- -------------------- ---------------
         3 CDB1_PDB_1      3578281016 14-MAR-2016 00:30:08 CREATE           PDB$SEED             ORPCDB1
         4 CDB1_PDB_2      2205836611 15-APR-2016 23:03:46 CREATE           PDB$SEED             ORPCDB1

Let’s open the cloned pluggable database CDB1_PDB_3 to view its history.

---//
---// opening pluggable database CDB1_PDB_3 //---
---//
SQL> alter pluggable database CDB1_PDB_3 open ;

Pluggable database altered.

---//
---// checking PDB history //---
---//
SQL>  select CON_ID,PDB_NAME,PDB_DBID,OP_TIMESTAMP,OPERATION,CLONED_FROM_PDB_NAME,DB_NAME  from cdb_pdb_history order by 4;

    CON_ID PDB_NAME          PDB_DBID OP_TIMESTAMP         OPERATION        CLONED_FROM_PDB_NAME DB_NAME
---------- --------------- ---------- -------------------- ---------------- -------------------- ---------------
         3 CDB1_PDB_1      3578281016 14-MAR-2016 00:30:08 CREATE           PDB$SEED             ORPCDB1
         4 CDB1_PDB_2      2205836611 15-APR-2016 23:03:46 CREATE           PDB$SEED             ORPCDB1
         4 CDB1_PDB_2      2205836611 15-APR-2016 23:03:46 CREATE           PDB$SEED             ORPCDB1
         5 CDB1_PDB_3      	263485772 15-APR-2016 23:08:24 CLONE           	CDB1_PDB_2           ORPCDB1		 

As we can see, from the historical information; we can derive that the pluggable database CDB1_PDB_3 (PDB_NAME) was cloned (OPERATION) on 15-APR-2016 (OP_TIMESTAMP) from pluggable database CDB1_PDB_2 (CLONED_FROM_PDB_NAME) in container database ORPCDB1 (DB_NAME). Notice that, we now have duplicate entries for pluggable database CDB1_PDB_2 creation operation. One of this entry relates to the operation where we created the pluggable database CDB1_PDB_2 using seed pluggable database. The other entry is due to clone operation where we cloned pluggable database CDB1_PDB_3 from the pluggable database CDB1_PDB_2. When we clone from a existing pluggable database, existing pluggable database’s history also gets cloned to the new pluggable database.

Let’s drop one of this pluggable database and see how it effects the PDB history.

---//
---// dropping cloned pluggable database CDB1_PDB_3 //---
---//
SQL> alter pluggable database CDB1_PDB_3 close;

Pluggable database altered.

SQL> drop pluggable database CDB1_PDB_3 including datafiles;

Pluggable database dropped.

---//
---// checking PDB history //---
---//
SQL>  select CON_ID,PDB_NAME,PDB_DBID,OP_TIMESTAMP,OPERATION,CLONED_FROM_PDB_NAME,DB_NAME  from cdb_pdb_history order by 4;

    CON_ID PDB_NAME          PDB_DBID OP_TIMESTAMP         OPERATION        CLONED_FROM_PDB_NAME DB_NAME
---------- --------------- ---------- -------------------- ---------------- -------------------- ---------------
         3 CDB1_PDB_1      3578281016 14-MAR-2016 00:30:08 CREATE           PDB$SEED             ORPCDB1
         4 CDB1_PDB_2      2205836611 15-APR-2016 23:03:46 CREATE           PDB$SEED             ORPCDB1

The moment, we dropped a pluggable database; all of its associated history information is vanished from dictionary. As we know, the historical information persists within the pluggable database and hence dropping the pluggable database will erase the historical information. We can also notice that the duplicate entry for pluggable database CDB1_PDB_2 is no longer visible as it was associated with the clone operation and we have dropped the cloned pluggable database CDB1_PDB_3.

Let’s go through another example to see what information is stored when we UNPLUG/PLUG a pluggable database. In the following example, I am unplugging pluggable database CDB1_PDB_2 from container ORPCDB1.

---//
---// unplugging pluggable database CDB1_PDB_2 //---
---//
SQL> alter pluggable database CDB1_PDB_2 close;

Pluggable database altered.

SQL> alter pluggable database CDB1_PDB_2 unplug into '/data/oracle/orpcdb1/template/cdb1_pdb_2.xml';

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB1_PDB_1                     READ WRITE NO
         4 CDB1_PDB_2                     MOUNTED

---//
---// dropping unplugged PDB (keeping datafile to use for later plugin) //---
---//		 
SQL> drop pluggable database CDB1_PDB_2 keep datafiles;

Pluggable database dropped.

SQL> show pdbs

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

---//
---// check PDB history //---
---//		 
SQL>  select CON_ID,PDB_NAME,PDB_DBID,OP_TIMESTAMP,OPERATION,CLONED_FROM_PDB_NAME,DB_NAME  from cdb_pdb_history order by 4;

    CON_ID PDB_NAME          PDB_DBID OP_TIMESTAMP         OPERATION        CLONED_FROM_PDB_NAME DB_NAME
---------- --------------- ---------- -------------------- ---------------- -------------------- ---------------
         3 CDB1_PDB_1      3578281016 14-MAR-2016 00:30:08 CREATE           PDB$SEED             ORPCDB1

As we can see, we are not able to view the UNPLUG operation details by querying the PDB history. Again the reason is the historical information being persisted within the pluggable database and not within the container. Since the pluggable database in UNPLUGED, there is no way to query its history from the ROOT (CDB$ROOT) container.

Let’s plug the pluggable database CDB1_PDB_2 back to container ORPCDB1 and see how the historical details are impacted.

---//
---// plugging CDB1_PDB_2 into container ORPCDB1 //---
---//
SQL> create pluggable database CDB1_PDB_2 using '/data/oracle/orpcdb1/template/cdb1_pdb_2.xml'
  2  NOCOPY
  3  TEMPFILE REUSE;

Pluggable database created.

SQL> alter pluggable database CDB1_PDB_2 open;

Pluggable database altered.

---//
---// checking PDB history //---
---//
SQL>  select CON_ID,PDB_NAME,PDB_DBID,OP_TIMESTAMP,OPERATION,CLONED_FROM_PDB_NAME,DB_NAME  from cdb_pdb_history order by 4;

    CON_ID PDB_NAME          PDB_DBID OP_TIMESTAMP         OPERATION        CLONED_FROM_PDB_NAME DB_NAME
---------- --------------- ---------- -------------------- ---------------- -------------------- ---------------
         3 CDB1_PDB_1      3578281016 14-MAR-2016 00:30:08 CREATE           PDB$SEED             ORPCDB1
         4 CDB1_PDB_2      2205836611 15-APR-2016 23:03:46 CREATE           PDB$SEED             ORPCDB1
         4 CDB1_PDB_2      2205836611 15-APR-2016 23:39:31 UNPLUG                                ORPCDB1
         4 CDB1_PDB_2      2205836611 15-APR-2016 23:48:33 PLUG             CDB1_PDB_2           ORPCDB1

The moment we plug the pluggable database into the container, all of its associated historical information reappears again.We are now able to view the details about the UNPLUG operation (which we performed earlier) as well the PLUG operation (which we performed now) along with the details about when (OP_TIMESTAMP) and where (DB_NAME) the pluggable database was originally created (OPERATION) .

Let’s plug another database (this time from a remote container database), before jumping into conclusions. In the following example, I am unplugging pluggable database CDB3_PDB_2 from remote container database ORPCDB3 and plugging that into local container database ORPCDB1 as pluggable database CDB1_PDB_4.

---//
---// logged into remote container ORPCDB3 //---
---//
SQL> select name,host_name,cdb from v$database,v$instance;

NAME      HOST_NAME                      CDB
--------- ------------------------------ ---
ORPCDB3   labserver3.oraclebuffer.com    YES

SQL> show pdbs

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

---//
---// UNPLUG pluggable database CDB3_PDB_2 //---
---//		 
SQL> alter pluggable database CDB3_PDB_2 close;

Pluggable database altered.


SQL> alter pluggable database CDB3_PDB_2 unplug into '/data/oracle/orpcdb3/template/cdb3_pdb2.xml';

Pluggable database altered.

Let’s transfer the datafiles and XML manifest file before we can PLUG the pluggable database in the local container.

##---
##--- transfer datafile and XML manifest file to local server ---##
##---
[oracle@labserver1 cdb1_pdb_4]$ pwd
/data/oracle/orpcdb1/cdb1_pdb_4
[oracle@labserver1 cdb1_pdb_4]$ scp oracle@labserver3:/data/oracle/orpcdb3/cdb3_pdb_2/* .
oracle@labserver3's password:
sysaux01.dbf                                                                  100%  230MB  46.0MB/s   00:05
system01.dbf                                                                  100%  225MB  45.0MB/s   00:05
temp01.dbf                                                                    100%   20MB  20.0MB/s   00:01
users01.dbf                                                                   100%  500MB  41.7MB/s   00:12
[oracle@labserver1 cdb1_pdb_4]$ scp oracle@labserver3:/data/oracle/orpcdb3/template/cdb3_pdb1.xml /data/oracle/orpcdb1/template/cdb3_pdb2.xml
oracle@labserver3's password:
cdb3_pdb1.xml                                                                 100% 5048     4.9KB/s   00:00
[oracle@labserver1 cdb1_pdb_4]$

Now we are ready to plug the pluggable database in local container. Let’s do it.

---//
---// PLUG cdb3_pdb_2 into local container as cdb1_pdb_4 //---
---//
SQL> show pdbs

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


SQL> create pluggable database CDB1_PDB_4 using '/data/oracle/orpcdb1/template/cdb3_pdb2.xml'
  2  source_file_name_convert=('/data/oracle/orpcdb3/cdb3_pdb_2/','/data/oracle/orpcdb1/cdb1_pdb_4/')
  3  NOCOPY
  4  TEMPFILE REUSE
  5  ;

Pluggable database created.

SQL> alter pluggable database CDB1_PDB_4 open;

Pluggable database altered.

---//
---// checking PDB history //---
---//
SQL> select CON_ID,PDB_NAME,PDB_DBID,OP_TIMESTAMP,OPERATION,CLONED_FROM_PDB_NAME,DB_NAME  from cdb_pdb_history order by 4;

    CON_ID PDB_NAME          PDB_DBID OP_TIMESTAMP         OPERATION        CLONED_FROM_PDB_NAME DB_NAME
---------- --------------- ---------- -------------------- ---------------- -------------------- ---------------
         3 CDB1_PDB_1      3578281016 14-MAR-2016 00:30:08 CREATE           PDB$SEED             ORPCDB1
---------------- following entries are related to first set (local) of UNPLUG/PLUG example ---------------------
         4 CDB1_PDB_2      2205836611 15-APR-2016 23:03:46 CREATE           PDB$SEED             ORPCDB1
         4 CDB1_PDB_2      2205836611 15-APR-2016 23:39:31 UNPLUG                                ORPCDB1
         4 CDB1_PDB_2      2205836611 15-APR-2016 23:48:33 PLUG             CDB1_PDB_2           ORPCDB1
---------------- following entries are related to second set (remote) of UNPLUG/PLUG example -------------------
         5 CDB3_PDB_2      4154857005 17-APR-2016 00:16:36 CREATE           PDB$SEED             ORPCDB3
         5 CDB3_PDB_2      4154857005 17-APR-2016 00:18:41 UNPLUG                                ORPCDB3
         5 CDB1_PDB_4      4154857005 17-APR-2016 00:24:37 PLUG             CDB1_PDB_4           ORPCDB1

We have three entries for each set of UNPLUG/PLUG operation. The three entries related to CDB1_PDB_2/CDB1_PDB_4 implies that the pluggable database CDB1_PDB_2/CDB3_PDB_2 was initially CREATED (OPERATION) in container database ORPCDB1/ORPCDB3 (DB_NAME), then UNPLUGED (OPERATION) from container database ORPCDB1/ORPCDB3 (DB_NAME) and finally PLUGGED (OPERATION) into container database ORPCDB1 (DB_NAME) as pluggable database CDB1_PDB_2/CDB1_PDB_4 (PDB_NAME) respectively.

Since, we have used the same container for first UNPLUG/PLUG demonstration; all the operations showing the same container database name (DB_NAME). If we PLUG a pluggable database from a remote container (like in the second demonstration), the DB_NAME field will reflect that container database name for the UNPLUG operation as it was UNPLUGGED in the remote container. The container (DB_NAME) for CREATE operation may also point to a different container if the pluggable database was initially created in a different container.

Notice that in case of the second example involving pluggable database CDB3_PDB_2, the CLONED_FROM_PDB_NAME is showing as CDB1_PDB_4 even though we actually plugged CDB3_PDB_2 pluggable database in the container database ORPCDB1. This could be due to the fact that we had chosen a different pluggable database name CDB1_PDB_4 (and not CDB3_PDB_2) while plugging it into the container (although I was expecting it to show the original pluggable database name as the source). In such a situation, we need to rely on the CON_ID and OP_TIMESTAMP to relate the historical information with a pluggable database.

Footnote

Pluggable database history is maintained within it’s own dictionary and not in the containers dictionary. Oracle keeps track of CREATE/UNPLUG/PLUG history of each of the pluggable database within the respective PDB’s dictionary (sys.pdb_history$) and the pluggable database must be in the OPEN state to be able to query it’s history.

CDB_PDB_HISTORY view is just a wrapper, which internally calls the DBA_PDB_HISTORY (which internally queries sys.pdb_history$ table in each PDB) view from all the containers (PDBs) as shown below.

---//
---// DDL from CDB_PDB_HISORY view //---
---//
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."CDB_PDB_HISTORY"  CONTAINER_DATA
(
"PDB_NAME", "PDB_ID", "PDB_DBID", "PDB_GUID", "OP_SCNBAS", "OP_SCNWRP", 
"OP_TIMESTAMP", "OPERATION", "DB_VERSION", "CLONED_FROM_PDB_NAME",
"CLONED_FROM_PDB_DBID", "CLONED_FROM_PDB_GUID", "DB_NAME", "DB_UNIQUE_NAME", 
"DB_DBID", "CLONETAG", "CON_ID"
) 
AS
SELECT
"PDB_NAME","PDB_ID","PDB_DBID","PDB_GUID","OP_SCNBAS","OP_SCNWRP",
"OP_TIMESTAMP","OPERATION","DB_VERSION","CLONED_FROM_PDB_NAME",
"CLONED_FROM_PDB_DBID","CLONED_FROM_PDB_GUID","DB_NAME","DB_UNIQUE_NAME",
"DB_DBID","CLONETAG","CON_ID" 
FROM CONTAINERS("SYS"."DBA_PDB_HISTORY") --> querying from all containers (PDBs)

We can query CDB_PDB_HISORY view from root container to know historical information related to all associated pluggable databases and query DBA_PDB_HISTORY view in a pluggable database to know the historical information of that pluggable database.

Hope you find this post useful in understanding, how the historical information (CREATE/UNPLUG/PLUG) is tracked for a pluggable database in Oracle 12c.

2 Comments
  1. Foued
Clef two-factor authentication
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed