[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


backup management oracle

Posted on 2014-01-03
Medium Priority
Last Modified: 2014-01-19
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.
Question by:pma111
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 668 total points
ID: 39753707
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.

Author Comment

ID: 39753720
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.
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 668 total points
ID: 39753760
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  


Author Comment

ID: 39753775
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
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39753785
>>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.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39753798
>>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.

Author Comment

ID: 39754072
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
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39754088
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.

Author Comment

ID: 39754106
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
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39754125
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.
LVL 12

Assisted Solution

by:Praveen Kumar Chandrashekatr
Praveen Kumar Chandrashekatr earned 664 total points
ID: 39758581
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.
LVL 38

Expert Comment

by:Geert Gruwez
ID: 39758608
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question