CBD AND PDB concepts

ORACLE - PDB AND CONTAINERS

CDB$ROOT (Container)
  • SYSTEM
  • SYSAUX
  • UNDOTBS
  • TEMP 
  • USERS 
  • Control Files 
  • Redo Logs 
  • Data Dictionary 
  • Common Users 
  • Roles.

PDB$SEED (Seed PDB) (Read Only)
  • SYSTEM
  • SYSAUX 
  • TEMP NOTE: 

Note: PDBs for each PDB the are SYSTEM, SYSAUX, TEMP, Private Users and Roles 

TIPs
  • All container must be the same character
  • Data Guard must be configured at the CDB because there are only stream of redo  
  • People to exclude some PDB from release 12.1.0.2
  • Database Vault must be configured individual each PDB  
  • Encryption must be configured individual each PDB  
  • Oracle Multitenant option is licensed on top of Enterprise Manager, but multitenant single tenancy is include in all editions of the database.
  • Each PDBs have data dictionary connect to CDB
  • CDB has one set of background process to shared with PDBs
  • Non-CDB has one set of background process for each DB
  • CDB has one SGA to shared with each DB  (which will usually be much smaller than total of individual SGA)
  • Non-CDB has one SGA for each DB
  • You should not met DB with requirement for each database complete different 
  • Upgrade within CDB usually
  • CDB if the only DB you can connect without going via database Listener
  • Within PDBs data dictionary are logical links to common object in container data dictionary, (union all)
  • Number and Name of containers are in control file, you can see inv$pdbs or v$container
  • Every PDB has a unique global name 
  • It’s possible to create DB link that use TCP or IPC
  • From within root container you can query all objects in PDBs
  • A command user can be defined in root container
  • CDB can have from zero up to 252 user defined- PDBs
  • CDB is a single instance, regardless of a number of PDBs within CDB
  • An individual PDB may have it’s own temporary tablespace.
  • Each PDB has it own system tablespace   
Note about Upgrade

  • You hove to upgrade only CDB, the result all PDBs be upgraded 
  • You can install a new Oracle Home with new version and plug PDBs database, there are fewest downtime 

VIEWS

Within CDB


CDB_TABLESPACES
note: you can see all tablespace belong to PDBs   
DBA_TABLESPACES 
note: you can see only tablespace belong to CDB

Within PDB
CDB_TABLESPACES=DBA_TABLESPACES  
note: you can see only tablespace belong to PDB 
SHOW commands 
show con_name

V$DATABASE 
select name, cdb from v$database;
V$PDBS
select con_id, name, open_mode, restricted, total_size/1024/1024/1024 GB_Total from v$pdbs;
V$CONTAINERS  = V$PDBS except in root container 
V$CONTAINERS
V$PDBS

select instance_name from v$instance
union all
select name from v$database
union all
select global_name from global_name;

INSTANCE_NAME
--------------------------------------------------------------------------------
cdbrac_1
CDBRAC
CDBRAC     or global_name can be different name  

CONNECT OR SWITCH AMONG CDB$ROOT, PDB$SEED or PDBs, 
show con_name
ALTER SESSION SET container =QA;
alter session set container=cdb$root;
alter session set container=pdb$seed;

Provisioning Pluggable Database
There are four techniques 
1. Create a new PDB from SEED, It has only system, sysaux and tenp.
2. Create a new PDB from non_CDB (must be release 12.x), It converts into PDBs and plug it into CDB, It’s no possible to convert back to non-CDB
3. Clone a PDB to another PDB, the source can be same or different CDB, easy for creating development or test environments. 
4. Plug and unplug PDB, for upgrading or moving to another CDB.

COMMUNICATION BETWEEN CONTAINERS
  
CREATE CONTAINER DATABASE  
* It’s not possible to change non-CDB into CDB or vice versa after created time 
* ENABLE_PLUGGABLE_DATABASE is parameter for CDB or non-CDB
* create database command with enable pluggable database for CDB
* You can create CDB database and specific names for container and seed, or they can be generated automatically by OMF (Oracle Managed Files)
* DB_CREATE_FILE_DEST parameter enable OMF, that applied for container, seed or PDBs.
* PDB_FILE_NAME_CONVERT parameter will generated name for seed and pluggable container by remapping the names 
* PDB_FILE_NAME_CONVERT=‘u01/oradata/cdba’,’/u01/oradata/seed’
* You can specified location of datafile on created time
       
