How to drop pluggable database from CDB
We have running below
pluggable database under container database. Out of all this pluggable database
we want to drop only one pluggable database MY_UNIFIED_TEST.
SQL > show
pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
115 MUMBAI READ WRITE NO
116 DELHI READ WRITE NO
119 PUNE READ WRITE NO
123 GOA READ WRITE NO
129 MY_UNIFIED_TEST READ WRITE NO
First as good
database administrator we need to consider for the backup for the future use.
So take the export backup or rman backup of the pluggable database MY_UNIFIED_TEST.
nohup expdp
system/Welcome1@<hostname>:1521/my_unified_test directory=expmy_unified_test
dumpfile=my_unified_test_06Apr2017_%U.dmp logfile=my_unified_test_06Apr2017log
LOGTIME=ALL CLUSTER=NO CONTENT=ALL FULL=Y parallel=5 &
To drop the
pluggable database, the pdb should be closed or unplugged. After completion of
the backup kindly close the database.
You can’t drop
PDB$SEED pluggable database.
Connect to the
container database and execute below statement.
SQL> ALTER
PLUGGABLE DATABASE MY_UNIFIED_TEST CLOSE IMMEDIATE;
Pluggable database
closed.
SQL>
Check the status
of the pdb.
SQL > show
pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
115 MUMBAI READ WRITE NO
116 DELHI READ WRITE NO
119 PUNE READ WRITE NO
123 GOA READ WRITE NO
129 MY_UNIFIED_TEST MOUNT NO
The OPEN MODE of
the pluggable database is MOUNT so you can now go ahead and drop the pdb.
If you want to check what are the different ways to connect pluggbale database
There are two
ways to drop the pluggable database.
1) “KEEP DATAFILES” - By keeping the datafiles
associated with pdb. The tempfiles will get dropped associated with this pdb.
2) “INCLUDING DATAFILES” – all datafiles and
tempfiles associated with the pdb get dropped.
Here we used INCLUDING DATAFILES as we want to release the space from the ASM too.
SQL> DROP
PLUGGABLE DATABASE MY_UNIFIED_TEST INCLUDING DATAFILES;
Pluggable
database dropped.
SQL>
You should
monitor the alert log of the container database.
Alert log of cdb –
Thu Apr 06
19:17:05 2017
alter pluggable
database MY_UNIFIED_TEST open
Thu Apr 06
19:17:06 2017
Database
Characterset for MY_UNIFIED_TEST is WE8MSWIN1252
Opening pdb MY_UNIFIED_TEST
(129) with no Resource Manager plan active
Pluggable
database MY_UNIFIED_TEST opened read write
Completed: alter
pluggable database MY_UNIFIED_TEST open
alter pluggable
database MY_UNIFIED_TEST close immediate
Thu Apr 06
19:17:59 2017
ALTER SYSTEM:
Flushing buffer cache inst=1 container=129 local
Thu Apr 06
19:18:13 2017
Pluggable
database MY_UNIFIED_TEST closed
Completed: alter
pluggable database MY_UNIFIED_TEST close immediate
DROP PLUGGABLE
DATABASE MY_UNIFIED_TEST INCLUDING DATAFILES
Thu Apr 06
19:36:41 2017
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/unified_portal.769.924274035
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/unified_portal.823.924274011
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/unified_portal.2416.924273987
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/unified_portal.2413.924273963
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/unified_portal.1332.929269035
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/unified_portal.1071.929269013
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/unified_portal.1070.929268991
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/unified_portal.480.929268965
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/users.2046.924273177
Thu Apr 06
19:36:52 2017
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/TEMPFILE/temp.2474.924273171
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/sysaux.2473.924273169
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/system.2472.924273169
Deleted Oracle
managed file +DATA/MYCDB/3DF334A2FE653A71E053720B4B0A32BC/DATAFILE/unified_indx.1424.931619925
Completed: DROP
PLUGGABLE DATABASE MY_UNIFIED_TEST INCLUDING DATAFILES
Check the status
now.
SQL > show
pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
115 MUMBAI READ WRITE NO
116 DELHI READ WRITE NO
119 PUNE READ WRITE NO
123 GOA READ WRITE NO
I hope this
exercise will help you.
No comments:
Post a Comment