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 NO
> SQL>
>
> 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-l