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

Multitenant : CDB Fleet Management in Oracle Database 18c

Oracle 18c allows you to monitor multiple container databases centrally as a fleet.

Multitenant : CDB Fleet Management

Prerequisites
CDB Fleet Lead Setup
CDB Fleet Member Setup
Test It
Appendix

Prerequisites

This feature is currently restricted to Enterprise Edition on Engineered Systems, like Exadata, and Enterprise Edition on Oracle Database Cloud Services, as described here. There is a workaround for testing by enabling the "_exadata_feature_on" initialisation parameter.

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;

exit;
EOF

In the examples below we have three databases running on the same virtual machine, but they could be running on separate physical or virtual servers.

cdb1 : Container database with pluggable database pdb1. This will be the fleet lead.
cdb2 : Container database with pluggable database pdb4.
cdb3 : Container database with pluggable database pdb5.

The commands to create these instances with all the necessary settings are covered in the appendix below.

CDB Fleet Lead Setup

Connect to the CDB root container on the CDB that will be the fleet lead, and set the LEAD_CDB parameter to TRUE.

CONN sys/SysPassword1@cdb1 AS SYSDBA

ALTER DATABASE SET lead_cdb = TRUE;


COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value 
FROM   database_properties 
WHERE  property_name = 'LEAD_CDB';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LEAD_CDB                       TRUE

SQL>

The fleet members will need database links back to the lead CDB, so create a common user to be used for these database link connections.

CREATE USER c##cdb_fleet_link_user IDENTIFIED BY cdb_fleet_link_user;
GRANT CREATE SESSION TO c##cdb_fleet_link_user;

CDB Fleet Member Setup

For each fleet member, connect to the CDB root container and create a database link to the fleet lead, then set the LEAD_CDB_URI parameter to reference the database link.

CONN sys/SysPassword1@cdb2 AS SYSDBA

-- DROP PUBLIC DATABASE LINK lead_cdb_link;

CREATE PUBLIC DATABASE LINK lead_cdb_link
  CONNECT TO c##cdb_fleet_link_user IDENTIFIED BY cdb_fleet_link_user USING 'cdb1';

ALTER DATABASE SET lead_cdb_uri = 'dblink:LEAD_CDB_LINK';

SELECT property_value
FROM   database_properties
WHERE  property_name = 'LEAD_CDB_URI';

PROPERTY_VALUE
--------------------------------------------------------------------------------
dblink:LEAD_CDB_LINK

SQL>


CONN sys/SysPassword1@cdb3 AS SYSDBA

-- DROP PUBLIC DATABASE LINK lead_cdb_link;

CREATE PUBLIC DATABASE LINK lead_cdb_link
  CONNECT TO c##cdb_fleet_link_user IDENTIFIED BY cdb_fleet_link_user USING 'cdb1';

ALTER DATABASE SET lead_cdb_uri = 'dblink:LEAD_CDB_LINK';

SELECT property_value
FROM   database_properties
WHERE  property_name = 'LEAD_CDB_URI';

PROPERTY_VALUE
--------------------------------------------------------------------------------
dblink:LEAD_CDB_LINK

SQL>

Using a public database link sounds scary, but remember it is only visible to common users in the CDB root container.

Test It

Connect to the CDB root container of the fleet lead and check what PDBs are present.

CONN sys/SysPassword1@cdb1 AS SYSDBA

COLUMN name FORMAT A30
COLUMN proxy_pdb FORMAT A10

SELECT con_id,
       name,
       open_mode,
       proxy_pdb
FROM v$pdbs
ORDER BY name;

    CON_ID NAME                           OPEN_MODE  PROXY_PDB
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         5 PDB4                           MOUNTED    YES
         7 PDB5                           MOUNTED    YES

SQL>

We can see the "pdb4" and "pdb5" pluggable databases are visible from the "cdb1" root container. The output of the PROXY_PDB column gives us a clue what is really happening here. The "pdb4" and "pdb5" pluggable databases are actually a type of proxy PDB in this root container, but they don't have all the functionality of a conventional proxy PDB, as the subset of tablespaces (SYSTEM, SYSAUX, TEMP and UNDO) are not created locally.

