Multitenant is the biggest architectural change of Oracle 12c and the enabler of many new database options in the years to come. Therefore I have decided to write over the time, few blog posts with basic examples of what should be done and not in a multitenant database environment.
Rule #1 – What should not be done
If you are a CDB DBA, always pay attention to which container you are connected to and remember that application data should be stored on Application PDB only!
Unfortunately this golden rule is not-enforced by the RDBMS, but it is left in our responsibility as shown on the example below:
oracle@lxoel7n01:~/ [CDB_TEST] sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 21 18:28:23 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
CDB$ROOT SQL>
CDB$ROOT SQL> show user
USER is "SYS"
CDB$ROOT SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
Once connected to the ROOT container let see if I can mistakenly create an application table:
CDB$ROOT SQL> CREATE TABLE EMP_1
(emp_id NUMBER,
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB); 2 3 4 5 6
Table created.
CDB$ROOT SQL> desc emp_1
Name Null? Type
----------------------------------- -------- ----------------------------
EMP_ID NUMBER
EMP_NAME VARCHAR2(25)
START_DATE DATE
EMP_STATUS VARCHAR2(10)
RESUME CLOB
CDB$ROOT SQL> insert into emp_1 values (1, 'Emiliano', sysdate, 'active', ' ');
1 row created.
CDB$ROOT SQL> commit;
Commit complete.
CDB$ROOT SQL> select * from emp_1;
EMP_ID EMP_NAME START_DAT EMP_STATUS RESUME
---------- ------------------------- --------- ---------- ----------------
1 Emiliano 21-SEP-16 active
CDB$ROOT SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
The answer is “YES” and the consequences can be devastating…
Rule #2 – Overview of Local and Common Entities
Non-schema entities can be created as local or common. Local entities exist only in one PDB similar to a non-CDB architecture, while Common entities exist in every current and future container.
List of possible Local / Common entities in a Multitenant database:
- Users
- Roles
- Profiles
- Audit Policies
All Local entities are created from the local PDB and all Common entities are created from the CDB$ROOT container.
Common-user-defined Users, Roles and Profiles require a standard database prefix, defined by the spfile parameter COMMON_USER_PREFIX:
SQL> show parameter common_user_prefix
NAME TYPE VALUE
--------------------------------- ----------- -----------------
common_user_prefix string C##
Example of Common User creation:
SQL> CREATE USER C##CDB_DBA1 IDENTIFIED BY PWD CONTAINER=ALL;
User created.
SQL> SELECT con_id, username, user_id, common
2 FROM cdb_users where username='C##CDB_DBA1' ORDER BY con_id;
CON_ID USERNAME USER_ID COMMON
---------- -------------------- ---------- ------
1 C##CDB_DBA1 102 YES
2 C##CDB_DBA1 101 YES
3 C##CDB_DBA1 107 YES
4 C##CDB_DBA1 105 YES
5 C##CDB_DBA1 109 YES
...
Example of Local user creation:
SQL> show con_name
CON_NAME
------------------------------
MYPDB
SQL> CREATE USER application IDENTIFIED BY pwd CONTAINER=CURRENT;
User created.
If we try to create a Local User from the CDB$ROOT container the following error occurs: ORA-65049: creation of local user or role is not allowed in CDB$ROOT
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE USER application IDENTIFIED BY pwd CONTAINER=CURRENT;
CREATE USER application IDENTIFIED BY pwd CONTAINER=CURRENT
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT
Rule #3 – Application should connect through user-defined database services only
We have been avoiding to create user-defined database services for many years, sometimes even for RAC databases. But in Multitenet or Singletenant architecture the importance of user-defined database service is even greater. For each CDB and PDB Oracle is still automatically creating a default service, but as in the past the default services should never be exposed to the applications.
To create user-defined database service in stand-alone environment use the package DBMS_SERVICE while connected to the corresponding PDB:
BEGIN
DBMS_SERVICE.CREATE_SERVICE(
SERVICE_NAME => 'mypdb_app.emilianofusaglia.net',
NETWORK_NAME => 'mypdb_app.emilianofusaglia.net',
FAILOVER_METHOD =>
...
);
DBMS_SERVICE.START_SERVICE('mypdp_app.emilianofusaglia.net ');
END;
/
The database services will not start automatically after opening a PDB! Create a database trigger for this purpose.
To create user-defined database service in clustered environment use the srvctl utility from the corresponding RDBMS ORACLE_HOME:
oracle@oel7n01:~/ [EFU1] srvctl add service -db EFU \
> -pdb MYPDB -service mypdb_app.emilianofusaglia.net \
> -failovertype SELECT -failovermethod BASIC \
> -failoverdelay 2 -failoverretry 90
List all CDB database services order by Container ID:
SQL> SELECT con_id, name, pdb FROM v$services ORDER BY con_id;
CON_ID NAME PDB
---------- --------------------------------------- -----------------
1 EFUXDB CDB$ROOT <-- CDB Default Service
1 SYS$BACKGROUND CDB$ROOT <-- CDB Default Service
1 SYS$USERS CDB$ROOT <-- CDB Default Service
1 EFU.emilianofusaglia.net CDB$ROOT <-- CDB Default Service
1 EFU_ADMIN.emilianofusaglia.net CDB$ROOT <-- CDB User-defined Service
3 mypdb.emilianofusaglia.net MYPDB <-- PDB Default Service
3 mypdb_app.emilianofusaglia.net MYPDB <-- PDB User-defined Service
7 rows selected.
EZCONNECT to a PDB using the user-defined service:
sqlplus <username>/<password>@<host_name>:<local-listener-port>/<service-name>
sqlplus application/pwd@oel7c-scan:1522/mypdb_app.emilianofusaglia.net
Rule #4 – Backup/Recovery strategy in Multitenant
As database administrator one of the first responsibility to fulfil is the “Backup/Recovery” strategy. The migration to multitenant database, due to the high level of consolidation density requires to review existing procedures. Few infrastructure operations, like creating a Data Guard or executing a backup, have been shifted from per-database to per-container consolidating the number of tasks.
RMAN in 12c covers all CDB, PDB backup/restore combinations, even though the best practice suggests to run the daily backup at CDB level, and in case of restore needed, the granularity goes down to the single block of one PDB. Below are reported few basic backup/restore operations in Multitenant environment.
Backup a full CDB:
RMAN> connect target /;
RMAN> backup database plus archivelog;
Backup a list of PDBs:
RMAN> connect target /;
RMAN> backup pluggable database mypdb, hrpdb plus archivelog;
Backup one PDB directly connecting to it:
RMAN> connect target sys/manager@mypdb.emilianofusaglia.net;
RMAN> backup incremental level 0 database;
Backup a PDB tablespace:
RMAN> connect target /;
RMAN> backup tablespace mypdb:system;
Generate RMAN report:
RMAN> report need backup pluggable database mypdb;
Complete PDB Restore
RMAN> connect target /;
RMAN> alter pluggable database mypdb close;
RMAN> restore pluggable database mypdb;
RMAN> recover pluggable database mypdb;
RMAN> alter pluggable database mypdb open;
Rule #5 – Before moving to Multitenant
Oracle Multitenant has introduced many architectural changes that force the DBA to evolve how databases are administered. My last golden rule suggests to thoroughly study the multitenant/singletenant architecture before starting any implementation.
During my experiences implementing multitenant/singletenant architectures, I found great dependencies with the following database areas:
- Provisioning/Decommissioning
- Patching and Upgrade
- Backup/recovery
- Capacity Planning and Management
- Tuning
- Separation of duties between CDB and PDB
My Oracle Notes 