Performing Basic Tasks in Oracle Multitenant
Overview
- Connect to a CDB and to a PDB.
- Create a PDB from the seed PDB.
- Manage CDBs and PDBs.
- Start a CDB, understand the different open modes of PDBs, and shut down a CDB.
- Open and close PDBs.
- Change the name of a PDB.
- Manage the storage in a CDB and its PDBs.
- Manage permanent tablespaces.
- Manage temporary tablespaces.
- Manage the security in PDBs.
- Create common and local users.
- Create common and local roles.
- Grant common and local privileges.
- Drop PDBs.
Purpose
This tutorial covers the steps to perform basic tasks on container databases (CDBs) and pluggable databases (PDBs).
Time to Complete
Approximately 45 minutes
Introduction
This tutorial shows you how to:
Note: For readability, formatting was applied to some columns shown in the output.
Prerequisites
Before starting this tutorial, you should:
- Install Oracle Database 12c
- Create one CDB and one PDB
- ORACLE_HOME: /u01/app/oracle/product/12.1.0
- TNS Listener port: 1521
- Container databases:
- SID: cdb1
- SID: cdb2
- Pluggable databases (in cdb1):
- pdb1
- pdb2
The environment used in the development of this tutorial is as follows:
Connecting to the CDB Root or to a PDB
Creating a CDB creates a service whose name is the CDB name. As
a side effect of creating a PDB in the CDB, a service is created
inside it with a property that identifies it as the initial
current container. The service is also started as a side effect
of creating the PDB. The service has the same name as the PDB.
Although its metadata is recorded inside the PDB, the invariant
is maintained so that a service name is unique within the entire
CDB.
Use the Easy Connect syntax to connect to the root unless a net service name is configured in the tnsnames for the root service.
. oraenv
[enter cdb1 at the prompt]
sqlplus sys/oracle@localhost:1521/cdb1 as sysdba
show con_name
show con_id

Connect to the root by using OS authentication.
connect / as sysdba
show con_name
show con_id

Display the list of available services for the root and the
PDBs.
select name, con_id from v$active_services order by 1;

Use the Easy Connect syntax to connect to the PDB unless a
net service name is configured in the tnsnames for the PDB
service.
connect sys/oracle@localhost:1521/pdb1 as sysdba
show con_name
show con_id
exit

Creating a PDB from the Seed PDB
In this section, you create a PDB from the seed PDB. Each CDB
has a template PDB whose name is PDB$Seed
.
Creating the OS Directory for the
New Data Files and Temp Files of the PDB
Before starting the PDB creation, create a destination directory for the data files.
mkdir
/u01/app/oracle/oradata/cdb1/pdb3
Creating the PDB
Create a PDB from the seed PDB.
sqlplus / as sysdba
create pluggable database
pdb3
admin user odb3_admin identified by oracle
roles = (DBA)
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed','/u01/app/oracle/oradata/cdb1/pdb3');
Verify the status, open mode, and service names of
the PDBs in the CDB. If the status for a PDB shows
NEEDS SYNC, you can connect to the PDB and run the DBMS_PDB.SYNC_PDB
procedure to change the status to NORMAL.
select pdb_name, status from cdb_pdbs;
select name, open_mode from v$pdbs;
select name, con_id from v$active_services order by 1;
List the PDB data files.
select name from v$datafile where con_id=5;

Managing the CDB and the PDBs
In this section, you start the CDB and shut it down. You
also open and close the PDBs.
Managing the CDB
Ensure that you are connected to the
root as SYSDBA.
Shut down the CDB.
show con_name
shutdown immediate

This operation first closes all PDBs, then dismounts the control files, and finally shuts down the instance.
Start the CDB. This operation requires the SYSDBA or
SYSBACKUP privilege.
startup
This operation first starts the instance, then mounts the control files, and finally opens only the root container.
Verify the open mode of the PDBs. This operation first starts the instance, then mounts the control files, and finally opens only the root container.
select name, open_mode from v$pdbs;

Notice that PDB1 is opened automatically because of a database trigger that was previously created in this environment. Later in this tutorial, you will learn how to create a trigger to open all PDBs after the CDB is started.
Managing the PDBs
Open a PDB.
alter pluggable database pdb2
open;
select name, open_mode from
v$pdbs;

