RE: df shows 100% on a empty filesystem-

A single mv * command is unable to check if a file is open or not...  

The script I've used in past is something like that (written from memory & untested):  

for i in $ARCH_DEST/*.arc ; do  

    if [ `fuser $i` == "1" ] ; then

        mv $i $BACKUP_DEST
    else

        echo "`date` File $i still open..."     fi  

done  

Note that fuser commands behave differently on different OSes (like Linux vs Solaris) so you have to test for yourself.  

Important note: I have seen many homegrown scripts generating a list of archivelogs into textfile and then skipping the last file in the list when moving/compressing files. This assumes that only the latest archivelog file can be still written into. This is wrong in cases where you have multiple active ARCx processes (or log_archive_max_processes > 1). I have seen at least one company where DBAs were happily using their old homegrown scripts from Oracle 7 times and were unaware that they may have gaps in their archivelog stream due such race conditions. I bet there are many more companies out there who just have been "lucky" to not experience those gaps during their recovery scenarions and testing.  

Note that the "fuser" approach assumes that an ARC process does not close (and reopen) the archivelog before it has completed all writes into it. So far it has always seemed to be the case, but times change, you cant know for sure.  

So, if you want to be safe (and get rid of those home-grown) scripts, then I suggest to start using RMAN / DataGuard for all archivelog operations. If you still need manual scripts, then the best would be to rely on V$ARCHIVED_LOG.ARCHIVED field for determining whether the archival of a particular log is complete (and why not have the fuser check as well just in case).

--

Regards,
Tanel Poder
http://blog.tanelpoder.com <http://blog.tanelpoder.com/>  

From: Prasad [mailto:p4cldba_at_gmail.com] Sent: Thursday, January 17, 2008 17:55
To: Tanel Poder
Cc: asif_oracle_at_yahoo.com; kennaim_at_gmail.com; andrew.kerber_at_gmail.com; oracle-l
Subject: Re: df shows 100% on a empty filesystem-

Hi Tanel,

there is a cronjob running which moves( mv) the archivelogs from /ofs/archlogs01 to another fielsystem. I suspect that as you mentioned it might be struck while moving partially completed archivelog files. so is there a way to validate the archivelog file size (bytes). I tried with v$archived_log ( blocks*block_size) . but somehow it do not seems to .

Thanks
-Prasad

On Jan 16, 2008 11:15 PM, Tanel Poder <tanel.poder.003_at_mail.ee> wrote:

When datafiles are not released after dropping a tablespace then again lsof is useful for identifying which process still holds the files open.  

I've experienced a similar case in Oracle 9.2 on Solaris, it turned out that the idle job queue processes still had the files open even after tablespace was dropped. As I did not want to bounce the instance just because of that, I set job_queue_processes = 0, waited until the processes died (and the files were released) and set job_queue_processes back to original again.

--

Regards,
Tanel Poder
http://blog.tanelpoder.com <http://blog.tanelpoder.com/>  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Asif Momen
Sent: Thursday, January 17, 2008 13:55
To: kennaim_at_gmail.com; andrew.kerber_at_gmail.com; p4cldba_at_gmail.com <mailto:p4cldba_at_gmail.com>
Cc: 'oracle-l'
Subject: RE: df shows 100% on a empty filesystem-

Hi,

As Ken mentioned, I have also seen this happening on IBM-AIX (Oracle 10g R2) when I dropped a huge undo tablespace. We had to bounce the instance to reclaim the space.

Regards

Asif Momen
http://momendba.blogspot.com

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 17 2008 - 07:18:24 CST