Oracle 12c New Features

Online Oracle Training

LESSON 04

Exploring created instance (mycdb) and pluggable database (mypdb1)

First try to execute created profile (.profile).

Go to the oracle user home directory.

# su " oracle

Connect to sqlplus.

# cd ~

# . ./.profile

# sqlplus /nolog

SQL> connect / as sysdba

Check the status of the listener.

# lsnrctl status

:

:

Services Summary "

Service "mycdb" has 1 instance (s)

   Instance "mycdb", status READY, has 1 handler(s) for this service "

. . .

Service "mypdb1" has 1 instance (s)

   Instance "mycdb", status READY, has 1 handler(s) for this service "

. . .

Note on the instance "mycdb" and service name "mypdb1" and "mycdb." Users can access to the mypdb1 pluggable database using the service name "mypdb1."

For example:

SQL> connect system/mypass@mypdb1

Start the listener if not yet started.

# lsnrctl start

Check services

# lsnrctl services

Services Summary "

Service "mycdb" has 1 instance (s)

   Instance "mycdb", status READY, has 1 handler(s) for this service "

. . .

Service "mypdb1" has 1 instance (s)

   Instance "mycdb", status READY, has 1 handler(s) for this service "

Start the mycdb database.

SQL> STARTUP;

Check the database, instance and global names.

SQL> show parameter name

Also, you can use the following query.

SQL> SELECT instance_name, status, con_id FROM v$instance;

Note: The status of the mycdb instance is open. Also the CON_ID value is 0. Always the root container is zero.

Let us check the mycdb database is a multitenant container database. In the following query, the cdb value should be "YES."

SQL> SELECT name, cdb, con_id FROM v$database;

Note: The CDB column value is "YES."

Oracle creates services automatically for each container (cdb, seed and pluggable database including SYS$BACKGROUND and SYS$USERS). Let us check query them.

SQL> COL name FORMAT A30

SQL> SELECT name, con_id FROM v$services;

As you can see, you do not see the PDB$SEED service. Oracle does not allow write access to this container. It is a template to create users pluggable databases.

You can check your pluggable databases, using the V$PDBS view.

SQL> SELECT con_id, name, open_mode FROM v$pdbs;

Notice that the MYPDB pluggable database is in READ WRITE open mode and the seed PDB (PDB$SEED) is in READ ONLY open mode. The number always assigned to the seed since it is the second created containers.

Using SHOW CON_NAME and CON_ID

You can query the container name and id by using the show con_name and con_id commants.

Getting the container name:

SQL> show con_name

Assume that you are in the root container.

Getting the container ID:

SQL> show con_id

Assume that you are in the root container. The number 1 will be assigned to the root container since it is the first container created.

Query the pluggable DBID using the CDB_PDBS view.

SQL> SELECT pdb_name, dbid FROM cdb_pdbs;

Query the redo log files using the V$LOGFILE view. Assume that you are in the root container.

SQL> SELECT group#, member, con_id FROM v$logfile;

Notice that the container id is zero. That means the redo log files are in the root container.

Query the control files using the V$CONTROLFILE view. Assume that you are in the root container.

SQL> SELECT name, con_id FROM v$controlfile;

Notice that the con_id value is zero. It means that the control files are in the root container.

Query the data files using the CDB_DATA_FILES view. Assume that you are in the root container.

Notice that you have CDB_, DBA_, USER_, and ALL_ views. The CDB_ views were added to the Oracle12c using multitenant architecture.

SQL> SELECT tablespace_name, file_name, con_id FROM cdb_data_files ORDER BY 3;

Notice on the con_id values. When you use the CDB_ views, they have the con_id column.

Query the data files using the DBA_DATA_FILES view. Assume still you are connected to the root.

SQL> SELECT tablespace_name, file_name FROM dba_data_files;

Notice that you only see the root data files.

Query the temp file of the root container using the CDB_TEMP_FILES view.

SQL> SELECT tablespace_name, file_name FROM cdb_temp_files;

Notice that the TEMP tablespace on the root and pluggable databases are located on different location.

In the Oracle12c using multitenant architecture, there are two types of users (the common users and the local users). The common users are users that are in all the containers.  For example, the SYSTEM and SYS users are on the root and all other pluggable databases. The local users only are on one container. Also, a common user password can be changed on a container.

To display the local users in the root using the CDB_USERS view.

SQL> SELECT username, con_id FROM cdb_users WHERE common = "NO";

Notice that we do not have any local users in the root container (CON_ID=1). The reason is that it is impossible to create a local user in the root container.

On the Oracle12c - multitenant architecture, there are two types of roles: The common roles and the local roles. The common roles are on all the containers. You can change a role within a container.

To query the common roles and privileges of the CDB, you should use the CDB_ROLES view.

SQL> SELECT role, common, con_id FROM cdb_roles ORDER by 3;

Notice that the CONNECT, RESOURCE, etc roles are on all the containers. Also you will not see any local role in the root since it is impossible to create a local role in the root.

A role can be created either commonly or locally.

In the following query, you will see the SYSTEM user was locally and commonly was granted the DBA and AQ_ADMINISTRATOR_ROLE role.

SQL> SELECT grantee, granted_role, con_id, common FROM cdb_role_privs

 2       WHERE grantee = "SYSTEM";