CREATE CONTAINER DB ON FILE SYSTEM SPECIFIC FILE SYSTEM 
1# PFILE /u01/admin/cdba/scripts or $ORACLE_HOME/dbs
control_files=‘/u01/oradata/cdba/control.ctl’
dba_name=cdba
memory_target=1g
enable_pluggable_databse=true 

2# export variables
export ORACLE_SID=cdb1 or WINDOWS set ORACLE_SID=cdb1  oradim -new -sid cdb1 

3#create directories
mkdir -p /u01/oradata/cdba
mkdir /u01/oradata/seed
mkdir /u01/ admin

4# Create container database 
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/admin/cdba/scripts/CreateDB.log append
startup nomount pfile="/u01/admin/cdba/scripts/init.ora";
CREATE DATABASE "cdba”
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '/u01/oradata/cdba/system01.dbf' SIZE 700M REUSE
  AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/oradata/cdba/sysaux01.dbf' SIZE 550M REUSE
  AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/cdba/temp01.dbf' SIZE 20M REUSE
  AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/u01/oradata/cdb1/undotbs01.dbf' SIZE 200M REUSE
  AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u01/oradata/cdba/redo01.log') SIZE 50M,
GROUP 2 ('/u01/oradata/cdba/redo02.log') SIZE 50M,
GROUP 3 ('/u01/oradata/cdba/redo03.log') SIZE 50M
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
enable pluggable database
seed file_name_convert=('/u01/oradata/cdba/system01.dbf','/u01/oradata/cdba/pdbseed/system01.dbf',
                        '/u01/oradata/cdba/sysaux01.dbf','/u01/oradata/cdba/pdbseed/sysaux01.dbf',                        '/u01/oradata/cdba/temp01.dbf','/u01/oradata/cdba/pdbseed/temp01.dbf',                  '/u01/oradata/cdba/undotbs01.dbf','/u01/oradata/cdba/pdbseed/undotbs01.dbf');
spool off

5# run catalog and catproc with perl 
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b catproc $ORACLE_HOME/rdbms/admin/cataproc.sql
6# check  
select name, cdb from v$database;
select name, con_id, open_mode from v$containers;
select con_id, name from v$datafile;
select con_id, name from v$tempfile;
select * from v$controlfile;
select con_id, member from v$logfile;
select sys_context('userenv','con_name') from dual;
Show con_name

Other example 
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/cdb1/scripts/CreateDB.log append
startup nomount pfile="/u01/app/oracle/admin/cdb1/scripts/init.ora";

 CREATE DATABASE "cdb1"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' SIZE 700M REUSE
  AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/cdb1/sysaux01.dbf' SIZE 550M REUSE
  AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/cdb1/temp01.dbf' SIZE 20M REUSE
  AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/u01/app/oracle/oradata/cdb1/undotbs01.dbf' SIZE 200M REUSE
  AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/cdb1/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/cdb1/redo02.log') SIZE 50M,
GROUP 3 ('/u01/app/oracle/oradata/cdb1/redo03.log') SIZE 50M
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
enable pluggable database
seed file_name_convert=('/u01/app/oracle/oradata/cdb1/system01.dbf','/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf',
                        '/u01/app/oracle/oradata/cdb1/sysaux01.dbf','/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf',                       '/u01/app/oracle/oradata/cdb1/temp01.dbf','/u01/app/oracle/oradata/cdb1/pdbseed/temp01.dbf',                     '/u01/app/oracle/oradata/cdb1/undotbs01.dbf','/u01/app/oracle/oradata/cdb1/pdbseed/undotbs01.dbf');
spool off

SCRIPT CREATE CONTAINER DB

1# vi $ORACLE_SID.sh

