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

트러블 슈팅

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

주의: 여기에 사용된 스크립트는 오라클에서 제공되어진 스크립트를 베이스로 작성되었으나, 사용함에 있어서 리스크가 있음을 명심해야 하며, 사용 전 자신의 DB에 맞는 정확한 내용인지 확인하시기 바랍니다.

SYSAUX에 용량은 있는데 SYS.WRH$_ACTIVE_SESSION_HISTORY 테이블를 확장 할 수 없다고 나올때 처리하는 방법입니다.

SYSAUX의 사용 내역을 확인.

SQL> select occupant_name, space_usage_kbytes from v$sysaux_occupants order by 2;

OCCUPANT_NAME                                                    SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
AUDIT_TABLES                                                                      0
ORDIM/ORDPLUGINS                                                                  0
ORDIM/SI_INFORMTN_SCHEMA                                                          0
ULTRASEARCH                                                                       0
TSM                                                                               0
ULTRASEARCH_DEMO_USER                                                             0
STATSPACK                                                                         0
AUTO_TASK                                                                       384
ORDIM                                                                           448
EM_MONITORING_USER                                                              768
STREAMS                                                                        1024
LOGSTDBY                                                                       1408
JOB_SCHEDULER                                                                  1472
PL/SCOPE                                                                       1600
SQL_MANAGEMENT_BASE                                                            1728
SMON_SCN_TIME                                                                  3328
WM                                                                             3584
EXPRESSION_FILTER                                                              3712
TEXT                                                                           3840
SM/OTHER                                                                       8448
XSAMD                                                                          9024
LOGMNR                                                                        13696
ORDIM/ORDDATA                                                                 13888
SM/ADVISOR                                                                    22272
AO                                                                            39104
XSOQHIST                                                                      39104
EM                                                                            47168
SDO                                                                           76032
SM/OPTSTAT                                                                    76288
XDB                                                                          130176
SM/AWR                                                                       550336

쿼리뿐만 아니라 @?/rdbms/admin/awrinfo.sql 스크립트를 이용해 조회 가능합니다.

OMPONENT        MB SEGMENT_NAME - % SPACE_USED                                           SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
...
ASH            19.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_2469019682_0          -  94%  TABLE PARTITION
ASH             3.0 WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_2469019682_0       -  79%  INDEX PARTITION
RAC             8.0 WRH$_DLM_MISC.WRH$_DLM_MI_2469019682_0                        -  93%  TABLE PARTITION

해당 문제관한 자료는 아래 문서에서 찾을 수 있습니다.

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

해당 쿼리를 통해 ASH ROW를 확인할수 있습니다.

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

해당 예제에는 row가 몇개 없지만 이 것이 굉장히 많을 경우 아래 쿼리를 이용해서 공간을 정리 할 수 있습니다.

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;

위 과정은 단지 정리를 통해 해결 하는 방법으로, 패치를 하지 않으면 지속적으로 발생 할 수 있습니다.

아래와 같은 스크립트로 문제가 발생하는 것에 대한 추적 로그를 남길수 있는데, 각자의 DB에 맞게 수정해서 사용하시기 바랍니다.

pool 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

만약 RAC에서 사용하길 원한다면 각각의 노드에서 한번씩 사용합니다. 또는 아래 쿼리를 이용해서 job 스케쥴러에 등록합니다.

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;