Oracle RMAN IMAGE COPY to BACKUPSET and using the BACKUPSET to perform restore

LAB: Backing up image copy as compressed backupset and restoring it to see if we are able to read the data again.

Step 1) Setup a test tablespace TBSPC_IMGCPY_COMP_TEST
Step 2) Setup a test table TBL_IMGCPY_COMP_TEST
Step 3) Put the tablespace in RO mode, such that we can do backup/restore all online in a noarchive db
Step 4) backup the tablespace as image copy
Step 5) backup the image copy as compressed backupset
Step 6) Remove the datafile
Step 7) Try to access the data and hit with the error
Step 8) Restore the tablespace from the backupset tag
Step 9) Try to access the data again


Step 1)
create tablespace TBSPC_IMGCPY_COMP_TEST datafile size 100m autoextend on next 100m;

Step 2)
create table SCOTT.TBL_IMGCPY_COMP_TEST tablespace TBSPC_IMGCPY_COMP_TEST as select * from SCOTT.TRAIN_SCHEDULE_LIST partition(TRN_19607);

Step 3)
SQL> alter tablespace TBSPC_IMGCPY_COMP_TEST read only;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where status='READ ONLY';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TBSPC_IMGCPY_COMP_TEST         READ ONLY

Step 4)
db_recovery_file_dest                string      /oradata_1/fast_recovery_area

backup as copy tablespace TBSPC_IMGCPY_COMP_TEST;


RMAN> backup as copy tablespace TBSPC_IMGCPY_COMP_TEST;

Starting backup at 18-NOV-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=01013 name=/oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf
output file name=/oradata_1/fast_recovery_area/RMN01TST/datafile/o1_mf_tbspc_im_fz1zggqg_.dbf tag=TAG20181118T135110 RECID=1 STAMP=992526673
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 18-NOV-18

Starting Control File and SPFILE Autobackup at 18-NOV-18
piece handle=/oradata_1/fast_recovery_area/RMN01TST/autobackup/2018_11_18/o1_mf_s_992526678_fz1zgqwx_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-NOV-18

RMAN> list backup of tablespace TBSPC_IMGCPY_COMP_TEST;

specification does not match any backup in the repository

RMAN> list copy of tablespace TBSPC_IMGCPY_COMP_TEST;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
1       1013 A 18-NOV-18       4826447    18-NOV-18
        Name: /oradata_1/fast_recovery_area/RMN01TST/datafile/o1_mf_tbspc_im_fz1zggqg_.dbf
        Tag: TAG20181118T135110


RMAN>

Step 5)

RMAN> backup as compressed backupset copy of tablespace TBSPC_IMGCPY_COMP_TEST;

Starting backup at 18-NOV-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: including datafile copy of datafile 01013 in backup set
input file name=/oradata_1/fast_recovery_area/RMN01TST/datafile/o1_mf_tbspc_im_fz1zggqg_.dbf
channel ORA_DISK_1: starting piece 1 at 18-NOV-18
channel ORA_DISK_1: finished piece 1 at 18-NOV-18
piece handle=/oradata_1/fast_recovery_area/RMN01TST/backupset/2018_11_18/o1_mf_nnndf_TAG20181118T135356_fz1zmnrt_.bkp tag=TAG20181118T135356 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-NOV-18

Starting Control File and SPFILE Autobackup at 18-NOV-18
piece handle=/oradata_1/fast_recovery_area/RMN01TST/autobackup/2018_11_18/o1_mf_s_992526837_fz1zmprx_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-NOV-18

RMAN> list backup of tablespace TBSPC_IMGCPY_COMP_TEST;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
88      Full    1.02M      DISK        00:00:00     18-NOV-18
        BP Key: 88   Status: AVAILABLE  Compressed: YES  Tag: TAG20181118T135356
        Piece Name: /oradata_1/fast_recovery_area/RMN01TST/backupset/2018_11_18/o1_mf_nnndf_TAG20181118T135356_fz1zmnrt_.bkp
  List of Datafiles in backup set 88
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1013    Full 4826447    18-NOV-18 /oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf

RMAN> list copy of tablespace TBSPC_IMGCPY_COMP_TEST;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
1       1013 A 18-NOV-18       4826447    18-NOV-18
        Name: /oradata_1/fast_recovery_area/RMN01TST/datafile/o1_mf_tbspc_im_fz1zggqg_.dbf
        Tag: TAG20181118T135110


[oracle@db12c backupset]$ cd 2018*
[oracle@db12c 2018_11_18]$ ls -altr
total 1056
drwxrwx---. 3 oracle oracle    4096 Nov 18 13:53 ..
drwxrwx---. 2 oracle oracle    4096 Nov 18 13:53 .
-rw-rw----. 1 oracle oracle 1073152 Nov 18 13:53 o1_mf_nnndf_TAG20181118T135356_fz1zmnrt_.bkp
[oracle@db12c 2018_11_18]$ pwd
/oradata_1/fast_recovery_area/RMN01TST/backupset/2018_11_18

[oracle@db12c 2018_11_18]$ cd ../../datafile
[oracle@db12c datafile]$ ls -altr
total 102416
drwxrwx---. 6 oracle oracle      4096 Nov 18 13:51 ..
drwxrwx---. 2 oracle oracle      4096 Nov 18 13:51 .
-rw-rw----. 1 oracle oracle 104865792 Nov 18 13:51 o1_mf_tbspc_im_fz1zggqg_.dbf
[oracle@db12c datafile]$ pwd
/oradata_1/fast_recovery_area/RMN01TST/datafile
[oracle@db12c datafile]$


alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
set lines 500
set pages 3000
col BACKUP_TYPE for a15
col FILE_TYPE for a15
col STATUS for a15
col FNAME for a78
col TAG for a19
col MEDIA for a5
col DEVICE_TYPE for a7
col COMPLETION_TIME for a20
col COMPRESSED for a4
col BS_TYPE for a15
col BS_INCR_TYPE for a5
col BS_COMPLETION_TIME for a20
col BS_STATUS for a15
col BS_COMPRESSED for a3
col DF_TABLESPACE for a25
col BS_TAG for a19
set colsep ,

select BACKUP_TYPE
,FILE_TYPE
,STATUS
,FNAME
,TAG
,MEDIA
,DEVICE_TYPE
,COMPLETION_TIME
,COMPRESSED
,OBSOLETE
,BYTES/1024/1024 sz_MB
,BS_TYPE
,BS_INCR_TYPE
,BS_PIECES
,BS_COPIES
,BS_COMPLETION_TIME
,BS_STATUS
,BS_BYTES/1024/1024 sz_BS_MB
,BS_COMPRESSED
,BS_TAG
,DF_FILE#
,DF_TABLESPACE
,DF_CHECKPOINT_CHANGE#
,DF_CKP_MOD_TIME
from v$backup_files
where df_file#=1013;

BACKUP_TYPE    ,FILE_TYPE      ,STATUS         ,FNAME                                                                         ,TAG                  ,MEDIA,DEVICE_,COMPLETION_TIME     ,COMP,OBS,     SZ_MB,BS_TYPE        ,BS_IN, BS_PIECES, BS_COPIES,BS_COMPLETION_TIME  ,BS_STATUS        ,  SZ_BS_MB,BS_,BS_TAG             ,  DF_FILE#,DF_TABLESPACE            ,DF_CHECKPOINT_CHANGE#,DF_CKP_MOD_TIME
---------------,---------------,---------------,------------------------------------------------------------------------------,-------------------,-----,-------,--------------------,----,---,----------,---------------,-----,----------,----------,--------------------,---------------,----------,---,-------------------,----------,-------------------------,---------------------,-------------------
COPY           ,DATAFILE       ,AVAILABLE      ,/oradata_1/fast_recovery_area/RMN01TST/datafile/o1_mf_tbspc_im_fz1zggqg_.dbf  ,TAG20181118T135110 ,       ,DISK   ,18/11/2018 13:51:13 ,    ,NO ,       100,               ,     ,          ,          ,                      ,               ,          ,   ,                   ,      1013,TBSPC_IMGCPY_COMP_TEST   ,       4826447,18/11/2018 13:47:50
BACKUP SET     ,DATAFILE       ,               ,/oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf                     ,             ,     ,       ,                    ,    ,YES,  1.046875,DATAFILE       ,FULL ,         1,         1,18/11/2018 13:53:56 ,AVAILABLE        ,  1.015625,YES,TAG20181118T135356 ,      1013,TBSPC_IMGCPY_COMP_TEST   ,              4826447,18/11/2018 13:47:50