#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/CUST/adump
mkdir -p /u01/app/oracle/admin/CUST/dpdump
mkdir -p /u01/app/oracle/admin/CUST/pfile
mkdir -p /u01/app/oracle/audit
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/CUST
mkdir -p /u01/app/oracle/product/12.1.0.1/dbhome_1/dbs
mkdir -p /u01/fra
mkdir -p /u01/fra/CUST
mkdir -p u01/oradata/CUST
mkdir -p u01/oradata/CUST/pdbseed
umask ${OLD_UMASK}
PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
ORACLE_SID=CUST; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: CUST:/u01/app/oracle/product/12.1.0/dbhome_1:Y
/u01/app/oracle/product/12.1.0.1/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/admin/CUST/scripts/CUST.sql 

2#

set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host /u01/app/oracle/product/12.1.0.1/dbhome_1/bin/orapwd file=/u01/app/oracle/product/12.1.0.1/dbhome_1/dbs/orapwCUST force=y format=12
@/u01/app/oracle/admin/CUST/scripts/CreateDB.sql
@/u01/app/oracle/admin/CUST/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/CUST/scripts/CreateDBCatalog.sql
@/u01/app/oracle/admin/CUST/scripts/JServer.sql
@/u01/app/oracle/admin/CUST/scripts/context.sql
@/u01/app/oracle/admin/CUST/scripts/ordinst.sql
@/u01/app/oracle/admin/CUST/scripts/interMedia.sql
@/u01/app/oracle/admin/CUST/scripts/cwmlite.sql
@/u01/app/oracle/admin/CUST/scripts/spatial.sql
@/u01/app/oracle/admin/CUST/scripts/labelSecurity.sql
@/u01/app/oracle/admin/CUST/scripts/apex.sql
@/u01/app/oracle/admin/CUST/scripts/datavault.sql
@/u01/app/oracle/admin/CUST/scripts/CreateClustDBViews.sql
@/u01/app/oracle/admin/CUST/scripts/lockAccount.sql
@/u01/app/oracle/admin/CUST/scripts/postDBCreation.sql
@/u01/app/oracle/admin/CUST/scripts/PDBCreation.sql
@/u01/app/oracle/admin/CUST/scripts/plug_PDB1.sql
@/u01/app/oracle/admin/CUST/scripts/postPDBCreation_PDB1.sql

3#
orapwd lo crea el anterior script

4#
db_block_size=8192
open_cursors=300
db_domain=""
db_name="CUST"
control_files=("u01/oradata/CUST/control01.ctl", "/u01/fra/CUST/control02.ctl")
db_recovery_file_dest="/u01/fra"
db_recovery_file_dest_size=4815m
compatible=12.1.0.0.0
diagnostic_dest=/u01/app/oracle
enable_pluggable_database=true
processes=300
sga_target=900m
audit_file_dest="/u01/app/oracle/admin/CUST/adump"
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
dispatchers="(PROTOCOL=TCP) (SERVICE=CUSTXDB)"
pga_aggregate_target=300m
undo_tablespace=UNDOTBS1

5# 
CREATE DATABASE "CUST"
 MAXINSTANCES 8
 MAXLOGHISTORY 1
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 1024
DATAFILE '/u01/oradata/CUST/system01.dbf' SIZE 700M REUSE
  AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/oradata/CUST/sysaux01.dbf' SIZE 550M REUSE
  AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/CUST/temp01.dbf'
  SIZE 20M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/u01/oradata/CUST/undotbs01.dbf'
  SIZE 200M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
  GROUP 1 ('u01/oradata/CUST/redo01.log') SIZE 50M,
  GROUP 2 ('u01/oradata/CUST/redo02.log') SIZE 50M,
  GROUP 3 ('u01/oradata/CUST/redo03.log') SIZE 50M
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
enable pluggable database
seed file_name_convert ('/u01/oradata/CUST/system01.dbf','/u01/oradata/CUST/pdbseed/system01.dbf','/u01/oradata/CUST/sysaux01.dbf','/u01/oradata/CUST/pdbseed/sysaux01.dbf','/u01/oradata/CUST/temp01.dbf','/u01/oradata/CUST/pdbseed/temp01.dbf','/u01/oradata/CUST/undotbs01.dbf','/u01/oradata/CUST/pdbseed/undotbs01.dbf');

