What are the Oracle 12c Pluggable databases? How does a pluggable database work?
These are the databases that are stored within the CDB.
A PDB is a portable collection of schemas, schema objects, and non schema objects that appears to an Oracle Net client as a non-CDB.
Physical Structure for PDBs
WHAT PLUGGABLE DATABASE’s have ??
- Each PDB has its own set of tablespaces including SYSTEM and SYSAUX.
- PDBs share UNDO, REDO and control files, (s)pfile of ROOT (CDB$ROOT).
- The CDB has a single TEMP tablespace but PDBs may create their own other wise it may use cdb’s TEMP tablespace.
- PDBs share common SGA and background process.
- Entire information of PDB’s are stored in CDB.
To check pluggable databases information.
|
1 2 3 4 5 6 7 8 9 10 |
SYS>>alter session set container=pdb$seed; Session altered. SYS>>show CON_NAME CON_NAME -------------- CDB$ROOT SYS>>show CON_ID CON_ID -------------- 1 |
To connect SEED DATABASE use following command.
|
1 2 3 4 5 6 7 8 9 10 |
SYS>>alter session set container=pdb$seed; Session altered. SYS>>sho con_name; CON_NAME ------------------------------ PDB$SEED SYS>>show CON_ID CON_ID -------------- 2 |
CREATE PLUGGABLE DATABASE using seed database :
Here we are creating a pluggable database called PLUG1 using SEED database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SYS>>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SYS>>create pluggable database plug1 admin user pluser identified by pluser roles=(dba) default tablespace userdata datafile '/u01/oradata/microcdb/plug1/userdata.dbf' size 10m file_name_convert=('/u01/oradata/microcdb/seed','/u01/oradata/microcdb/plug1'); Pluggable database created. SYS>>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG1 MOUNTED |
NOTE:
Whenever we create any pluggable database .The default mode of pluggable database is MOUNTED.we can make read write also.
Now we want connect to plug1 pluggable database.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SYS>>alter session set container=plug1; Session altered. SYS>>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PLUG1 MOUNTED SYS>>alter database open; Database altered. SYS>>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PLUG1 READ WRITE NO |
CREATE PLUGGABLE DATABASE BY USING CLONING METHODS:
Here I would like to create new pluggable database from existing pluggable database nothing but CLONING.
NOTE :
When ever we are creating a PDB using cloning method the existing pdb should be in READ WRITE mode.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SYS>>sho pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PLUG1 READ WRITE NO SYS>>conn / as sysdba Connected. SYS>>sho pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG1 READ WRITE NO SYS>>create pluggable database plug2 from plug1 file_name_convert=('/u01/oradata/microcdb/plug1','/u01/oradata/microcdb/plug2'); Pluggable database created. SYS>>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED --------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG1 READ WRITE NO 4 PLUG2 MOUNTED |
Making PDB as READ WRITE mode in another way.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SYS>>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED --------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG1 READ WRITE NO 4 PLUG2 MOUNTED SYS>>alter pluggable database plug2 open; Database altered. SYS>>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED --------- --------------------- ---------- --------- 2 PDB$SEED READ ONLY NO 3 PLUG1 READ WRITE NO 4 PLUG2 READ WRITE NO |
CREATE PLUGGABLE DATABASE USING XML FILE
Note :
For creating pdb using xml first we need to unplug the pdb into xml file.
Unplug a database :
|
1 2 3 4 5 6 |
SYS>>sho con_name; CON_NAME ------------------------------ CDB$ROOT SYS>>alter pluggable database plug1 unplug into '/users/oracle/plug1.xml'; Pluggable database altered. |
Plug a database:
While plug a database using xml file make sure we need to drop existing pluggable database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SYS>>sho pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG1 READ WRITE NO 4 PLUG2 READ WRITE NO SYS>>shut immediate; Database closed. Database dismounted. ORACLE instance shut down SYS>>startup ORACLE instance started. Total System Global Area 281018368 bytes Fixed Size 2923440 bytes Variable Size 222299216 bytes Database Buffers 50331648 bytes Redo Buffers 5464064 bytes Database mounted. Database opened. SYS>>sho pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG1 MOUNTED 4 PLUG2 MOUNTED SYS>>sho con_name; CON_NAME ------------------------------ CDB$ROOT SYS>>alter pluggable database plug1 unplug into '/users/oracle/plug1.xml'; Pluggable database altered. SYS>>drop pluggable database plug1 keep datafiles; Pluggable database dropped. SYS>>sho pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 PLUG2 MOUNTED SYS>>create pluggable database plug4 using '/users/oracle/plug1.xml' file_name_convert=('/u01/oradata/microcdb/plug1','/u01/oradata/microcdb/plug4'); Pluggable database created. SYS>>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG4 MOUNTED 4 PLUG2 MOUNTED |
CLONING PLUGGABLE DATABASE WITH OUT USERDATA datafiles.
Here we are creating PLUG5 using PLUG1 without cloning of userdata datafiles.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
SYS>>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG4 MOUNTED 5 PLUG3 READ WRITE NO SYS>>alter pluggable database plug4 open; Pluggable database altered. SYS>>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG4 READ WRITE NO 5 PLUG3 READ WRITE NO SYS>>create pluggable database plug5 from plug4 file_name_convert=('/u01/oradata/microcdb/plug4','/u01/oradata/microcdb/plug5') user_tablespaces=none; Pluggable database created. SYS>>alter session set container=plug5; Session altered. SYS>>alter database open; Database altered. SYS>>select name from v$datafile; NAME ---------------------------------------- /u01/oradata/microcdb/undotbs.dbf /u01/oradata/microcdb/plug5/system.dbf /u01/oradata/microcdb/plug5/sysaux.dbf /oraeng/app/oracle/product/12.1.0/dbs/MISSING00139 |
To SHUTDOWN and STARTUP all the pluggable database at once we need to use below command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SYS>>alter pluggable database all close; Database altered. SYS>>startup ORACLE instance started. Total System Global Area 281018368 bytes Fixed Size 2923440 bytes Variable Size 222299216 bytes Database Buffers 50331648 bytes Redo Buffers 5464064 bytes Database mounted. Database opened. SYS>>sho pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG1 MOUNTED 4 PLUG3 MOUNTED 5 PLUG5 MOUNTED SYS>>alter pluggable database all OPEN; Database altered. SYS>>sho pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- -------- 2 PDB$SEED READ ONLY 3 PLUG1 READ WRITE 4 PLUG3 READ WRITE 5 PLUG5 READ WRITE |
Note: Please test scripts in Non Prod before trying in Production.
Loading...
SUMAN MORA
Its a very nice article and easily understandable. Very good explanation.
Vinod
Hello Everyone
We have discussed regarding the introduction of multitenency in our first session.
Now we will discuss on different ways of creating the container databases,
how to connect to the PDB and creating the global and local users in our next session
Please go through below article before connecting to the session.
https://www.ktexperts.com/what-are-the-oracle-12c-pluggable-databases-how-does-a-pluggable-database-work/
Also, please give your your valuable comments in the comment section at end of the article.
Session Details :
What are the Oracle 12c Pluggable databases?
Sat, Jun 16, 2018 8:00 AM – 11:00 AM PDT
Please join my meeting from your computer, tablet or smartphone.
https://global.gotomeeting.com/join/735527389
You can also dial in using your phone.
United States: +1 (408) 650-3123
Access Code: 735-527-389
Time Zone Converter :
https://www.timeanddate.com/worldclock/converter.html?iso=20180616T150000&p1=176&p2=137
Vinod
Hello Everyone,
Great response to the session on How to work with PDBs.
Please check Vimeo video link.:
https://vimeo.com/275418530/887cfb5a49