Oracle

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

Posted Anand

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 [email protected]> 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 [email protected]> select count(1) from v$datafile;
  COUNT(1)
----------
         9

Create controlfile backup

13:24:50 [email protected]> 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 [email protected]> 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 [email protected]> 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 [email protected]> alter database open;
Database altered.

Check from V$CONTROLFILE_RECORD_SECTION the value for DATAFILE section.

13:32:44 [email protected]> 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 [email protected]> 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 [email protected]> 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