Monday , September 25 2017
Home / Oracle DBA / ORACLE BACKUP & RECOVERY

ORACLE BACKUP & RECOVERY

ORACLE 11G BACKUP & RECOVERY

COLD BACKUP

  • Backup is a copy of original data which will be used to recover databases.
  • If the data is reproducable and backup not existing, still we can recover the data. But it is a tedious and time consuming task.
  • Taking backup after shutting down the database is called cold backup and because no transactions exist, the backup will be consistent.
  • In real time, we will perform cold backup very rarely.
STEPS to take cold backup
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;
SQL> shutdown immediate
[oracle@server1 ~]$ mkdir /u03/coldbkp
[oracle@server1 ~]$ cp /datafiles/prod/*.dbf /u03/coldbkp
[oracle@server1 ~]$ cp /datafiles/prod/*.log /u03/coldbkp
[oracle@server1 ~]$ cp /datafiles/prod/*.ctl /u03/coldbkp
[oracle@server1 ~]$ cp $ORACLE_HOME/dbs/*.ora /u03/coldbkp
[oracle@server1 ~]$ sqlplus “/ as sysdba”
SQL> startup
SQL> alter database backup controlfile to trace;
Note: archives are not required to take back up with cold backup.
HOT BACKUP
  1. Taking the backup while the database is up and running is called hot backup.
  2. During hot backup database will be in fuzzy state and still users can perform transactions which makes backup inconsistent.
  3. Whenever we place a tablespace or database in begin backup mode, following happens
    1. The corresponding datafiles header will be freezedi.e CKPT process will not update latest SCN
    2. Body of the datafile is still active i.eDBWRn will write the dirty blocks to datafiles
  4. After end backup, datafile header will be unfreezed and CKPT process will update latest SCN immediately by taking that information from controlfiles.
  5. During hot backup, we will observe much redo generated because oracle will copy entire data block as redo entry into LBC. This is to avoid fractured block.
  6. A block fracture occurs when a block is being read by the backup, and being written to at the same time by DBWR. Because the OS (usually) reads blocks at a different rate than Oracle, your OS copy will pull pieces of an Oracle block at a time. What if the OS copy pulls half a block, and while that is happening, the block is changed by DBWR? When the OS copy pulls the second half of the block it will result in mismatched halves, which Oracle would not know how to reconcile.
  7. This is also why the SCN of the datafile header does not change when a tablespace enters hot backup mode. The current SCNs are recorded in redo, but not in the datafile. This is to ensure that Oracle will always recover over the datafile contents with redo entries. When recovery occurs, the fractured datafile block will be replaced with a complete block from redo, making it whole again.
Note:  Database should be in archivelog mode to perform hot backup.
STEPS to take hot backup in 9i
[oracle@server1 ~]$ mkdir /u03/hotbkp
SQL> select name from v$datafile;
SQL> select name from v$controlfile;
SQL> alter tablespace system begin backup;
SQL> !cp /datafiles/prod/system01.dbf /u03/hotbkp
SQL> alter tablespace system end backup;
Repeat above steps for all the tablespaces in the database
SQL> !cp /datafiles/prod/*.ctl /u03/hotbkp
SQL>alter system switch logfile;
SQL> !cp /u03/archives/*.arc /u03/hotbkp/archbkp
Taking archive backup is the important step in hot backup
SQL> alter database backup controlfile to trace;
[oracle@server1 ~]$ cp $ORACLE_HOME/dbs/*.ora /u03/hotbkp
STEPS to take hot backup in 10g
[oracle@server1 ~]$ mkdir /u03/hotbkp
SQL> select name from v$datafile;
SQL> select name from v$controlfile;
SQL> alter database begin backup;
SQL> !cp /datafiles/prod/*.dbf /u03/hotbkp
SQL> alter database end backup;
Since we are placing entire database into begin backup mode, no repetition for all the tablespaces is required.
SQL> !cp /datafiles/prod/*.ctl.dbf /u03/hotbkp
SQL>alter system switch logfile;
SQL> !cp /u03/archives/*.arc /u03/hotbkp/archbkp
Taking archive backup is the important step in hot backup
SQL> alter database backup controlfile to trace;
[oracle@server1 ~]$ cp $ORACLE_HOME/dbs/*.ora /u03/hotbkp
Note: In any version, during hot backup we will not take redolog files backup.
Read More:
Oracle Materialized Views
Oracle Database Links
Oracle Distributed Database Management

Comments

comments

Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

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