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.

Restoring a PDB from another CDB can increase your SCN

 I start with a 19c CDB called "ORCLCDB" and PDB "ORCLPDB1" that is present in it.

SQL> col name format a16
SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
2778483057 ORCLCDB             20906515

SQL> select dbid, guid, name, open_mode from v$pdbs;

      DBID GUID                             NAME             OPEN_MODE
---------- -------------------------------- ---------------- ----------
2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED         READ ONLY
4057974068 8812E29864F47615E0530100007FA424 ORCLPDB1         READ WRITE
 181927604 C07D1F2BA98C23D0E0530100007F7D34 RMANCAT          MOUNTED

SQL>
SQL> alter pluggable database orclpdb1 close;

Pluggable database altered.

SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Sep 24 09:39:55 2023
Version 19.12.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057)

RMAN> backup pluggable database orclpdb1 tag for_migration;

Starting backup at 24-SEP-23
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00034 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_tpcctab_jyl0mzsp_.dbf
input datafile file number=00009 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
input datafile file number=00026 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf
input datafile file number=00032 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf
channel ORA_DISK_1: starting piece 1 at 24-SEP-23
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00011 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
input datafile file number=00010 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
input datafile file number=00012 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
input datafile file number=00031 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf
channel ORA_DISK_2: starting piece 1 at 24-SEP-23
channel ORA_DISK_1: finished piece 1 at 24-SEP-23
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2023_09_24/o1_mf_nnndf_FOR_MIGRATION_ljz5825q_.bkp tag=FOR_MIGRATION comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_2: finished piece 1 at 24-SEP-23
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2023_09_24/o1_mf_nnndf_FOR_MIGRATION_ljz5828m_.bkp tag=FOR_MIGRATION comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03
Finished backup at 24-SEP-23

Starting Control File and SPFILE Autobackup at 24-SEP-23
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2023_09_24/o1_mf_s_1148377605_ljz585bw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-SEP-23

RMAN>
RMAN> quit


Recovery Manager complete.
oracle19c>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 24 09:48:04 2023
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter pluggable database orclpdb1 unplug into '/opt/oracle/product/19c/dbhome_1/orclpdb1.xml';

Pluggable database altered.

SQL> !ls -l /opt/oracle/product/19c/dbhome_1/orclpdb1.xml
-rw-r--r--. 1 oracle oinstall 12583 Sep 24 09:50 /opt/oracle/product/19c/dbhome_1/orclpdb1.xml

SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>

Now I go to the target CDB CDB2 and identify that there is no conflicting PDB.  I also find that it has a lower SCN.

SQL> col name format a16
SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
 837065713 CDB2                20664227

SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
 837065713 CDB2                20664231

SQL>
SQL> select * from v$pdbs
  2  /

    CON_ID       DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY
---------- --- --------------------------------------------------------------------------- ---------- ---------- ---------- --------
SNAPSHOT_PARENT_CON_ID APP APP APP APPLICATION_ROOT_CON_ID APP PRO LOCAL_UNDO   UNDO_SCN UNDO_TIME CREATION_ DIAGNOSTICS_SIZE
---------------------- --- --- --- ----------------------- --- --- ---------- ---------- --------- --------- ----------------
 PDB_COUNT AUDIT_FILES_SIZE   MAX_SIZE MAX_DIAGNOSTICS_SIZE MAX_AUDIT_SIZE LAST_CHANGE TEM
---------- ---------------- ---------- -------------------- -------------- ----------- ---
TENANT_ID
------------------------------------------------------------------------------------------------------------------------------------
UPGRADE_LEVEL GUID_BASE64
------------- ------------------------------
         2 2054948555 2054948555 88129263B99F4BBDE0530100007F7BDF
PDB$SEED
READ ONLY  NO  24-SEP-23 09.26.31.678 AM +08:00                                               1997190  957349888       8192 ENABLED
                       NO  NO  NO                          NO  NO           1        280           04-MAY-19                0
         0                0          0                    0              0 COMMON USER NO

            1 iBKSY7mfS73gUwEAAH973wA=

         3 2205532678 2205532678 E6BD9C73839C10BEE0550A00275FC834
