8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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

Oracle 12.1 allowed 252 user-defined pluggable databases. Oracle 12.2 allows 4096 user-defined pluggable databases on engineered systems, including application root and application containers. 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 user-defined 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.

19c Update
Accidental Creation of a PDB
MAX_PDBS (12.2 Onward)
Prevent Accidental Creation of a PDB (12.1)
Cleanup After an Accident

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.

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

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.

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>

Remember, in 19c onward the safe limit is 3 PDBs, so you can use the following.

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 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 attempt 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 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 MAX_PDBS 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...

Back to the Top.