So it is verfied against 1013 datafile from v$datafile view.


SQL> select file#,name,checkpoint_change#,checkpoint_time from v$datafile where file#=1013;

     FILE#,NAME                                                        ,CHECKPOINT_CHANGE#,CHECKPOINT_TIME
----------,------------------------------------------------------------,------------------,-------------------
      1013,/oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf   ,           4826447,18/11/2018 13:47:50

vdf.file# and vdf.ckpt = v$backup_files.ckpt and v$backup_files.file#.

4826447

Step 6)
Remove file for the backup to test.....

SQL> !ls /oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf
ls: cannot access /oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf: No such file or directory


Step 7)
I needed to flush buffer cache and run a select to bring the failure to alertlog...

2018-11-18 15:13:33.517000 +08:00
ALTER SYSTEM: Flushing buffer cache inst=0 container=255 global
2018-11-18 15:13:43.947000 +08:00
Checker run found 1 new persistent data failures

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from SCOTT.TBL_IMGCPY_COMP_TEST;
select * from SCOTT.TBL_IMGCPY_COMP_TEST
                    *
ERROR at line 1:
ORA-01116: error in opening database file 1013
ORA-01110: data file 1013: '/oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> select file#,name,checkpoint_change#,checkpoint_time,status from v$datafile where file#=1013;

     FILE#
----------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CHECKPOINT_CHANGE# CHECKPOIN STATUS
------------------ --------- -------
      1013
/oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf
           4826447 18-NOV-18 ONLINE


SQL> !ls /oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf
ls: cannot access /oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf: No such file or directory

SQL> select file#,STATUS,error,format,recover,FUZZY,TABLESPACE_NAME,BYTES from v$datafile_header where file#=1;

     FILE# STATUS  ERROR                    FORMAT REC FUZ TABLESPACE_NAME                     BYTES
---------- ------- -------------------- ---------- --- --- ------------------------------ ----------
         1 ONLINE                               10 NO  YES SYSTEM                          891289600

SQL> select file#,STATUS,error,format,recover,FUZZY,TABLESPACE_NAME,BYTES from v$datafile_header where file#=1013;

     FILE# STATUS  ERROR                    FORMAT REC FUZ TABLESPACE_NAME                     BYTES
---------- ------- -------------------- ---------- --- --- ------------------------------ ----------
      1013 ONLINE  CANNOT OPEN FILE              0                                                 0            <<< notice the file isnt openable, compare against the proper file above.


Step 8)

BS_TAG: TAG20181118T135356

BACKUP_TYPE    ,FILE_TYPE      ,STATUS         ,FNAME                                                                         ,TAG                  ,MEDIA,DEVICE_,COMPLETION_TIME     ,COMP,OBS,     SZ_MB,BS_TYPE        ,BS_IN, BS_PIECES, BS_COPIES,BS_COMPLETION_TIME  ,BS_STATUS        ,  SZ_BS_MB,BS_,BS_TAG             ,  DF_FILE#,DF_TABLESPACE            ,DF_CHECKPOINT_CHANGE#,DF_CKP_MO
---------------,---------------,---------------,------------------------------------------------------------------------------,-------------------,-----,-------,--------------------,----,---,----------,---------------,-----,----------,----------,--------------------,---------------,----------,---,-------------------,----------,-------------------------,---------------------,---------
BACKUP SET     ,DATAFILE       ,               ,/oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf                     ,             ,     ,       ,                    ,    ,YES,  1.046875,DATAFILE       ,FULL ,         1,         1,18-NOV-18     ,AVAILABLE      ,  1.015625,YES,TAG20181118T135356 ,      1013,TBSPC_IMGCPY_COMP_TEST   ,              4826447,18-NOV-18


TIP: Oracle encapsulates the 'list command' in rman with its own pl/sql procedure to not to reveal what exactly is examined to report. I traced a list command i didnt see v$backup_files being referecned in the 10046 trace.

