Sreenu Oracle DBA

Tuesday, 2 June 2020

Creating or Cloning new pluggable database with existing PDB under same container db and on same host

This document describes how to create or clone new pluggable database with exisitng PDB under same container db and on same host.

In my test case:
Container DB Name: MYCDB
Existing Pluggable DB Name:ANGLES
New Pluggable DB Name:HEARTLY

Prerquites:

1. Verify Existing PDB physical size and Diskgroup size(Diskgroup Free space should be grater than Existing PDB Size)

[oracle@oracle11g-MYCDB] /home/oracle > ps -ef|grep pmon
oracle 24463 1 0 Apr29 ? 00:02:57 ora_pmon_MYCDB
oracle 28099 28002 0 09:02 pts/0 00:00:00 grep --color=auto pmon
grid 29579 1 0 Apr23 ? 00:01:30 asm_pmon_+ASM
[oracle@oracle11g-MYCDB] /home/oracle > . oraenv
ORACLE_SID = [MYCDB] ? MYCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracle11g-MYCDB] /home/oracle > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 09:02:32 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- ----------
MYCDB READ WRITE

SQL> select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;

NAME TOTAL_MB/1024 FREE_MB/1024
------------------------------ ------------- ------------
DATA 1300 474,527344
LOG 60 54,734375
ARCHIVE 400 363,273438

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ANGLES READ WRITE NO

SQL>alter session set container=ANGLES;

session altered

SQL> show con_name

CON_NAME
------------------------------
ANGLES

SQL>select sum(bytes/1024/1024/1024) from dba_data_files;

SUM(BYTES/1024/1024/1024)
-------------------------
90.0976563

As per above output DATADG free space grater than ANGLES PDB physical size.

Creating or Cloning new PDB with existing PDB.

[oracle@oracle11g-MYCDB] /home/oracle > ps -ef|grep pmon
oracle 24463 1 0 Apr29 ? 00:02:57 ora_pmon_MYCDB
oracle 28099 28002 0 09:02 pts/0 00:00:00 grep --color=auto pmon
grid 29579 1 0 Apr23 ? 00:01:30 asm_pmon_+ASM
[oracle@oracle11g-MYCDB] /home/oracle > . oraenv
ORACLE_SID = [MYCDB] ? MYCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracle11g-MYCDB] /home/oracle > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 09:02:32 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
MYCDB READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ANGLES READ WRITE NO

SQL> Create pluggable database HEARTLY from ANGLES;

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ANGLES READ WRITE NO
4 HEARTLY MOUNTED

SQL> alter session set container=HEARTLY;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
HEARTLY

SQL> ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 HEARTLY READ WRITE NO

PDB Created succsessfully, Connect to Container DB and check voilation error of newly created PDB(HEARTLY)

[oracle@oracle11g-MYCDB] /home/oracle > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 09:16:25 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ANGLES READ WRITE NO
4 HEARTLY READ WRITE NO
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> col cause for a30;
col name for a20;
col message for a80 word_wrapped;
col CAUSE for a20
SET PAGESIZE 100;
set linesize 300;
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='HEARTLY';

NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- -------------------------------------------------------------------------------- ---------
HEARTLY OPTION WARNING Database option APS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.

HEARTLY OPTION WARNING Database option CONTEXT mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.

HEARTLY OPTION WARNING Database option DV mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.

HEARTLY OPTION WARNING Database option OLS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.

HEARTLY OPTION WARNING Database option ORDIM mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.

HEARTLY OPTION WARNING Database option SDO mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.

HEARTLY OPTION WARNING Database option XOQ mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.

HEARTLY APEX WARNING APEX mismatch: PDB installed version NULL CDB installed version 4.2.5.00.08 PENDING
HEARTLY Service Name Conflic WARNING Service name or network name of service TESTXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service SIT in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service TESTXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service XYZ in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service MOXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service MO in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service SIT in the PDB is invalid or conflicts PENDING
t with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service GGADMIN.OGG$Q_INEDW in the PDB is PENDING
t invalid or conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service ABCXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service ABC in the PDB is invalid or conflicts PENDING
t with an existing service name or network name in the CDB.

HEARTLY Service Name Conflic WARNING Service name or network name of service ANGLESXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.

19 rows selected.

As Per above violations error fine and can be ignored

Verify newly created PDB GUID

SQL> set linesize 300
select con_id, dbid, con_uid, guid, name, open_mode from v$containers;SQL>

