Oracle

Oracle RMAN Recovery manager-13

Advertisements

RMAN- Recovery manager

It’s a utility and an oracle internal tool.

Keyword — rman

Features of RMAN

RMAN only backup the used block, incremental backup with block change tracking and optimization on archivelog. Whereas hot & cold backup.  Less space used.

From 10g onwards, You can backup used +free data by rman. It’s called image copy.

Used data backup is called backupset backup. Default one.

All data backup — Image copy

Used backup–Backupset

By default, We can compress the currently not used & unused block compression to skip datafile blocks

RMAN also supports binary compression of backup sets. The supported algorithms are BZIP2 (default) – max compression and ZLIB – CPU efficiency 11.0.0 or higher ad requires the Oracle Advanced Compression option. .. It compresses almost 83%.

RMAN has catalog option. It can have more retention.

RMAN supports backup encryption for backup sets.

By default a backup set contains one backup piece. You can use MAXPIECESIZE to create multiple pieces.

We can use Multiplexed Backup Sets, RMAN read more datafiles simultaneously. It determined by several factors

  1. FILESPERSET default is 64 – Number of files in each backup set 2.MAXOPENFILES default is 8 – The level of multiplexing.

This will determine how many data files need to simultaneously writes into disk/channel into each backup piece.

RMAN can also manage user/outside created image copy. You can use the CATALOG command to enter the details of image copy metadata into the RMAN repository.

RMAN supports Duplexed Backup Sets. Like mirroring

You can also backup “Backups of Backups” existing backup sets and image copies to spread backups among multiple media.

RMAN has an Autobackups of Control File and Server Parameter File.

We can also, do an incremental backup, whereas hot & cold bakup.

By default, RMAN makes full backups. Incremental backup copies only those data blocks that have changed since a previous backup.

Level 0 & 1, 0 is a full backup but part of incremental backup strategy and level 1 is incremental – changed blocks .

The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.

A full backup

backup database;

A level 1 incremental backup can be either of the following types:

  • A differential incremental backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0. Incremental backups are differential by default. – Like, a SQL transaction log backup

BACKUP INCREMENTAL LEVEL 1 DATABASE;

  • A cumulative incremental backup, which backs up all blocks changed after the most recent incremental backup at level 0 – Like, a SQL differential backup

BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; #blocks changed since level 0

 

It has a block level recovery.

It is an automated backup & recovery, No manual interaction required. Like, it will backup to the default location and restore it from the file system.

Overview & Difference

RMAN Hot Cold
RMAN only backup the used block. All Data All Data
10g Features All versions All versions
Backup compression no no
Auto control & parameter  file Only control file No
Incremental backup no no
block level recovery possible no no
manual interaction not required Need a manual copy/paste Need a manual copy/paste
Catalog DB option with  having long retention No No
not require extra logging or backup mode Begin backup+ extra logging
Online (in consistent) & mount (consistent) Online  / partial / inconsistent backup Offline / consistent backup
Physical file + Tablespace Tablespace, control file All physical file
Archivelog backup Archivelog backup Archivelog backup
No archivelog & Archivelog Archive mode only No archivelog & Archivelog

 

Backupset — It’s a backup of all physical database files. It has one or more backup pieces.  Default one.

Backup pieces– It has one or more binary files.

Image copy — a bit-for-bit copy of a single datafile, archived redo log file, or control file.

RMAN Channel — It will read & write  a data from DB and disk.

 

Default backup location

Version Linux Winodows
9i $ORACLE_HOME/dbs %$ORACLE_HOME%\database
10g & 11g $ORACLE_BASE/Flash_recovery_Area %$ORACLE_BASE%\Flash_recovery_Area

 

Note: We can also change the backup location. RMAN only backup used blocks default and logically it stores in backupset in a backup pieces.  Unlike a cold & hot backup.

How many file does it store for each backup

Backup combination No.of files Backupset  name
Datafile + Control file one Backupset1
Archivelog file + Control file one Backupset1
Datafile + Archivelog file Two Backupset1, Backupset2

 

What can be backup by RMAN

 

Full DB
Tablespace
Data file
Archive log file
Control file
Spfile

 

Other Third party tools for backups

Product name Corporation
Veritas NetBackup SUN
Symantec NetBackup Symantec
Tivoli IBM
Avamar EMC
Netmanager EMC
SMO Netapp

Every tool, RMAN scripts only runs behind the screen.

We can take backup in both modes. Differences

Archive log No Archivelog
Backup on mount & open Backup on mount
Rman hot backup  Rman cold backup (Mount)

 

RMAN Backup and recovery in NO Archivelog mode

Sql> startup mount

$ export ORACLE_HOME =Muthudb location

$ rman target / – (/ means sys/sys)

Rman> backup database; – Only backup used data by default

Rman> backup tablespace tools;

Rman> backup datafile 4 or ‘path’; — data file No or full path

Rman> backup current controlfile;

Rman> backup spfile;

 

Only for Archivelog mode

Rman> backup archivelog all;

Rman> backup archivelog all delete input; — delete input will delete the files from physical location, after a backup.

Rman> backup databse plus archivelog; — Full DB+ archivelog

Rman> backup archivelog sequence 21; — Only for particular archive log file

Rman> backup archivelog sequence between 21 and 35; — Only for particular archive log file

 

Image copy Backup:

Rman> backup as copy database;

Rman> backup as copy tablespace tools;

Rman> backup as copy datafile 4 or path;

Compressed Backup:

Rman> backup as compressed backupset database;

Rman> backup as compressed backupset tablespace tools;

Rman> backup as compressed backupset datafile 4 or ‘path’;

 

