Physical Backups  «Prev  Next»

Lesson 10 Monitoring an open database backup
Objective Explain how to Check the State of an Open Database Backup.

Check State of Open Database Backup

In Oracle 19c, you can check the state of an "Open Database Backup" (also known as a hot backup) using the V$BACKUP and V$DATAFILE views. These views provide information about the backup status of datafiles in the database. Additionally, Recovery Manager (RMAN) can be used to monitor backups, for example, with commands like LIST BACKUP or REPORT SCHEMA, but this lesson focuses on SQL-based monitoring using dynamic performance views.
Steps to Check the State of an Open Database Backup:
  1. Connect to the Database:

    Connect to the Oracle database using SQL*Plus or any other SQL client with a user that has the necessary privileges (e.g., SYSDBA).

    sqlplus / as sysdba
  2. Query the V$BACKUP View:

    The V$BACKUP view contains information about the backup status of each datafile. The STATUS column indicates whether a datafile is currently being backed up.

    SELECT file#, status, change#, time
FROM v$backup;
    • file#: The file number of the datafile.
    • status: The backup status of the datafile.
      • ACTIVE indicates that the datafile is currently being backed up.
      • NOT ACTIVE indicates that the datafile is not being backed up.
    • change#: The system change number (SCN) at which the backup started.
    • time: The time when the backup started (note: the format depends on the database's NLS_DATE_FORMAT setting, and times are typically in the database server's time zone).
  3. Query the V$DATAFILE View:

    The V$DATAFILE view provides additional information about the datafiles, such as their names and current status.

    SELECT file#, name, status
FROM v$datafile;
    • file#: The file number of the datafile.
    • name: The name of the datafile.
    • status: The current status of the datafile (e.g., ONLINE, OFFLINE, etc.).
  4. Combine Information from Both Views:

    You can join the V$BACKUP and V$DATAFILE views to get a comprehensive view of the backup status along with the datafile names.


SELECT b.file#, d.name, b.status, b.change#, b.time
FROM v$backup b
JOIN v$datafile d ON b.file# = d.file#;

This query will show which datafiles are currently being backed up (ACTIVE status) and which are not (NOT ACTIVE), along with the datafile names and other relevant information.
Example Output: 
FILE#  NAME                              STATUS     CHANGE#  TIME
------ -------------------------------- ---------- -------- ---------
1      /u01/oradata/ORCL/system01.dbf    ACTIVE     1234567  2023-10-01 12:00:00
2      /u01/oradata/ORCL/sysaux01.dbf    NOT ACTIVE 0
3      /u01/oradata/ORCL/undotbs01.dbf   NOT ACTIVE 0
4      /u01/oradata/ORCL/users01.dbf     ACTIVE     1234568  2023-10-01 12:05:00

Interpretation:
  • FILE# 1 and FILE# 4 are currently being backed up (ACTIVE status).
  • FILE# 2 and FILE# 3 are not being backed up (NOT ACTIVE status).

Additional Notes:
  • The V$BACKUP view is particularly useful during hot backups (open database backups) to monitor which datafiles are currently in backup mode.
  • If you are performing a cold backup (database shutdown), the V$BACKUP view will not show any active backups since the database is not open.
  • For complex scenarios (e.g., incomplete or failed backups, or multiple concurrent backups), you may need to check additional views like V$SESSION_LONGOPS for long-running backup operations or use RMAN commands to diagnose issues.

By using these queries, you can effectively monitor the state of an open database backup in Oracle 19c.
When you perform an open database backup, you can obtain information about the status of datafiles by querying the dynamic data dictionary views
  1. V$BACKUP and
  2. V$DATAFILE_HEADER.

If you are trying to shut down the database while a tablespace backup is in process, you will get error messages indicating that your statement cannot be processed. In this case, you can query these views to find the name of the datafile(s) currently in backup mode. The code examples in this lesson show what you will see when you use data dictionary views and how they help you monitor the backup process.
V$BACKUP view 1 
SQL> desc V$backup
Name                            Null?    Type
------------------------------- -------- ----
FILE#                                    NUMBER
STATUS                                   VARCHAR2(18)
CHANGE#                                  NUMBER
TIME                                     DATE

