Solved

Oracle db_file prameter

Posted on 2006-06-22
27
2,129 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
  • 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
 
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 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Expert Comment

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

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 50 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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now