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
- Cold backup (database closed).
- Hot backup (database open, ARCHIVELOG mode).
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 RMAN backup to different server with same database name
- Restore RMAN backup to another server (Gavin Soorma)
- Restore an RMAN backup 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
- After an incomplete recovery or a control file restore, open the database with
RESETLOGS. - Deleting all archived logs without backing them up breaks point-in-time recovery; use it only as a temporary measure.