Bug 14383007 workaround

We appear to be hitting this bug on our production Exadata RAC system:

Bug 14383007 – Sort runs out of temp space in RAC even when temp space is available

One particular job errors out with ORA-01652 errors on the TEMP tablespace even though there is tons of free space in the sort segments.  So, I got the idea of building a work around to have this job only login to the node that has the most free temp space.  Normally space just gets reallocated from the node that has it to the node that needs it.  But, I guess the bug is that in certain cases this doesn’t happen and you get the ORA-01652.

Here is my example unix script (actually this is run on an HP-UX box, don’t ask me why).

# This script demonstrates how to login to the instance
# of a RAC database that has the most free space in 
# the TEMP tablespace.  It takes three arguments
# which are the connection information for the RAC
# database:
# 1 - Oracle database username
# 2 - password
# 3 - connect string
#
# in sqlplus this would be like connecting as username/password@connectstring
#
# Step one - login to the RAC database and extract a connect string that
# connects to the node with the most free temp space.  
# This has several assumptions:
# 1 - temporary tablespace is named TEMP
# 2 - all of the temp space is allocated to sort segments
# 3 - the port number is 1521
NEW_CONNECT_STRING=`sqlplus -s /nolog<<EOF
connect $1/$2@$3
set heading off
set feedback off
set linesize 32000
set trimspool on
select 
'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = '||
host_name||
')(Port = 1521))) (CONNECT_DATA = (SID = '||
INSTANCE_NAME ||
')))'
from
gv\\$instance
where inst_id=
(select 
inst_id
from 
gv\\$sort_segment
where 
TABLESPACE_NAME='TEMP' and
FREE_BLOCKS =
(select max(FREE_BLOCKS)
from 
gv\\$sort_segment
where 
TABLESPACE_NAME='TEMP'));
exit
EOF
`
#trim the leading space from the connect string
NEW_CONNECT_STRING=`echo $NEW_CONNECT_STRING|sed 's/^ //'`
echo connect string = $1/$2@$NEW_CONNECT_STRING
# Now connect to the node using this connect string just to show
# that it works and query the sort segment view to show that this
# really is the instance with the most free temp space.
sqlplus /nolog<<EOF
set echo on
set termout on
set pagesize 1000
connect $1/$2@$NEW_CONNECT_STRING
-- show current instance
select instance_name from v\$instance;
-- list instances in descending order
-- of free blocks.  current instance
-- should be listed first.
select inst_id,free_blocks
from gv\$sort_segment
where 
TABLESPACE_NAME='TEMP'
order by free_blocks desc;
EOF

You wouldn’t want to use this except for a special case like this where you need a workaround for the bug.  Otherwise you would just end up running on one node and all the temp space would get allocated to it.  But, if you are hitting bug 14383007 this may be helpful.

– Bobby

 

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply