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.
Related articles.
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...