• Arun Kumar

RMAN Incremental Backup & Recovery

Trigger RMAN Incremental Backup


RMAN> backup incremental level 0 database format='/ora_backup/proddb/proddb_bkp_L0_%U';


RMAN> backup incremental level 1 database format='/ora_backup/proddb/proddb_bkp_L1_%U';

RMAN Incremental Backup with Channels


RMAN> run{
allocate channel ch1 device type disk format='/ora_backup/proddb/proddb_bkp_L0_%U';
allocate channel ch2 device type disk format='/ora_backup/proddb/proddb_bkp_L0_%U';
allocate channel ch3 device type disk format='/ora_backup/proddb/proddb_bkp_L0_%U';
backup incremental level 0 database;
release channel ch1;
release channel ch2;
release channel ch3;
}

Checking DB Backups


RMAN> list backup of database summary;
RMAN> list backup tag;

You can check DB backup details via below SQL PLUS command as well


SQL> set linesize 500
col BACKUP_SIZE for a20
SELECT
INPUT_TYPE "BACKUP_TYPE",
--NVL(INPUT_BYTES/(1024*1024),0)"INPUT_BYTES(MB)",
--NVL(OUTPUT_BYTES/(1024*1024),0) "OUTPUT_BYTES(MB)",
STATUS,
TO_CHAR(START_TIME,'MM/DD/YYYY:hh24:mi:ss') as START_TIME,
TO_CHAR(END_TIME,'MM/DD/YYYY:hh24:mi:ss') as END_TIME,
TRUNC((ELAPSED_SECONDS/60),2) "ELAPSED_TIME(Min)",
--ROUND(COMPRESSION_RATIO,3)"COMPRESSION_RATIO",
--ROUND(INPUT_BYTES_PER_SEC/(1024*1024),2) "INPUT_BYTES_PER_SEC(MB)",
--ROUND(OUTPUT_BYTES_PER_SEC/(1024*1024),2) "OUTPUT_BYTES_PER_SEC(MB)",
--INPUT_BYTES_DISPLAY "INPUT_BYTES_DISPLAY",
OUTPUT_BYTES_DISPLAY "BACKUP_SIZE",
OUTPUT_DEVICE_TYPE "OUTPUT_DEVICE"
--INPUT_BYTES_PER_SEC_DISPLAY "INPUT_BYTES_PER_SEC_DIS",
--OUTPUT_BYTES_PER_SEC_DISPLAY "OUTPUT_BYTES_PER_SEC_DIS"
FROM V$RMAN_BACKUP_JOB_DETAILS
where start_time > SYSDATE -10
and INPUT_TYPE != 'ARCHIVELOG'
ORDER BY END_TIME DESC
/

RMAN Incremental Recovery


Trigger DB L0 Backup


Connect to the target DB and catalog

Take level 0 backup


RMAN> backup incremental level 0 database plus archivelog;

Once backup is completed, check backup tag via below command:


RMAN> list backup of database summary;

TAG20170115T113749 –> L0 Backup tag


Create New User & Table


SQL> create user ogr identified by ogr;
SQL> grant connect, resource, create session to ogr;
SQL> conn ogr/ogr
SQL> create table test(serial number(2),name varchar2(5));
SQL> insert into test values(1,'one');
SQL> insert into test values(2,'Two');
SQL> insert into test values(3,'Three');
SQL> insert into test values(4,'Four');
SQL> commit;

Trigger DB L1 Backup


Connect to the target DB and catalog

Take level 1 backup


RMAN> backup incremental level 1 database plus archivelog;

Once backup is completed, check backup tag via below command:


RMAN> list backup of database summary;

TAG20170115T114127 –> Level 1 Backup tag


Simulate Failure


Delete all the datafiles from server. Connect as sysdba and


SQL> select name from v$datafile;

rm -rf <DF locations>

Start DB Recovery


Kill the DB instance, if running. You can do shut abort or kill pmon at OS level

Start the DB instance and take it to Mount stage

Connect to RMAN and issue below command:


RUN
{
RESTORE DATABASE from tag TAG20170115T113749;
RECOVER DATABASE from tag TAG20170115T114127;
RECOVER DATABASE;
sql 'ALTER DATABASE OPEN';
}


420 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir