RMAN - Incremental Backup - Beginners Guide (Part 2)

§  Incremental backups capture the block level changes (Block being changed/used) from the previous incremental backup (either Level 0 – Cumulative Incremental Backup or Level 1 – Differential Incremental Backup)

§  Command to use for Incremental backup in RMAN prompt – BACKUP INCREMENTAL

§  Backup Levels in Backup Strategy –

Level 0 Incremental Backup: A full database backup which is considered as starting point in the backup strategy

Level 1 Incremental Backup: A backup taken after the Level 0 backup and has only the blocks changes since the level 0 backup. If no level 0 backup exists in when we run a level 1 backup, then RMAN makes a level 0 backup automatically.

Note:

Incremental backups are allowed for both Cold (Consistent) & Hot (In-Consistent) backups.

But for Cold (NonArchiveLog database), incremental backup is possible only the database is in mounted state after a consistent shutdown

Types of backups in Level 1 Incremental Backup:

Differential Incremental Backups:

In a differential level 1 backup, RMAN backs up all blocks that have changed since the most recent incremental backup at level 1 (cumulative or differential) or level 0. For example, in a differential level 1 backup, RMAN determines which level 1 backup occurred most recently and backs up all blocks modified after that backup. If no level 1 is available, then RMAN copies all blocks changed since the base level 0 backup.

Incremental backups are differential by default (one need to mention CUMULATIVE keyword in BACKUP command to have a Level 1 cumulative backup, else RMAN considers it as Differential level 1 backup)

Cumulative backups are preferable to differential backups when recovery time is more important than disk space, because fewer incremental backups must be applied during recovery.

To make incremental backups of the database:

    1. BACKUP INCREMENTAL LEVEL 0 DATABASE; - Full backup which serves as base/starting point for incremental backup strategy

RMAN> backup incremental level 0 database;

Starting backup at 27-OCT-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

channel ORA_DISK_1: starting piece 1 at 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd0_TAG20131027T095528_96s5fxl3_.bkp tag=TAG20131027T095528 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn0_TAG20131027T095528_96s5gvf9_.bkp tag=TAG20131027T095528 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

Finished backup at 27-OCT-13


2. BACKUP INCREMENTAL LEVEL 1 DATABASE; -  This command creates the level 1 differential incremental backup (as default RMAN creates the differential unless the cumulative keyword is mentioned)

RMAN> backup incremental level 1 database;

Starting backup at 27-OCT-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

channel ORA_DISK_1: starting piece 1 at 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd1_TAG20131027T100718_96s643f8_.bkp tag=TAG20131027T100718 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn1_TAG20131027T100718_96s64hs3_.bkp tag=TAG20131027T100718 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

Finished backup at 27-OCT-13

3. BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; - This command creates the level 1 cumulative incremental backup

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; 

Starting backup at 27-OCT-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

channel ORA_DISK_1: starting piece 1 at 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd1_TAG20131027T101742_96s6qjxg_.bkp tag=TAG20131027T101742 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn1_TAG20131027T101742_96s6qyqx_.bkp tag=TAG20131027T101742 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

Finished backup at 27-OCT-13

To displayed the details of the backups use below command, look for Ckp SCN which useful to identify whether a particular datafile is backed up using Level 1 Differential or Cummulative backup using the below sql statement(Details with example will upload in other post)

select file#,incremental_level,incremental_change#,checkpoint_change# , checkpoint_time
from v$backup_datafile
order by 1,4;

RMAN> list backup summary;

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- ---

24      B  0  A DISK        27-OCT-13       1       1       NO         TAG20131027T095528

25      B  0  A DISK        27-OCT-13       1       1       NO         TAG20131027T095528

26      B  1  A DISK        27-OCT-13       1       1       NO         TAG20131027T100718

27      B  1  A DISK        27-OCT-13       1       1       NO         TAG20131027T100718

28      B  1  A DISK        27-OCT-13       1       1       NO         TAG20131027T101742

29      B  1  A DISK        27-OCT-13       1       1       NO         TAG20131027T101742

RMAN> list backup;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

24      Incr 0  490.35M    DISK        00:00:20     27-OCT-13     

        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T095528

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd0_TAG20131027T095528_96s5fxl3_.bkp

  List of Datafiles in backup set 24

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  1    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf

  2    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf

  3    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf

  4    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf

  5    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

  6    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

  8    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