CON_ID DBID CON_UID GUID NAME OPEN_MODE
---------- ---------- ---------- -------------------------------- -------------------- ----------
1 3062830738 1 FD9AC20F64D344D7E043B6A9E80A2F2F CDB$ROOT READ WRITE
2 2184882395 2184882395 A457A3D879377F37E053CE80C80A6D40 PDB$SEED READ ONLY
3 1199942799 1199942799 A573324654A42B0EE053CE80C80A90D0 ANGLES READ WRITE
4 956378767 956378767 A59748F1645A6E4AE053CE80C80AF3F2 HEARTLY READ WRITE
SQL>alter session set container=HEARTLY;

session altered.

SQL>
col DB_NAME for a20
col DATA_FILE_NAME for a60
set linesize 400
set pagesize 9999
SELECT c.name DB_NAME, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.bytes/1024/1024 SIZE_MB, b.status
FROM v$tablespace a, v$datafile b, v$containers c
WHERE a.con_id = b.con_id
AND a.con_id = c.con_id
AND a.ts# = b.ts#
ORDER BY a.con_id, a.TS#;
2 3 4 5 6

DB_NAME TABLESPACE_NAME DATA_FILE_NAME SIZE_MB STATUS
-------------------- ------------------------------ ------------------------------------------------------------ ---------- -------
HEARTLY SYSTEM +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/sys 20480 SYSTEM
tem.352.1040375171

HEARTLY SYSAUX +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/sys 5120 ONLINE
aux.354.1040375171

HEARTLY LOBS +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/lob 512 ONLINE
s.300.1040375171

HEARTLY USERS +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/use 5120 ONLINE
rs.362.1040375171

HEARTLY INDX +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ind 10240 ONLINE
x.297.1040375171

HEARTLY INSIS_HIST_DATA +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 20480 ONLINE
is_hist_data.356.1040375171

HEARTLY INSIS_MDS +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 1024 ONLINE
is_mds.353.1040375171

HEARTLY GG_DATA +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/gg_ 3072 ONLINE
data.351.1040375171

HEARTLY INSIS_CUST +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 11264 ONLINE
is_cust.360.1040375171

HEARTLY INSIS_CORE +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 20480 ONLINE
is_core.294.1040375171

HEARTLY INSIS_CORE +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 20480 ONLINE
is_core.361.1040375171

HEARTLY INSIS_CORE +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 20480 ONLINE
is_core.359.1040375171

HEARTLY INSIS_CORE +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 20480 ONLINE
is_core.355.1040375171

HEARTLY INSIS_CORE +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 30720 ONLINE
is_core.357.1040375171

HEARTLY INSIS_JMS +DATA/MYCDB/A59748F1645A6E4AE053CE80C80AF3F2/DATAFILE/ins 1024 ONLINE
is_jms.358.1040375171

PL/SQL: ORA-00904: "PATCH_DIRECTORY": invalid identifier & PL/SQL: ORA-00904: "INSTALL_ID": invalid identifier During datapatch

Symptoms:During datapatch below error reported
Error at line 89: Warning: Package Body created with compilation errors.
Error at line 95: 311/9 PL/SQL: SQL Statement ignored
Error at line 96: 313/24 PL/SQL: ORA-00904: "PATCH_DIRECTORY": invalid identifier
Error at line 97: 951/5 PL/SQL: SQL Statement ignored
Error at line 98: 951/20 PL/SQL: ORA-00904: "INSTALL_ID": invalid identifier
Prereq check failed, exiting without installing any patches.

Cause:DBMS_SQLPATCH PackageBody Is Invalid status
SQL>select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS from dba_objects where OBJECT_NAME='DBMS_SQLPATCH';

OWNER OBJECT_NAME OBJECT_TYPE STATUS
------ ---------------- --------------- ------
SYS DBMS_SQLPATCH PACKAGE BODY INVALID

Fix:

SQL>select name from v$database;

NAME
---------
TESTDB

SQL> set linesize 400
col ACTION_TIME for a30
col DESCRIPTION for a70
col BUNDLE_SERIES for a20
select patch_id,action,status,action_time,description,bundle_series from DBA_REGISTRY_SQLPATCH;

