Oracle 12c Multitenant Database

Doyensys

DOYENSYS
Program Agenda

 Oracle Multitenant Database Architecture

 Overview of Multitenant : (CDB) & (PDB)
 Multitenant : Create and Configure a (CDB) & (PDB)
 Multitenant : Startup and Shutdown (CDB) & (PDB)
 Multitenant : Manage Users and Privileges
 Multitenant :Cloning pluggable database
 Multitenant : Backup and Recovery (CDB) & (PDB)
 Multitenant : Migrate a (Non-CDB) to a (PDB)
Oracle Multitenant Database Architecture
 Oracle Database 19c offers a new option called Oracle Multitenant that
enables to do just that. It offers simplified consolidation that requires no
changes to your applications.
 Main database contains oracle metadata of all tenant database.
 All tenant databases contain user data.
 Single instance would be shared by all tenant databases.
 Main database called as CDB(container database)
 Tenant database called as PDB(pluggable database)
 Shared SGA and PGA
 Single set of background processes
 Let's see how the benefits of Oracle Multitenant stack up against your current
database architecture
Oracle Multitenant Database Architecture
INCREASE SERVER UTILIZATION
Oracle Multitenant Database Architecture
MANAGE MANY DATABASES AS ONE
Oracle Multitenant Database Architecture
BACKUP MANY DATABASES AS ONE
Oracle Multitenant Database Architecture
MOVE DATABASES RAPIDLY
Program Agenda

 Oracle Multitenant Database Architecture

 Overview of Multitenant : (CDB) & (PDB)
 Multitenant : Create and Configure a (CDB) & (PDB)
 Multitenant : Startup and Shutdown (CDB) & (PDB)
 Multitenant : Manage Users and Privileges
 Multitenant :Cloning pluggable database
 Multitenant : Backup and Recovery (CDB) & (PDB)
 Multitenant : Migrate a (Non-CDB) to a (PDB)
Overview of Multitenant : (CDB) & (PDB)
Overview of Multitenant : (CDB) & (PDB)
Overview of Multitenant : (CDB) & (PDB)
Overview of Multitenant : (CDB) & (PDB)
Overview of Multitenant : (CDB) & (PDB)
Program Agenda

 Oracle Multitenant Database Architecture

 Overview of Multitenant : (CDB) & (PDB)
 Multitenant : Create and Configure a (CDB) & (PDB)
 Multitenant : Startup and Shutdown (CDB) & (PDB)
 Multitenant : Manage Users and Privileges
 Multitenant :Cloning pluggable database
 Multitenant : Backup and Recovery (CDB) & (PDB)
 Multitenant : Migrate a (Non-CDB) to a (PDB)
Create and Configure a (CDB) & (PDB) Databases

Oracle Universal Installer (OUI)

Create and Configure a (CDB) & (PDB) Databases

Advanced configuration options(OUI)

Create and Configure a (CDB) & (PDB) Databases

Database Configuration Assistant (DBCA)

Create and Configure a (CDB) & (PDB) Databases

Database Configuration Assistant (DBCA)

Create and Configure a (CDB) & (PDB) Databases

Manual Creation
Creating CDB using CREATE DATABASE

Þ The CREATE DATABASE command is almost the same

Þ ENABLE PLUGGABLE DATABASE clause must be used

Þ SEED FILE_NAME_CONVERT clause (only if not using OMF)

Þ DB_CREATE_FILE_DEST initialization parameter if using OMF

or
Þ PDB_FILE_NAME_CONVERT initialization parameter
Create and Configure a (CDB) & (PDB) Databases

CREATE DATABASE without OMF

SQL> CREATE DATABASE acdb

USER SYS IDENTIFIED BY ORACLE
USER SYSTEM IDENTIFIED BY ORACLE
….
ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT =
('/u01/app/oracle/oradata/acdb/', '/u01/app/oracle/oradata/pdbseed/') SYSTEM
DATAFILES SIZE 300M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED SYSAUX
DATAFILES SIZE 200M USER_DATA TABLESPACE usertbs DATAFILE
'/u01/app/oracle/oradata/pdbseed/usertbs01.dbf' SIZE 200M REUSE
AUTOEXTEND ON MAXSIZE UNLIMITED;

