This topic describes how to create and manage a user in Multitenant database.

There are two types of users in Multitenant Database:

• Common User: User exists in all containers (root container and all PDBS).
• Local User: User exists on particular PDB only.

Similarly, Two types of role are available in Multitenant Database:

• Common Role: Role exists in all containers (root container and all PDBS).
• Local Role: Role exists on particular PDB only.

Note::: Common or Local User and Role are same way to create to manager.

Create Common User:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 SALESPDB                       MOUNTED
         5 PDBTEST                        READ WRITE NO
         7 PDBNODATA                      MOUNTED

SQL> show parameter common

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix                   string      C##

Note::: Grant privileges to common user using container=all for root as well as PDBs. If don’t use container=all then it will applicable for container root only.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create user c##usercon identified by oracle;

User created.

SQL> grant create session, connect, resource to c##usercon;

Grant succeeded.

SQL> conn c##usercon/oracle;
Connected.

SQL> show user
USER is "C##USERCON"

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> conn / as sysdba
Connected.

SQL> conn c##usercon/oracle@pdbtest
ERROR:
ORA-01045: user C##USERCON lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

SQL> grant connect, resource to c##usercon container=all;

Grant succeeded.

SQL> conn c##usercon/oracle@pdbtest
Connected.

SQL> show user
USER is "C##USERCON"

SQL> show con_name

CON_NAME
------------------------------
PDBTEST

Common user will be created in all PDBs automatically:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 SALESPDB                       MOUNTED
         5 PDBTEST                        READ WRITE NO
         7 PDBNODATA                      MOUNTED

SQL> col USERNAME for A20
SQL> set linesize 100
SQL> SELECT username, b.name, user_id, a.con_id
FROM cdb_users a, v$containers b
WHERE a.con_id = b.con_id AND username = 'C##USERCON';  

USERNAME             NAME                              USER_ID     CON_ID
-------------------- ------------------------------ ---------- ----------
C##USERCON           PDBTEST                               105          5
C##USERCON           CDB$ROOT                              102          1

SQL> alter pluggable database pdbnodata open;

Pluggable database altered.

SQL> SELECT username, b.name, user_id, a.con_id
FROM cdb_users a, v$containers b
WHERE a.con_id = b.con_id AND username = 'C##USERCON';  

USERNAME             NAME                              USER_ID     CON_ID
-------------------- ------------------------------ ---------- ----------
C##USERCON           PDBTEST                               105          5
C##USERCON           CDB$ROOT                              102          1
C##USERCON           PDBNODATA                             105          7

Common user can be created only from root container:

SQL> alter session set container=PDBTEST;

Session altered.

SQL> show user
USER is "SYS"

SQL> create user c##usercon01 identified by oracle;
create user c##usercon01 identified by oracle
            *
ERROR at line 1:
ORA-65094: invalid local user or role name

Create Local User:

SQL> alter session set container=PDBTEST;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDBTEST

SQL> create user user_pdb identified by oracle;

User created.

SQL> grant create session, connect, resource to user_pdb;

Grant succeeded.

SQL> conn user_pdb/oracle@pdbtest
Connected.

SQL> show con_name

CON_NAME
------------------------------
PDBTEST

SQL> show user
USER is "USER_PDB"

Local user can be created from PDB only but not from root container:

SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> create user user_pdb identified by oracle;
create user user_pdb identified by oracle
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Common Role:

Common role has to be created same way like Common user.

• Role Name should be starting with C##
• Creates from root container.
• Can be assigned to common user as well as local users any PDBs.
• Common Role name is unique for entire database (CDB + PDBs).

SQL> create role c##role_hr;

Role created.

SQL> grant create session to c##role_hr;

Grant succeeded.

SQL> grant c##role_hr to c##usercon;

Grant succeeded.

SQL> grant c##role_hr to user_pdb;
grant c##role_hr to user_pdb
                    *
ERROR at line 1:
ORA-01917: user or role 'USER_PDB' does not exist

SQL> alter session set container=pdbtest;

Session altered.

SQL> grant c##role_hr to user_pdb;

Grant succeeded.

Local Role:

• Role Name should not starting with C##.
• Creates from a PDB.
• Can be assigned to local user of the PDB only.
• Local Role name is unique for a PDB.

SQL> create role role_local;

Role created.

SQL> grant create session, select any table to role_local;

Grant succeeded.

