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;

Oracle Database 12c Backup and Recovery Survival Guide

Oracle Database 12c Backup and Recovery Survival Guide

Overview of this book

Playing with RMAN, FRA, and catalog views

Monitoring a backup

Note

Incremental backups

Multisection backups

FRA – checking number of redo switches

Check for alerts

Check FRA usage

See the archived log generated by the DB target

See the control file backups

See the corrupted list that exists in datafile backups

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

See all RMAN configurations (equivalent to show all)

Monitor backup outputs (RMAN)

Offline backups with RMAN

Offline backup without using configured defaults

Using backup limits (duration)

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

Note

Archive deletion policy

Using RMAN to scan DB for physical and logical errors

Configuring tablespaces for exclusion from whole database backups

Skipping offline, inaccessible, or read-only datafiles

Forcing backups of read-only datafiles

Backup of newly added datafiles

Backup files not backed up during a specific period

General backup examples

Backup copies

Note

Unlock full access

Continue reading for free

Oracle Database 12c Backup and Recovery Survival Guide

Oracle Database 12c Backup and Recovery Survival Guide

Overview of this book

Playing with RMAN, FRA, and catalog views

Monitoring a backup

Note

Incremental backups

Multisection backups

FRA – checking number of redo switches

Check for alerts

Check FRA usage

See the archived log generated by the DB target

See the control file backups

See the corrupted list that exists in datafile backups

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

See all RMAN configurations (equivalent to show all)

Monitor backup outputs (RMAN)

Offline backups with RMAN

Offline backup without using configured defaults

Using backup limits (duration)

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

Note

Archive deletion policy

Using RMAN to scan DB for physical and logical errors

Configuring tablespaces for exclusion from whole database backups

Skipping offline, inaccessible, or read-only datafiles

Forcing backups of read-only datafiles

Backup of newly added datafiles

Backup files not backed up during a specific period

General backup examples

Backup copies

Note

Unlock full access

Continue reading for free

Delete Bookmark