Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Wednesday, June 03, 2015

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;



2 comments:

technode said...

Thanks for sharing , very informative and presented well , keep updating morered hat linux training centers in chennai|red hat linux training institute in chennai|red hat training institutes in chennai|linux courses in chennai|linux classes in chennai|linux certification centers in chennai

amitha said...

I am setting up a 12c RAC without multi-tenant option, but with a single-PDB. We have 15 databases in 11g to be migrated, so without multi-tenancy, there will be 15 CDBs with 1 PDB each within. Unless we schema-consolidate which the guys do not have much appetite for at this stage.

I created 2 databases with PDB01 as the PDB name in each of the CDBs. Applications connect to the DB presently using the default service for PDB01. This is a test environment and a POC of sorts before the 11g DBs get migrated to 12c and into the PDBs. At that stage I will be creating meaningful service names for the PDBs, for the applications to connect into.

The current problem: When we connect using the default service exposed for PDB01, the listener routes connection requests sometimes to CDB01 and other times to CDB02. This is since PDB01 service is exposed in both DBs. I take one of the DBs down and all works well.

So, the lesson I learnt was that the PDB name within a cluster needs to be unique across all CDBs. I could well have custom services exposed but users can still connect to PDB01 service if they know it exists.

Is there a way to disable the default service that is created as part of the PDB?