25      Incr 0  8.36M      DISK        00:00:09     27-OCT-13     

        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T095528

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn0_TAG20131027T095528_96s5gvf9_.bkp

  SPFILE Included: Modification time: 27-OCT-13

  SPFILE db_unique_name: MYDB

  Control File Included: Ckp SCN: 792803       Ckp time: 27-OCT-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

26      Incr 1  336.00K    DISK        00:00:05     27-OCT-13     

        BP Key: 26   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T100718

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd1_TAG20131027T100718_96s643f8_.bkp

  List of Datafiles in backup set 26

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  1    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf

  2    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf

  3    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf

  4    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf

  5    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

  6    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

  8    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

27      Incr 1  8.36M      DISK        00:00:09     27-OCT-13     

        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T100718

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn1_TAG20131027T100718_96s64hs3_.bkp

  SPFILE Included: Modification time: 27-OCT-13

  SPFILE db_unique_name: MYDB

  Control File Included: Ckp SCN: 793091       Ckp time: 27-OCT-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

28      Incr 1  520.00K    DISK        00:00:04     27-OCT-13     

        BP Key: 28   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T101742

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd1_TAG20131027T101742_96s6qjxg_.bkp

  List of Datafiles in backup set 28

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  1    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf

  2    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf

  3    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf

  4    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf

  5    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

  6    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

  8    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

29      Incr 1  8.36M      DISK        00:00:08     27-OCT-13     

        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T101742

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn1_TAG20131027T101742_96s6qyqx_.bkp

  SPFILE Included: Modification time: 27-OCT-13

  SPFILE db_unique_name: MYDB

  Control File Included: Ckp SCN: 793374       Ckp time: 27-OCT-13

List of useful data dictionary view related to RMAN backups

SQL> select table_name from dict where table_name like '%BACKUP%';

TABLE_NAME

--------------------------------------------------------------------------------

V$RMAN_BACKUP_JOB_DETAILS

V$RMAN_BACKUP_SUBJOB_DETAILS

V$RMAN_BACKUP_TYPE

GV$BACKUP

GV$BACKUP_ASYNC_IO

GV$BACKUP_CORRUPTION

GV$BACKUP_DATAFILE

GV$BACKUP_DEVICE

GV$BACKUP_NONLOGGED

GV$BACKUP_PIECE

GV$BACKUP_REDOLOG

GV$BACKUP_SET

GV$BACKUP_SPFILE

GV$BACKUP_SYNC_IO

V$BACKUP

V$BACKUP_ARCHIVELOG_DETAILS

V$BACKUP_ARCHIVELOG_SUMMARY

V$BACKUP_ASYNC_IO

V$BACKUP_CONTROLFILE_DETAILS

V$BACKUP_CONTROLFILE_SUMMARY

V$BACKUP_COPY_DETAILS

V$BACKUP_COPY_SUMMARY

V$BACKUP_CORRUPTION

V$BACKUP_DATAFILE

V$BACKUP_DATAFILE_DETAILS

V$BACKUP_DATAFILE_SUMMARY

V$BACKUP_DEVICE

V$BACKUP_FILES

V$BACKUP_NONLOGGED

V$BACKUP_PIECE

V$BACKUP_PIECE_DETAILS

V$BACKUP_REDOLOG

V$BACKUP_SET

V$BACKUP_SET_DETAILS

V$BACKUP_SET_SUMMARY

V$BACKUP_SPFILE

V$BACKUP_SPFILE_DETAILS

V$BACKUP_SPFILE_SUMMARY

V$BACKUP_SYNC_IO

V$UNUSABLE_BACKUPFILE_DETAILS

40 rows selected.

SQL> select Table_name from dict where table_name like '%RMAN%';

TABLE_NAME

--------------------------------------------------------------------------------

V$RMAN_BACKUP_JOB_DETAILS

V$RMAN_BACKUP_SUBJOB_DETAILS

V$RMAN_BACKUP_TYPE

V$RMAN_COMPRESSION_ALGORITHM

V$RMAN_CONFIGURATION

V$RMAN_ENCRYPTION_ALGORITHMS

V$RMAN_OUTPUT