Recovery:

Tablespace:

Rman> sql ‘alter tablespace tools offline’;

Rman>restore tablespace tools;

Rman>recover tablespace tools;

Rman> sql ‘alter tablespace tools online’;

 

Datafile:

Rman> sql ‘alter database datafile 4 or path drop offline’;

Rman>restore datafile 4;

Rman>recover datafile 4;

Rman> sql ‘alter database datafile 4 online’;

 

Scenario:

We have backup on Snday with 500 emp table + mon to wed archivelog backup + on wednedes day DB crashed with 1500 emp table data.

Rman> sql ‘alter database datafile 4 drop offline’;

Rman>restore datafile 4; (500 dat will come)

Rman>restore archivelog sequence between 21 and 52 /all;

Rman>recover datafile 4; (It will recover upto DB crash, Only archivelog restore will take all data)

Roman> sql ‘alter database datafile 4 online’;

 

Note: The recovery will fail, in case of archive log deletion. Since we have deleted archive on Wednesday.

If we don’t know log sequence numbers, Restore archivelog all; but it will fill up the space. In RMAN, it will suggest the sequence numbers, you can get the starting number from RMAN and end number by looking physical location.

For 11g:

No need,  Rman>restore archivelog sequence between 21 and 52 /all;  Oracle will go  and check the physical path  and will restore the  file if available, if not it will restore it from backup and recover the database. After that, it will delete the restored archives from the file system.

 

Same Scenario for Control file:

Control file can be restored only in nomonut

 

Rman>startup nomount;

Rman>restore controlfile;  — If it’s not working

Rman> restore controlfile from backup ‘/backup.controlfile01.bkp;  Get  the location from backupset

Rman> sql ‘alter database mount’;

Rman>recover database;

The Sunday backup has old SCN, SO we need to recover the database, to get the latest SCN.

Rman> sql ‘alter database open’; (OR)

Rman>open database;

 

Whole database Recovery:

We lost: Control file, data file & log files

 

Rman>startup nomount;

Rman>restore controlfile;  — If it’s not working

Rman> restore controlfile from backup ‘/backup.controlfile01.bkp; Get  the location from backupset

Rman> sql ‘alter database mount’;

Rman>restore database;

Rman>recover database;

Rman> sql ‘alter database open’; (OR)

 

SPfile lost

Rman>restore spfile;

 

Block level recovery

Rman> blockrecover datafile 4 or ‘path’ block 8130;

How to find the corrupted block number: By reading an alertlog. You can see corrupted datafile 4 block 8130.

Catalog DB – RMAN Backup and recovery in Archivelog mode

In general, RMAN backup will have a catalog database. It’s an optional one. For a catalog option we need two databases.

Use and difference of catalog

Target database Catalog Database
It’s an original database It will store a metadata info of target DB (Control file, Backup info of backupset, backup piece)
We can only backup target Optional one
Backup info will store in control file , if no catalog Stored in catalog repository
Default 7 days of backup info only stored Unlimited
Without catalog only  last 7 days data can be recovered Unlimited years, until we delete backup physically

 

Pre-requirements of catalog database configuration

Login to catalog database

$ export ORACLE_SID = test

Sql> create tablespace tbs_catalog datafile ‘\opt\oracle\oradata\ tbs_catalog.dbf’ size 500m; minimum 60mb

Sql> create user rmancat identified by rmancat default tablespace tbs_catalog quota unlimited on tbs_catalog;

Sql> grant recovery_catalog_owner to rmancat

 

Create TNS entry of the target DB

$ export ORACLE_SID = Muthu

$ rman target  / catalog rmancat/rmancat@test

Test : Net service name of Muthu DB. ‘/’ is a sys/password

rman> create catalog; — Space will be allocated in the catalog tablespace.

rman> register database; — It will store/write info in the catalog DB.

Note: The two commands are used only one time for registration

rman> resync catalog; — We need to do this frequently to store the current info of target DB.

 

Everything will be same as normal RMAN backup & store, after done the configuration.

 

Incremental Backup

Types: 1. Differential (n,n-1,<n-1)2. Cumulative   (n-1,<n-1)

Backup modes – Level 0,1,2

Level 0 is full

Level 1 is either differential or cumulative.

Level 0 is mandatary to start the incremental backup. Even, if we take a level 1,2 without level 0, default oracle will run and take level 0 only.

Differential Incremental backup:

 

Differential backup will check the condition up to level 0 and if it success it will take a backup.

rman> backup incremental level 0 database;

rman> backup incremental level 1 database;

rman> backup incremental level 2 database;

Cumulative Incremental backup:

rman> backup incremental level 0 cumulative database;

rman> backup incremental level 1 cumulative database;

rman> backup incremental level 2 cumulative database;

 

10 g feature – Block change tracking

It will track changed blocks and will improve the incremental backup speed.

How to enable block change tracking

Sql> select * from v$block_change_tracking

Sql> show parameter  db_create_file_dest;

By default it will show Oracle Base location, if not we can to set to any location.

Sql> Alter system set  db_create_file_dest =’/opt/oracle’;

Sql> Alter database enable block change tracking;

It will create a binary file in the path. It will have a changed blocks info and incremental backup will take an advantage of this.

Real time: 2 TB DB –full backup 15 hours –incremental 7 hours –after tracking 1.2 Hours

Change the block tracking to a non-default location.

Sql> Alter database enable block change tracking ‘/data/tracking01.bin’ reuse;

Reuse – Incase if it’s disabled, reuse will use the same file after an enable.

Sql> Alter database disable block change tracking;

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: