Oracle Multitenant/ Container Database
1) In the previous release the character set for the root container and all pluggable databases associated with it had to be the same. This could limit the movement of PDBs and make consolidation difficult where a non-standard character set was required.
3) Background processes and memory allocation are at CDB level only
The SEED FILE_NAME_CONVERT Clause
The PDB_FILE_NAME_CONVERT Initialization Parameter
9) A PDB would have its SYSTEM, SYSAUX, TEMP tablespaces.It can also contains other user created tablespaces in it
10) For revoking privilege from common user
If the current container is the root:
/ Specify CONTAINER = CURRENT to revoke a locally granted system privilege, object privilege, or role from a common user or common role. The privilege or role is revoked from the user or role only in the root. This clause does not revoke privileges granted with CONTAINER = ALL.
/ Specify CONTAINER = ALL to revoke a commonly granted system privilege, object privilege on a common object, or role from a common user or common role. The privilege or role is revoked from the user or role across the entire CDB. This clause can revoke only a privilege or role granted with CONTAINER = ALL from the specified common user or common role. This clause does not revoke privileges granted locally with CONTAINER = CURRENT. However, any locally granted privileges that depend on the commonly granted privilege being revoked are also revoked.
If you omit this clause, then CONTAINER = CURRENT is the default.
11) When we restart CDB , be default Seed will be in Read only mode and all PDB will be in mount stage
Hoe to check if database is Container Database / Cdb:
Undo Management :
Interesting behavior in 12.1.0.1 DB of creating an undo tablespace in a PDB. With the new Multitenant architecture the undo tablespace resides at the CDB level and PDBs all share the same UNDO tablespace. When the current container is a PDB, an attempt to create an undo tablespace fails without returning an error.
A PDB can either have its owner temporary tablespace, or if it is created without a temporary tablespace, it can share the temporary tablespace with the CBD.
User and Role Management : Create User c##pwtest identified by password container=all ;
ALTER PLUGGABLE DATABASE OPEN issued from that PDb
In Oracle Database 12c Release 2 (12.2) a PDB can use a different character set to the CDB, provided the character set of the CDB is AL32UTF8, which is now the default character set when using the Database Configuration Assistant
2) When we open Container database seed database is in read only mode and Pdb are in mount stage
2) When we open Container database seed database is in read only mode and Pdb are in mount stage
3) Background processes and memory allocation are at CDB level only
4) Log switches occur only at the multitenant container database (CDB) level.
5) Instance recovery is always performed at the CDB level.
6) Patching and Upgrades are done at Cdb level
7) User creation can be done at root level and Pdb level . User created in root is considered global and created in all pdb. Though schema objects can vary in all pdb
8) While creating CDB we can place PDB and Seed files in different directory using below
The PDB_FILE_NAME_CONVERT Initialization Parameter
9) A PDB would have its SYSTEM, SYSAUX, TEMP tablespaces.It can also contains other user created tablespaces in it
10) For revoking privilege from common user
If the current container is the root:
/ Specify CONTAINER = CURRENT to revoke a locally granted system privilege, object privilege, or role from a common user or common role. The privilege or role is revoked from the user or role only in the root. This clause does not revoke privileges granted with CONTAINER = ALL.
/ Specify CONTAINER = ALL to revoke a commonly granted system privilege, object privilege on a common object, or role from a common user or common role. The privilege or role is revoked from the user or role across the entire CDB. This clause can revoke only a privilege or role granted with CONTAINER = ALL from the specified common user or common role. This clause does not revoke privileges granted locally with CONTAINER = CURRENT. However, any locally granted privileges that depend on the commonly granted privilege being revoked are also revoked.
If you omit this clause, then CONTAINER = CURRENT is the default.
11) When we restart CDB , be default Seed will be in Read only mode and all PDB will be in mount stage
Hoe to check if database is Container Database / Cdb:
Select CDB from v$database ;
Undo Management :
Interesting behavior in 12.1.0.1 DB of creating an undo tablespace in a PDB. With the new Multitenant architecture the undo tablespace resides at the CDB level and PDBs all share the same UNDO tablespace. When the current container is a PDB, an attempt to create an undo tablespace fails without returning an error.
In Oracle 12c Release 2 each container in an instance can use its own undo tablespace. This new undo management mechanism is called local undo mode, whilst that of previous releases is now known as shared undo mode. Local undo mode is the default mode in newly created databases, so you will probably only need to consider switching undo modes for upgraded instances
Prior to Oracle 19.9, setting the UNDO_RETENTION parameter in the root container meant that value was set for all pluggable databases also. From Oracle 19.9 onward this is not the case. Now there are two ways to set the parameter in the root container.
In addition, the UNDO_RETENTION parameter can be set separately in each PDB, provided local undo is being used.
column property_name format a30
column property_value format a30
select property_name, property_value
from database_properties
where property_name = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE
SQL>
select con_id, tablespace_name
from cdb_tablespaces
where contents = 'UNDO'
order by con_id;
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 UNDOTBS1
3 UNDOTBS1
SQL>
conn / as sysdba
shutdown immediate;
startup upgrade;
alter database local undo off;
shutdown immediate;
startup;
conn / as sysdba
-- Just the root container.
alter system set undo_retention=3000;
-- The root container and all PDBs.
alter system set undo_retention=3000 container=all;
Tablespace and Datafile Management :
Management of the temporary tablespace in a CDB is unchanged from that of a non-CDB database.
A PDB can either have its owner temporary tablespace, or if it is created without a temporary tablespace, it can share the temporary tablespace with the CBD.
ALTER SESSION SET CONTAINER = pdb1;
CREATE TABLESPACE dummy
DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/dummy01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1M;
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE users;
ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE temp;
SQL> SELECT CON_ID,TABLESPACE_NAME FROM CDB_TABLESPACES WHERE CON_ID=1;
SQL> SELECT CON_ID,FILE_NAME FROM CDB_DATA_FILES WHERE CON_ID=1;
SQL> SELECT CON_ID,FILE_NAME FROM CDB_TEMP_FILES WHERE CON_ID=1;
select con_id, tablespace_name
from cdb_tablespaces
where contents = 'UNDO'
order by con_id;
from cdb_tablespaces
where contents = 'UNDO'
order by con_id;
Connect to a PDB directly with ORACLE_PDB_SID
'
This is when you set environmental variable ORACLE_PDB_SID on top of ORACLE_SID
It is done with a trigger called DBMS_SET_PDB.
This trigger most likely comes in with the April 2019 RUs for Oracle Database 19c and 18c. It does not exist in Oracle Database 12.2.0.1 or Oracle 12.1.0.2.
The ORACLE_SID defines to which SID you connect to. ORACLE_PDB_SID does not work without the correct ORACLE_SID
It is an AFTER EVENT LOGON trigger. The trigger fires when you logon as SYS or SYSTEM
SQL> select trigger_body from dba_triggers where trigger_name = 'DBMS_SET_PDB'
Saving STATE of PDB
The PDB defaults to a closed state when the CDB first starts up .Oracle has introduced the “Save state” option for the alter pluggable database command.This command will take the current state of the PDB and preserve that mode after the CDB is restarted.
the save state command is one that is instance specific so if you are in a RAC environment be sure to use the “instances=all” clause when placing the PDB into a specific state.
Alter plugable database abc save state instances=all ;
alter pluggable database all save state;
select con_name , instance_name , state from dba_pdb_saved_states ;
select con_name , state from CDB_PDB_SAVED_STATES ;
The state is only saved and visible in the DBA_PDB_SAVED_STATES view if the container is in READ ONLY or READ WRITE mode. The ALTER PLUGGABLE DATABASE ... SAVE STATE command does not error when run against a container in MOUNTED mode, but nothing is recorded, as this is the default state after a CDB restart.
--> Open pdbs automatically and save its state
alter pluggable database PDB1 save state;
select con_name, state from dba_pdb_saved_states;
alter pluggable database PDB2 discard state;
As this feature is not available in 12.1.0.1 , we can create a trigger to open the PDBs automatically, with container database startup.
CREATE TRIGGER open_all_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
END ;
/
--> Open Services along with
alter pluggable database pdborcl open services=('pdborcl_srv');
alter pluggable database pdborcl open services=All;
Reference : 12c services will not start while manually restart pdb (Doc ID 2006021.1)
Removing Pdb from Restricted Mode :
Ideally Pdb will be in restricted mode due to existing violations . Please fix violation and restart PDB
1) Check PDB status.
select INST_ID,NAME,OPEN_MODE,RESTRICTED from gv$pdbs order by 1,2;
2) Check PDB_PLUG_IN_VIOLATIONS.
select status, message, action from pdb_plug_in_violations where status !='RESOLVED';
3)Bounce all instances and pdbs.
ALTER PLUGGABLE DATABASE ALL EXCEPT PDB001 OPEN READ WRITE;
ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE FORCE;
User and Role Management :
Create User c##pwtest identified by password container=all ;
grant create session to c#pwtest container=all ;
connect c#pwtest/password
alter user c#pwtest identified by password ;
alter user c#pwtest identified password contrainer=all ;
select username , profile , common , con_id from cdb_users where username='C##a1';
select username,profile,common,con_id from cdb_users where username='C##AA1';
SELECT ROLE,ROLE_ID,COMMON,CON_ID FROM CDB_ROLES WHERE ROLE='C##ROLE1';
SQL> select username, con_id from cdb_users where username='C##AA1';
SQL> select d.username, c.con_id,p.name from dba_users d, cdb_users c , v$pdbs p where d.username='C##AA1' and d.username=c.username and p.con_id=c.con_id;
Create Role C##ROLE1 CONTAINER=ALL ;
Create Role Lrole container=current ;
Grant C##ROLE1 to c##user1 ;
GRANT SELECT ON SYS.V_SESSION TO C##USER2 container=all ;
There are some predefined Roles that can be visible using Below
SELECT ROLE, common, con_id FROM cdb_roles order by role , con_id ;
select role , commn from dba_roles order by role , con_id ;
select * from session_roles ;
COL grantee format a12
col privilege format a30
select grantee , privilege , common , con_id from cdb_sys_privs where grantee='C##USER1' ORDER BY 1,3 ;
#Enabling Common User to view data of specific Pdb
COL USERNAME format a10
col default_attrformat a7
col owner format a6
col object_name format all
col all_comtainer format a3
column container_name format a10
col con_id format 999
set pages 100
set line 200
select username , default_attr , owner , object_name , all_containers , container_name , con_id form cdb_container_data
where username not in ( 'GDADMIN_INTERNAL','APPQOSSYS','DBSNMP') ORDER BY USERNAME ;
ALTER USER C##USER2 SET CONTAINER_DATA = (CDB$ROOT , PDB2) FOR V_SESSION CONTAINER=CURRENT ;
Starting / Shutting down PDB
ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from the root
ALTER DATABASE OPEN issued from that PDBALTER PLUGGABLE DATABASE OPEN ALL ISSUED from the root
ALTER PLUGGABLE DATABASE OPEN issued from that PDb
alter pluggable database PDB2 close immediate ;
alter pluggable database PDB2 open ;
alter pluggable database test1_tech close;
alter pluggable database test1_tech close immediate;
alter pluggable database test1_tech open;
alter pluggable database test1_tech open read only ;
alter pluggable database test1_tech open force;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;
ALTER PLUGGABLE DATABASE OPEN FORCE;
ALTER PLUGGABLE DATABASE NOLOGGING;
ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING;
STARTUP OPEN
STARTUP OPEN READ ONLY
STARTUP RESTRICT OPEN READ ONLY
alter pluggable database all close immediate;
alter pluggable database all open;
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE <pdb-name-clause> CLOSE [IMMEDIATE];
We can create startup trigger to start all PDB
CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/
Backup and recovery commands :
1) Backup and recovery can be done at CDB and PDB level
1) Backup and recovery can be done at CDB and PDB level
Backup Commands :
RMAN> BACKUP DATABASE ROOT;
RMAN> BACKUP PLUGGABLE DATABASE pdb1, pdb2;
RMAN> BACKUP PLUGGABLE DATABASE pdb1 plus archivelog ;
-- It will skip archivelog backup when connected to Pdb
RMAN> BACKUP DATABASE;
RMAN> BACKUP TABLESPACE system, sysaux, users;
RMAN> BACKUP TABLESPACE pdb1:system, pdb1:sysaux, pdb1:users, pdb2:system;
list backup of PLUGGABLE DATABASE pdb1 ;
list backup of tablespace PDB1:users ;
Restore Commands :
RESTORE TABLESPACE pdb2:system ;
RUN {
SHUTDOWN IMMEDIATE; # use abort if this fails
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}
RUN {
SHUTDOWN IMMEDIATE; # use abort if this fails
STARTUP MOUNT;
RESTORE DATABASE ROOT;
RECOVER DATABASE ROOT;
# Consider recovering PDBs before opening.
ALTER DATABASE OPEN;
}
RUN {
ALTER PLUGGABLE DATABASE pdb1 CLOSE;
SET UNTIL TIME "TO_DATE('23-DEC-2013 12:00:00','DD-MON-YYYY HH24:MI:SS')";
RESTORE PLUGGABLE DATABASE pdb1;
RECOVER PLUGGABLE DATABASE pdb1;
ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
}
RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
UNTIL TIME "TO_DATE('01-JAN-2013 15:00', 'DD-MON-YYYY HH24:MI')"
AUXILIARY DESTINATION '/u01/aux'
REMAP TABLE 'TEST'.'T1':'T1_PREV';
RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
UNTIL SCN 5695703
AUXILIARY DESTINATION '/u01/aux'
DATAPUMP DESTINATION '/u01/export'
DUMP FILE 'test_t1_prev.dmp'
NOTABLEIMPORT;
Datapump :
It’s important to note that Data Pump doesn’t allow you to use a directory that’s owned by the root container or CDB for imports and exports. You also can’t use one that’s owned by a different PDB.
Instead, you need to create a directory under the PDB. The PDB must own the directory for you to use Data Pump export and import functionality.
Using a Data Pump export for PDBs is identical to using a Data Pump export for a non-CDB database. The only difference in using the Data Pump export utility for a PDB is that you must use a connect identifier, or Transparent Network Substrate (TNS) alias, in the export command prompt when you initiate the export. This step ensures that you’re initiating the Data Pump export for a specific PDB.
Parameter check :
select name , value from v$nls_parameters where parameter='NLS_CHARACTERSET';
set linesize 300
column name format a30
column value format a30
select b.name , a.name , a.value$ "Value" from pdb_spfile$ a , v$pdbs b where a. pbd_uid=b.con_uid and a.name in ('cpu_cont','sga_target') order by 1,2 ;
alter session set CONTAINER=PDB1;
select value, ISPDB_MODIFIABLE from v$system_parameter where name='db_recover_file_dest_size';
Managing Services :
SQL> connect /@CDB1 as sysdba
SQL> alter session set container=pdb1;
SQL> exec dbms_service.create_service(service_name=>'pdb1_RW',network_name=>'pdb1_RW');
SQL> exec dbms_service.start_service(service_name=>'pdb1_RW');
SQL> alter session set container=cdb$root;
select name,name_hash,network_name,creation_date,pdb from cdb_services order by con_id,service_id;
select name,name_hash,network_name,creation_date,con_id from v$active_services order by con_id,service_id;
select name,name_hash,network_name,creation_date,con_id from v$active_services order by con_id,service_id;
srvctl add service -db CDB01 -service TEST_NEW -pdb TEST
srvctl modify service -db CDB01 -service TEST_NEW -pdb TEST
srvctl remove service -db CDB01 -service TEST_NEW
BEGIN
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'TEST_NEW',
network_name => 'TEST_NEW.com');
END;
/
BEGIN
DBMS_SERVICE.DELETE_SERVICE(
service_name => 'TEST_NEW');
END;
/
SQL> select name, con_id from v$active_services order by 1;
Adding service with domain name :
-- on primary
srvctl add service -db CDB01_fra3gh -service hr.oracle.com -pdb pdb01 -role primary
srvctl start service -db CDB01_fra3gh -service hr.oracle.com
lsnrctl services
...
Service "hr.oracle.com" has 1 instance(s).
...
-- on standby
srvctl add service -db CDB01_amsad1 -service hr.oracle.com -pdb pdb01 -role primary
srvctl start service -db CDB01_amsad1 -service hr.oracle.com
lsnrctl services
...
Service "hr.oracle.com" has 1 instance(s).
...
Drop Plugable Database :
drop pluggable database pdb3_test including datafiles;
Rename Plugable Database :
alter pluggable database TEST rename global_name to TEST1;
Pdb checks :
show con_name
show con_id
show pdbs
alter session set container=cdb$root;
col con_name for a10
col instance_name for a10
select con_name , instance_name , state from dba_pdb_saved_states ;
select name , open_mode from v$pdbs ;
COLUMN NAME FORMAT AI5
COLUMN RESTRICTED FORMAT A10
COLUMN OPEN_TIME FORMAT A130
SELECT NAME , OPEN_MODE , RESTRICTED , OPEN_TIME FROM $PDBS ;
COLUMN NAME FORMAT A8
SELECT name , con_id , dbid , con_uid , guid from v$containers order by con_id ;
COLUMN PDB_NAME FORMAT AIS
SELECT PDB_ID , PDB_NAME , STATUS FROM DBA_PDDBS order by pdb_id ;
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;
COLUMN name FORMAT A30
SELECT name, pdb
FROM v$services
ORDER BY name;
SQL> select tablespace_name, con_id from cdb_tablespaces where con_id =1;
SQL> select file_name, con_id from cdb_data_files where con_id=1;
SQL> select file_name, con_id from cdb_temp_files where con_id =1 ;
col name for a10
col owner for a8
col object_type for a10
select t.con_id,p.name,t.owner,t.object_type,count(*) from cdb_objects t ,v$containers p
where p.con_id = t.con_id and t.status = 'INVALID' group by t.con_id,p.name,t.owner,t.object_type;
-- Size of Pluggable Database :
select con_id, name, open_mode, total_size/1024/1024/1024 "PDB_SIZE_GB" from v$pdbs;
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;
SQL> connect / as sysdba
Connected.
SQL> get show_orclpdb1_params.sql
1 select pdb.dbid, pdb.con_uid, sp.name, sp.value$
2 from v$pdbs pdb, pdb_spfile$ sp
3 where pdb.name = 'ORCLPDB1'
4 and pdb.con_uid=sp.pdb_uid
5* order by sp.name
SQL> /
Flashback Cdb :
SHUTDOWN IMMEDIATE
STARTUP MOUNT
FLASHBACK DATABASE to TIME “TO_DATE (‘08/20/12’ , ‘MM/DD/YY’)”;
ALTER DATABASE OPEN RESETLOGS;
ALTER PLUGGABLE DATABASE ALL OPEN;
Note :
A. Flashback must be enabled at cdb level
B. To enable flashback database, the CDB must be mounted.
c. The DB_FLASHBACK RETENTION_TARGET parameter must be set to enable
flashback of the CDB.
flashback of the CDB.
d. A CDB can be flashed back specifying the desired target point in time or an SCN, but not a restore point.
Creating new Pdb
$ sqlplus sys@cdb1 sys as sysdba
SQL> ALTER SYSTEM SET db_create_file_dest = '/u01/app/oracle/oradata';
SQL> CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb1adm IDENTIFIED BY *******;
OR
SQL> CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb1adm IDENTIFIED BY *******
2 CREATE_FILE_DEST='/u01/app/oracle/oradata';
OR
SQL> CREATE PLUGGABLE DATABASE repotestpdb ADMIN USER pdbadm IDENTIFIED BY *******
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');
OR
SQL> ALTER SESSION SET PDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdb1/pdbseed/'
,'/u01/app/oracle/oradata/cdb1/pdb3/';
SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1 ROLES=(DBA);
or
Creating a CDB: Using SQL*Plus
1) Instance startup:
• Set ORACLE_SID=CDB1
• Set in initCDB1.ora:
• Set CONTROL_FILES to CDB control file names.
• Set DB_NAME to CDB name.
• Set ENABLE_PLUGGABLE_DATABASE to TRUE.
SQL> CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT
2) Create the database:
• CDB$ROOT container
• PDB$SEED pluggable database
SQL> CREATE DATABASE CDB1 ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT ('/oracle/dbs','/oracle/seed');
3) Run the catcdb.sql script.
Cloning PDB
CDB1
$ sqlplus sys@cdb1 sys as sysdba
SQL> ALTER PLUGGABLE DATABASE PDB1 CLOSE;
SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ ONLY;
SQL> CREATE PLUGGABLE DATABASE PDB2 FROM PDB1
2 STORAGE UNLIMITED TEMPFILE REUSE
3 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/', '/u01/app/oracle/oradata/cdb1/pdb2');
PDB is in LOCAL UNDO MODE - HOT CLONING
SQL> CREATE PLUGGABLE DATABASE PDB3 FROM PDB1
2 STORAGE UNLIMITED TEMPFILE REUSE
3 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/', '/u01/app/oracle/oradata/cdb1/pdb3');
Plugging In an Unplugged PDB
UNPLUG
SQL> CONNECT SYS@CDB1 AS SYSDBA
SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE;
SQL> ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/u01/app/oracle/oradata/cdb1/pdb1/pdb1.xml';
OR
SQL> ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/u01/app/oracle/oradata/cdb1/pdb1/pdb1.pdb';
PLUG-IN
There are multiple options to Plug . Copy,Nocopy & Move
Before We plug In we need to check for violations
set serveroutput on
DECLARE
compatible BOOLEAN := FALSE;
BEGIN
compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/u01/app/oracle/oradata/pdb1.xml');
if compatible then
DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? YES');
else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? NO');
end if;
END;
/
NOTE: If the scripts returns NO, then check PDB_PLUG_IN_VIOLATIONS view
SQL> col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='PDB1';
Resolve all the violation reported by the above command before proceeding.
SQL> CONNECT SYS@CDB2 as sysdba
SQL> CREATE PLUGGABLE DATABASE pdb2 USING '/u01/app/oracle/oradata/cdb2/pdb2/pdb1.xml'
2 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb2/pdb2/');
OR
SQL> CREATE PLUGGABLE DATABASE pdb2 USING '/u01/app/oracle/oradata/cdb2/pdb2/pdb1.pdb'
2 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb2/pdb2/');
SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
Adopting a Non-CDB as a PDB
DB19C NON-CDB
sqlplus / as sysdba
– SQL> shutdown immediate
– SQL> startup mount exclusive
– SQL> alter database open read only;
– SQL> exec dbms_pdb.describe(pdb_descr_file=>'/u01/app/oracle/oradata/db19cnoncdb/noncdb.xml');
– SQL> shutdown immediate
CDB1
sqlplus / as sysdba
– SQL> create pluggable database db19cpdb as clone using '/ u01/app/oracle/oradata/db19cnoncdb/noncdb.xml'
2 file_name_convert=('/u01/app/oracle/oradata/db19cnoncdb','/u01/app/oracle/oradata/db19cpdb') copy;
– SQL> alter pluggable database db19cpdb open;
sqlplus sys@db19cpdb as sysdba
– SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
PDB Snapshots
– PDB snapshot is a point-in-time copy of a PDB
– Maximum number of snapshots MAX_PDB_SNAPSHOTS = 8 (default)
NONE : The PDB does not support snapshots.
MANUAL : The PDB supports snapshots, but they are only created manually requested.
EVERY n HOURS : A snapshot is automatically created every "n" hours. Where "n" is between 1 and 1999.
EVERY n MINUTES : A snapshot is automatically created every "n" minutes. Where "n" is between 1 and 2999.
SQL> CONNECT / AS SYSDBA
SQL> CREATE PLUGGABLE DATABASE PDB2 ADMIN USER PDBADMIN IDENTIFIED BY ***********
FILE_NAME_CONVERT=('pdbseed','pdb2')
SNAPSHOT MODE EVERY 24 HOURS;
SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN;
SQL> ALTER PLUGGABLE DATABASE PDB2 SAVE STATE;
SQL> SELECT con_id, con_name,snapshot_name,
snapshot_scn, full_snapshot_path
FROM cdb_pdb_snapshots
ORDER BY con_id, snapshot_scn;
CON_ID CON_NAME SNAPSHOT_NAME SNAPSHOT_SCN FULL_SNAPSHOT_PATH
---------- ---------- ------------------------------ ------------ -------------------------------------------
4 PDB2 SNAP_688979926_996491289 1764864 /u02/oradata/snap_688979926_1764864.pdb
Recovering From a PDB Snapshot
SQL> CREATE PLUGGABLE DATABASE PDB2COPY FROM pdb2 USING SNAPSHOT SNAP_688979926_996491631;
OR
SQL> CREATE PLUGGABLE DATABASE PDB2COPY FROM pdb2 USING SNAPSHOT SNAP_688979926_996491631
SNAPSHOT MODE EVERY 24 HOURS;
SQL> ALTER PLUGGABLE DATABASE DROP SNAPSHOT SNAP_688979926_996491289;
Resource Plan for PDB database
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'TEST_CDB_PLAN';
DECLARE
l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.create_cdb_plan(
plan => l_plan,
comment => 'A test CDB resource plan');
DBMS_RESOURCE_MANAGER.create_cdb_plan_directive(
plan => l_plan,
pluggable_database => 'pdb1',
shares => 3,
utilization_limit => 100,
parallel_server_limit => 100);
DBMS_RESOURCE_MANAGER.create_cdb_plan_directive(
plan => l_plan,
pluggable_database => 'pdb2',
shares => 3,
utilization_limit => 100,
parallel_server_limit => 100);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
DECLARE
l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.update_cdb_plan_directive(
plan => l_plan,
pluggable_database => 'pdb3',
new_shares => 1,
new_utilization_limit => 100,
new_parallel_server_limit => 100);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
DECLARE
l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.delete_cdb_plan_directive(
plan => l_plan,
pluggable_database => 'pdb3');
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
DECLARE
l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.update_cdb_default_directive(
plan => l_plan,
new_shares => 1,
new_utilization_limit => 80,
new_parallel_server_limit => 80);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
DECLARE
l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.update_cdb_autotask_directive(
plan => l_plan,
new_shares => 1,
new_utilization_limit => 75,
new_parallel_server_limit => 75);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
DECLARE
l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.delete_cdb_plan(plan => l_plan);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
COLUMN plan FORMAT A30
COLUMN comments FORMAT A30
COLUMN status FORMAT A10
SET LINESIZE 100
SELECT plan_id,
plan,
comments,
status,
mandatory
FROM dba_cdb_rsrc_plans
WHERE plan = 'TEST_CDB_PLAN';
COLUMN plan FORMAT A30
COLUMN pluggable_database FORMAT A25
SET LINESIZE 100
SELECT plan,
pluggable_database,
shares,
utilization_limit AS util,
parallel_server_limit AS parallel
FROM dba_cdb_rsrc_plan_directives
WHERE plan = 'TEST_CDB_PLAN'
ORDER BY pluggable_database;
Other Commands :
Alter pluggable database PDB1 default temporary tablespace pdb1_temp1 ;
Alter pluggable database PDB1 set default bigfile tablespace ;
ALter pluggable database PDB1 STORAGE(MAXSIZE UNLIMITED) ;
ALTER PLUGGABLE DATABASE pdb1 storage unlimited ;
Alter pluggable database pdb1 enable force logging ;
References :
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/overview-of-the-multitenant-architecture.html#GUID-5C339A60-2163-4ECE-B7A9-4D67D3D894FB
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/overview-of-the-multitenant-architecture.html#GUID-D0F40745-FC70-4BE0-85D3-3745DE3312AC
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/pdb/pdb_basics/pdb_basics.html
How to Change Initialization Parameters at PDB Level on Standby Database (Doc ID 2903547.1)
How to Unplug and Plugin PDB (Doc ID 2728046.1)
Character Sets For CDB And PDB in 12.2 (Doc ID 2231602.1)
Moving PDB from Lower Release Update (or Patch Set Update, Bundle Patch) on Same or Higher DB Version (Doc ID 2847234.1)
How to set the Undo_tablespace in PDB in Physical Standby RAC Database. (Doc ID 2726173.1)
No comments:
Post a Comment