V$RMAN_STATUS

GV$RMAN_COMPRESSION_ALGORITHM

GV$RMAN_CONFIGURATION

GV$RMAN_OUTPUT

11 rows selected.

RMAN - Database Backup - Beginner's Guide(Part 1)

RMAN (Recovery Manager) is an Oracle database client which basically performs backup & recovery tasks on database and automates the backup strategies administration.

        RMAN environment 

RMAN Environment has below components

-   Target Database - To which RMAN connects to perform backup, restore & recovery operations. RMAN uses the database details present in target database control files (location of redo log/data file location) and to store the metadata of its own (RMAN)

-        RMAN Client – Installed at the time of oracle database installation. Used for executing the backup, restore & recovery commands. Located in $ORACLE_HOME/bin

-  Recovery Catalog – Metadata about the RMAN operations stored in tables/views of a database.

-    Recovery Catalog Database – This is a separate oracle database(not the target database) in which RMAN stores its backup & recovery metadata in recovery catalog schema – tables/views.

Primarily the RMAN metadata is captured in control files of the database, which then moves to Recovery Catalog tables so that in case of control file corrupts/lost, recovery catalog is used for recovery operations.

-     Fast Recovery Area – This is a part of a physical disk location which can be used by RMAN to store the files (Control Files, ORLF(online redo log files), RMAN backups, Archived Redo Logs) which are used for recovery operations.

-    Media Manager - A vendor-specific application that enables RMAN to back up to a storage system such as tape.

Below is the RMAN Architecture(Courtesy: Oracle Docs) having components like – Media Manager/Management subsystem for tape backup, Recovery Catalog database (separate database), RMAN Client & OEM installed and running in a separate system.

But at the very basic architecture level for RMAN backup & restore it requires – Target Database & RMAN Client

RMAN Channels

-      RMAN performs the backup and recovery operations by using the Server sessions. RMAN client just triggers the command to server sessions on the target database instance and they take up the backup & recovery operations on behalf of RMAN client.

-      RMAN channel represents one stream of data to a device (Disk or Tape) which can be configured as Automatic or Manual.

-      RMAN channel (Server Session) reads the data into PGA memory, processes it & then writes it to device(disk or tape)

RMAN in Action now:

Recovery Manager automatically requests a connection to the target database as SYSDBA.  If the target database does not have a password file, the user you are logged in as must be validated using Operating system authentication

Will let you know how to use password file to connect to RMAN as SYSDBA or any other user later in other post. For now we will connect to RMAN using OS authentication method as below.

Connecting to Target Database in RMAN:

[oracle@ol6-12c ~]$ rman

Recovery Manager: Release 12.1.0.1.0 - Production on Fri Oct 25 08:40:19 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: MYDB (DBID=2743095732)

DBID is the value which is listed in the v$database view

SQL> select name, DBID from v$database;

NAME                   DBID

---------                ----------

MYDB                 2743095732

RMAN Configuration details:

RMAN client ships with a default pre-configurations set on the target database which are adequate to perform the basic level of back & recovery operations on the database.

Based on the requirements we need, the configurations can be changed using the CONFIGURE command.

To show the list of current configurations set by RMAN on target database – Show All command will display the details

Note:

Unlike SQLPLUS prompts wherein few commands doesn’t require to be ended with ; (Show user, describe <table or view> etc.,) all command in RMAN need to be ended with ;

And to execute the OS commands from the RMAN prompt –try    host ‘<os command>’;

RMAN> host 'pwd';

/home/oracle

host command complete

RMAN> show all;

RMAN configuration parameters for database with db_unique_name MYDB are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/db_1/dbs/snapcf_MyDB.f'; # default

To get the configuration details of only specific RMAN parameter using SHOW <parameter name>

RMAN> show BACKUP OPTIMIZATION;

RMAN configuration parameters for database with db_unique_name MYDB are:

CONFIGURE BACKUP OPTIMIZATION OFF; # default

To set the configuration parameter to default value use – CONFIGURE <parameter name> CLEAR

RMAN> CONFIGURE BACKUP OPTIMIZATION Clear;

old RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION OFF;

RMAN configuration parameters are successfully reset to default value

Note:

