IMPORTANT COMMANDS
ABOUT PLUGGABLE DB:
CDB= emcdb
PDB= emprep
Check all PDBS.
show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 EMREP READ WRITE NO
How to connect
to the Pluggable Database.
alter
session set container = EMREP;
Using the
SQL*Plus Command to connect directly to the PDB.
sqlplus
username/password@//hostname:1521/EMREP
show con_name;
CON_NAME
------------------------------
EMREP
Otherweise you will get following error:
ORA-65066: The specified changes must apply to all containers
connect / as sysdba
CON_NAME
------------------------------
CDB$ROOT
SQL> alter user system identified by "xyz" account unlock;
Check the Status of the PDB.
col NAME format a30
col OPEN_TIME format a40
set linesize 200
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
NAME OPEN_MODE RES OPEN_TIME
------------------------------ ---------- --- --------------------------------
EMREP READ WRITE NO 18-DEC-19 09.58.31.722 AM +01:00
select name, open_mode, total_size from v$pdbs;
NAME OPEN_MODE TOTAL_SIZE
--------------------------------------------------
---------- ----------
EMREP
READ WRITE 5890899968
Start up a Pluggable database.
alter pluggable database EMREP open;
This will open all pluggable databases.
alter pluggable database all open;
How to shutdown or close a Pluggable database
alter pluggable database EMREP close immediate;
This will close all pluggable databases.
alter pluggable database all close immediate;
Using a trigger to open all pluggable databases.
sqlplus '/ as sysdba'
CREATE OR REPLACE TRIGGER pdb_startup AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter pluggable database all open';
END pdb_startup;
/
Switch to main container Database.
ALTER SESSION SET CONTAINER = CDB$ROOT;
To check on which CDB or PDB you are currently connected.
show con_name;
select sys_context ( 'Userenv', 'Con_Name') "Container DB"
from dual;
Which parameters are modifiable at PDB’s.
col NAME format a50;
select NAME, ISPDB_MODIFIABLE from V$PARAMETER;
What common users do we have in my cdb.
col USERNAME format a30;
select distinct USERNAME from
CDB_USERS where common = 'YES';
create a common user.
create user c##pdb_dba identified by password container=all;
Create a local user
create user pdb1_dba1 identified by password container=current;
Description of CON_ID in Container Objects.
0 = The data pertains to the entire CDB
1 = The data pertains to the root
2 = The data pertains to the seed
3 – 254 = The data pertains to a PDB, Each PDB has its
own container ID
Any background processes, PMON, SMON etc associated with PDBs.
No there is just one set of background
processes shared by the root and all PDBs.
Control file required for each PDB?
No there is a single redo log and a single
control file for an entire CDB.
Redo log file required for each PDB?
No. There is a single redo log and a
single control file for an entire CDB.
Is it possible to monitor SGA usage on a PDB?
There are single SGA shared by all
pluggable databases. But you can determine SGA consumptions by all containers.
alter session set container=CDB$ROOT;
select POOL, NAME, BYTES from V$SGASTAT where CON_ID =
'&con_id';
3
POOL
NAME
BYTES
--------------
-------------------------------------------------- ----------
shared pool
KKKI consumer 4072
shared pool
ASM generic network state 2336
shared pool
alls-ktimcem
1048824
...
...
select CON_ID, POOL, sum(bytes) from v$sgastat group by CON_ID, POOL order by CON_ID, POOL;
CON_ID
POOL SUM(BYTES)
---------- -------------- ----------
0 java
pool 50331648
0 large
pool 24969216
0 shared
pool 62433872
0
streams pool 14052608
0 1308622288
1 large
pool 8585216
1
shared pool 377902448
2
shared pool 6063816
3
shared pool 426015096
3 streams
pool 2724608
10 rows selected.
Create a pluggable database.
create pluggable database xyz admin user adm identified by xyz;
create pluggable database xyz admin user adm identified by xyz
file_name_convert = ('pdbseed', 'y');
Drop a PDB irreversible.
drop pluggable database xyz including datafiles;
Clone a PDB from an existing PDB.
create pluggable database pdb2 from pdb1;
Unplug a PDB ?
alter pluggable database pdb1 unplug into
'/directory/pdb1_desp.xml' ;
Add or modify a user-managed service ?
Starting a user-managed service using
srvtcl. It will open the PDB automatically in all the instances.
srvctl add service –pdb [pdb_name]
How to view which service is attached to my Pluggable database?
col PDB format a20;
column NAME format a30
select PDB, INST_ID, NAME from gv$services order by 1;
PDB INST_ID
NAME
-------------------- ---------- ------------------------------
CDB$ROOT 1 emcdbXDB
CDB$ROOT 1
SYS$BACKGROUND
CDB$ROOT 1
SYS$USERS
CDB$ROOT 1
emcdb
EMREP 1
emrep
