• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2247
  • Last Modified:

Oracle db_file prameter

today a user come to me saying that he could not create datafile.  I check the following:

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
       320

SQL> show parameter db_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
db_file_multiblock_read_count        integer     8
db_file_name_convert                 string
db_files                             integer     320
SQL>

so I know we need to increase the db_file.  my question is what is the max number of file I can have and whhat is the downsize of making this parameter =1000?  

Thanks
0
salasadi
Asked:
salasadi
  • 8
  • 5
  • 4
  • +3
1 Solution
 
jrb1Commented:
There is no real issue with increasing it to 1000.  Change the parameter and restart the DB, and you should be fine.
0
 
salasadiAuthor Commented:
jrb1,
thanks for your comment, once i heard that the check point will take longer becuase oracle goes through all datafiles or search for datafile accourding to the db_file. I hope that was false statement.
0
 
jrb1Commented:
I haven't seen anything to indicate the checkpoint process takes longer, but Oracle does automatically starts the CKPT process if you exceed 50 datafiles.  However, it doesn't have to search for datafiles according to the db_file.

The two things I have read is, each process SGA will be larger to allow for the extra data files, but I haven't seen any number to indicate just how much memory.  I can't imagine it is uses that much memory, though.  And a generic "performance is better with a small number of datafiles rather than a large number of small datafiles".  If you have a bunch of small datafiles, that would make sense.  If you have a large number of large datafiles, you just have a big database.

Consider the following documentation when choosing a value for db_files:

http://h50.isi.u-psud.fr/docmiage/oracle/doc/server.817/a76956/dfiles.htm

When determining a value for DB_FILES, take the following into consideration:
- If the value of DB_FILES is too low, you will be unable to add datafiles beyond the DB_FILES limit without first shutting down the database.
- If the value of DB_FILES is too high, memory is unnecessarily consumed.

Theoretically, an Oracle database can have an unlimited number of datafiles. Nevertheless, you should consider the following when determining the number of datafiles:

- Performance is better with a small number of datafiles rather than a large number of small datafiles. A large number of files also increases the granularity of a recoverable unit.
- Operating systems often impose a limit on the number of files a process can open simultaneously. Oracle's DBWn processes can open all online datafiles. Oracle is also capable of treating open file descriptors as a cache, automatically closing files when the number of open file descriptors reaches the operating system-defined limit.

Oracle allows more datafiles in the database than the operating system-defined limit; this can have a negative performance impact. When possible, adjust the operating system limit on open file descriptors so that it is larger than the number of online datafiles in the database.

The operating system specific limit on the maximum number of datafiles allowed in a tablespace is typically 1023 files.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
actonwangCommented:
see this:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch145.htm#1016261

Also remember:

YOU CAN NOT specify a value greater than MAXDATAFILES value when you create the database.

by default , you set DB_FILES value equal to MAXDATAFILES.


acton
0
 
actonwangCommented:
>>i heard that the check point will take longer becuase oracle goes through all datafiles or search for datafile accourding to the db_file

    Not likely. You want to watch  v$sysstat or v$system_event  to see if some waits really happen for that.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Please tell us:
1. Which version of Oracle you have
2. Which O/S you have

"what is the max number of file I can have?"  That is different with different versions of Oracle.

"what is the downside of making this parameter =1000?"  Some space is require in the controlfile for each datafile, so this will make the controlfile larger, but that is usually not a big problem.

In most versions of Oracle though you cannot simply increase this parameter, if the MAXDATAFILES parameter is the same value.  If that is higher, you may be able to change this easily, but if not, you may have to create a new controlfile, and that has some risks, plus it requires a database shutdown.

Another option might be to make one or more of the current data files larger, but this will only be an option if they are currently smaller than the maximum file size allowed by your O/S and Oracle version.

0
 
jrb1Commented:
actonwang wrote:
>YOU CAN NOT specify a value greater than MAXDATAFILES value when you create the database.

markgeer wrote:
>If that is higher, you may be able to change this easily, but if not, you may have to create a new
>controlfile, and that has some risks, plus it requires a database shutdown.

Is that true, or is this poster correct?

http://www.dbasupport.com/forums/showthread.php?t=47368&goto=nextnewest

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
0
 
actonwangCommented:
>>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

to jrb1: Really?? did you try?
0
 
jrb1Commented:
No, I don't have a DB I can do this to at the moment.  Maybe this weekend, if we don't have a definitive answer.
0
 
actonwangCommented:
>> if we don't have a definitive answer.
      I have definitive answer to it :

      NO. for given db, you can not change MAXDATAFILE.
     
