RE: Sanity Check Please: Removing Datafiles after Dropping Tablespace?
Date: Tue, 25 May 2021 17:16:33 -0400
Message-ID: <4e6b01d751ab$3d34ffe0$b79effa0$_at_rsiz.com>
+1.
By the way, you’re not paranoid if they really are out to get you…
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andrew Kerber
Sent: Tuesday, May 25, 2021 11:56 AM
To: Mark W. Farnham
Cc: Mladen Gogala; ORACLE-L
Subject: Re: Sanity Check Please: Removing Datafiles after Dropping Tablespace?
I have found paranoia pays as a dba. I would start by renaming them at the OS level and restarting the instance to see what happens....
On Tue, May 25, 2021 at 10:51 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:
That seems just fine. Extra paranoid folks (aka operational DBAs) might run strings on the control file, too.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala
Sent: Tuesday, May 25, 2021 11:28 AM
To: oracle-l_at_freelists.org
Subject: Re: Sanity Check Please: Removing Datafiles after Dropping Tablespace?
Hi Chris, that's not a problem. I don't have 12.1 on my machine any more but I will demonstrate with 19c. First, let's create tablespace "CHRIS" and drop it without "including contents and datafiles":
SQL> select file_name from dba_data_files;
FILE_NAME _____________________________________________________
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
Elapsed: 00:00:00.561
SQL> create tablespace chris datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/chris.dbf' size 100M;
Tablespace CHRIS created.
Elapsed: 00:00:00.484
SQL> drop tablespace chris;
Tablespace CHRIS dropped.
Elapsed: 00:00:00.389
SQL>
Now, let's check which data files are in use:
bash-4.2$ fuser -a *.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/chris.dbf:
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf: 2302 2310 2340 2575 2742 2765 2767 2988 3044
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf: 2302 2310 2328 2340 2575 2736 2742 2746 2748 2752 2763 2765 2988
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf: 2302 2763
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf: 2302 2310 2342 2575
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf: 2302 2310
As you can see, the file /opt/oracle/oradata/ORCLCDB/ORCLPDB1/chris.dbf doesn't have any processes associated with it. So, let's remove it and restart the instance:
bash-4.2$ rm -i chris.dbf
rm: remove regular file ‘chris.dbf’? y
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 25 11:25:25 2021 Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> startup force
ORACLE instance started.
Total System Global Area 4966052456 bytes
Fixed Size 9146984 bytes Variable Size 2214592512 bytes Database Buffers 2734686208 bytes Redo Buffers 7626752 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NOSQL> So, all is well. You can use "fuser -a" and remove the files which do not have any processes associated with them. With ASM you would have to use some additional trickery, but that's another story.
Regards
On 5/25/21 9:21 AM, Chris Taylor wrote:
Env :12.1.0.2 x86-64 Linux NON-RAC,NON-ASM
As haste makes waste (or leaves waste as the case may be), I dropped a tablespace as part of a scheduled maintenance. However, I neglected the "INCLUDING CONTENTS AND DATAFILES" portion of the drop command.
So now I have a lot of datafiles left on the OS.
I've confirmed that fuser doesn't show any PIDs touching the datafiles.
I think I can drop the datafiles straight from the OS now on both the primary and standby, but need a sanity check here if anyone knows something different? I'm being careful here in case I've forgotten something.
Thanks,
Chris
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-l -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 25 2021 - 23:16:33 CEST