We help IT Professionals succeed at work.

backup management oracle

pma111
pma111 asked
on
648 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.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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 GOracle dba
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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 ChandrashekatrDatabase Analysist Senior
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.