📋Steps in this guide1/5

Multitenant : Prevent Accidental Creation of a Pluggable Database (PDB) - Lone-PDB

This article gives an example of a way to save yourself from a costly mistake of creating more than one PDB in a Lone-PDB instance.

oracle 12cconfigurationintermediate

by OracleDba

12 views

19c Update

From 19c onward, you can have up to 3 user-defined PDBs without having to license the multitenant option, so lone-PDB is not so much of an issue, but you still have to control accidental created of PDBs in excess of your licensed limit.

Accidental Creation of a PDB

On checking the current instance we can see there is already an existing user-defined PDB. There is nothing in Enterprise Edition to stop you creating additional user-defined pluggable databases, even if you don't have the Multitenant option. Having done this the database will have a "detected usage" reported in the view. It takes a while for this to be visible, but we'll force a sample to check it. 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 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 column is greater than 1 for this feature, you need to buy the option! Let's remove the PDB we just created. What happens to the feature usage now? Notice the column now has a value of "1".

Code/Command (click line numbers to comment):

SELECT con_id, name FROM v$pdbs;

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

SQL>

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>

-- 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>

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

-- 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>

MAX_PDBS (12.2 Onward)

Oracle 12cR2 includes a new initialization parameter called MAX_PDBS , which allows you to set an upper limit for the number of user-defined PDBs. If you are using 12cR2 onward, use this parameter, rather than the trigger approach described below. Remember, in 19c onward the safe limit is 3 PDBs, so you can use the following.

Code/Command (click line numbers to comment):

SQL> ALTER SYSTEM SET max_pdbs=1;

System altered.

SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1;
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
*
ERROR at line 1:
ORA-65010: maximum number of pluggable databases created

SQL>

SQL> ALTER SYSTEM SET max_pdbs=3;

Prevent Accidental Creation of a PDB (12.1)

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 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. With the trigger in place, we attempt to create another pluggable database. As expected, the are prevented from creating a second user-defined PDB.

Code/Command (click line numbers to comment):

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;
/

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>

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 anyone knows something more about this, please contact me. :) 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 or 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. You can read more about how to unplug/plugin a PDB here . For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!