Performing Basic Tasks in Oracle Multitenant

Performing Basic Tasks in Oracle Multitenant

Overview

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:
• 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.

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
The environment used in the development of this tutorial is as follows:

• 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

 

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.

1) .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

2.)  Connect to the root by using OS authentication. 

connect / as sysdba
show con_name
show con_id


3) Display the list of available services for the root and the PDBs. 

select name, con_id from v$active_services order by 1;



4) 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


• 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');


2) . 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 theDBMS_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;

3). The open mode of the newly created PDB is still MOUNTED. You will learn how to open the PDB in the next section. The creation of the PDB created a new service.

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.

 

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;


2) .Open all PDBs at once.

alter pluggable database all open;

3) . 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;





When a CDB instance starts, you discover that its PDBs do not automatically open. In a development environment, it is convenient to ensure that all PDBs open with the instance.

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;



2) .  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;

The PDB was renamed.

3)  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.


List the root's tablespaces, data files, and 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 a permanent tablespace in the root.

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 a temporary tablespace in the root.

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;

Create a tablespace in a PDB.

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 a temporary tablespace in the PDB.

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







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.

• 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.

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

The user is recognized in each PDB but cannot connect because the CREATE SESSION privilege was not yet granted.

3) .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';

 4) . Connect as the local HR user in each PDB.

connect hr/oracle@localhost:1521/pdb2

connect hr/oracle@localhost:1521/pdb3_bis

The user is recognized in the PDB where he was created (pdb3_bis) but not in another PDB (pdb2) because he is local only to pdb3_bis.

 




Managing Common and Local Roles




Each container in a CDB holds common and local roles.

• 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.

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';







The role is created in all PDBs except the seed PDB (con_id 2).

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;

You should see an error message after executing this statement because you cannot create a common role inside a PDB.

 

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.

• 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.

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;

/




************************************************************************




Create a Pluggable Database (PDB) Manually

To create a new pluggable database from the seed database, all we have to do is tell Oracle where the file should be placed. We can do this using one of two methods. The first method uses theFILE_NAME_CONVERT clause in the CREATE PLUGGABLE DATABASE statement.

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');

Alternatively, we can specify the PDB_FILE_NAME_CONVERT initialization parameter before calling the command without using the FILE_NAME_CONVERT clause.

CONN / AS SYSDBA

ALTER SESSION SET PDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb3/';

CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1;

Every time there is a need to convert file locations, either of these two methods will work. For the remainder of the article I will stick to using the FILE_NAME_CONVERT method to cut down on the variations I have to display.

We can see the PDBs are present by querying the DBA_PDBS and V$PDBS views.

COLUMN pdb_name FORMAT A20

SELECT pdb_name, status
FROM   dba_pdbs
ORDER BY pdb_name;

PDB_NAME      STATUS
-------------------- -------------
PDB$SEED      NORMAL
PDB1       NORMAL
PDB2       NEW
PDB3       NEW

SQL>

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB1          MOUNTED
PDB2          MOUNTED
PDB3          MOUNTED

SQL>

The PDBs are created with the status of 'NEW'. They must be opened in READ WRITE mode at least once for the integration of the PDB into the CDB to be complete.

ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;

SELECT pdb_name, status
FROM   dba_pdbs
ORDER BY pdb_name;

PDB_NAME      STATUS
-------------------- -------------
PDB$SEED      NORMAL
PDB1       NORMAL
PDB2       NORMAL
PDB3       NORMAL

SQL>

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB1          MOUNTED
PDB2          READ WRITE
PDB3          READ WRITE

SQL>

Unplug a Pluggable Database (PDB) Manually

Before attempting to unplug a PDB, you must make sure it is closed. To unplug the database use the ALTER PLUGGABLE DATABASE command with the UNPLUG INTO clause to specify the location of the XML metadata file.

ALTER PLUGGABLE DATABASE pdb2 CLOSE;
ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml';

The pluggable database is still present, but you shouldn't open it until the metadata file and all the datafiles are copied somewhere safe.

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB1          MOUNTED
PDB2          MOUNTED
PDB3          READ WRITE

SQL>

You can delete the PDB, choosing to keep the files on the file system.

DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB1          MOUNTED
PDB3          READ WRITE

SQL>

Plugin a Pluggable Database (PDB) Manually

Plugging in a PDB into the CDB is similar to creating a new PDB. First check the PBD is compatible with the CDB by calling the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function, passing in the XML metadata file and the name of the PDB you want to create using it.

SET SERVEROUTPUT ON
DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml',
                pdb_name       => 'pdb2');

  IF l_result THEN
    DBMS_OUTPUT.PUT_LINE('compatible');
  ELSE
    DBMS_OUTPUT.PUT_LINE('incompatible');
  END IF;
END;
/
compatible

PL/SQL procedure successfully completed.

SQL>

If the PDB is compatible, create a new PDB using it as the source. If we were creating it with a new name we might do something like this.

CREATE PLUGGABLE DATABASE pdb5 USING '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml'
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2/','/u01/app/oracle/oradata/cdb1/pdb5/');

Instead, we want to plug the database back into the same container, so we don't need to copy the files or recreate the temp file, so we can do the following.

CREATE PLUGGABLE DATABASE pdb2 USING '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml'
  NOCOPY
  TEMPFILE REUSE;

ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB1          MOUNTED
PDB2          READ WRITE
PDB3          READ WRITE

SQL>

Clone a Pluggable Database (PDB) Manually

Cloning an existing local PDB is similar to creating a new PDB from the seed PDB, except now we are using non-seed PDB as the source, which we have to identify using the FROM clause. Make sure the source PDB is open in READ ONLY mode.

ALTER PLUGGABLE DATABASE pdb3 CLOSE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ ONLY;

CREATE PLUGGABLE DATABASE pdb4 FROM pdb3
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb3/','/u01/app/oracle/oradata/cdb1/pdb4/');

ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;

-- Switch the source PDB back to read/write
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;

The cloning syntax also allows for cloning from remote databases using a database link in the local CBD. There are a few restriction associated with this functionality.

• The database link can point directly to the remote PDB or to a common user in the remote CBD that owns the remote PDB.
• If it points to a common user in the remote CBD that owns the remote PDB, that user must have the CREATE PLUGGABLE DATABASE system privilege.
• The source and target CDBs must have the same endians.
• The source and target CDBs must have the same options installed.
• The source and target CDBs must have the same character set and national character set.

Assuming the remote PDB was in READ ONLY mode, the following command should perform the required operation.

CREATE PLUGGABLE DATABASE pdb5 FROM remote_pdb5@remotecdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/remote_pdb5/','/u01/app/oracle/oradata/cdb1/pdb5/');

ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;

This functionality does not work properly in the 12.1.0.1 release of the database, but it should be fixed in the first patchset.

Delete a Pluggable Database (PDB) Manually

When dropping a pluggable database, you must decide whether to keep or drop the associated datafiles. The PDBs must be closed before being dropped.

ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

ALTER PLUGGABLE DATABASE pdb4 CLOSE;
DROP PLUGGABLE DATABASE pdb4 INCLUDING DATAFILES;

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB1          MOUNTED

SQL>







**************************************************************************



CREATE_CDBVIEWS

cdbview.create_cdbviews(chk_upgrade IN BOOLEAN); True indicates the call is part of an upgrade and the views are not recreated. False indicates the call is not part of an upgrade and all CDB views are (re)created.

exec cdbview.create_cdbviews(FALSE);

*****************************************************************************

Thank You

*****************************************************************************