Link to home
Start Free TrialLog in
Avatar of doberboy
doberboyFlag for United States of America

asked on

OS not releasing space after moving Oracle datafile.

Oracle 9.2.0.4
AIX 5.2

I found a filesystem that was 100% full this morning. I moved a 7GB datafile with the following commands:

alter tablespace HISTORICAL offline;
!mv /txpd_dat1/oradata/HISTORICAL02.dbf /txpd_dat2/oradata/HISTORICAL02.dbf
alter tablespace HISTORICAL rename datafile '/txpd_dat1/oradata/HISTORICAL02.dbf' to '/txpd_dat2/oradata/HISTORICAL02.dbf';
alter tablespace HISTORICAL online;

The datafile was moved, but the original filesystem still showed 100% full. I bounced the database because it wasn't very busy and it released the space.

If I get into a situation where I can't bounce a production database, what can be done to have the OS release the space?

 


ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It seems the files are in the trash can.
See here

http://linux.about.com/od/linux101/a/desktop03e.htm
To schwertner:

Was your comment here with the link to a Linux site directed to me?  No, that was *NOT* the problem we had!  I did file moves or deletes from the Linux command line using "mv" or "rm", not via a GUI tool that could have left them in the trash can.

This question was originally about AIX, not Linux.  I admit, I have no experience with AIX, but I'm guessing it does not have a Linux-like "trash can" for files that were deleted via a GUI interface.
It seems this is taugh problem. I succeed to find this for the community here:

http://www.unix.com/filesystems-disks-memory/48678-mv-files-still-use-space-not-freed.html 

fuser will show open files that have been deleted on a given file system. Until that process is stopped the space will not be freed. Before messing with files make sure no process has it open with fuser.
 
http://www.ibm.com/developerworks/aix/library/au-lsof.html 
Finding open files with lsof
Good point Schwertner.

I have actually used fuser before when I was receiving Device Busy message when attempting to unmount the filesystem.

I used the following:
fuser u /dev/hdg4 (to determine any users occupying thefilesystem)
<!--[if !supportLists]-->-        <!--[endif]-->none
fuser m /dev/hdg4 (to determine processes)
<!--[if !supportLists]-->-        <!--[endif]-->16908c
I then killed the process.

The only problem is that the offending process may likely be a critical Oracle process which should not be terminated leaving us with an only solution of terminating the instance afterall.

While this tool increases our knowledge of what is going on, it probably would not give us the finite control we wish for.

R.

There is a chance that fuser or lsof will not return any process that has the file open.  I have seen this.  It is just that the file system has not been updated yet.  If that is the case, the 3 sync commands should fix it.
Sorry my posting was formated weirdly:

fuser u /dev/hdg4 (to determine any users occupying the filesystem)
- none
 
fuser m /dev/hdg4 (to determine processes)
- 16908c

johnsone, what does sync do? What is it sync'ing and why three occurences?

R.
sync is an OS command.  What we want to use it for is to flush the unwritten system buffers to disk.  This should release any space that has been deleted, but just not registered back to the file system.

There may not be a reason for three, however, historically, three have always been done.  The first is supposed to do the job, but sometimes will not flush all the buffers.  The second should get any missing buffers.  The third is a just in case and really shouldn't do anything.  It doesn't hurt to do more than one.
Avatar of doberboy

ASKER

I appreciate the suggestions but the sync command even three times doesn't release the disk space.
HI I found this for you.

ATTENTION!!!
Use it very carefully, because this is REMOVE command and can harm
your installation. Check it on a experimental Lunux installation.

http://forums.contractoruk.com/technical/9131-need-reclaim-disk-space-linux.html
su root
cd /
rm -rf
schwertner,

That suggestion was a joke. Read that thread further and you'll see it was posted as a joke. Running rm -rf on the root directory is NOT recommended.

R.
Yes, thanks!
I got the same feeling because of the "rm" command.
It is not intended for this aim.

To be honest I will say that removing an Oracle disk file
is a major change of the DB. Oracle recommends to make full
cold backup before this and backup the control files.
So it will be reasonable to turn of the OS.
The only thing is to find the time window to do this.
If the sync is not working, that would mean that something still has the file open.  You should be able to confirm that with lsof.  Be careful killing processes at the OS level, because if you kill a background process it will bring the database down.  It would be interesting to know which process still has an open file handle.

It certainly sounds like a bug to me.