Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Mar 26, 2015

Oracle 12c for Pro Newbies: Plug and Play (Part 1)


(In continuation to Oracle 12c for Pro Newbies: Pluggable Database Architecture)    


INTRODUCTION:

Recall the architecture and basic components of Pluggable databases. Now, it is time that we create a pluggable database and play with it. Before we move to the technicalities of creating a PDB, let’s recapitulate the things we learnt earlier in a nutshell. A CDB is the root container identified by CDB$ROOT and allows for the plugging and unplugging of PDBs. There is a seed container called PDB$SEED which can serve as a base model for the creation of the PDBs within a CDB. The architecture is built upon the concept of multitenancy

Before we plan to create a pluggable database (PDB), we need to create a CDB (container database). This can be generally done in two ways:

1) Using the DBCA (Oracle prefers using this approach as it is more an automated one

2) Using the CREATE DATABASE statement (DBAs use this as it allows more control)

Using the DBCA

 

Using the DBCA in GUI mode is quite easy and interactive. However, here we’ll create a container database using DBCA in the “silent” mode. We can use dbca –help command to view all the options in this mode.
[oracle@12ctst ~]$ dbca -help
dbca [-silent | -progressOnly] {<command> <options> } | { [<command> [options] ] -responseFile <response file > } [-continueOnNonFatalErrors <true | false>]

<command> : -createDatabase | -configureDatabase | -createTemplateFromDB | -createCloneTemplate | -generateScripts | -deleteDatabase | -createPluggableDatabase | -unplugDatabase | -deletePluggableDatabase | -configurePluggableDatabase
Enter "dbca -<command> -help" for more option
Since we are creating a new database here, dbca –createDatabase –help will provide more options.
Here is the command for a simple container database creation without taking into account various other aspects of the database and without creating any pluggable databases:
dbca -silent -createDatabase -templateName /u01/app/oracle/product/12.1.0.1/db_1/assistants/dbca/templates/General_Purpose.dbc -gdbname cdb12ct.motleydata.com -sid cdb12ct -createAsContainerDatabase true -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL
Let’s first take a look inside the CDB and then create another one (in the next part of the blog) using CREATE DATABASE statement when we need to plug out the PDB from one CDB and plug in to other.

Inside the CDB
In a CDB when we query a view, we see one column with the name CON_ID. Understanding its significance will help us make an analysis of the output from querying various views following the table below:

pluggable1

 

pluggable2
pluggable3
Thus, we have two containers one CDB$ROOT in READ WRITE MODE which is the root to which a PDB can be attached and another PDB$SEED in READ ONLY MODE which can serve as a model database for PDBs to be created.
We have the following options while creating a PDB:
i. Use the SEED database to create a PDB
ii. Using a non-CDB (should be 12c or higher)
iii. Using an existing PDB
iv. Plug an unplugged PDB into a CDB
In this article, we will cover point (i) along with some important concepts while working with the pluggable databases. Point (ii) & (iii) will be discussed in the coming blogs while upgrading from an existing 11g database to 12c non-CDB and then to a 12c PDB. Point (iv) will be taken along with the CREATE DATABASE statement as mentioned earlier.
pluggable4.JPG

Using the SEED database to create a PDB