PDBTMP
MOUNTED                                                                                      17541716  999292928       8192 ENABLED
                       NO  NO  NO                          NO  NO           1        280           21-AUG-22                0
         0                0          0                    0              0 COMMON USER NO

            1 5r2cc4OcEL7gVQoAJ1/INAA=

I then have the backups and the "closed" datafiles from the source CDB copied over to the target.  (The CREATE PLUGGABLE DATABASE command will not be using the RMAN Backups but the PDB Describle XML file and the "closed" datafiles).

oracle19c>pwd
/tmp/From_Source
oracle19c>ls -l
total 1882384
-rw-r-----. 1 oracle oinstall 1146109952 Sep 24 09:56 o1_mf_nnndf_FOR_MIGRATION_ljz5825q_.bkp
-rw-r-----. 1 oracle oinstall  758202368 Sep 24 09:56 o1_mf_nnndf_FOR_MIGRATION_ljz5828m_.bkp
-rw-r-----. 1 oracle oinstall   23232512 Sep 24 09:55 o1_mf_s_1148377605_ljz585bw_.bkp
-rw-r--r--. 1 oracle oinstall      12583 Sep 24 09:59 orclpdb1.xml
oracle19c>

I now prepare to copy the PDB to the target CDB2.. First I check for compatibility.. And then I plug in the PDB with a new name "ORCLPDB1_NEW"

SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
 837065713 CDB2                20664231
SQL> set serveroutput on
SQL> declare
  2   compatible constant varchar2(3) :=
  3     case dbms_pdb.check_plug_compatibility(
  4       pdb_descr_file=>'/tmp/From_Source/orclpdb1.xml',
  5       pdb_name=>'ORCLPDB1')
  6     when true then 'YES'
  7     else 'NO'
  8  END;
  9  begin
 10    dbms_output.put_line(compatible);
 11  end;
 12  /
YES

PL/SQL procedure successfully completed.

SQL>
SQL> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /opt/oracle/oradata

SQL>
SQL> create pluggable database orclpdb1_new
  2  using '/tmp/From_Source/orclpdb1.xml'
  3  copy;  --- copy will copy the datafiles to the location based on db_create_file_dest and CDB database name

Pluggable database created.

SQL>
SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
 837065713 CDB2                20910195

SQL>
SQL> select dbid, guid, name, open_mode from v$pdbs;

      DBID GUID                             NAME             OPEN_MODE
---------- -------------------------------- ---------------- ----------
2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED         READ ONLY
2205532678 E6BD9C73839C10BEE0550A00275FC834 PDBTMP           MOUNTED
4057974068 8812E29864F47615E0530100007FA424 ORCLPDB1_NEW     READ WRITE

SQL>

The SCN of the target CDB2 has now been set to the much higher 20910195 (that was 20664231 before I "plugged in" ORCLDB1 as ORCLDB1_NEW).  This is because it "read" the SCN from the headers of the datafiles that were plugged in.

I can go back to the source and drop the PDB.

SQL> select dbid, name, current_scn from v$database;

      DBID NAME             CURRENT_SCN
---------- ---------------- -----------
2778483057 ORCLCDB             20910076

SQL> select dbid, guid, name, open_mode from v$pdbs;

      DBID GUID                             NAME             OPEN_MODE
---------- -------------------------------- ---------------- ----------
2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED         READ ONLY
4057974068 8812E29864F47615E0530100007FA424 ORCLPDB1         MOUNTED
 181927604 C07D1F2BA98C23D0E0530100007F7D34 RMANCAT          MOUNTED

SQL> 
SQL> drop pluggable database orclpdb1 including datafiles;

Pluggable database dropped.

SQL> 
SQL> select dbid, guid, name, open_mode from v$pdbs;

      DBID GUID                             NAME             OPEN_MODE
---------- -------------------------------- ---------------- ----------
2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED         READ ONLY
 181927604 C07D1F2BA98C23D0E0530100007F7D34 RMANCAT          MOUNTED

SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>

So, I have "migrated" ORCLPDB1 from ORCLCDB to CDB2 and, in the process, have found that the SCN in CDB2 got reset to a much higher value, because it "inherited" the higher Checkpoint SCN that was in the datafiles of ORCLPDB1.

