Oct 7, 2016

How to Prevent Accidental Creation of a Pluggable Database (PDB) - Lone-PDB


From Oracle 12.1.0.2 onward the non-CDB architecture is deprecated. As a result you may decide to use the Multitenant architecture, but stick with a single pluggable database (PDB), also known as single-tenant or lone-PDB, so you don't have to pay for the Multitenant option. 


In Standard Edition you can't accidentally create additional PDBs, but in Enterprise Edition you are potentially one command away from having to buy some extra licenses. This article gives an example of a way to save yourself from the costly mistake of creating more than one user-defined PDB in a Lone-PDB instance.






Accidental Creation of a PDB

On checking the current instance we can see there is already an existing user-defined PDB.
SELECT con_id, name FROM v$pdbs;

    CON_ID NAME
---------- ------------------------------
         2 PDB$SEED
         3 PDB1

SQL>
There is nothing in Enterprise Edition to stop you creating additional user-defined pluggable databases, even if you don't have the Multitenant option.
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdbadmin IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');

ALTER PLUGGABLE DATABASE pdb2 OPEN;

SELECT con_id, name FROM v$pdbs;

    CON_ID NAME
---------- ------------------------------
         2 PDB$SEED
         3 PDB1
         4 PDB2

SQL>
Having done this the database will have a "detected usage" reported in the DBA_FEATURE_USAGE_STATISTICS view. It takes a while for this to be visible, but we'll force a sample to check it.
-- Force usage sample.
EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);


COLUMN name  FORMAT A40
COLUMN detected_usages FORMAT 999999999999

SELECT name,
       detected_usages,
       aux_count,
       last_usage_date
FROM   dba_feature_usage_statistics
WHERE  name = 'Oracle Pluggable Databases'
ORDER BY name;

NAME      DETECTED_USAGES  AUX_COUNT LAST_USAG
---------------------------------------- --------------- ---------- ---------
Oracle Pluggable Databases         16   2 04-OCT-16

SQL>
I'm doing this on a test instance, so it has detected the feature usage several times. The important point to notice here is the AUX_COUNT column, which indicates the number of user-defined PDBs currently running. Using the Multitenant architecture results in the detected usage, regardless of the number of PDBs, so this alone does not indicate if you need to buy the Multitenant option. If the AUX_COUNT column is greater than 1 for this feature, you need to buy the option!

 

Let's remove the PDB we just created.

ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;

 

What happens to the feature usage now?

-- Force usage sample.
EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);


COLUMN name  FORMAT A40
COLUMN detected_usages FORMAT 999999999999

SELECT name,
       detected_usages,
       aux_count,
       last_usage_date
FROM   dba_feature_usage_statistics
WHERE  name = 'Oracle Pluggable Databases'
ORDER BY name;

NAME      DETECTED_USAGES  AUX_COUNT LAST_USAG
---------------------------------------- --------------- ---------- ---------
Oracle Pluggable Databases         17   1 04-OCT-16

SQL>
Notice the AUX_COUNT column now has a value of "1".

 

Prevent Accidental Creation of a PDB

We can prevent accidental creation of a PDB using a system trigger. The following trigger is fired for any "CREATE" DDL on the database where the ORA_DICT_OBJ_TYPE system defined event attribute is set to 'PLUGGABLE DATABASE'. It checks to see how many user-defined PDBs are already present. If the number of user-defined PDBs are in excess of the maximum allowed (1), then we raise an error.
CONN / AS SYSDBA

CREATE OR REPLACE TRIGGER max_1_pdb_trg
  BEFORE CREATE ON DATABASE
  WHEN (ora_dict_obj_type = 'PLUGGABLE DATABASE')
DECLARE
  l_max_pdbs PLS_INTEGER := 1;
  l_count    PLS_INTEGER;
BEGIN
  SELECT COUNT(*) 
  INTO   l_count
  FROM   v$pdbs 
  WHERE  con_id > 2;
    
  IF l_count >= l_max_pdbs THEN
    RAISE_APPLICATION_ERROR(-20001, 'More than 1 PDB requires the Multitenant option.' );
  END IF;
END;
/
With the trigger in place, we attemtp to create another pluggable database.
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdbadmin IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdbadmin IDENTIFIED BY Password1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: More than 1 PDB requires the Multitenant option.
ORA-06512: at line 12

SQL>
As expected, the are prevented from creating a second user-defined PDB.

 

Cleanup After an Accident

Looking at the feature usage described above, it would appear in 12.1 all you need to do to recover from accidentally creating more than one PDB is to drop the extra PDBs. At this point I don't know if there is any other mechanism for tracking the maximum number of PDBs ever created in an instance, so I don't know if there is any record of a mistake left behind in the instance for future reference by auditors.
If you do accidentally create more than one user-defined PDB in a container database and you are paranoid about a potential licensing breach, you might want to do the following.
  • Create a new CDB instance with no PDBs.
  • Protect the new CDB instance with the trigger mentioned previously.
  • Unplug the PDB of interest from the original CDB.
  • Plug the PDB into the new clean CDB.
  • Throw away the original CDB instance.





Since the new CDB has never had more than one PDB present, there should be no way the instance could secretly track any breach of the license.

Post a Comment

 
TECH SUPPORT © 2012-2016