Urgent!!Max_data_files

[portal]

Folks my Prod system is reaching max limit on db_files,I remember we can do that dynamically without recreating the control files, help me with the code.Max_data_file while creating the instance was 200 and db_files in init ora is also 200.Advice me if Im wrong.
Oracle 9i rel 2 on NT.

[hacketta1]

As I understand it, MAXDATAFILES is the "hard" limit in terms of how many datafiles you can have. db_files is the "soft" limit and can be changed in your spfile followed by a restart. You say that both maxdatafiles and db_files are set to 200 and you're running out of files e.g. your soft limit is almost hitting your hard limit. In this case don't you need to change maxdatafiles as well? The way you do this is by recreating the controlfile I'm afraid.

[pando]

you dont need to recreate control file, just set db_file higher

[hacketta1]

Pando

Don't know if I'm being thick here or misinterpreting the post, but if he's running out of files and both dbfiles and maxdatafiles are set to the same value doesn't he have to set maxdatafiles to a higer value (logiically speaking) in order to increase db_files (because db_files can't go higher than maxdatafiles)?

[hacketta1]

On seconds thoughts I'm being thick!

worst scenario:

maxdatafiles = 200
db_files = 199

So we can still 'alter system set db_files = 200 scope = both' and then restart the db. Still need to rebuild the controlfile at some point soon though.

[pando]

you dont need to recreate controlfile, if your maxdatafile is 200 and db_file is 200 and you hit that limit then just modify db_files maxdatafile will increase automatically, just look your alert and you will see

[kris123]

really hun? Im a lil confused,since what version can u dynamically increase max_data_files?

Thanks

[tamilselvan]

===
you dont need to recreate controlfile, if your maxdatafile is 200 and db_file is 200 and you hit that limit then just modify db_files maxdatafile will increase automatically, just look your alert and you will see

===
I don't think so. I tested it 9i. It does not work.

MAXDATAFILE is defined in the DB creation script. If you want to increase, then you need to re-run the control file after changing it manually.

Tamil

[kris123]

Thats what i thot too? But i cant believe my idolized papa said it

[pando]

maxdatafiles is set at 6 in my controlfile

select  * from V$CONTROLFILE_RECORD_SECTION where type = 'DATAFILE';

TYPE              RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
----------------- ----------- ------------- ------------ ----------- ---------- ----------
DATAFILE                  180             6            6           0          0          2


select count(*) from dba_data_files;

  COUNT(*)
----------
         6

show parameter db_files

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
db_files                             integer 8


create tablespace dummy03  datafile '/tmp/dummy03.dbf' size 1m

Tablespace created.

>create tablespace dummy04  datafile '/tmp/dummy04.dbf' size 1m;

Tablespace created.

I have reached 8 datafiles now, so the following shoudl fail

create tablespace dummy05  datafile '/tmp/dummy05.dbf' size 1m;

*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

and it failed!

and if I check my alert I actually see this:


kccrsz: expanded controlfile section 6 from 7 to 8 records
  number of logical blocks in section remains at 1
kccrsz: expanded controlfile section 4 from 6 to 7 records
  number of logical blocks in section remains at 1
Completed: create tablespace dummy03  datafile '/tmp/dummy03.
Mon May  9 19:09:39 2005
create tablespace dummy04  datafile '/tmp/dummy04.dbf' size 1m
Mon May  9 19:09:39 2005
kccrsz: expanded controlfile section 6 from 8 to 9 records
  number of logical blocks in section remains at 1
kccrsz: expanded controlfile section 4 from 7 to 8 records
  number of logical blocks in section remains at 1

kccrsz: expanded controlfile section 6 from 9 to 10 records
  number of logical blocks in section remains at 1
DB_FILES[8] exceeded (fno=9 kcfdpk=8)
ORA-59 signalled during: create tablespace dummy05  datafile '/tmp/dummy05....

now I will increase only db_files parameters from 8 to 10

show parameters db_files

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
db_files                             integer 10

create tablespace dummy05  datafile '/tmp/dummy05.dbf' size 1m;

Tablespace created.

wow I didnt recreate controlfile and I am able to create new datafiles just by increasing db_files!

create tablespace dummy06 datafile '/tmp/dummy06.dbf' size 1m;

Tablespace created.

next should fail because I reached 10

create tablespace dummy07 datafile '/tmp/dummy07.dbf' size 1m;
create tablespace dummy07 datafile '/tmp/dummy07.dbf' size 1m
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded


and this is 8.1.7.4

check alert again




create tablespace dummy05  datafile '/tmp/dummy05.dbf' size 1m
Mon May  9 19:12:21 2005
kccrsz: expanded controlfile section 4 from 8 to 9 records
  number of logical blocks in section remains at 1
Completed: create tablespace dummy05  datafile '/tmp/dummy05.
Mon May  9 19:13:08 2005
create tablespace dummy06 datafile '/tmp/dummy06.dbf' size 1m
Mon May  9 19:13:08 2005
kccrsz: expanded controlfile section 6 from 10 to 11 records
  number of logical blocks in section remains at 1
kccrsz: expanded controlfile section 4 from 9 to 10 records
  number of logical blocks in section remains at 1
Completed: create tablespace dummy06 datafile '/tmp/dummy06.d
Mon May  9 19:13:24 2005
create tablespace dummy07 datafile '/tmp/dummy07.dbf' size 1m
Mon May  9 19:13:24 2005
kccrsz: expanded controlfile section 6 from 11 to 12 records
  number of logical blocks in section remains at 1
DB_FILES[8] exceeded (fno=11 kcfdpk=10)
ORA-59 signalled during: create tablespace dummy07 datafile '/tmp/dummy07.d...


check maxdatafiles again and it´s magically set to 10!

select  * from V$CONTROLFILE_RECORD_SECTION where type = 'DATAFILE';

TYPE              RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
----------------- ----------- ------------- ------------ ----------- ---------- ----------
DATAFILE                  180            10           10           0          0          6

dump the controlfile and it´s 10 too!

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "LNX817" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 10
    MAXINSTANCES 8
    MAXLOGHISTORY 226

!!!