Connect to the root container as SYS and use the CREATE PLUGGABLE DATABASE command to copy the SEED database’s datafiles and create a PDB. We can also create the same using DBCA. For all clauses of CREATE PLUGGABLE DATABASE command, please refer to the Oracle Database Administrator’s Guide 12c Release 1.
Some of the related clauses of CREATE PLUGGABLE DATABASE command are:
STORAGE: Specifies the storage limit of the PDB. Specify MAXSIZE UNLIMITED for no limit on the maximum size of the database or omit the clause.
DEFAULT TABLESPACE: If you omit this clause, the SYSTEM tablespace will be the default tablespace for non-SYSTEM users for those no different tablespace is specified.
PATH_PREFIX: This clause adds an absolute path that is used as a prefix for all relative directory object paths associated with the PDB. You can omit this clause if you plan to use absolute paths or OMF.
FILE_NAME_CONVERT: This clause specifies the target location of the file based on the names of the source files.
CREATE_FILE_DEST: To specify the OMF default location for PDB’s files.
TEMPFILE REUSE: Use this clause if you want an already existing temp file to be formatted and used for the PDB. If you omit this clause, a new tempfile is created for the PDB.
USER_TABLESPACES: This clause is used to include all or a list of tablespaces or exclude some of the tablespaces in the PDB creation. You can omit this clause if you want to include all the tablespaces from the SEED.
logging_clause: Specifies the logging attribute of the PDB. It can be omitted or either set to LOGGING or NOLOGGING.
ADMIN USER: This creates a local user for the administrative tasks with the PDB_DBA role assigned to the user.
Now we are having enough information to run a CREATE PLUGGABLE DATABASE statement. Let’s now create a basic PDB using this knowledge.
[oracle@12ctst ~]$ echo $ORACLE_SID
cdb12ct

[oracle@12ctst ~]$ sqlplus / as sysdba

SQL> CREATE PLUGGABLE DATABASE pdb1t
ADMIN USER pdb1adm IDENTIFIED BY pdb1adm
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE USERS
CREATE_FILE_DEST='/u01/app/oracle/oradata/cdb12ct/';

Pluggable database created.

Let’s now get some details of the CDB and PDB that we have just created.

pluggable5
Our PDB is still in the MOUNTED state. As we open it we are connected to the CDB so I need to alter my session to point to the PDB.
SQL> alter session set container=pdb1t;
Session altered.

SQL> alter database open;
Database altered.

Querying the “v$containers” again now gives the output for only the present container.

pluggable2

Once we are inside a PDB, it behaves the same as the non-CDB databases behaved. The DBA having the responsibility to manage a PDB can only check what lies inside that particular PDB, thus providing a separate database altogether.
To view information about the PDBs, we can use the views CDB_PDBS or DBA_PDBS which provide almost the same info when the query is run by a “common user” whose current container is the root.

pluggable8
v$datafile and v$tablespace

SELECT TS.CON_ID CON_ID, TS.TS# TS_NUM, TS.NAME TABLESPACE, DF.NAME DATAFILE FROM V$DATAFILE DF JOIN V$TABLESPACE TS ON (TS.TS#=DF.TS#) AND (TS.CON_ID=DF.CON_ID) ORDER BY CON_ID, TS_NUM;

pluggable9

Let’s turn to some cdb_* views. As per Oracle Database Reference for 12c, “For every DBA_* view, a CDB_* view is defined.” A user attribute called CONTAINER_DATA comes into effect and decides what is to be shown when a user connected to CDB$ROOT queries on the database through CDB_* views. This attribute can be altered for a common user via the ALTER USER command. The container_data_clause of the ALTER USER SQL statement is shown in the picture below:


pluggable10.JPG

Image Source: https://docs.oracle.com/database/121/SQLRF/statements_4003.htm#SQLRF01103

alter user <user_name> set container_data=<value> container=current;

The possible values for the container_data are ALL (to specify that all current and future containers will be accessible to the user), DEFAULT (to specify the default behavior which in case of a default CONTAINER_DATA attribute would make the root and the CDB as a whole accessible to the user while in case of an object-specific CONTAINER_DATA attribute, the database will use the user’s default CONTAINER_DATA attribute), <container_name> to specify one or more containers accessible to the user.

Now when I was defining the cdb_* views here, I used a jargon “common user”.  Let us throw some light on this term. In Oracle 12c, Common Users are the users which have access to the entire CDB whereas Local Users are specific to a PDB. Leaving aside the Oracle supplied common user accounts such as SYS, SYSTEM, etc., common users username should begin with c## or C## e.g: c##pdbadmin. Local users being local to the PDBs cannot be created inside the root container.

