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

Uploaded by roughsealtd on 11.01.2011

I now want to talk about a serious topic, which also happens to be a favourite topic
of DBA job interviews –
When you install Oracle Express on your machine, you find in the menus (here on Linux)
two very interesting commands, backup database and restore database.
If you have ever lost a file following a bad operation or a computer hiccup,
you’ll probably grasp how people feel about database backups when the database
contains all the critical data of a business or organization.
The menu options actually run scripts that are located under
$ORACLE_HOME/config/scripts on Linux,
and under BIN on windows. Those scripts are simply wrappers for
RMAN, Oracle’s backup utility. RMAN stands for Recovery Manager. It can do many things,
I’ll just mention a few, and the syntax can be rather complex.
I’ll try to give you a few ideas about it.
What RMAN does as the Oracle Express scripts are written is that it recombines
the data that is in your datafile.
You have seen in part 3 that when you create a tablespace you associate to it a
file of a given size. A lot of space can be unallocated to tables or indexes in
this file; and even what is allocated may not be used to store data.
As a result, if you were just to copy files, you would copy a lot of unused bytes.
Not a major issue for a small database, but it can be a serious drain on disk
resources and a very long operation for big database. RMAN can copy files as
is on demand, but by default it extracts the used bits from the datafiles,
and recombines them in noticeably smaller files called backup sets.
The detail of how the chunks stored in the backup sets relate to the datafile
is most often stored in the control file, which is one of the reasons why I
have told you that the control file should be mirrored.
It’s also possible, in big corporate systems with multiple databases, to centralize
the information in a dedicated database.
If you run the backup script, you’ll discover two new subdirectories in
the flash recovery area.
One is called autobackup, and under it you’ll find a subdirectory the name
of which is actually a timestamp for the day.
The other one is called backupset, and it also contains a time stamped directory.
In both time stamped directories you’ll find a file with the .bkp extension,
which is a backupset.
I’ll try to explain the magic by taking a look at the script that is launched.
This is the key part in the Linux script, you find exactly the same RMAN commands
in the Windows script.
First you connect to rman saying "target /", which is very similar to
"sqlplus / as sysdba", it just means that you connect from a privileged
OS account to the local database defined in your environment.
First, a retention policy is set. It defines how many backups you want to keep.
Sometimes a backup may be corrupt, and you may be happy to discover that
you have another older one.
The first time you backup your database, the backup sets go to the flash recovery area;
the next time too, but starting from
the third backup, the oldest existing backup will be automatically deleted.
You need not worry about the flash recovery area growing out of control.
The next instruction basically tells that an "autobackup" goes to the flash recovery
area (it’s not the default destination).
What is an autobackup?
It’s something which is switched on by the next command, it’s not the default,
and it asks RMAN to also save a copy of the control file and of the
parameter file to a backup set. You’ll see soon when and how you can use
such a backup.
Next comes a command that saves as a text file the binary parameter file.
I have already shown this to you when mirroring control files, I hadn’t
mentioned the possibility that is used here of specifying the file name.
This file is used in the recovery process.
The really important command is the "backup" one that really starts the operation.
The following command undoes the autobackup. If you comment it out, your control file
would be automatically saved when you add a file to the database.
Therefore, control files are only backed up during the backup of the database
– perhaps because you arent’t supposed to add files to an Oracle Express database.
On some large databases files may be added and dropped regularly, and I’d keep
autobackup on.
For a small database it doesn’t matter so much.
Finally, the "delete" command cleans up, following the retention policy that
was specified at the beginning of the RMAN script.
The way it works is as follows.
Suppose that at time t0 you run the backup script. You are going to save backupsets
for datafiles and control file and spfile.
Then you go on with operations and log files are archived. Remember that log files
record the history of all changes to the database.
At t1, new backup, and the archived log files that have been archived since the
end of the t0 backup record the history of transactions between t0 and t1.
They provide all the detail between the two backups.
Let’s go on, new backup at t2 and here, per the retention policy,
the backupsets created at t0 will be removed.
But then the archived log files for the t0 to t1 become obsolete,
because they are meaningless without the t0 data.
RMAN automatically removes them. By scheduling regular backups, you don’t have
to worry about purging directories.
Now, let’s suppose you delete by mistake forum.dbf or any datafile.
It’s time for the "restore database" script.
What it does is that, first it forcefully shuts the database down,
then, by using information from the controlfile, it unpacks the backupset
and restores the datafiles to their state at the end of the backup.
But then, rman takes all the archived log files that have been generated
since the database was backed up, and applies them.
Finally, it takes whatever hasn’t been archived yet in the online directory,
and applies it too.
When you open the database, not a single transaction has been lost.
But if deleting a file sometimes happens, what is more common is a big mistake
in processes – for instance corrupting the data by running the same program twice
instead of once, or purging data before it was archived.
RMAN allows you in such a case, when you have a backup ready, to restore the database
to a time when it was correct.
This is known as a point-in-time recovery.
I am going to show you how to proceed by basically applying the RMAN commands
from the "restore" script, with just a few changes.
We have already seen that we must connect to rman using "rman target /".
Then I first restart the database using as parameter file the text file that was
created during the backup process.
I say "nomount", which means that the control file isn’t opened.
This is just to bootstrap the process, I need the instance to be started
to run the next commands.
Then, I restore the parameter file and the controlfile that were saved when
autobackup was switched on. At this stage I am sure that I have the proper
parameter file and control file,
and I restart again the database ("force" shuts it down if it’s up) with the
parameter file that was restored, and I say "mount", which means that this time
I open the control file.
This control file describes the state of my database at the end of the backup,
which may have occurred several days ago.
Now the interesting stuff.
I start a block with "run" – it means that all commands, like in a transaction,
must succeed together, that they really are a single process.
First thing, I define a limit with ‘set time until’. If everything went wrong
during the last hour, I say that my limit will be now minus one hour,
that is 1/24th of a day.
I could also specify an absolute date and time.
I am going to rewind the clock by one hour.
Restore database extracts the files from the backup sets.
Next step, recover database is going to take the archived logs and apply them,
but only changes that occurred between the backup and, in this case, one hour ago.
I may have later archived redo log files, and may have an online redo log file
that wasn’t archived. I have already forgotten the archived redo log files,
and I must say to Oracle to completely forget the online redo log as well,
which I do by adding the "resetlogs" clause to the "alter database open" command.
Et voila, mission accomplished.
Be aware that in a point in time recovery the whole database experiences time travel.
If you have dropped an important table by mistake, for instance because you thought
that you were on another database, you need with Oracle 10 no point in time recovery.
It works exactly like when you remove a file in Linux or Windows:
Oracle has a trash bin that it calls recycle bin.
When you drop a table, it is actually renamed BIN$ followed by some horrid name,
and the same thing happens to its indexes.
You can check what the recycle bin contains by running "select * from recyclebin".
To restore a table that was dropped by mistake,
a simple "flashback" command is enough.
Oracle purges the recycle bin when it needs the space, but you can do
an "empty trash" the Oracle way by issuing "purge recyclebin".
If you are a mouse addict, there is also something in the administration toy,
in the "Utilities" section, that allows you to manage the recycle bin.
I have talked so far of how to recover the database when something wrong has
been done, which is frequent enough.
But what if say your power unit fails?
What if the room where the computer stands is flooded?
Obviously keeping all backups on the server is taking a big risk.
I’ll talk about disaster recovery in my next video.