backup management oracle

if you were starting a new role as DBA and were inheriting say 50 oracle databases, and you wanted some assurance on the backup policy for all 50 oracle databases, what exactly would you check for, and how would you check for it within the oracle software. What kind of problems can you encounter with oracle backups, presumably forgetting to backup all the relevant pieces would be the first thing youd check, i.e. is there a backup scheduled.

ps - I am not a DBA I work in a risk role but I would be interested in the above.
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
As far as making sure some backup has been performed, there are a couple of views:
v$backup and v$backup_datafile that should tell you what has been done.

The problems that may be there are numerous but you hit on the biggest one:  Not backup up everything you need.

A very strong second, IMO, is improper tape rotation.

For example:
Only having files, especially archived redo logs, on one and only one tape.
Getting tapes out of sequence.
pma111Author Commented:
do you write your db backups directly to tape, or are they written to disk before the file system backup sweeping them up with all other data out to tape? -security of the folder where the backups were written to was another possible issue.

when it comes to configuring a new backup job for an oracle DB, where exactly is that done within oracle, or where would you as a DBA see what is currently configured to run and when.
Geert GConnect With a Mentor Oracle dbaCommented:
i get this question every year
the audit picks some random database and some random timestamps and asks for a restore to that point in time of each database

keep in mind that this can take time
> retrieving the tapes from the off-site backup storage company
> restoring the database
> get the apps to connect to the restored database

not only will you get an answer if there are backups, but you'll also be able to assess if the team is capable of actually performing a recovery in case of disaster
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

pma111Author Commented:
are there any queries that would list:

1) back history log for say last month, including success/failure
2) backup schedule, i.e. how often the backup is scheduled to run (and to where)
3) pieces missing a recent backup
slightwv (䄆 Netminder) Commented:
>>do you write your db backups directly to tape

I do disk-to-disk-to-tape.  The backup process grabs the folder that contains all my backup files.

I also do what I call a rolling incremental (I forget Oracle's official term).  It takes an incremental backup then applies it against the full.  This way I am only one incremental away from a full recovery.

I keep 7 days of my backup files online at all times.  This way any single file is on at least 7 consecutive tapes (except for the ones created from 1 to 6 days ago...).

>>where exactly is that done within oracle

For me, it isn't.  I have an RMAN script file that is called from a BAT file I created.  The BAT file is a scheduled task in Windows.
slightwv (䄆 Netminder) Commented:
>>are there any queries that would list:

Possibly yes.  A lot depends on how the database is backed up.

For example:  Are you using RMAN or some 3rd party product?

Before you ask:  I don't really have any in my toolbox for my database.  I'm sure you can find some out on the web.

All I check is v$backup_datafile for the previous day to make sure all my datafiles were backed up.  If they were, I sort of 'assume' my entire BAT script ran and everything else was backed up.

If you use RMAN there is a TON of reporting you can do.  The online docs talk about them.
pma111Author Commented:
is RMAN part of the purchase when you buy oracle RDBMS, or an additional product you have to pay for. one of our admins tells me he uses shell scripts to do hot backups, which sounds like they dont use rman at all. or maybe the shell scripts call on rman
slightwv (䄆 Netminder) Commented:
RMAN is included.

>>which sounds like they dont use rman at all. or maybe the shell scripts call on rman

My gut instinct is they do not use RMAN.

RMAN isn't a simple 'run setup.exe' and you are done.  Like anything powerful, it takes a lot on understanding because of its complexity.

I've been using Oracle for over 20 years and when I finally made the switch to RMAN it was difficult.  It took a LOT of reading (and testing/playing) and even an SR with Oracle Support to finally pull off what I was wanting it to do for me.

Also, many people 'go with what they know'.  If they feel they are good at raw shell scripts, they likely use them.  If they are good at something else, they use it.

No matter what is used, it all comes down to expertise with Oracle and how it works.  It is sooooo much more than just backing up the data files.
pma111Author Commented:
it just becomes a bit impossible to prove whats being backed up, is it succesful, if they arent using common methods, i.e. shell scripts, as I assume the whole RMAN reports are pointless at proving whats being backed up if they dont use RMAN
slightwv (䄆 Netminder) Commented:
Correct on all assumptions.

Keep in mind that even RMAN doesn't do everything that a DBA might deem 'important'.

For example:  Part of my RMAN script creates a text-based pfile in a specific location from my spfile.  Is this a necessary step for everyone?  Probably not but it is for me.

In past systems I have even created a trace version of my controlfile as part of the backups just in case my binary controlfile becomes corrupt and I need to recreate it.

There are some basics that must be backed up but there really isn't a list of 'everything'.

Most of the things that are optional are personal preference or possibly driven by SLAs.
Praveen Kumar ChandrashekatrConnect With a Mentor Database Analysist Senior Commented:
I totally agree with Slightwv on backup and recovery strategies, as it depends entirly on the  bussiness needs.

First check with your DBA what is that he is doing in shell script to backup the database i.e is he using RMAN, begin backup mode or a logical backup. We can create a log file through shell scripts through which we can know is the backup was successfull.

I'm using shell scripts to call RMAN for backup in my projects, as its easy to schedule it on crontab in unix severs.
Geert GOracle dbaCommented:
didn't know risk assessment actually required a detail of how it was done ...
everyone can give scripts of how the backups are taken, doesn't mean they actually are

an automated report of the backups being taken: either daily, weekly or monthly report

a timed test every 6 months or so showing the team is capable of actually restoring a database addressing it

the test should be documented and should give all the details of how it is done
all the failures should appear like this
even if the 2nd person responsible for restoring is capable when the 1st is sick

there is also the docs of what can go wrong and how to recover from it with rman
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.