Master the Oracle Database 11g tutorials – Easy Way

I assume 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 let us set the SID

set ORACLE_SID = xe

To connect to the database, we use 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, nomount is used

SQL> nomount;

To show the server parameter file location, with show parameter pfile command

SQL> show parameter pfile;

To shutdown database

SQL> shutdown immediate;

let’s 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 then it won’t go on nomount mode

SQL> startup nomount;

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

SQL> select status from v$instance;

To know the name of control file (Control file can be considered as a 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;

To 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 multiplexing by using the spfile.

To show/ describe the number of column in controlfile, we use the following command

desc v$controlfile;

To 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 the location of previous control file location and another in a 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, let’s try shutdown command

shutdown immediate;

the role of spfile is seen when starting the system

Copying our control file to our new location 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 copy in similar fashion to make CONTROL02.CTL file

let’s 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 others.

let’s 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.

To get the location/ contents of control file:

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

• the alert file will have a log of everything
• scrolling at the bottom we will find the trace file/ back up file with .trc extension
• let’s 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 a new control file) with a new name and type all files.

check the status with

select status from v$instance;

if it is open, shutdown

shutdown immediate;

• now let’s 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, let’s try startup nomount

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

To restore the control files and change to OPEN state, this can achieved by the command

@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, let’s 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 a log file
• more than one group
• fills the next group after filling first, and fills in a circular fashion
• overrides first after writing last

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

Show the number of logfile

desc v$logfile;

List all groups, 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 the 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 with 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 let’s 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 the inactive state only
• I am trying to drop group 4, so le’ts 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

Show size and details about the logfile

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 achieved by:

Steps to rename/relocate logfiles:

Shutdown the database

shutdown immediate; 

Move the logfile from old location to a 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;