Home » Database Administration » Check your MAXDATAFILES parameter on Oracle

Check your MAXDATAFILES parameter on Oracle

RSS Link

  • An error has occurred; the feed is probably down. Try again later.

Blog Stats

  • 690,237 hits


Categories

Advertisements

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.

Advertisements

1 Comment

  1. […] my Oracle control file From my previous posting I had mention that I need to recreate my Oracle database control file since I need to increase my […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

My twitter post @devratt

May 2010
M T W T F S S
« Apr   Jun »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

Popularity


PageRank

Bookmark and Share

Top Clicks

  • None
%d bloggers like this: