Tempfile file# in alert.log

While investigating “Resize operation completed” messages from alert.log, I came across a file number (#1023) which was much higher and kind of not expected.

Resize operation completed for file# 1023, old size 4198400K, new size 4249600K
Resize operation completed for file# 1023, old size 4249600K, new size 4300800K

Made following checks on v$datafile & v$tempfile, but they also had file number ranging from 1-6

SQL> select file# from v$datafile;
FILE#
———-
1
2
3
4
5
6
6 rows selected.
SQL> select file# from v$tempfile;
FILE#
———-
1

I explicitly used following commends to resize the file size, so that “Resize operation completed” can be checked again (i.e. it will log the message in alert.log).

SQL> alter database datafile ‘datafile name’ resize <newsize>;
SQL> alter database tempfile ‘tempfile name’ resize <newsize>;

Message against file# 1023 was logged, when TEMPFILE was resized. After checking few things found that it’s starting TEMPFILE numbering with value DB_FILES parameter value +1, +2 and so on.

SQL> show parameter db_files
NAME TYPE                               VALUE
——————– ——————————
db_files integer                        1022

Currently DB_FILES is set to 1022, so it started TEMPFILE numbering as DB_FILES + 1 = 1023

To test this further, added another tempfile

SQL> alter tablespace temp add tempfile ‘+DATADG’ size 10m ;
Tablespace altered.
SQL> select file# from v$tempfile;
FILE#
———-
1
2
SQL> alter database tempfile ‘+DATADG/ORCL/TEMPFILE/temp.627.925659727’ resize 20m;
Database altered.

New message in alert.log “Resize operation completed for file# 1024, old size 10240K, new size 20480K”, DB_FILES + 2 = 1024.

Advertisements
This entry was posted in Oracle Administration and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s