No More ORA-1118 – cannot add any more data files: limit of XX exceeded

ORA-1118 occurs when the database has hit the MAXDATAFILES limit and to resolve it, you need to

1. alter database backup controlfile to trace;
2. Shu immediate;
3. Increase MAXDATAFILES in backed-up controlfile.
4. Move/Rename the existing controlfiles
5. Startup nomount
6. Execute the create controfile command
7. alter database open;

The steps 5 and 6 can be combined,as the backed-up controlfile has the startup nomount command.

But,the good news is, NO MORE :).No need for downtime anymore 🙂

13:20:47 SYS@MATRIX> select * from V$CONTROLFILE_RECORD_SECTION where type='DATAFILE';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATAFILE                             520           100            9           0          0          0

Total number of records allocated for the DATAFILE is 100 and used is 9, which means the database is currently having 9 datafiles.

13:21:07 SYS@MATRIX> select count(1) from v$datafile;

  COUNT(1)
----------
         9

Create controlfile backup

13:24:50 SYS@MATRIX> alter database backup controlfile to trace;

Database altered.

The backed-up controlfile trace shows –

CREATE CONTROLFILE REUSE DATABASE "MATRIX" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292

Changed the value of MAXDATAFILES to 9 and created new database controlfile.
Had shutdown the database

13:29:52 SYS@MATRIX> startup nomount
ORACLE instance started.

Total System Global Area  544030720 bytes
Fixed Size                  1375848 bytes
Variable Size             360710552 bytes
Database Buffers          176160768 bytes
Redo Buffers                5783552 bytes
13:29:58 SYS@MATRIX> CREATE CONTROLFILE REUSE DATABASE "MATRIX" NORESETLOGS FORCE LOGGING ARCHIVELOG
13:31:01   2      MAXLOGFILES 16
13:31:01   3      MAXLOGMEMBERS 3
13:31:01   4      MAXDATAFILES 9
13:31:01   5      MAXINSTANCES 8
13:31:01   6      MAXLOGHISTORY 292
13:31:01   7  LOGFILE
13:31:01   8    GROUP 1 'D:\ORACLE\APP\ORADATA\MATRIX\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
13:31:01   9    GROUP 2 'D:\ORACLE\APP\ORADATA\MATRIX\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
13:31:01  10    GROUP 3 'D:\ORACLE\APP\ORADATA\MATRIX\REDO03.LOG'  SIZE 50M BLOCKSIZE 512,
13:31:01  11    GROUP 4 'D:\ORACLE\APP\ORADATA\MATRIX\REDO04.LOG'  SIZE 50M BLOCKSIZE 512
13:31:01  12  -- STANDBY LOGFILE
13:31:01  13  DATAFILE
13:31:01  14    'D:\ORACLE\APP\ORADATA\MATRIX\SYSTEM01.DBF',
13:31:02  15    'D:\ORACLE\APP\ORADATA\MATRIX\SYSAUX01.DBF',
13:31:02  16    'D:\ORACLE\APP\ORADATA\MATRIX\UNDOTBS01.DBF',
13:31:02  17    'D:\ORACLE\APP\ORADATA\MATRIX\USERS01.DBF',
13:31:02  18    'D:\ORACLE\APP\ORADATA\MATRIX\EXAMPLE01.DBF',
13:31:02  19    'D:\ORACLE\APP\ORADATA\MATRIX\TEST_01.DBF',
13:31:02  20    'D:\ORACLE\APP\ORADATA\MATRIX\TEST_02.DBF',
13:31:02  21    'D:\ORACLE\APP\ORADATA\MATRIX\TEST_03.DBF',
13:31:02  22    'D:\ORACLE\APP\ORADATA\MATRIX\TEST_04.DBF'
13:31:02  23  CHARACTER SET WE8MSWIN1252
13:31:02  24  ;

Control file created.

13:31:30 SYS@MATRIX> alter database open;

Database altered.

Check from V$CONTROLFILE_RECORD_SECTION the value for DATAFILE section.

13:32:44 SYS@MATRIX> select * from V$CONTROLFILE_RECORD_SECTION where type='DATAFILE';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATAFILE                             520             9            9           0          0          0

Lets try adding a datafile

13:34:38 SYS@MATRIX> alter tablespace TEST add datafile 'D:\ORACLE\APP\ORADATA\MATRIX\TEST_05.DBF' size 5M;

Tablespace altered.

Datafile got added , so no more ORA-01118 🙂

The alert log shows

Sun Feb 20 13:35:02 2011
alter tablespace TEST add datafile 'D:\ORACLE\APP\ORADATA\MATRIX\TEST_05.DBF' size 5M
Expanded controlfile section 4 from 9 to 41 records
Requested to grow by 32 records; added 1 blocks of records
Completed: alter tablespace TEST add datafile 'D:\ORACLE\APP\ORADATA\MATRIX\TEST_05.DBF' size 5M
13:35:13 SYS@MATRIX> select * from V$CONTROLFILE_RECORD_SECTION where type='DATAFILE';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATAFILE                             520            41           10           0          0          1

There are 2 different types of sections in the control file:-

1. Circularly reusable – archive log records and various backup records
2. Non-circularly reusable – records for datafile, tablespace, and redo thread records

The new feature in Oracle 10.2 is that for the non-reusable records, control file size extends if we pass the previous hard limit.So,the values for MAXLOGFILE, MAXLOGMEMBERS,MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES no longer set a hard limit for the number of records in the control file.

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