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
-----------------