asked on
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.
ps - I am not a DBA I work in a risk role but I would be interested in the above.
Oracle DatabaseDatabases
Last Comment
Geert G
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
>>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.
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.
>>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.
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.
ASKER
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
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.
>>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.
ASKER
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
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.
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.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
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.