Monitoring Open Database Backups in Oracle 26ai

Monitor User-Managed Backup Mode

SELECT b.con_id,
       t.name AS tablespace_name,
       d.file#,
       d.name AS datafile_name,
       b.status,
       b.change#,
       TO_CHAR(b.time, 'YYYY-MM-DD HH24:MI:SS') AS backup_start
FROM   v$backup b
       JOIN v$datafile d
         ON d.file# = b.file#
        AND d.con_id = b.con_id
       JOIN v$tablespace t
         ON t.ts# = d.ts#
        AND t.con_id = d.con_id
WHERE  b.status = 'ACTIVE'
ORDER BY b.con_id, d.file#;

ALTER TABLESPACE users BEGIN BACKUP;

ALTER TABLESPACE users END BACKUP;

ALTER DATABASE BEGIN BACKUP;
ALTER DATABASE END BACKUP;

Respond to Files Left in Backup Mode

Backup mode should last only as long as the external copy requires it. While an online read/write tablespace remains in this state, Oracle can place complete changed blocks into redo. Heavy update activity can therefore increase redo volume, archive-storage consumption, and performance pressure. A normal shutdown can also fail with ORA-01149 when files remain in backup mode.

An unexpected ACTIVE row requires investigation, not an automatic END BACKUP command. The external copy may still be running, an automation job may have failed between steps, or the instance may have restarted after an interruption. Use this response sequence:

1. Identify every active data file, tablespace, and container.
2. Find the corresponding backup job, script execution, operator action, or storage-snapshot workflow.
3. Review the alert log and the external utility's log to determine whether copying completed.
4. Choose the correct end-backup or recovery action for the actual state. Do not end a legitimate operation blindly.
5. Archive and preserve the required redo, then verify the resulting copy according to the recovery procedure.

V$BACKUP also has recovery-related limitations. If the current control file was restored from a backup or re-created after a media failure, it may not contain the information needed to populate the view accurately. If a data file was restored, its row can reflect the status of the older restored file instead of the latest pre-failure state. During recovery, correlate this view with the alert log, recovery status, data file headers, RMAN records, and the files that actually survived.

Inspect Data File Headers

SELECT con_id,
       file#,
       name,
       status,
       error,
       recover,
       fuzzy,
       checkpoint_change#,
       checkpoint_time
FROM   v$datafile_header
ORDER BY con_id, file#;

Monitor an RMAN Backup Job

Most production online backups use RMAN. Monitor them with RMAN-specific views and the RMAN client or scheduler log. The following query displays recent backup jobs, including current jobs represented by the view:

SELECT session_key,
       input_type,
       status,
       start_time,
       end_time,
       input_bytes_display,
       output_bytes_display,
       time_taken_display,
       output_device_type
FROM   v$rman_backup_job_details
ORDER BY session_key DESC
FETCH FIRST 20 ROWS ONLY;

INPUT_TYPE identifies the general type of work, such as a full database, incremental database, data file, archived redo log, control file, or SPFILE backup. The display columns provide readable input and output sizes and elapsed time. OUTPUT_DEVICE_TYPE identifies disk, SBT, or more than one device type.

Interpret STATUS carefully. Typical values include RUNNING, COMPLETED, COMPLETED WITH WARNINGS, COMPLETED WITH ERRORS, and FAILED. A job that finishes with warnings or errors needs review. Do not count every row containing the word COMPLETED as an acceptable backup.

Review RMAN Operations and Messages

V$RMAN_STATUS displays ongoing and finished RMAN jobs and their operation hierarchy. Ongoing records reside in memory; completed records are stored in the control file. The following query shows session and command rows with their timing and destination information:

SELECT session_recid,
       session_stamp,
       operation,
       status,
       object_type,
       output_device_type,
       start_time,
       end_time
FROM   v$rman_status
WHERE  row_type IN ('SESSION', 'COMMAND')
ORDER BY start_time DESC;

V$RMAN_OUTPUT can expose messages produced by RMAN work that is still represented in memory. These messages can help identify the channel, file, backup piece, or error involved. They are not a durable substitute for capturing the RMAN client output or scheduler log in protected, centralized monitoring.

