Skip to Main Content
  • Questions

  • drop pluggable database does not remove all files

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mateusz.

Asked: November 22, 2016 - 12:13 pm UTC

Last updated: April 25, 2019 - 11:55 pm UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hello,

I use multitenant architecture(CDB) in my daily deployments. So on my oracle server i have two "model" pluggable db with name PDB10, PDB11. These db are using in cloning process. using following statement i make duplicate of these db e.g

create pluggable database xx from PDB10 file_name_convert=('E:\Oracle\oradata\orclb\PDB10','E:\Oracle\oradata\orclb\xxx')

this is fine, but before cloning i want drop existing database by this command "DROP PLUGGABLE DATABASE &1 INCLUDING DATAFILES" and this command does not remove all data files - especially SYSTEM01.dbf and SYSAUX01.dbf still exists! how i can resolve it?:)

thanks,

Best regards,
Mateusz

and Connor said...

Sorry, I can't reproduce that behaviour

C:\Users\comcdona>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 23 19:13:18 2016

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

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


SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------------------------------------
C:\ORACLE\ORADATA\CDB12\SYSTEM01.DBF
C:\ORACLE\ORADATA\CDB12\PDBSEED\SYSTEM01.DBF
C:\ORACLE\ORADATA\CDB12\SYSAUX01.DBF
C:\ORACLE\ORADATA\CDB12\PDBSEED\SYSAUX01.DBF
C:\ORACLE\ORADATA\CDB12\UNDOTBS01.DBF
C:\ORACLE\ORADATA\CDB12\USERS01.DBF
C:\ORACLE\ORADATA\CDB12\PDB121\SYSTEM01.DBF
C:\ORACLE\ORADATA\CDB12\PDB121\SYSAUX01.DBF
C:\ORACLE\ORADATA\CDB12\PDB121\PDB121_USERS01.DBF
C:\ORACLE\ORADATA\CDB12\PDB122\SYSTEM01.DBF
C:\ORACLE\ORADATA\CDB12\PDB122\SYSAUX01.DBF
C:\ORACLE\ORADATA\CDB12\PDB122\PDB122_USERS01.DBF

12 rows selected.

SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
PDB121
PDB122

SQL> drop pluggable database PDB122 including datafiles;

Pluggable database dropped.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------------------------------------
C:\ORACLE\ORADATA\CDB12\SYSTEM01.DBF
C:\ORACLE\ORADATA\CDB12\PDBSEED\SYSTEM01.DBF
C:\ORACLE\ORADATA\CDB12\SYSAUX01.DBF
C:\ORACLE\ORADATA\CDB12\PDBSEED\SYSAUX01.DBF
C:\ORACLE\ORADATA\CDB12\UNDOTBS01.DBF
C:\ORACLE\ORADATA\CDB12\USERS01.DBF
C:\ORACLE\ORADATA\CDB12\PDB121\SYSTEM01.DBF
C:\ORACLE\ORADATA\CDB12\PDB121\SYSAUX01.DBF
C:\ORACLE\ORADATA\CDB12\PDB121\PDB121_USERS01.DBF

9 rows selected.


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mateusz HERC, November 23, 2016 - 11:39 am UTC

Hello,

Thanks for message.

these files was gone from v$datafile view - it's true. but still exists on hard disk - and are used by oracle services(windows).

when i try delete these files via windows del command then i get message "The action can't be completed because the file is open in OracleServiceORCLB"

Best regards,
Mateusz
Connor McDonald
November 24, 2016 - 4:12 am UTC

I think you'll to take it up with Support, because on mine, the files definitely did disappear after the drop.

C:\>dir C:\oracle\oradata\cdb12\pdb122
Volume in drive C is System
Volume Serial Number is 7AD7-C05A

Directory of C:\oracle\oradata\cdb12\pdb122

23/11/2016 07:14 PM <DIR> .
23/11/2016 07:14 PM <DIR> ..
0 File(s) 0 bytes
2 Dir(s) 47,567,396,864 bytes free

Check your alert log to see if any OS-level error was logged during the delete which might account for them still being left there.


Mateusz HERC, November 23, 2016 - 11:43 am UTC


steps to reproduce

Mateusz HERC, November 23, 2016 - 11:50 am UTC

Hello,

Thanks for a message.

These files was gone from a view v$datafile - it's true. But are still exists on hard disk. When i try drop them via windows delete command then i get "The action can't be completed because the file is open in OracleSetrvicesORCLB"


This is happen on every of my 3 servers with CDB architecture.

I don't know if this have a matter but I didn't change tsnames, listener, sqlnet files after created these databases.

Best regards,
Mateusz

Thanks

A reader, May 22, 2017 - 8:27 am UTC

Thanks

This is a bug in 12.1

David Warsop, November 23, 2017 - 4:15 pm UTC

I came across the same behaviour - on Windows. It works OK on Linux. I logged it with Support and they created bug 20101917. It is described as an Enhancement Request and a 'very desirable feature', though I argued it is a bug as it doesn't work as documented. It appears to be fixed in 12.2 (at least it has been working for me), but as far as I know there's no sign of a fix in 12.1.
Connor McDonald
November 27, 2017 - 1:17 am UTC

Thanks for the info.

I'd suggest escalation to lobby for classification as a bug

12.1 Bug

Kristine Barry, April 23, 2019 - 2:03 pm UTC

This is unacceptable to us, since the version of ArcGIS we are working under does not work with 12.2. It means that if you need to recreate your pluggable db for the client using the name they requested, you cannot do so. <sigh>
Connor McDonald
April 25, 2019 - 11:55 pm UTC

Contact Support and ask for a backport.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.