My Journey as Oracle DBA+DevOps Engineer: RMAN - Database Backup

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

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