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.