15 October, 2023

Do DataFiles get updated when in BACKUP mode ?

 The Oracle feature allowing the DBA to put a database in "BACKUP" mode is useful for environments where non-RMAN methods / storage snapshots etc are used to make database backups.

However, there seems to be misconception that datafiles are not updated when the database is in BACKUP mode.

Here is a quick demonstration to disprove it.  I first place the database in BACKUP mode and then, from other sessions, start-off transactions and checkpoints and switch logfiles.  I then check if the datafile headers are updated and whether the datafiles themselves are updated.



17:55:28 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1240322

17:55:34 SQL> alter system checkpoint;

System altered.

17:55:42 SQL> @get_dbf_chk_scns

     FILE# NAME                                                     CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- -------------------------------------------------------- ------------------ ------------------
         1 /opt/oracle/oradata/HEMANT/system.dbf                               1240326 15-OCT-23 17:55:42
         2 /opt/oracle/oradata/HEMANT/sysaux.dbf                               1240326 15-OCT-23 17:55:42
         3 /opt/oracle/oradata/HEMANT/undotbs.dbf                              1240326 15-OCT-23 17:55:42
         4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf             1240326 15-OCT-23 17:55:42
         5 /opt/oracle/oradata/HEMANT/indx01.dbf                               1240326 15-OCT-23 17:55:42
         6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf                              1240326 15-OCT-23 17:55:42
        10 /opt/oracle/oradata/HEMANT/indx02.dbf                               1240326 15-OCT-23 17:55:42
        11 /opt/oracle/oradata/HEMANT/indx03.dbf                               1240326 15-OCT-23 17:55:42

8 rows selected.

17:56:01 SQL> alter database begin backup;

Database altered.

17:56:10 SQL> @get_dbf_chk_scns

     FILE# NAME                                                     CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- -------------------------------------------------------- ------------------ ------------------
         1 /opt/oracle/oradata/HEMANT/system.dbf                               1240339 15-OCT-23 17:56:10
         2 /opt/oracle/oradata/HEMANT/sysaux.dbf                               1240339 15-OCT-23 17:56:10
         3 /opt/oracle/oradata/HEMANT/undotbs.dbf                              1240339 15-OCT-23 17:56:10
         4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf             1240339 15-OCT-23 17:56:10
         5 /opt/oracle/oradata/HEMANT/indx01.dbf                               1240339 15-OCT-23 17:56:10
         6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf                              1240339 15-OCT-23 17:56:10
        10 /opt/oracle/oradata/HEMANT/indx02.dbf                               1240339 15-OCT-23 17:56:10
        11 /opt/oracle/oradata/HEMANT/indx03.dbf                               1240339 15-OCT-23 17:56:10

8 rows selected.

17:56:18 SQL>


The BEGIN BACKUP causes a Checkpoint.  Now I start off transactions and log file switches and checkpoints from other sessions and monitor the state here.



17:59:15 SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/archivelog/HEMANT
Oldest online log sequence     118
Next log sequence to archive   120
Current log sequence           120
17:59:18 SQL> @get_dbf_chk_scns

     FILE# NAME                                                     CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- -------------------------------------------------------- ------------------ ------------------
         1 /opt/oracle/oradata/HEMANT/system.dbf                               1240339 15-OCT-23 17:56:10
         2 /opt/oracle/oradata/HEMANT/sysaux.dbf                               1240339 15-OCT-23 17:56:10
         3 /opt/oracle/oradata/HEMANT/undotbs.dbf                              1240339 15-OCT-23 17:56:10
         4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf             1240339 15-OCT-23 17:56:10
         5 /opt/oracle/oradata/HEMANT/indx01.dbf                               1240339 15-OCT-23 17:56:10
         6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf                              1240339 15-OCT-23 17:56:10
        10 /opt/oracle/oradata/HEMANT/indx02.dbf                               1240339 15-OCT-23 17:56:10
        11 /opt/oracle/oradata/HEMANT/indx03.dbf                               1240339 15-OCT-23 17:56:10

8 rows selected.

