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 NOMOUNTCREATE CONTROLFILE REUSE DATABASE “[SID]” NORESETLOGS FORCE LOGGING ARCHIVELOG— SET STANDBY TO MAXIMIZE PERFORMANCEMAXLOGFILES 255MAXLOGMEMBERS 3MAXDATAFILES 784 ——-> this is the information I mentioned.MAXINSTANCES 50MAXLOGHISTORY 22462LOGFILEGROUP 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.