ORACLE - File System Full

[alessia]

Hi all,
I have the following issue, file system where oracle datafiles are stored, are full or quite full:

/dev/vg_eps1/lvol1 22528000 22467728 59848 100% /prod_oradata/data_3
/dev/vg_eps1/lvol2 15667200 15414656 250576 98% /prod_oradata/data_4

but I cannot extend F.S.

which files can I delete in order to access DB again? I mean I din't care to loose old records contained in the DB.

following the content of directories:

The full one:

root@ips3:/prod_oradata/data_3/eps1 > ll
total 43562464
-rw-r--r-- 1 ora920 oinstall 71 Nov 26 15:31 afiedt.buf
-rw-r----- 1 ora920 oinstall 9756672 Feb 7 11:14 control01.ctl
-rw-r----- 1 ora920 oinstall 9756672 Feb 7 11:14 control03.ctl
-rw-r----- 1 ora920 oinstall 4718600192 Feb 7 11:14 ips101.dbf
-rw-r----- 1 ora920 oinstall 6920609792 Feb 7 11:14 ips103.dbf
-rw-r----- 1 ora920 oinstall 4404027392 Feb 7 11:14 ips106.dbf
-rw-r----- 1 ora920 oinstall 1363156992 Feb 7 11:06 ips108.dbf
-rw-r----- 1 ora920 oinstall 1073750016 Feb 7 10:04 ips_redirect.dbf
-rw-r----- 1 ora920 oinstall 2097160192 Feb 7 11:14 rbs01.dbf
-rw-r----- 1 ora920 oinstall 1048584192 Feb 7 11:14 rbs02.dbf
-rw-r----- 1 ora920 oinstall 52429824 Feb 7 06:55 redoeps101.log
-rw-r----- 1 ora920 oinstall 52429824 Feb 7 09:33 redoeps102.log
-rw-r----- 1 ora920 oinstall 52429824 Feb 7 11:14 redoeps103.log
-rw-r----- 1 ora920 oinstall 576724992 Feb 7 11:04 system01.dbf

The almost full one:

root@ips3:/prod_oradata/data_4/eps1 > ll
total 30823776
-rw-r--r-- 1 ora920 oinstall 0 Feb 4 14:34 afiedt.buf
-rw-r----- 1 ora920 oinstall 9756672 Feb 7 11:15 control02.ctl
-rw-r----- 1 ora920 oinstall 545267712 Feb 7 10:04 drsys01.dbf
-rw-r----- 1 ora920 oinstall 20979712 Feb 7 10:04 indx01.dbf
-rw-r----- 1 ora920 oinstall 6710894592 Feb 7 11:14 ips102.dbf
-rw-r----- 1 ora920 oinstall 1048584192 Feb 7 11:14 ips104.dbf
-rw-r----- 1 ora920 oinstall 1572872192 Feb 7 10:52 ips105.dbf
-rw-r----- 1 ora920 oinstall 2097160192 Feb 7 11:15 ips107.dbf
-rw-r----- 1 ora920 oinstall 52429824 Feb 7 06:55 redoeps101.log
-rw-r----- 1 ora920 oinstall 52429824 Feb 7 09:33 redoeps102.log
-rw-r----- 1 ora920 oinstall 52429824 Feb 7 11:15 redoeps103.log
-rw-r----- 1 ora920 oinstall 524296192 Feb 7 10:04 stats01.dbf
-rw-r----- 1 ora920 oinstall 3081609216 Feb 7 10:59 temp01.dbf
-rw-r----- 1 ora920 oinstall 73408512 Feb 7 10:04 tools01.dbf
-rw-r----- 1 ora920 oinstall 20979712 Feb 7 10:04 users01.dbf

Thanks in advance

[Thierry Poels_1]

hi,

you can delete "afiedt.buf", that's all.
You can't delete any datafile without hurting your DB. And surely don't delete any redo*.log files!

regards,
Thierry.

[Robert-Jan Goossens]

Hi,

The only save methode is to shutdown oracle, move files to a filesystem with enough space, extend the oracle filesytems and move the files back.

Regards,
Robert-Jan

[Sunil Sharma_1]

You can not delete records from database using operating system command. You have to shutdown the database and transfer file to other place increase the file system and restore database. Once you have access to database you can sit with your dba and plan to delete unwated records from database.


Sunil

[Mobeen_1]

Aliessia,
Agree with Snil, Robert and Thierry !!

DON'T DELETE THE REDO LOGS.. these are needed for recovery

The only way to go about is

1. Shutdown you Oracle Server

2. Move all the files to a FS that has enough space

3. Extend the volumes and then

4. Move those back

rgds
Mobeen

[B. Hulst · Accepted solution]

Hi,

(Don't remove file with *.dbf or any other with log in the filename. Oracle will crash!)

1.
However if on the same partition you have stored oracle archives then you can move those to another partition.

2.
You can also manually resize (smaller then of course) the datafiles. This is online and no shutdown needed but you need at least oracle 8 or newer. It looks you have oracle 9.2.0.x

sqlplus "/ as sysdba"
alter database datafile '/prod_oradata/data_4/eps1/ips102.dbf' resize 5000M;

Regards,
Bob

[Indira Aramandla]

Hi Alessia,

When your file system is full, you will need to extend the file system provided you have free extends. The files in that file system are all oracle database datafiles, redo logs, and control files which are all required for the proper functioning of the database, and you cannot delete any one of them.

To extend the file system, you will have to unmount, extend and then mount (if you do not have on-line JFS). For this reason you will have to shutdown the database and see no user is accessng any files from the file system (stop the listener as well) and extend the file system.

Afterwards if you feel like cleaning up your database or archiving the data (older records, then you can export the data, and save the dump file on to a tape if you need them later on. Then delete the data that you do not want, and reorg the data (by export and import) to gain the free space back. Just deleting the records does not release free space.

The other option fi you cannot extent that file system and have free space else where then shutdown the database, copy the datafiles and redologs to the new location and re-create the control files and startup the database.

Indira A

[Yogeeraj_1]

hi,

two options:

1. Extend the current file systems
2. Move existing datafiles to a new system
For that, you will need to:
a. Backup the controlfile to trace
b. shutdown the database
c. Move the datafile to another file system
d. modify the trace file to reflect the above change
e. Recreate the controlfiles using the trace file (modified above)
f. startup the database.

hope this helps too!

NB. NEVER delete the either of the dbf or log files.

regards
yogeeraj

[Albert Smith_1]

1 - Shutdown the DB
2 - Have DBA modify the control file to a new location of the redo logs and archive files and control files.
3 - Create a new FS for your Redo Logs and your Archive logs
4 - Move the redo logs,archive files and control files to their new FS
5 - Restart the DB with the new control file.

Once the DB has successfully come up and is using the new filesystems delete the old redo logs and control files and archive logs.

[Ron Gordon]

I think you are missing the point. There is no problem if the filesystem is 100% full as long as the tablespaces are not set to autoexend. However, if you want to delete the tablespaces datafiles (dbf) This is what you should do.

1) sqlplus system/manager
2) SQL> select tablespace_name, file_name, bytes status from sys.dba_data_files
order by 1,2;

3) SQL> DROP TABLESPACE IPS INCLUDING CONTENTS AND DATAFILES;

Please note, I am assuming the tablespace name is IPS. You can probably drop the following tablespaces associated with the datafiles, indx01, tools01, stats01.

Also, without knowing your application your temp tablespace seems large. And since this is a version 9 database I would get ride of the redo logs and change it to undo space.

Good luck.

[Rory R Hammond]

Make sure auto extend is turned off on all of your dbf files.

I would make another logical volume filesystem and move your application dbf files to the new location. With 9i, I have successfully on a live production database during low activity, taken dbf files offline, copied to new filesystem. renamed them and put them back on line and removed the old dbf file. (I used fuser to make sure the old one was no longer being used by oracle)

My recommendation for you is to take the system down and use the documented methods
If you are able to take the system down. You should be able to extend the logical volume. and not do any oracle stuff.

Your note suggests that you have old data. After you get past your space crisis. Have your application people look at the database schemas and purge old records. Also look for tables that are not being used and have them dropped. I have a App owner who copies large tables to a backup name and forgets about them. Do Not drop the dbf files they are your file cabinets not your data.

Sound like you have lots of maintaince to do.
After you get your data purged. I would schedule downtime and export your data. delete your table spaces and recreate it with better sized dbfs. I also would set the datafiles to not extend past a particular size. In the future, I would manually add dbf files as table space gets low. That way I could put the files in filesystem with free space. This also might allow time to consider balancing I/O by putting certian files on a particular disks.


Good Luck

[zmyle]

first, the archive logs have to go.
if still not enough space, transfer to a more abundant storage space.