ORA-00490

ORA-00490: PSP process terminated with error

Cause: The process spawner died

Action: Warm start instance

Note:

The following is an example of ORA-00490.

% more alert.log:

Wed Aug 13 20:20:58 2008

Completed: ALTER DATABASE OPEN

Wed Aug 13 21:00:39 2008

Process startup failed, error stack:

Wed Aug 13 21:01:07 2008

Errors in file …/bdump/mydb_pmon_13768.trc:

ORA-00490: PSP process terminated with error

Wed Aug 13 21:01:07 2008

PMON: terminating instance due to error 490

Instance terminated by PMON, pid = 13768

% more …/bdump/mydb_pmon_13768.trc:

*** 2008-08-13 21:01:07.535

*** SERVICE NAME:(SYS$BACKGROUND) 2008-08-13 21:01:07.534

*** SESSION ID:(170.1) 2008-08-13 21:01:07.534

Background process PSP0 found dead

Oracle pid = 3

OS pid (from detached process) = 13770

OS pid (from process state) = 13770

dtp = 38000af80, proc = 3a4654618

Dump of memory from 0x000000038000AF80 to 0x000000038000AFC8

......

3A4654DF0 00000000 00000000 00000002 00000000 [................]

error 490 detected in background process

ORA-00490: PSP process terminated with error

Solution:

These errors are likely to occur when there is resource crunch (Physical /Swap Space exhaust) or other ulimit parameters are set to low value at the OS level.

The cause of the problem is that the OS can't spawn a new process; which may be related to lack of privilege or system resources (it could be any thing at the OS level). Check the OS message log file to verify any memory/swap related message logged at the time of error.

% df -h

Filesystem size used avail capacity Mounted on

swap 12G 552K 12G 1% /tmp

% sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 14 14:30:36 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba

Connected to an idle instance.

SQL> !ps -ef | grep pmon

oracle 22030 21949 0 14:30:50 pts/9 0:00 grep pmon

SQL> startup

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1980584 bytes

Variable Size 578815832 bytes

Database Buffers 25165824 bytes

Redo Buffers 6406144 bytes

Database mounted.

Database opened.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

More Oracle DBA tips, please visit Oracle DBA Tips 

V$UNDOSTAT

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================
V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns NULL values if the system is in manual undo management mode.
Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 576 rows, spanning a 4 day cycle.

