Re: Sanity Check Please: Removing Datafiles after Dropping Tablespace?

You can try running an RMAN ‘catalog start with …’ on the data file directory to see if it picks those data files up as data file copies. Once registered as data file copies just use RMAN ‘delete datafilecopy …’

We used to run something similar on a standby to clean up dropped but not removed datafiles, but please test/check the approach first before running in prod.

Regards,

Mark
On 26 May 2021, 1:56 AM +1000, Andrew Kerber <andrew.kerber_at_gmail.com>, wrote:
> 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