CDB Creation 12c

Oracle 12c

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName cdb12c -sid cdb12c -createAsContainerDatabase true -numberOfPdbs 2 -pdbName pdb -pdbadminUsername pdba -pdbadminPassword Oracle123 -SysPassword Oracle123 -SystemPassword Oracle123 -emConfiguration NONE -datafileDestination /u01/app/oracle/ -storageType FS -characterSet AL32UTF8 -memoryPercentage 40

Ref: https://pierreforstmanndotcom.wordpress.com/2013/06/27/how-to-create-a-12-1-database-in-silent-mode/

PDB creation:

ALTER SESSION SET PDB_FILE_NAME_CONVERT=’/u01/app/oracle/cdb12c/pdbseed/’,’/u01/app/oracle/cdb12c/pdb3/’;

CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdba IDENTIFIED BY Oracle123;

(OR)

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=(‘/u01/app/oracle/cdb12c/pdbseed/’,’/u01/app/oracle/cdb12c/pdb3/’);

ALTER PLUGGABLE DATABASE pdb3 OPEN;
(OR)
ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

======================================================================================

WHERE AM I AT: CDB VS. PDB? ñ 12 EDITION
July 21, 2013 ∑ by Bobby Curtis ∑ in Database ∑ 4 Comments
All right, how many of us use the V$INSTANCE view to verify the instance name and current status of the database after we connect? In Oracle Database 12c, we can still find this information from V$INSTANCE. Breath a sigh of relief, for the moment! What if I connect to a container database and then issue an ALTER SESSION command to move into a different container, i.e. as PDB? Will I get the name of the PDB that I move into from the V$INSTANCE view or do I need to look somewhere else? Lets take a look.

Connect to the container database as usual and use the V$INSTANCE view to see where Iím at.

[[email protected] dbhome_1]$ sqlplus / as sysdba
SQL> select instance_name, version, status, con_id from v$instance;
INSTANCE_NAME VERSION STATUS CON_ID


ora12cb 12.1.0.1.0 OPEN 0
Lets move into a PDB. I have quite a few PDBs created, lets just use PDB1.

SQL> alter session set container=PDB1;
Session altered.
Did I actually move containers? How can I tell? Instead of selecting INSTANCE_NAME from V$INSTANCE; we have two new SHOW commands that will provide us the information we are looking for. These command are quite simple (SHOW CON_ID & SHOW CON_NAME).

SQL> show con_id

CON_ID

3
SQL> show con_name

CON_NAME

PDB1
Now that we are confident that we are in PDB1, lets take a look at the V$INSTANCE view again. What you will notice is that V$INSTANCE provides us with information relative to the container database (CDB). It only makes sense that the V$INSTANCE would return CDB information. So, how do we identify items related to the pluggable database (PDB) without the SHOW commands?

Lets take a look at a view that is similar to V$INSTANCE. Enter the V$PDBS view!

SQL> desc v$pdbs;
Name Null? Type


CON_ID NUMBER
DBID NUMBER
CON_UID NUMBER
GUID RAW(16)
NAME VARCHAR2(30)
OPEN_MODE VARCHAR2(10)
RESTRICTED VARCHAR2(3)
OPEN_TIME TIMESTAMP(3)
CREATE_SCN NUMBER
TOTAL_SIZE NUMBER
This view provides similar information as V$INSTANCE does; yet is only specific to PDBs. If we wanted to find out the container_id, name, open_mode and size of the PDB we are currently connected to, we can use this query:

SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE


3 PDB1 READ WRITE 393216000
What is interesting to point out, is that since we used an ALTER SESSION statement to switch to PDB1, the V$PDBS view only lists the PDB we are currently working in. If we want to get a complete list of PDBs within the container database, we need to go back to the root container database and run the same SQL statement.

SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> show con_name

CON_NAME

CDB$ROOT

SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE


2 PDB$SEED READ ONLY 283115520
3 PDB1 READ WRITE 393216000
4 PDBTEST MOUNTED 0
5 PDB2 READ ONLY 393216000
I hope that everyone now understands how to find information related to PDBs from SQL*Plus.

Leave a Reply

Your email address will not be published. Required fields are marked *