6# CreateDBCatalog.sql 

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/CUST/scripts/CreateDBCatalog.log append
alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CUST/scripts -b catalog /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catalog.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CUST/scripts -b catblock /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catblock.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CUST/scripts -b catproc /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catproc.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CUST/scripts -b catoctk /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catoctk.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CUST/scripts -b owminst /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/owminst.plb;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CUST/scripts -b pupbld -u SYSTEM/&&systemPassword /u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/admin/CUST/scripts/sqlPlusHelp.log append
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CUST/scripts -b hlpbld -u SYSTEM/&&systemPassword -a 1  /u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin/help/hlpbld.sql 1helpus.sql;
spool off

Hay que chequearlo


Pluggable database 

USING PDB$SEED TO CREATE A NEW PDB
-PDB$SEED are copied to new PDB
-local version of the system, sysaux, temp
-a local metadata is initialized 
-the command user including SYS and SYSTEM
-A local user is created and granted pdb_dba role
-A new default service is created and registered with listener 

1#
ORACLE _SID=pdba
lsnrctl start # It is running by default address and port
sqlplus a/ as sysdba 
alter system set db_create_file_dest='c:\oradata';

2#
Create pluggable database pdba admin user pdba_admin identified by oracle;

You can specify the following clauses to create new PDB from SEED in different ways:
  • Storage: you can specify the storage limits
  • Default Tablespace: It creates a small file tablespace and assigns this to non-system users.
  • path_prefix : It specifies the absolute path
  • file_name_convert: It specifies the new location of PDB files from source files.
  • Tempfile reuse: it specifies, reuse the tempfile from target location.
  • Roles: the predefined Oracle roles to grant to the PDB_DBA role.
3#
Alter pluggable database pdba open;
Select con_id, name, open-mode from v$containers;
Select con_id, pdb_id, pdb_name, status from cdb_pdbs;

4#
Note the service is registered with the database listener, include a service for root container and a service for PDB
lsnrctl status

CLONIG PDB TO A NEW PDB     
alter pluggable database qa_2014 close;
alter pluggable database qa_2014 open read only;

create pluggable database qa_2015  from qa_2014 storage unlimited file_name_comvert=none;
or
create pluggable database qa_2015  from qa_2014;

alter pluggable database qa_2015 open read write;
alter pluggable database qa_2014 close;
alter pluggable database qa_2014 open read write;
or
CREATE PLUGGABLE DATABASE newpdb FROM salespdb
  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/salespdb/', '/disk1/oracle/dbs/newpdb/')
  PATH_PREFIX = '/disk1/oracle/dbs/newpdb';
or
create pluggable database clone_pdb from pdb 
PATH_PREFIX= '+BRLOAD’ FILE_NAME_CONVERT = ('+DATA','+BRLOAD');
or 
 Create pluggable database pdb1 as clone using ‘/u01/app/oracle/pdb1.xml’ move file_name_convert=(‘+DATA/pdb1/’,’+DATA1’/pdb1/’);

PLUG A NON-CDB INTO A CDB 
note: NON-CDB MUST BE 12c
         DBMS_PDB.DESCRIBE got export metadata 

startup nomount
Alter database open read only;

exec dbms_pdb.describe(‘/temp/rptqa12c.xml’);

sqlplus / as sysdba.  #CDB database    

Create pluggable database rptqa12c using ‘/temp/rptqa12c.xml’;

alter session set container rptqa12c;

To clean up unnecessary metadata not needed in a multitenant enviroment   
@ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sq
Alter pluggable database rptqa12c open read write;

PLUG AND UNPLUGGED PDB INTO CDB
-A sunset of parameters set at the container level can be overridden at the PDBs
-Unplug a PDB and plugging back into same container database preserves the customized parameter in the PDB
-Unplug a PDB and plugging into a different container database preserves the customized parameter in the PDB 
-When plug NON-CDB into CDB the NON-CDB cannot have more than 8 Consumer group, must all be at the level 1, and must have NO plan. Because they will be invalidated the imported resource plan.   

#you has xml belong to ccrespo and datafile, sometime you hast to edit xml for location   
create pluggable database ccrespo using ‘/tmp/ccrespo.xml’ nocopy;
Alter pluggable database Crespo open read write;

UNPLUG AND DROP A PDB

