Refer Me for Oracle: Container (CDB) and Pluggable (PDB) Database

Container (CDB) and Pluggable (PDB) Database

--------------------------------------------

Why CDB?

--------

1) Effective hardware utilization.

2) DB's which are not so complex to require 100% of the attention of Full time DBA.

3) To better exploit hardware and DBA resource.

4) Patching / Upgrade time iz minimized.

--> Reduced instance overhead.

--> Reduction of redunant copies of data dictionary and consequently Storage Allocation.

--> Fast provisioning.

--> CDB

--> PDB_SEED

--> PDB 1

--> PDB 2

--> PDB n

Metadata

--------

CDB

--> Contains the Oracle supplied metadata.

PDB

--> Contains the user objects metadata

--> Oracle supplied metadata are visibile in PDB using the pointers without duplicating the Oracle metadata inside the PDB.

PDB Creation

------------

CDB -> PDB Seed -> PDB

--> PDB can be created using

--> From the PDB seed.

--> Plug in a non cdb into CDB.

--> Clone a non cdb into cdb as pdb or Clone pdb and plug it into same or another CDB.

--> Plug an unplugged pdb into cdb.

Architecture

-------------

Single DB shares

--> Background processes

--> Shared process / Memory

--> Oracle Metadata

--> Redo log files / Controlfiles / Undo tablespace.

Containers

----------

Root Container (CDB$ROOT)

--> The first container created at CDB creation.

--> Contains Oracle Supplied objects / Metadata / Users and roles.

PDB Seed (PDB$SEED)

--> Contains tablespace for fast provisioning of PDB's.

PDB's

--> Container for application, Contains users objects metadata / application tablespaces.

Maximum Limit of 253 PDB's including PDB root / Limit of 1024 Services in CDB.

SQL> select con_id, name, open_mode from V$containers;

CON_ID NAME OPEN_MODE

---------- ------------------------------ ----------

1 CDB$ROOT READ WRITE

2 PDB$SEED READ ONLY

Shared Objects

--------------

Metadata-Linked Objects --> Metadata-linked objects store metadata about dictionary objects only in the root. Each PDB's PDB has a

private data copy of an object pointing to a metadata link stored in the root.

Object Link --> An object link and its data resides only in the root and shared by all PDB's.

Views

-----

cdb_XXX ( all object in the Multitenant accross all the pdbs)

dba_XXX ( all objects in the cdb or pdb)

all_XXX ( objects accessible by current user)

user_XXX ( objects owner by that user)

select view_name from dba_views where view_name like '%CDB%';

select table_name from dict where table_name like '%DBA%';

Impacts

-------

--> One character set for entire CDB.

--> PDB init parameters but single spfile.

--> Oracle Data guard at CDB level.

--> Oracle Database vault at PDB only.

--> Each PDB has its own master key used to encrypt data in the PDB.

--> Oracle Streams only supports non-CDB.

Creating CDB Using SQL Plus

---------------------------

a) Create directories.

[oracle@rhel11gr2rac1 dbs]$ mkdir -p /oracle/product/admin/sec_cdb/adump

[oracle@rhel11gr2rac1 dbs]$ mkdir -p /oracle/product/oradata/sec_cdb

[oracle@rhel11gr2rac1 dbs]$ mkdir -p /oracle/product/fast_recovery_area/sec_cdb/controlfile

[oracle@rhel11gr2rac1 dbs]$ mkdir -p /oracle/product/fast_recovery_area/sec_cdb/onlinelog

[oracle@rhel11gr2rac1 dbs]$ mkdir -p /oracle/product/oradata/sec_cdb/seed

b) Create Parameter file as below (check the enable_pluggable_database=true) which is to create db as cdb.

initsec_cdb.ora

*.audit_file_dest='/oracle/product/admin/sec_cdb/adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/oracle/product/oradata/sec_cdb/control01.ctl','/oracle/product/fast_recovery_area/sec_cdb/controlfile/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='/oracle/product/oradata'

