Archive for the ‘Oracle DBA’ Category

Find out DBID !!

One can find out dbid of a database through various means :

a) Execute a query, if db is either in mount mode or open

SQL> select dbid from v$database ;


b) If the db is is nomount mode but connected to recovery catalog, then you can find out while connected to Recovery Catalog

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
——- ——- ——– —————- — ———- ———-
1       18      PRIMDB   555044608        PARENT  1          09/18/2011 17:33:42
1       19      PRIMDB   555044608        PARENT  787897     05/23/2015 20:01:07
1       20      PRIMDB   555044608        PARENT  1098108    06/10/2015 18:22:30
1       2       PRIMDB   555044608        CURRENT 1115148    06/11/2015 03:44:36

Read more…

Categories: OCM, Oracle DBA Tags: ,

Setting up swapspace for Oracle

Sometime we land up in a situation where we have to increase swap space, since Oracle installation expects a certain % of swap space depending upon how much RAM you have allocated to O/S. For example if we have allocated 64 mb of RAM, then we need to specify 128 mb of swap space… again this is not always the case that you have to specify swapsize twice of RAM. It all depends on the OS etc, generally here is a little thumb rule

If you have between 1 and 2G RAM, you need to configure 1.5x RAM for swap space.
For 2 to 8G RAM, swap space has to equal RAM.
For RAM more than 8G, swap needs to be ¾ RAM.

If we need to ad swaps pace, we can either add a new partition of type swap or else we can file which can be used by O/S to be used as swap. I am going to demostrate adding swap space via adding file

How to check how much swapsize is specified via following command ?

# swapon -s


# cat /proc/meminfo

If I need to add swap space of let’s say 2 gb, i can issue following ‘dd‘ command

# dd if=/dev/zero of=/swapfile bs=1024 count=2097152

Count 2097152 is calculated via multiplying bs or blocksize of 1024 * 2048 mb


1) if=/dev/zero : Read from /dev/zero file. /dev/zero is a special file in that provides as many null characters to build storage file called /swapfile.
of=/swapfile : Read from /dev/zero write stoage file to /swapfile.
2) bs=1024 : Read and write 1024 BYTES bytes at a time.
3) count=2097152 : Copy only 2097152 BLOCKS input blocks.

The following command will setup the swap space

# mkswap /swapfile

Activate it via

# swapon/swapfile

There is one small thing still pending, once you reboot the Linux server this information is lost, in order to make it more permanent, we need to add following in /etc/fstab file, this file is primarily responsible for telling Linux that what all devices to mount, just add following line in fstab ( File System Table )

/swapfile               swap                    swap    defaults        0 0

How to Drop Oracle Database ?

September 14, 2011 Leave a comment


With Oracle 10g release, Oracle Inc. has provided a sql command to Drop Database. Prior to Oracle 10g, the DBA had to physically delete the relevant files ( such as data files, control files, online redo log files & spfile )used by Oracle Database.

To drop the database, before even connecting the SQL prompt , make sure you have specified correct ORACLE_SID. You do not want to drop another db by accident.

Once you have specified/verified correct ORACLE_SID variable, now you need to shutdown the database via sql

SQL> Shutdown immediate;

Now, you need to to bring the database in exclusive restrictive mount mode

SQL> startup mount exclusive restrict;

Mount Database in exclusive restrict mode
Now just issue the drop database command

SQL> drop database;

Drop Database

NOTE : You can also perform whole of this activity via GUI utility provided by Oracle which is called dbca ( search for binary dbca in $ORACLE_HOME/bin dir) . That utility will behind the scenes take care of starting db in mount restrict mode & then based on your selection of db that you would want to drop, it will do so.

In case this activity was performed on windows server, then you need to also execute following command to remove service

oradim -DELETE -SID <name of instance>


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