Unlike SQLPLUS prompts wherein few commands doesn’t require to be ended with ; (Show user, describe <table or view> etc.,) all command in RMAN need to be ended with ;

And to execute the OS commands from the RMAN prompt –try    host ‘<os command>’;

RMAN> host 'pwd';

/home/oracle

host command complete

Database Backup Using RMAN:

In database backup we have types of backups –

1.       Consistent (Cold) Backup – Database backup taken after shutting down the database.

2.       In-Consistent (Hot) Backup – Database backup taken when the database is Open and connected by client applications. Database should be in Archive Log enabled mode.

Consistent (Cold) Backup:

For this type of database backup, the database should be shutdown and started up in MOUNT state and then execute the RMAN BACKUP command to backup the database.

Note:

It is always advisable to configure the Flash Recovery Area (Physical Disk location where in archive redo log files, control file backup, RMAN backup sets will be located) by setting db_recovery_file_dest initiazation parameter.

Else RMAN keep the backup sets in $ORACLE_HOME/dbs folder.

1.       Set the FRA location in initialization file

SQL> alter system set db_recovery_file_dest = '/u01/app/oracle/MyDB_fast_recovery_area' scope=both;

System altered.

SQL> show parameter db_recovery_file_dest

NAME                                                        TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                      string               /u01/app/oracle/MyDB_fast_recovery_area

db_recovery_file_dest_size           big integer 2G

2.       Shut down the database using immediate (You can use abort as well. We need basically the database should be in mount state for performing cold backups).

RMAN> shutdown immediate;

database dismounted

Oracle instance shut down

In here I am using FORCE DBA clause in Startup to make sure that there are no connections to database from other users or databases (Mine is test environment. Showing you this that in real prod environment it is better to execute FORCE DBA before we take up the cold backup)

RMAN> startup force dba;

Oracle instance started

database mounted

database opened

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                687867136 bytes

Database Buffers             373293056 bytes

Redo Buffers                   5480448 bytes

RMAN> shutdown immediate;

database closed

database dismounted

Oracle instance shut down

3.       Start the database in mount mode

RMAN> startup mount;

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                687867136 bytes

Database Buffers             373293056 bytes

Redo Buffers                   5480448 bytes

4.       Performing the full database backup

RMAN> backup database;

Starting backup at 26-OCT-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=201 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

channel ORA_DISK_1: starting piece 1 at 26-OCT-13

channel ORA_DISK_1: finished piece 1 at 26-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_26/o1_mf_nnndf_TAG20131026T122913_96pt235p_.bkp tag=TAG20131026T122913 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 26-OCT-13

channel ORA_DISK_1: finished piece 1 at 26-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_26/o1_mf_ncsnf_TAG20131026T122913_96pt349k_.bkp tag=TAG20131026T122913 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

Finished backup at 26-OCT-13

In-Consistent (Hot) Backup

To perform the hot backup when the database is in open mode, ARCHIVELOG mode should be enabled (from sqlplus prompt)

1.       Archive Log mode enabling – set the Archive log destination to Init parameter - log_archive_dest_1

SQL> ARCHIVE LOG LIST;

Database log mode        No Archive Mode

Automatic archival         Disabled

Archive destination                       USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     44

Current log sequence                   46

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/ArchiveLogDest';

System altered.

Shutdown the instance and startup in mount mode. Enable the ArchiveLog mode and Open the database

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                       2296576 bytes

Variable Size                               687867136 bytes

Database Buffers       373293056 bytes

Redo Buffers                                 5480448 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode        Archive Mode

Automatic archival         Enabled

Archive destination                       /u01/app/oracle/ArchiveLogDest

Oldest online log sequence     44

Next log sequence to archive   46

Current log sequence                   46

2.       Backup the Database

RMAN> backup database;

Starting backup at 26-OCT-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=158 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

channel ORA_DISK_1: starting piece 1 at 26-OCT-13

channel ORA_DISK_1: finished piece 1 at 26-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_26/o1_mf_nnndf_TAG20131026T204313_96qq0gmd_.bkp tag=TAG20131026T204313 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 26-OCT-13

channel ORA_DISK_1: finished piece 1 at 26-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_26/o1_mf_ncsnf_TAG20131026T204313_96qq1d09_.bkp tag=TAG20131026T204313 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

Finished backup at 26-OCT-13