Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle db_file prameter

Posted on 2006-06-22
27
Medium Priority
?
2,208 Views
Last Modified: 2008-01-09
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
Comment
Question by:salasadi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
  • +3
27 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 16960282
There is no real issue with increasing it to 1000.  Change the parameter and restart the DB, and you should be fine.
0
 

Author Comment

by:salasadi
ID: 16960309
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
 
LVL 25

Expert Comment

by:jrb1
ID: 16961339
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 19

Expert Comment

by:actonwang
ID: 16961357
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16961397
>>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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16961549
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
 
LVL 25

Expert Comment

by:jrb1
ID: 16962469
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16962487
>>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
 
LVL 25

Expert Comment

by:jrb1
ID: 16962639
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16962661
>> if we don't have a definitive answer.
      I have definitive answer to it :

      NO. for given db, you can not change MAXDATAFILE.
     
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16962712
Yep, I just found it in the 10g docs.  Obviously you can't believe everything your read on the internet.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16962960
:)
0
 

Author Comment

by:salasadi
ID: 16963057
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16963086
yeah. you can recreate your controlfile to do it if you want to.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16963167
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
 
LVL 7

Accepted Solution

by:
vishal68 earned 200 total points
ID: 16967628
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
 

Author Comment

by:salasadi
ID: 16968702
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16968830
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
 
LVL 7

Expert Comment

by:vishal68
ID: 16990677
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16991754
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
 
LVL 7

Expert Comment

by:vishal68
ID: 16998889
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 17000467
Thank you for the explanation, Vishal.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17000488
>>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
 

Author Comment

by:salasadi
ID: 17000648
what is the limit of file handler in Linux and Windows?
0
 
LVL 7

Expert Comment

by:vishal68
ID: 17007352
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
 
LVL 1

Expert Comment

by:Computer101
ID: 20294456
Forced accept.

Computer101
EE Admin
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question