OCM 11g Tips and Tricks – Creating Incrementally updated backups

To speed up the recovery process during a failure, image copy backups can be updated with daily incremental backups. By taking an incremental backup, you recover it over the image copy backups. And when database is crashed, you will only need to restore the incrementally updated backup and apply changes from the redo log files. The restored backup will already contain all incremental backups applied.

Let’s try the following scenario to show how it works and make sure we can use and implement incrementally updated backup/recovery during OCM exam:

– Create an Incrementally updated backup

– Add new tablespace with new datafile, create table on that tablespace

– Take an incrementally updated backup again

– Delete the datafile and query the table

– Restore a datafile from the backup

 

First of all, enable block change tracking as follows:

 

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/tmp/blk_change.trc’;

 

Next, connect to RMAN and take an incrementally updated backup:

 

RMAN> RUN {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘mydb_incr_backup’ DATABASE;
RECOVER COPY OF DATABASE WITH TAG ‘mydb_incr_backup’;
}

 

This script will take a LEVEL 1 image backup of the database. Now add new tablespace, create a table and query it:

 

SQL> CREATE TABLESPACE mytbs01 DATAFILE ‘/home/oracle/oradata/mytbs03.dbf’;
SQL> CREATE TABLE tbl_test01 (id NUMBER, name VARCHAR2(10)) TABLESPACE mytbs01;
SQL> INSERT INTO tbl_test01 VALUES(1, ‘oracle’);
SQL> COMMIT;
SQL> SELECT * FROM tbl_test01;
ID NAME
---------- ----------
1          oracle

 

Run the same backup command again. This time it will take an incremental backup and recover it over the image copy backup of the database:

 

RMAN> RUN {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘mydb_incr_backup’ DATABASE;
RECOVER COPY OF DATABASE WITH TAG ‘mydb_incr_backup’;
}

 

Remove the datafile that you’ve already created, flush the buffer and query the table:

 

[oracle@ocm11g oradata] mv mytbs01.dbf mytbs01.dbf_backup
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> SELECT * FROM tbl_test01;
SELECT *FROM tbl_test01
*

ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: ‘/home/oracle/oradata/mytbs01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

 

As we have incrementally updated backup, we can restore it using SWITCH DATAFILE … TO COPY command as follows:

 

RMAN> LIST COPY OF DATAFILE 5;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time

——- —- – ————— ———- —————

21 5 A 24-OCT-14 1458646 24-OCT-14
Name: /home/oracle/flash_recovery_area/OCM_NEW/datafile/o1_mf_mytbs01_b4mqo4k5_.dbf
Tag: MYDB_INCR_BACKUP

RMAN> SWITCH DATAFILE 5 TO COPY;
datafile 5 switched to datafile copy “/home/oracle/flash_recovery_area/OCM_NEW/datafile/o1_mf_mytbs01_b4mqo4k5_.dbf”
RMAN>

 

Now recover the datafile and query the table:

 

RMAN> RECOVER DATAFILE 5;
SQL> SELECT * FROM tbl_test01;
ID NAME
---------- ----------
1          oracle

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>