To protect the database against potential media failures. You must develop proper backup strategy to perform recovery if disk failure damages the datafiles, online redo log files or control files. To develop a proper backup strategy and plan you must decide the type of requirement first, off course to know the type of requirements you need to find the answer’s to the following questions. I hope this article will help to the fresher DBA who is recently join the company and working in independent mode.
If a media failure happens, is it acceptable to lose any transactions performed between the last backup and the time of failure?
If it is ‘NO’ then you must run the database in “ARCHIVE” mode otherwise you can run in “NOARCHIVE” mode.
Does the database need to be available 24 hours a day, 7 days a week?
If it is ‘YES’ you must run the database in ARCHIVE mode and possibly configure the standby feature to minimize the downtime of database otherwise you can run the database in noarchive mode and take consistent (which require the database to be shutdown) whole database backup.
Will the database ever need to be recovered to a past point in time other than the time a backup was taken?
If it is ‘YES’ you must run the database in ARCHIVE mode other wise you can run the database in Noarchive Mode.
Can you afford downtime to perform a cold backup?
If it is YES please do so generally for a large database the answer is most likely ‘NO’, However there are backup solution in the market that can backup high rate per hour but in fact it is expensive solution.
What is your Recovery Window?
The more data you have, the longer it will take to restore and recover the entire database. You might want to backup more often or employ incremental backups to reduce the recovery window.
Do you have Read Only data?
If YES, backup time can be reduced by skipping READ ONLY tablespace. These only need to be backed up once, after the tablespace is made READ ONLY.
What kind of archived redologs volume can you expect?
The more bytes of redo you generate, the more bytes of redo that you will have to apply in recovery. You can lessen this by backing up more often and/or employing incremental.
Are you required to store the backup in special location?
It is a good idea to have offsite storage for a copy of your backups. In that way, if your entire data center catches fire, you can go offsite to get your data. Many companies are starting to ensure that their data is stored offsite in some form
What kind of Backup solution do you need?
Where the backup eventually ends up can be a big concern. With larger databases, it gets harder to backup directly to a disk device. And with offsite concerns, one probably wants a copy of the backup on tape somewhere.
Strategy to Complete database Recovery:
Before making Recovery Plan we need to identify all the risks that our data center can face such as: Media recovery, loss or corrupted datafiles, Loss of OS, loss of a disk drive, complete loss of our server etc. Oracle provides number of recovery plan we need to select best one as per our business requirement.
The following combination of backup strategies with RMAN when planning to recover from complete database loss will provide the maximum flexibility for recovery of database. Using this strategy it is possible to recover specific point in time, loss of CRD files as well as spfile. In order to completely restore or recover the lost database all of these items must be available, or, you must have the knowledge required to rebuild them and at last you must maintain some rules of Backup and Recovery:
– The set of files (redundancy set) required for recovery any Oracle database are: datafiles, control file, or online redo log is called the.
- – Multiplex the online redo log files and current control file at OS or hardware level. Thus an I/O failure or lost write should only corrupt one of the copies.
- – Use OS or hardware mirroring for the primary datafiles if possible to avoid having to apply media recovery for simple disk failures.
- – Keep at least one copy of most recent backup on hard disk.
- – If your database is stored on a RAID device, then place the redundancy set on a set of devices that is not in the same RAID device.
- – If you keep backupset on tapes, then maintain at least two copies of the data because tapes can fail. If you are keeping the backupset on disk then keep additional copy on different server.
Note: The complete database consists of datafiles, controlfiles, redolog file along with spfile. You can recover the loss of table from Export backup.
RMAN full offline backup:
This is good solution for large database but it requires down time. It is good to deal the corrupt or missing datafiles. Need to restore only comparably easier, any data entered into the database after the last full backup will be lost. Usually it is taken weekly or monthly basis depending upon the size/space or requirement you have.
RMAN full online backup with incremental and cumulative:
This is most preferable method, RMAN does not require down time. Data can be recovered up to the last entry in the online redo logs or possibly restored or recovered up to the date and time of the last entry in the archive log file. You can take incremental and cumulative backup on daily basis and full backup on weekly or monthly basis. Here I am adding two very good script for rman backup on windows environment, which can take upto 120 GB size of database full backup within 25 minutes and daily incremental /cumulative backup within 35 minutes.
EXPORT full backup:
Full exports will allow recovery to the last full export date. Any data entered after the export will not be recoverable. The best source to recover the loss of tables and cross platform migration of database. To recover a database using a full export you must re-create the database and then import the full export into the new database.
Testing the Plan
It is highly recommended to test your plan before actual problem occurs. When your database failed is not good time to test your plan and strategy go through possible causing the types of failure (in Test Environment) you want to be able to recover from and make sure your backup strategy is working.
Benefit of Test Environment:
- It requires almost same amount of space as the database or off course same as offline backup.
- We can Test or assure our backup validity. In case of disaster some times we came to know our backup is not valid backup for recovery (minimizing risk).
- While taking offline backup we have to change the mode of DB (shutdown) each and every time. Test environment will minimize this risk. As all of application and database on the same server (DBF structure not well organized). It is not preferable to change mode of db each time. Suppose our schedule offline backup is terminated in middle for any abnormal power off then DB will remain in any other stats will not available for end user.
- We can Test major changing before applying on the production server.
- We can replace any corrupt DBF from Test environment DBF and recover the database till the failure using our regular RMAN online backup.
- We can synchronize our Test environment with the weekly online production database.
- We can Test our application with the Test environment by making sure we can use this server as a production server in case of emergency.
- In future we can convert this standby as a mirroring of Production DB. If we have alternative standby server then creating mirroring from prod server risk will minimize.
Data Center having some other complex or expensive solution to improve the data security or avoid disaster without any downtime such as the concept of “DATAGUARD” and “RAC”.
