LLI Wiki — My Digital Knowledge Base

Oracle RMAN: backup, restore and archived logs cheat sheet

RMAN configuration, full and point-in-time restore, Veeam plug-in and archived log housekeeping.

This note is a field cheat sheet for Oracle RMAN: a baseline configuration, restoring a database from disk or from a Veeam Plug-in backup, point-in-time recovery, archived log housekeeping and a few useful reports.

Baseline RMAN configuration

show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/{server name}/%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/{server name}/%U' MAXPIECESIZE 10 G;
CONFIGURE MAXSETSIZE TO UNLIMITED;
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/dbhome_1/dbs/snapcf_orcl.f'; # default

The %F format generates a name c-IIIIIIIIII-YYYYMMDD-QQ where IIIIIIIIII is the DBID, YYYYMMDD a timestamp and QQ a sequence number in hexadecimal.

Environment used in the examples:

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl

Backup types

To restore a backup on another server, copy the backup pieces there and catalog them first:

catalog start with '/backup/sqlserver01/';

References on restoring to a different host:

Restore from disk

Restore puts back the files needed to bring the database to a consistent state (copying backup files from tape or storage to a staging area). Recovery applies the transactions recorded in the archived logs, rolling the database forward to a point in time or to the last recorded transaction.

First check the backup directory (for example /backup/<servername>).

Step 1: restore the control file

See How to restore the controlfile using RMAN.

shutdown immediate;
startup nomount;
-- restore controlfile from autobackup
alter database open resetlogs;

To restore from a specific backup piece, the DBID is in the file name (c-4065378253-20210903-00 gives DBID 4065378253 in this example):

set dbid=4065378253;

restore controlfile from autobackup;
-- if it fails:
set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/sqlserver01/%F';
restore controlfile from autobackup;
-- if it still fails:
restore controlfile from '/backup/sqlserver01/c-4065378253-20210903-00';

Step 2: restore the database

catalog start with '/backup/sqlserver01';
restore database preview;
restore database;
alter database open resetlogs;

Point-in-time recovery:

run {
-- change the date and time to suit
SET UNTIL TIME "to_date('01 SEP 2011 12:04:00','DD MON YYYY hh24:mi:ss')";
recover database;
}
shutdown immediate;
startup mount;
run
{
allocate channel ch_1 type disk;
set until time "to_date('2021-08-18 20:10:14','yyyy-mm-dd hh24:mi:ss')";
restore database;
recover database;
}

Step 3: open with resetlogs

alter database open resetlogs;

Restore a tablespace or datafiles

RESTORE TABLESPACE dev1;
RESTORE TABLESPACE dev1, dev2;

RESTORE DATAFILE '/u01/oradata/devdb/dev1_01.dbf';
RESTORE DATAFILE '/u01/oradata/devdb/dev1_01.dbf', '/u01/oradata/devdb/dev1_02.dbf';
RESTORE DATAFILE 34, 35;

To find the file numbers: select file_id, file_name from dba_data_files;

Restore archived redo logs

RESTORE ARCHIVELOG ALL;
SET ARCHIVELOG DESTINATION TO '/home/oracle/';
RESTORE ARCHIVELOG ALL;
RESTORE ARCHIVELOG FROM SEQUENCE 153 UNTIL SEQUENCE 175;
RESTORE ARCHIVELOG FROM SCN 56789;

Recover a tablespace or datafile

RECOVER TABLESPACE dev1;
RECOVER TABLESPACE dev1, dev2;
RECOVER DATAFILE '/u01/oradata/devdb/dev1_01.dbf';
RECOVER DATAFILE 34, 35;
-- optionally delete archived logs once applied
RECOVER TABLESPACE dev1 DELETE ARCHIVELOG;

Validate before restoring (dry run)

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
RESTORE DATABASE VALIDATE;
RESTORE DATABASE PREVIEW;
RESTORE DATABASE PREVIEW SUMMARY;

Restore pluggable databases

run {
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE;
RESTORE PLUGGABLE DATABASE pdb1, pdb2;
RECOVER PLUGGABLE DATABASE pdb1, pdb2;
ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN;
}

Restore with the Veeam Plug-in for Oracle RMAN

References: Veeam control file restore and point-in-time restore from tape.

Configure the plug-in (wizard, or set the credentials explicitly):

OracleRMANConfigTool --wizard
OracleRMANConfigTool --set-credentials "<DOMAIN>\<backup_user>" "<PASSWORD>"