This behaviour, in my opinion, is similar to the SCN Synchronization that occurs when you use Database Links.

Also be aware of this when you use monitoring scripts that check for or rely on SCN values.

I can verify that all the datafiles (that are OPEN) in CDB2 can get stamped with the new SCN.

SQL> select con_id, file#, plugin_change#, checkpoint_change#
  2  from v$datafile
  3  order by 1,2
  4  /

    CON_ID      FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE#
---------- ---------- -------------- ------------------
         1          1              0           20658011
         1          3              0           20658011
         1          4              0           20658011
         1          7              0           20658011
         2          5              0           13768423
         2          6              0           13768423
         2          8              0           13768423
         3         58              0           20657657
         3         59              0           20657657
         3         60              0           20657657
         4         69              0           20908595
         4         70              0           20908595
         4         71              0           20908595
         4         72              0           20908595
         4         73              0           20908595
         4         74              0           20908595
         4         75              0           20908595
         4         76              0           20908595

18 rows selected.

SQL> alter system checkpoint;

System altered.

SQL> select con_id, file#, plugin_change#, checkpoint_change#
  2  from v$datafile
  3  order by 1,2
  4  /

    CON_ID      FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE#
---------- ---------- -------------- ------------------
         1          1              0           20912767
         1          3              0           20912767
         1          4              0           20912767
         1          7              0           20912767
         2          5              0           13768423
         2          6              0           13768423
         2          8              0           13768423
         3         58              0           20657657
         3         59              0           20657657
         3         60              0           20657657
         4         69              0           20912767
         4         70              0           20912767
         4         71              0           20912767
         4         72              0           20912767
         4         73              0           20912767
         4         74              0           20912767
         4         75              0           20912767
         4         76              0           20912767

18 rows selected.

SQL> col name format a16
SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME             OPEN_MODE
---------- ---------------- ----------
         2 PDB$SEED         READ ONLY
         3 PDBTMP           MOUNTED
         4 ORCLPDB1_NEW     READ WRITE

SQL> alter pluggable database pdbtmp open;

Pluggable database altered.

SQL> select con_id, file#, plugin_change#, checkpoint_change#
  2  from v$datafile
  3  order by 1,2
  4  /

    CON_ID      FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE#
---------- ---------- -------------- ------------------
         1          1              0           20912767
         1          3              0           20912767
         1          4              0           20912767
         1          7              0           20912767
         2          5              0           13768423
         2          6              0           13768423
         2          8              0           13768423
         3         58              0           20912888
         3         59              0           20912888
         3         60              0           20912888
         4         69              0           20912767
         4         70              0           20912767
         4         71              0           20912767
         4         72              0           20912767
         4         73              0           20912767
         4         74              0           20912767
         4         75              0           20912767
         4         76              0           20912767

18 rows selected.

SQL> alter system checkpoint;

System altered.

SQL> select con_id, file#, plugin_change#, checkpoint_change#
  2  from v$datafile
  3  order by 1,2
  4  /

    CON_ID      FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE#
---------- ---------- -------------- ------------------
         1          1              0           20912967
         1          3              0           20912967
         1          4              0           20912967
         1          7              0           20912967
         2          5              0           13768423
         2          6              0           13768423
         2          8              0           13768423
         3         58              0           20912967
         3         59              0           20912967
         3         60              0           20912967
         4         69              0           20912967
         4         70              0           20912967
         4         71              0           20912967
         4         72              0           20912967
         4         73              0           20912967
         4         74              0           20912967
         4         75              0           20912967
         4         76              0           20912967

18 rows selected.

SQL>
SQL> alter session set container=ORCLPDB1_NEW;

Session altered.
SQL> col name format a54
SQL> select name from v$datafile;

NAME
------------------------------------------------------
/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_system_ljz7d02h_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_sysaux_ljz7d02l_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_undotbs1_ljz7d02m_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_users_ljz7d02o_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_data_min_ljz7d02p_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_hemant_d_ljz7d02r_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_hemant_d_ljz7d02s_.dbf

/opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA
424/datafile/o1_mf_tpcctab_ljz7d02t_.dbf


8 rows selected.