17:59:25 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf
-rw-r-----. 1 oracle oinstall   5251072 Oct 15 17:56 /opt/oracle/oradata/HEMANT/HR_DATA.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx02.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx03.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 17:52 /opt/oracle/oradata/HEMANT/redo01.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 17:58 /opt/oracle/oradata/HEMANT/redo02.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 17:58 /opt/oracle/oradata/HEMANT/redo03.dbf
-rw-r-----. 1 oracle oinstall 419438592 Oct 15 17:56 /opt/oracle/oradata/HEMANT/sysaux.dbf
-rw-r-----. 1 oracle oinstall 566239232 Oct 15 17:56 /opt/oracle/oradata/HEMANT/system.dbf
-rw-r-----. 1 oracle oinstall 104865792 Oct 15 17:56 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
-rw-r-----. 1 oracle oinstall 209723392 Oct 15 17:56 /opt/oracle/oradata/HEMANT/undotbs.dbf

17:59:38 SQL>
18:01:04 SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/archivelog/HEMANT
Oldest online log sequence     119
Next log sequence to archive   121
Current log sequence           121
18:01:17 SQL> @get_dbf_chk_scns

     FILE# NAME                                                     CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- -------------------------------------------------------- ------------------ ------------------
         1 /opt/oracle/oradata/HEMANT/system.dbf                               1240339 15-OCT-23 17:56:10
         2 /opt/oracle/oradata/HEMANT/sysaux.dbf                               1240339 15-OCT-23 17:56:10
         3 /opt/oracle/oradata/HEMANT/undotbs.dbf                              1240339 15-OCT-23 17:56:10
         4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf             1240339 15-OCT-23 17:56:10
         5 /opt/oracle/oradata/HEMANT/indx01.dbf                               1240339 15-OCT-23 17:56:10
         6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf                              1240339 15-OCT-23 17:56:10
        10 /opt/oracle/oradata/HEMANT/indx02.dbf                               1240339 15-OCT-23 17:56:10
        11 /opt/oracle/oradata/HEMANT/indx03.dbf                               1240339 15-OCT-23 17:56:10

8 rows selected.

18:01:25 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf
-rw-r-----. 1 oracle oinstall   5251072 Oct 15 17:56 /opt/oracle/oradata/HEMANT/HR_DATA.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx02.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx03.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:01 /opt/oracle/oradata/HEMANT/redo01.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 17:58 /opt/oracle/oradata/HEMANT/redo02.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:00 /opt/oracle/oradata/HEMANT/redo03.dbf
-rw-r-----. 1 oracle oinstall 419438592 Oct 15 17:56 /opt/oracle/oradata/HEMANT/sysaux.dbf
-rw-r-----. 1 oracle oinstall 566239232 Oct 15 18:00 /opt/oracle/oradata/HEMANT/system.dbf
-rw-r-----. 1 oracle oinstall 104865792 Oct 15 17:56 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
-rw-r-----. 1 oracle oinstall 209723392 Oct 15 18:01 /opt/oracle/oradata/HEMANT/undotbs.dbf

18:01:34 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1240837

18:01:44 SQL>
18:01:44 SQL> !sleep 120

18:04:00 SQL>
18:04:04 SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/archivelog/HEMANT
Oldest online log sequence     122
Next log sequence to archive   124
Current log sequence           124
18:04:10 SQL> @get_dbf_chk_scns

     FILE# NAME                                                     CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- -------------------------------------------------------- ------------------ ------------------
         1 /opt/oracle/oradata/HEMANT/system.dbf                               1240339 15-OCT-23 17:56:10
         2 /opt/oracle/oradata/HEMANT/sysaux.dbf                               1240339 15-OCT-23 17:56:10
         3 /opt/oracle/oradata/HEMANT/undotbs.dbf                              1240339 15-OCT-23 17:56:10
         4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf             1240339 15-OCT-23 17:56:10
         5 /opt/oracle/oradata/HEMANT/indx01.dbf                               1240339 15-OCT-23 17:56:10
         6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf                              1240339 15-OCT-23 17:56:10
        10 /opt/oracle/oradata/HEMANT/indx02.dbf                               1240339 15-OCT-23 17:56:10
        11 /opt/oracle/oradata/HEMANT/indx03.dbf                               1240339 15-OCT-23 17:56:10

8 rows selected.