SQL> @?/rdbms/admin/catcdb.sql
Create and Configure a (CDB) & (PDB) Databases

CREATE DATABASE with OMF

SQL> CREATE DATABASE acdb

USER SYS IDENTIFIED BY ORCL
USER SYSTEM IDENTIFIED BY ORCL
EXTENT MANAGEMENT LOCAL
DEFAULT TABLESPACE users
DEFAULT TEMPORARY TABLESPACE temp
UNDO TABLESPACE undotbs1
ENABLE PLUGGABLE DATABASE
SEED
SYSTEM DATAFILES SIZE 300M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 200M;

SQL> @?/rdbms/admin/catcdb.sql*
Create and Configure a (CDB) & (PDB) Databases

Data Dictionary Changes

Þ CDB_*
–Dictionary views showing data from Root and all Pluggable databases
Þ DBA_*
−Dictionary views showing data from a container or from Pluggable databases
Þ ALL_*
–Dictionary views showing data accessible by a users

Þ USER_*
−Dictionary views showing data owned by a user

SELECT table_name FROM dict WHERE table_name like 'DBA%';

SELECT view_name FROM dba_views WHERE view_name like 'CDB%';
Program Agenda

 Oracle Multitenant Database Architecture

 Overview of Multitenant : (CDB) & (PDB)
 Multitenant : Create and Configure a (CDB) & (PDB)
 Multitenant : Startup and Shutdown (CDB) & (PDB)
 Multitenant : Manage Users and Privileges
 Multitenant :Cloning pluggable database
 Multitenant : Backup and Recovery (CDB) & (PDB)
 Multitenant : Migrate a (Non-CDB) to a (PDB)
Startup and Shutdown (CDB) & (PDB)

Container Database (CDB)

STARTUP [NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]

SHUTDOWN [IMMEDIATE | ABORT]

Pluggable Database (PDB)

STARTUP FORCE;
STARTUP OPEN READ WRITE [RESTRICT];
STARTUP OPEN READ ONLY [RESTRICT];
STARTUP UPGRADE;
SHUTDOWN [IMMEDIATE];
Startup and Shutdown (CDB) & (PDB)
ALTER PLUGGABLE DATABASE

ALTER PLUGGABLE DATABASE OPEN READ WRITE [RESTRICTED] [FORCE];

ALTER PLUGGABLE DATABASE OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE CLOSE [IMMEDIATE];

Some examples of open using Alter Command:

alter pluggable database zealpdb1 open;

alter pluggable database zealpdb1 open read only;
alter pluggable database zealpdb1 open read write;
alter pluggable database zealpdb1,zealpdb2 open immediate;
alter pluggable database all open read write;
alter pluggable database all except zealpdb1 open read write;
alter pluggable database all except zealpdb1 open read write force;
alter pluggable database all except zealpdb1 open resetlogs;
alter pluggable database zealpdb1 open restricted;
startup
Startup and Shutdown (CDB) & (PDB)
ALTER PLUGGABLE DATABASE

Some examples of close using Alter Command:

alter pluggable database zealpdb1 close;

alter pluggable database zealpdb1 close immediate;
alter pluggable database zealpdb1,zealpdb2 close immediate;
alter pluggable database all close immediate;
alter pluggable database all except zealpdb1 close immediate;
shutdown immediate

Pluggable Database (PDB) Automatic Startup

CREATE OR REPLACE TRIGGER open_pdbs

AFTER STARTUP ON DATABASE BEGIN EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL
OPEN';
END open_pdbs;
/
Startup and Shutdown (CDB) & (PDB)

Data Dictionary and Dynamic Performance Tables to check the status

Þ SELECT NAME, CDB, CON_ID FROM V$DATABASE;

Þ select instance_name, version, status, con_id from v$instance;

Þ COL PDB_NAME FOR A30

SELECT PDB_ID,PDB_NAME,STATUS,CON_ID FROM CDB_PDBS;