SQL>

CON_ID=1 is Root and the datafiles get the new SCN after a Checkpoint.   Note these datafiles still had the "older" SCN 20658011 before I issued the Checkpoint.

CON_ID=2  is the SEED PDB so it does not get updated.

CON_ID=3 is PDTMP  at 20657657 and is stamped to 2091288 after I OPEN it.

CON_ID=4 is the ORCLPDB1_NEW that I plugged in today.

All the non-SEED datafiles are stamped again (to 20912967) after another Checkpoint.

The FILTER operation for an EXISTS query

 Let's assume a business case where we want to track customers who have ever placed orders with us.

(The business case could be extended to identify customers who have registered with us at least 3 years ago but have not placed any order in the last 12months --- they could either be targets for an advertising or promotions/discount campaign or customers to be retired, depending on the nature of our business and products.  But I'll keep the query simple here).

Name              Null?       Type            
_________________ ___________ _______________ 
CUST_ID           NOT NULL    NUMBER(6)       
CUST_NAME         NOT NULL    VARCHAR2(56)    
CUST_REGN_DATE    NOT NULL    DATE                                                     
SQL> select count(*) from customers;
                                                                                                                     
   COUNT(*)                                                                                                                   
___________                                                                                                                            
        100                                                                                                                                   
                                                                                                                                                   
SQL> desc sales_orders;
                                                                                                                                                                    
Name          Null?       Type          
_____________ ___________ _____________ 
ORDER_ID      NOT NULL    NUMBER(12)    
SALE_DATE                 DATE          
CUST_ID                   NUMBER(6)     
PRODUCT_ID                NUMBER(6)     
QUANTITY                  NUMBER(12)    
SQL> 
SQL> select count(*) from sales_orders;

   COUNT(*) 
___________ 
    1979580 

SQL> select count(distinct(cust_id)) from sales_orders;

   COUNT(DISTINCT(CUST_ID)) 
___________________________ 
                         99 

SQL> 
SQL> create table sale_made_tracker (cust_id number(6,0), sales_made varchar2(3));

Table SALE_MADE_TRACKER created.

SQL> 

Given that we have 100 customers and 1.98million sales orders, we want to add an entry to the SALE_MADE_TRACKER for a customer with a sales order.  However, this is an "on-demand" query that an analyst runs from a front-end, customer-by-customer for specific customers.  Thus, it is not executed as a batch for all 100 customers and 1.98million sales orders.

The developer likes to use Bind Variables and comes up with a query like this :

insert into sale_made_tracker
select cust_id, 'YES'
from customers a
where a.cust_name = :bind_cust_name
and exists
(select '1'
 from customers c, sales_orders s
 where s.cust_id=c.cust_id
 and c.cust_id = a.cust_id)
 

This allows the user to specify a customer name without knowing the cust_id when executing the query.

When testing this in 23c, I've had to add the NO_UNNEST Hint so that I could see the FILTER operation (similar to what I saw in a current 12.1 database):

 SQL> explain plan for
  2  insert into sale_made_tracker
  3  select cust_id, 'YES'
  4  from customers a
  5  where a.cust_name = :bind_cust_name
  6  and exists
  7  (select  '1' 
  8   from customers c, sales_orders s
  9   where s.cust_id=c.cust_id
 10   and c.cust_id = a.cust_id) 
 11  /

Explained.

SQL> 
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                 
_________________________________________________________________________________________________ 
Plan hash value: 1657757803                                                                       
                                                                                                  
----------------------------------------------------------------------------------------------    
| Id  | Operation                | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    
----------------------------------------------------------------------------------------------    
|   0 | INSERT STATEMENT         |                   |     1 |    45 |  2447   (1)| 00:00:01 |    
|   1 |  LOAD TABLE CONVENTIONAL | SALE_MADE_TRACKER |       |       |            |          |    
|   2 |   NESTED LOOPS SEMI      |                   |     1 |    45 |  2447   (1)| 00:00:01 |    
|*  3 |    TABLE ACCESS FULL     | CUSTOMERS         |     1 |    43 |     3   (0)| 00:00:01 |    
|   4 |    VIEW PUSHED PREDICATE | VW_SQ_1           |   208 |   416 |  2444   (1)| 00:00:01 |    
|   5 |     NESTED LOOPS         |                   | 20828 |   528K|  2444   (1)| 00:00:01 |    
|*  6 |      INDEX UNIQUE SCAN   | SYS_C0012587      |     1 |    13 |     1   (0)| 00:00:01 |    
|*  7 |      TABLE ACCESS FULL   | SALES_ORDERS      | 20828 |   264K|  2443   (1)| 00:00:01 |    
----------------------------------------------------------------------------------------------    
                                                                                                  
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
                                                                                                  
   3 - filter("A"."CUST_NAME"=:BIND_CUST_NAME)                                                    
   6 - access("C"."CUST_ID"="A"."CUST_ID")                                                        
   7 - filter("S"."CUST_ID"="A"."CUST_ID")                                                        
                                                                                                  
Note                                                                                              
-----                                                                                             

PLAN_TABLE_OUTPUT                                           
___________________________________________________________ 
   - dynamic statistics used: dynamic sampling (level=2)    

25 rows selected. 

SQL> delete plan_table;

15 rows deleted.

SQL> 
SQL> explain plan for
  2  insert into sale_made_tracker
  3  select cust_id, 'YES'
  4  from customers a
  5  where a.cust_name = :bind_cust_name
  6  and exists
  7  (select /*+ NO_UNNEST */ '1' 
  8   from customers c, sales_orders s
  9   where s.cust_id=c.cust_id
 10   and c.cust_id = a.cust_id) 
 11  /

Explained.

SQL> 
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                 
_________________________________________________________________________________________________ 
Plan hash value: 2963538242                                                                       
                                                                                                  
----------------------------------------------------------------------------------------------    
| Id  | Operation                | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    
----------------------------------------------------------------------------------------------    
|   0 | INSERT STATEMENT         |                   |     1 |    43 |     6   (0)| 00:00:01 |    
|   1 |  LOAD TABLE CONVENTIONAL | SALE_MADE_TRACKER |       |       |            |          |    
|*  2 |   FILTER                 |                   |       |       |            |          |    
|*  3 |    TABLE ACCESS FULL     | CUSTOMERS         |     1 |    43 |     3   (0)| 00:00:01 |    
|   4 |    NESTED LOOPS          |                   |     1 |    26 |     3   (0)| 00:00:01 |    
|*  5 |     INDEX UNIQUE SCAN    | SYS_C0012587      |     1 |    13 |     1   (0)| 00:00:01 |    
|*  6 |     TABLE ACCESS FULL    | SALES_ORDERS      | 20828 |   264K|     2   (0)| 00:00:01 |    
----------------------------------------------------------------------------------------------    
                                                                                                  
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
                                                                                                  
   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "SALES_ORDERS" "S","CUSTOMERS"              
              "C" WHERE "C"."CUST_ID"=:B1 AND "S"."CUST_ID"=:B2))                                 
   3 - filter("A"."CUST_NAME"=:BIND_CUST_NAME)                                                    
   5 - access("C"."CUST_ID"=:B1)                                                                  
   6 - filter("S"."CUST_ID"=:B1)                                                                  
                                                                                                  
