ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY

(Warning: This contains a script that I wrote based on the instructions provided by Oracle. Use of this script is at your own risk. Please review it for accuracy before using).

One of my more problematic databases gave me trouble recently due to excessive growth of the SYSAUX table. Is is Oracle 11.2.0.3.0. It had grown to 120GB and was continuing. I have SQL Plan Baselines on and those take a lot of space, but this was getting ridiculous. I did get the error “ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY” which pointed me to the problem. In this case, it was an Oracle bug where the database leaves orphaned records in  WRH$_ACTIVE_SESSION_HISTORY.

I’ve seen articles indicating there are other things that can cause excessive growth in SYSAUX, so you can check with the following query:

select occupant_name, space_usage_kbytes from v$sysaux_occupants order by 2;
OCCUPANT_NAME         SPACE_USAGE_KBYTES
--------------------- ------------------
...
SQL_MANAGEMENT_BASE   26,306,048
SM/AWR                71,037,824

In this case you I could see that the SM/AWR was the largest consumer, and watching it you could see it grow. You can also see this using @?/rdbms/admin/awrinfo.sql

COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
ASH 40,744.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_3817148667_12500 - 98% TABLE PARTITION
ASH 8,104.0 WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_3817148667_12500 - 61% INDEX PARTITION

Researching in MOS I found the following article that described the problem –

AWR Data Uses Significant Space in the SYSAUX Tablespace (Doc ID 287679.1)

The following query will tell you if you have this problem (even before you might have space issues):

SELECT COUNT(1) Orphaned_ASH_Rows
FROM wrh$_active_session_history a
WHERE NOT EXISTS
(SELECT 1
FROM wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
);
ORPHANED_ASH_ROWS
-----------------
122,845,480

Here we can see that I had a LOT of orphaned rows. I needed to get rid of them and then shrink the table (per Oracle of course).

Per Oracle the steps to fix this are as follows:

DELETE
FROM wrh$_active_session_history a
WHERE NOT EXISTS
 (SELECT 1
 FROM wrm$_snapshot
 WHERE snap_id = a.snap_id
 AND dbid = a.dbid
 AND instance_number = a.instance_number
 );
alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;

The only problem is this is just a workaround to cleanup. If you don’t patch this will just keep happening. I decided to create a job to do this work, but wanted a way to know it is out there running and, if so, provide useful information to anyone finding it. I decided on the following solution that creates a trace file when it runs to provide information somewhere the Administrator will see it. You can of course do it however you wish.

Here is my script for maintaining the cleanup regularly. It has been running for months now and the growth is under control.

This is written to be run from SQL*Plus.

spool ash_purge_job.lst
prompt 2013/10/28 by Jed S. Walker
prompt This procedure is to handle an 11.2.0 bug that results in orphaned records in wrh$_active_session_history
prompt this procedure runs against SYS data so be careful.
prompt As a pre-caution it should not run as the SYS user
prompt Due to this the procedure generates a trace file on completion so that there is
prompt something available to make sure it isn't totally forgotten.
prompt
prompt Hit Enter to continue
prompt
accept continue

-- Do user level stuff
whenever sqlerror exit
accept running_user prompt "Enter the name of the user that will own/run this job: "
accept running_pwd prompt "Enter the password for user &&running_user : "
-- grant necessary privileges only
connect / as sysdba
grant select on sys.wrm$_snapshot to &&running_user;
grant select,delete on wrh$_active_session_history to &&running_user;
grant execute on dbms_system to &&running_user;
-- get password, connect, and install
spool off
connect &&running_user/&&running_pwd
spool ash_purge_job.lst append
whenever sqlerror continue

-- create the procedure
create procedure clean_ash 
is
 v_proc_name varchar2(35):='clean_ash';
 v_user_name varchar2(35):='&&running_user';
 v_commit_size number:=10000;
 v_commit_count number;
 v_total_count number;
 v_notes varchar2(4000);
begin
 v_total_count:=0; --initialize
 loop
 DELETE
 FROM sys.wrh$_active_session_history a
 WHERE NOT EXISTS (SELECT 1
 FROM sys.wrm$_snapshot
 WHERE snap_id = a.snap_id
 AND dbid = a.dbid
 AND instance_number = a.instance_number
 )
 and rownum <= v_commit_size;
 v_commit_count:=sql%rowcount;
 commit;
 v_total_count:=v_total_count+v_commit_count;
 if v_commit_count = 0 then
 exit; -- end loop for now
 end if;
 end loop;
 -- write a note for the trace file
 v_notes:=''; -- start variable
 v_notes:=v_notes || 'Written by Jed S. Walker October 28th, 2013 ' || chr(10);
 v_notes:=v_notes || 'WARNING! The author of this script has no liability for any damage caused by your use of this script.' || chr(10);
 v_notes:=v_notes || 'Please reference MOS ID 387914.1' || chr(10);
 v_notes:=v_notes || 'This is a trace file from an automated job running ' || v_user_name || '.' || v_proc_name || chr(10);
 v_notes:=v_notes || 'This job removes orphaned records in WRH$_ACTIVE_SESSION_HISTORY due to an Oracle 11.2.0.x bug' || chr(10);
 v_notes:=v_notes || 'You should make sure the bug still applies if you continue to run this job in releases above 11.2.0.3 ' || chr(10);
 v_notes:=v_notes || 'If your SYSAUX tablespace, specifically component SM/AWR, has grown too large for comfort, you should run' || chr(10);
 v_notes:=v_notes || 'alter table WRH\$_ACTIVE_SESSION_HISTORY shrink space;' || chr(10);
 v_notes:=v_notes || 'to recover the space after this script has cleaned out the orphaned records.' || chr(10);
 v_notes:=v_notes || '' || chr(10);
 v_notes:=v_notes || '' || chr(10);
 if v_total_count = 0 then
 v_notes:=v_notes || 'ORA-20444: This run removed ' || v_total_count || ' orphaned records.' || chr(10);
 v_notes:=v_notes || 'If the count remains zero for several days, then chances are' || chr(10);
 v_notes:=v_notes || 'you are on a version higher than 11.2.0.3 and this bug may have been fixed.' || chr(10);
 v_notes:=v_notes || 'Verify the bug fix and if good then you should remove this job to avoid any unintended consequences.' || chr(10);
 else
 v_notes:=v_notes || 'This run removed ' || v_total_count || ' orphaned records.' || chr(10);
 v_notes:=v_notes || 'It appears the bug is not fixed so you should continue to let this run unless Oracle Support recommends otherwise.' || chr(10);
 v_notes:=v_notes || 'Have a great day!' || chr(10);
 end if;
 -- create the trace file
 sys.dbms_system.ksdwrt(1,v_notes);
