Oracle – File system full

[adsense id=”0514458240″ width=”468″ height=”60″]

The following scenario occurs frequently, and while is should be monitored a quickly growing database with tablespaces in auto extend mode with no high limit, can cause the file system to fill up and thus the database can no longer function correctly.

If capacity is available on another disk we can issue the following set of commands:

The first step is to stop the Oracle server. The main reason for this is to then bring the database server up in mount state. Having the data server in mount state allows Oracle to read the control files which contains information about the setup of the database i.e. the location of the data files, tablespaces etc.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Now we restart the database in mount state – this is important as we will need to rename the data files after they have been moved to a new disk and this is only possible in mount state.

SQL> startup mount
ORACLE instance started.

Total System Global Area  134217728 bytes
Fixed Size		    1257596 bytes
Variable Size		   79695748 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    2932736 bytes
Database mounted.

Now that we have this in mount state, let’s take a look at the datafiles belonging to the database server:

SQL> select, d.bytes/1024/1024 mbytes, tablespace
  2  from v$datafile d, v$tablespace t
  3  where d.ts# = t.ts#
  4  ;

-------------------------------------------------- ---------- ------------------------------
/usr/lib/oracle/xe/oradata/XE/system.dbf		  350 SYSTEM
/usr/lib/oracle/xe/oradata/XE/undo.dbf			  210 UNDO
/usr/lib/oracle/xe/oradata/XE/sysaux.dbf		  340 SYSAUX
/usr/lib/oracle/xe/oradata/XE/users.dbf 		    0 USERS

The second field defines the actual size of the data files in Mbytes. In this case here we are going to move the data file ‘/usr/lib/oracle/xe/oradata/XE/users.dbf’ to ‘/tmp/users.dbf’. This is to demonstrate the steps – we strongly recommend moving the data file to a properly mirrored disk instead of /tmp.

shell> mv /usr/lib/oracle/xe/oradata/XE/users.dbf /tmp

Now that we have freed up capacity we need to notify Oracle that the data file has moved.

SQL> ALTER DATABASE RENAME FILE '/usr/lib/oracle/xe/oradata/XE/users.dbf' TO '/tmp/users.dbf';

Database altered.

Now we can Open the database.

SQL> alter database open;

Database altered.