SQL> grant role_local to user_pdb;

Grant succeeded.

SQL> grant role_local to c##usercon;

Grant succeeded.

Create an object from common user:

Can be created an object (like Table or View etc) with same name on CDB and PDBs from common user.

Table create on a CDB using common user:

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "C##USERCON"

SQL> Create table root_test (id number);

Table created.

Table create on a PDB using common user:

SQL> show con_name
CON_NAME
------------------------------
PDBNODATA

SQL> show user
USER is "C##USERCON"

SQL> create table C##USERCON.root_test (id number);

Table created.

Table create on a PDB using common user:

SQL> show con_name
CON_NAME
------------------------------
PDBTEST

SQL> show user
USER is "C##USERCON"

SQL> create table C##USERCON.root_test (id number);

Table created.

Table with same name on all PDBs and Container for a common user:

col OWNER for A20
col OBJECT_NAME for A20
set linesize 100

SQL> SELECT a.con_id, b.name, owner, object_name, object_id
FROM cdb_objects a, v$containers b
WHERE a.con_id = b.con_id AND a.object_name = 'ROOT_TEST';

    CON_ID NAME                           OWNER                OBJECT_NAME           OBJECT_ID
---------- ------------------------------ -------------------- -------------------- ----------
         7 PDBNODATA                      C##USERCON           ROOT_TEST                 91941
         5 PDBTEST                        C##USERCON           ROOT_TEST                 91823
         1 CDB$ROOT                       C##USERCON           ROOT_TEST                 92447

• Change the PDB State.
• Unplug the PDB.
• Set default tablespace.
• Set default temporary tablespace.
• Enable (disable) logging.
• Change status of datafile (online/offline).

In this topic, I will show how to Create and manage PDB:

• Create a PDB from SEED.
• Clone a PDB with No Data.
• Clone a PDB from existing local PDB.
• Clone a PDB from existing remote PDB.
• Clone a PDB from existing remote Non-CDB.
• Clone a PDB from non-CDB using DBMS_PDB Package.
• Plugging In an Unplugged PDB from one CDB to another CDB.
• Drop a PDB (Pluggable Database)

In Oracle OCM exam, You may need to create database using command line. Even in real life, You may no have the option to use the software for GUI. I will show how to create a CDB and PDB using SQL Command.

• Create required directories.
• Set Environment Variables.
• Create Password File.
• Create a pfile and spfile.
• Run Create Database Command.
• Data Dictionary views.
• Create a PDB Database.
• Recompile All Invalid Objects.

In this Topic, I will show how to install Oracle 12.1.0.2 Software only and Create database using DBCA by silent mode.

• Oracle Installation Prerequisites
• Install Oracle 12c Software in Silent Mode:
• Create Database Using DBCA with Silent Mode:

Oracle Installation Prerequisites:

Add following kernel parameters on /etc/sysctl.conf.

s.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

Run following command to verify and output will be like below:

[root@proddb ~]# /sbin/sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576

Add following kernel parameters on /etc/security/limits.conf.

oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768

Install oracle database related packages: If you have internet connection then you can install from public Yum.

yum install binutils -y
yum install compat-libcap1 -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install gcc -y
yum install gcc-c++ -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libXext -y
yum install libXext.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install make -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y

Create groups and user:

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
# Below group can be created if you want to separate the responsibility like backup team need to access the database backup. 
groupadd -g 54324 backupdba
groupadd -g 54325 dgdba
groupadd -g 54326 kmdba
# Below group can be created if you use GRID Infrastructure even for Stand alone DB
groupadd -g 54327 asmdba
groupadd -g 54328 asmoper
groupadd -g 54329 asmadmin

useradd -u 54321 -g oinstall -G dba,oper oracle

Create required directories:

mkdir -p /u01/app/oracle/product/12.1.0.2/dbhome_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Set secure Linux to permissive by editing the “/etc/selinux/config” file or use “setenforce Permissive” and reboot the server.

[root@proddb ~]# setenforce Permissive
[root@proddb ~]# cat /etc/selinux/config | grep SELINUX
# SELINUX= can take one of these three values:
SELINUX=permissive
# SELINUXTYPE= can take one of these two values:
SELINUXTYPE=targeted

Install Oracle 12c Software in Silent Mode:

Prepare the response file with required Input:

Copy the response file from Software unzipped location to any other location and change the file.