Solved

backup management oracle

Posted on 2014-01-03
12
577 Views
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.
0
Comment
Question by:pma111
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 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.
0
 
LVL 3

Author Comment

by:pma111
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.
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 167 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
0
 
LVL 3

Author Comment

by:pma111
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
0
 
LVL 76

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.
0
 
LVL 76

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.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Author Comment

by:pma111
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
0
 
LVL 76

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.
0
 
LVL 3

Author Comment

by:pma111
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
0
 
LVL 76

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.
0
 
LVL 12

Assisted Solution

by:praveencpk
praveencpk earned 166 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.
0
 
LVL 36

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
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmrvcon.htm#BRADV89703
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now