Let us create a common user and then try to explain the theory behind CONTAINER_DATA attribute.

SQL> CREATE USER c##superdba

IDENTIFIED BY password

CONTAINER=ALL;

User created.

 

SQL> GRANT SET CONTAINER, CREATE SESSION TO C##SUPERDBA CONTAINER=ALL;

Grant succeeded.

 

SQL> GRANT SELECT_CATALOG_ROLE TO C##SUPERDBA CONTAINER=ALL;

Grant succeeded.

 

SQL> SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='C##SUPERDBA';

pluggable11

SQL> SELECT DISTINCT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='C##SUPERDBA'; 

pluggable13

Let’s alter the CONTAINER_DATA clause for the user C##SUPERDBA. If we put CONTAINER=ALL, we’ll face ORA-65048 & 56 as below which seem to be self-explaining.
SQL> ALTER USER C##SUPERDBA SET CONTAINER_DATA=ALL CONTAINER=ALL;

ALTER USER C##SUPERDBA SET CONTAINER_DATA=ALL CONTAINER=ALL
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in pluggable database PDB1T
ORA-65056: CONTAINER_DATA attribute is not used in a pluggable database.

SQL> ALTER USER C##SUPERDBA SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
User altered.


Querying the CDB_CONTAINER_DATA view

SQL> set lines 200
SQL> set pages 500
SQL> col USERNAME format a25
SQL> col DEFAULT_ATTR format a10
SQL> col OWNER format a15
SQL> col OBJECT_NAME format a15
SQL> col ALL_CONTAINERS format a10
SQL> col CONTAINER_NAME format a15

SQL> select CON_ID, USERNAME, DEFAULT_ATTR, OWNER, OBJECT_NAME, ALL_CONTAINERS, CONTAINER_NAME from CDB_CONTAINER_DATA where USERNAME=‘C##SUPERDBA’;
pluggable14
Now I have created another PDB “test” with con_id 4 using the seed container to verify the effect of CONTAINER_DATA clause.

SQL> CONN C##SUPERDBA/password
Connected.

Querying the view cdb_tablespaces will return the rows from all the containers for user C##SUPERDBA because CONTAINER_DATA=ALL.

SQL> select distinct CON_ID from cdb_tablespaces ORDER BY CON_ID;
pluggable15.JPG

Let’s now change the CONTAINER_DATA attribute for the user to specified containers. Keep in mind that while changing this attribute to particular container names, the current container should always be included else the command will throw ORA- 65057 as below:

SQL> ALTER USER C##SUPERDBA SET CONTAINER_DATA=(PDB1T) CONTAINER=CURRENT;
ALTER USER C##SUPERDBA SET CONTAINER_DATA=(PDB1T) CONTAINER=CURRENT
*
ERROR at line 1:
ORA-65057: CONTAINER_DATA attribute must always include the current container

SQL> conn / as sysdba
Connected.
SQL> ALTER USER C##SUPERDBA SET CONTAINER_DATA=(CDB$ROOT,PDB1T) CONTAINER=CURRENT;

User altered.


Querying the CDB_CONTAINER_DATA view

CON_ID USERNAME DEFAULT_ATTR OWNER OBJECT_NAME ALL_CONTAINERS CONTAINER_NAME
1 C##SUPERDBA Y N CDB$ROOT
1 C##SUPERDBA Y N PDB1T



SQL> CONN C##SUPERDBA/password
Connected.

pluggable16.JPG

SQL> select distinct CON_ID from cdb_tablespaces ORDER BY CON_ID;

pluggable17.JP

Only two containers are shown in this case which validates the use of our CONTAINER_DATA attribute.

This brings us to the end of part 1 of this blog. Stay tuned for the next in the series.

References:

Oracle® Database Administrator's Guide 12c Release 1 (12.1)

Oracle® Database Reference 12c Release 1 (12.1)