Archive for August, 2010

Oracle concepts – pfile vs spfile ?

Today we are going to discuss about pfile and spfile…

Whenever we start Oracle instance , it internally refers to a parameter file which is either a pfile or a spfile.

With regard to Older Oracle version, this parameter file is known as pfile and the DBA use to more often call it as INIT.ORA file , but starting from Oracle 9i and up , Oracle corp. introduced another parameter file which is called spfile.

Oracle recommends that you should use spfile instead of pfile , since there are certain advantages of doing so. We will cover what are the advantages of spfile over pfile in just a moment.

The pfile can be modified via a TEXT Editor such as vi editor / gedit on Linux/Unix based systems and via notepad on Windows, whereas spfile is a binary file which can be modified only by Oracle using ALTER SYSTEM set command.

CAUTION : Don’t modify the spfile via a text editor, there are high chances of file corruption since it’s a binary file and if file corruption happens then your db wont start untill and unless you re-create spfile.

Click here to download a sample pfile.

Path for your pfile or spfile on Oracle Server

FOR UNIX / LINUX based systems

FOR WINDOWS based systems

ORACLE_HOME is a env. variable which is holding the path for Oracle software dir.

So what are the ADVANTAGES of spfile over pfile ?

1. spfile can be backed up by RMAN, where as pfile cannot be backed up by RMAN. You have to manually copy the pfile to a backup location.
2. Less chances of human errors, if you are using spfile, since the file resides on server and when you modify any of the parameter in spfile then Oracle checks for the syntax.
3. If you need to start Oracle remotely and if you are using pfile instead of spfile then you should also have the same pfile on your local machine, where as in case of spfile you don’t need spfile file on your local machine.

So, how do you identify if you are using pfile or spfile ??
Just execute the following query

SQL> set linesize 100
SQL> set pagesize 200
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
       FROM sys.v_$parameter WHERE name = 'spfile'

You can also use the V$SPPARAMETER view to check if you are using a PFILE or not: if the “value” column is NULL for all parameters, you are using a PFILE.

See below screen shot for more details.

pfile or spfile

pfile or spfile

You can also see through SHOW PARAMETER command

SQL> show parameter spfile

Here goes the sequence followed by Oracle when we start the instance :

1. If will try to find spfile${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
For example, if my ORACLE_SID is : mytest , then my spfile would be


2. If above file is not present then use the spfile.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)

3. If above file is not present , then use the init${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database(Windows).

4. If above file is not present , then use the init.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database(Windows).

If you have a pfile, you can always create a spfile out of out or if you have a spfile you can create a pfile from it.

Assuming your SID is 'mytest' and
ORACLE_HOME is '/u01/oracle/product/10.2.0/db_1/'

SQL> create pfile from spfile
This will simply create a pfile in the
following path '/u01/oracle/product/10.2.0/db_1/dbs/initmytest.ora' file

SQL> create pfile='/u01/oracle/product/10.2.0/db_1/try.ora' from spfile
This will create a pfile into user specified dir with the name try.ora

SQL> create spfile from pfile
This will create a spfile from a pfile with the following name 'spfilemytest.ora'
at the following path $ORACLE_HOME/dbs/

SQL> create spfile ='/u01/some_dir/spfilemytest.ora' from
pfile = '/u01/some_other_dir/initmytest.ora'

This will create a spfile from a pfile with the following name 'spfilemytest.ora'
at a user defined DIR

While doing a Oracle DB startup, you can specify pfile as a parameter. 
For example
SQL> startup pfile='/some_path/initSID.ora'
This will process or read pfile to start the DB

NOTE : You cannot specify spfile with startup command, 
SQL> startup spfile='some_dir/spfile.ora'
The above SQL is not possible, but you can always call a spfile within a pfile 

and then call the same like

SQL> startup pfile='/some_dir/initSID.ora'
Contents of initSID.ora will contain only 1 parameter i.e.



SQL> show parameter control_file_record_keep_time
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     10

SQL> alter system set control_file_record_keep_time=12 scope=both;

System altered.

SQL> show parameter control_file_record_keep_time

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     12

Alter Parameter

The SCOPE parameter can be set to SPFILE, MEMORY or BOTH:

– MEMORY: Set for the current instance only. This is the default behaviour if a PFILE was used at STARTUP.
– SPFILE: update the SPFILE, the parameter will take effect with next database startup.
– BOTH: affect the current instance and persist to the SPFILE. This is the default behaviour if an SPFILE was used at STARTUP.

The COMMENT parameter (optional) specifies a user remark.


RMAN (Oracle’s Recovery Manager) will backup the SPFILE with the database control file if setting “CONFIGURE CONTROLFILE AUTOBACKUP” is ON (the default is OFF). PFILEs cannot be backed-up with RMAN. Look at this example:


Use the following RMAN command to restore an SPFILE:

Categories: Oracle DBA

How to put Oracle Database in Archive Log Mode !!

Today i am going to show you how we can change Oracle database Server mode from NO-ARCHIVE LOG to ARCHIVE LOG .

You can run a Oracle Database in ARCHIVE LOG mode or NO-ARCHIVE LOG mode depending upon the requirement.

Normally one would prefer to run in ARCHIVE LOG mode in Production , where as in Test/Development server one might want to avoid , since there are some overheads , but if you are really concerned about your data even on the Development Server, then you can always put DB in ARCHIVE LOG mode.

So basically the advantages of putting db in ARCHIVE LOG mode are :
1) Point in time DB recovery.
2) If you need to perform hot backup or RMAN ( Recovery Manager backup ) then your DB should be running under ARCHIVE LOG mode.