Note                                                                                              

PLAN_TABLE_OUTPUT                                           
___________________________________________________________ 
-----                                                       
   - dynamic statistics used: dynamic sampling (level=2)    

26 rows selected. 

SQL> delete plan_table;

7 rows deleted.

SQL> 

So, for the purpose of this demo of the FILTER operation for an EXISTS clause, I will use the second form of the query.

If I run the query for customer name "HEMANT" :

 SQL> exec :bind_cust_name := 'HEMANT'

PL/SQL procedure successfully completed.

SQL> 
SQL> insert /*+ gather_plan_statistics */ into sale_made_tracker
  2  select cust_id, 'YES'
  3  from customers a
  4  where a.cust_name = :bind_cust_name
  5  and exists
  6  (select /*+ NO_UNNEST */ '1' 
  7   from customers c, sales_orders s
  8   where s.cust_id=c.cust_id
  9   and c.cust_id = a.cust_id) 
 10  /

1 row inserted.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                           
___________________________________________________________________________________________________________ 
SQL_ID  1j3jggfsurwg4, child number 0                                                                       
-------------------------------------                                                                       
insert /*+ gather_plan_statistics */ into sale_made_tracker select                                          
cust_id, 'YES' from customers a where a.cust_name = :bind_cust_name and                                     
exists (select /*+ NO_UNNEST */ '1'   from customers c, sales_orders s                                      
where s.cust_id=c.cust_id  and c.cust_id = a.cust_id)                                                       
                                                                                                            