*.db_domain=''

*.db_name='sec_cdb'

*.db_recovery_file_dest='/oracle/product/fast_recovery_area'

*.db_recovery_file_dest_size=4800m

*.diagnostic_dest='/oracle/product'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=cdbXDB)'

*.enable_pluggable_database=true

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=640m

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

c ) export ORACLE_SID=sec_cdb

d) start instance in no mount

SQL> create database sec_cdb

user sys identified by admin

user system identified by admin

Logfile group 1 ('/oracle/product/oradata/sec_cdb/redo01a.log','/oracle/product/fast_recovery_area/sec_cdb/onlinelog/redo01b.log') size 100M,

group 2 ('/oracle/product/oradata/sec_cdb/redo02a.log','/oracle/product/fast_recovery_area/sec_cdb/onlinelog/redo02b.log') size 100M

character set AL32UTF8 national character set AL16UTF16

extent management local

datafile '/oracle/product/oradata/sec_cdb/system01.dbf' size 750M

sysaux datafile '/oracle/product/oradata/sec_cdb/sysaux01.dbf' size 750M

default temporary tablespace temptbs1

tempfile '/oracle/product/oradata/sec_cdb/temptbs01.dbf' size 100M

undo tablespace undotbs1

datafile '/oracle/product/oradata/sec_cdb/undotbs01.dbf' size 200M

enable pluggable database

seed file_name_convert = ('/oracle/product/oradata/sec_cdb','/oracle/product/oradata/sec_cdb/seed');

Database created.

--- Without seed file_name_convert (When any of the below is added in the parameter file)

--> OMF - DB_FILE_CREATE_DEST='/oracle/product/oradata' or

--> - PDB_FILE_NAME_CONVERT='/oracle/product/oradata/sec_cdb','/oracle/product/oradata/sec_cdb/seed'

SQL> @?/rdbms/admin/catcdb.sql

In 12.1.0.1 ( The catcdb file is missing in oracle software, check MISSING FILE CATCDB.SQL IN $ORACLE_HOME/RDBMS/ADMIN in 12C RDBMS software (Doc ID 1575778.1))

In 12.1.0.2 ( apply the below changes if you get the below error).

Error-

Can't locate Term/ReadKey.pm in @INC (@INC contains: /oracle/product/12.1.0.2/db_1/rdbms/admin /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi

/usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8

/usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 . /oracle/product/12.1.0.2/db_1/rdbms/admin/) at

/oracle/product/12.1.0.2/db_1/rdbms/admin/catcon.pm line 189.

BEGIN failed--compilation aborted at /oracle/product/12.1.0.2/db_1/rdbms/admin/catcon.pm line 189.

Compilation failed in require at /oracle/product/12.1.0.2/db_1/rdbms/admin/catcon.pl line 94.

BEGIN failed--compilation aborted at /oracle/product/12.1.0.2/db_1/rdbms/admin/catcon.pl line 94.

Solution

--------

Download and install the perl-TermReadKey package as per your Os version.

http://rpmfind.net/linux/rpm2html/search.php?query=perl&submit=Search+...&system=redhat-5.5&arch=

[root@rhel11gr2rac1 oracle]# rpm -ivh perl-TermReadKey-2.30-1.el5.rf.x86_64.rpm

warning: perl-TermReadKey-2.30-1.el5.rf.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 6b8d79e6

Preparing... ########################################### [100%]

1:perl-TermReadKey ########################################### [100%]

Set perl location in PATH variable.

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

run the script again.

-->

Once done CBD is ready. Verify it.

Creaing New PDB's

-----------------

Creating new PDB from PDB Seed.

-------------------------------

--> Copies the datafile for system / sysaux and tempfiles from PDB seed.

--> Creates a full catalog including metadata pointing to oracle supplied objects.

--> Creates common users sys / system.