Þ select NAME,OPEN_MODE,CON_ID from v$pdbs;

Þ COL PDB_NAME FOR A30

select b.con_id,a.pdb_name,a.status,b.open_mode
from dba_pdbs a, v$pdbs b
where a.pdb_name= b.name
order by b.con_id;
Program Agenda

 Oracle Multitenant Database Architecture

 Overview of Multitenant : (CDB) & (PDB)
 Multitenant : Create and Configure a (CDB) & (PDB)
 Multitenant : Startup and Shutdown (CDB) & (PDB)
 Multitenant : Manage Users and Privileges
 Multitenant :Cloning pluggable database
 Multitenant : Backup and Recovery (CDB) & (PDB)
 Multitenant : Migrate a (Non-CDB) to a (PDB)
Manage Users and Privileges

• When connected to a multitenant database the management of users and

privileges is a little different to traditional Oracle environments. In multitenant
environments there are two types of user.
• Common User : The user is present in all containers (root and all PDBs).
• Local User : The user is only present in a specific PDB. The same
username can be present in multiple PDBs, but they are unrelated.
• Likewise, there are two types of roles.
• Common Role : The role is present in all containers (root and all PDBs).
• Local Role : The role is only present in a specific PDB. The same role name
can be used in multiple PDBs, but they are unrelated.
Manage Users and Privileges
Create Common Users

CONN / AS SYSDBA

-- Create the common user using the CONTAINER clause.

CREATE USER c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;

GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;
-- Create the common user using the default CONTAINER setting.

CREATE USER c##test_user2 IDENTIFIED BY password1;

GRANT CREATE SESSION TO c##test_user2;
Manage Users and Privileges
Create Local Users

CONN / AS SYSDBA

-- Switch container while connected to a common user.

ALTER SESSION SET CONTAINER = pdb1;

-- Create the local user using the CONTAINER clause.

CREATE USER test_user3 IDENTIFIED BY password1 CONTAINER=CURRENT;

GRANT CREATE SESSION TO test_user3 CONTAINER=CURRENT;

-- Connect to a privileged user in the PDB.

CONN system/password@pdb1

-- Create the local user using the default CONTAINER setting.

CREATE USER test_user4 IDENTIFIED BY password1;
GRANT CREATE SESSION TO test_user4;
Manage Users and Privileges
Create Common Roles

CONN / AS SYSDBA
-- Create the common role.

CREATE ROLE c##test_role1;

GRANT CREATE SESSION TO c##test_role1;

-- Grant it to a common user.

GRANT c##test_role1 TO c##test_user1 CONTAINER=ALL;

--Grant it to a local user.

ALTER SESSION SET CONTAINER = pdb1;

GRANT c##test_role1 TO test_user3;
Manage Users and Privileges

Create Local Roles

CONN / AS SYSDBA
-- Switch container.

ALTER SESSION SET CONTAINER = pdb1;

-- Alternatively, connect to a local or common user -- with the PDB service.

-- CONN system/password@pdb1

-- Create the common role.

CREATE ROLE test_role1;
GRANT CREATE SESSION TO test_role1;
Program Agenda

 Oracle Multitenant Database Architecture

 Overview of Multitenant : (CDB) & (PDB)
 Multitenant : Create and Configure a (CDB) & (PDB)
 Multitenant : Startup and Shutdown (CDB) & (PDB)
 Multitenant : Manage Users and Privileges
 Multitenant : Cloning pluggable database
 Multitenant : Backup and Recovery (CDB) & (PDB)
 Multitenant : Migrate a (Non-CDB) to a (PDB)
Multitenant : Cloning pluggable database

Þ Provision a new PDB

create pluggable database PDB3

admin user PDB_Admin identified by password roles = (DBA);

Þ Provision a remote full clone

create pluggable database PDB2 from PDB1@CDB2;

Thinly provision a local snapshot clone

Þ create pluggable database PDB2 from PDB1;

snapshot copy
Multitenant : Cloning pluggable database

Create a Pluggable Database (PDB) Manually

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

Alternative Method

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;

Multitenant : Cloning pluggable database