18:04:16 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf
-rw-r-----. 1 oracle oinstall   5251072 Oct 15 18:03 /opt/oracle/oradata/HEMANT/HR_DATA.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:03 /opt/oracle/oradata/HEMANT/indx01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:03 /opt/oracle/oradata/HEMANT/indx02.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:03 /opt/oracle/oradata/HEMANT/indx03.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:03 /opt/oracle/oradata/HEMANT/redo01.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:03 /opt/oracle/oradata/HEMANT/redo02.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:03 /opt/oracle/oradata/HEMANT/redo03.dbf
-rw-r-----. 1 oracle oinstall 419438592 Oct 15 18:03 /opt/oracle/oradata/HEMANT/sysaux.dbf
-rw-r-----. 1 oracle oinstall 566239232 Oct 15 18:03 /opt/oracle/oradata/HEMANT/system.dbf
-rw-r-----. 1 oracle oinstall 104865792 Oct 15 18:03 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
-rw-r-----. 1 oracle oinstall 209723392 Oct 15 18:03 /opt/oracle/oradata/HEMANT/undotbs.dbf

18:04:24 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1242436

18:04:34 SQL>
18:05:13 SQL> !sleep 120

18:07:15 SQL>
18:07:17 SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/archivelog/HEMANT
Oldest online log sequence     124
Next log sequence to archive   126
Current log sequence           126
18:07:20 SQL> alter system archive log current;

System altered.

18:07:30 SQL> @get_dbf_chk_scns

     FILE# NAME                                                     CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- -------------------------------------------------------- ------------------ ------------------
         1 /opt/oracle/oradata/HEMANT/system.dbf                               1240339 15-OCT-23 17:56:10
         2 /opt/oracle/oradata/HEMANT/sysaux.dbf                               1240339 15-OCT-23 17:56:10
         3 /opt/oracle/oradata/HEMANT/undotbs.dbf                              1240339 15-OCT-23 17:56:10
         4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf             1240339 15-OCT-23 17:56:10
         5 /opt/oracle/oradata/HEMANT/indx01.dbf                               1240339 15-OCT-23 17:56:10
         6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf                              1240339 15-OCT-23 17:56:10
        10 /opt/oracle/oradata/HEMANT/indx02.dbf                               1240339 15-OCT-23 17:56:10
        11 /opt/oracle/oradata/HEMANT/indx03.dbf                               1240339 15-OCT-23 17:56:10

8 rows selected.

18:07:36 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf
-rw-r-----. 1 oracle oinstall   5251072 Oct 15 18:07 /opt/oracle/oradata/HEMANT/HR_DATA.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:07 /opt/oracle/oradata/HEMANT/indx01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:07 /opt/oracle/oradata/HEMANT/indx02.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:07 /opt/oracle/oradata/HEMANT/indx03.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:07 /opt/oracle/oradata/HEMANT/redo01.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:06 /opt/oracle/oradata/HEMANT/redo02.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:07 /opt/oracle/oradata/HEMANT/redo03.dbf
-rw-r-----. 1 oracle oinstall 419438592 Oct 15 18:07 /opt/oracle/oradata/HEMANT/sysaux.dbf
-rw-r-----. 1 oracle oinstall 566239232 Oct 15 18:07 /opt/oracle/oradata/HEMANT/system.dbf
-rw-r-----. 1 oracle oinstall 104865792 Oct 15 18:07 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
-rw-r-----. 1 oracle oinstall 209723392 Oct 15 18:07 /opt/oracle/oradata/HEMANT/undotbs.dbf

18:07:43 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1242872

18:07:50 SQL>
18:07:50 SQL> alter system checkpoint;

System altered.

18:08:23 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1243044

18:08:29 SQL> @get_dbf_chk_scns

     FILE# NAME                                                     CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- -------------------------------------------------------- ------------------ ------------------
         1 /opt/oracle/oradata/HEMANT/system.dbf                               1240339 15-OCT-23 17:56:10
         2 /opt/oracle/oradata/HEMANT/sysaux.dbf                               1240339 15-OCT-23 17:56:10
         3 /opt/oracle/oradata/HEMANT/undotbs.dbf                              1240339 15-OCT-23 17:56:10
         4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf             1240339 15-OCT-23 17:56:10
         5 /opt/oracle/oradata/HEMANT/indx01.dbf                               1240339 15-OCT-23 17:56:10
         6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf                              1240339 15-OCT-23 17:56:10
        10 /opt/oracle/oradata/HEMANT/indx02.dbf                               1240339 15-OCT-23 17:56:10
        11 /opt/oracle/oradata/HEMANT/indx03.dbf                               1240339 15-OCT-23 17:56:10