--> Creates local user and assigned PDB_DBA role.

--> Create a new default service.

New PDB datafile Location - /opt/oracle/oradata/pdb_sales

SQL> select con_id, name, open_mode from V$pdbs;

CON_ID NAME OPEN_MODE

---------- ------------------------------ ----------

2 PDB$SEED READ ONLY

SQL> select name from V$datafile where con_id = 2;

NAME

--------------------------------------------------------------------------------

/oracle/product/oradata/fir_cdb/pdbseed/system01.dbf

/oracle/product/oradata/fir_cdb/pdbseed/sysaux01.dbf

--- PDB seed datafiles are location in above location.

SQL> CREATE PLUGGABLE DATABASE PDB_SALES

ADMIN USER MANZOOR IDENTIFIED BY ahamed ROLES=(connect)

FILE_NAME_CONVERT = ('/oracle/product/oradata/fir_cdb/pdbseed','/opt/oracle/oradata/pdb_sales')

Pluggable database created.

or (12.1.0.2 can use create_file_dest)

SQL> CREATE PLUGGABLE DATABASE PDB_SALES

ADMIN USER MANZOOR IDENTIFIED BY ahamed ROLES=(connect)

CREATE_FILE_DEST = '/opt/oracle/oradata/pdb_sales'

--- Without location clause (When any of the below is added in the parameter file)

--> OMF - DB_FILE_CREATE_DEST='/oracle/product/oradata' or

--> - PDB_FILE_NAME_CONVERT='/oracle/product/oradata/fir_cdb/pdbseed','/opt/oracle/oradata/pdb_sales'

-- in 12.1.02 new clause included in create pluggable database.

USER_TABLESPACE - When moving non-cdb to cdb we can include one or more tablespaces.

ENABLE|DISABLE | FORCE LOGGING | NOLOGGING

STANDBY=ALL|NONE whether to include the new pdb in standby or not.

SQL> select con_id, name, open_mode from V$pdbs;

CON_ID NAME OPEN_MODE

---------- -------------------------------------------------------------------------------- ----------

2 PDB$SEED READ ONLY

3 PDB_SALES MOUNTED

SQL> alter pluggable database pdb_sales open;

Pluggable database altered.

SQL> select NAME from cdb_services where CON_ID =3;

NAME

--------------------------------------------------------------------------------

pdb_sales

SQL> select name from V$datafile where con_id=3 union all

select name from V$tempfile where con_id=3;

NAME

--------------------------------------------------------------------------------

/opt/oracle/oradata/pdb_sales/system01.dbf

/opt/oracle/oradata/pdb_sales/sysaux01.dbf

/opt/oracle/oradata/pdb_sales/pdbseed_temp01.dbf

[oracle@rhel11gr2rac1 pdb_sales]$ lsnrctl status LISTENER_FIRCDB

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 11-OCT-2015 18:33:46

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rhel11gr2rac1.manzoor.com)(PORT=1522)))

STATUS of the LISTENER

------------------------

Alias LISTENER_FIRCDB

Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production

Start Date 11-OCT-2015 18:13:48

Uptime 0 days 0 hr. 19 min. 58 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/product/12.1/db_1/network/admin/listener.ora

Listener Log File /oracle/product/diag/tnslsnr/rhel11gr2rac1/listener_fircdb/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel11gr2rac1.manzoor.com)(PORT=1522)))

Services Summary...

Service "fir_cdb" has 1 instance(s).

Instance "fircdb", status READY, has 1 handler(s) for this service...

Service "fircdbXDB" has 1 instance(s).

Instance "fircdb", status READY, has 1 handler(s) for this service...

Service "pdb_sales" has 1 instance(s).

Instance "fircdb", status READY, has 1 handler(s) for this service...

The command completed successfully

-- Service is registered with listener.

-- Update tnsnames.ora

SALES =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rhel11gr2rac1.manzoor.com)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb_sales)

)

