ITPub博客

首页 > 数据库 > Oracle > Oracle 18.3 Resize operation completed for file#

Oracle 18.3 Resize operation completed for file#

原创 Oracle 作者:chenoracle 时间:2018-12-18 19:26:36 0 删除 编辑

 

Oracle 18.3 Resize operation completed for file#

 

问题:

Oracle 告警日志有大量如下信息:

……

2018-12-18T18:00:46.291350+08:00

CJCPDB(3):Resize operation completed for file# 9, old size 276480K, new size 286720K

 

环境说明:

OS: CentOS Linux release 7.5

DB: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.log

2018-12-18T18:00:46.291350+08:00

CHENPDB(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.


SOLUTION

It'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",共同学习,共同成长!!!



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2285654/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle 11g OCP、Oracle 11g OCM、OCMU 用户组成员,微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    454
  • 访问量
    912143