Product

Platform

Intel

Operating system

Windows 2000

Error

Fri Feb 11 01:00:00 2005 Backups are enabled

Fri Feb 11 01:00:01 2005 Error: oci.error, (1144, 'ORA-01144: File size (4196215 blocks) exceeds maximum of 4194303 blocks\012ORA-06512: at "PRINERGY.DBCS_COMPRESSDATABASE", line 298\012ORA-06512: at "PRINERGY.DBCS_COMPRESSDATABASE", line 319\012ORA-06512: at "PRINERGY.DBCS_SHRINKALLTABLESPACES_U", line 4\012ORA-06512: at line 2\012'), <traceback object at 1597900> traceback: Exception in process 5560, thread 5788

Traceback (innermost last):

File "D:\Prinergy2.0.4\lib\Python\com\creo\araxi\database\BackupDatabase.py", line 500, in backupOracle

shrinkconn.procedures.DBCS_ShrinkAllTableSpaces_U()

File "D:\Prinergy2.0.4\lib\Python\DCOracle\ociProc.py", line 133, in __call__

if oci_.oexec(oc): self._error()

File "D:\Prinergy2.0.4\lib\Python\DCOracle\ociProc.py", line 106, in _error

raise error, (rc, oci_.OracleErrorMessage(c.lda, rc))

oci.error: (1144, 'ORA-01144: File size (4196215 blocks) exceeds maximum of 4194303 blocks\012ORA-06512: at "PRINERGY.DBCS_COMPRESSDATABASE", line 298\012ORA-06512: at "PRINERGY.DBCS_COMPRESSDATABASE", line 319\012ORA-06512: at "PRINERGY.DBCS_SHRINKALLTABLESPACES_U", line 4\012ORA-06512: at line 2\012')

Symptoms

The database backups fail with the error shown. One or more of the database files in the Orant\Oracle\Data folder are approximately 16 GB in size.

Cause

Solution

There are two issues that must be addressed.

1) The datafile must not be allowed to extend close to the 16GB limit.

2) The ShrinkTablespace bug.

To address problem 1 - The Oracle bug will occur if the database extends to within 4 KB of the 16 GB limit. Set the maximum datafile size less than this. The maximum size that you set the datafile will depend on the current size of the datafile.

To find out the current size:

- D:\> Sqlplus /nolog

- SQL> connect prinergy/araxi@araxidbserver as sysdba;

- SQL> set numwidth 20

- SQL> select maxbytes, maxblocks, bytes, blocks, file_name from dba_data_files;

If the maxblocks is set to 4194302, then the datafile could potentially extend to 16 GB. Set the maximum size of the datafile to a smaller value. The column Blocks shows the current size of the datafile in 4 KB blocks, and the Bytes column shows the current size in bytes. Reduce the maximum size of the datafile to a size above the current size but below 17179865088 bytes or 4194301 blocks. If the datafile maxbytes is below 17175674882 bytes and maxblocks is below 4193280 blocks, then nothing needs to be done.

To reduce the maximum size of the datafile, get the maximum and current size of the database in KBytes:

- SQL> select maxbytes/1024, bytes/1024, file_name from dba_data_files;

If maxbytes/1024 is between 16777212 and 16777216, find a new value for the maximum size of the datafile in KBytes which is above the current size (bytes/1024) and preferably below 16777200. If the current size of the database is above 16777200 KB, then set the maxsize to the current size + 1KB.

NOTE: Setting the maximum size to 16773119 or lower would have avoided this bug in the ShrinkTablespaces stored procedure.

Set this new value in the database:

- SQL alter database datafile 'J:\ORANT\....\<datafile name>' autoextend on maxsize nnnnKB;

Substitute the <datafiile name> with the actual datafile name, and replace nnnn with the maximum size (in kilobytes) of the datafile determined above.

Ensure that the tablespaces have sufficient disk space for growth

This query will show the percentage of space used for each tablespace. If any are reaching 90% or more you should consider adding in another datafile for the tablespace.

- SQL> select tablespace_name, trunc( 100 * sum(bytes)/sum(maxbytes), 2) as "% Usage" from dba_data_files group by tablespace_name;

To address problem 2 - To work around the problem, compress the database. If there is sufficient free space in the datafile, the datafile will shrink small enough to set the maximum size small enough to not encounter the bug.

Permanent fix:

See also

Keywords

backup, oracle

Technical contact(s)

Stan Yoshida

Submitted by

Stan Yoshida

Date submitted

11 Feb 2005