0
 
jrb1Commented:
Yep, I just found it in the 10g docs.  Obviously you can't believe everything your read on the internet.
0
 
actonwangCommented:
:)
0
 
salasadiAuthor Commented:
I thought the MAXDBFILES  can be change by recreate your controlfile in version 8i and higher.  

alter database backup controlfile to trace;

shutdown immediate;


change the MAXDBFILE to reasanble number, then startup nmount and point to tracefile that you modified and that should change the number.
0
 
actonwangCommented:
yeah. you can recreate your controlfile to do it if you want to.
0
 
Mark GeerlingsDatabase AdministratorCommented:
You can change MAXDATAFILES, if you need to, but this requires creating a new controlfile, and this requires a database shutdown and has some risks.  Make sure that you have a good backup if you try this!
0
 
vishal68Commented:
Starting from Oracle 8i onwards, you do not have to recreate the controlfile to increase the Maxdatafiles. As long as you have a higher value for db_files, Oracle will automatically expand the controlfile whenever you add a new datafile. Following is from Oracle 8i Documentation

MAXDATAFILES Clause
Specify the initial sizing of the datafiles section of the control file at CREATE
DATABASE or CREATE CONTROLFILE time. An attempt to add a file whose number
is greater than MAXDATAFILES, but less than or equal to DB_FILES, causes the
Oracle control file to expand automatically so that the datafiles section can
accommodate more files.
The number of datafiles accessible to your instance is also limited by the
initialization parameter DB_FILES.

For actonwang : I have tested the same. Oracle will simple write a message in the alert log, saying it has expanded the controlfile.

As for the original questions, generally it does not matter to have higher number of datafiles. One thing that you need to keep in mind is that all the Oracle processes (one per session if you are using dedicated server) will open all the datafiles at the OS level. This can sometime create problems of running out of File Handlers at the OS level, due to large number of data files and sessions.

HTH
Vishal
0
 
salasadiAuthor Commented:
Vishal,
thanks for the valuable information.  it seems that it is a matter of time when I reach the OS limitation.   I have 50 schema, each schema have 5 datafiles, and some have 12 datafiles.  so any user login to the system, Oracle open the entire datafiles for all schema or just for the schema that the user logged in?

where can I find the limit of open file in Windows vs. Linux?

Thanks
0
 
actonwangCommented:
Vishal,

        Thanks for the info. I didn't have time to test and looked my oracle manual and it says that you can not do it.
        I will do a test and let you know the result.

acton
0
 
vishal68Commented:
Hi

Could not post earlier, I had taken a couple of days off. There is nothing in oracle linking a file with a schema. Files are linked with a tablespace. Each oracle session process (no matter which user/schema it is using to login) will open all the data files belonging to all the tablespaces in the database at the OS level. This means that if you have 1000 files and 100 users, U will need 100000 file handlers at the OS level.

As for the number of open files. On Unix this is set as Kernel Parameter, on Windows it is automatically controlled depending on the availability of memory.

HTH
Vishal
0
 
Mark GeerlingsDatabase AdministratorCommented:
To vishal68:

I have never heard this before about Oracle: "Each oracle session process ... will open all the data files belonging to all the tablespaces in the database at the OS level."  Are you sure?  Have you seen that documented somewhere?  I understood that the Oracle background processes open each data file, but that user sessions do not.
0
 
vishal68Commented:
They do. I have faced this problem earlier in one of the databases, when we used to create datafiles of less than 2GB, due to OS file size limits. In one of the databases which was about 500GB, we ended up with about 2500 files. There were about 1000 users logging on the the system. And we suddenly started getting strange I/O errors. Working with Oracle Support we found that we are running out of file handlers at the OS level. At that time Oracle Support told us that each Oracle server process will open all the data files for read purpose. Makes sense as all the read operations are performed by the Oracle Server process, while all the write operations are performed by the Oracle background processes.

HTH
Vishal
0
 
Mark GeerlingsDatabase AdministratorCommented:
Thank you for the explanation, Vishal.
0
 
actonwangCommented:
>>each Oracle server process will open all the data files for read purpose
    so if there are 2500 files, server process (session process) will obtain 2500 file handles throughout the session?
   
0
 
salasadiAuthor Commented:
what is the limit of file handler in Linux and Windows?
0
 
vishal68Commented:
Hi

On Linux it would be a kernel parameter, not sure which one (have not worked much on Linux, mostly on UNIX flavors and windows). On Windows, as I had posted earlier, it is automatically adjusted as per the availability of resources, mainly memory.

HTH
Vishal
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 8
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now