ORA-00600 internal error caused by /tmp swap full
Today we encountered a problem when oracle failed to functioning. After some checking, this error was caused by /tmp running out of space. This also confirmed by OS logs:
Jun 20 17:43:59 tmpfs: [ID 518458 kern.warning] WARNING: /tmp: File system full, swap space limit exceeded
Oracle uses /tmp to compile PL/SQL code, so if there no space it unable to compile/execute. Which causing functions/procedures/packeges and trigers to timeout. The same also described in oracle note: ID 1389623.1
So in order to prevent further occurrences of this error, we should increase /tmp on the system to at least 4Gb.
There is an Oracle parameter to change the default location of these temporary files(_ncomp_shared_objects_dir), but it's not a dynamic parameter. And also, while there is a way to resize a tmpfs filesystem online but it's somehow risky. So the best idea is that, we firstly bring down Oracle DB on this host, then modify /etc/vfstab, and then reboot the whole system. This way will protect our data against the risk of corruption or lost etc, also it'll have some outage time.
So finally, here's the steps:
Amend the line in /etc/vfstab from:
swap - /tmp tmpfs - yes size=512m
swap - /tmp tmpfs - yes size=4096m
Reboot machine and bring up oracle DB