Oracle DBA Lite: an introduction to Oracle administration, part 2

Uploaded by roughsealtd on 09.12.2010

You have seen in the previous video that when you install Oracle Express you end up with
ONE control file, and
TWO redo log files. You probably don’t see anything wrong here, but this is a situation
that makes
many an experienced DBA cringe, and I’m going to try to explain you why.
When you start Oracle, it begins by reading the parameter file, spfilexe.ora.
As we shall see soon, the parameter file contains
the name of the control file that in turns contains
the names of the various data files you have seen in the previous video,
as well as the names of the online log files. I just show one here, because
at any given time only one is active. But it doesn’t JUST contain names. It also
controls whether files are up to date. As they are written asynchronously, they may
be “behind” if the database wasn’t properly shutdown.
In that case the online log that was last active is used to
bring all data files up-to-date, which is why log files are often called “redo”
log files. The control file is also very important for
backups because Oracle’s backup utility will usually store
backups to
“backup sets” that have no obvious relationship to data files. The relationships are usually
stored in the control file.
In other words, control files and redo log files protect data files. But what protects
Nothing. Hardware failures happen, and human errors happen more often.
As paranoia is a virtue of a good database administrator,
an Oracle DBA usually wants those files mirrored to another disk.
Oracle knows how to maintain the multiple copies.
The administration tool that is provided with Oracle Express has no facility for mirroring
files, and you’ll have to do it with Oracle’s
trusted command-line tool – sqlplus. If you want to become an
Oracle DBA, you’ll probably use it pretty regularly.
Real DBAs all use sqlplus very often. Even if the other editions come with a more serious
GUI tool for administering the database, sqlplus is invaluable in scripts and you should be
familiar with it.
If you are on Linux, connect to the oracle account that was created during the installation,
and ensure that you have ORACLE_HOME correctly set in your environment, as well as ORACLE_SID,
and that your PATH contains ORACLE_HOME/bin. You probably want to set these variables in
your bash profile. On Windows, everything is in your
registry already.
Before using sqlplus, I advise you to navigate to ORACLE_HOME sqlplus admin,
and to edit the file called glogin.sql. This file is always read when you launch sqlplus.
Add to this file the following:
Define underscore editor = and notepad if you are on Windows, or gedit or vi if you
are on Linux, or whichever text editor you are comfortable
with. This will allow you to easily edit your commands,
as I’m going to demonstrate shortly.
To use sqlplus, you must open a terminal window and type
Sqlplus / as sysdba
/ (slash) takes the place of the username and password that you usually provide.
As “sysdba” suggests, you are here identified by your system account, not by Oracle.
On Linux, you must belong to the same group as oracle to be able to use this way to connect
to the database.
You can now type SQL commands to the prompt. Commands are kept in a buffer and executed
when you type either a semi-colon or / as the first
character of a new line.
I am going to mistype something on purpose. This query should give my username but I have
mistyped “from”.
I just have to type “ed”, it saves the last query to a temporary file and opens the
editor I have specified in glogin.sql. I correct the mistake, save
the file on exit, it’s reimported by sqlplus and I just have to type / to run it.
As you can see, I am connected as SYS, the most powerful user in an Oracle database.
But first let’s modify the settings so that Oracle mirrors the control file.
The first command to run is
Create pfile from spfile
What does it do? Well, the spfile is spfilexe.ora, as told by the ORACLE_SID in our environment.
This file is a binary file that cannot be easily edited. The create statement copies
its content to a file named, not “pfilexe.ora”, but “initxe.ora” in the same directory
on Linux and in the “database” directory on Windows.
This will allow us to edit and change the parameters.
Most parameter can be dynamically changed by issuing special SQL statements, but as
the control file is so vital it cannot be changed dynamically.
Next we are going to shutdown the database by issuing “shutdown immediate”.
Then we can exit sqlplus.
Next step, we must edit initxe.ora.
On Windows, don’t use Notepad to do it but WordPad, because Notepad doesn’t recognize
You see that some parameters are prefixed by xe,
and most other ones by *. It’s possible with other editions to have two instances
of the database accessing the same set of data files. Parameters
are preceded by * when they are common to all database
instances, and by the instance name when they can be different.
So, this is my windows parameter file,
here is the parameter I want to change, you can note that it’s control_files, plural,
and I just add a comma and the name of my copy of the control file between quotes.
I save the file, and of course I must copy the original control file to the location
I have specified.
Remember I have shutdown the database, I couldn’t do it if it were open.
Now let’s use sqlplus again.
“Idle instance” is the Oracle way to say that the database is down.
First thing, I execute the command “create spfile from pfile”,
which is just the opposite of the command I run to create initxe.ora.
As you can guess, it takes initxe.ora and regenerates a new spfilexe.ora binary parameter
Once this is done, I just have to run “startup”.
I can type “show parameter control_files” to check that my two files are taken into
Now, let’s deal with redo log files.
The way they work is that every time you commit a change, it may not be written immediately
to database files but it is immediately recorded
to the current redo log file.
When the file is completely written, it triggers a flush of memory changes to database files
(it’s called a checkpoint),
then there is a log switch and the other log file becomes current.
When this one becomes full, log switch again and the other file is overwritten – because
the changes it records have been at this stage applied
to the database files, its content is no longer needed.
When you mirror redo log files, the same information is simultaneously written to two files,
which should be on two different disks.
Two (or more) files that are simultaneously written constitute a group.
Individual files are called the members of the group.
To display the current status I am going to query a view called V$LOG.
The Oracle data dictionary contains many views called V$ something that are simply the mapping
of relational views to data structures that contain
volatile information about the current state of affair.
V$LOG tells which redo log group is currently active. To add a new redo log file to a group,
I just have to issue the command
Alter database add ‘the file name’ to ‘the number of the group’
No need to specify the size, it will necessarily be the same size as the existing file.
ALTER DATABASE is the command that you usually use to modify the structure of your database
– to add, resize or remove files most often.
I execute the same command for the other group and then I can query V$log again to check
how many members I have now. But v$log only gives a
global picture of groups, and I may want to query V$logfile
to have information about individual members, and here the picture doesn’t look so good.
But in fact, the status “invalid” is easy to understand: as we have just added the files,
they aren’t yet exact copies of the original file.
What I am going to do is manually trigger a redo log switch.
For that, I use the ALTER SYSTEM command that is used, contrarily to ALTER DATABASE, to
change not the structure but how a currently running
system operates.
This really brings in the system one of the two files I have added.
I switch again, and now both files are fully operational as I can easily check.
So at this stage we have mirrored both the control file and the redo log files to a different
We can now give a first look at the administration toy but before that
I want to show you how you can change the HTTP port on which it listens if 8080 conflicts
with something else. This is more particularly
for Windows, as you cannot configure again as you could
with Linux.
You change the port by calling a stored procedure that takes the new port, for instance 8081,
as parameter. This procedure, sethttpport, belongs to a package called dbms_xdb.
But remember that my HTTP server is the listener. I must therefore warn the listener to listen
on the new port, which I do by issuing
ALTER SYSTEM REGISTER, what is normally automatically done when the instance starts.
We can quit SQL*Plus, and run lsnrctl stat to check that the listener is aware of the
new port.
So, let’s now point our browser to localhost colon 8080 or whichever port you have specified
slash apex.
On the login screen you can connect as SYSTEM with the password you have specified during
the installation.
Then you see the main page and what you should look at is at the bottom right.
What an Oracle production DBA doesn’t like is this:
log archiving off.
What is log archiving? Well, it still has to do with redo log files.
When log archiving is on, a special process copies the redo log file in the group that
is no longer current and saves it, so that you still
have a copy of it when it is later overwritten. Archiving redo logs is required if you want
to take
hot backups, which means taking a backup of your database while it is running and that
people are changing the data – and if you want
your database to power a website, you usually want backups
but no downtime.
Archiving redo logs is necessary for one very simple reason:
when you copy a file, it can never be an instant operation. It takes some time, which depends
on the size of your file and the performance of your
If you start the copy of a file while it’s been modified, at the end of the copy the
file will be different. And what will the copy look like?
It will be neither the image of the original file at the beginning of the copy, nor the
image of the file at the end, but something in between
that very probably the original file never was.
But it will not be a problem for Oracle if you have all the redo log files that were
generated during the copy, because by applying them it will
be able to turn the copy into the exact copy of the
original file at the end of the backup.
But this only works if you have all the redo log files. If you have wrapped around your
groups, ouch.
Then you are in dire straits.
So, let’s quit the administration tool (I’ll come back to it in another video, promised),
return to SQL*plus, and once again let’s shut the database down. We cannot change the
logging mode dynamically, and we must record it in the
control files. I am going to start the database but with a twist: the “mount” parameter
tells to open the control files, but not to open the data files
for regular access.
A simple alter database command does the trick, which I can follow with “alter database
open” to make the database accessible to all users.
I can then switch the log files a couple of times. Where are all the archives gone?
If you go to the flash_recovery_area directory, you will discover that you have a new directory
called ARCHIVELOG with a subdirectory that is the
timestamp for the day. There you’ll find today’s archived files
with the .arc extension.
Now your database looks more like a serious production database.
Next time we’ll explore the administration tool – and still do stuff with sqlplus.