Open all PDBs at once.
alter pluggable database all open;
Verify the open mode of the PDBs.
select name, open_mode from v$pdbs;

Close a PDB.
alter pluggable database pdb1
close immediate;
select name, open_mode from v$pdbs;
Close all PDBs at once.
alter pluggable database all
close immediate;
select name, open_mode from
v$pdbs;

Perform the following actions:
a. Create a trigger to open all PDBs
after CDB startup.
b. Shut down and start the CDB to verify
that the trigger automatically opens all PDBs.
create or replace trigger
Sys.After_Startup after startup on database
begin
execute immediate 'alter pluggable
database all open';
end After_Startup;
/
shutdown immediate
startup
select name, open_mode from
v$pdbs;

Renaming a PDB
Open the PDB in restricted mode.
alter pluggable database pdb3 close immediate;
alter pluggable database pdb3 open restricted;
select name, restricted from
v$pdbs;

Rename the PDB. You must be connected to
the PDB to rename it.
alter pluggable database pdb3 rename global_name to pdb3_bis;
Note: You should
receive an error message when you execute this
statement because you are not connected to the pluggable database that is being renamed.
connect sys/oracle@localhost:1521/pdb3 as sysdba
alter pluggable database pdb3 rename global_name to pdb3_bis;

Close and open the PDB.
alter pluggable database close immediate;
alter pluggable database open;
select name, open_mode from
v$pdbs;

Managing Storage in a CDB and Its PDBs
Each container in a CDB stores data in its own data files and handles temporary data in its own temp files.
connect / as sysdba
select tablespace_name, con_id from cdb_tablespaces where con_id=1;
select file_name, con_id from cdb_data_files where con_id=1;
select file_name, con_id from cdb_temp_files where con_id=1;

create tablespace cdata datafile '/u01/app/oracle/oradata/cdb1/cdata01.dbf' SIZE 10M;
select tablespace_name, con_id from cdb_tablespaces order by con_id;
select file_name, con_id from
cdb_data_files order by con_id;

create temporary tablespace temp_root tempfile '/u01/app/oracle/oradata/cdb1/temproot01.dbf' SIZE 10M;
select tablespace_name, con_id from cdb_tablespaces where contents='TEMPORARY' and con_id=1;
select file_name, con_id from
cdb_temp_files where con_id=1;

connect system/oracle@localhost:1521/pdb3_bis
create tablespace ldata datafile '/u01/app/oracle/oradata/cdb1/pdb3/ldata01.dbf' SIZE 10M;
select tablespace_name, con_id from cdb_tablespaces order by con_id;
select file_name, con_id from cdb_data_files order by con_id;
select file_name from dba_data_files;

When you are connected to a PDB, the CDB_xxx or DBA_xxx views show the same information.
create temporary tablespace temp_pdb3 tempfile '/u01/app/oracle/oradata/cdb1/pdb3/temppdb301.dbf' SIZE 10M;
select tablespace_name, con_id from cdb_tablespaces where contents='TEMPORARY';
select file_name from dba_temp_files;

Managing Security in PDBs
- Common users are created from the root and are
automatically replicated in each PDB except the seed
PDB. Common users can connect to any PDB. The name
assigned to a common user must start with c##.
- Local users are created in a PDB they need to access.
Local users can only connect to the PDB where they are
created. They are not visible to the other PDBs of the
same CDB.
- Common roles are created from the root and are automatically replicated in each PDB except the seed PDB. The name assigned to a common role must start with c##.
- Common roles can be granted commonly: The grant
operation is replicated in each PDB except the seed
PDB.
- Common roles can be granted locally: The grant operation is performed in the container where the operation takes place.
- Local roles are created in a PDB they need to access. Local roles can be granted locally only in the PDB where they are created. They are not visible to the other PDBs of the same CDB.
- Common privileges are automatically granted to the common grantee (user or role) in each PDB except the seed PDB.
- Local privileges are granted to a grantee (user or role) in a specific PDB.
Managing Common and Local Users
Each container in a CDB holds common and local users. Any user, common or local, can only exercise the granted privileges inside the specific container to which it is connected.
Create a common user while you are
connected to the root.
connect / as sysdba
create user c##1 identified
by oracle container=all;
select username, common,
con_id from cdb_users where username like 'C##%';