unplug
alter pluggable database  ccrespo close;
alter pluggable database  ccrespo unplug into ‘/tmp/ccrespo.xml’;

Drop
alter pluggable database  ccrespo close;
drop pluggable database ccrespo including datafile;

Plug
nocopy method
create pluggable database pdb4 using '/media/sf_scripts_agap/pdb4.xml' nocopy tempfile reuse;

copy method
create pluggable database pdb_plug_copy using '/u01/app/oracle/oradata/pdb2.xml' 
COPY 
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_copy');
   
MIGRATE A PRE-12C NON-CDB DATABASE TO CDB   
-Only 12c can be converted into CDB
-upgrade and plug is generally more faster than other options.  
-Data pump method 
-ensure compatibility with oracle 12c with plug method 

Establish connection with CDBs and PDBs
-you con connect to CDB with OS authentication with SYS
-You can connect to CDB or PDBs using service name, easy connection or tnsnames.ora 
-By default service is created by each new, cloned or plug DB.
-You can use DBMS_SERVICE or SRVCTL to created additional service for PDB.

Understanding CDB and PDB Service Names
-one listener on oracle 12c con manage a combination of NON-CDB and PDB services.
-PDB$SEED do not have service because it is only for cloned PDB, it is not necessary service for it.
-You can connect with common user with privilege to maintain all PDBs within CDB
select name, pdb from v$services order by 1

Creating with srvctl 
oracle@ol7-121-rac1 ~]$ srvctl add service -db CDBRAC -s angel -pdb qa
[oracle@ol7-121-rac1 ~]$ srvctl start service -db CDBRAC -s angel
[oracle@ol7-121-rac1 ~]$ lsnrctl status

 select name, pdb from v$services order by 1

SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string       (ADDRESS=(PROTOCOL=TCP)(HOST=
                                                 192.168.56.103)(PORT=1521))
remote_listener                      string

STARTUP AND CLOSED
- shutdown with immediate, transactional and abort    
alter pluggable database pdb open;
alter pluggable database pdb close;
alter pluggable database pdb open read only;
alter pluggable database pdb open read write;
alter pluggable database all except pdb2 open;

TRIGGER FOR STARING UP AUTOMATICALLY 
create trigger open_pdb2 
after startup on database
begin
 execute immediate ‘alter pluggable database pdb2 open’;
end;
/ 

Select con_id, name, open_mode from v$pdba;

DATABASE RESTRICTED 
-you can set PDB datafiles offline or online
-you can change PDB’s default tablespace or PDB’s default temporary tablespace
-change the maximum  size of a PDB 
alter pluggable database storage (maxsize 50g)
-change the name of a PDB
-as PDB is easier than NON-CDB these can of change or setting (other way you must be shutdown )   
alter pluggable database pdb2 restricted;  
note: to check PDB restricted in v$pdbs

PARAMETER CHANGE
-the following show the non-default values for species parameters across al PDBs
ispdb_modifiable in v$parameters
select pdb_uid, name, value$ from pdb_spfile$ where name='start_transformation_enabled';
-some of ALTER SYSTEM commands affect only PDBs or CDB in with they are run, in contrast, others can be run only in the run container.
e.g. only affect PDBs where run 
Alter system flush shared_pool 
Alter system flush buffer_cache
Alter system enable restricted session 
Alter system kill session
Alter system set <parameter>
e.g. you Run PDB but affect entire container
alter system checkpoint
Unless datafile or database are read-only mode or offline
Other alter system only run in container DB with a common user with sysdba privilege

TO CHECK PARAMETER DISTINT TO CDB IN PDB 
select value, con_id from v$system_parameter where name='db_create_file_dest'

SQL> select value, con_id from v$system_parameter where name='db_create_file_dest'
  2  ;

VALUE                                                  CON_ID
-------------------------------------------------- ----------
+DATA                                                        0
/u01/app/oracle/oradata/agap/client                        3

CREATE TABLESPACE WITHIN PDB 
alter session set container=pdba;
create tablespace test datafile size 500m 
auto extend on next 100m maxsize 1g;

alter default tablespace test;

create temporary table space tmp_pdba tempfile size 100m 
auto extend on next 100m max size 500m;

