OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.11.0.0
에러 : 오라클 19c SYSAUX 테이블스페이스 SM/ADVISOR 용량증가 시 대처방안
SYSAUX tablespace 용량이 SM/ADVISOR으로 인해 증가한 경우 대처방안
*모든 작업을 sysdba에서 함
alert log 확인
|
1
2
3
4
5
6
7
8
|
$ vi /app/oracle/diag/rdbms/orcl19/ORCL19/trace/alert_ORCL19.log
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_641"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
|
alert log에 위 메세지가 발생함
sysaux 테이블스페이스 사용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SQL>
col col0 format a18 heading 'TS Name'
col col1 format 9,999,999.99 heading 'Size(MB)'
col col2 format 9,999,999.99 heading 'Used(MB)'
col col3 format 9,999,999.99 heading 'Free(MB)'
col col4 format 9,999,999.99 heading '% Free'
col col5 format 9,999,999.99 heading 'Max(MB)'
select col0, col1, col3, col2, col5, col4
from (
select a.tablespace_name col0,
b.bytes/1024/1024 col1,
a.bytes/1024/1024 col3,
b.max/1024/1024 col5,
(b.bytes - a.bytes)/1024/1024 col2,
(a.bytes/ b.bytes) * 100 col4
from (
select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) a,
(
select tablespace_name,
sum(bytes) bytes,
sum(maxbytes) max
from dba_data_files
where tablespace_name = 'SYSAUX'
group by tablespace_name) b
where b.tablespace_name=a.tablespace_name)
order by col4 asc;
TS Name Size(MB) Free(MB) Used(MB) Max(MB) % Free
------------------ ------------- ------------- ------------- ------------- -------------
SYSAUX 21,200.00 215.62 20984.38 32,767.98 1.12
|
현재 sysaux 테이블스페이스가 20gb 사용중임
sysaux 테이블스페이스 내 세부 사용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
SQL>
col occupant_name for a20
col schema_name for a20
select occupant_name, schema_name, round(space_usage_kbytes/1024) MB
from v$sysaux_occupants
where space_usage_kbytes >= 1
order by 3 desc;
OCCUPANT_NAME SCHEMA_NAME MB
-------------------- -------------------- ----------
SM/ADVISOR SYS 20163
SM/AWR SYS 355
SM/OPTSTAT SYS 293
XDB XDB 63
SM/OTHER SYS 35
SQL_MANAGEMENT_BASE SYS 33
LOGMNR SYSTEM 11
JOB_SCHEDULER SYS 7
WM WMSYS 7
PL/SCOPE SYS 3
SMON_SCN_TIME SYS 3
AO SYS 2
STREAMS SYS 2
LOGSTDBY SYSTEM 2
AUTO_TASK SYS 1
EM_MONITORING_USER DBSNMP 1
16 rows selected.
|
SM/ADVISOR가 많은 용량을 차지하고있음
SM/ADVISOR 세부 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
col segment_name format a30
col owner format a10
col tablespace_name format a10
col segment_type format a15
select * from
(select segment_name,owner,tablespace_name,bytes/1024/1024 "size(mb)", segment_type
from dba_segments
where tablespace_name='SYSAUX'
order by bytes desc)
where rownum<=10;
SEGMENT_NAME OWNER TABLESPACE SIZE(MB) SEGMENT_TYPE
------------------------------ ---------- ---------- ---------- ---------------
WRI$_ADV_OBJECTS SYS SYSAUX 14500 TABLE
WRI$_ADV_OBJECTS_IDX_01 SYS SYSAUX 2700 INDEX
WRI$_ADV_OBJECTS_IDX_02 SYS SYSAUX 2700 INDEX
|
위 세그먼트들의 용량이 많음을 확인가능함
SM/ADVISOR 란?
SM/ADVISOR는 advisor frameworks 테이블을 참조함
SQL Tuning Advisor, SQL Access Advisor, ADDM과 같은 advisor가 이 인프라를 사용함
이러한 advisor는 AWR의 데이터를 사용하여 advise를 작성하지만 일단 생성되면 자체 테이블에 저장되고 SM/ADVISOR로 분류됨
원문
This refers to the advisor frameworks tables. Advisors like SQL Tuning Advisor, SQL Access Advisor, ADDM use this infrastructure. These advisors use the data in the AWR to build their advise, but once created this will be stored in their own tables, and that is classed under this heading.
해결 방법 : AUTO_STATS_ADVISOR_TASK 삭제 후 재생성 또는 보존기한 단축
방법1_1. AUTO_STATS_ADVISOR_TASK 데이터 삭제
기존 AUTO_STATS_ADVISOR_TAS 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
col name for a40
col how_created for a10
col owner_name for a10
select name, ctime, how_created, owner_name
from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
NAME CTIME HOW_CREATE OWNER_NAME
---------------------------------------- ------------ ---------- ----------
AUTO_STATS_ADVISOR_TASK 10-DEC-21 CMD SYS
INDIVIDUAL_STATS_ADVISOR_TASK 11-JAN-21 CMD SYS
|
AUTO_STATS_ADVISOR_TASK 가 존재함
AUTO_STATS_ADVISOR_TASK 데이터 삭제
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
PL/SQL procedure successfully completed.
|
위 명령 실행 시 WRI$_ADV_OBJECTS 테이블의 데이터가 지워짐
용량에 따라 몇분정도 소요됨
AUTO_STATS_ADVISOR_TASK 를 사용하지 않으려면
아래나오는 EXEC DBMS_STATS.INIT_PACKAGE(); 을 실행하지 않으면됨
TASK 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
col name for a40
col how_created for a10
col owner_name for a10
select name, ctime, how_created, owner_name
from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
NAME CTIME HOW_CREATE OWNER_NAME
---------------------------------------- ------------ ---------- ----------
INDIVIDUAL_STATS_ADVISOR_TASK 11-JAN-21 CMD SYS
|
데이터 삭제 작업 후 확인시 AUTO_STATS_ADVISOR_TASK 가 없음
WRI$_ADV_OBJECTS 테이블 재생성 및 인덱스 rebuild
|
1
2
3
4
5
6
|
SQL>
EXEC DBMS_STATS.INIT_PACKAGE();
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
|
TASK 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
col name for a40
col how_created for a10
col owner_name for a10
select name, ctime, how_created, owner_name
from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
NAME CTIME HOW_CREATE OWNER_NAME
---------------------------------------- ------------ ---------- ----------
AUTO_STATS_ADVISOR_TASK 17-DEC-21 CMD SYS
INDIVIDUAL_STATS_ADVISOR_TASK 11-JAN-21 CMD SYS
|
AUTO_STATS_ADVISOR_TASK 가 다시 생성됨
작업 후 sysaux tablespace 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SQL>
col col0 format a18 heading 'TS Name'
col col1 format 9,999,999.99 heading 'Size(MB)'
col col2 format 9,999,999.99 heading 'Used(MB)'
col col3 format 9,999,999.99 heading 'Free(MB)'
col col4 format 9,999,999.99 heading '% Free'
col col5 format 9,999,999.99 heading 'Max(MB)'
select col0, col1, col3, col2, col5, col4
from (
select a.tablespace_name col0,
b.bytes/1024/1024 col1,
a.bytes/1024/1024 col3,
b.max/1024/1024 col5,
(b.bytes - a.bytes)/1024/1024 col2,
(a.bytes/ b.bytes) * 100 col4
from (
select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) a,
(
select tablespace_name,
sum(bytes) bytes,
sum(maxbytes) max
from dba_data_files
where tablespace_name = 'SYSAUX'
group by tablespace_name) b
where b.tablespace_name=a.tablespace_name)
order by col4 asc;
TS Name Size(MB) Free(MB) Used(MB) Max(MB) % Free
------------------ ------------- ------------- ------------- ------------- -------------
SYSAUX 21,200.00 19215.62 1984.38 32,767.98 90.63
|
sysaux 사용량이 줄어듬
이 방법의 경우 삭제 후 한달이 지나면 다시 이전용량까지 sysaux가 증가할 수도 있음
(마이그레이션 등 일시적으로 통계정보를 대량 import 한 경우 제외)
방법1_2. AUTO_STATS_ADVISOR_TASK 데이터를 삭제하는 방법(수동 백업 삭제)
WRI$_ADV_OBJECTS 테이블에 AUTO_STATS_ADVISOR_TASK에 데이터가 너무 많으면 엄청난 UNDO가 필요함
이경우 과도한 redo/undo 생성을 방지하면서 데이터를 제거하기 위해 아래 방법을 사용할 수 있음
아래 작업을 수행 시 downtime 은 필요 없음, 그러나 피크 시간이 아닌 시간에 이 작업을 수행해야함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
Auto Stats Advisor 태스크에 대한 WRI$_ADV_OBJECTS의 row수 확인
SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS WHERE TASK_ID=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');
WRI$_ADV_OBJECTS에서 CTAS를 사용하여 row를 AUTO_STATS_ADVISOR_TASK와 분리
SQL> CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');
CTAS로 만들어진 백업용 WRI$_ADV_OBJECTS_NEW 테이블 count 확인
SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS_NEW;
WRI$_ADV_OBJECTS 테이블 truncate
SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;
백업용 WRI$_ADV_OBJECTS_NEW 테이블 데이터를 이용해서 WRI$_ADV_OBJECTS 데이터를 복원
SQL> INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;
SQL> COMMIT;
*19c 이상의 db에선 WRI$_ADV_OBJECTS 테이블에 SQL_ID_VC 컬럼이 추가되어 아래 구문을 사용해야함
SQL> INSERT INTO WRI$_ADV_OBJECTS("ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4" ,"ATTR5" ,
"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9" ,"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18",
"ATTR19","ATTR20","OTHER" ,"SPARE_N1" ,"SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3" ,"SPARE_C4" )
SELECT "ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,
"ATTR9" ,"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","OTHER" ,
"SPARE_N1" , "SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3" ,"SPARE_C4" FROM WRI$_ADV_OBJECTS_NEW;
SQL> COMMIT;
인덱스, PK rebuild
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
위 명령이 정상적으로 수행된 후 백업 테이블 삭제
SQL> DROP TABLE WRI$_ADV_OBJECTS_NEW PURGE;
|
이 방법의 경우 삭제 후 한달이 지나면 다시 이전용량까지 sysaux가 증가할 수도 있음
(마이그레이션 등 일시적으로 통계정보를 대량 import 한 경우 제외)
방법2. AUTO_STATS_ADVISOR_TASK 보존기한 단축
기존 AUTO_STATS_ADVISOR_TASK 의 보존기한 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
col name for a40
col value for a10
select name,value
from wri$_adv_parameters
where task_id in
(select id from wri$_adv_tasks where name = 'AUTO_STATS_ADVISOR_TASK' and owner_name = 'SYS')
and name = 'EXECUTION_DAYS_TO_EXPIRE';
NAME VALUE
---------------------------------------- ----------
EXECUTION_DAYS_TO_EXPIRE 30
|
기본값으로 30일로 설정되어있음
1일로 변경(원하는 날짜로 변경)
|
1
2
3
|
SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 1);
PL/SQL procedure successfully completed.
|
1일이 지난 데이터는 만료된 것으로 표시됨
자동 제거 작업은 보존기한이 초과한(만료된) 데이터를 제거함
변경 후 AUTO_STATS_ADVISOR_TASK 의 보존기한 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
col name for a40
col value for a10
select name,value
from wri$_adv_parameters
where task_id in
(select id from wri$_adv_tasks where name = 'AUTO_STATS_ADVISOR_TASK' and owner_name = 'SYS')
and name = 'EXECUTION_DAYS_TO_EXPIRE';
NAME VALUE
---------------------------------------- ----------
EXECUTION_DAYS_TO_EXPIRE 1
|
정상적으로 변경됨
수동으로 만료된 데이터 제거
|
1
2
3
|
SQL> exec PRVT_ADVISOR.DELETE_EXPIRED_TASKS;
PL/SQL procedure successfully completed.
|
용량에 따라 몇분정도 소요됨
AUTO_STATS_ADVISOR_TASK 데이터 삭제
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
PL/SQL procedure successfully completed.
|
위 명령 실행 시 WRI$_ADV_OBJECTS 테이블의 데이터가 지워짐
용량에 따라 몇분정도 소요됨
AUTO_STATS_ADVISOR_TASK 를 사용하지 않으려면
아래나오는 EXEC DBMS_STATS.INIT_PACKAGE(); 을 실행하지 않으면됨
TASK 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
col name for a40
col how_created for a10
col owner_name for a10
select name, ctime, how_created, owner_name
from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
NAME CTIME HOW_CREATE OWNER_NAME
---------------------------------------- ------------ ---------- ----------
INDIVIDUAL_STATS_ADVISOR_TASK 11-JAN-21 CMD SYS
|
데이터 삭제 작업 후 확인시 AUTO_STATS_ADVISOR_TASK 가 없음
WRI$_ADV_OBJECTS 테이블 재생성 및 인덱스 rebuild
|
1
2
3
4
5
6
|
SQL>
EXEC DBMS_STATS.INIT_PACKAGE();
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
|
TASK 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
col name for a40
col how_created for a10
col owner_name for a10
select name, ctime, how_created, owner_name
from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
NAME CTIME HOW_CREATE OWNER_NAME
---------------------------------------- ------------ ---------- ----------
AUTO_STATS_ADVISOR_TASK 17-DEC-21 CMD SYS
INDIVIDUAL_STATS_ADVISOR_TASK 11-JAN-21 CMD SYS
|
AUTO_STATS_ADVISOR_TASK 가 다시 생성됨
테이블 재생성 후 AUTO_STATS_ADVISOR_TASK 의 보존기한 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
col name for a40
col value for a10
select name,value
from wri$_adv_parameters
where task_id in
(select id from wri$_adv_tasks where name = 'AUTO_STATS_ADVISOR_TASK' and owner_name = 'SYS')
and name = 'EXECUTION_DAYS_TO_EXPIRE';
NAME VALUE
---------------------------------------- ----------
EXECUTION_DAYS_TO_EXPIRE 30
|
재생성 했기 때문에 30일로 원상복구됨
다시 1일로 변경(원하는 날짜로 변경)
|
1
2
3
|
SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 1);
PL/SQL procedure successfully completed.
|
1일이 지난 데이터는 만료된 것으로 표시됨
자동 제거 작업은 보존기한이 초과한(만료된) 데이터를 제거함
변경 후 AUTO_STATS_ADVISOR_TASK 의 보존기한 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
col name for a40
col value for a10
select name,value
from wri$_adv_parameters
where task_id in
(select id from wri$_adv_tasks where name = 'AUTO_STATS_ADVISOR_TASK' and owner_name = 'SYS')
and name = 'EXECUTION_DAYS_TO_EXPIRE';
NAME VALUE
---------------------------------------- ----------
EXECUTION_DAYS_TO_EXPIRE 1
|
정상적으로 변경됨
작업 후 sysaux tablespace 용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SQL>
col col0 format a18 heading 'TS Name'
col col1 format 9,999,999.99 heading 'Size(MB)'
col col2 format 9,999,999.99 heading 'Used(MB)'
col col3 format 9,999,999.99 heading 'Free(MB)'
col col4 format 9,999,999.99 heading '% Free'
col col5 format 9,999,999.99 heading 'Max(MB)'
select col0, col1, col3, col2, col5, col4
from (
select a.tablespace_name col0,
b.bytes/1024/1024 col1,
a.bytes/1024/1024 col3,
b.max/1024/1024 col5,
(b.bytes - a.bytes)/1024/1024 col2,
(a.bytes/ b.bytes) * 100 col4
from (
select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) a,
(
select tablespace_name,
sum(bytes) bytes,
sum(maxbytes) max
from dba_data_files
where tablespace_name = 'SYSAUX'
group by tablespace_name) b
where b.tablespace_name=a.tablespace_name)
order by col4 asc;
TS Name Size(MB) Free(MB) Used(MB) Max(MB) % Free
------------------ ------------- ------------- ------------- ------------- -------------
SYSAUX 21,200.00 19215.62 1984.38 32,767.98 90.63
|
sysaux 사용량이 줄어듬
방법3. AUTO_STATS_ADVISOR_TASK disable
12.2 이전에는 AUTO_STATS_ADVISOR_TASK 를 비활성화 하는 방법이 없었지만
12.2 부터는 26749785 패치를 적용하면 비활성화가 가능해짐(버전별 OPTIMIZER 패치가 나옴)
패치가 적용되지 않은 상태에서 disable 시도
|
1
2
3
4
5
6
7
8
9
|
SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
BEGIN dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE'); END;
*
ERROR at line 1:
ORA-20001: Invalid input values for pname
ORA-06512: at "SYS.DBMS_STATS", line 52489
ORA-06512: at "SYS.DBMS_STATS", line 52737
ORA-06512: at line 1
|
안됨
패치 적용(현재 db 버전이 19.11 이어서 19.11용 패치파일 다운로드 후 적용)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
$ cd /home/oracle/media
$ unzip p26749785_1911000DBRU_Generic.zip
$ cd 26749785
$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.24
Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /app/oracle/product/19.0.0
Central Inventory : /shcsw/oracle/app/oraInventory
from : /app/oracle/product/19.0.0/oraInst.loc
OPatch version : 12.2.0.1.24
OUI version : 12.2.0.7.0
Log file location : /app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-27_09-52-47AM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 26749785
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '26749785' to OH '/app/oracle/product/19.0.0'
Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...
Patch 26749785 successfully applied.
Log file location: /app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-27_09-52-47AM_1.log
OPatch succeeded.
$ cd $ORACLE_HOME/OPatch
$ datapatch
SQL Patching tool version 19.11.0.0.0 Production on Mon Dec 27 10:35:36 2021
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /app/oracle/cfgtoollogs/sqlpatch/sqlpatch_28146_2021_12_27_10_35_36/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 26749785 (PERF_DIAG NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK):
Binary registry: Installed
SQL registry: Not installed
Current state of release update SQL patches:
Binary registry:
19.11.0.0.0 Release_Update 210413004009: Installed
SQL registry:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 11-JAN-21 06.36.28.702431 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
No interim patches need to be rolled back
Patch 32545013 (Database Release Update : 19.11.0.0.210420 (32545013)):
Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.11.0.0.0 Release_Update 210413004009
The following interim patches will be applied:
26749785 (PERF_DIAG NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK)
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...done
Patch 26749785 apply: SUCCESS
logfile: /app/oracle/cfgtoollogs/sqlpatch/26749785/24119974/26749785_apply_ORCL19_2021Dec27_10_38_02.log (no errors)
Please refer to MOS Note 1609718.1 and/or the invocation log
/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_28146_2021_12_27_10_35_36/sqlpatch_invocation.log
for information on how to resolve the above errors.
SQL Patching tool complete on Mon Dec 27 10:38:27 2021
|
정상적으로 패치됨
패치 적용 후 AUTO_STATS_ADVISOR_TASK 값 확인
|
1
2
3
4
5
|
SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;
DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------
TRUE
|
TRUE임(enable 상태)
패치 적용 후 disable
|
1
2
3
|
SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
PL/SQL procedure successfully completed.
|
disable 후 AUTO_STATS_ADVISOR_TASK 값 확인
|
1
2
3
4
5
|
SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;
DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
-------------------------------------------------------------------------
FALSE
|
FALSE가 됨(disable 상태)
원인 : AUTO_STATS_ADVISOR_TASK로 인한 SYSAUX 용량증가
Oracle 19c 에서 Statistics Advisor Task 실행이 더 많아졌고 그로인해 SYSAUX 테이블스페이스에서 더 많은 공간을 차지 하게됨
보존기한을 줄이거나 수동으로 AUTO_STATS_ADVISOR_TASK 를 제거해주면됨
기존 dba_advisor_objects 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
select task_name, count(*) cnt
from dba_advisor_objects
group by task_name
order by cnt desc;
TASK_NAME CNT
----------------------------------- ----------
AUTO_STATS_ADVISOR_TASK 76318521
SYS_AUTO_SPM_EVOLVE_TASK 21
SYS_AUTO_SQL_TUNING_TASK 39
.
.
|
AUTO_STATS_ADVISOR_TASK의 cnt가 많음
이로 인해 sysaux 용량을 많이 차지하게됨
위 작업 후 dba_advisor_objects 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
select task_name, count(*) cnt
from dba_advisor_objects
group by task_name
order by cnt desc;
TASK_NAME CNT
----------------------------------- ----------
AUTO_STATS_ADVISOR_TASK 3
.
.
|
AUTO_STATS_ADVISOR_TASK의 cnt가 줄어듬
참조 :
2305512.1, 329984.1, 2730556.1, 2692726.1, 2544788.1, 27983174.8, 2686022.1, bug 27983174
https://community.oracle.com/mosc/discussion/3807325/ora-12012-error-on-auto-execute-of-job-sys-ora-at-os-opt-sy-42249
https://community.oracle.com/tech/apps-infra/discussion/4031378/ora-20001-statistics-advisor-invalid-task-name-for-the-current-user
https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/sql-access-advisor.html#GUID-561EC9B4-0930-4915-B5E1-17F2C5ACD261
https://dataonair.or.kr/db-tech-reference/d-guide/dbms-1/?mod=document&uid=101814
http://power.iotn.co.kr/zboard.php?tab_num=1&id=9_1_bbs&page=31&sn1=&divpage=1&sn=off&ss=on&sc=off&select_arrange=hit&desc=asc&no=2551
https://h391106.tistory.com/270
https://www.codetd.com/ko/article/6286857
https://positivemh.tistory.com/392
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_655536183_298 by 128 in tablespace SYSAUX
OS환경 : Oracle Linux6.8(64bit) DB 환경 : Oracle Database 11.2.0.4 에러 : ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_655536183_298 by 128 in tablespace..
positivemh.tistory.com
'ORACLE > Trouble Shooting' 카테고리의 다른 글
| 오라클 19c RAC GRID_HOME relink 시 발생하는 메세지 (0) | 2021.12.30 |
|---|---|
| AHF-00014: AHF Data Location /app/oracle/oracle.ahf/data is not owned by root in directory hierarchy (0) | 2021.12.25 |
| ORA-28040: No matching authentication protocol (0) | 2021.12.09 |
| WARNING: inbound connection timed out (ORA-3136) (0) | 2021.11.24 |
| ORA-03135: connection lost contact (0) | 2021.11.23 |