How do I determine if my Oracle DB is running under ARCHIVE LOG mode ??
Once you have taken a connection to SQL Plus , execute following command

SQL> archive log list


SQL> select name "DB Name", log_mode from v$database

Refer to below screen shot for output

Check Archive log mode

As we can see currently my db ( testdb ) is in NO-ARCHIVE LOG Mode.

Here goes the steps to put it in ARCHIVE LOG mode.

SQL > shutdown immediate;
This will terminate all sessions, rollback any un-committed
transactions & shutdown Oracle db.

SQL> startup mount;
This will start the db in Mount mode.

SQL> alter database archivelog;
This step will put db in ARCHIVE LOG mode.

Refer to below screen shot for commands in action


Now since the DB mode has been changed, we can open the database by executing following command

SQL> alter database open;
This step will open the database.


Open the Database

We can verify if db is now running in Archive Log or not by re-executing the following command

SQL> archive log list


SQL> select name "DB Name", log_mode from v$database

Refer to below screen shot for output


So going forward all our Redo logs are getting ARCHIVED in Archive Log location instead of getting overwritten … 🙂 , so the next question which you might ask as to since now db is running in ARCHIVE LOG Mode where are the logs getting archived … i mean what’s the ARCHIVE LOG path ??

If you refer above screen shot ( SCREEN 4 carefully ) it says “Archive Destination ” as “USE_DB_RECOVERY_FILE_DEST”, so basically this parameter is holding the path

execute following code

SQL > select name, value from v$parameter where name = 'db_recovery_file_dest';
OUTPUT -- > This gives the path as

Note : Your system might have a different path
depending upon what's your ORACLE_HOME DIR

Refer to below screen shot


Archive Log Path

Now go into this path , in my case I will jump to /u01/oracle/product/10.2.0/db_1/flash_recovery_area and do a file listing at OS Level, the directory contains a folder with my db name i.e. MYTEST and within that there are some sub folder which are expected to holds actual ARCHIVE LOG files

The presence of ARCHIVE FILE would depend upon if Oracle has really Archived any of the REDO file yet or not, but you can always force Oracle to Archive current Redo log by executing the following commands

SQL> alter system switch logfile;

Complete path for Archive log files

IMPORTANT NOTE : Make sure that you have some mechanism setup which should delete the obsolete archive files from this path after every 2-3 days. In case this drive gets full and Oracle cannot archive Redo Log to this location, the database will freeze or hang instead of overwriting existing REDO Logs. I will try to cover this topic in coming weeks as to how we can remove these files after every 2 -3 days.