How to recovery PDB when PDB database is dropped in Oracle

 

How to recovery PDB when PDB database is dropped :)

[oracle@rac01 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 11 12:20:24 2025
Version 21.13.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL>
SQL>
SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL>
SQL>
SQL> drop pluggable database PDB1 including datafiles;

Pluggable database dropped.




SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB2                           READ WRITE NO

============Alert log file contents======

2025-01-11T12:20:48.610884+01:00
alter pluggable database PDB1 close
2025-01-11T12:20:48.621225+01:00
PDB1(3):Pluggable database PDB1 closing
PDB1(3):JIT: pid 92739 requesting stop
PDB1(3):Closing sequence subsystem (204729984896).
PDB1(3):Buffer Cache flush started: 3
PDB1(3):Buffer Cache flush finished: 3
Pluggable database PDB1 closed
Completed: alter pluggable database PDB1 close
2025-01-11T12:21:10.490974+01:00
drop pluggable database PDB1 including datafiles
2025-01-11T12:21:10.596417+01:00
Deleted file +DATA/CDBAM/PDB1/undo_seed01.dbf
Deleted file +DATA/CDBAM/PDB1/users01.dbf
Deleted file +DATA/CDBAM/PDB1/temp01.dbf
Deleted file +DATA/CDBAM/PDB1/sysaux01.dbf
Deleted file +DATA/CDBAM/PDB1/system01.dbf
2025-01-11T12:21:10.640983+01:00
Stopped service pdb1
Completed: drop pluggable database PDB1 including datafiles

======Execute the below script to recover PDB1 ======

Note: Never use RESTORE command as per Oracle support suggestion

run
{
set newname for datafile 20 to '+DATA/CDBAM/PDB1/system01.dbf';
set newname for datafile 21 to '+DATA/CDBAM/PDB1/sysaux01.dbf';
set newname for datafile 22 to '+DATA/CDBAM/PDB1/users01.dbf';
set newname for datafile 23 to '+DATA/CDBAM/PDB1/undo_seed01.dbf';
recover pluggable database pdb1 until time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')" 
auxiliary destination '/applications/oracle/d19.3.0/CDBAM_BKP/AUX/';
}

log for recovery :-

[oracle@rac01 AUX]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jan 11 14:07:44 2025
Version 19.23.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDBAM (DBID=1604254345)

RMAN>

RMAN> run
{
set newname for datafile 20 to '+DATA/CDBAM/PDB1/system01.dbf';
set newname for datafile 21 to '+DATA/CDBAM/PDB1/sysaux01.dbf';
set newname for datafile 22 to '+DATA/CDBAM/PDB1/users01.dbf';
set newname for datafile 23 to '+DATA/CDBAM/PDB1/undo_seed01.dbf';
2> recover pluggable database pdb1 until time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '/applications/oracle/d19.3.0/CDBAM_BKP/AUX/';
}3> 4> 5> 6> 7> 8>

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting recover at 11-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='aiAi'

initialization parameters used for automatic instance:
db_name=CDBAM
db_unique_name=aiAi_pitr_pdb1_CDBAM
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/applications/oracle/d19.3.0/oracle_base
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=6144M
processes=200
db_create_file_dest=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/
log_archive_dest_1='location=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance CDBAM

Oracle instance started

Total System Global Area    6442447480 bytes

Fixed Size                     9192056 bytes
Variable Size               1124073472 bytes
Database Buffers            5301600256 bytes
Redo Buffers                   7581696 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 11-JAN-25
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=211 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/c-1604254345-20250111-01
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/c-1604254345-20250111-01 tag=TAG20250111T121656
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/controlfile/o1_mf_mr4v9ltv_.ctl
Finished restore at 11-JAN-25

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  5 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  6 to new;
set newname for datafile  20 to new;
set newname for datafile  21 to new;
set newname for datafile  22 to new;
set newname for datafile  23 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 5, 3, 6, 20, 21, 22, 23;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 11-JAN-25
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/0i3evgde_18_1_1
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/0i3evgde_18_1_1 tag=TAG20250111T121646
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00020 to +DATA/CDBAM/PDB1/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00021 to +DATA/CDBAM/PDB1/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00022 to +DATA/CDBAM/PDB1/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00023 to +DATA/CDBAM/PDB1/undo_seed01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/0k3evgdm_20_1_1
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/0k3evgdm_20_1_1 tag=TAG20250111T121646
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 11-JAN-25

datafile 1 switched to datafile copy
input datafile copy RECID=29 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_system_mr4v9vv6_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=30 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_undotbs1_mr4v9vvc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=31 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_mr4v9vv9_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=32 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_users_mr4v9vvg_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  5 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  6 online";
sql clone 'PDB1' "alter database datafile
 20 online";
sql clone 'PDB1' "alter database datafile
 21 online";
sql clone 'PDB1' "alter database datafile
 22 online";
sql clone 'PDB1' "alter database datafile
 23 online";
#recover pdb
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS" pluggable database
 'PDB1'  delete archivelog;
#open in read write mode
sql clone 'alter database open resetlogs';
#unplug dropped pdb into temp file
sql clone "alter pluggable database PDB1 unplug into ''
/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml''";
#create pdb using temp file of recovered pdb
sql "create pluggable database PDB1 using ''
/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml'' nocopy tempfile reuse";
alter pluggable database PDB1 open;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  5 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  6 online

sql statement: alter database datafile  20 online

sql statement: alter database datafile  21 online

sql statement: alter database datafile  22 online

sql statement: alter database datafile  23 online

Starting recover at 11-JAN-25
using channel ORA_AUX_DISK_1

Executing: alter database datafile 2, 4, 7, 15 offline
Executing: alter database datafile 16, 17, 18, 19 offline
starting media recovery

archived log for thread 1 with sequence 269 is already on disk as file +ARCH/cdbam/arch/1_269_1189847369.dbf
archived log for thread 1 with sequence 270 is already on disk as file +ARCH/cdbam/arch/1_270_1189847369.dbf
archived log file name=+ARCH/cdbam/arch/1_269_1189847369.dbf thread=1 sequence=269
archived log file name=+ARCH/cdbam/arch/1_270_1189847369.dbf thread=1 sequence=270
media recovery complete, elapsed time: 00:00:01
Finished recover at 11-JAN-25

sql statement: alter database open resetlogs

sql statement: alter pluggable database PDB1 unplug into ''/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml''

sql statement: create pluggable database PDB1 using ''/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml'' nocopy tempfile reuse

Statement processed

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml deleted
auxiliary instance file /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_mr4v9vv9_.dbf deleted
auxiliary instance file /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/controlfile/o1_mf_mr4v9ltv_.ctl deleted
Finished recover at 11-JAN-25

RMAN>

PDB1 recovered successfully.

[oracle@rac01 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 11 13:00:24 2025
Version 21.13.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0


SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
	 3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

Oracle Multitenant / Container Database -- PDB / CDB

           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.

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 

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 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



How 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;

Connect to a PDB directly with ORACLE_PDB_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.

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.

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 PDB
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 

 

Backup Commands : 

RMAN> BACKUP DATABASE ROOT;

RMAN> BACKUP PLUGGABLE DATABASE pdb1, pdb2;

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;

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 ;

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.

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

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;

  

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