Oracle 12c Multitentant My Top Questions Part II

CDB / PDB Operations

Q. How can I install and setup Pluggable Database ?

A. Use runInstaller to install the Oracle Database software
Use dbca to create databases. You can create many pluggable databases in a single operation.
DBCA enables you to specify the number of PDBs in the CDB when it is created.After a CDB is created, you can use DBCA to plug PDBs into it and unplug PDBs from it.
What Operations act on PDBs as entities ?
These operations act on PDBs as entities:
• create PDB (brand-new, as a clone of an existing PDB, by plugging in an unplugged PDB)
• unplug PDB
• drop PDB
• set the Open_Mode for a PDB

Q. How do I start up a Pluggable database ?

A. Connect to current PDB:
SQL> alter pluggable database open;

When connect to root:
SQL> alter pluggable database nisha open;

Q. How do I shutdown / close a Pluggable database ?

A. When connect to current PDB:
SQL> alter pluggable database close;

When connect to root:
SQL> alter pluggable database nisha close;

Q. How do I drop a PDB?

A. drop pluggable database nisha including datafiles;

 

Q.  How do I recover/restore a PDB in RAC?

A.

alter pluggable database pdb close immediate instances=all;  <– can do this using srvctl too

restore pluggable database pdb until scn <scn number>;

recover pluggable database pdb until scn <scn number>;

alter pluggable database pdb  open resetlogs;

alter pluggable database pdb open immediate instances=all;  <– can do this using srvctl too

Q. How to clone a PDB from an existing PDB ?

A. Note that the source must be open in read only mode.
— Using Oracle-Managed Files
create pluggable database ishan from nisha;

Q. How do I unplug a PDB ?

A. alter pluggable database Nisha unplug into ‘/acfsdata/oradata/manifestNisha.xml’ ;
The manifest file is an XML file that contains information on a PDB and is required to plug in a PDB, as it contains all the information on a PDB.
It can be re-created using the DBMS_PDB.RECOVER() procedure

Q. How can I tell which parameters are modifiable at PDB level ?

A. select NAME, ISPDB_MODIFIABLE from V$PARAMETER;

Q. What common users do I have in my cdb ?

A. SQL> select distinct USERNAME from CDB_USERS where common = ‘YES’;

Q. How do I create a common user ?
SQL> create user c##db_dba1 identified by manager1 container=all;

Q. How do I create a local user ?
SQL> create user nisha_dba1 identified by manager1 container=current;
Q. How can I view which service is attached to my Pluggable database ? 

A. The following query illustrates

SQL> column NAME format a30
SQL> select PDB, INST_ID, NAME from gv$services order by 1;

PDB                                    INST_ID    NAME
——————————– ———- ——————————–
CDB$ROOT                                  1 cdb1XDB
CDB$ROOT                                  1 SYS$BACKGROUND
CDB$ROOT                                  1 SYS$USERS
CDB$ROOT                                  1 cdb1
NISHA                                           1 nisha
ISHAN                                           1 ishan

Q. Where can I find Alert log and traces for my pluggable Database ?

A. Single copy of Alert log is generated which contains warnings and alert information for all PDBs. Find details by selecting from v$diag_info dynamic view.
Q. Is the multitenant option available in Standard Edition?

A. Yes, but you may only create one PDB, per CDB

Q. How to monitor the undo usage of each container /database in CDB/PDB ?

select NAME,MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(EXPSTEALCNT)
from V$CONTAINERS c , V$UNDOSTAT u
where c.CON_ID=u.CON_ID
group by NAME;

select NAME,SNAP_ID,UNDOTSN,UNDOBLKS,TXNCOUNT,MAXQUERYLEN,MAXQUERYSQLID
from V$CONTAINERS c , DBA_HIST_UNDOSTAT u
where c.CON_ID=u.CON_ID
and u.CON_DBID=c.DBID
order by NAME;

Q. Are there any background processes ex, PMON, SMON etc associated with PDBs ?

A. No. There is one set of background processes shared by the root and all PDBs.

Q. Are there separate control files required for each PDB ?

A. No. There is a single redo log and a single control file for an entire CDB.

Q. Are there separate Redo log files required for each PDB ?

A. No. There is a single redo log and a single control file for an entire CDB.

Q. Can I monitor SGA usage on a PDB by PDB basis?

A. There is a single SGA shared by all pluggable databases. However, you can determine SGA consumptions by all containers i.e, root and PDB.

SQL> alter session set container=CDB$ROOT;
SQL> select POOL, NAME, BYTES from V$SGASTAT where CON_ID = ‘&con_id’;
SQL> select CON_ID, POOL, sum(bytes) from  v$sgastat
group by CON_ID, POOL order by  CON_ID, POOL;

Q. Can I monitor PGA usage on a PDB by PDB basis?

A. select CON_ID, sum(PGA_USED_MEM), sum(PGA_ALLOC_MEM), sum(PGA_MAX_MEM)
from  v$process group by CON_ID order by  CON_ID;

alter session set container =CDB$ROOT;
select NAME , value from  v$sysstat  where NAME like ‘workarea%’;

alter session set container = ;
select NAME , value from  v$sysstat  where NAME like ‘workarea%’;