So, this blog post will be all about the Oracle Database, lessons from very beginning to advanced level. The contents will be added regularly.

If you are searching for resources for Bsc CSIT Seventh Semester(7th sem), then these are some of the links of the materials I got that might be useful for learning and appearing DBA exams.

  • Database Administration Handbook, short note by Dadhi R. Ghimire available on
  • Oracle DBA Concise Handbook(covers 9i to 11g) by Saikat Basak , Oracle Certified Professional on
  • TU CSIT DBA Model/Old Question Collection by csitauthority

I assume that you have already installed Oracle Database Software 11 g R2 edition on your computer.

All the commands below are to be executed in terminal/command prompt.

At first lets set the SID

set ORACLE_SID = xe

connect to database with sys dba privileges

sqlplus / as sysdba

After establishing a database connection you can see SQL> prefixed in your command prompt. We will be doing all our database queries after this SQL>, remember we don’t need to write this command. Also, all database queries end with a semicolon. ;

Some basic commands##

To search for server parameter file

SQL> nomount;

To Show the server parameter file location

SQL> show parameter pfile;

To Shutdown database

SQL> shutdown immediate;

lets rename the parameter file , both the files, the location is the value shown in the show parameter pfile;

we are renaming the file to show that if we change initial parameter file, than it wont go on nomount mode

SQL> startup nomount;

this means it didn’t get the parameter file specified as we have changed the file name, lets rename the filename to previous state

SQL> select status from v$instance;

To know the name of control file (Control file is the heart of database)

SQL> select name from v$controlfile;

Change to mount mode

SQL> alter database mount; 

after altering the database mount, now we can see the control file is mounted

To show the mount status:

SQL> select status from v$instance;

Change database to open mode:

SQL>  alter database open;

Checking the database status:

SQL> select status from v$instance;

we can see the status as OPEN

Multiplexing in Oracle##

We are doing multplexing by using the spfile.

Show/describe the number of column in controlfile

desc v$controlfile;

List the control files and their location

select name from v$controlfile;

Alter system set control_files=’C:\APP\DBA\ORADATA\ORCL\CONTROL01.CTL’,’C:\APP\DBA\ORADATA\ORCL\CONTROL02.CTL’, ’E:\multiplexing\CONTROL03.CTL’ Scope=spfile;

here we are creating new control file one in location of previous control file location and another in different drive.

I made a new folder multiplexing in E drive so the new control file is in E:\multiplexing

After altering the set of control files, lets shutdown

shutdown immediate;

the role of spfile is seen when starting the system

in E drive

host copy C:\APP\DBA\ORADATA\ORCL\CONTROL01.CTL  D:\multiplexing\CONTROL03.CTL

Here, we are copying from C drive CONTROL01.CTL file to E drive’s CONTROL03.CTL file

if there was only one control file present in the system, then we can similarly copy to make CONTROL02.CTL file

lets startup our system,


Next thing what we are going to do is create pfile from spfile

create pfile from spfile;

after creating pfile from spfile, now we are backing up our controlfiles, backup is necessary for databases, when one controlfile fails, we can restore from other.

lets check our work by

select name from v$controlfile;

This lists our control file which are in different drives.

Backing Up Control Files##

Control Files are backed up with

alter database backup controlfile to trace;

the backup will be in ASCII format, where as the original control file will be in binary form.

Get the location/contents of control file:

select value from v$diag_info where name='Diag Trace';

  • the alert file will have log of everything

  • scrolling at the bottom we will find the trace file/ back up file with .trc extension

  • lets open the file

  • this trace file will have instruction for creating manually the controlfile

Save the file in the location of multiplexing folder(another drive than C where we created new control file) with new name and type all files.

check the status with

select status from v$instance;

if it is open, shutdown

shutdown immediate;

  • now lets open the control file locations and rename them.

we assume there is no control file in our system by renaming all the control files, in my case there are three control files , you can list the control files with their location by

select name from v$controlfile;

We are doing this just to see the effect when there is no control file.

after renaming, to see the effect, do

startup nomount;

it won’t find/show control files as they have been modified or removed

In such case we need to restore control files.

Restoring Control Files##

Restore the control files and change to OPEN state by


this is the location of the .trc file we just saved in our F drive

this will restore our original control files, previously we renamed them assuming the files are lost, lets see there will be the original control files restored.

select name from v$controlfile;

Check status

select status from v$instance;

Viewing Control File Names and Locations##

if our database is in : nomount/mount/open stage we can

View location and name of the control files’ location with:

show parameter control_files;

Redo Log File##

  • important for recovery
  • logwriter LGWR writes in log file
  • more than one group
  • fills next group after filling first, and fills in circular fashion
  • overrides first after writing last

Two modes: archive(before overididng, writes from archiver process, no data loss) and non archive

desc v$logfile;

List all gorups, redo log files and their members

select group#, member from v$logile;

we can keep copy of each redo logfile by multiplexing.

Show the status of logfile

select group#,status,members from v$log;


    GROUP# STATUS              MEMBERS
	------ ---------------- ----------
         1 CURRENT                   1
         2 INACTIVE                  1

here, the redo log file is writing to group 1 logfile.

Status explained

  • INACTIVE: redo logfile doesn’t use for instance recovery

  • ACTIVE: previous logfile where log was written

  • CURRENT: the file currently being written

Forcefully switch from one log to other

alter system switch logfile;

After doing this, we can check again by

select group#,status,members from v$log;

in my case the group 1 is set to active and 2 to current.


------ ---------------- ----------
     1 ACTIVE                    1
     2 CURRENT                   1

Add a new logfile by multpliplexing

alter database add logfile member 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_ARE
A\XE\ONLINELOG\01_MF_1_DG0QXZPK_01.LOG' to group 1;


Database altered.

