Home » Posts tagged 'control file'

Tag Archives: control file

Difference Between Oracle Mount, No Mount and Open Stage

Just for refreshing my memory and yours

he differences in the 3 startup stages for oracle db are as below.

NoMount Stage:
When DB is in the nomount stage, oracle first opens and reads the initialization parameter file “init.ora” for the DB configuration which includes various parameters required for initialization. Based on the parameters, the memory areas in database instance are allocated and db background processes are started. The instance then starts.

Mount Stage:
When DB is in this stage, it opens and reads the control file which contains the physical structure of db  like the database datafiles etc.. It determines the location of the datafiles and the database is ready to be opened.

Open Stage
When DB is in this stage, it opens the database, it tries to access all of the datafiles associated with the database. Once it has accessed the database datafiles, it makes sure that all of the database datafiles are consistent.

After that DB is into normal operations state.

Re-create 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 MAXDATAFILES parameter.

Yesterday (sunday) I had a chance to manage my control file since I have time to shutdown my server. I log on my AIX server and logon to Oracle SQL Plus.

ora[sid]>sqlplus /nolog

Connect to my database.

SQL>conn /  as sysdba;

Then dump my current control file to a text file.

SQL>alter database backup controlfile to trace as ‘/oracle/[SID]/controlfile.txt’;

Then I copied that file (controlfile.txt) to my local notebook and edit it using my favourite text editor.

Here are list of my old control file :


Maximum Oracle Datafiles is exceeding

We are currently running our SAP R/3 4.7 system on Oracle 9i database. Oracle 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.









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.