Use SET COMMAND ID in operational RMAN scripts when an organization needs to correlate a job with database views, scheduler records, or an external monitoring system. A meaningful command identifier makes concurrent jobs easier to distinguish.

Estimate Progress with V$SESSION_LONGOPS

V$SESSION_LONGOPS can expose progress for RMAN operations that qualify for long-operation reporting. A focused query avoids unrelated database work:

SELECT sid,
       serial#,
       opname,
       sofar,
       totalwork,
       units,
       ROUND(sofar / NULLIF(totalwork, 0) * 100, 2) AS pct_complete,
       elapsed_seconds,
       time_remaining
FROM   v$session_longops
WHERE  opname LIKE 'RMAN%'
AND    totalwork > 0
AND    sofar < totalwork
ORDER BY start_time;

SOFAR and TOTALWORK support an estimated completion percentage. TIME_REMAINING is also an estimate. Rows can appear, disappear, or restart as RMAN advances through data files, archived logs, backup sets, channels, and processing phases. A missing row does not prove that the RMAN job stopped, and one long-operation row does not represent every component of the complete backup.

Correlate the session, operation name, RMAN command ID, job status, and job log. Capacity or throughput alerts should also examine the backup destination, fast recovery area, SBT media manager, cloud object storage integration, or Recovery Appliance involved in the operation.

Define Useful Monitoring Alerts

A production monitor should evaluate several signals instead of looking only for a failed status. Alert when a scheduled backup does not start, remains in a running state substantially longer than its established baseline, finishes with warnings or errors, or produces no expected output. Also watch the destination for capacity exhaustion, media-manager failures, unavailable object storage, authentication failures, and network interruptions.

For an ARCHIVELOG database, backup health also depends on the redo recovery chain. A successful data file backup is not sufficient if archived redo cannot be created, transported, retained, or retrieved. Monitor the fast recovery area and archive destinations so that a full destination does not interrupt archiving or invalidate the expected recovery point. Coordinate archive-log deletion with the approved retention, Data Guard, and backup policies.

Thresholds should reflect the database's normal workload and service objectives. A large level 0 backup, a small archived-log backup, and an SBT backup across a shared network have different duration and throughput profiles. Compare like jobs by input type, device, schedule, and database size instead of assigning one universal elapsed-time threshold.

Retain monitoring history outside the target control file when the recovery design requires longer analysis. Finished RMAN records in the control file are subject to repository retention and record reuse, while in-progress information and V$RMAN_OUTPUT messages are memory based. Protected job logs and centralized monitoring provide evidence that remains available after an instance restart or control-file loss.

Verify the Backup After Monitoring Ends

Live monitoring answers whether an operation is running and whether obvious errors have occurred. After the job finishes, shift from progress monitoring to verification. RMAN commands such as these inspect records in the repository:

LIST BACKUP SUMMARY;
LIST BACKUP OF DATABASE;

LIST BACKUP inventories backups known to RMAN. REPORT SCHEMA, by contrast, describes the target database's data files and structure. It can help identify what should be protected, but it does not prove that a backup job succeeded.

Before accepting the run, confirm that:

• the RMAN job has an acceptable final status and every warning or error has been reviewed;
• the expected data files, control file, SPFILE, archived redo logs, and control-file autobackup are represented;
• the destination remained available and had enough capacity;
• retention, deletion, or archive-log maintenance did not remove redo required by the recovery objective;
• repository records agree with accessible backup pieces or image copies; and
• the appropriate validation, crosscheck, restore, and recovery tests have been completed on schedule.

A status of COMPLETED is valuable evidence, but it is not a guarantee that the organization can recover. The strongest evidence is a successful restore and recovery test in an isolated environment using the same credentials, keys, media libraries, network paths, and documented procedures that a real incident would require.

Effective monitoring therefore combines the correct database view with RMAN logs, storage observations, alerting, and recovery testing. Use V$BACKUP for user-managed backup mode, V$DATAFILE_HEADER for file-header diagnostics, and RMAN-specific views for RMAN jobs. The next lesson examines the backup implications of logging and nologging modes.

Monitoring Database Backup - Quiz