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.
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.