restore tablespace...


RMAN> restore tablespace TBSPC_IMGCPY_COMP_TEST from tag TAG20181118T135356;

Starting restore at 18-NOV-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 01013 to /oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf
channel ORA_DISK_1: reading from backup piece /oradata_1/fast_recovery_area/RMN01TST/backupset/2018_11_18/o1_mf_nnndf_TAG20181118T135356_fz1zmnrt_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/18/2018 15:40:30
ORA-19870: error while restoring backup piece /oradata_1/fast_recovery_area/RMN01TST/backupset/2018_11_18/o1_mf_nnndf_TAG20181118T135356_fz1zmnrt_.bkp
ORA-19573: cannot obtain exclusive enqueue for datafile 1013                                        <<<<<<< Since the DB is in noarchivelog mode, we are asked to take the tablespace to offline status.

RMAN> alter tablespace TBSPC_IMGCPY_COMP_TEST offline;

Statement processed

RMAN> restore tablespace TBSPC_IMGCPY_COMP_TEST from tag TAG20181118T135356;

Starting restore at 18-NOV-18
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 01013 to /oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz1yp57s_.dbf
channel ORA_DISK_1: reading from backup piece /oradata_1/fast_recovery_area/RMN01TST/backupset/2018_11_18/o1_mf_nnndf_TAG20181118T135356_fz1zmnrt_.bkp
channel ORA_DISK_1: piece handle=/oradata_1/fast_recovery_area/RMN01TST/backupset/2018_11_18/o1_mf_nnndf_TAG20181118T135356_fz1zmnrt_.bkp tag=TAG20181118T135356
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 18-NOV-18

SQL> select file_name,file_id,tablespace_name from dba_data_files where tablespace_name='TBSPC_IMGCPY_COMP_TEST';

FILE_NAME                                                                  ,   FILE_ID,TABLESPACE_NAME
---------------------------------------------------------------------------,----------,------------------------------
/oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz25yvth_.dbf                  ,      1013,TBSPC_IMGCPY_COMP_TEST


SQL> select file#,name,checkpoint_change#,checkpoint_time from v$datafile where file#=1013;

     FILE#,NAME                                                                       ,CHECKPOINT_CHANGE#,CHECKPOINT_TIME
----------,---------------------------------------------------------------------------,------------------,-------------------
      1013,/oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz25yvth_.dbf                  ,           4826447,18/11/2018 13:47:50


===> restore is finished, hence the compressed backup of image copy is absolutely fine to restore.

2018-11-18 15:13:43.947000 +08:00
Checker run found 1 new persistent data failures                    <<< past error when the datafile was removed
2018-11-18 15:42:14.610000 +08:00
alter tablespace TBSPC_IMGCPY_COMP_TEST offline
Completed: alter tablespace TBSPC_IMGCPY_COMP_TEST offline
2018-11-18 15:42:23.440000 +08:00
Full restore complete of datafile 1013 /oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz25yvth_.dbf.  Elapsed time: 0:00:01
  checkpoint is 4826447
  last deallocation scn is 1720084


Step 9)
col ARRVL_TM for a15
col DEPT_TM for a15
col SRC_STN_NM for a12
col DESTN_STN for a12
col DESTN_STN_NM for a15
col STN_NM for a15
col TRAIN_NM for a12
set lines 300
set pages 3000
select * from SCOTT.TBL_IMGCPY_COMP_TEST;

SQL> select * from SCOTT.TBL_IMGCPY_COMP_TEST;
select * from SCOTT.TBL_IMGCPY_COMP_TEST
*
ERROR at line 1:
ORA-00376: file 1013 cannot be read at this time
ORA-01110: data file 1013: '/oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz25yvth_.dbf'        << even now we have this error!!

RMAN> validate tablespace TBSPC_IMGCPY_COMP_TEST;

Starting validate at 18-NOV-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=01013 name=/oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz25yvth_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1013 OK     0              61           12801           4826319
  File Name: /oradata_1/RMN01TST/datafile/o1_mf_tbspc_im_fz25yvth_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1
  Index      0              0
  Other      0              12738

