• ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY
    ORACLE/Trouble shooting 2015.07.31 15:48
    주의: 여기에 사용된 스크립트는 오라클에서 제공되어진 스크립트를 베이스로 작성되었으나, 사용함에 있어서 리스크가 있음을 명심해야 하며, 사용 전 자신의 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 스크립트를 이용해 조회 가능


    COMPONENT        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
    (SELECT 1
    FROM wrm$_snapshot
    WHERE snap_id = a.snap_id
    AND dbid = a.dbid
    AND instance_number = a.instance_number




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

    FROM wrh$_active_session_history a
     (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에 맞게 수정해서 사용.

    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 Hit Enter to continue
    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
     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);
     v_total_count:=0; --initialize
     FROM sys.wrh$_active_session_history a
     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;
     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 ' || 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 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);
     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
     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);
    end clean_ash;
    show errors
    -- create the job to run once per day so table never grows too big
     comments=>'Special job to mitigate MOS ID 387914.1 in Oracle, see trace files for notes.');
    -- 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;

    댓글 0

Designed by black7375.

티스토리 툴바