Troubleshooting ORA-1652 Errors in RAC

    今天在alert日志中发如下错误：

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Thu Feb 24 22:01:38 EAT 2011
GATHER_STATS_JOB encountered errors.  Check the trace file.
Thu Feb 24 22:01:38 EAT 2011
Errors in file /oracle/product/admin/sczh/bdump/sczh1_j001_5976.trc:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Thu Feb 24 22:07:30 EAT 2011

最后通过加大TEMP表空间解决。

以下为metalink ID 280578.1提供的解决方法

PURPOSE

-------

To provide information on how to troubleshoot ORA-1652 errors in RAC.

SCOPE & APPLICATION

-------------------

This document is intended for DBA's and support analysts experiencing

ORA-1652 errors in a Real Application Clusters environment.

TROUBLESHOOTING ORA-1652 ERRORS IN RAC

--------------------------------------

The following is the generic error text associated with an ORA-1652 error:

Error:  ORA-1652

Text:   unable to extend temp segment by %s in tablespace %s

------- -----------------------------------------------------------------------

Cause:  Failed to allocate an extent for temp segment in tablespace.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated or create the object in another tablespace.

In a RAC enviornment, there are 2 scenerios where an ORA-1652 error can occur:

1. We are completely out of space in the temp tablespace.

2. Our local temp segment cannot extend but space for this temp tablespace is available on other instances.

To find out which scenerio we are hitting, run the following query:

  select sum(free_blocks) from gv$sort_segment where tablespace_name = ''

If the free blocks is '0' then we have hit scenerio 1 and are completely out of temp space.  In this case see Note 19047.1 "OERR:  ORA 1652 "unable to extend temp segment by %s in tablespace %s" for instructions.

If sufficent space is available from the query, we are likely hitting scenerio 2. 

ORA-1652 errors are handled differently in a RAC envoronment than a non-RAC envoronment when the local instance cannot extend it's temp segment.  In a non-RAC environment, an ORA-1652 can cause all SQL utilizing that tablespace to fail.  In RAC, we may be able to get additional sort segment space from  other instances.  To see how space is allocated across all instances, run

the following query:

  select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;

Total_blocks will show the total amount of temp segment space available for each instance.Used_Blocks will show how much of that space has been utilized.  Free_blocks will show how much space has been ALLOCATED to this  instance.  If you are getting ORA-1652 errors on an instance, you will likely see that used_blocks = total_blocks and free_blocks = 0.  If this happens, you may see ORA-1652 errors repeated in the alert log:

  Sun Aug  1 08:12:41 2004

  ORA-1652: unable to extend temp segment by 2048 in tablespace TEMP

  Sun Aug  1 08:12:51 2004

  ORA-1652: unable to extend temp segment by 2048 in tablespace TEMP

  Sun Aug  1 08:16:37 2004

  ORA-1652: unable to extend temp segment by 2048 in tablespace TEMP

 

When this happens, we are requesting free space from another instance.  This should be considered a warning to the DBA that there is instance contention for temporary space.  This may cause the instance to take longer to service internal temporary-space requests because inter-node coordination is required.

If there is severe temp segment space contention across instances, a slowdown can occur.  The following are potential workarounds:

  - Increase size of the temp tablespace

  - Do not use the DEFAULT temp tablespace feature in RAC.  Note that a default temp tablespace cannot be removed after it is created but you can assign users to use different temp tablespaces with:

  alter user  temporary tablespace ;

  - Increase sort_area_size and/or pga_aggregate_target

If there is a severe slowdown, it is likely that SMON is unable to process sort segment requests because it is busy doing other work.  The following diagnostics may be needed to diagnose inter-node sort segment contention:

- Output from the following query periodically during the problem:

  select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;

- Global hanganalyze and systemstate dumps as described in Note 206567.1.

RELATED DOCUMENTS

-----------------