Restore the control file through an SBT channel:

shutdown immediate;
startup nomount;

RUN {
ALLOCATE CHANNEL c0 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/opt/veeam/VeeamPluginforOracleRMAN/libOracleRMANPlugin.so';
RESTORE controlfile FROM '<backup_piece>.vab';
}

backup current controlfile;

Related commands:

restore controlfile from autobackup preview;
list backup summary;
LIST BACKUP OF CONTROLFILE;
LIST BACKUP OF ARCHIVELOG ALL;
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

Get the DBID, then restore and recover:

select DBID from v$database;
SET DBID 1234567890;

restore database;
recover database;

alter database open resetlogs;

Catalog backup pieces after a rebuild or a VM restore

catalog device type 'sbt_tape' backuppiece '<backup_piece>.vab';
crosscheck backuppiece '<backup_piece>.vab';

Restore from a backup taken on another server (srcSrv is the name of the original server):

run {
ALLOCATE CHANNEL a1 TYPE sbt_tape PARMS "SBT_LIBRARY=/opt/veeam/VeeamPluginforOracleRMAN/libOracleRMANPlugin.so" FORMAT '<backup_piece>.vab' SEND "srcSrv=original_server_name";
restore database;
recover database;
}

Generic catalog examples:

-- catalog a backup piece
RMAN> catalog backuppiece '/dba/backup/rman/data01.bkp';
-- catalog a user-managed datafile copy
RMAN> catalog datafilecopy '/dba/backup/grid/data01.dbf';
-- uncatalog a backup piece
RMAN> change backuppiece '/dba/backup/grid/data01.bkp' uncatalog;

Archived log deletion policy

If you get RMAN-08591: warning: invalid archived log deletion policy, adjust the policy to your setup (see Veeam archived redo retention):

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';

Catalog a backup piece, then restore the database:

catalog device type 'sbt_tape' backuppiece 'bk_s785_p1_t755621364';
catalog backuppiece 'C:\Oracle\recovery_area\orcl\BACKUPSET\2012_09_04\O1_MF_NNND0_TAG20120904T133454_84CFKB4D_.BKP'
restore database;

Restore an existing database in place

RUN {
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
}

alter database open resetlogs;

Recovery to an SCN and incarnations

RMAN> run {
set until scn=2146578;
restore database;
recover database;
}

alter database open resetlogs;

Check the incarnations (see Database incarnation using RMAN):

select incarnation#, resetlogs_change# from v$database_incarnation;

Point-in-time restore including the control file (see the Oracle documentation):

RUN
{
  -- set target time for all operations in the RUN block
  SET UNTIL TIME 'Oct 8 2004 07:55:00';
  RESTORE CONTROLFILE;
  -- without recovery catalog, use RESTORE CONTROLFILE FROM AUTOBACKUP
  ALTER DATABASE MOUNT;
  RESTORE DATABASE;
  RECOVER DATABASE;
}

Listing and housekeeping

list backup tag LV0BKP;

report obsolete;
delete obsolete;
crosscheck backup;

Archived logs:

RMAN> list archivelog all;                                  -- archived logs known to the controlfile
RMAN> backup archivelog all;                                -- back up all archived logs
RMAN> backup archivelog all not backed up 1 times;
RMAN> backup archivelog all delete input;                   -- back up and delete from disk
RMAN> list archivelog all backed up 1 times to DISK;

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
RMAN> delete archivelog all backed up 1 times to DISK;
RMAN> crosscheck archivelog sequence 24;
RMAN> crosscheck backup of archivelog sequence 24;

Script that purges all archived logs every few minutes (a stop-gap when the archive destination fills up):

while true
do
        echo "Press [CTRL+C] to stop.."
        ${ORACLE_HOME}/bin/rman <<EOF
        connect target sys/<PASSWORD>
        delete noprompt archivelog all;
EOF
        sleep 101
done

Backup size per day

select ctime "Date"
       , decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type
       , bsize "Size MB"
  from (select trunc(bp.completion_time) ctime
        , backup_type
        , round(sum(bp.bytes/1024/1024),2) bsize
     from v$backup_set bs, v$backup_piece bp
     where bs.set_stamp = bp.set_stamp
     and bs.set_count  = bp.set_count
     and bp.status = 'A'
     group by trunc(bp.completion_time), backup_type)
  order by 1, 2;

Notes

Mastodon LLIMastodon SocialFosstodon