Oracle 18.3 Resize operation completed for file#
问题：Oracle
告警日志有大量如下信息：……2018-12-18T18:00:46.291350+08:00CJCPDB(3):Resize operation completed for file# 9, old size 276480K, new size 286720K
环境说明：OS: CentOS Linux release 7.5DB:Oracle 18.3.0.0.0
问题分析：
数据文件自动扩展，当数据文件不足，自动执行扩展时，告警日志会打印这些信息；
问题重现：
新建大表SQL> create table test01 as select level as id from dual connect by level <=1000000;
告警日志出现如下信息：[root@oracle-server001 trace]# tail -f alert_cjcdb.log2018-12-18T18:00:46.291350+08:00CHENPDB(3):Resize operation completed for file# 9, old size 276480K, new size 286720K
select
bytes
/
1024
/
1024
,
a.
*
from
dba_data_files a
where
file_id
=
9
;
解决方案：
Resize Operation Completed For File# 201; FILE# Does Not Exist (
文档
ID 2246369.1)
启用
_disable_file_resize_logging
；
---
查询
select a.ksppinm name , b.ksppstvl value , a.ksppdesc description
from x$ksppi a , x$ksppcv b
where a.inst_id = USERENV ( 'Instance' )
and b.inst_id = USERENV ( 'Instance' )
and a.indx = b.indx
and
a.ksppinm
=
'_disable_file_resize_logging'
order
by
1
;
---2
启用SQL> alter system set "_disable_file_resize_logging"=TRUE ;CDB
下sys
用户执行，如果PDB
下sys
用户执行，会报如下错误：
---support.oracle.com上
详细说明如下：
Resize Operation Completed For File# 201; FILE# Does Not Exist (
文档 ID 2246369.1)APPLIES TO:Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.2.0.1 [Release 12.1 to 12.2]Information in this document applies to any platform.alert.log has many entries with:Resize operation completed for file# 201, old size 111616K, new size 112640K
Problematic file# 201 is not existing.
SOLUTIONIt's tempfiles and its number is generated dynamically based on parameter db_files - default value 200.We have to subtract db_files value to get the file number in v$tempfile or db_files parameter value + tempfile# so in this case 201-200=1 is the tempfile#.
There is one Enhancement request raised for this issue to include filename as well in meaasage.Bug 25661172 : DIAG ENH - INCLUDE FILENAME IN ALERT.LOG FOR DATAFILE/TEMPFILE RESIZE OPS
However these messages are for information only and you can disable this using below parameter:Apply patch 18603375 first, then set the below parameter along with the fix.
SQL> alter system set "_disable_file_resize_logging"=TRUE ; (Its default value is FALSE)
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
