ORACLE 12C MULTITENANT ARCHITECTURE :
---------------------------------------------------------------
Pluggable Databases (PDBs) is new in Oracle Database 12c Release 1 . You can have many pluggable databases inside a single Oracle Database and single instance
Following factors will save using Multitenant Architecture
-------------------------------------------------------------------------
Reduce Total Cost of Operation
Administration costs
Operations costs
Data center costs
Storage costs
Contingency costs
Improve Functionalities
Resource utilization
Manageability
Integration
Service management
simplify patching and upgrade of Oracle Database
in oracle 11g Architecture----->schema---->database
3 databases --->3 licenses --->3 patching-----> additional monitoring----> space/performance/backups-----> 3 instance
hr
sales
finance
One single Instance -----> multiple tenants
CDB$root- root container - Primary db - with single instance
pluggable container databases. - 256 - tenants
redo , controlfiles,system dictionary data, one time patching
one license,segregated roles(cdb_dba,pdb_dba)
Default with every root container , will have a seed database PDB$SEED
template to create pdb databases - read-only
Pdb to be started manually after root container started.
PDB - Default will be in mount stage
sql>alter pluggable database hrpdb open;
Using dbca - will create container and pluggable databases.
$dbca - database configuration assistant.
create database
choose - Global Database Name : prod.chaitanyaodb.com
Create as container database
pdbhr
delete database
manage pluggable database
create/unplug/delete
Verify - 12c is multi-tenant Option
SQL> show parameter enable_pluggable
true
Use option create pluggable container db
while runInstaller -
Creating Pluggable database
----------------------------
$dbca
One Container Database
Multiple Pluggable Database - 253
One database - multiple schemas till 11g
now 12c - One container database - multiple databases
CDB - root container
PDB - Pluggable Database
When you install
will have default one root container database
with default one pdb$seed template
PDB$SEED - read only mode - Template database
using we can create -New Pluggable Database
sql>show con_name
sql>show con_id
For root container database - CDB$root
List of Container - v$containers
SQL> COLUMN NAME FORMAT A8
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
-------- ----------
PDB$SEED READ ONLY
PDB_DB2 MOUNTED
PDB67 MOUNTED
PDB10 MOUNTED
12c - Cloud Technology
Multinenant Feature.
Root - Container Database - Metadata
Multiple Pluggable Database-253
user metadata/user data
Four Methods:
---------------
Create a new PDB from the seed
Plug non cdb in a cdb as pdb
Clone a PDB from another PDB of the same CDB
Plug an unplugged PDB into another CDB.
Note : When we start the CDB, the PDB will be default in Mount mode.
sql>select con_id,cdb,name,con_dbid,open_mode from v$database;
CON_ID CDB NAME CON_DBID OPEN_MODE
---------- --- --------- ----------
0 YES PROD 246685968 READ WRITE
sql>select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB5 READ WRITE
PDB8 READ WRITE
How to connect to a PDB?
SQL> alter session set container=CDB$root;
sql>show con_id
sql>show con_name
SQL> alter session set container=ptgdb;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
pbnk
CDB$root - system tablespace contain oracle supplied system related dictionary data.
PDB - system tablespace contain user metadata of PDB Database.
SQL> select pdb_name,status from cdb_pdbs;
cdb_pdbs - list of all pdbs
cdb_tablespaces - list of all pdb tablespaces
cdb_data_files
cdb_users
To shutdown the PDB
--------------------
sql>select name,open_mode from v$pdbs;
sql>alter pluggable database hrdb close immediate;
sQL> select name,open_mode from v$pdbs;
sql>alter pluggable database hrdb open;
To open all the PDB's
sql>alter pluggable database all open;
-------------------------------
COLUMN NAME FORMAT A8
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
Using DBCA- Database Configuration Assistant
-------------------------------------------
$dbca
manage pluggable databases
unplug pdb database
select pdb name
location
sql>
CREATE PLUGGABLE DATABASE salesdb
ADMIN USER sadmin IDENTIFIED BY s123
ROLES=(CONNECT);
COLUMN NAME FORMAT A8
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
COLUMN PDB_NAME FORMAT A15
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
SELECT NAME FROM V$SYSTEM_PARAMETER
WHERE ISPDB_MODIFIABLE = 'TRUE'
ORDER BY NAME;
Finding history on PDB and CDB
``````````````````````````````
COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
creating users and how to connect to oracle12c containerand pluggable databases in multitenant environment
two types of multitenant database users-----> common user and local user
Creating Common User : A common user is created in root cdb common user can connect to root cdb and all pdb's including future pdb's which you may plug
-------------------
SQL> create user c##chaitanya identified by chai123 container=all;
User created.
SQL> grant connect,resorces to c##chaitanya;
SQL> conn c##chaitanya/chai123
Creating Local user - : A local user is created in a pdb database and he can connect and has priveleges in that pdb only
----------------------------
SQL> alter session set container=salesdb;
Session altered.
SQL> create user abhiram identified by abhi123 quota 50m on users;
User created.
SQL> grant connect,resorce to abhiram;
To connect a pluggable database salesdb as abhiram user you have to connect through e2 connect method or through TNS names to connect through e2 connect
SQL> conn abhiram/abhi123@192.168.50.100/salesdb
to connect throug TNS names you have to add entry in the TNSNames.ora file
open TNSNames.ora file add the following entry
$cd
$ORACLE_HOME/network/admin
$ vi tnsnames.ora
salesdb=
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)
(HOST=192.168.50.100)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=salesdb)
)
)
now to connect
$ sqlplus abhiram/abhi123@salesdb
creating pluggable databases from seed
---------------------------------------
lets create a pluggable database salesdb we will place the datafiles in the directory
/u02/oracle/salesdb/salesdb1 directory
create the directory
$ mkdir -p /u02/oracle/salesdb/salesdb1
$ sqlplus '/as sysdba'
sql> create pluggable database salesdb1 admin user salesdb1admin
identified by saledb1123
storage (maxsize 2g max_shared_temp_size 100m)
default tablespace users
datafile '/u02/oracle/salesdb/salesdb1/user01.dbf'
size 250 m autoextend on
file_name_convert= ('/u02/oracle/salesdb/pdbseed/','/u02/oracle/salesdb/salesdb1/')
Note: here salesdb=source
salesdb1=clone
cloning an existing pluggable database
---------------------------------------
let us clone the local pluggable database salesdb1 to salesdb2
first create the directory to hold salesdb2 datafiles
$mkdir -p /u02/oracle/salesdb/salesdb2
sqlplus to connect the root
sqlplus '/as sysdba'
sql> alter pluggable database salesdb1 close;
sql> create pluggable database salesdb2 from salesdb1 file_name_convert= ('/u02/oracle/salesdb/salesdb1/,',' /u02/oracle/salesdb/salesdb2/')
storage (maxsize 2g max_shared_temp_size 100m);
sql>pluggable database created
Unplugging and Plugging database from cdb to another cdb
---------------------------------------------------------
step 1: connect to salesdb databse
$ export ORACLE_SID= salesdb
$sqlplus '/as sysdba'
step2: close the pluggable database salesdb2
sql> alter pluggable database salesdb2 close;
step 3: alter pluggable database salesdb2
unplug into '/u02/oracle/salesdb/salesdb2.xml;
step 4: create target directory
$ mkdir -p /u01/oracle/salesdb_moved
step 5: connect to cdb 'orcl'
$ export ORACLE_SID =ORCL
sqlplus '/as sysdba'
step 6: start the target cdb
sql> startup
step 7: create pluggable database salesdb_moved using '/u02/oracle/salesdb2.xml' move file_name_convert=('/u02/oracle/salesdb/salesdb2/',' /u01/oracle/salesdb_moved/')
storage (maxsize 2g max_shared_temp_size 100m);
pluggable database created
sql> show pdbs
conn_id ---> 2 conn_name--->pdb$seed open mode restricted-----> read only no
conn_id------->3 conn_name---->salespdb open mode restricted -----> mounted
conn_id-------> 4 conn_name---->salesdb_moved open mode restricted ----> mounted
step 8 : openthe pluggable database salesdb_moved
sql > alter pluggable database salesdb_moved open;
--------
CDB$root - Root Container
System - common for all schemas
redo - common
undo -
SGA + PGA
BG Processes
PDB Databases - Max - 253
Pluggable Database - Schema1,schema3 - tablespace -datafile
Pluggable Database - Schema2,schema4 - tablespace -datafile
Note: oracle 12c multitenant architecture info it may be differ from your environment like production,development,testing,and directory structure ,db names etc
THANKING YOU FOR VIEWING MY BLOG FOR MORE UPDATES FOLLLOW ON MY BLOG
