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 csitportal.com
  • Oracle DBA Concise Handbook(covers 9i to 11g) by Saikat Basak , Oracle Certified Professional on conseiller.com
  • 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,

startup;


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

@f:\multiplexing\create_ctlfile.sql


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;


output:

    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.

output

GROUP# STATUS              MEMBERS
------ ---------------- ----------
     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;

Output

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
EA\XE\ONLINELOG\01_MF_1_DG0QXZPK_01.LOG'

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;

output

MEMBER
----------------------------------------------------

C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_DG0QXZPK_.LOG
C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_DG0QY303_.LOG


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;

output

   GROUP#    MEMBERS STATUS
-------- ---------- ------------
        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;

output

 GROUP#    MEMBERS STATUS
------- ---------- ---------
      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.

output

  GROUP#    MEMBERS STATUS
------ ---------- -------------
       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;

output

BYTES STATUS              MEMBERS
------ ---------------- ----------
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