[oracle@rhel11gr2rac1 admin]$ tnsping sales

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 11-OCT-2015 18:35:43

Used parameter files:

/oracle/product/12.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rhel11gr2rac1.manzoor.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb_sales)))

OK (0 msec)

[oracle@rhel11gr2rac1 admin]$ sqlplus manzoor/ahamed@sales

SQL*Plus: Release 12.1.0.1.0 Production on Sun Oct 11 18:35:56 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user;

USER is "MANZOOR"

SQL> select * from session_roles;

ROLE

----------

PDB_DBA

CONNECT

SQL> select * from role_sys_privs where role = 'PDB_DBA';

ROLE PRIVILEGE ADM COM

---------- ---------------------------------------- --- ---

PDB_DBA CREATE SESSION NO NO

PDB_DBA SET CONTAINER NO NO

PDB_DBA CREATE PLUGGABLE DATABASE NO NO

SQL> select * from role_role_privs where role = 'PDB_DBA';

ROLE GRANTED_ROLE ADM COM

---------- -------------------------------------------------------------------------------------------------------------------------------- --- ---

PDB_DBA CONNECT NO NO

SQL> Conn / as sysdba

Connected.

SQL> alter session set container=PDB_SALES;

Session altered.

SQL> create user sales_admin identified by sales_admin;

User created.

SQL> grant connect, resource to sales_admin;

Grant succeeded.

SQL> grant SELECT_CATALOG_ROLE to sales_admin;

Grant succeeded.

SQL> grant unlimited tablespace to sales_admin;

Grant succeeded.

[oracle@rhel11gr2rac1 admin]$ sqlplus sales_admin/sales_admin@sales

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 12 07:13:47 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name;

CON_NAME

------------------------------

PDB_SALES

After the PDB is created using seed it has to be syncronised with the cdb. If any common users are created before the pdb is open

it has to be re-sync with the cdb. The sync will automatically happen when you open the cdb.

If you open in read only mode an error will return.

When a pdb is opened compatibility will be checked, it will be either error or warning. If error then error

will be displayed and the db will not be open, and it will be recorded in alert log, if warning it will open

the db and waring is added in alert log. Only users which restricted session priv can login to pdb when there

is error to address it. You can view PDB_PLUG_IN_VIOLATION to view the compatibility violations.

Plugin a Non-cdb database into cdb.

-----------------------------------

Non cdb db - purchase (12.1.0.2)

Cdb db - sec_Cdb (12.1.0.2)

Three method

-> Using TTS / TDB / export/import

-> Using dbms_pdb

-> using Golden gate replication.

Method usig dbms_pdb.

-- Open the purchase database in read only mode.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 605450240 bytes

Fixed Size 2291232 bytes

Variable Size 499124704 bytes

Database Buffers 100663296 bytes

Redo Buffers 3371008 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

-- Execute the dbms_pdb.describe proceudre to extract the xml definition file.

SQL> exec dbms_pdb.describe('/tmp/purchase_noncbd.xml');

PL/SQL procedure successfully completed.

Connect as sys user in CDB database.

-- Before plugging in the non cdb, verify whether the database is compatible to plug in using

the dbms_pdb.check_plug_compatibility function.

SQL> set serveroutput on;

SQL> declare

k boolean;

begin

k := dbms_pdb.CHECK_PLUG_COMPATIBILITY('/tmp/purchase_noncbd.xml');

if k then

dbms_output.put_line ('Compatible Check passed');

else

dbms_output.put_line ('Compatible Check Failed');

end if;

end;

Compatible Check Failed

PL/SQL procedure successfully completed.

The output shows as as "Compatible Check Failed", Need to check the option which is failing.

SQL> select view_name from dba_viewS where view_name like '%VIOL%';

VIEW_NAME

--------------------------------------------------------------------------------

PDB_PLUG_IN_VIOLATIONS

set lines 200 pages 200;

