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%’;

Oracle 12c Multitentant – My Top Ten PDB Questions PartI

Q. What are my options for connecting to a Pluggable Database ?

A. Connect to root, then
SQL> alter session set container = nisha;

Database connection using easy connect

Ex: CONNECT username/password@host[:port][/service_name][:server][/instance_name]

$ sqlplus test1/test123@//localhost/nisha

Need to define Database connection using a net service name

Example TNSNAMES.ora:
=======
LISTENER_CDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1)
    )
  )

nisha =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = nisha)
    )
  )

Q. How do I switch to main(root) container Database ?

A. SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

Q. How do I determine which PDB or CDB I am currently connected to ?

SQL> show con_name
CON_NAME
——————————
nisha

OR

SQL> select sys_context ( ‘Userenv’, ‘Con_Name’) “Container DB” from dual;
Container DB
——————————————————————————–
nisha

Q. What are my options to migrate an existing pre 12.1 database to 12c Multi-tenant database ?

A.
Option 1.
• Upgrade an existing pre 12.1 database to 12.1
• Plug-in the database post upgrade into a CDB
Option 2.
• Create a staged PDBs based on pdb$seed
• Use datapump or golden gate replication to migrate a Database into a PDB
 

Q. How do I know if my database is Multitenant or not ?

A. From root container execute:
SQL> select NAME, DECODE(CDB, ‘YES’, ‘Multitenant Option enabled’, ‘Regular 12c Database: ‘) “Multitenant Option ?” , OPEN_MODE, CON_ID from V$DATABASE;
NAME                        Multitenant Option ?                  OPEN_MODE              CON_ID
———           ——————————           ——————–           ———-
CDBJIVE              Multitenant Option enabled                      MOUNTED                       0
 

Q. How do I know What Pluggable databases do we have in this container database ?

SQL>  select CON_ID, NAME, OPEN_MODE from V$PDBS;
    CON_ID NAME                           OPEN_MODE
———- ————————        ————
         2 PDB$SEED                         READ ONLY
         3 PDB1                                 MOUNTED
         4 PDB2                                 MOUNTED
         5 PDB3                                 MOUNTED
         6 PDB4                                 MOUNTED
         7 PDB5                                 MOUNTED
         8 nisha                                MOUNTED
         9 nitin                                MOUNTED
 …
 

Q. What is the different Container ID signify?

A. CON_ID “0” means data does not pertain to any particular Container but to the CDB as a whole. For example, a row returned by fetching from V$DATABASE pertains to the CDB and not to any particular Container, so CON_ID is set to “0”.  A CONTAINER_DATA object can conceivably return data pertaining to various Containers (including the Root which has CON_ID==1) as well as to the CDB as a whole, and CON_ID in the row for the CDB will be set to 0.

Following table describes  various values of CON_ID Column in Container Data 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.
Q. Do I need separate SYSTEM,SYSAUX, Temporary tablespaces, and Undo for each of my PDB ?

A. There is a separate SYSTEM and SYSAUX tablespace for the root and for each PDB. However, there is one default temporary tablespace for the entire CDB. But, you can create additional temporary tablespaces in individual PDBs. One active temporary tablespace is needed for a single-instance CDB, or one active temporary tablespace is needed for each instance of an Oracle RAC CDB. There is one active undo tablespace for a single-instance CDB. As with previous versions, for RAC [CDB], there is one active undo tablespace for each instance. Only a common user who has the appropriate privileges and whose current container is the root can create an undo tablespace.

Q. Can I specify a separate default tablespace for the CDB and for each PDB ?

A. Yes. You can specify a separate default tablespace for the root and for each PDB

Q. Does the CDB contain any user data ?

A. No. All user data is in the PDBs. The root contains no user data or minimal user data. This makes the unplug-ability very streamlined

Q. Does Pluggable database support separate database characterset ? 

A. A CDB uses a single character set. All of the PDBs in the CDB use this character set.
 
Q. Is there a specific Net Files in a Pluggable database environment ? 

A. There is a single listener.ora, tnsnames.ora, and sqlnet.ora file for an entire CDB. All of the PDBs in the CDB use these files.
 

Q. Can I change init.ora for my PDB.
For Multitenant there are two groups of parameters: – Those that can be modified within a PDB and those that can only be set at CDB level
The following query helps to determine:

SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE=’TRUE’ ORDER BY NAME;