MAXDATAFILES, DB_FILES (ORA-01118, ORA-00059)

ORA-01118: cannot add any more database files: limit of XXX exceededORA-00059: maximum number of DB_FILES exceeded



MAXDATAFILES

select type, records_total, records_used
from v$controlfile_record_section
where type='DATAFILE';

TYPE      RECORDS_TOTAL   RECORDS_USED 
--------------------------------------
DATAFILE            100              4



DB_FILES

show parameter db_files

NAME     TYPE    VALUE
-------- ------- -----
db_files integer 200


ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'c:\temp\crctl.sql' REUSE NORESETLOGS;


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_1_FRHJCVRL_.LOG',
    'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_1_FRHJCYST_.LOG'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_2_FRHJD1RB_.LOG',
    'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_2_FRHJD4TN_.LOG'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_3_FRHJD8BP_.LOG',
    'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_3_FRHJDCO7_.LOG'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSTEM_FRHJDJVM_.DBF',
  'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSAUX_FRHJDXSS_.DBF',
  'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_UNDOTBS1_FRHJF6QN_.DBF',
  'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_USERS_FRHJFRGR_.DBF'
CHARACTER SET AL32UTF8
;

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_TEMP_FRHJFBC0_.TMP'
     SIZE 65011712  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;




select
tablespace_name,
file_name,
bytes/1024/1024 as Size_Mb
from dba_data_files
order by tablespace_name, file_name;


TABLESPACE_NAME   FILE_NAME                                                                  SIZE_MB 
------------------------------------------------------------------------------------------------------
SYSAUX            C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSAUX_FRHJDXSS_.DBF        1540
SYSTEM            C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSTEM_FRHJDJVM_.DBF         930
UNDOTBS1          C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_UNDOTBS1_FRHJF6QN_.DBF       985
USERS             C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_USERS_FRHJFRGR_.DBF          210


select
tablespace_name,
count(file_name)
from dba_data_files
group by tablespace_name
order by tablespace_name;


TABLESPACE_NAME   COUNT(FILE_NAME) 
------------------------------------
SYSAUX                             1
SYSTEM                             1
UNDOTBS1                           1
USERS                              1

Сейчас всего 4 файла данных в базе.



 Добавим ещё 103 файла:

create tablespace TEST
 datafile  size 128M autoextend on next 128M maxsize unlimited,
           size 128M autoextend on next 128M maxsize unlimited,
           size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

Tablespace TEST created.



BEGIN
    FOR i IN 1 .. 100 LOOP
      execute immediate 'alter tablespace TEST add datafile size 128M autoextend on next 128M maxsize unlimited';
    END LOOP;
END;

PL/SQL procedure successfully completed.


select
tablespace_name,
count(file_name)
from dba_data_files
group by tablespace_name
order by tablespace_name;


TABLESPACE_NAME   COUNT(FILE_NAME) 
------------------------------------
SYSAUX                             1
SYSTEM                             1
TEST                             103
UNDOTBS1                           1
USERS                              1

 
Смотрим что произошло с параметром MAXDATAFILES:

select type, records_total, records_used
from v$controlfile_record_section
where type='DATAFILE';

TYPE       RECORDS_TOTAL   RECORDS_USED 
-----------------------------------------
DATAFILE            200            107


Как видим значение параметра MAXDATAFILES увеличилось до 200,
т.е. до значения  db_files:

show parameter db_files

NAME     TYPE    VALUE
-------- ------- -----
db_files integer 200


ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'c:\temp\crctl.sql' REUSE NORESETLOGS;

Database altered.


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 200
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_1_FRHJCVRL_.LOG',
    'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_1_FRHJCYST_.LOG'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_2_FRHJD1RB_.LOG',
    'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_2_FRHJD4TN_.LOG'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_3_FRHJD8BP_.LOG',
    'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_3_FRHJDCO7_.LOG'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSTEM_FRHJDJVM_.DBF',
  'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSAUX_FRHJDXSS_.DBF',
  'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_UNDOTBS1_FRHJF6QN_.DBF',
  'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_USERS_FRHJFRGR_.DBF',
  'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_TEST_FVDGKGM7_.DBF',

.......................................................................

  'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_TEST_FVDHYR81_.DBF'
CHARACTER SET AL32UTF8
;

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_TEMP_FRHJFBC0_.TMP'
     SIZE 65011712  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;




Начиная с версии 8i, не нужно пересоздавать controlfile при возникновении ошибки ORA-01118, а просто нужно увеличить параметр DB_FILES как и при ORA-00059.
Значение параметра MAXDATAFILES будет автоматически выровнено со значением
DB_FILES.


alter system set db_files=2048  scope=spfile sid='*';
alter system set processes=1024 scope=spfile sid='*';
alter system set control_file_record_keep_time=30 scope=spfile sid='*';
alter system set cursor_sharing='EXACT' scope=spfile sid='*';
alter system set filesystemio_options='SETALL' scope=spfile sid='*';