ORA-01118: cannot add any more database files: limit of 125 exceeded

Cause : MAXDATAFILES parameter value of control file exceeded.

Solution: Create control file to trace and change the value of MAXDATAFILES parameter into new control file and start database using new control file.

ORA-00059: maximum number of DB_FILES exceeded

SQL> ALTER TABLESPACE DATA ADD DATAFILE '/oradata/erpdcdb/DATA12.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

ALTER TABLESPACE DATA ADD DATAFILE '/oradata/erpdcdb/DATA12.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
*
ERROR at line 1:
ORA-01118: cannot add any more database files: limit of 125 exceeded


SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/oradata/erpdcdb/CONTROL_FILE.SQL';

Database altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 6.8564E+10 bytes
Fixed Size                  2382016 bytes
Variable Size            2.4478E+10 bytes
Database Buffers         4.4074E+10 bytes
Redo Buffers               10338304 bytes
SQL>

Now open the file and generate the scripts for control file and execute like below

SQL> CREATE CONTROLFILE REUSE DATABASE "erpdcdb" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
  2    3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 500
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 1168
  7  LOGFILE
  8    GROUP 1 (
  9      '/fra/erpdcdb/redo01a.log',
 10      '/oradata/erpdcdb/redo01b.log'
 11    ) SIZE 1024M BLOCKSIZE 512,
 12    GROUP 2 (
 13      '/fra/erpdcdb/redo02a.log',
 14      '/oradata/erpdcdb/redo02b.log'
 15    ) SIZE 1024M BLOCKSIZE 512,
 16    GROUP 3 (
 17      '/fra/erpdcdb/redo03a.log',
 18      '/oradata/erpdcdb/redo03b.log'
 19    ) SIZE 1024M BLOCKSIZE 512,
 20    GROUP 4 (
 21      '/oradata/erpdcdb/redo04b.log',
        '/fra/erpdcdb/redo04a.log'
 22   23    ) SIZE 1024M BLOCKSIZE 512,
 24    GROUP 5 (
 25      '/fra/erpdcdb/redo05a.log',
 26      '/oradata/erpdcdb/redo05b.log'
 27    ) SIZE 1024M BLOCKSIZE 512,
 28    GROUP 6 (
 29      '/fra/erpdcdb/redo06a.log',
 30      '/oradata/erpdcdb/redo06b.log'
 31    ) SIZE 1024M BLOCKSIZE 512
 32  DATAFILE
 33    '/oradata/erpdcdb/system01.dbf',
 34    '/oradata/erpdcdb/sysaux01.dbf',
 35    '/oradata/erpdcdb/undotbs01.dbf',
 36    '/oradata/erpdcdb/users01.dbf'
37  CHARACTER SET WE8MSWIN1252;

Control file created.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/erpdcdb/temp01.dbf'
     SIZE 32767M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M; 

Tablespace altered.

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 6.8564E+10 bytes
Fixed Size                  2382016 bytes
Variable Size            2.4478E+10 bytes
Database Buffers         4.4074E+10 bytes
Redo Buffers               10338304 bytes
Database mounted.
Database opened.
SQL> ALTER TABLESPACE DATA ADD DATAFILE '/oradata/erpdcdb/DATA12.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Tablespace altered.

SQL>