ORA-12801 ORA-01114 ORA-27072 and Linux Error: 25

We have recently experienced below issue while running some long running recon scripts. These scripts demand about 70 GB of temp space and failed with below error after running for 1.5 hours.

java.sql.SQLException: ORA-12801: error signaled in parallel query server P011
ORA-01114: IO error writing block to file 1004 (block # 413969)
ORA-27072: skgfdisp: I/O error
Linux Error: 25: Inappropriate ioctl for device
Additional information: 413969

After consulting with our SA and some research in google, we came to know about the root cause of this issue.

One of our DBA has created a 2 temp files with 20 GB each on a file system that has only 30 GB free space. Initially I wondered, How the ORACLE allowed to create a 40 GB temp files though we just have 30 GB free space on File system. But then I came to know about oracle sparse temp files creation and understood that oracle doesn’t actually allocate/reserve the space when we create temp files.

******************************************************************
What are Sparse Files?
On many Unix file systems, files containing long strings of nulls can be stored much more efficiently than other files. To be specific, if a string of nulls spans an entire allocation block, that whole block is not stored on disk at all. Files where one or more blocks are omitted in this way are called sparse files. The missing blocks are also known as holes.

Note that sparse files are not the same as compressed files. Sparse files are exactly the same as their non-sparse equivalents when they are read.The Unix kernel simply fills in nulls for the missing blocks.

Sparse files are created by seeking beyond the end of a file and then writing data. Because of the nature of these applications, sparse files are often created by random-access database programs.

******************************************************************

Here is the brief explanation from TOM KYTE about these temp sparse files.

One of the nuances of true temporary files is that if the operating system permits it – the temporary files will be created “sparse”. That is, they will not actually consume disk storage until they need to. You can see that easily using this example (on Red Hat Linux in this case)



SQL> !df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/hda2 74807888 41999488 29008368 60% /
/dev/hda1 102454 14931 82233 16%
/bootnone 1030804 0 1030804 0% /dev/shm


SQL> create temporary tablespace temp_huge tempfile '/tmp/temp_huge' size 2048m
/
Tablespace created.



SQL> !df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/hda2 74807888 41999616 29008240 60% /
/dev/hda1 102454 14931 82233 16%
/bootnone 1030804 0 1030804 0% /dev/shm



NOTE: df is a Unix command to show “disk free”. This command showed that I have 29,008,368 K free in the file system containing /tmp before I added a 2gig tempfile to the database. After I added that file, I had 29,008,240 K free in the file system. Apparently it only took 128 K of storage to hold that file. But if we “ls” it:



SQL> !ls -l
/tmp/temp_huge-rw-rw---- 1 ora10g ora10g 2147491840 Jan 2 16:34 /tmp/temp_huge


It appears to be a normal “2 gig” file. But it is only consuming some 128 K of storage. The reason I point this out is because I would be able to actually create hundreds of these two gigabyte temporary files – even though I have roughly 29 GIG free. Sounds great – free storage for all! The problem is as we started to use these temporary files and they started expanding out – you would rapidly hit errors stating “no more space”. Since the space is allocated as needed, you stand a definite chance of running out of room (especially if after you create the tempfiles someone else fills up the file system with other stuff). How to solve this differs from OS to OS, on Linux some of the options are to use ‘dd’ to fill the file or use ‘cp’ to create a non-sparse file. For example:



SQL> !cp --sparse=never /tmp/temp_huge /tmp/temp_huge2

SQL> !df
Filesystem 1K-blocks Used Available Use% Mounted on

/dev/hda2 74807888 44099336 26908520 63% /
/dev/hda1 102454 14931 82233 16% /boot
none 1030804 0 1030804 0% /dev/shm


SQL> drop tablespace temp_huge;
Tablespace dropped.


SQL> create temporary tablespace temp_huge 2 tempfile '/tmp/temp_huge2' reuse; Tablespace created.


SQL> !df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/hda2 74807888 44099396 26908460 63% /
/dev/hda1 102454 14931 82233 16% /boot
none 1030804 0 1030804 0% /dev/shm


After copying the sparse 2 G file to “/tmp/temp_huge2” and creating the temporary tablespace using that tempfile with the REUSE option – we are assured that tempfile has allocated all of its file system space and our database actually has 2 G of temporary space to work with.


I would also recommend you to read the below Metalink articles and a discussion from asktom if you would like to know more about it:

Metalink Notes: 1020110.6 251336.1 121732.1
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:34014729642755