Wednesday , July 26 2017
Home / Oracle DBA / Oracle database Hot Backups

Oracle database Hot Backups

WHY HOT BACKUPS?

In some companies because we can’t afford to bring the instance down.(e.g. Credit Card Companies, Stock Market or where we have 24 hours of DB operations) we have to make the DB available to end-users for 24 hours. At the same time we need to backup the database to protect our self from any type of disasters. So, Oracle is giving us an excellent option of backing up of DB even while it is up & running. Which is known as HOT-BACKUP. The recovery procedures are 100% alike in HOT-BKUP, when compared to that of COLD-BKUP. As we have done 4 types of recovery scenarios:

      A. Media Recovery (when we have lost everything and trying to build the most-possible from LN- Bkup and applying AL-Files An example for In-Complete Recovery)
      B. File Lost(performing Complete Recovery)
      C. Time-Based Recovery
      D. Getting back lost objects (g. Table dropped by mistakenly)

All the above recoveries that we have performed under Cold-Bkup are exactly identical in Hot-Bkup also. So basically the steps we take in Backing-up the DB is different, in HOT-BKUP compared to Cold-Bkup, but Recoveries are exactly alike. Rules to implement HOT-BKUP.

        1.The database must be in ALM.
        2.While we are doing HB, users can perform reads/write operations to DB since HB is transparent to users. So that it can be done at any point in time, even in Lunch hours. This operation may slow down the server’s response time.
        3.We do HB by TS(each on individually).
ALTER TABLESPACE USER_ORCL BEGIN BACKUP;

Now, at OS-Level, we have to take backup of all the files belonging to this Tablespace. We cannot take the backup of multiple files that are belonging to this TS in multiple shots. All files must be backed up.

ALTER TABLESPACE USER_ORCL END BACKUP;
              4.We do HB only for
                              i. Controlfile (not at OS Level, but at SQL Level)
                              ii. DBFs (datafiles at OS Level)

We”ll ignore REDO-LOG files, since these files are open while the HB is going on. Well, in case of DBFs, we are taking the TS in a special mode, where Oracle is letting you to backup the DBFs at OS Level. But Oracle generates excessive LOG. So, it is recommended that you try to keep each TS in backup mode as little time as possible.

      E. Controlfile cannot be backed up as it is since the DB is Up & Running. We do it by SQL (not at OS Level) after finishing with all Tablespaces.
ALTER DATABASE BACKUP CONTROLFILE TO ‘/w001/oradata/backup/hot/control.ctl’ ;
      F. If we go with HB, we can only do Restore + Recovery. Only Restore is not possible. This leads us to have at least one AF after finishing with HB. So at the end of the HB, we do
ALTER SYSTEM SWITCH LOGFILE;
      G. In point #4 we are talking about keeping each TS in backup-mode as little time as possible. This is not possible in case we try to write all the datafiles to the TAPE, which belong to this TS. So instead of writing to the TAPE (especially in case of HB), it is highly recommended that we write to another HD. Likewise, we write all the DBFs to this HD, and the end of the HB, we go to this HD and copy all files to TAPE in one shot. (Here we understand that writing to HD is much faster compared to TAPE).
      H. The recovery procedures are same (including syntax) compared to CB, excepting the diff, that in CB, simple Restore is possible, while in HB. We have to perform Restore+Recovery is demanding at least one archivelog file (after performing Bkup on all TSes and Controlfile. SR is not possible in HB).
Read More:
sql server dba interview questions from Unisys company
Control File Management
Redo Logfile Management

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

Your email address will not be published. Required fields are marked *