I just renamed my previous logfile by appending _01

Oracle recommends to place each redo file in different drives and similarly the multiplexed file.

Dropping a Logfile##

Logfiles can be dropped by drop command

we are dropping the previous logfile we created

alter database drop logfile member 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AR

After dropping the logfile, we need to delete it physically for erasing permanently.

Adding a New Redo Logfile Group in Oracle 11g database##

first lets find the location of our existing logfiles with

select member from v$logfile;




Add a new Redo Logfile Group

alter database add logfile group 4 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_DG0QXZPK_02.LOG' size 50M;

Here, the size is 50MB.

Now, we can verify with:

select group#, members, status from v$log;


-------- ---------- ------------
        1          1 INACTIVE
        2          1 CURRENT
        4          1 UNUSED

we can alter the status with

alter system switch logfile;

and if we redo

select group#, members, status from v$log;


------- ---------- ---------
      1          1 INACTIVE
      2          1 ACTIVE
      4          1 CURRENT

Group 4 is in Current status.

Dropping Group##

  • possible in inactive state only
  • I am trying to drop group 4, so lets switch the group with alter system switch logfile;

until group 4, is in inactive stage, lets keep altering.


------ ---------- -------------
       1          1 INACTIVE
       2          1 CURRENT
       4          1 INACTIVE

Dropping group

 alter database drop logfile group 4;

now there are only two groups left.

useful commands

select * from v$log;

select bytes, status, members from v$log;


------ ---------------- ----------
52428800 INACTIVE                  1
52428800 CURRENT                   1

Resizing Logfiles##

  • You cannot resize logfiles.
  • If you want to resize a logfile create a new logfile group with the new size and subsequently drop the old logfile group.

Renaming or Relocating Logfiles in Oracle 11g##

To Rename or Relocate Logfiles perform the following steps

For Example, suppose we want to move a logfile from C:\APP\DBA\ORADATA\ORCL\REDO01.LOG to D:\multiplexing\REDO01.LOG, then this is achived by:

Steps to rename/relocate logfiles:

Shutdown the database

shutdown immediate; 

Move the logfile from Old location to new location using operating system command

host move C:\APP\DBA\ORADATA\ORCL\REDO01.LOG  D:\multiplexing\REDO01.LOG

Start and mount the database

startup mount

Now give the following command to change the location in controlfile

alter database rename file 'C:\APP\DBA\ORADATA\ORCL\REDO01.LOG' to 'D:\multiplexing\REDO01.LOG';

Open the database

alter database open;

last updated on Tuesday, April 11, 2017

Posts you might like

Withdrawing Google Adsense Payment to eSewa in Nepal via Western Union

Quick how to guide on withdrawing your Google Adsense payment via Western Union remittance payment in Nepal. read more

Online Shopping Sites in Nepal

A curated list of ecommerce sites currently operating in Nepal providing different online shopping experience to Nepalese in different sectors. read more

Google Photos is really awesome! My Story

Sharing a piece of my story on how Google Photos eased my photo management. read more

Git remote basic commands

Git and Github basics read more

NASA Space Apps Challenge 2017 Kathmandu Nepal

International Space Apps 2017 held in Kathmandu, Nepal, Why should you participate in hackathons like NASA Space Apps, about the event blog and more. read more

Setting up git on Linux/Mac

Setup Git in your computer in few quick steps, be it on Linux (Ubuntu, Fedora, Redhat, Arch etc) or Mac or Windows read more

Oracle Database 11g R2 installation on Linux with Docker

Installing Oracle Database 11g R2 with Docker on Linux is pretty easy and saves time and effort. In a matter of few minutes, you will have your Oracle database up... read more

Buy Viber Out Credit without Credit Card with eSewa

Now, you don't need a credit/debit card to buy Viber Out Credits in Viber to make call to different numbers even when they don't have viber. read more

Github header goes black, more design changes rolling soon

Github's navbar is black now! And yeah it didn't appeal me. More design changes coming soon. read more

How to activate 4G in Nepal Telecom Prepaid/Postpaid with review [video]

Nepal Telecom just launched 4g service for the first time in Nepal today. To actiate 4G in your mobile follow these steps read more

10 Best Games of the Year 2016 | Clash Royale tops the list

Clash Royale gaming app becomes app of the year in app store. Read who made to the top 10 best games of the year 2016. read more

Prisma becomes App of the Year | 10 Best Apps of the Year 2016

Prisma a fancy photo editing app becomes app of the year in app store. Read who made to the top 10 best apps of the year 2016. read more

Configuring Github Pages for site5 sub domains

Explaining to you, how you can configure DNS for sub domains in site5 site to github page sites in easiest manner. read more

Top Trending Technological Innovations in Nepal

The most used and liked technological products and innovations either developed in Nepal or the general public platforms that have brought major changes in the lifestyle of Nepalese people. Also,... read more

How to uninstall Chinese 360cn Software/malware from your Windows Computer

Remove 360cn Chinese malware from your computer in easiest manner read more

Install iOS 10 on your device

Install the biggest update from apple to your iphone, ipad or any iOS device, the new iOS 10 is out now to the public. Install it today. read more

Doing facebook live from your computer

Live stream to facebook from your laptop or desktop computer in easiest way using OBS read more

Internet setting for Nepal Telecom and Ncell in iphone

Internet/3g/GPRS/4G/LTE Configuration setting for Nepal Telecom(NTC) and Ncell in an iphone in easiest way read more

What is is a Content Delivery Network(CDN) used by facebook read more

How to download a photo album from your facebook

Download albums from facebook at once. read more

Disabling automatic loading of last session in Sublime Text Editor

You might have got irritated everytime seeing two windows of Sublime Text Editor being opened every time you open Sublime. Here is a quick fix to it. read more