PATCH_ID ACTION STATUS ACTION_TIME DESCRIPTION
-------- ------ ------- ---------------------------- ------------------------------------------------------
21076681 APPLY SUCCESS 30-APR-17 09.36.19.247000 PM bundle:PSU
17030189 APPLY SUCCESS 30-APR-17 09.36.19.247000 PM sqlpatch
25433286 APPLY SUCCESS 22-MAY-17 07.23.52.903000 AM WINDOWS DB BUNDLE PATCH 12.1.0.2.170228(64bit):25433286
26994216 APPLY SUCCESS 06-DEC-17 04.41.39.935000 PM WINDOWS DB BUNDLE PATCH 12.1.0.2.171130(64bit):26994216
oracle@oractest1.lfnet.se-TESTDB] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch > ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Tue May 12 09:26:21 2020
Copyright (c) 2012, 2016, Oracle. All rights reserved.

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

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Error in bootstrap log /u01/app/cfgtoollogs/sqlpatch/sqlpatch_20923_2020_05_12_09_26_21/bootstrap1_TESTDB.log:
Error at line 89: Warning: Package Body created with compilation errors.
Error at line 95: 311/9 PL/SQL: SQL Statement ignored
Error at line 96: 313/24 PL/SQL: ORA-00904: "PATCH_DIRECTORY": invalid identifier
Error at line 97: 951/5 PL/SQL: SQL Statement ignored
Error at line 98: 951/20 PL/SQL: ORA-00904: "INSTALL_ID": invalid identifier
Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log
/u01/app/cfgtoollogs/sqlpatch/sqlpatch_20923_2020_05_12_09_26_21/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Tue May 12 09:26:24 2020
[oracle@oractest1-TESTDB] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch > echo $ORACLE_SID
TESTDB
[oracle@oractest1-TESTDB] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 12 09:27:16 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS from dba_objects where OBJECT_NAME='DBMS_SQLPATCH';

OWNER OBJECT_NAME OBJECT_TYPE STATUS
----- -------------- ------------ ------
SYS DBMS_SQLPATCH PACKAGE BODY INVALID
SYS DBMS_SQLPATCH PACKAGE VALID
PUBLIC DBMS_SQLPATCH SYNONYM VALID

SQL> create table registry$sqlpatch_backup as select * from registry$sqlpatch ;

Table created.

SQL> drop table registry$sqlpatch;

Table dropped.

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

Session altered.

Package created.

No errors.

Synonym created.

Grant succeeded.

Session altered.

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

Session altered.

Table created.

View created.

Synonym created.

Grant succeeded.

PL/SQL procedure successfully completed.

Grant succeeded.

Synonym created.

Session altered.

SQL> @?/rdbms/admin/prvtsqlpatch.plb

Session altered.

PL/SQL procedure successfully completed.

Table created.

Package body created.

No errors.

Session altered.

SQL>
SQL> alter package SYS.DBMS_SQLPATCH compile body;

Package body altered.

SQL>select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS from dba_objects where OBJECT_NAME='DBMS_SQLPATCH';

OWNER OBJECT_NAME OBJECT_TYPE STATUS
----- ---------------- --------------- ------
SYS DBMS_SQLPATCH PACKAGE BODY VALID
SYS DBMS_SQLPATCH PACKAGE VALID
PUBLIC DBMS_SQLPATCH SYNONYM VALID

SQL>select name from v$database;

NAME
------
TESTDB

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2.1475E+10 bytes
Fixed Size 7654304 bytes
Variable Size 1.8589E+10 bytes
Database Buffers 2818572288 bytes
Redo Buffers 59453440 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
------ -----------
TESTDB READ WRITE

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@oractest1-TESTDB] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch > echo $ORACLE_SID
TESTDB
[oracle@oractest1-TESTDB] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch > ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Tue May 12 09:29:51 2020
Copyright (c) 2012, 2016, Oracle. All rights reserved.

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

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series PSU:
ID 190115 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
28729169 (DATABASE PATCH SET UPDATE 12.1.0.2.190115)

Installing patches...
Patch installation complete. Total patches installed: 1

Validating logfiles...
Patch 28729169 apply: SUCCESS
logfile: /u01/app/cfgtoollogs/sqlpatch/28729169/22626239/28729169_apply_TESTDB_2020May12_09_30_04.log (no errors)
SQL Patching tool complete on Tue May 12 09:30:34 2020
[oracle@oractest1-TESTDB] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch >
[oracle@oractest1-TESTDB] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch > echo $ORACLE_SID
TESTDB
[oracle@oractest1-TESTDB] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch > sqlplus / as sydba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 12 09:31:01 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
------ ----------
TESTDB READ WRITE