col name for a20;

col cause for a25;

col message for a80;

col action for a70;

SQL> select name, cause, TYPE, MESSAGE, STATUS, ACTION from PDB_PLUG_IN_VIOLATIONS where type = 'ERROR';

NAME CAUSE TYPE MESSAGE STATUS ACTION

--------------- -------------------- --------- ------------------------------------------------------------ --------- ------------------------------

PURCHASE SQL patch error ERROR (PSU bundle patch 4 (Database Patch Set Update : 12.1.0.2.4 PENDING Call datapatch to reinstall

(20831110)): APPLY SUCCESS): with status in the PDB.

PURCHASE SQL patch error ERROR (SQL patch ID/UID 21068507/18985494 (Database PSU 12.1.0.2. PENDING Call datapatch to reinstall

4, Oracle JavaVM Component (Jul2015)): APPLY SUCCESS): with

status in the PDB.

There are some SQL patch related violations been observed, here both cdb and non-cdb are running from the same oracle home,

Patching has been done in OS level, but post sql patching is not done in CDB.

Query from non cdb.

SQL> select PATCH_ID, PATCH_UID, version, action, status from DBA_REGISTRY_SQLPATCH;

PATCH_ID PATCH_UID VERSION ACTION STATUS

---------- ---------- -------------------- --------------- ---------------

20831110 18977826 12.1.0.2 APPLY SUCCESS

21068507 18985494 12.1.0.2 APPLY SUCCESS

Query from CDB.

SQL> select PATCH_ID, PATCH_UID, version, action, status from DBA_REGISTRY_SQLPATCH;

no rows selected

as per the above Post patching (sql patch) is not done in the CDB, Patch the CDB.

[oracle@rhel11gr2rac1 OPatch]$ cd $ORACLE_HOME/OPatch

[oracle@rhel11gr2rac1 OPatch]$ ./datapatch -prereq

SQL Patching tool version 12.1.0.2.0 on Mon Oct 12 08:13:51 2015

Connecting to database...OK

Note: Datapatch will only apply or rollback SQL fixes for PDBs

that are in an open state, no patches will be applied to closed PDBs.

Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

(Doc ID 1585822.1)

Determining current state...done

Adding patches to installation queue and performing prereq checks...done

Installation queue:

For the following PDBs: CDB$ROOT PDB$SEED

Nothing to roll back

The following patches will be applied:

21068507 (Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015))

20831110 (Database Patch Set Update : 12.1.0.2.4 (20831110))

For the following PDBs: PDB_PURCHASE

Nothing to roll back

Nothing to apply

Error: prereq checks failed!

patch 21068507: The pluggable databases that need to be patched must be in upgrade mode

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log

/u01/app/oraInventory/cfgtoollogs/sqlpatch/sqlpatch_14012_2015_10_12_08_13_51/sqlpatch_invocation.log

for information on how to resolve the above errors.

SQL Patching tool complete on Mon Oct 12 08:14:37 2015

Shutdown the CDB and startup in upgrade mode :-

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 658505728 bytes

Fixed Size 2927864 bytes

Variable Size 490734344 bytes

Database Buffers 159383552 bytes

Redo Buffers 5459968 bytes

Database mounted.

Database opened.

SQL> exit

Apply the patch now.

[oracle@rhel11gr2rac1 OPatch]$ ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 on Mon Oct 12 08:20:19 2015

Log file for this invocation: /u01/app/oraInventory/cfgtoollogs/sqlpatch/sqlpatch_16408_2015_10_12_08_20_19/sqlpatch_invocation.log

Connecting to database...OK

Note: Datapatch will only apply or rollback SQL fixes for PDBs

that are in an open state, no patches will be applied to closed PDBs.

Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

(Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of SQL patches:

Patch 21068507 (Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015)):

Installed in the binary registry only

Bundle series PSU:

ID 4 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks...

Installation queue:

For the following PDBs: CDB$ROOT PDB$SEED

