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.
1 comment:
Post a Comment