Understand SCN movement during online user managed backup;

Before online backup, check current scn, system and datafile scn in controlfile and scn in datafile header.

As expected checkpoint scn are same in controlfile and datafile headers, and it is behind current scn.

SQL> select current_scn, checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
  325011036          325009912

SQL> select name,checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/system.270.675355175              325009912
+DBA_DATA/dbaprd/datafile/sysaux.269.675355177              325009912
+DBA_DATA/dbaprd/datafile/undotbs1.266.675355179            325009912
+DBA_DATA/dbaprd/datafile/undotbs2.264.675355187            325009912
+DBA_DATA/dbaprd/datafile/users.263.675355189               325009912
+DBA_DATA/dbaprd/datafile/xml_data.262.675355189            325009912


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%';

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/users.263.675355189               325009912


Start online tablespace backup

Oracle did a checkpoint on USERS tablespace and datafile only, and freeze the checkpoint scn on datafile header.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> select current_scn, checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
  325011196          325009912


SQL>  select name,checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/system.270.675355175              325009912
+DBA_DATA/dbaprd/datafile/sysaux.269.675355177              325009912
+DBA_DATA/dbaprd/datafile/undotbs1.266.675355179            325009912
+DBA_DATA/dbaprd/datafile/undotbs2.264.675355187            325009912
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011168
+DBA_DATA/dbaprd/datafile/xml_data.262.675355189            325009912


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%';

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011168


during the online backup checkpoint scn is freeze on datafile belongs to USERS tablespace 

SQL> alter system checkpoint;

System altered.

SQL> select current_scn, checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
  325011272          325011243

SQL>  select name,checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/system.270.675355175              325011243
+DBA_DATA/dbaprd/datafile/sysaux.269.675355177              325011243
+DBA_DATA/dbaprd/datafile/undotbs1.266.675355179            325011243
+DBA_DATA/dbaprd/datafile/undotbs2.264.675355187            325011243
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011168
+DBA_DATA/dbaprd/datafile/xml_data.262.675355189            325011243


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%';

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011168


END online tablespace backup;

Oracle advanced checkpoint scn on USERS tablespace and datafile only to be same as system checkpoint scn 


SQL> alter tablespace users end backup;

Tablespace altered.

SQL> select current_scn, checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
  325011488          325011243

SQL> select name,checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/system.270.675355175              325011243
+DBA_DATA/dbaprd/datafile/sysaux.269.675355177              325011243
+DBA_DATA/dbaprd/datafile/undotbs1.266.675355179            325011243
+DBA_DATA/dbaprd/datafile/undotbs2.264.675355187            325011243
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011243
+DBA_DATA/dbaprd/datafile/xml_data.262.675355189            325011243


9 rows selected.

SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%';

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011243