Nothing to roll back

The following patches will be applied:

21068507 (Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015))

20831110 (Database Patch Set Update : 12.1.0.2.4 (20831110))

Installing patches...

Patch installation complete. Total patches installed: 4

Validating logfiles...

Patch 21068507 apply (pdb CDB$ROOT): SUCCESS

logfile: /u01/app/oraInventory/cfgtoollogs/sqlpatch/21068507/18985494/21068507_apply_SEC_CDB_CDBROOT_2015Oct12_08_20_53.log (no errors)

Patch 20831110 apply (pdb CDB$ROOT): SUCCESS

logfile: /u01/app/oraInventory/cfgtoollogs/sqlpatch/20831110/18977826/20831110_apply_SEC_CDB_CDBROOT_2015Oct12_08_23_14.log (no errors)

Patch 21068507 apply (pdb PDB$SEED): SUCCESS

logfile: /u01/app/oraInventory/cfgtoollogs/sqlpatch/21068507/18985494/21068507_apply_SEC_CDB_PDBSEED_2015Oct12_08_23_25.log (no errors)

Patch 20831110 apply (pdb PDB$SEED): SUCCESS

logfile: /u01/app/oraInventory/cfgtoollogs/sqlpatch/20831110/18977826/20831110_apply_SEC_CDB_PDBSEED_2015Oct12_08_25_32.log (no errors)

SQL Patching tool complete on Mon Oct 12 08:25:44 2015

Re-start the CDB in normal mode.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 658505728 bytes

Fixed Size 2927864 bytes

Variable Size 490734344 bytes

Database Buffers 159383552 bytes

Redo Buffers 5459968 bytes

Database mounted.

Database opened.

SQL> CREATE PLUGGABLE DATABASE PDB_PURCHASE USING '/tmp/purchase_noncbd.xml'

FILE_NAME_CONVERT=('/u01/app/oracle/oradata/purchase','/opt/oracle/oradata/pdb_purchase');

Pluggable database created.

-- Connect to the pdb database and exeucte the noncdb_to_pdb script.

SQL> alter session set container= pdb_purchase;

Session altered.

SQL> @?/rdbms/admin/noncdb_to_pdb;

---

PL/SQL procedure successfully completed.

SQL> alter pluggable database pdb_purchase open;

Pluggable database altered.

The PDB is got open now.

SQL> select name, open_mode, RESTRICTED from V$pdbs;

NAME OPEN_MODE RES

-------------------- ---------- ---

PDB$SEED READ ONLY NO

PDB_PURCHASE READ WRITE NO

3) Creating PDB by cloning another PDB.

SQL> select name, open_mode from V$pdbs;

NAME OPEN_MODE

------------------------------ ----------

PDB$SEED READ ONLY

SAL_PDB MOUNTED

HRDEPT MOUNTED

Clone sal_pdb as pur_pub database.

----------------------------------

Open the source sal_pdb database in read only mode.

SQL> alter pluggable database SAL_PDB open read only;

Pluggable database altered.

Execute the create pluggable database statement as below.

SQL> create pluggable database pur_pdb from SAL_PDB

2 file_name_convert=('/oracle/product/oradata/fir_cdb/SAL_PDB','/oracle/product/oradata/fir_cdb/PUR_PDB');

Pluggable database created.

Open the source and the target pdb db's.

SQL> alter pluggable database pur_pdb open;

Pluggable database altered.

SQL> alter pluggable database SAL_PDB close;

Pluggable database altered.

SQL> alter pluggable database SAL_PDB open;

Pluggable database altered.

SQL> select name, open_mode from V$pdbs;

NAME OPEN_MODE

------------------------------ ----------

PDB$SEED READ ONLY

SAL_PDB READ WRITE

HRDEPT MOUNTED

PUR_PDB READ WRITE

SQL> alter pluggable database PUR_PDB close;

Pluggable database altered.

