12c: Managing PDBs

Managing a pluggable database (PDB) involves a subset of the tasks required to administer a non-CDB. In this subset of tasks, most are the same for a PDB and a non-CDB, but there are some differences. A PDB administrator is limited to managing a single PDB and cannot affect other PDBs in the multitenant container database (CDB).

When you are managing a PDB, you can modify the PDB with an ALTER DATABASE, ALTER PLUGGABLE DATABASE, or ALTERSYSTEM statement. You can also execute DDL statements on the PDB.

Below are some of the quick examples.


-- Create the TNS Entry for the PDB to be used in tnsnames.ora

PDB2=

  (DESCRIPTION=    (ADDRESS=      (PROTOCOL=TCP)      (HOST=ws9374)      (PORT=1521)

    )    (CONNECT_DATA=      (SERVER=dedicated)      (SERVICE_NAME=pdb2)

    )

  ) 

-- Connect with PDB

C:\Users\ibukhary>sqlplus system/manager@pdb2

-- Change the open mode of the current PDB to mounted
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

-- Change the open mode of the current PDB to open read-only
-- A PDB must be in mounted mode to change its open mode to read-only or read/write unless you --specify the FORCE keyword.ALTER PLUGGABLE DATABASE OPEN READ ONLY;

--Change the open mode of the current PDB from mounted or open read-only to open
--read-write
ALTER PLUGGABLE DATABASE OPEN FORCE;


--Change the open mode of the current PDB from mounted to migrate
ALTER PLUGGABLE DATABASE OPEN UPGRADE;

-- Bringing a Data File Online for a PDB
ALTER PLUGGABLE DATABASE DATAFILE '/u01/mypdbs/pdb1_01.dbf' ONLINE;

--Changing the Default Tablespaces for a PDB, The tablespace or tablespace group 
--specified inthe ALTER PLUGGABLE DATABASE statement must exist in the PDB.
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE pdb1_tbs;
ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE pdb1_temp;

-- Changing the Default Tablespace Type for a PDB
ALTER PLUGGABLE DATABASE SET DEFAULT BIGFILE TABLESPACE;

-- Setting Storage Limits for a PDB

ALTER PLUGGABLE DATABASE STORAGE(MAXSIZE 2G);
ALTER PLUGGABLE DATABASE STORAGE(MAXSIZE UNLIMITED);

--amount of storage in a shared temporary tablespace that can be

--used by sessions connected to the PDB

ALTER PLUGGABLE DATABASE STORAGE(MAX_SHARED_TEMP_SIZE 500M); 

ALTER PLUGGABLE DATABASE STORAGE(MAX_SHARED_TEMP_SIZE UNLIMITED);

ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

-- Setting the Logging Attribute of a PDB
--With the PDB open in restricted mode, this statement specifies the NOLOGGING attribute for the

-- PDB

ALTER PLUGGABLE DATABASE NOLOGGING;

-- Enable force logging mode for the PDB
ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING;

-- To restrict sessions in a PDB

 ALTER SYSTEM disABLE RESTRICTED SESSION;

-- Changing the Statistics Gathering Level for the PDB

ALTER SYSTEM SET STATISTICS_LEVEL = ALL SCOPE = MEMORY;

PDBs Services 

Database services have an optional PDB property. You can set a PDB property when you create a service, and you can modify the PDBproperty of a service.

When a PDB is created, a new default service for the PDB is created automatically, and this service has the same name as the PDB. You cannot manage this service, and it should only be used for administrative tasks. Do not use this default PDB service for applications. Always use user-defined services for applications because you can customize user-defined services to fit the requirements of your applications.

--Creating a Service for a PDB Using the SRVCTL Utility
srvctl add service -db ORCL12c -service mysrv -pdb pdb2

srvctl modify service -db ORCL12c -service mysrv -pdb pdb2

srvctl remove service -db ORCL12c -service mysrv -pdb pdb2

-- Creating a Service for a PDB Using the DBMS_SERVICE Package

BEGIN

  DBMS_SERVICE.CREATE_SERVICE(

    service_name => 'mysrv',

    network_name => 'mysrv.mydomain.sa');

END;

select * from ALL_SERVICES

-- Removing a Service Using the DBMS_SERVICE Package

BEGIN

  DBMS_SERVICE.DELETE_SERVICE(

    service_name => 'mysrv');

END;

CDB and PDB Information in Views

In a CDB, for every DBA_ view, there is a corresponding CDB_ view. All CDB_ views are container data objects, but most DBA_ views are not. Each container data object (tables,views) contains a CON_ID column that identifies the container for each row returned.

-- Meanings of the values in the CON_ID column
0    The data pertains to the entire CDB, In a non-CDB, all CON_ID columns in container 

      data       objects are 0 (zero).

1    The data pertains to the root
2    The data pertains to the seed
3    The data pertains to a PDB, Each PDB has its own container ID.

-- Determining Whether a Database is a CDB
SELECT CDB FROM V$DATABASE;

-- Viewing Information About Each Container in a CDB
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

-- Viewing Container ID, Name, and Status of Each PDB
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

-- Viewing the Name and Open Mode of Each PDB
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

-- Showing the Tables Owned by Specific Schemas in Multiple PDBs
SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
FROM DBA_PDBS p, CDB_TABLES t
WHERE p.PDB_ID > 2 -- to avoid showing the users in the root and seed
AND p.PDB_ID = t.CON_ID AND
t.OWNER IN('RD','HR')
ORDER BY p.PDB_ID;

-- Showing the Users in Multiple PDBs
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
FROM DBA_PDBS p, CDB_USERS u
WHERE p.PDB_ID > 2 AND p.PDB_ID = u.CON_ID
ORDER BY p.PDB_ID;

-- Showing the Data Files for Each PDB in a CDB
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.PDB_ID = d.CON_ID
ORDER BY p.PDB_ID;

-- Showing the Temp Files in a CDB
SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
FROM CDB_TEMP_FILES
ORDER BY CON_ID;

-- Showing the Services Associated with PDBs
SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES
WHERE PDB IS NOT NULL AND
CON_ID > 2
ORDER BY PDB;

-- Querying a Table Owned by a Common User Across All PDBs
SELECT * FROM CONTAINERS(dba_tables);
SELECT * FROM CONTAINERS(emp);

SELECT * FROM CONTAINERS(emp) WHERE CON_ID IN(3,4);

The CONTAINERS clause (available starting 12.1.0.2)enables you to query user-created tables and views across all PDBs in a CDB. This clause enables queries from the root to display data in tables or views that exist in all of the open PDBs in a CDB. The tables and views, or synonyms of them, specified in the CONTAINERS clause must exist in the root and in all PDBs.

-- List the initialization parameters that you can set independently in each PDB
SELECT NAME FROM V$SYSTEM_PARAMETER
WHERE ISPDB_MODIFIABLE = 'TRUE'
ORDER BY NAME;
-- Viewing the History of PDBs
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;