Home » Posts tagged 'MAXDATAFILES'

Tag Archives: MAXDATAFILES

Check your MAXDATAFILES parameter on Oracle

May 7, 2010 9:15 am / 1 Comment on Check your MAXDATAFILES parameter on Oracle


A few weeks ago, I need to manage my number of datafiles on Oracle server parameter file (spfile). On Oracle spfile, by using BRTOOLS or BRSPACE, you can check your DB_FILES parameter.

ora[sid]>brspace -f dbparam

On my system, currently DBFILES = 768. I need to increase my DB_FILES so it can manage larger number of datafiles but unfortunately I can’t do it right now since my MAXDATAFILES was set to 784. So I can only increase my number of datafiles from 768 to 748.

You can check MAXDATAFILES number on your Oracle control file. Follow this instruction to get your MAXDATAFILES parameter info :

  • Login to your DB server using root
  • Change to ora[sid] user. root#su – ora[sid] [ENTER]
  • Go to sqlplus. ora[sid]>sqlplus /nolog [ENTER]
  • Connect to your database instance. SQL>conn / as sysdba [ENTER]
  • Run command to extract control file to trace file. SQL>alter database backup controlfile to trace [ENTER]
  • Get information about where your trace file is stored. SQL>show parameter user_dump_dest
  • Get your trace file (usually it has .trc extension) and open it using your favourite text editor.
  • Find MAXDATAFILES info inside it.

Here is my trace file sample :

—-more lines—–
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “[SID]” NORESETLOGS FORCE LOGGING ARCHIVELOG
—  SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXDATAFILES 784   ——-> this is the information I mentioned.
MAXINSTANCES 50
MAXLOGHISTORY 22462
LOGFILE
GROUP 1 (
‘/oracle/[SID]/origlogA/log_g11m1.dbf’,
‘/oracle/[SID]/mirrlogA/log_g11m2.dbf’
) SIZE 50M,
—-more lines—–

If I want to increase MAXDATAFILES, then I need to re-create my control file. I’ll update more about re-creating control file on my next posting.

Maximum Oracle Datafiles is exceeding

May 4, 2010 10:22 am / Leave a comment


We are currently running our SAP R/3 4.7 system on Oracle 9i database. Oracle 9.2.0.7 is the exact version. We haven’t change any parameter on our control file.

The main problem is we need to set our number of datafiles on Oracle

Currently we have DB_FILES set to 768 on Oracle parameter file. We need to set this to higher number (may be around 1024). I ask this question to SAP SDN forum and Stefan said I need to check MAXDATAFILES on our control file. So I need to check my control file.

I log on to my database system and try to dump our control file to trace file using this SQL command :

SQL> alter database backup controlfile to trace;
Database altered.

I check my dump trace location.

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE

———————————— ———– ——————————

user_dump_dest                       string      /oracle/[SID]/saptrace/usertrace

Than I try to get my files and read it using my text editor.

This is some lines on my trace file.

# only if the current version of all online logs are available.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “[SID]” NORESETLOGS FORCE LOGGING ARCHIVELOG

—  SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 255

MAXLOGMEMBERS 3

MAXDATAFILES 784

MAXINSTANCES 50

MAXLOGHISTORY 22462

From MAXDATAFILES line, I get information about my maximum datafiles I can set, 784. So I cannot set my DB_FILES parameter to 1024 yet. I need to adjust and re-create my control file.

I need to shutdown, backup and re-create it. I’ll explained on mye next posting.