Oracle 18.3 Resize operation completed for file#

 

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)

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

转载于:http://blog.itpub.net/29785807/viewspace-2285654/