Tuesday, September 20, 2016

Linux Error: No Space Left on Device

On Linux machine, there could be several reasons for this error message appearing in some log file, or on the command line output while executing some command. Most of the time we immediately start checking if our file system has still sufficient space left, but file system space is not the problem most of the time. Following are a few of them I will be discussing.

  1. Alert log shows ORA-27102 with "No space left on device" error message. Probably reason is that kernel parameters are not set properly to run Oracle database. To solve the problem, check the installation guide of particular Oracle version for appropriate kernel settings to run Oracle instance.
  2. Memory and/or Swap space has depleted. "top" command of Linux will return total amount of RAM/SWAP and current usage.
  3. Inodes of file system no more available. Look at the following output of "df -i"
    Filesystem            Inodes   IUsed      IFree          IUse%    Mounted on
    /dev/sda3            3923968  3923968 0                 100%         /
    tmpfs                 790326     279        790047       1%         /dev/shm
    /dev/sda1              51200      44        51156         1%        /boot
    If your file system contains a lot of files/folders which is eventually using all available inodes, you can start seeing error message "No space left on device". To solve this problem in this scenario, you would need to find out why this file system has this many file and why so many files/folders were created on this file system, and also deleting these files/folders.

Monday, September 12, 2016

Enable Asynchronous IO for file System

We noticed that on one of our standby database, the log apply was quite slow. I stopped managed recovery process which showed ORA-16037 in the alert log file along with trace file name.
ALTER DATABASE RECOVER managed standby database cancel
Fri Nov 20 16:13:12 2015
RFS[2]: Selected log 12 for thread 1 sequence 504809 dbid -1228626264 branch 748801133
Fri Nov 20 16:13:13 2015
Archived Log entry 11098 added for thread 1 sequence 504808 ID 0xbe776bb0 dest 1:
Fri Nov 20 16:13:44 2015
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/standbydb/MYDB/trace/MYDB_pr00_21313.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply

Trace file was having following messages in it.
*** 2015-11-20 16:08:28.092
Media Recovery Log /arch/oracle/mydb/archive/ARC504565_0748801133.0001
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2015-11-20 16:10:01.217
Media Recovery Log Media Recovery Log /arch/oracle/mydb/archive/ARC504566_0748801133.0001
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2015-11-20 16:11:31.517
Media Recovery Log Media Recovery Log /arch/oracle/mydb/archive/ARC504567_0748801133.0001
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

It meant that asynchronous IO was not working for the file systems which needed to be set by setting initialization parameter FILESYSTEMIO_OPTIONS=’SETALL’. By default value of this parameter is NONE. (Instance restart is required as this is not a dynamic init parameter)
It is recommended to set value of this parameter to SETALL (or ASYNCH or DIRECTIO). SETALL is equal to both ASYNCH and DIRECTIO.
Point to note is that parameters DISK_ASYNCH_IO (and TAPE_ASYNCH_IO) is by default set to TRUE which enables asynchronous IO regardless of where oracle files are stored, whereas FILESYSTEMIO_OPTIONS is specific to the files stored on the file system. Moreover, before setting this parameter, you should confirm if your file system supports asynchronous IO, otherwise you may face severe performance issue.
To set value of this parameter, set value in SQLPLUS if you are using spfile, and bounce the instance. If you are using pfile, set value in pfile and bounce the instance.

SQL> ALTER SYSTEM SET filesystemio_options=’SETALL’ SCOPE=spfile;