SQL> drop pluggable database PUR_PDB including datafiles;

Pluggable database dropped.

Create PDB using remote non-cdb or pdb.

---------------------------------------

Source db (non-cdb) - Purchase

Target db (pdb) - pdb_purchase

Open the source database in read only mode.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 605450240 bytes

Fixed Size 2291232 bytes

Variable Size 499124704 bytes

Database Buffers 100663296 bytes

Redo Buffers 3371008 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

SQL> exit;

Create a database link in the target cdb root as below.

SQL> create database link purchase_link connect to system identified by admin using 'purchase';

Database link created.

SQL> select sysdate from dual@purchase_link;

SYSDATE

---------

12-OCT-15

Create the PDB

SQL> CREATE PLUGGABLE DATABASE PDB_PURCHASE FROM PURCHASE@purchase_link

FILE_NAME_CONVERT=('/u01/app/oracle/oradata/purchase','/opt/oracle/oradata/pdb_purchase');

Pluggable database created.

SQL> alter session set container=pdb_purchase;

Session altered.

SQL> @?/rdbms/admin/noncdb_to_pdb.sql;

---

PL/SQL procedure successfully completed.

SQL> alter pluggable database pdb_purchase open;

Pluggable database altered.

The PDB is got open now.

SQL> select name, open_mode, RESTRICTED from V$pdbs;

NAME OPEN_MODE RES

-------------------- ---------- ---

PDB$SEED READ ONLY NO

PDB_PURCHASE READ WRITE NO

Unplug a pdb from a cdb and plug it to another cdb.

---------------------------------------------------

Source pdb - HRDEPT

SQL> select name, open_mode from V$pdbs;

NAME OPEN_MODE

------------------------------ ----------

PDB$SEED READ ONLY

SAL_PDB MOUNTED

HRDEPT MOUNTED

SQL> alter pluggable database HRDEPT unplug into 'hr_unplug.xml';

Pluggable database altered.

Connect to the target cdb and execute the below.

You can use either nocopy or file_name_convert clause.

SQL> select name, open_mode from V$pdbs;

NAME OPEN_MODE

------------------------------ ----------

PDB$SEED READ ONLY

SQL> create pluggable database HRDEPT using '/oracle/product/12.1/db_1/dbs/hr_unplug.xml'

file_name_convert=('/oracle/product/oradata/fir_cdb/hrdept','/oracle/product/oradata/sec_cdb/hrdept');

Pluggable database created.

SQL> alter pluggable database HRDEPT open;

Warning: PDB altered with errors.

-- HEre we got some error while opening the pdbs..Check the error.

SQL> col message for a50;

SQL> col action for a50;

SQL> select cause, message, action from pdb_plug_in_violations where type = 'ERROR';

CAUSE MESSAGE ACTION

---------- -------------------------------------------------- --------------------------------------------------

VSN not match PDB's version does not match CDB's version: PDB's Either upgrade the PDB or reload the components in

version 12.1.0.1.0. CDB's version 12.1.0.2.0. the PDB.

-- Here the thing is the pdb from which we have unplugged is version 12.1.0.1 , and we pluged it in to version 12.1.0.2

its is also like version upgrade.

-- Check the action.

Run the below to upgrade a particular set of pdb. Here we want to upgrade the pdb HRDEPT

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -c 'HRDEPT' catupgrd.sql

Total Upgrade Time: [0d:1h:20m:28s]

Time: 4836s For PDB(s)

SQL> alter session set container=HRDEPT;

Session altered.

SQL> startup

Pluggable Database opened.

SQL>

SQL> @?/rdbms/admin/utlrp.sql;

SQL> select comp_id, comp_name , version, status from dba_registry;

COMP_ID COMP_NAME VERSION STATUS

------------------------------ ---------------------------------------- ------------------------------ --------------------------------------------

DV Oracle Database Vault 12.1.0.2.0 VALID