Finished validate at 18-NOV-18

SQL> select file#,STATUS,error,format,recover,FUZZY,TABLESPACE_NAME,BYTES from v$datafile_header where file#=1013;

     FILE#,STATUS         ,ERROR                                                            ,    FORMAT,REC,FUZ,TABLESPACE_NAME         ,     BYTES
----------,---------------,-----------------------------------------------------------------,----------,---,---,------------------------------,----------
      1013,OFFLINE        ,OFFLINE NORMAL                                                   ,         0,   ,   ,               ,  0                <<< but this is because the tablespace is in offline state. Let us switch it to online state.

SQL> alter tablespace TBSPC_IMGCPY_COMP_TEST online;

Tablespace altered.


SQL> select file#,STATUS,error,format,recover,FUZZY,TABLESPACE_NAME,BYTES from v$datafile_header where file#=1013;

     FILE#,STATUS         ,ERROR          ,    FORMAT,REC,FUZ,TABLESPACE_NAME               ,     BYTES
----------,---------------,---------------,----------,---,---,------------------------------,----------
      1013,ONLINE         ,               ,        10,NO ,NO ,TBSPC_IMGCPY_COMP_TEST        , 104857600


Checker run found 1 new persistent data failures
2018-11-18 15:52:32.794000 +08:00
Checker run found 1 new persistent data failures
2018-11-18 15:55:02.193000 +08:00
alter tablespace TBSPC_IMGCPY_COMP_TEST online
Completed: alter tablespace TBSPC_IMGCPY_COMP_TEST online


SQL> select * from SCOTT.TBL_IMGCPY_COMP_TEST;

  TRAIN_NO,TRAIN_NM    ,STN_TRVL_SEQ,STN       ,STN_NM         ,ARRVL_TM       ,DEPT_TM        ,  DISTANCE,SRC_STN   ,SRC_STN_NM  ,DESTN_STN   ,DESTN_STN_NM
----------,------------,------------,----------,---------------,---------------,---------------,----------,----------,------------,------------,---------------
...

30 rows selected.

So I could select the data fine now.

Result: The situation under which you have to create a backupset out of image copy is very rare, but still verifying to see weather the backupset is restorable takes more priority.

Orace block dump @ various stages of the transaction

Goal: See what happens during a Transaction (TX)... learn from block dump.

Step 1: Get to know your table - block map [thanks to Arup Nanda - http://arup.blogspot.com/2011/01/more-on-interested-transaction-lists.html]

SQL> select
dbms_rowid.rowid_relative_fno(rowid) File#,
dbms_rowid.rowid_block_number(rowid) Block#,
count(1)
from T1_TEST1
group by
dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid)
order by
1,2  2    3    4    5    6    7    8    9   10  ;

     FILE#     BLOCK#   COUNT(1)
---------- ---------- ----------
      1012      55571         65
      1012      55572         59
      1012      55573         59
      1012      55574         59
      1012      55575         59
      1012      55576         59
      1012      55577         59
      1012      55578         60
      1012      55579         59
      1012      55580         59
      1012      55581         59
      1012      55582         59
      1012      55584         59
      1012      55585         59
      1012      55586         59
      1012      55587         59
      1012      55588         59
      1012      55589         59
      1012      55590         59
      1012      55591         59
      1012      55592         59
...
      1012      55750         59
      1012      55751         59
      1012      55752         59
      1012      55753         59
      1012      55754         59
      1012      55755         59
      1012      55756         59
      1012      55757         59
      1012      55758         59
      1012      55759         59
      1012      55760         59
      1012      55761         59
      1012      55762         59
      1012      55763         59
      1012      55764         59
      1012      55765         59
      1012      55766         59
      1012      55767         59
      1012      55768         40

189 rows selected.


Step 2: Pick the block we wanted to update and then find out its high/low value which will be used in the preference clause.

select min(OBJECT_ID), max(OBJECT_ID)
from T1_TEST1
where dbms_rowid.rowid_block_number(rowid) = 55572;

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
        104344         149818


Step 3: For keeping this test simple, I will pick the second block and its min [104344]. value.

Step 4: Will dump the preimage of the block.