alter default temporary tablespace temp_pdba;
DEFAULT TABLESPACE OR TEMPORARY TABLESPACE
YOU SHOUL EXECUTE ON CDB$ROOT

alter default tablespace users;
select con_id, property_value from cdb_properties where property_name='DEFAULT_PERMANENT_TABLESPACE'

  CON_ID PROPERTY_VALUE
---------- --------------------
         1 USERS
         3 USERS   
alter default temporary tablespace temp;
select con_id, property_value from cdb_properties where property_name='DEFAULT_TEMP_TABLESPACE';

    CON_ID PROPERTY_VALUE
---------- --------------------
         3 TEMP
         1 TEMP


USER AND ROLE
-there are two kind of user, 
* local user
  CDB cannot have a local user 
you should add container=current at created time for local user to applied current PDB
Create user in PDB is the same as NON-CDB
* common use
common user must being with C## unless you changed COMMON_USER_PREFIX. 
you should add container=all at created time for common user to applied all PDB, or container=pdba for specific PDB
If you did not add current=all of specific PDB for command user you can fix issue granted create session to user in PDB
in CDB$root cannot have a local user
common user have s=the same indemnified and password on CDB and every PDB in CDB
having a common user account does not automatically mean you have the same privileges across every PDB in a CDB
  and CDB
a common user only connect by default into container, regardless of privileges granted 


container=all
container=current. # It is not permitted in CDB$ROOT that it does not sense  
container=pdba1

CREATE LOCAL USER IN PDB
alter session set container=PDBA;

create user prueba identidad prueba;
grant create session to prueba; 
or It ‘is the same
create user prueba identified prueba container=current;

CREATE COMMON USER
create user c##agap identified by 23112001 container=all;
grant dba to c##agap; # this case you only have dba privilege on CDB$root
grant dba to c##agap container=all;

GRANT
grant create user, set container c##agap container=all;
grant dba to c##agap container=all;

REVOKE
revoke set container from c##agap container=all;
revoke dba from c##agap container=all;

ROLE
-there are common role and local
-common role is visible in all PDBs in CDB
-common role can have different privileges in within each PDB

-only common role can created in CDB$ROOT
create role c##mv container=all; 

Selelct * from session_roles;   # be must connect as user want to see whom 
Select role, common, oracle_maintained, con_id from cdb_roles order by role;

SELECT
-ORACLE_MAINTENED Denotes whether the user was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql). A user for which this column has the value Y must not be changed in any way except by running an Oracle-supplied script.

To see if user is it a common user or not
select COMMON from dba_users where username='C##AGAP';
select CON_ID, username, common, ORACLE_MAINTAINED from cdb_users order by 1,3

To see role granted 
select GRANTEE, GRANTED_ROLE, COMMON from dba_role_privs where GRANTEE='C##AGAP';

Enable a common user to see data in specified PDBs.
-by default local or common object are not shared and are accessible only in the PDBs
-Only common user can create shared table

Shared objects are two types
object links
-It connect to every PDBs to a shared table in CDB, and each PDBs see the same rows  
metadata links
--It connect to every PDBs to a shared table in CDB, and each PDBs see it own private rows
  
 AUDIT IN MULTITENANT ENVIROMENT
traditional Audit
-Traditional Audit is enable the same as NON-CDB with audit_* (audit_trail, audit_file_dest, ….)
-It is configured in each PDB 
Unified Audit
-It is multitenent 
-If a policy is defined in a root container, I can be enable in al PDBs.
-It cannot enable or disable within PDB
-A policy defined while connect to PDB is visible only PDB and can manage by DBA of that PDB
unified_audit_trail
cdb_unified_audit_trail

Create Policy

create audit policy audit_sys actions all
when 'sys_context(''ruserenv'',''isdba'')=''true'''
evaluate per statement
/
ENABLE POLICY
audit policy audit_sys whenever successful;

DISABLE POLICY
-only you can disable in CDB$ROOT
-you cannot disable policy connect to PDB even as SYSDBA
Noaudit policy audit_sys;

TO SEE AUDIT IN CDB UNIFIED AUDIT
Select con_id, dbusername, sql_text from cdb_unified_audit_trail where unified_audit_policies='AUDIT_SYS' and sql_text like '%emp%'