APEX Oracle Application Express 4.2.5.00.08 VALID

OLS Oracle Label Security 12.1.0.2.0 VALID

SDO Spatial 12.1.0.2.0 VALID

ORDIM Oracle Multimedia 12.1.0.2.0 VALID

CONTEXT Oracle Text 12.1.0.2.0 VALID

OWM Oracle Workspace Manager 12.1.0.2.0 VALID

XDB Oracle XML Database 12.1.0.2.0 VALID

CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID

CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID

JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID

XML Oracle XDK 12.1.0.2.0 VALID

CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID

APS OLAP Analytic Workspace 12.1.0.2.0 VALID

XOQ Oracle OLAP API 12.1.0.2.0 VALID

RAC Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF

16 rows selected.

-- Now we have unplugged the pdb from 1 cdb and pluged into another cdb. Lets try to open the pbd in the

source pdb.

SQL> select name, open_mode from V$pdbs;

NAME OPEN_MODE

------------------------------ ----------

PDB$SEED READ ONLY

SAL_PDB MOUNTED

HRDEPT MOUNTED

SQL> alter pluggable database HRDEPT open;

alter pluggable database HRDEPT open

ERROR at line 1:

ORA-65086: cannot open/close the pluggable database

After a PDB is unplugged, it remains in the CDB with an open mode of MOUNTED and a status of UNPLUGGED.

The only operation you can perform on an unplugged PDB is DROP PLUGGABLE DATABASE, which will remove it from the CDB.

You must drop the PDB before you can plug it into the same CDB or another CDB.

SQL> drop pluggable database hrdept;

Pluggable database dropped.

SQL> create pluggable database hrdept using '/oracle/product/12.1/db_1/dbs/hr_unplug.xml' nocopy;

Pluggable database created.

SQL> alter pluggable database hrdept open;

Pluggable database altered.

SQL> select name, open_mode from V$pdbs;

NAME OPEN_MODE

------------------------------ ----------

PDB$SEED READ ONLY

SAL_PDB MOUNTED

HRDEPT READ WRITE

SQL> alter session set container=hrdept;

Session altered.

SQL> select count(*) from hr_admin.emp_record;

COUNT(*)

----------

Now PDB HRDEPT is open with same name in both the Cdbs's (fircdb and seccdb), where both are on the same server

with different versions.

Lets connect to the desire hr_dept pdb.

[oracle@rhel11gr2rac1 admin]$ sqlplus hr_admin/hr_admin@//rhel11gr2rac1.manzoor.com:1522/hrdept

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 24 07:15:50 2015

Last Successful login time: Mon Aug 24 2015 07:14:09 +08:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from V$database;

NAME

---------

FIR_CDB

SQL> show con_name;

CON_NAME

------------------------------

HRDEPT

SQL> exit

[oracle@rhel11gr2rac1 admin]$ sqlplus hr_admin/hr_admin@//rhel11gr2rac1.manzoor.com:1521/hrdept

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 24 07:16:19 2015

Last Successful login time: Sun Aug 23 2015 11:46:28 +08:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from V$database;

NAME

---------

SEC_CDB

SQL> show con_name;

CON_NAME

------------------------------

HRDEPT

Note - Before plugging in a pdb from one cdb to another one .. you can check the compatibility

using the dbms_pdb.check_plug_compatibility function as below.

set serveroutput on

describe

result boolean := FALSE;

begin

result := dbms_pdb.check_plug_compatibility ('the xml file','pdb name');

if result then

dbms_output.put_line ('Compatibility is ok');

else

dbms_output.put_line ('Compatibility is Not ok, check the pdb_plug_in_violations');

end if;

end;

If the above result is compatibility is not ok then verify the

pdb_plug_in_violations view and rectify it.

Refer Me for Oracle

Monday, 12 October 2015

Container (CDB) and Pluggable (PDB) Database

No comments:

Post a Comment

About Me