Column	Datatype	Description
BEGIN_TIME	DATE	Identifies the beginning of the time interval
END_TIME	DATE	Identifies the end of the time interval
UNDOTSN	NUMBER	Represents the last active undo tablespace in the duration of time. The tablespace ID of the active undo tablespace is returned in this column. If more than one undo tablespace was active in that period, the active undo tablespace that was active at the end of the period is reported.
UNDOBLKS	NUMBER	Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system.
TXNCOUNT	NUMBER	Identifies the total number of transactions executed within the period
MAXQUERYLEN	NUMBER	Identifies the length of the longest query (in seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter. The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor. Only the length of those cursors that have been fetched/executed during the period are reflected in the view.
MAXQUERYID	VARCHAR2(13)	SQL identifier of the longest running SQL statement in the period
MAXCONCURRENCY	NUMBER	Identifies the highest number of transactions executed concurrently within the period
UNXPSTEALCNT	NUMBER	Number of attempts to obtain undo space by stealing unexpired extents from other transactions
UNXPBLKRELCNT	NUMBER	Number of unexpired blocks removed from certain undo segments so they can be used by other transactions
UNXPBLKREUCNT	NUMBER	Number of unexpired undo blocks reused by transactions
EXPSTEALCNT	NUMBER	Number of attempts to steal expired undo blocks from other undo segments
EXPBLKRELCNT	NUMBER	Number of expired undo blocks stolen from other undo segments
EXPBLKREUCNT	NUMBER	Number of expired undo blocks reused within the same undo segments
SSOLDERRCNT	NUMBER	Identifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error.
NOSPACEERRCNT	NUMBER	Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace.
ACTIVEBLKS	NUMBER	Total number of blocks in the active extents of the undo tablespace for the instance at the sampled time in the period
UNEXPIREDBLKS	NUMBER	Total number of blocks in the unexpired extents of the undo tablespace for the instance at the sampled time in the period
EXPIREDBLKS	NUMBER	Total number of blocks in the expired extents of the undo tablespace for the instance at the sampled time in the period
TUNED_UNDORETENTION	NUMBER	Amount of time (in seconds) for which undo will not be recycled from the time it was committed. At any point in time, the latest value of TUNED_UNDORETENTION is used to determine whether data committed at a particular time in the past can be recycled.

Note:

1.       This V$UNDOSTAT view contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management (ASM) mode.

The V$UNDOSTAT view is useful for monitoring the effects of transaction execution on undo space in the current instance. Statistics are available for UNDO space consumption, transaction concurrency, the tuning of undo retention, and the length and SQL ID of long-running queries in the instance.

2.       Explain the meaning of columns:

When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is an indication of space pressure.

If the column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.

If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.

3.       In 10g DBA_HIST_UNDOSTAT view contains statistical snapshots of V$UNDOSTAT information.

4.       Oracle initialization parameter UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention.  The parameter can only be honoured if the current undo tablespace has enough space.

5.       The amount of time for which undo is retained for the Oracle Database for the current undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.

6.       Scripts using

-- See how much undo information has been written in the past hour. select begin_time, end_time, undoblks, txncount,        maxconcurrency, nospaceerrcnt from   V$UNDOSTAT where  begin_time > sysdate-(1/24); BEGIN_TI END_TIME   UNDOBLKS   TXNCOUNT MAXCONCURRENCY NOSPACEERRCNT -------- -------- ---------- ---------- -------------- ------------- 10:11:49 10:19:56        155        228              3             0 10:01:49 10:11:49        177        763              3             0 09:51:49 10:01:49        150        593              3             0 09:41:49 09:51:49        398       1797              5             0 09:31:49 09:41:49       1340       7060              5             0 09:21:49 09:31:49        163       2124              4             0 Note: This query shows how much redo has been used over the last hour. Here we can see that there have been 7060 transactions from 09:31 – 09:41 using 1340 undo blocks and none of them received any errors due to space management.

-- To find the time of the longest query in the instance:   select begin_time, MAXQUERYLEN, undoblks from   V$UNDOSTAT where  begin_time > sysdate-(1/12);   BEGIN_TI MAXQUERYLEN   UNDOBLKS -------- ----------- ---------- 10:21:49           0          2 10:11:49           0        223 10:01:49           0        177 09:51:49           0        150 09:41:49           0        398 09:31:49          36       1340   <--- 36 seconds 09:21:49           0        163 09:11:49           0         82 09:01:49           0        326 08:51:49           0        253 08:41:49           0        186   Note: This shows that the longest query that has been run was 36 seconds long. To ensure this query never gets ORA-01555 set UNDO_RETENTION to at least 36 seconds. If UNDO_RETENTION is set too high the UNDO tablespace will need to be very large. If to low ORA-01555 may occur to often.

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-29 Thursday

V$SORT_SEGMENT

Oracle 11gR1

V$SORT_SEGMENT displays information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.

Column	Datatype	Description
TABLESPACE_NAME	VARCHAR2(31)	Name of the tablespace
SEGMENT_FILE	NUMBER	File number of the first extent
SEGMENT_BLOCK	NUMBER	Block number of the first extent
EXTENT_SIZE	NUMBER	Extent size
CURRENT_USERS	NUMBER	Number of active users of the segment
TOTAL_EXTENTS	NUMBER	Total number of extents in the segment
TOTAL_BLOCKS	NUMBER	Total number of blocks in the segment
USED_EXTENTS	NUMBER	Extents allocated to active sorts
USED_BLOCKS	NUMBER	Blocks allocated to active sorts
FREE_EXTENTS	NUMBER	Extents not allocated to any sort
FREE_BLOCKS	NUMBER	Blocks not allocated to any sort
ADDED_EXTENTS	NUMBER	Number of extent allocations
EXTENT_HITS	NUMBER	Number of times an unused extent was found in the pool
FREED_EXTENTS	NUMBER	Number of deallocated extents
FREE_REQUESTS	NUMBER	Number of requests to deallocate
MAX_SIZE	NUMBER	Maximum number of extents ever used
MAX_BLOCKS	NUMBER	Maximum number of blocks ever used
MAX_USED_SIZE	NUMBER	Maximum number of extents used by all sorts
MAX_USED_BLOCKS	NUMBER	Maximum number of blocks used by all sorts
MAX_SORT_SIZE	NUMBER	Maximum number of extents used by an individual sort
MAX_SORT_BLOCKS	NUMBER	Maximum number of blocks used by an individual sort
RELATIVE_FNO	NUMBER	Relative file number of the sort segment header

Note:

1.

select tablespace_name, segment_file, extent_size, current_users, used_extents, used_blocks from v$sort_segment;

TABLESPACE_NAME	SEGMENT_FILE	EXTENT_SIZE	CURRENT_USERS	USED_EXTENTS	USED_BLOCKS
TEMP_APP	0	64	0	0	0
TEMP	0	64	5	406	25984

2. You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view.

Oracle dynamic performance views

V$TEMPSEG_USAGE

 Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================


V$TEMPSEG_USAGE describes temporary segment usage.

Column	Datatype	Description
USERNAME	VARCHAR2(30)	User who requested temporary space
USER	VARCHAR2(30)	This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in USERNAME.
SESSION_ADDR	RAW(4 | 8)	Address of shared SQL cursor
SESSION_NUM	NUMBER	Serial number of session
SQLADDR	RAW(4 | 8)	Address of SQL statement
SQLHASH	NUMBER	Hash value of SQL statement
SQL_ID	VARCHAR2(13)	SQL identifier of SQL statement
TABLESPACE	VARCHAR2(31)	Tablespace in which space is allocated
CONTENTS	VARCHAR2(9)	Indicates whether tablespace is TEMPORARY or PERMANENT
SEGTYPE	VARCHAR2(9)	Type of sort segment: · SORT · HASH · DATA · INDEX · LOB_DATA · LOB_INDEX
SEGFILE#	NUMBER	File number of initial extent
SEGBLK#	NUMBER	Block number of the initial extent
EXTENTS	NUMBER	Extents allocated to the sort
BLOCKS	NUMBER	Extents in blocks allocated to the sort
SEGRFNO#	NUMBER	Relative file number of initial extent

Note:

1. The script to find who and what sql is using temp segments

For 8.1.7 to 9.2	SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$session a, v$sort_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value ORDER BY b.tablespace, b.blocks;
For 10.1 and above	SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$session a, v$tempseg_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value ORDER BY b.tablespace, b.blocks;

2. The V$TEMPSEG_USAGE view identifies the current sort users in the temporary tablespace sort segments.

Oracle data dictionary views

Oracle dynamic performance views

Last updated: August 4, 2009

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

I have stopped updating the blog.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================

DBA_TEMP_FILES

Oracle 11gR1

DBA_TEMP_FILES describes all temporary files (tempfiles) in the database.

Column	Datatype	NULL	Description
FILE_NAME	VARCHAR2(513)		Name of the database temp file
FILE_ID	NUMBER		File identifier number of the database temp file
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace to which the file belongs
BYTES	NUMBER		Size of the file (in bytes)
BLOCKS	NUMBER		Size of the file (in Oracle blocks)
STATUS	CHAR(9)		File status: · AVAILABLE
RELATIVE_FNO	NUMBER		Tablespace-relative file number
AUTOEXTENSIBLE	VARCHAR2(3)		Indicates whether the file is autoextensible (YES) or not (NO)
MAXBYTES	NUMBER		maximum size of the file (in bytes)
MAXBLOCKS	NUMBER		Maximum size of the file (in Oracle blocks)
INCREMENT_BY	NUMBER		Default increment for autoextension
USER_BYTES	NUMBER		Size of the useful portion of the file (in bytes)
USER_BLOCKS	NUMBER		Size of the useful portion of the file (in Oracle blocks)

Note:

1.

select * from dba_temp_files;

FILE_NAME	FILE _ID	TABLESPACE _NAME	BYTES	BLOCKS	STATUS	RELATIVE _FNO	AUTOEXTENSIBLE	USER _BYTES	USER _BLOCKS
/u04//temp01.dbf	1	TEMP	2097152000	128000	AVAILABLE	1	NO	2096103424	127936
/u04//temp02.dbf	2	TEMP	2097152000	128000	AVAILABLE	2	NO	2096103424	127936
/u04//temp_app01.dbf	9	TEMP_APP	2097152000	128000	AVAILABLE	1	NO	2096103424	127936
/u04//temp_app02.dbf	10	TEMP_APP	2097152000	128000	AVAILABLE	2	NO	2096103424	127936
/u04//temp_app03.dbf	11	TEMP_APP	2097152000	128000	AVAILABLE	3	NO	2096103424	127936

2. The V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.

3. Example of creating temporary tablespace:

CREATE TEMPORARY TABLESPACE temp TEMPFILE ‘/u04/temp01.dbf’ SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

4. On some operating systems (such as Solaris), the database does not allocate space for the tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. The workaround is to move tempfiles to another place and move them back to have disk space allocated.

Oracle data dictionary views