Recovery Manager (RMAN)

Recovery manager is a platform non-specific utility for coordinating you backup and restoration procedures across multiple servers. 

 it's value is limited if you only have on or two instances, but it comes into it's own where large numbers of instances on multiple platforms are used. 

Backup and Recovery functionality

Create Recovery Catalog

First create a user to hold the recovery catalog.

SQL> 
SQL> show user;
USER is "SYS"
SQL> alter session set container=PDB1;

Session altered.

SQL> -- Create tablepsace to hold repository

SQL> CREATE TABLESPACE TBS_TOOLS
  2  DATAFILE '/u02/oradata/CDB1/pdb1/TBS_TOOLS01.dbf' size 10M
  3  AUTOEXTEND ON NEXT 1024K
  4  EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL> --- Create rman schema owner
SQL> CREATE USER rman IDENTIFIED BY rman
  2  TEMPORARY TABLESPACE temp 
  3  DEFAULT TABLESPACE TBS_TOOLS 
  4  QUOTA UNLIMITED ON TBS_TOOLS;

User created.

SQL> GRANT connect, resource, recovery_catalog_owner TO rman;

Grant succeeded.

SQL>

Then create the recovery catalog.

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ rman catalog rman/rman@PDB1

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 31 12:16:01 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database

RMAN> create catalog tablespace TBS_TOOLS;

recovery catalog created

RMAN> 

RMAN> exit


Recovery Manager complete.
[oracle@oracle-db-19c ~]$ 

Register Database

[oracle@oracle-db-19c ~]$ rman target sys/sys@PDB1 rcvcat rman/rman@PDB1 msglog 'OracleBackupPDB1PDB1_Daily_Backup.log'
RMAN> register database;
RMAN> exit
[oracle@oracle-db-19c ~]$ ls -ltr
total 2988308
-rw-r--r--. 1 root   root     3059705302 Nov  2 13:56 LINUX.X64_193000_db_home.zip
drwxr-xr-x. 2 oracle oinstall          6 Nov 26 10:45 Templates
drwxr-xr-x. 2 oracle oinstall          6 Nov 26 10:45 Public
drwxr-xr-x. 2 oracle oinstall          6 Nov 26 10:45 Downloads
drwxr-xr-x. 2 oracle oinstall          6 Nov 26 10:45 Documents
drwxr-xr-x. 2 oracle oinstall          6 Nov 26 10:45 Desktop
drwxr-xr-x. 2 oracle oinstall          6 Nov 26 10:45 Videos
drwxr-xr-x. 2 oracle oinstall          6 Nov 26 10:45 Pictures
drwxr-xr-x. 2 oracle oinstall          6 Nov 26 10:45 Music
-rw-r--r--. 1 root   root         313139 Nov 26 10:54 rlwrap-0.43.tar.gz
drwxr-xr-x. 2 oracle oinstall        136 Jan  1 12:33 expbk
-rw-r--r--. 1 oracle oinstall        669 Jan 31 12:26 OracleBackupPDB1PDB1_Daily_Backup.log
[oracle@oracle-db-19c ~]$

Cold Backup

This RMAN script starts by doing a a clean mount of the database. It then backs up the datafiles, controlfile and archivelogs, with old archive logs deleted in the process. Finally the database is opened.

replace script 'PDB1_daily_backup' {

  # make sure database is shutdown cleanly
  shutdown immediate;
  startup force;
  shutdown immediate;
  
  
  #Mount the database and start backup
  startup mount;

  
  # Backup datafile, controlfile and archivelogs
  allocate channel ch1 type 
    disk format '/home/oracle/Backup/PDB1/%d_DB_%u_%s_%p'; 
  backup database include current controlfile
    tag = 'PDB1_daily_backup';
  release channel ch1;


  # Open the database
  alter database open;


  # Archive all logfiles including current
  sql 'ALTER SYSTEM ARCHIVE LOG CURRENT'; 


  # Backup outdated archlogs and delete them
  allocate channel ch1 type 
    disk format '/home/oracle/Backup/PDB1/%d_ARCH_%u_%s_%p'; 
  backup archivelog 
    until time 'Sysdate-2' all 
    delete input;
  release channel ch1;

  
  # Backup remaining archlogs
  allocate channel ch1 type 
    disk format '/home/oracle/Backup/PDB1/%d_ARCH_%u_%s_%p'; 
  backup archivelog all;
  release channel ch1;
  
}

The file can be loaded as a stored script and run using the following commands.

RMAN> @/home/oracle/Backup/PDB1_daily_backup.txt
RMAN> run {execute script TSH1_daily_backup; }

Hot Backup

Hot backups using RMAN are very simple. There is no need to alter the tablespace or database mode.

run {
  allocate channel ch1 type disk format '/home/oracle/Backup/PDB1/%d_DB_%u_%s_%p';
  backup database;
  backup archivelog all;
  release channel ch1;
}

Restore & Recover The Whole Database

Recovering from a media failure is as simple.

run { 
    startup mount;

    allocate channel ch1 type disk;

    restore database;
    recover database;
  
    release channel ch1;
}

Restore & Recover A Subset Of The Database

A subset of the database can be restored in a similar fashion.

run {
    sql 'ALTER TABLESPACE users OFFLINE IMMEDIATE';
    restore tablespace users;
    recover tablespace users;
    sql 'ALTER TABLESPACE users ONLINE'; 
}

Incomplete Recovery

RMAN allows incomplete recovery to a specified time, SCN or sequence number.

run
{ 
  set until time 'Feb 1 2023 09:00:00';
  # set until scn 1000;       # alternatively, you can specify SCN
  # set until sequence 9923;  # alternatively, you can specify log sequence number
  restore database;
  recover database;
}

alter database open resetlogs;

Lists And Reports

RMAN has extensive listing and reporting functionality allowing you to monitor you backups and maintain the recovery catalog. Here are a few useful commands.

# Show all backup details
list backup;

# Show items that need 7 days worth of
# archivelogs to recover completely
report need backup days = 7 database;  


# Show/Delete items not needed for recovery
report obsolete;
delete obsolete;

# Show/Delete items not needed for point-in-time
# recovery within the last week
report obsolete recovery window of 7 days; 
delete obsolete recovery window of 7 days;

# Show/Delete items with more than 2 newer copies available
report obsolete redundancy = 2 device type disk;
delete obsolete redundancy = 2 device type disk;

# Show datafiles that connot currently be recovered
report unrecoverable database;
report unrecoverable tablespace 'USERS';