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

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 telling you 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 lines in the rest of this lesson show you 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 an 
ALTER TABLESPACE BEGIN BACKUP;

is issued, 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.

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