Oracle Database Backup – We are AI Techno

Recovery Manager (RMAN) is an Oracle Database client that manages your backup plans automatically and handles backup and recovery operations on your databases. It makes recovering, restoring, and backing up database files a lot easier.

Download PDF

RMAN is available for both online and offline consistent backup.

Online Backup (Database should be in Archive Mode and Open State)

Offline Backup (Database Should be in Mount Mode)

Full Backup (Consistent Full Database Backup)

Incremental Backup (Consistent Full Database Backup for Incremental backup)

Below are the steps for Enabling RMAN backup and scripts for taking full or incremental backups.

Archive should be Enabled. (We have to Enable Archive mode in Database for online backup)
- mkdir /archives/orcl
- sqlplus / as sysdba
- alter system set log_archive_dest_1='location=/archives/orcl';
- shutdown immediate;
- startup mount;
- alter database archivelog;
- alter database open;
Define Parallelism in RMAN Setting.
- RMAN target /
- CONFIGURE DEVICE TYPE DISK PARALLELISM 10 BACKUP TYPE TO BACKUPSET;
Create Backup Directories (These Directories needs to create)
- mkdir /backup/backup
- mkdir /backup/scripts
- mkdir /backup/logs
Enable Auto Backup of Control File. (We have to Enable Auto backup of Control File)
- RMAN Target /
- CONFIGURE CONTROLFILE AUTOBACKUP ON;
- CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/backup/%F';
- Define Path of Auto Backup Control File.
Database Backup Types
- Full Database Backup (Non Incremental) (These Script for taking online Full Database backup)
  - rman target / LOG='/backup/logs/rman_backup_Level0.log' <<EOF
  - run{
  - BACKUP AS COMPRESSED BACKUPSET DATABASE format '/backup/backup/level0_db_%U_%D_%M_%Y.bkp';
  - backup current controlfile format '/backup/backup/level0_dbctl_%U_%D_%M_%Y.bkp';
  - BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL format '/backup/backup/level0_arc_%U_%D_%M_%Y.bkp' delete input;
  - backup current controlfile format '/backup/backup/level0_arcctl_%U_%D_%M_%Y.bkp';
  - backup spfile format '/backup/backup/level0_spfile_%U_%D_%M_%Y.bkp';
  - BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/backup/level0_%U';
  - create pfile='/backup/backup/level0_pfile.ora' from spfile;
  - }
  - EOF
- Full Database Backup (Incremental) (Below are the Steps for Taking Incremental Backups)
  - Enable Block Change Tracking File
    - ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/blocktracking.ora';
    - SELECT filename, status, bytes FROM v$block_change_tracking;
  - Full Incremental Backup (Level 0 Backup)
    - rman target / LOG='/backup/logs/rman_backup_Level0.log' <<EOF
    - run{
    - BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE format '/backup/backup/level0_db_%U_%D_%M_%Y.bkp';
    - backup current controlfile format '/backup/backup/level0_dbctl_%U_%D_%M_%Y.bkp';
    - BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 ARCHIVELOG ALL format '/backup/backup/level0_arc_%U_%D_%M_%Y.bkp' delete input;
    - backup current controlfile format '/backup/backup/level0_arcctl_%U_%D_%M_%Y.bkp';
    - backup spfile format '/backup/backup/level0_spfile_%U_%D_%M_%Y.bkp';
    - BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/backup/level0_%U';
    - create pfile='/backup/backup/level0_pfile.ora' from spfile;
    - }
    - EOF
  - Incremental Backup (Level 1 Backup)
    - rman target / LOG='/backup/logs/rman_backup_Level1_incremental.log' <<EOF
    - run{
    - BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE format '/backup/backup/level1_inc_db_%U_%D_%M_%Y.bkp';
    - backup current controlfile format '/backup/backup/level1_inc_dbctl_%U_%D_%M_%Y.bkp';
    - BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 ARCHIVELOG ALL format '/backup/backup/level1_inc_arc_%U_%D_%M_%Y.bkp' delete input;
    - backup current controlfile format '/backup/backup/level1_inc_arcctl_%U_%D_%M_%Y.bkp';
    - backup spfile format '/backup/backup/level1_inc_spfile_%U_%D_%M_%Y.bkp';
    - BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/backup/level1_inc_%U';
    - create pfile='/backup/backup/level1_inc_pfile.ora' from spfile;
    - }
    - EOF
  - Cumulative Backup (Level 1 Backup)
    - rman target / LOG='/backup/logs/rman_backup_Level1_comulative.log' <<EOF
    - run{
    - BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 cumulative DATABASE format '/backup/backup/level1_cum_db_%U_%D_%M_%Y.bkp';
    - backup current controlfile format '/backup/backup/level1_cum_dbctl_%U_%D_%M_%Y.bkp';
    - BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 cumulative ARCHIVELOG ALL format '/backup/backup/level1_cum_arc_%U_%D_%M_%Y.bkp' delete input;
    - backup current controlfile format '/backup/backup/level1_cum_arcctl_%U_%D_%M_%Y.bkp';
    - backup spfile format '/backup/backup/level1_cum_spfile_%U_%D_%M_%Y.bkp';
    - BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/backup/level1_cum_%U';
    - create pfile='/backup/backup/level1_cum_pfile.ora' from spfile;
    - } EOF
Cold Backup of Database
- sqlplus / as sysdba
- shutdown immediate;
- startup mount;
- Execute RMAN backup Script
- alter database open;
Monitoring RMAN Backup

SELECT status status,
object_type object_type,
input_bytes / 1024 / 1024 / 1024 input_bytes,
output_bytes / 1024 / 1024 / 1024 output_bytes,
TO_CHAR (start_time, 'mm/dd/yyyy:hh:mi:ss') AS start_time,
TO_CHAR (end_time, 'mm/dd/yyyy:hh:mi:ss') AS end_time
FROM v$rman_status
WHERE start_time > SYSDATE - 1 AND operation = 'BACKUP'

Recommendations

For Incremental Backup

Full Backup Should be execute on Sunday.

Incremental Backup execute on Monday, Tuesday and Thursday.

Cumulative Backup executes on Wednesday.

General Tips

We should take backup on Daily basis.
Backup should be Copy on External Drive as well Like Tape Drive or USB Drive.
Backup Drive / Tape should move to External Locations.

Recommendations

about

Contacts

Quick Links