1.
1.Tnsnames when connecting to either Container or Pluggable instance
The tnsnames.ora should be configured , for eg to add a tns entry for pluggable database PDB_OMF_1
PDB_OMF_1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MFarag-OEL)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb_omf_1)
)
)
Alternatively the easy connect syntax could be used as follow :
SYS@mfarag-oel/PDB_OMF_1>CONN A/A@mfarag-oel/PDB_OMF_1
Connected.
A@mfarag-oel/PDB_OMF_1>conn xx/xx@mfarag-oel/XX
Connected.
XX@mfarag-oel/XX>
Add the following entry to the glogin.sql script under $ORACLE_HOME/sqlplus/admin
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER'>'"
2.Always use OMF pluggable Database when the Container is Pluggable :
SYS@CDB>create pluggable database pdbomf admin user a identified by a ;
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/');
Please note FILE_NAME_CONVERT(filename_pattern ,replacement_filename_pattern) clause is required if you are not using OMF. If you omit this clause, then the database first attempts to use Oracle Managed Files to generate file names. If you are not using Oracle Managed Files, then the database uses the PDB_FILE_NAME_CONVERT initialization parameter to generate file names. If this parameter is not set, then an error occurs.
SYS@CDB>select CON_ID,DBID,GUID,NAME,OPEN_MODE
from V_$CONTAINERS ;
CON_ID DBID GUID
---------- ---------- --------------------------------
NAME OPEN_MODE
------------------------------ ----------
1 1949193435 DD7C48AA5A4504A2E04325AAE80A403C
CDB$ROOT READ WRITE
2 4075234582 EC25D0EC10347332E043CC8DAB0A7AE1
PDB$SEED READ ONLY
3 3255612964 EC25E0FB670B76A8E043CC8DAB0A08D8
PDB READ WRITE
CON_ID DBID GUID
---------- ---------- --------------------------------
NAME OPEN_MODE
------------------------------ ----------
4 3244820336 EC276A999A790163E043CC8DAB0AD357
PDBOMF MOUNTED
.
.
SYS@CDB>alter session set container=pdbomf ;
Session altered.
SYS@CDB>alter pluggable database open ;
Pluggable database altered.
3.Open the Pluggable Database in Restricted mode before Renaming it
SYS@CDB>conn sys@pdb_omf_1 as sysdba
Enter password:
Connected.
SYS@pdb_omf_1>alter pluggable database close ;
SYS@pdb_omf_1>alter pluggable database open restricted;
SYS@pdb_omf_1>alter pluggable database pdbomf rename global_name to pdb_omf_1 ;
4.unplug and Plug a Database :
SYS@CDB1>alter pluggable database y close immediate ;
SYS@CDB1>alter pluggable database y unplug into '/home/oracle/XDESC.xml' ;
SYS@CDB1>drop pluggable database y ;
SYS@CDB1>create pluggable database y using '/home/oracle/XDESC.xml' nocopy ;
5. startup the Pluggable database in single step :
SYS@CDB>select CON_ID,GUID,NAME,OPEN_MODE
from V$containers;
CON_ID GUID NAME
---------- -------------------------------- ------------------------------
OPEN_MODE
----------
1 DD7C48AA5A4504A2E04325AAE80A403C CDB$ROOT
READ WRITE
2 EC25D0EC10347332E043CC8DAB0A7AE1 PDB$SEED
READ ONLY
3 EC2B4C003E221F09E0438636AF0AA57B X
MOUNTED
CON_ID GUID NAME
---------- -------------------------------- ------------------------------
OPEN_MODE
----------
4 EC276A999A790163E043CC8DAB0AD357 PDB_OMF_1
MOUNTED
SYS@CDB>alter pluggable database all open ;
SYS@CDB>select CON_ID,GUID,NAME,OPEN_MODE
from V$containers
CON_ID GUID NAME
---------- -------------------------------- ------------------------------
OPEN_MODE
----------
1 DD7C48AA5A4504A2E04325AAE80A403C CDB$ROOT
READ WRITE
2 EC25D0EC10347332E043CC8DAB0A7AE1 PDB$SEED
READ ONLY
3 EC2B4C003E221F09E0438636AF0AA57B X
READ WRITE
CON_ID GUID NAME
---------- -------------------------------- ------------------------------
OPEN_MODE
----------
4 EC276A999A790163E043CC8DAB0AD357 PDB_OMF_1
READ WRITE
Note : the startup of the Container CDB will place all the pdb in mount status by Defualt
6.Use catcon perl script to run scripts for selective or all pluggable and container database :
E.g Run the awrinfo script to have information about the awr usage under sysaux for all the databases :
$cd $ORACLE_HOME/rdbms/admin
$ perl catcon.pl -u SYS -U SYS -d $ORACLE_HOME/rdbms/admin -l '/home/oracle' -b catblock_output awrinfo.sql
--Confirm that the script run against all the databases :
$cd /home/oracle
$ grep -i "CURRENT CONTAINER" *
catblock_output0.log:==== Current Container = CDB$ROOT ====
catblock_output0.log:==== Current Container = PDB$SEED ====
catblock_output1.log:==== Current Container = XX ====
catblock_output2.log:==== Current Container = PDB_OMF_1 ====
Note : use the -c option tpo specify certain databases to have this script run against
7.How to use non-seed template to create CDB + Pluggable database :
$ dbca -silent -createDatabase -templateName Custom_DB_.dbt -gdbName FOFO -sid FOFO -createAsContainerDatabase true -numberOfPDBs 1 -pdbName pdb_silent -sysPassword <password> -systemPassword <password> -listeners listener
Enter PDBADMIN
Note : the above command used the non-seed Custome_DB_ template to create CDB names FOFO with a PDB database named pdb_silent in silent mode .
8. Modify the Value "_datafile_write_errors_crash_instance" to be FALSE
SQL>alter system set "_datafile_write_errors_crash_instance"=FALSE
**This has to be set at CDB level. This is to allow the Container CDB to continue running if one of its Pdb lost one of the datafiles for any reason
NOTE:This is only Valid for the non system datafiles for the pdb container .
9.How To check if a parameter have been modified within the PDB?
SYS@CDB>column PDB_NAME format a20
SYS@CDB>column NAME format a20
SYS@CDB>column VALUE$ format a20
SYS@CDB>select p.PDB_NAME,s.name,s.value$
from cdb_pdbs p , pdb_spfile$ s
where p.CON_UID=s.PDB_UID
PDB_NAME NAME VALUE$
-------------------- -------------------- --------------------
XX cursor_sharing 'SIMILAR'
YY_NEW cursor_sharing 'SIMILAR'
**Note This could never be checked for non default pdb init parameters valunes using alertlog .
2. Clone a Pluggable Database – 12c Edition
With the introduction of Pluggable Databases in Oracle Database 12c, there is going to be questions on how to clone a pluggable database (PDB) either from the seed PDB or an existing PDB. In the testing that I have done both in the beta program and with the general release creating a clone is fairly simple depending on the settings of your database. The simplest way is using Oracle Managed Fils (OMF).
Using OMF, all we need to do is specify the PDB name that we want to create.
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;
If we are not using the OMF, the SQL syntax is bit drawn out; yet still simple enough to use for creating a PDB.
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
PATH_PREFIX = '/oracle/app/oradata'
FILE_NAME_CONVERT = ('/oracle/app/oradata/ora12c/pdb1', '/oracle/app/oradata/ora12c/pdb2');
You will notice the difference in the syntax is that we needed to add PATH_PREFIX and FILE_NAME_CONVERT to the CREATE PLUGGABLE DATABASE sql. The PATH_PREFIX option is used to ensure that all relative directory object paths and relative paths contain certain initialization parameters associated with the PDB are treated as relative to the specified directory or subdirectories. The FILE_NAME_CONVERT option is used to specify the target location of the files based on the file locations of the source.
There are multiple ways of performing the SQL commands above. Oracle has done a very good job at enabling their tools to perform the tasks of cloning a PDB. Cloning can be done from the SQL*Plus prompt (above commands), Oracle Enterprise Manager 12c Cloud Control and SQLDeveloper. I encourage you to give it a go; I’m positive you will like provisioning a database in under two (2) minutes*!
Enjoy!
https://dbasolved.com/2013/07/01/clone-a-pluggable-database-12c-edition/
__
D:dbachangeenv>sqlplus sys/oracle123@testuat12c as sysdba
CREATE PLUGGABLE DATABASE PtestDEV FROM PtestUAT;
SQL> select con_id,name, OPEN_MODE from v$pdbs;
CON_ID NAME
---------- ----------------------------------------------------------
OPEN_MODE
--------------------
2 PDB$SEED
READ ONLY
3 PtestUAT
READ WRITE
4 PtestDEV
MOUNTED
alter pluggable database PtestDEV close;
alter pluggable database PtestDEV open read write;