Once the fleet is configured, we can query container data objects (V$, GV$, CDB_, and some Automatic Workload Repository DBA_HIST* views) across the whole fleet.

Since these are a type of proxy PDB we can use the CONTAINERS clause to query the contents of a common user in each PDB. Here's a quick example, based on one from the proxy PDB article.

Create a common user in each instance and create a common object in each of them container (root and PDB) in each instance. We will only be querying the contents of the PDBs, so we don't need to populate those in the root container. They are only present to prevent us getting errors.

CONN sys/SysPassword1@cdb1 AS SYSDBA
CREATE USER c##common_user IDENTIFIED BY Common1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM TO c##common_user CONTAINER=ALL;

CONN c##common_user/Common1@cdb1
CREATE TABLE c##common_user.common_user_tab (id NUMBER);

CONN c##common_user/Common1@pdb1
CREATE TABLE c##common_user.common_user_tab AS
SELECT level AS ID
FROM   dual
CONNECT BY level <= 2;


CONN sys/SysPassword1@cdb2 AS SYSDBA
CREATE USER c##common_user IDENTIFIED BY Common1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM TO c##common_user CONTAINER=ALL;

CONN c##common_user/Common1@cdb2
CREATE TABLE c##common_user.common_user_tab (id NUMBER);

CONN c##common_user/Common1@pdb4
CREATE TABLE c##common_user.common_user_tab AS
SELECT level AS ID
FROM   dual
CONNECT BY level <= 4;


CONN sys/SysPassword1@cdb3 AS SYSDBA
CREATE USER c##common_user IDENTIFIED BY Common1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM TO c##common_user CONTAINER=ALL;

CONN c##common_user/Common1@cdb3
CREATE TABLE c##common_user.common_user_tab (id NUMBER);

CONN c##common_user/Common1@pdb5
CREATE TABLE c##common_user.common_user_tab AS
SELECT level AS ID
FROM   dual
CONNECT BY level <= 6;

We can now connect to the common user in the CDB fleet lead and query the contents of the common object in each PDB.

CONN c##common_user/Common1@cdb1

SELECT a.con_id, COUNT(*)
FROM   CONTAINERS(common_user_tab) a
GROUP BY a.con_id
ORDER BY 1;

    CON_ID   COUNT(*)
---------- ----------
         3          2
         5          4
         7          6

SQL>

Appendix

The three container database instances were built using the following commands. The commands to remove the instances are included, so you can clean up when you are finished.

lsnrctl start


# Container (cdb1) with PDB (pdb1).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb1 \
 -pdbAdminPassword SysPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u01/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

# Set required parameters.
sqlplus / as sysdba <<EOF
alter pluggable database pdb1 save state;
alter system set db_create_file_dest = '/u01/oradata';
alter system set "_exadata_feature_on"=true scope=spfile;
alter system set local_listener='LISTENER';

shutdown immediate;
startup;
exit;
EOF



# Container (cdb2) with PDB (pdb4).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb2 -sid cdb2 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb4 \
 -pdbAdminPassword SysPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u01/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

export ORACLE_SID=cdb2
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

# Set required parameters.
sqlplus / as sysdba <<EOF
alter pluggable database pdb4 save state;
alter system set db_create_file_dest = '/u01/oradata';
alter system set "_exadata_feature_on"=true scope=spfile;
alter system set local_listener='LISTENER';

shutdown immediate;
startup;
exit;
EOF



# Container (cdb3) with PDB (pdb5).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb3 -sid cdb3 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb5 \
 -pdbAdminPassword SysPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u01/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

export ORACLE_SID=cdb3
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

# Set required parameters.
sqlplus / as sysdba <<EOF
alter pluggable database pdb5 save state;
alter system set db_create_file_dest = '/u01/oradata';
alter system set "_exadata_feature_on"=true scope=spfile;
alter system set local_listener='LISTENER';

shutdown immediate;
startup;
exit;
EOF



# Delete the instances.
#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1
#dbca -silent -deleteDatabase -sourceDB cdb2 -sysDBAUserName sys -sysDBAPassword SysPassword1
#dbca -silent -deleteDatabase -sourceDB cdb3 -sysDBAUserName sys -sysDBAPassword SysPassword1

For more information see:

Hope this helps. Regards Tim...

Back to the Top.