oracle 19c alert 日志 提示Resize Operation Completed For File# 和 支持在线move 数据文件

File Extension Messages are seen in alert log.There was no explicit file resize DDL as well.

Resize operation completed for file# 45, old size 26M, new size 28M
Resize operation completed for file# 45, old size 28M, new size 30M
Resize operation completed for file# 45, old size 30M, new size 32M
Resize operation completed for file# 36, old size 24M, new size 26M

In a busy system with lots of DML (insert/update/delete),the alert log could be flooded with many such file extension messages.

CHANGES

 

CAUSE

These file extension messages were result of diagnostic enhancement through unpublished to record automatic datafile resize operations in the alert log with a message of the form:

 "File NN has auto extended from x bytes to y bytes"

This can be useful when diagnosing problems which may be impacted by a file resize. 

 

SOLUTION

In busy systems, the alert log could be completely flooded with file extension messages. A new Hidden parameter parameter "_disable_file_resize_logging" has been introduced through bug 18603375 to stop these messages getting logged into alert log.

(Unpublished) Bug 18603375 - EXCESSIVE FILE EXTENSION MESSAGE IN ALERT LOG 

Set the below parameter along with the fix.

SQL> alter system set "_disable_file_resize_logging"=TRUE ; (Its default value is FALSE)

The bug fix 18603375 is included in 12.1.0.2 onwards.

 

 

 

########

如果数据库文件最末尾有空格，从12C 开始，支持在线move 数据文件

fix: 使用move 方式 移动数据文件
SYMPTOMS
On : 12.1.0.2 version, RDBMS

When attempting to move data file and reported below error

ERROR
-----------------------

SQL> ALTER DATABASE MOVE DATAFILE '/path/oradata/<DB_NAME>/DATA04.dbf' TO '/path/oradata/<DB_NAME>/DATA04.dbf';
ALTER DATABASE MOVE DATAFILE '/path/oradata/<DB_NAME>/DATA04.dbf' TO '/path/oradata/<DB_NAME>/DATA04.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/path/oradata/<DB_NAME>/DATA04.dbf"

 

CAUSE
There is space appended with datafile name, datafile created with blank space.

SOLUTION
Move the datafile by using datafile ID:
alter session set container=db;
SQL> SELECT FILE#,NAME FROM V$DATAFILE; --To query the datafile ID.
SQL> ALTER DATABASE MOVE DATAFILE 20 TO '/data/Cdb/db/db_data08.dbf';
（1-3分钟）