Plan hash value: 2963538242                                                                                 
                                                                                                            
--------------------------------------------------------------------------------------------------------    
| Id  | Operation                | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
--------------------------------------------------------------------------------------------------------    
|   0 | INSERT STATEMENT         |                   |      1 |        |      0 |00:00:00.01 |     195 |    
|   1 |  LOAD TABLE CONVENTIONAL | SALE_MADE_TRACKER |      1 |        |      0 |00:00:00.01 |     195 |    
|*  2 |   FILTER                 |                   |      1 |        |      1 |00:00:00.01 |     193 |    
|*  3 |    TABLE ACCESS FULL     | CUSTOMERS         |      1 |      1 |      1 |00:00:00.01 |       7 |    
|   4 |    NESTED LOOPS          |                   |      1 |      1 |      1 |00:00:00.01 |     186 |    
|*  5 |     INDEX UNIQUE SCAN    | SYS_C0012587      |      1 |      1 |      1 |00:00:00.01 |       1 |    
|*  6 |     TABLE ACCESS FULL    | SALES_ORDERS      |      1 |  20828 |      1 |00:00:00.01 |     185 |    
--------------------------------------------------------------------------------------------------------    
                                                                                                            
Predicate Information (identified by operation id):                                                         
---------------------------------------------------                                                         
                                                                                                            

PLAN_TABLE_OUTPUT                                           
___________________________________________________________ 
   2 - filter( IS NOT NULL)                                 
   3 - filter("A"."CUST_NAME"=:BIND_CUST_NAME)              
   5 - access("C"."CUST_ID"=:B1)                            
   6 - filter("S"."CUST_ID"=:B1)                            
                                                            
Note                                                        
-----                                                       
   - dynamic statistics used: dynamic sampling (level=2)    
                                                            

33 rows selected. 

SQL> 

Which shows that a total of 185 blocks were read from the SALES_ORDERS table for "HEMANT"'s orders.  Plus 1 block for the Index Unique Scan and 7 blocks for the Full Table Scan of CUSOMERS (totally 193 blocks for the SELECT) {Ignore the A-Rows being 0 for the actual INSERT in the Plan output, the execution shows that 1 row was inserted)

But if I run the query for  customer name "LARRY" :

SQL> exec :bind_cust_name := 'LARRY'

PL/SQL procedure successfully completed.

SQL> 
SQL> insert /*+ gather_plan_statistics */ into sale_made_tracker
  2  select cust_id, 'YES'
  3  from customers a
  4  where a.cust_name = :bind_cust_name
  5  and exists
  6  (select /*+ NO_UNNEST */ '1' 
  7   from customers c, sales_orders s
  8   where s.cust_id=c.cust_id
  9   and c.cust_id = a.cust_id) 
 10  /

0 rows inserted.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                           
___________________________________________________________________________________________________________ 
SQL_ID  1j3jggfsurwg4, child number 0                                                                       
-------------------------------------                                                                       
insert /*+ gather_plan_statistics */ into sale_made_tracker select                                          
cust_id, 'YES' from customers a where a.cust_name = :bind_cust_name and                                     
exists (select /*+ NO_UNNEST */ '1'   from customers c, sales_orders s                                      
where s.cust_id=c.cust_id  and c.cust_id = a.cust_id)                                                       
                                                                                                            
Plan hash value: 2963538242                                                                                 
                                                                                                            
