How to Drop Pluggable Database Manually
Contents
___________________________________________________________________________________________________
1. Environment
2. Pre-requisites
3. Close Pluggable database
4. Drop Pluggable database
5. Verify
___________________________________________________________________________________________________
Platform : Linuxx86_64 Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102 DB Version : Oracle 12.2.0.1.0, File system: Normal CDB Name : CDB1 PDB Name : PDB1 Oracle Home: /u01/app/oracle/product/12.2.0.1
[oracle@rac2 ~]$ . oraenv ORACLE_SID = [oracle] ? CDB1 The Oracle base has been set to /u01/app/oracle [oracle@rac2 ~]$ [oracle@rac2 ~]$ 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 PDB1 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/CDB1/PDB1/system01.dbf SYSTEM 3 /u01/app/oracle/oradata/CDB1/PDB1/sysaux01.dbf SYSAUX 3 /u01/app/oracle/oradata/CDB1/PDB1/undotbs01.dbf UNDOTBS1 3 /u01/app/oracle/oradata/CDB1/PDB1/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/CDB1/PDB1/temp01.dbf TEMP SQL> [oracle@rac2 ~]$ 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: CDB1 (DBID=931007404) <---- RMAN> BACKUP PLUGGABLE DATABASE PDB1 TAG 'PDB1_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/CDB1/PDB1/sysaux01.dbf input datafile file number=00009 name=/u01/app/oracle/oradata/CDB1/PDB1/system01.dbf input datafile file number=00011 name=/u01/app/oracle/oradata/CDB1/PDB1/undotbs01.dbf input datafile file number=00012 name=/u01/app/oracle/oradata/CDB1/PDB1/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=PDB1_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>
SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- -------------------- ---------- 2 PDB$SEED READ ONLY 3 PDB1 READ WRITE <---- SQL> SQL> alter pluggable database PDB1 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 PDB1 MOUNTED <------ SQL>
4. Drop PDB
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> drop pluggable database PDB1 including contents and datafiles; drop pluggable database PDB1 including contents and datafiles * ERROR at line 1: ORA-02000: missing DATAFILES keyword SQL> drop pluggable database PDB1 including datafiles; Pluggable database dropped. SQL>
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@rac2 ~]$ cd /u01/app/oracle/oradata/CDB1/PDB1 [oracle@rac2 PDB1]$ ls -ltr total 0 <---- [oracle@rac2 PDB1]$
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.