exception
 when others then
 v_notes:=v_notes || 'ORA-20445: ' || v_user_name || '.' || v_proc_name || ' failed.' || chr(10);
 v_notes:=v_notes || 'Oracle error "' ||SQLERRM|| chr(10);
 sys.dbms_system.ksdwrt(1,v_notes);
end clean_ash;
/
show errors
-- create the job to run once per day so table never grows too big
begin
 dbms_scheduler.create_job(job_name=>'CLEAN_ASH_JOB',
 job_type=>'STORED_PROCEDURE',
 job_action=>'clean_ash',
 number_of_arguments=>0,
 repeat_interval=>'FREQ=Daily;BYHOUR=12;BYMINUTE=0',
 enabled=>true,
 comments=>'Special job to mitigate MOS ID 387914.1 in Oracle 11.2.0.3.x, see trace files for notes.');
end;
/
-- verify
set pages 50 linesize 120
col owner format a20
col object_type format a20
col object_name format a20
col status format a20
select owner, object_type, object_name, status from dba_objects where object_name in ('CLEAN_ASH','CLEAN_ASH_JOB');

prompt Done, please check for errors
prompt You are currently logged in as:
show user
spool off

Note that if you’re running on RAC the output trace file could be on any of the nodes – so you will have to check each one, or check the job scheduler for where it ran:

select log_date, owner, job_name, status, error#, actual_start_date,run_duration, instance_id 
from dba_scheduler_job_run_details
where job_name = 'CLEAN_ASH_JOB' 
order by actual_start_date;

Here is an example of the output:

/oracle/diag/rdbms/prd/prd_4/trace/prd_4_j000_5701.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/11.2.0.3/db
System name: Linux
Node name: rac-node-04
Release: 2.6.18-238.el5
Version: #1 SMP Sun Dec 19 14:22:44 EST 2010
Machine: x86_64
Instance name: prd_4
Redo thread mounted by this instance: 2
Oracle process number: 63
Unix process pid: 5701, image: oracle@rac-node-04 (J000)

*** 2013-10-29 12:00:21.460
*** SESSION ID:(4403.21091) 2013-10-29 12:00:21.460
*** CLIENT ID:() 2013-10-29 12:00:21.460
*** SERVICE NAME:(SYS$USERS) 2013-10-29 12:00:21.460
*** MODULE NAME:(DBMS_SCHEDULER) 2013-10-29 12:00:21.460
*** ACTION NAME:(CLEAN_ASH_JOB) 2013-10-29 12:00:21.460
Written by Jed S. Walker October 28th, 2013
WARNING! The author of this script has no liability for any damage caused by your use of this script.
Please reference MOS ID 387914.1
This is a trace file from an automated job running dba.clean_ash
This job removes orphaned records in WRH$_ACTIVE_SESSION_HISTORY due to an Oracle 11.2.0.x bug
You should make sure the bug still applies if you continue to run this job in releases above 11.2.0.3
If your SYSAUX tablespace, specifically component SM/AWR, has grown too large for comfort, you should run
alter table WRH\$_ACTIVE_SESSION_HISTORY shrink space;
to recover the space after this script has cleaned out the orphaned records.
This run removed 62301 orphaned records.
It appears the bug is not fixed so you should continue to let this run unless Oracle Support recommends otherwise.
Have a great day!

In the case it finds no records to cleanup, it displays the following with an ORA- message so that hopefully your alerting will detect it:

ORA-20444: This run removed 0 orphaned records.
If the count remains zero for several days, then chances are
you are on a version higher than 11.2.0.3 and this bug may have been fixed.
Verify the bug fix and if good then you should remove this job to avoid any unintended consequences.

Enjoy!

Advertisements

3 responses to “ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY

  1. Pingback: ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY | Jed's

  2. Hey I’m not sure whether or not it’s me or perhaps yuor web blog but it
    is loading pretty slow , I had to spend like a minute or two to successfully load still , facebook
    does work perfectly . Well, Thank you for writing such a great
    articles. I do believe it has already been extremely
    helpful visitor who actually click here. I ought to point out that you really have done amazing job with this as well as expect to discover much more wonderful things through you.
    I have got you saved to my bookmarks to see blogs
    you publish.

    • Thank you. I’m sorry it was slow to load. My site is from WordPress and I’ve never noticed it slow myself, but there are many reasons that could happen.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s