--------------------------------------------------------------------------------------------------------    
| Id  | Operation                | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
--------------------------------------------------------------------------------------------------------    
|   0 | INSERT STATEMENT         |                   |      1 |        |      0 |00:00:00.06 |    8723 |    
|   1 |  LOAD TABLE CONVENTIONAL | SALE_MADE_TRACKER |      1 |        |      0 |00:00:00.06 |    8723 |    
|*  2 |   FILTER                 |                   |      1 |        |      0 |00:00:00.06 |    8723 |    
|*  3 |    TABLE ACCESS FULL     | CUSTOMERS         |      1 |      1 |      1 |00:00:00.01 |       7 |    
|   4 |    NESTED LOOPS          |                   |      1 |      1 |      0 |00:00:00.06 |    8716 |    
|*  5 |     INDEX UNIQUE SCAN    | SYS_C0012587      |      1 |      1 |      1 |00:00:00.01 |       1 |    
|*  6 |     TABLE ACCESS FULL    | SALES_ORDERS      |      1 |  20828 |      0 |00:00:00.06 |    8715 |    
--------------------------------------------------------------------------------------------------------    
                                                                                                            
Predicate Information (identified by operation id):                                                         
---------------------------------------------------                                                         
                                                                                                            

PLAN_TABLE_OUTPUT                                           
___________________________________________________________ 
   2 - filter( IS NOT NULL)                                 
   3 - filter("A"."CUST_NAME"=:BIND_CUST_NAME)              
   5 - access("C"."CUST_ID"=:B1)                            
   6 - filter("S"."CUST_ID"=:B1)                            
                                                            
Note                                                        
-----                                                       
   - dynamic statistics used: dynamic sampling (level=2)    
                                                            

33 rows selected. 

SQL>

Which shows that a total of 8,715 blocks were read for "LARRY"'s orders from the SALES_ORDERS table alone but actual rows returned was 0 -- so no rows were inserted into SALE_MADE_TRACKER.

Both "HEMANT" and "LARRY" exist in the CUSTOMERS table.

SQL> select cust_id, cust_name 
  2  from customers
  3  where cust_id in (1,2)
  4* /

   CUST_ID CUST_NAME    
__________ ____________ 
         1 HEMANT       
         2 LARRY        

SQL>
SQL> select cust_id, count(*)
  2   from sales_orders
  3  where cust_id in (1,2)
  4  group by cust_id
  5  order by 1
  6* /

   CUST_ID    COUNT(*) 
__________ ___________ 
         1       20502 

SQL> 
SQL> select /*+ gather_plan_statistics FULL (s) */ count(*)
  2  from sales_orders s
  3* /

   COUNT(*) 
___________ 
    1979580 

SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                
________________________________________________________________________________________________ 
SQL_ID  042jxgv8y5xny, child number 0                                                            
-------------------------------------                                                            
select /*+ gather_plan_statistics FULL (s) */ count(*) from                                      
sales_orders s                                                                                   
                                                                                                 
Plan hash value: 1409771706                                                                      
                                                                                                 
---------------------------------------------------------------------------------------------    
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
---------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |              |      1 |        |      1 |00:00:00.03 |    8715 |    
|   1 |  SORT AGGREGATE    |              |      1 |      1 |      1 |00:00:00.03 |    8715 |    
|   2 |   TABLE ACCESS FULL| SALES_ORDERS |      1 |   2082K|   1979K|00:00:00.02 |    8715 |    
---------------------------------------------------------------------------------------------    
                                                                                                 
Note                                                                                             
-----                                                                                            
   - dynamic statistics used: dynamic sampling (level=2)                                         
                                                                                                 

19 rows selected. 

SQL> 

While "HEMANT" has 20,502 sales orders, "LARRY", despite being a registered customer, has not issued any order.

The EXISTS query for "HEMANT" had to read only 185 blocks from the SALES_ORDERS table until it was *stopped* by the FILTER operation because the FILTER here stops the subquery as soon as the first row is returned (which you can also see as actual rows being "1" from the SALES_ORDERS table) -- as soon as the subquery returns TRUE.

"LARRY" has no orders so the EXISTS query ran through the whole SALES_ORDERS table to read 8,715 blocks.

Side note on the use of the Bind Variable here :  The fact that the developer used Bind Variables means that the Estimated Rows from the SALES_ORDERS table  in the Execution Plan was always going to be the same -- no matter which customer name was queried for -- because it prevents the use of Histograms (if they existed) on the tale.