The user is not created in the seed PDB (con_id 2).
Connect as a common user in a PDB.
connect c##1/oracle@localhost:1521/pdb2
connect c##1/oracle@localhost:1521/pdb3_bis
CREATE SESSION
privilege was not yet granted.Connect as a DBA in a PDB to create a local user.
connect
system/oracle@localhost:1521/pdb3_bis
create user hr identified
by oracle;
select username, common, con_id from cdb_users where username ='HR';

Connect as the local HR user in each
PDB.
connect hr/oracle@localhost:1521/pdb2
connect hr/oracle@localhost:1521/pdb3_bis
Managing Common and Local Roles
Each container in a CDB holds common and local roles.
Create a common role.
connect / as sysdba
create role c##r1 container=all;
select role, common, con_id from cdb_roles where role='C##R1';
Create a local role in a PDB. (When you
are connected to a PDB, you cannot create a common
role.)
connect system/oracle@localhost:1521/pdb3_bis
create role hr_manager;
select role, common, con_id from cdb_roles where role='HR_MANAGER';
create role c##r2
container=all;
Managing Common and Local
Privileges
You can grant common and local privileges to common and
local users and roles. The privileges become common or
local based on how they are granted. They are common when
they are granted with the CONTAINER=ALL
clause.
Grant CREATE SESSION as a common
privilege to a common user.
connect / as sysdba
grant create session to c##1 container=all;
select grantee, privilege,
common, con_id from cdb_sys_privs
where privilege='CREATE SESSION' and grantee='C##1';
connect c##1/oracle@localhost:1521/pdb2
select * from session_privs;
connect c##1/oracle@localhost:1521/pdb3_bis
select * from session_privs;
Granting a privilege as a common privilege to a local
user is not allowed. But you can grant the privilege
locally to a local user: CREATE SESSION
becomes a local privilege that allows the local user
to exercise it only in the PDB and not in another PDB.
Grant the privilege locally to a local
user.
connect system/oracle@localhost:1521/pdb3_bis
grant create session to hr
container=all;
[you should see an error message after executing this
statement. why?]
grant create session to hr;
select grantee, privilege,
common, con_id from cdb_sys_privs
where privilege='CREATE SESSION' and grantee='HR';
connect
hr/oracle@localhost:1521/pdb2
[you should see an error message after executing this
statement. why?]
connect hr/oracle@localhost:1521/pdb3_bis
select * from session_privs;

Dropping PDBs
When you drop a PDB, you can specify to keep or delete the data
files. Keeping the data files is required when you unplug a PDB
and want to plug it into another CDB (or the same CDB). The data
files are reused when plugging in the PDB.
Close the PDBs.
connect / as sysdba
alter pluggable database all close immediate;
select name, open_mode from v$pdbs;

Drop the PDBs, including their data files.
drop pluggable database pdb3_bis
including datafiles;
select name from v$pdbs;

Resetting Your Environment
Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.
Drop the common user and role that you
created.
drop user c##1;
drop role c##r1;
Drop the tablespaces that you created in the CDB root.
drop tablespace cdata including contents;
drop tablespace temp_root including contents;
Open pdb1 and replace the database trigger with a trigger that opens only pdb1 at CDB startup.
alter pluggable database pdb1 open;
create or replace trigger
Sys.After_Startup after startup on database
begin
execute immediate 'alter pluggable database pdb1
open';
end After_Startup;
/
Summary
- To learn more about pluggable databases, refer to
additional OBEs in the Oracle Learning Library.
In this tutorial, you learned how to manage basic tasks on container and pluggable databases, including creating PDBs from seed PDBs; managing tablespaces and security; and creating common and local users, roles, and privileges.
Resources
Credits
Curriculum Developers: Dominique Jeunot and Jean-François Verrier
To navigate this Oracle by Example tutorial, note the following:
- Hide Header Buttons:
- Click the title to hide the buttons in the header. To show the buttons again, click the title again.
- Topic List:
- Click a topic to navigate to that section.
- Expand All Topics:
- Click the button to show or hide the details for the sections. By default, all topics are collapsed.
- Hide All Images:
- Click the button to show or hide the screenshots. By default, all images are displayed.
- Print:
- Click the button to print the content. The content that is currently displayed or hidden is printed.
To navigate to a particular section in this tutorial, select the topic from the list.