MULTITENANT BACKUP AND RECOVERY 
- You can back up and restore entire CDB or PDB, a tablespace, a datafile or even a single block anywhere in CDB
- You can duplicate PDB with RMAN, also there are more options, e.g. duplicate all PDBs, subset of PDBs within CDB to new CDB, even including root and seed.  
- Carefully when connect with RMAN with authentication because ORACLE_SID refer to the instance, that mean you will connect to CDO$ROOT, to make sure that you have to connect with password file authentication and connect through TNS service name
- rman target sys/oracle@pdba  
- The scope of a session launched in this way will be limited to the tablespace that make up the pluggable database 
- loss any temporary tablespace within PDB are re-created automatically at the container open.
- you can back up controlfile when connected to the root container or PDBs
- Tablespace backup can include multiplex trablespace from different PDBs.
- PDBs can back up by connecting to the target PDBs or CDB
- SYSBACKUP privileges can granted commonly to a common user

Operation that you only do connect to CDB
-only you can use DATA RECOVERY ADVISOR in a CDB
List failure
Advise failure 
Repair failure
-Restore database
-restore controlfile
-recover database (complete or incomplete)
-critical datafile in CDB iare SYSTEM and UNDO
-if critical datafile in CDB are damage that will force to instance to terminate, and they must be restore and recovery in mount mode 
-In Pluggable database system is a critical tablespace, if it damage the PDB brough down to mount mode.
-No critical datafile or tablespace in CDB or within PDB can restore and recover it as offline mode

DUPLICATE DATABASE WITH RMAN
-you must create an auxiliary instance for destination CDB
-auxiliary instance must be started with initial parameter ENABLE_PLUGGABLE_DATABASE=TRUE
-you can choose with PDB include and with it not include   
-duplicate always include root and seed container
-when duplicate PDB the custom parameters are cloner as well, you can see this parameter in pdb_spfile$
-Using the SKIP keyword, you can duplicate CDB with all PDBs except for the PDBs   
-the TABLESPACE keyword with qualified table space name will duplicate a single tablespace along with any other complete PDBs, in a single duplicate command  

The following duplicate CDB with only PDB tool to a new CDB called nine.
RMAN>duplicate database to nine pluggable database tool;

The following duplicate CDB with PDBs tool and qa to a new CDB called nine.
RMAN>duplicate database to nine pluggable database tool, qa;

You can also duplicate with individual tablespace 
RMAN>duplicate databased   

Operation that you only do connect to PDB  

RMAN COMMANDS    
backup database;
backup pluggable database pdba;
backup of tablespace of specified PDB  
Backup tablespace pdba:users, pdbb:users;
MULTITENANT PERFOMANCE MONITORING AND TUNNING
-You can use Resource management to made sure resources for each PDB
-There are one spfile for all PDBs but many of those you can set within PDB, you can know in v$parameter (ispdb_modifiable column) 
-each ID_CON are stored in each block in data buffer cache, as the same in shared pool in SGA and Global PGA
-individual memory parameter are limited within PDB with SORT_AREA_SIZE and SOR_AREA_RETAINED_SIZE
-Neither Consumer Groups nor shares can be defined for root container
-Resource usage allocate between PDBs are measured in shares.
-The default resource allocation in a CDB is none, all pubs compete equally of all resource  

E.g.
PDB Name Shares CPU percent (Maximum)
pdba 1 16.67
pdbb 3 50
pdbc 1 16.67
pdbd 1 16.67

If one or more are not active, their shared is available to active PDBs.
You can do with DBMS_RESOUCE_MANAGER
  
NONE: Let each PDB use all resource of the CDB if not other PDB is active, when multiple PDB need resources, they are divide equal.
MINIMUM: Each PDB get minimum guaranteed location resources .
MINIMUM AND MAXIMUM: Beach PDB get both minimum and maximum guaranteed resources.

BY default is NONE

TO SEE WHICH PAARMETER CAN BE SET WITHIN PDB
CDB$ROOT@AGAP> select ISPDB_MODIFIABLE, count(ISPDB_MODIFIABLE) from v$parameter group by ISPDB_MODIFIABLE;

