RMAN - Basic backup/restore examples

Use the "archive log list" command to verify if your database is in archive log mode:

1. setup all RMAN variables correctly

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HPCDR1 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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbs/snapcf_HPCDR1.f'; # default

RMAN>

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/backup/HPCDR1/snapcf_HPCDR1.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/backup/HPCDR1/snapcf_HPCDR1.f';
new RMAN configuration parameters are successfully stored

RMAN>

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/HPCDR1/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/HPCDR1/%F';
new RMAN configuration parameters are successfully stored

RMAN>

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN>

2. control_file_record_keep_time

When you do not use a recovery catalog, the control file is the sole source of information about RMAN backups. As you make backups, Oracle records these backups in the control file. To prevent the control file from growing without bound to hold RMAN repository data, records can be reused if they are older than a threshhold you specify.

The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter determines the minimum age in days of a record before it can be overwritten:

CONTROL_FILE_RECORD_KEEP_TIME = integer

For example, if the parameter value is 14, then any record aged 14 days and older is a candidate for reuse. Information in an overwritten record is lost. The oldest record available for reuse will be used first.

When Oracle needs to add new RMAN repository records to the control file, but no record is older than the threshhold, Oracle attempts to expand the size of the control file. If the underlying operating system prevents the expansion of the control file (due to a disk full condition, for instance), Oracle overwrites the oldest record in the control file and logs this action in the alert log.

The default value of CONTROL_FILE_RECORD_KEEP_TIME is 7 days. If you are not using a recovery catalog, then set the CONTROL_FILE_RECORD_KEEP_TIME value to slightly longer than the oldest file that you need to keep. For example, if you back up the whole database once a week, then you need to keep every backup for at least seven days. Set CONTROL_FILE_RECORD_KEEP_TIME to a value such as 10 or 14.

Caution:

Regardless of whether you use a recovery catalog, never use RMAN when CONTROL_FILE_RECORD_KEEP_TIME is set to 0. If you do, then you may lose backup records.

I always set mine to 365 days:

3. Test RMAN by backing up all archive log files for past 24 hours:

run {
allocate channel dev1 type disk;backup as compressed backupset
format '/backup/HPCDR1/arch_%d_T%T_s%s_p%p'
(archivelog from time 'sysdate-1' all);
release channel dev1;
}

Use this command to see all archive logs backed up so far:

list backup of archivelog all;

a Linux 'ls -l' will show the controlfile snapshot backup, as well as the archive log backupset:

4. Take a complete backup of the database

The below, shows that no backup of the database exist, which is correct as this is a newly created database:

Use the below RMAN script to take a full, compressed backup of the database, including all archived logs:

run {
allocate channel dev1 type disk maxpiecesize=10G;
backup as compressed backupset
full
format '/backup/HPCDR1/full_%d_T%T_s%s_p%p'
(database);
backup as compressed backupset
format '/backup/HPCDR1/arch_%d_T%T_s%s_p%p'
archivelog from scn=0 all delete input;
release channel dev1;
}

Do another "list backup of database" to see of the backup is registered in the controlfile:

The "list backup" command will list all backups registered in the control file.

You can once again confirm on an O/S level that the backups did take place:

5. Backup All ONLINE log files:

run {
allocate channel dev1 type disk;
sql "alter system archive log current";
backup as compressed backupset
format '/backup/HPCDR1/arch_%d_T%T_s%s_p%p'
(archivelog from time 'sysdate-1' all delete input);
release channel dev1;
}

6. Monitoring The Backup Progress

To monitor the backup progress run the following sql against the target database:

select sid, serial#, context ,round(sofar/totalwork*100,2) "% Complete",
         substr(to_char(sysdate,'yymmdd hh24: mi:ss'),1,15) "Time Now"
from  v$session_longops
where substr(opname,1,4) = 'RMAN';         

7. Take a full cold backup of the database

Exactly the same RMAN script as in use at 4) , but the database must be in MOUNTED mode, not opened:

startup mount;

8. Backup only the current control files:

run {
allocate channel dev1 type disk;
backup 
format '/backup/HPCDR1/current_cf_%d_T%T_s%s_p%p'
(current controlfile);
release channel dev1;
}

Use "list backup of controlfile" to see all previous controlfile backups:

9. Make Incremental Backup Since Last Full backup – level 1

run { 
allocate channel dev1 type disk; 
backup as compressed backupset
incremental level 1 
format '/backup/HPCDR1/database_lev1_%d_T%T_s%s_p%p'(database); release channel dev1; 
}

10. Make Incremental Backup Since Last Incremental Backup – level 2

run { 
allocate channel dev1 type disk;backup as compressed backupset
incremental level 2 
format '/backup/HPCDR1/database_lev2_%d_T%T_s%s_p%p'
(database); 
release channel dev1; 
}

You can see the difference below in the size between a Level0 and a level2 RMAN backup:

Full backup:                445Mb

Level0 Backup:           445Mb

Level2 Backup:           4.2Mb

12. Test the restore of a tablespace when a datafile was deleted AND the database is up and running –ONLY UNIX/Linux platforms:

SQL> select file_name,file_id from dba_data_files;

FILE_NAME                                               FILE_ID
--------------------------------------------------      --------
/u01/app/oracle/oradata/HPCDR1/system01.dbf              1
/u01/app/oracle/oradata/HPCDR1/sysaux01.dbf              2
/u01/app/oracle/oradata/HPCDR1/undotbs01.dbf             3
/u01/app/oracle/oradata/HPCDR1/users01.dbf               4

SQL>

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1TEMP
USERS

SQL>

While the database is up and running, delete the USERS tablespace's datafile, and recover it again:

Test that there is a problem with the tablespace, before attempting to recover:

Use the below to recover either the datafile or the complete tablespace:

(a) Datafile recovery - use this when your tablespace as more than 1 datafiles, and only one datafile has a problem:

run { 
allocate channel dev1 type disk;restore datafile 4; 
recover datafile 4; 
alter database open;
release channel dev1; 
}

(b) Tablespace recovery - use this when more than one datafile is corrupt, or if the corruption cannot be resolved by only restoring one or many, but not all datafiles. Database can be mounted, but not opened.

Delete the datafile again:

run { 
allocate channel dev1 type disk; 
restore tablespace USERS;recover tablespace USERS; 
alter database open;
release channel dev1;} 

13. Full database restore when the SYSTEM datafile is corrupt/missing, and all archive log files are available:

The first step is to start the database in NOMOUNT mode, and to recover the controlfile from the latest autobackup:

SQL> startup nomount;

RMAN> restore controlfile from <backupfile location>;

Change the database to MOUNT mode, and use RMAN to restore the database from the last full backup RMAN is aware off ( as registered in the controlfile restore in the first step above)

Recover database up to the last archive log, using the "recover database" RMAN command:

Open the database