Before You Begin
This 15-minute tutorial shows you how to define a CDB as the CDB
lead within a CDB fleet and how to define other CDBs as members
of the CDB fleet.
Background
Oracle Database 18c introduces the CDB fleet concept, which is a collection of different CDBs that can be managed as one logical CDB to provide the underlying infrastructure for massive scalability and centralized management of many CDBs.
This technique uses database properties setting and database links.
What Do You Need?
- Oracle Database 18c installed
- Three container databases (CDB) and a pluggable database
(PDB) in two of the CDBs:
ORCL
CDB as the CDB lead,cdb1
andcdb2
as CDB members,PDB1
PDB incdb1
CDB andPDB2
PDB incdb2
CDB
Set the CDB Lead in the CDB Fleet
- Log in to
ORCL
CDB asSYS.
This session is called Session1.export ORACLE_SID=ORCL sqlplus / AS SYSDBA
- Verify that the CDB is not already a CDB lead before you set
ORCL
as the CDB lead in the CDB fleet.
There is another way to verify this.SELECT property_name, property_value FROM database_properties WHERE property_name LIKE 'LEAD%'; PROPERTY_NAME PROPERTY_VALUE ------------------ -------------------- LEAD_CDB FALSE
SELECT sys_context('userenv', 'is_lead_cdb') LEAD_CDB FROM dual; LEAD_CDB -------------------------------------------------------------------- NO
- Set
ORCL
as the CDB lead in the CDB fleet.ALTER DATABASE SET LEAD_CDB = true;
- Verify that
ORCL
is the CDB lead in the CDB fleet.SELECT sys_context('userenv', 'is_lead_cdb') LEAD_CDB FROM dual; LEAD_CDB -------------------------------------------------------------------- YES
- View the same information of the lead role of the CDB in the
alert log file.
$ tail /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/a* ... 2017-12-12T03:37:33.595312+00:00 ALTER DATABASE SET LEAD_CDB = true 2017-12-12T03:37:33.595487+00:00 The role of current CDB in the Fleet is: LEAD Completed: ALTER DATABASE SET LEAD_CDB = true
- Grant the appropriate privileges to the common user in the
CDB lead that will be used for future CDB members to exchange
with the CDB lead through database links.
GRANT sysoper TO system CONTAINER=ALL;
Define CDBs as Members of the Fleet
- In another terminal window, log in to
cdb1
asSYS.
This session is called Session2.export ORACLE_SID=cdb1 sqlplus / AS SYSDBA
- Set
cdb1
as a member of the fleet.SELECT sys_context('userenv', 'is_member_cdb') MEMBER_CDB FROM dual; MEMBER_CDB ------------ NO
- Use a common user in the CDB member identical to a common
user in the CDB lead that will be used to create the fixed
user database link.
CREATE PUBLIC DATABASE LINK lorcl CONNECT TO system IDENTIFIED BY password USING 'ORCL';
- Set the CDB as a member of the fleet.
ALTER DATABASE SET LEAD_CDB_URI = 'dblink:lorcl';
- Verify that
cdb1
is a CDB member in the CDB fleet.SELECT sys_context('userenv', 'is_member_cdb') MEMBER_CDB FROM dual; MEMBER_CDB ------------ YES
- View the same information of the member role of the CDB in
the alert log file.
$ tail /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/a* ... 2017-12-12T04:15:00.352901+00:00 ALTER DATABASE SET LEAD_CDB_URI = 'dblink:lorcl' 2017-12-12T04:15:00.423278+00:00 The role of current CDB in the Fleet is: MEMBER Completed: ALTER DATABASE SET LEAD_CDB_URI = 'dblink:lorcl'
- List the PDBs of the CDB member.
SHOW PDBS CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO
- In Session1, list all PDBs of all CDBs in the
fleet.
Why is theSHOW PDBS CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB_ORCL READ WRITE NO 5 PDB1 MOUNTED
OPEN_MODE
value of thepdb1
memberMOUNTED
in the CDB lead whereas it isOPENED
incdb1?
Members can only be opened in the CDB where they physically exist.
TheSELECT pdb_id, pdb_name, status, is_proxy_pdb "IS_PROXY_PDB" FROM dba_pdbs; PDB_ID PDB_NAME STATUS IS_PROXY_PDB ---------- -------- ------- ------------ 3 PDB_ORCL NORMAL NO 2 PDB$SEED NORMAL NO 4 cdb1 STUB YES 5 PDB1 STUB YES
STUB
value in theSTATUS
column defines the CDB and its PDBs as members in the fleet. After you configure the CDB fleet, PDB information from the various CDB members including the CDB member is synchronized with the lead CDB. All PDBs in the CDB members are now “visible” in the lead CDB, enabling you to access the PDBs in the fleet as a single, logical CDB from the lead CDB. - In another terminal window, log in to
cdb2
asSYS.
This session is called Session3,export ORACLE_SID=cdb2 sqlplus / AS SYSDBA
- Repeat the steps 2 to 5 in order to set
cdb2
as another member of the fleet.SELECT sys_context('userenv', 'is_member_cdb') MEMBER_CDB FROM dual; MEMBER_CDB ----------- NO
- Use a common user in the CDB member identical to a common
user in the CDB lead that will be used to create the fixed
user database link.
CREATE PUBLIC DATABASE LINK lorcl CONNECT TO system IDENTIFIED BY password USING 'ORCL';
- Set the CDB as a member of the fleet.
ALTER DATABASE SET LEAD_CDB_URI = 'dblink:lorcl';
- Verify that
cdb2
is a CDB member in the CDB fleet.SELECT sys_context('userenv', 'is_member_cdb') MEMBER_CDB FROM dual; MEMBER_CDB ------------ YES
- View the same information of the member role of the CDB in
the alert log file.
$ tail /u01/app/oracle/diag/rdbms/cdb2/cdb2/trace/a* ... 2017-12-12T04:20:00.352901+00:00 ALTER DATABASE SET LEAD_CDB_URI = 'dblink:lorcl' 2017-12-12T04:20:00.423999+00:00 The role of current CDB in the Fleet is: MEMBER Completed: ALTER DATABASE SET LEAD_CDB_URI = 'dblink:lorcl'
- List the PDBs of the CDB member.
SHOW PDBS CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB2 READ WRITE NO
- In Session1, check that PDB information from the
CDB members is synchronized with the CDB lead. Read the list
from code1.
SELECT name, open_mode, member_cdb "MEMBER_CDB" FROM v$containers;
- Read the list from another view. See the result from code2.
SELECT pdb_id, pdb_name, status, is_proxy_pdb "IS_PROXY_PDB" FROM dba_pdbs;
Unset the CDB Lead
- You decide to deactivate the CDB lead. In Session1,
execute the following
ALTER DATABASE
statement.
Does it deactivate CDB members? No, it does not because the membership property is still maintained in CDB members. The CDB members can be reattached to the CDB leadALTER DATABASE SET LEAD_CDB = false;
ORCL
onceORCL
will have been reset as the lead of the fleet. - Verify in Session2.
SELECT sys_context('userenv', 'is_member_cdb') MEMBER_CDB FROM dual; MEMBER_CDB ------------ YES
- In Session1, reset the CDB lead.
ALTER DATABASE SET LEAD_CDB = true;
- Observe that the members of the fleet reappear in the list
of monitored PDBs once they get reregistered to the lead.
SELECT pdb_id, pdb_name, status, is_proxy_pdb "IS_PROXY_PDB" FROM dba_pdbs; PDB_ID PDB_NAME STATUS IS_PROXY_PDB ---------- -------- ------- ------------ 3 PDB_ORCL NORMAL NO 2 PDB$SEED NORMAL NO
- Repeat the query to see the CDB members reattaching.
SELECT pdb_id, pdb_name, status, is_proxy_pdb "IS_PROXY_PDB" FROM dba_pdbs; PDB_ID PDB_NAME STATUS IS_PROXY_PDB ---------- -------- ------- ------------ 3 PDB_ORCL NORMAL NO 2 PDB$SEED NORMAL NO 4 cdb1 STUB YES 5 PDB1 STUB YES
- Repeat the query to see the CDB members reattaching.
SELECT pdb_id, pdb_name, status, is_proxy_pdb "IS_PROXY_PDB" FROM dba_pdbs; PDB_ID PDB_NAME STATUS IS_PROXY_PDB ---------- -------- ------- ------------ 3 PDB_ORCL NORMAL NO 2 PDB$SEED NORMAL NO 4 cdb1 STUB YES 5 PDB1 STUB YES 6 cdb2 STUB YES 7 PDB2 STUB YES
Unset a CDB Member
- You decide to release the membership of
cdb1
from the fleet. You may prefer to associate it to another fleet. Go to Session2.ALTER DATABASE SET LEAD_CDB_URI = '';
- In Session1, check that the CDB lead does not
monitor it anymore.
SELECT pdb_id, pdb_name, status, is_proxy_pdb "IS_PROXY_PDB" FROM dba_pdbs; PDB_ID PDB_NAME STATUS IS_PROXY_PDB ---------- -------- ------- ------------ 3 PDB_ORCL NORMAL NO 2 PDB$SEED NORMAL NO 6 cdb2 STUB YES 7 PDB2 STUB YES