ISPDB COUNT(ISPDB_MODIFIABLE)
----- -----------------------
TRUE                      182
FALSE                     199

Select pdb_uid, pdb_name, name, valu$ from pdb_spfile$ ps, join cdb_pdbs cp on ps.pdb_uid=cp.con_uid;  
UTILIZATION_LIMIT define percentage of CPU, I/Oand parallel servers available to a PDB

PARRALEL_SERVE_LIMIT define maximum percentage of the CDB’s PARRALLEL_SERVER_TARGET, this value override UTILIZATION_LIMITE directive, default is 100%

CREATING THE CDB RESOURCE PLAN
1. CREATE PENDING AREA
2. CREATE CDB RESOURCE PLAN
3. CREATE PDB DIRECTIVES
4. UPDATE DEFAUL PDB DIRECTIVES
5. UPDATE DEFAUL AUTOTASK DIRECTIVES
6. VALIDATE PENDING AREA
7. SUBMIT THE PENDING AREA

1#
connect /as sydba
exec dbms_rssource_manager.create_pending_area();
2#
begin
dbms_resource_manager.create_cdb_plan(
plan =>’low_prio_apps’,
comment =>’TOOL and rep database low priority’);
end;
/

3#
begin
dbms_resource_manager.create_cdb_plan_directives(
plan =>’low_prio_apps’,
pluggable_databse => ‘tool’,
shares =>1,
utilization_limit =>50,
parrallel_server_limit =>50);
end;
/
begin
dbms_resource_manager.create_cdb_plan_directives(
plan =>’low_prio_apps’,
pluggable_databse => ‘ccrespo’,
shares =>1,
utilization_limit =>75,
parrallel_server_limit =>75);
end;
/

4#
Exec dbms_resource_manager.validate_pending_area();

5#
Exec dbms_resource_manager.submit_pending_area();

6#
Alter system set resource_manager_plan=‘low_prio_apps’;

VIEW RESOURCE MANAGER DIRECTIVES
To see all the current resource plan
DBA_RSRC_PLAN_DIRECTIVES

select plan, pluggable_database, shares, utilization_limit, parallel_server_limit from dba_cdb_rsrc_plan_directives order by plan, pluggable_database
MANAGING RESOURCE WITHON PDB
V$RSRC_PLAN with con_id

CAPTURE SOURCE DATABASE WORKLOAD
You can capture the database workload to see how they would perform as a PDB of other CDB, the general step are:
1. Capture the workload of an existing non-CDB OR PDB
2. Optionally export te=he AWR snapshots for the database
3. Restore the candidate database onto the target system
4. Make change to the imported database as needed, such upgrading to Oracle database 12c.
5. Copy the generated workload files to the target system.
6. Process the worload as a one-time prerequisite step 
7. Repeat al steps 1 - 6 for all candidate database
8. Configure the target system for replay (such as the workload replay processes)
9. Replay the workload for all PDBs within a single CDB on the target system

Note: page 77 of the book last chapter 
You can capture workload for 8 or 24 hours
You can also export AWR snapshots, SQL profiles, SQL tuning sets 
 It is possible to reversed entire CDB to replay test 
   
PITR (Point in Time Recovery)
RECOVERY PLUGGABLE DATABASE 

$ export NLS_LANG=american_america.utf8
$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'

SQLPLUS>alter pluggable database pdb2 close immediate;
exit

rman target /

run{
set until time='2016-09-27 15:06:05';
restore pluggable database pdb2;
recover pluggable database pdb2 auxiliary destination '/home/oracle/';
alter pluggable database pdb2 open resetlogs;}

sqlplus sys/oracle_4U@pdb2 as sysdba
# IN PDB
select DB_INCARNATION#,PDB_INCARNATION#,INCARNATION_TIME from v$pdb_incarnation;
# IN CDB
connect / as sysdba
select sequence#,status from v$log;
select INCARNATION#,RESETLOGS_TIME from v$database_incarnation;


RESTORE CONTROLFILE IN CDB
- If auto backup is on, after the any backup even backup of PDB, the control file and spfile already backed up
1. Start up CDB instance in nomount
2. Restore Controlfile from autobackup
3. Recover the database
4. Open database with open RESETLOGS
Posted by