Playing with RMAN, FRA, and catalog views

Now that we have all configured for RMAN (including our catalog), let's play with our environment.

Monitoring a backup

Of course, to be able to monitor a backup running in our database, we will need to first run our scripts using the following command:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Now that we have our backup running, open a new terminal, connect to SQL*Plus, and execute the following command:

SQL> SELECT sid, serial#, context, sofar, totalwork, round(sofar/totalwork*100,2) "%_COMPLETE"
 2   FROM   v$session_longops
 3   WHERE  opname like 'RMAN%'
 4   AND    opname not like '%aggregate%'
 5   AND    totalwork !=0
 6   AND    sofar <> totalwork
/

Note

This script will show the completed percentage of the current channels, not of the complete job.

Incremental backups

Here are some examples of incremental backup statements:

RMAN> BACKUP INCREMENTALLEVEL=0 DATABASE PLUS ARCHIVELOG DELETE INPUT;
RMAN> BACKUP INCREMENTALLEVEL=1 DATABASE PLUS ARCHIVELOG DELETE INPUT;
RMAN> BACKUP INCREMENTALLEVEL=0 CUMULATIVE DATABASE PLUS ARCHIVELOG DELETE INPUT;

Multisection backups

Have a look at the following command:

RMAN> BACKUP SECTION SIZE 10M TABLESPACE users;

FRA – checking number of redo switches

Have a look at the following command:

SQL> ALTER SESSION SET nls_date_format='dd/mm/yyyy hh24:mi:ss';

SQL> SELECT sequence#, first_time log_started,
lead(first_time, 1,null) over (order by first_time) log_ended
 2   FROM (SELECT DISTINCT sequence#, first_time
 3         FROM   dba_hist_log
 4         WHERE  archived='YES'
 5         AND    sequence#!=0
 6         ORDER BY first_time)
 7   ORDER BY sequence#;

Check for alerts

Have a look at the following command:

SQL> SELECT reason FROM dba_outstanding_alerts;

Check FRA usage

Have a look at the following command:

SQL> SELECT * FROM v$recovery_file_dest;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT * FROM v$recovery_file_dest;
SQL> SELECT * FROM v$flash_recovery_area_usage;

See the archived log generated by the DB target

Have a look at the following command:

SQL> SET PAGESIZE 200
SQL> SET LINESIZE 200
SQL> COLUMN name FORMAT a50
SQL> COLUMN completion_time FORMAT a25
SQL> ALTER SESSION SET nls_date_format= 'DD-MON-YYYY:HH24:MI:SS';
SQL> SELECT name, sequence#, status, completion_time
 2   FROM   CATALOG_BCK.rc_archived_log;

See the control file backups

Have a look at the following command:

SQL> SET PAGESIZE 200
SQL> SET LINESIZE 200
SQL> SELECT file#, creation_time, resetlogs_time
blocks, block_size, controlfile_type
 2   FROM   v$backup_datafile where file#=0;

SQL> COLUMN completion_time FORMAT a25
SQL> COLUMN autobackup_date FORMAT a25
SQL> ALTER SESSION SET nls_date_format= 'DD-MON-YYYY:HH24:MI:SS';
SQL> SELECT db_name, status, completion_time, controlfile_type,
autobackup_date 
 2   FROM   CATALOG_BCK.rc_backup_controlfile;

SQL> SELECT creation_time, block_size, status,completion_time,autobackup_date, autobackup_sequence
 2   FROM   CATALOG_BCK.rc_backup_controlfile;

See the corrupted list that exists in datafile backups

Have a look at the following command:

SQL> SELECT db_name, piece#, file#, block#, blocks, corruption_type
 2   FROM   CATALOG_BCK.rc_backup_corruption where db_name='ORCL';

See block corruption in the DB, populated when backup or backup validate

Have a look at the following command:

SQL> SELECT file#, block#, corruption_type
 2   FROM   v$database_block_corruption;

See all RMAN configurations (equivalent to show all)

Have a look at the following command:

SQL> COLUMN value FORMAT a60
SQL> SELECT db_key,name, value
 2   FROM   CATALOG_BCK.rc_rman_configuration;

Monitor backup outputs (RMAN)

Have a look at the following command:

SQL> SELECT output FROM v$rman_output ORDER BY stamp;

Offline backups with RMAN

Have a look at the following command:

$ rman target / catalog=catalog_bck/rmancatalog@pdborcl
RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;
RMAN> ALTER DATABASE OPEN;

Offline backup without using configured defaults

Have a look at the following command:

SHUTDOWN IMMEDIATE
STARTUP MOUNT
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '/DB/u02/backups/other/bck1/orcl_%U';
ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT '/DB/u02/backups/other/bck2/orcl_%U';
BACKUP AS COMPRESSED BACKUPSET DATABASE;
BACKUP CURRRENT CONTROLFILE;
}

Using backup limits (duration)

Have a look at the following command:

RMAN> BACKUP DURATION 00:05 DATABASE;
RMAN> BACKUP DURATION 00:05 MINIMIZE TIME DATABASE;
RMAN> BACKUP DURATION 00:05 MINIMIZE LOAD DATABASE;

Modifying the retention policy for a backup set (archival backups)

Have a look at the following command.

Note

In Oracle 11g, the KEEP command overrides the default criteria; this cannot use the FRA.

RMAN> BACKUP DATABASE KEEP FOREVER;
RMAN> BACKUP DATABASE FORMAT '/DB/u02/backups/other/bck1/orcl_%U' KEEP untiltime='sysdate+180' TAG keep_backup;

Archive deletion policy

Have a look at the following command:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE DISK;

Using RMAN to scan DB for physical and logical errors

Have a look at the following command:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

Configuring tablespaces for exclusion from whole database backups

Have a look at the following command:

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE example;
RMAN> BACKUP DATABASE;
# backs up the whole database, including example
RMAN> BACKUP DATABASE NOEXCLUDE;
RMAN> BACKUP TABLESPACE example;  # backs up only  example
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE example CLEAR;

Skipping offline, inaccessible, or read-only datafiles

Have a look at the following command:

RMAN> BACKUP DATABASE SKIP READONLY;
RMAN> BACKUP DATABASE SKIP OFFLINE;
RMAN> BACKUP DATABASE SKIP INACCESSIBLE;
RMAN> BACKUP DATABASE SKIP READONLY SKIP OFFLINE SKIP INACCESSIBLE;

Forcing backups of read-only datafiles

Have a look at the following command:

RMAN> BACKUP DATABASE FORCE;

Backup of newly added datafiles

Add a new datafile for the tablespace example and execute using the following command:

RMAN> BACKUP DATABASE NOT BACKED UP;

Backup files not backed up during a specific period

Have a look at the following command:

RMAN> BACKUP DATABASE NOT BACKED UP SINCE time='sysdate-2';
RMAN> BACKUP ARCIVELOG ALL NOT BACKED UP 1 TIMES;
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES DELETE INPUT;

General backup examples

Have a look at the following command:

RMAN> BACKUP TABLESPACE USERS INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG;
RMAN> BACKUP DATAFILE 2;
RMAN> BACKUP ARCHIVELOG ALL;
RMAN> BACKUP ARCHIVELOG FROM TIME 'sysdate-1';
RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 123; (Enter here a valid sequence from your database)
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
RMAN> BACKUP ARCHIVELOG FROM SEQUENCE xxx DELETE INPUT;
RMAN> BACKUP ARCHIVELOG NOT BACKED UP 3 TIMES;
RMAN> BACKUP ARCHIVELOG UNTIL TIME 'sysdate - 2' DELETE ALL INPUT;

Backup copies

Have a look at the following command.

Note

RMAN will use FRA if it is configured.

RMAN> BACKUP AS COPY DATABASE;
RMAN> BACKUP AS COPY TABLESPACE USERS;
RMAN> BACKUP AS COPY DATAFILE 1;
RMAN> BACKUP AS COPY ARCHIVELOG ALL;