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)

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！