The V$BACKUP view tells you which files are in backup mode. The code below is an example of the structure of the V$BACKUP view.
V$BACKUP view 2 
SQL> select * from V$backup;
     FILE#      STATUS                CHANGE#      TIME
----------   ------------------    ----------  ---------
         1   NOT ACTIVE                  0
         2   NOT ACTIVE                  0
         3   NOT ACTIVE                  0
         4   NOT ACTIVE                  0
         5   NOT ACTIVE                  0
         6   NOT ACTIVE                  0
         7   NOT ACTIVE                  0
         8   NOT ACTIVE                  0
         9   NOT ACTIVE                  0
        10   NOT ACTIVE                  0
        11   NOT ACTIVE                  0
     FILE#      STATUS                 CHANGE#    TIME
----------   ------------------     ----------  ---------
        12     ACTIVE                 130521   14-JAN-2020
12 rows selected.

When the following command is issued, 
ALTER TABLESPACE BEGIN BACKUP;

you can see the status column change from NOT ACTIVE to ACTIVE for the tablespace that is currently being backed up. The STATUS column value will change to NOT ACTIVE once the datafile is backed up.

V$DATAFILE_HEADER view 
SQL> desc V$datafile_header
 Name                            Null?    Type
 ------------------------------- -------- ----
 FILE#                                    NUMBER
 STATUS                                   VARCHAR2(7)
 ERROR                                    VARCHAR2(18)
 FORMAT                                   NUMBER
 RECOVER                                  VARCHAR2(3)
 FUZZY                                    VARCHAR2(3)
 CREATION_CHANGE#                         NUMBER
 CREATION_TIME                            DATE
 TABLESPACE_NAME                          VARCHAR2(30)
 TS#                                      NUMBER
 RFILE#                                   NUMBER
 RESETLOGS_CHANGE#                        NUMBER
 RESETLOGS_TIME                           DATE
 CHECKPOINT_CHANGE#                       NUMBER
 CHECKPOINT_TIME                          DATE
 CHECKPOINT_COUNT                         NUMBER
 BYTES                                    NUMBER
 BLOCKS                                   NUMBER

You may have noticed that the V$BACKUP view does not contain the file name. More detailed information about datafiles in backup mode can be obtained by querying the V$DATAFILE_HEADER view. Below is the code for the structure of the V$DATAFILE_HEADER view. 
SQL> select name, status, fuzzy from V$datafile_header; 
NAME                                          STATUS   FUZZY
--------------------------------------------- -------  -----
/u02/oradata/DVL1/system_DVL1_01.dbf          ONLINE
/u02/oradata/DVL1/rbs_DVL1_01.dbf             ONLINE
/u02/oradata/DVL1/temp_DVL1_01.dbf            ONLINE
/u02/oradata/DVL1/tools_DVL1_01.dbf           ONLINE
/u02/oradata/DVL1/users_DVL1_01.dbf           ONLINE
/u02/oradata/DVL1/des_tab_DVL1.dbf            ONLINE
/u03/oradata/DVL1/des_idx_DVL1.dbf            ONLINE
/u02/oradata/DVL1/des_rbs_DVL1.dbf            ONLINE
/u02/oradata/DVL1/synchro_dvl1.dbf            ONLINE
/u02/oradata/DVL1/olant_dvl1.dbf              ONLINE
/u02/oradata/DVL1/celeris_dvl1.dbf            ONLINE
/u02/oradata/DVL1/isd_002_proj_dvl1.dbf       ONLINE    YES
12 rows selected.

When an 
ALTER TABLESPACE BEGIN BACKUP;

command is issued, the value in the FUZZY column for the tablespace's datafiles changes to YES to indicate that the corresponding files are in backup mode. The value of the FUZZY column changes to NULL when the ALTER TABLESPACE END BACKUP command is issued. The next lesson examines the backup implications of logging and nologging modes.

RMAN Backup and Recovery Guide

Monitoring Database Backup - Quiz

Click the Quiz link below to review your understanding of some control file database backup issues.
Monitoring Database Backup - Quiz


SEMrush Software 10 SEMrush Banner 10