8 rows selected.

18:08:35 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf
-rw-r-----. 1 oracle oinstall   5251072 Oct 15 18:08 /opt/oracle/oradata/HEMANT/HR_DATA.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:08 /opt/oracle/oradata/HEMANT/indx01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:08 /opt/oracle/oradata/HEMANT/indx02.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:08 /opt/oracle/oradata/HEMANT/indx03.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:08 /opt/oracle/oradata/HEMANT/redo01.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:08 /opt/oracle/oradata/HEMANT/redo02.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:07 /opt/oracle/oradata/HEMANT/redo03.dbf
-rw-r-----. 1 oracle oinstall 419438592 Oct 15 18:08 /opt/oracle/oradata/HEMANT/sysaux.dbf
-rw-r-----. 1 oracle oinstall 566239232 Oct 15 18:08 /opt/oracle/oradata/HEMANT/system.dbf
-rw-r-----. 1 oracle oinstall 104865792 Oct 15 18:08 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
-rw-r-----. 1 oracle oinstall 209723392 Oct 15 18:08 /opt/oracle/oradata/HEMANT/undotbs.dbf

18:08:41 SQL>


I now, finally, issue an END BACKUP and check the status.


18:09:45 SQL> alter database end backup;

Database altered.

18:09:51 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1244942

18:09:58 SQL> @get_dbf_chk_scns

     FILE# NAME                                                     CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- -------------------------------------------------------- ------------------ ------------------
         1 /opt/oracle/oradata/HEMANT/system.dbf                               1243041 15-OCT-23 18:08:23
         2 /opt/oracle/oradata/HEMANT/sysaux.dbf                               1243041 15-OCT-23 18:08:23
         3 /opt/oracle/oradata/HEMANT/undotbs.dbf                              1243041 15-OCT-23 18:08:23
         4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf             1243041 15-OCT-23 18:08:23
         5 /opt/oracle/oradata/HEMANT/indx01.dbf                               1243041 15-OCT-23 18:08:23
         6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf                              1243041 15-OCT-23 18:08:23
        10 /opt/oracle/oradata/HEMANT/indx02.dbf                               1243041 15-OCT-23 18:08:23
        11 /opt/oracle/oradata/HEMANT/indx03.dbf                               1243041 15-OCT-23 18:08:23

8 rows selected.

18:10:05 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf
-rw-r-----. 1 oracle oinstall   5251072 Oct 15 18:09 /opt/oracle/oradata/HEMANT/HR_DATA.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:09 /opt/oracle/oradata/HEMANT/indx01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:09 /opt/oracle/oradata/HEMANT/indx02.dbf
-rw-r-----. 1 oracle oinstall  10493952 Oct 15 18:09 /opt/oracle/oradata/HEMANT/indx03.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:08 /opt/oracle/oradata/HEMANT/redo01.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:09 /opt/oracle/oradata/HEMANT/redo02.dbf
-rw-r-----. 1 oracle oinstall  10486272 Oct 15 18:09 /opt/oracle/oradata/HEMANT/redo03.dbf
-rw-r-----. 1 oracle oinstall 419438592 Oct 15 18:09 /opt/oracle/oradata/HEMANT/sysaux.dbf
-rw-r-----. 1 oracle oinstall 566239232 Oct 15 18:09 /opt/oracle/oradata/HEMANT/system.dbf
-rw-r-----. 1 oracle oinstall 104865792 Oct 15 18:09 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
-rw-r-----. 1 oracle oinstall 209723392 Oct 15 18:09 /opt/oracle/oradata/HEMANT/undotbs.dbf

18:10:10 SQL>


So, while the database was in BACKUP mode (from 17:56:10 to 18:09:45) the datafiles were being updated continuously, Log Switches were happening and ArchiveLogs were being generated.  I also had ALTER SYSTEM CHECKPOINT commands being issued.

The  last Checkpoint command issued from the same session that issued the BEGIN BACKUP  that was marked as completed (obviously, all previous Checkpoints where also completed) when the  END BACKUP command from the same session.

So a BEGIN BACKUP
a.  Issues a Checkpoint
b. Does *not* stop updates to datafiles
The last Checkpoint is marked as completed when an END BACKUP is issued -- but that doesn't mean that datafiles are not being updated.