Urgent!!Max_data_files
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Urgent!!Max_data_files

  1. #1
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87

    Urgent!!Max_data_files

    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.
    sat

  2. #2
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you dont need to recreate control file, just set db_file higher

  4. #4
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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)?

  5. #5
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  7. #7
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    really hun? Im a lil confused,since what version can u dynamically increase max_data_files?

    Thanks

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    ===
    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

  9. #9
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Thats what i thot too? But i cant believe my idolized papa said it

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Code:
    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
    
    !!!
    Last edited by pando; 05-10-2005 at 03:53 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width