How to resolve ORA-00490 error?

How to resolve ORA-00490 error?

Sometimes all of a sudden our Instance will be crashed and when looked at alert log file we found below error message

ORA-00490: PSP process terminated with error

While PSP process itself terminated due to any error the whole instance is crashed with ORA-00490 error message.

On further check you may found one more error also in the alert log file which describes as

ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

When operating system is encountering with some unknown error like insufficient space in temp Area or swap Area or insufficient system resources then Oracle throws above errors

Same time PMON is terminating instance with following error with process id of PMON process. Because Oracle processes are being unmanageable of Oracle database instance.

PMON: terminating instance due to error 490
Instance terminated by PMON, pid = 20094

Root Cause:

This error will occur when there is no free space available in swap area of System for spawning new process of Oracle. Due to this reason Process SPwaner process PSP0 (with ORA-00490 error code of Oracle) terminated because it doesn’t able to manage or create Oracle processes. Result is Oracle instance crashed by PMON process with errorstack 490 (which is pointing out ORA-00490). If lack of system resource found then also same situation can be occurring.

Solution :

There are 2 solutions for this problem which are mentioned below

1. Check your swap space and increase swap area in system. Because due to lack of space in swap are Oracle unable to create new process and PSP0 Process SPwaner is unable to manage Oracle process.

2. Check “ulimit” setting for Oracle. “ulimit” is for user shell limitation. If maximum shell limit is reached then also PSP0 process becomes unstable to manage other Oracle processes.Increase the “ulimit” setting for Oracle user.

spfile get created in the directory DB_UNKNOWN

Create database SPFILE Under ASM

[Why does the spfile get created in the directory DB_UNKNOWN?]

On 10.2.0.1 in Production:

-- Suppose that the database instance name is "osmdr"  

-- In the database instance

When trying to create a new spfile from a pfile under sqlplus

SQL> create SPFILE='+DATA/osmdr/spfileosmdr.ora' from pfile='/home/oracle/osmdr.init';
File created.

SQL>create SPFILE='+DATA/osmdr/spfileosmdr.ora' from PFILE='/home/oracle/osmdr.init';   File created

Under ASM , the spileosmdr.ora is link to the wrong location under ASM

ASMCMD> ls -alr
Type Redund Striped Time Sys Name
N spfileosmdr.ora =>
+DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.272.613162051
ASMCMD>

Q. Why it will create +DATA/DB_UNKNOW instead of +DATA/osmdr ? 
The same question can be like that

Q. Why does the spfile get created in the directory DB_UNKNOWN?


IT'S EXPECTED BEHAVIOR 

CAUSE

-- Considering the TESTCASE hereunder

The string "DB_UNKNOWN/PARAMETERFILE/SPFILE" is a generic system tag for the proxied spfile creation. This will be used in case the database instance has no open client session to the ASM instance at the time of the 'create spfile' command. That is, the ASM instance is not aware of the db name and therefore uses "DB_UNKNOWN".

Please see the TESTCASE section above that demonstrates two scenarios; [A] where the DB_UNKNOWN
directory structure gets created, and [B] where the spfile is created in the directory //PARAMETERFILE.

SOLUTION

TESTCASE

[A] No open database connection to the ASM instance

-- In the ASM instance 

SQL> select * from v$asm_client;

no rows selected 

-- In the database instance

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string V1020

SQL> select * from v$asm_client;

no rows selected

SQL> create spfile='+DG1' from pfile;

File created.

-- In asmcmd
-- The DB_UNKNOWN directory structure gets created

ASMCMD> pwd
+dg1
ASMCMD> ls
DB_UNKNOWN/
V1020/
ASMCMD> ls DB_UNKNOWN/
PARAMETERFILE/
ASMCMD> ls DB_UNKNOWN/PARAMETERFILE/
SPFILE.259.613339345

-- Remove the parameter file from the ASM diskgroup

ASMCMD> rm DB_UNKNOWN/PARAMETERFILE/SPFILE.259.613339345

-- Now the DB_UNKNOWN directory structure gets automatically removed

[B]

-- In the database instance
-- Open a connection to the ASM instance by accessing a datafile in an ASM diskgro
-- In this example, a tablespace with a datafile in an ASM diskgroup is onlined

SQL> alter tablespace ts1 online;

Tablespace altered.

SQL> select * from v$asm_client;

GROUP_NUMBER INSTANCE_NAM DB_NAME STATUS SOFTWARE
------------ ------------ -------- ------------ -
1 +ASM V1020 CONNECTED 10.2.0.3.0 10.2.0.0.0

-- In the ASM instance

SQL> select * from v$asm_client;

GROUP_NUMBER INSTANCE_NAM DB_NAME STATUS SOFTW
------------ ------------ -------- -----------
1 V1020 V1020 CONNECTED 10.2.0.3.0 10.2.0.1.0

-- In the database instance

SQL> create spfile='+DG1' from pfile;

File created.

-- In asmcmd

ASMCMD> pwd
+dg1
ASMCMD> ls
V1020/
ASMCMD> ls V1020/
DATAFILE/
PARAMETERFILE/
ASMCMD> ls V1020/PARAMETERFILE/
spfile.259.613339813

-- Result:
- The spfile is located in the directory //PARAMETERFILE
- No DB_UNKNOWN directory structure got created