SQL> set linesize 400
col ACTION_TIME for a30
col DESCRIPTION for a70
col BUNDLE_SERIES for a20
select patch_id,action,status,action_time,description from DBA_REGISTRY_SQLPATCH;

PATCH_ID ACTION STATUS ACTION_TIME DESCRIPTION
-------- ------ ------ ---------------------------- -----------------------------------------
28729169 APPLY SUCCESS 12-MAY-20 09.30.34.964893 AM DATABASE PATCH SET UPDATE 12.1.0.2.190115

Friday, 8 May 2020

How to Drop Pluggable Database Manually

Contents

1. Environment
2. Pre-requisites
3. Backup PDB Before Drop
4. Close Pluggable database
5. Drop Pluggable database
6. Verify

1. Environment

Platform : Linuxx86_64
Server Name: TESTHOST
DB Version : Oracle 12.2.0.1.0, File system: Normal
CDB Name : CONCDB
PDB Name : ORAPDB
Oracle Home: /u01/app/oracle/product/12.2.0.1

2. Pre-requisites

[oracle@TESTHOST]$ . oraenv
ORACLE_SID = [oracle] ? CONCDB
The Oracle base has been set to /u01/app/oracle
[oracle@TESTHOST]$
[oracle@TESTHOST]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 12 09:37:14 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_id con_name

CON_ID
------------------------------
1

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> col name for a20
SQL> select con_id,name,open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- -------------------- ----------
2 PDB$SEED READ ONLY
3 ORAPDB READ WRITE <----

SQL>
SQL> col file_name for a50
SQL> set lines 180
SQL> select con_id,FILE_NAME,TABLESPACE_NAME from cdb_data_files where con_id=3;

CON_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
3 /u01/app/oracle/oradata/CONCDB/ORAPDB/system01.dbf SYSTEM
3 /u01/app/oracle/oradata/CONCDB/ORAPDB/sysaux01.dbf SYSAUX
3 /u01/app/oracle/oradata/CONCDB/ORAPDB/undotbs01.dbf UNDOTBS1
3 /u01/app/oracle/oradata/CONCDB/ORAPDB/users01.dbf USERS

SQL>

SQL> select CON_ID,FILE_NAME,TABLESPACE_NAME from cdb_temp_files where con_id=3;

CON_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
3 /u01/app/oracle/oradata/CONCDB/ORAPDB/temp01.dbf TEMP

SQL>

3. Take backup before Drop

[oracle@TESTHOST]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jul 12 10:06:02 2018

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

connected to target database: CONCDB (DBID=931007404) <----

RMAN> BACKUP PLUGGABLE DATABASE ORAPDB TAG 'ORAPDB_BACKUP_BEFORE_DROP';

Starting backup at 12-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/CONCDB/ORAPDB/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/CONCDB/ORAPDB/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/CONCDB/ORAPDB/undotbs01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/CONCDB/ORAPDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-18
channel ORA_DISK_1: finished piece 1 at 12-JUL-18
piece handle=/u01/app/oracle/product/12.2.0.1/dbs/01t7qack_1_1 tag=ORAPDB_BACKUP_BEFORE_DROP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 12-JUL-18

Starting Control File and SPFILE Autobackup at 12-JUL-18
piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-931007404-20180712-00 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUL-18

RMAN>

4. Close PDB

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

CON_ID NAME OPEN_MODE
---------- -------------------- ----------
2 PDB$SEED READ ONLY
3 ORAPDB READ WRITE <----

SQL>
SQL> alter pluggable database ORAPDB close immediate;

Pluggable database altered.

SQL>
SQL> col name for a20
SQL> select con_id,name,open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- -------------------- ----------
2 PDB$SEED READ ONLY
3 ORAPDB MOUNTED <------

SQL>

5. Drop PDB

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> drop pluggable database ORAPDB including datafiles;

Pluggable database dropped.

SQL>

6. Verify

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

CON_ID NAME OPEN_MODE
---------- -------------------- ----------
2 PDB$SEED READ ONLY

SQL>
SQL> select con_id,FILE_NAME,TABLESPACE_NAME from cdb_data_files where con_id=3;

no rows selected <----

SQL> select CON_ID,FILE_NAME,TABLESPACE_NAME from cdb_temp_files where con_id=3;

no rows selected <----

SQL>

[oracle@TESTHOST]$ cd /u01/app/oracle/oradata/CONCDB/ORAPDB
[oracle@rac2 ORAPDB]$ ls -ltr
total 0 <----
[oracle@rac2 ORAPDB]$