Cloning methods from CDB

Þ alter session set pdb_file_name_convert='pdbseed','pdb2';

create pluggable database pdb2 admin user pdb_sam_admin identified by oracle;

Þ create pluggable database pdb_hr admin user pdb_hr_admin identified by oracle

file_name_convert = ('pdbseed', 'pdb3');

Þ create pluggable database pdb_sales admin user pdb_sales_admin identified by

oracle file_name_convert = ('pdbseed', 'pdb_sales')
default tablespace pdb_sales_users;

COLUMN pdb_name FORMAT A20

SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name;
SELECT name, open_mode FROM v$pdbs ORDER BY name;
Program Agenda

 Oracle Multitenant Database Architecture

 Overview of Multitenant : (CDB) & (PDB)
 Multitenant : Create and Configure a (CDB) & (PDB)
 Multitenant : Startup and Shutdown (CDB) & (PDB)
 Multitenant : Manage Users and Privileges
 Multitenant : Cloning pluggable database
 Multitenant : Backup and Recovery (CDB) & (PDB)
 Multitenant : Migrate a (Non-CDB) to a (PDB)
Multitenant : Backup and Recovery (CDB) & (PDB)

Container Database (CDB) Backup

$ rman target=/
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Root Container Backup

$ rman target=/
RMAN> BACKUP DATABASE ROOT;

Pluggable Database (PDB) Backup

$ rman target=/
RMAN> BACKUP PLUGGABLE DATABASE pdb1, pdb2;
Multitenant : Backup and Recovery (CDB) & (PDB)

Tablespace and Datafile Backups

Þ $ rman target=sys@pdb1
RMAN> BACKUP TABLESPACE system, sysaux, users;

Þ $ rman target=sys@pdb1
RMAN> BACKUP DATAFILE 8, 9, 10;

Complete Recovery
Container Database (CDB) Complete Recovery

Þ $ rman target=/
RUN
{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}
Multitenant : Backup and Recovery (CDB) & (PDB)

Pluggable Database (PDB) Complete Recovery

$ rman target=/
RUN
{
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE;
RESTORE PLUGGABLE DATABASE pdb1, pdb2;
RECOVER PLUGGABLE DATABASE pdb1, pdb2;
ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN;
}

Table Point In Time Recovery (PITR) in PDBs

RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1

UNTIL SCN 5695703 AUXILIARY DESTINATION '/u01/aux'
REMAP TABLE 'TEST'.'T1':'T1_PREV';
Program Agenda

 Oracle Multitenant Database Architecture

 Overview of Multitenant : (CDB) & (PDB)
 Multitenant : Create and Configure a (CDB) & (PDB)
 Multitenant : Startup and Shutdown (CDB) & (PDB)
 Multitenant : Manage Users and Privileges
 Multitenant : Cloning pluggable database
 Multitenant : Backup and Recovery (CDB) & (PDB)
 Multitenant : Migrate a (Non-CDB) to a (PDB)
Multitenant : Migrate a (Non-CDB) to a (PDB)

Using DBMS_PDB

Step : 1
Cleanly shutdown the non-CDB and start it in read-only mode.
• export ORACLE_SID=db12c
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP OPEN READ ONLY;

Step :2
BEGIN
DBMS_PDB.DESCRIBE( pdb_descr_file => '/tmp/db12c.xml');
END;
/

Step:3
Shutdown the non-CDB database.
export ORACLE_SID=db12c
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
Multitenant : Migrate a (Non-CDB) to a (PDB)

Using DBMS_PDB

Step:4

export ORACLE_SID=cdb1
sqlplus / as sysdba
CREATE PLUGGABLE DATABASE pdb6 USING '/tmp/db12c.xml' COPY FILE_NAME_CONVERT
= ('/u01/app/oracle/oradata/db12c/', '/u01/app/oracle/oradata
/cdb1/pdb6/');

Step:5
ALTER SESSION SET CONTAINER=pdb6;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Step:6
ALTER SESSION SET CONTAINER=pdb6;
ALTER PLUGGABLE DATABASE OPEN;