Oracle Backup and Recovery Essentials
Oracle Backup and Recovery can be complicated (especially the recovery) and the consequences of getting it wrong can be very expensive in time and money and sleepless nights. Avoiding this requires knowledge - what are the possible strategies? typical scenarios? the pros and cons of each method of backup and recovery? | | We'll take a look at these so you have the information you need to make Oracle Backup and Recovery stress free in your organisation. However there will still be one thing missing - the proof. Whichever method(s) you chose, you need to test the whole process - the backup and the recovery - and the various scenarios - from the best to the worst - then you can sleep easy at night! |
What To Consider
One of the most important tasks is to find out how much data the business can afford to lose. For a production database, the users will probably say none, in which case you have to dig deeper and find out the source of the data, the amount of changes per day, how much it would cost the business if they lost any data and how much the business is prepared to pay to ensure zero data loss (deep pockets will be required).But let's not get ahead of ourselves, before we can discuss Oracle backup and recovery options with business users we need to know what options are available and the pros and cons of each option.
Oracle Tips& Tricks to
SKYROCKET Your Career!If you're not already a subscriber to Oracle Secrets Revealed, you're missing out on a myriad of tips and techniques to help you work better, faster and smarter.
Subscribe now
and ignite your career.
Oracle Backup and Recovery Options- Oracle utilities
export
(for backup) and
import
(for recovery)
or Oracle Datapump in 10g and above.
Advantages: fairly easy to use for basic situations. Can be used to backup only those objects (tables/triggers/views etc.) which have changed. Can be used to backup and recover individual objects, schemas, tablespaces or the whole database whilst the database is being used (although this may mean the backup contains inconsistent data if updates are taking place).
Disadvantages: both backup and recovery could be slow - especially for large databases, as objects are copied one at a time. No good for recovering from physical corruption or physical data loss. -
Offline (cold) backup:
This is performed when the database is shutdown (cold) and therefore unavailable for use. Operating system commands are used to copy the database files to an archive location on disk or possibly straight to tape. Recovery is the reverse - copy all the database files from the backup area to their original location (with the database shutdown), open the database and reset the logs.
Advantages: simple to understand, simple to perform.
Disadvantages: In the event of a failure you would lose all changes since the last backup. This is also potentially a slow process - you have to copy all the data files and other files comprising the database (except for the redo logs) every time and recovery would take at least as long as the backup. -
Online (hot) backup:
To use this option the database must be running in archive log mode. Tablespaces are backed up one at a time. Recovery involves restoring the required files from backup and then rolling forward (redoing changes since the backup) using the redo logs. For more details about this option see
here
.
Advantages: No data is lost. Database can be open for read/write during both backup and recovery. Good for recovering from physical data file loss/corruption.
Disadvantages: More complex than cold backup and database performance may be affected during backup and recovery. It may take some time to recover the data as all changes since the last backup have to be replayed. Not useful for recovering an individual or small number of objects (table/view/etc.) or from a logical data loss/corruption. Extra disk space is required to hold all the redo logs. - Standby database/Oracle Dataguard. This is strictly speaking more a high-availability option than an Oracle backup and recovery option, but is an option worth considering. This option uses one or more extra databases to act as a standby in case of a failure of the main database.
Advantages: Can protect against a wide range of failure scenarios. Standby databases can also be used for reporting whilst remaining in sync with the primary database.
Diadvantages: Much more complex and more expensive than other methods. - Oracle flashback database (available in 10g onwards). This builds on the flashback query option available in Oracle 9i and enables you to revert the whole database or just an individual table to its state at a given scn or point in time (assuming the flashback logs are still available).
Advantages: Easy to configure. Good for recovery of small number of tables from logical data loss or corruption.
Disadvantages: Extra i/o load may impact performance. Extra disk space is required for flashback logs. No good for recovering from physical data loss. - RMAN (Oracle Recovery Manager). This is a tool designed for the management of Oracle backup and recovery and can be used with the options already discussed except for export/import/datapump and Flashback database.
Advantages: Manages backup/recovery options for you and keeps track of required backups and created backup files and backup sets. Can be used for incremental backups. Backup and recovery may be faster due to its ability to read/write files in parallel.
Disadvantages: Another utility to master. Adds a layer of complexity to Oracle backup and recovery operations.
How To Translate This Into Business Terms
We've looked at the options for Oracle backup and recovery and seen that they all have disadvantages so it's really up to you as the dba to pick the best option depending on business requirements and constraints (money/resources) and the consequences (costs) of not meeting the requirements.Another thing to consider is that the best option may not be to bother backing up the database at all! You might consider this the best option for a development database and devolve responsibility to the developers to keep copies of their source code and test data. This might also be the best option for a datawarehouse if it would take longer to recover the data from backup then it would to reload the data from the source system and re-process it. On the other hand for an ecommerce database with 24x7 operation, you will need to use hot backups as a minimum and you would probably use Oracle RAC and Oracle Dataguard as well. One very important point to remember is that whichever option you pick, as mentioned earlier, make sure you test everything - test the whole process from end to end, the backup and the recovery. Ensure your backups have actually been created and are readable and that you have everything you need should a failure occur. This may sound obvious but many companies have lost valuable data and spent thousands of dollars and many hundreds of man-hours extra because either the backup failed and nobody noticed or not all of the data was available when recovery was required. So make sure you test frequently and thoroughly, then when the inevitable problems occur you'll have an excellent chance of averting a disaster. Click here for more on Oracle disaster recovery. Need Help with Oracle Backup and Recovery?Take advantage of our consultants' skills honed to a fine edge over many years with myriad clients in a wide variety of industries to maximise availability and minimise costs.
Ask us how we can help.
Return from Oracle backup and recovery to home page
|