set lines 300 pages 3000
select a.sid,a.serial#,b.pid,b.spid,b.tracefile from v$session a,v$process b where a.paddr=b.addr and a.username='SCOTT';

alter system dump datafile 1012 block min 55572 block max 55572;

Step 5: Update the chosen record..

update T1_TEST1 set object_name='22SEP18_CHANGED' where object_id=104344;

Step 6: Will dump the postimage of the block.

alter system dump datafile 1012 block min 55572 block max 55572;

Step 7: Will perform commit and take a dump of the block.

commit;
alter system dump datafile 1012 block min 55572 block max 55572;

>>> done , the trace file is recovered @ all the three steps. I managed to remove the duplicate copies of the trace entries and kept only relevant parts in all 3 traces.
Let us do the examination.

Observations:
1) In the preimage dump of the block, we only had 1 buffer copy

Dump of buffer cache at level 4 for pdb=0 tsn=1013 rdba=-50276076
BH (0x9df6a158) file#: 1012 rdba: 0xfd00d914 (1012/55572) class: 1 ba: 0x9d104000
...
hash: [0xa95592e0,0xa95592e0] lru: [0x9df6a388,0x9df6a108]
...
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.31ee4e tch: 2

flags: only_sequential_access

Block dump from disk:
buffer tsn: 1013 rdba: 0xfd00d914 (1012/55572)

scn: 0x0.2cf023 seq: 0x02 flg: 0x04 tail: 0xf0230602

frmt: 0x02 chkval: 0xd066 type: 0x06=trans data

What we notice in this trace is, we had only the current version of the block available in memory. There were no active transaction in the block.

2) The same block dump on DML, had 2 versions of the block. 1 the pre-modification version which is the CR (consistent Read version) aka actually the step 1's current version and the actual current version which is the modified block version (this block actually is flagged as privte).

Current:
BH (0x9df61e18) file#: 1012 rdba: 0xfd00d914 (1012/55572) class: 1 ba: 0x9d032000
..
hash: [0x9df6a210,0xa95592e0] lru: [0x9df62048,0x9df61dc8]
..
use: [NULL] wait: [NULL]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.31ee4e tch: 2 txn: 0xa530acb8   //// look here the block has a TXN associated
flags: private

Apart from this we had the past image of the block (CR)..

BH (0x9df6a158) file#: 1012 rdba: 0xfd00d914 (1012/55572) class: 1 ba: 0x9d104000
..
hash: [0xa95592e0,0x9df61ed0] lru: [0x9df627c8,0x9df6e348]

lru-flags: moved_to_tail
..
cr: [scn: 0x0.31ef05],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.31ef05],[sfl: 0x0],[lc: 0x0.2cf023]
flags: only_sequential_access

Rest all resembles.

3) Post the commit, I noticed the following sections in the block dump trace shows changes...

Pre commit#
ckptq: [NULL] fileq: [NULL]

objq: [0x9df6a3b0,0x9df69d70] objaq: [0x9df6a3c0,0x9df69d80]

use: [NULL] wait: [NULL]
                /// entry is found here alone
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.31ee4e tch: 2 txn: 0xa530acb8

flags: private

LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]


Post Commit#
obj-flags: object_ckpt_list //// a new line post commit.From what I have read is the object checkpoint queue, the object post commit gets into the checkpoint queue.

ckptq: [0x9df941d8,0x9df6e398] fileq: [0xa9d30cb8,0xa9d30cb8]
        //// The block getting into checkpoint queue and also it gets a LRBA value (low redo block address)
objq: [0x7f0ea940,0x7f0ea940] objaq: [0x9df6a3c0,0x9df69d80]

st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.31ee4e tch: 2
 //// No Transaction entry, meaning the transaction is completed.
flags: buffer_dirty
                            //// Oracle announces now the buffer as dirty, earlier the buffer was private now it has become dirty   
LRBA: [0x7e.119ef.0] LSCN: [0x0.31ef32] HSCN: [0x0.31ef61] HSUB: [1]    One unique identification is the block dump didnt show use of ITL at any stage.
Since there were ckpt or events that can kick off DBW to write dirt list or cold buffers, the block isnt written to the disk yet.

Thanks