Thinking Out Loud

October 31, 2013

What’s RMAN Backup Size

Filed under: 11g,RMAN — mdinh @ 5:35 am

So there I was, looking for a script to calculate the backup size.  After several thoughts, it was not exactly what I was looking for.

UPDATED: Feb 27, 2016

The SQL with join on p.recid=s.recid is not correct!

Please go to https://mdinh.wordpress.com/2016/02/27/revised-rman-backup-size/

Not sure why it matched on the test case used at the time.
SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
FROM 
(
SELECT 
  CASE
    WHEN s.backup_type='L' THEN 'ARCHIVELOG' 
    WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
    WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
    WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
  END type,
  TRUNC(s.completion_time) completion_time,
  p.bytes,
  s.elapsed_seconds
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND p.recid=s.recid
UNION ALL
SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
)
group by TO_CHAR(completion_time, 'YYYY-MON-DD'), type
order by 1 asc,2,3
;

Note that the backup size did not have any calculation for LEVEL1.

A closer look at backup by file shows that BS Key 731 is a controlfile backup and not a datafile backup.

LAX:(SYS@db01)> SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
FROM
  2    3  (
  4  SELECT
  5    CASE
  6      WHEN s.backup_type='L' THEN 'ARCHIVELOG'
  7      WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
  8      WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
  9      WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
 10    END type,
 11    TRUNC(s.completion_time) completion_time,
 12    p.bytes,
 13    s.elapsed_seconds
 14  FROM v$backup_piece p, v$backup_set s
 15  WHERE status='A'
 16  AND p.recid =s.recid
 17  UNION ALL
 18  SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
 19  )
 20  group by TO_CHAR(completion_time, 'YYYY-MON-DD'), type
 21  order by 1 asc,2,3
 22  ;

COMPLETION_TIME      TYPE                 MB        MIN
-------------------- ------------ ---------- ----------
2013-OCT-30          ARCHIVELOG            2          0
2013-OCT-30          CONTROLFILE          36          0
2013-OCT-30          DATAFILECOPY       1696          0
2013-OCT-30          LEVEL0               63          0

LAX:(SYS@db01)>

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
722     B  0  A DISK        30-OCT-2013 20:47:32 1       1       YES        DINC0_WED
723     B  0  A DISK        30-OCT-2013 20:47:35 1       1       YES        DINC0_WED
724     B  0  A DISK        30-OCT-2013 20:47:45 1       1       YES        DINC0_WED
725     B  0  A DISK        30-OCT-2013 20:47:58 1       1       YES        DINC0_WED
726     B  0  A DISK        30-OCT-2013 20:48:01 1       1       YES        DINC0_WED
728     B  A  A DISK        30-OCT-2013 20:48:11 1       1       YES        AINC0_WED
729     B  F  A DISK        30-OCT-2013 20:48:20 1       1       NO         TAG20131030T204813
730     B  A  A DISK        30-OCT-2013 21:50:09 1       1       YES        MIB_UPDATE
731     B  1  A DISK        30-OCT-2013 21:53:46 1       1       YES        MIB_UPDATE
732     B  A  A DISK        30-OCT-2013 21:53:54 1       1       YES        MIB_UPDATE
733     B  F  A DISK        30-OCT-2013 21:54:05 1       1       NO         TAG20131030T215358

RMAN> list backup by file;

List of Datafile Backups
========================

File Key     TY LV S Ckp SCN    Ckp Time             #Pieces #Copies Compressed Tag
---- ------- -  -- - ---------- -------------------- ------- ------- ---------- ---
1    724     B  0  A 1899045    30-OCT-2013 20:47:38 1       1       YES        DINC0_WED
2    725     B  0  A 1899051    30-OCT-2013 20:47:53 1       1       YES        DINC0_WED
3    722     B  0  A 1899041    30-OCT-2013 20:47:31 1       1       YES        DINC0_WED
4    723     B  0  A 1899043    30-OCT-2013 20:47:34 1       1       YES        DINC0_WED
5    726     B  0  A 1899055    30-OCT-2013 20:48:01 1       1       YES        DINC0_WED

List of Archived Log Backups
============================

Thrd Seq     Low SCN    Low Time             BS Key  S #Pieces #Copies Compressed Tag
---- ------- ---------- -------------------- ------- - ------- ------- ---------- ---
1    13      1899034    30-OCT-2013 20:47:26 728     A 1       1       YES        AINC0_WED
1    14      1899064    30-OCT-2013 20:48:09 730     A 1       1       YES        MIB_UPDATE
1    15      1901013    30-OCT-2013 21:50:05 732     A 1       1       YES        MIB_UPDATE

List of Control File Backups
============================

CF Ckp SCN Ckp Time             BS Key  S #Pieces #Copies Compressed Tag
---------- -------------------- ------- - ------- ------- ---------- ---
1901124    30-OCT-2013 21:53:58 733     A 1       1       NO         TAG20131030T215358
1901091    30-OCT-2013 21:53:38 731     A 1       1       YES        MIB_UPDATE
1899073    30-OCT-2013 20:48:14 729     A 1       1       NO         TAG20131030T204813
List of SPFILE Backups
======================

Modification Time    BS Key  S #Pieces #Copies Compressed Tag
-------------------- ------- - ------- ------- ---------- ---
30-OCT-2013 20:01:18 733     A 1       1       NO         TAG20131030T215358
30-OCT-2013 20:01:18 729     A 1       1       NO         TAG20131030T204813

RMAN> list backup of database;

using target database control file instead of recovery catalog

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
722     Incr 0  1.37M      DISK        00:00:01     30-OCT-2013 20:47:32
        BP Key: 722   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9qonns33_1_1.inc0
  List of Datafiles in backup set 722
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    0  Incr 1899041    30-OCT-2013 20:47:31 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-UNDOTBS_FNO-3_p9onf3nk.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
723     Incr 0  1.05M      DISK        00:00:01     30-OCT-2013 20:47:35
        BP Key: 723   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9ronns36_1_1.inc0
  List of Datafiles in backup set 723
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  4    0  Incr 1899043    30-OCT-2013 20:47:34 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-USER_DATA_FNO-4_paonf3on.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
724     Incr 0  38.05M     DISK        00:00:07     30-OCT-2013 20:47:45
        BP Key: 724   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9sonns3a_1_1.inc0
  List of Datafiles in backup set 724
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1899045    30-OCT-2013 20:47:38 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSTEM_FNO-1_pbonf3p6.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
725     Incr 0  21.10M     DISK        00:00:05     30-OCT-2013 20:47:58
        BP Key: 725   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9tonns3p_1_1.inc0
  List of Datafiles in backup set 725
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1899051    30-OCT-2013 20:47:53 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSAUX_FNO-2_pconf3pm.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
726     Incr 0  1.02M      DISK        00:00:00     30-OCT-2013 20:48:01
        BP Key: 726   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9uonns41_1_1.inc0
  List of Datafiles in backup set 726
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  5    0  Incr 1899055    30-OCT-2013 20:48:01 /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf

RMAN> list datafilecopy all;

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

Key     File S Completion Time      Ckp SCN    Ckp Time
------- ---- - -------------------- ---------- --------------------
133     1    A 30-OCT-2013 21:53:02 1901076    30-OCT-2013 21:52:45
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSTEM_FNO-1_a5onnvtd.dbf
        Tag: MIB_UPDATE

134     2    A 30-OCT-2013 21:53:28 1901086    30-OCT-2013 21:53:10
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSAUX_FNO-2_a6onnvu6.dbf
        Tag: MIB_UPDATE

131     3    A 30-OCT-2013 21:51:54 1901024    30-OCT-2013 21:50:14
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-UNDOTBS_FNO-3_a3onnvom.dbf
        Tag: MIB_UPDATE

132     4    A 30-OCT-2013 21:52:35 1901058    30-OCT-2013 21:51:59
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-USER_DATA_FNO-4_a4onnvrv.dbf
        Tag: MIB_UPDATE

135     5    A 30-OCT-2013 21:53:38 1901090    30-OCT-2013 21:53:36
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-CORRUPT_FNO-5_a7onnvv0.dbf
        Tag: MIB_UPDATE

RMAN>

$ du -scm /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-*FNO-*_a*
17      /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-CORRUPT_FNO-5_a7onnvv0.dbf
201     /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSAUX_FNO-2_a6onnvu6.dbf
201     /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSTEM_FNO-1_a5onnvtd.dbf
1025    /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-UNDOTBS_FNO-3_a3onnvom.dbf
257     /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-USER_DATA_FNO-4_a4onnvrv.dbf
1697    total
[oracle@lax:db01]/home/oracle
$

Update based on Coskan comment.  This could be because my database is small, but it does not have the granularity. The timing is much better.

LAX:(SYS@db01)> set echo on
LAX:(SYS@db01)> @bk
LAX:(SYS@db01)> set pages 1000
LAX:(SYS@db01)> select TO_CHAR(end_time, 'YYYY-MON-DD') dt, input_type, round(sum(output_bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
  2  from v$rman_backup_job_details where status='COMPLETED' and end_time>trunc(sysdate)
  3  group by TO_CHAR(end_time, 'YYYY-MON-DD'), input_type
  4  order by 1, 2;

DT                   INPUT_TYPE            MB        MIN
-------------------- ------------- ---------- ----------
2013-NOV-01          DB INCR             1784          6

LAX:(SYS@db01)> SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
  2  FROM
  3  (
  4  SELECT
  5    CASE
  6      WHEN s.backup_type='L' THEN 'ARCHIVELOG'
  7      WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
  8      WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
  9      WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
 10    END type,
 11    TRUNC(s.completion_time) completion_time,
 12    p.bytes,
 13    s.elapsed_seconds
 14  FROM v$backup_piece p, v$backup_set s
 15  WHERE p.status='A'
 16  AND p.recid=s.recid
 17  UNION ALL
 18  SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
 19  )
 20  group by TO_CHAR(completion_time, 'YYYY-MON-DD'), type
 21  order by 1 asc,2,3
 22  ;

COMPLETION_TIME      TYPE                 MB        MIN
-------------------- ------------ ---------- ----------
2013-NOV-01          ARCHIVELOG            2          0
2013-NOV-01          CONTROLFILE          36          0
2013-NOV-01          DATAFILECOPY       1696          0
2013-NOV-01          LEVEL0               63          1

LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/home/oracle
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Nov 1 07:23:57 2013

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

connected to target database: DB01 (DBID=1452485914)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
749     B  0  A DISK        01-NOV-2013 06:52:38 1       1       YES        DINC0_FRI
750     B  0  A DISK        01-NOV-2013 06:52:47 1       1       YES        DINC0_FRI
751     B  0  A DISK        01-NOV-2013 06:53:05 1       1       YES        DINC0_FRI
752     B  0  A DISK        01-NOV-2013 06:53:47 1       1       YES        DINC0_FRI
753     B  0  A DISK        01-NOV-2013 06:53:51 1       1       YES        DINC0_FRI
755     B  A  A DISK        01-NOV-2013 06:54:07 1       1       YES        AINC0_FRI
756     B  F  A DISK        01-NOV-2013 06:54:18 1       1       NO         TAG20131101T065411
757     B  A  A DISK        01-NOV-2013 07:06:49 1       1       YES        MIB_UPDATE
758     B  1  A DISK        01-NOV-2013 07:10:26 1       1       YES        MIB_UPDATE
759     B  A  A DISK        01-NOV-2013 07:10:32 1       1       YES        MIB_UPDATE
760     B  F  A DISK        01-NOV-2013 07:10:42 1       1       NO         TAG20131101T071035

RMAN> exit
Advertisements

October 30, 2013

Playing with Oracle Free Block Corruption

Filed under: 11g,oracle,RMAN — mdinh @ 7:04 am

First and foremost, thank you to all the bloggers sharing their knowledge.

My previous post on corruption is here
The corrupt.sql script was stolen from here
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(35) was stolen from here

Free Block Corruption is from a data file block which is now Free but used to be occupied by a corrupted segment.

How to Format Corrupted Block Not Part of Any Segment (Doc ID 336133.1) is a good reference.

So how did I get here?

The objective is to remove rows from v$database_block_corruption and this can be done by running exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(35).

The caveat is when the next corruption check, v$database_block_corruption is populated again.

This appears to only treat the symptoms and not the root cause.

Here’s my test case:

LAX:(SYS@db01)> create tablespace corrupt datafile size 16m;

Tablespace created.

LAX:(SYS@db01)> create index hr.x on hr.departments_old(last_update) tablespace corrupt;

Index created.

LAX:(SYS@db01)> exit

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 29 22:45:58 2013

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

connected to target database: DB01 (DBID=1452485914)

RMAN> validate tablespace corrupt;

Starting validate at 29-OCT-2013 22:46:05
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=37 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.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
---- ------ -------------- ------------ --------------- ----------
5    OK     0              1917         2048            1833133
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              1
  Other      0              130

Finished validate at 29-OCT-2013 22:46:07

RMAN> exit

LAX:(SYS@db01)> @corrupt.sql

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=1051 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=1043 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=1047 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=1045 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=1034 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2308 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2274 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2258 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2292 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2362 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2244 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2390 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2283 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2228 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2304 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2234 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2238 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=827 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=843 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=835 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=131 << EOF 
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt 
EOF 

21 rows selected. 

LAX:(SYS@db01)> exit

[oracle@lax:db01]/home/oracle
$ dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=131 << EOF 
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF
0+1 records in
0+1 records out
112 bytes (112 B) copied, 0.000109233 s, 1.0 MB/s


RMAN> validate tablespace corrupt;

Starting validate at 29-OCT-2013 22:46:42
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=16 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1917         2048            1833132
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      1              131

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_3351.trc for details
Finished validate at 29-OCT-2013 22:46:46

RMAN> exit

LAX:(SYS@db01)> @corrupt_seg.sql
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ---------
    5    131      1                  0 CORRUPT

LAX:(SYS@db01)>
LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2         , greatest(e.block_id, c.block#) s_blk#
  3         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6        , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13         , header_block s_blk#
 14         , header_block e_blk#
 15         , 1 blk_corrupt
 16         , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22         , greatest(f.block_id, c.block#) s_blk#
 23         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
 24         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blk_corrupt
 26         , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, s_blk#
 32  ;

OWNER                SEGMENT_TYPE       SEGMENT_NAME              PARTITION_NAME            FILE# S_BLK#    E_DBLK# BLK_CORRUPT DESCRIPTION
-------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
HR                   INDEX              X                                                       5    131        131           1

LAX:(SYS@db01)> drop index hr.x;

Index dropped.

LAX:(SYS@db01)>

RMAN> validate tablespace corrupt;

Starting validate at 29-OCT-2013 22:49:56
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=37 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1917         2048            1833264
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      1              131

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_3364.trc for details
Finished validate at 29-OCT-2013 22:50:00

RMAN>

LAX:(SYS@db01)> @corrupt_seg.sql
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ---------
    5    131      1                  0 CORRUPT

LAX:(SYS@db01)>
LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2         , greatest(e.block_id, c.block#) s_blk#
  3         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6        , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13         , header_block s_blk#
 14         , header_block e_blk#
 15         , 1 blk_corrupt
 16         , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22         , greatest(f.block_id, c.block#) s_blk#
 23         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
 24         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blk_corrupt
 26         , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, s_blk#
 32  ;

OWNER                SEGMENT_TYPE       SEGMENT_NAME              PARTITION_NAME            FILE# S_BLK#    E_DBLK# BLK_CORRUPT DESCRIPTION
-------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
                                                                                                5    131        131           1 Free Block

LAX:(SYS@db01)>
LAX:(SYS@db01)> exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(35);

PL/SQL procedure successfully completed.

LAX:(SYS@db01)> @corrupt_seg.sql
LAX:(SYS@db01)> set lines 200 pages 10000 echo on
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

no rows selected

LAX:(SYS@db01)>
LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2         , greatest(e.block_id, c.block#) s_blk#
  3         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6        , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13         , header_block s_blk#
 14         , header_block e_blk#
 15         , 1 blk_corrupt
 16         , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22         , greatest(f.block_id, c.block#) s_blk#
 23         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
 24         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blk_corrupt
 26         , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, s_blk#
 32  ;

no rows selected

LAX:(SYS@db01)>
LAX:(SYS@db01)>

RMAN> validate tablespace corrupt;

Starting validate at 29-OCT-2013 22:51:54
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=16 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1917         2048            1833264
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      1              131

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_3382.trc for details
Finished validate at 29-OCT-2013 22:51:58

RMAN>

LAX:(SYS@db01)> @corrupt_seg.sql
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ---------
    5    131      1                  0 CORRUPT

LAX:(SYS@db01)>
LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2         , greatest(e.block_id, c.block#) s_blk#
  3         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6        , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13         , header_block s_blk#
 14         , header_block e_blk#
 15         , 1 blk_corrupt
 16         , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22         , greatest(f.block_id, c.block#) s_blk#
 23         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
 24         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blk_corrupt
 26         , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, s_blk#
 32  ;

OWNER                SEGMENT_TYPE       SEGMENT_NAME              PARTITION_NAME            FILE# S_BLK#    E_DBLK# BLK_CORRUPT DESCRIPTION
-------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
                                                                                                5    131        131           1 Free Block

LAX:(SYS@db01)>
LAX:(SYS@db01)>

Update:

Team mate has suggested the following article:

How to Clean entries from V$DATABASE_BLOCK_CORRUPTION when listing file/block corruption for a Datafile/Tablespace that was already dropped (Doc ID 1484189.1)

Following the note above to perform backup check logical does not solve the issue.

RMAN> backup check logical validate tablespace corrupt;

Starting backup at 30-OCT-2013 06:55:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=30 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1917         2048            1833264
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      1              131

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_2183.trc for details
Finished backup at 30-OCT-2013 06:55:13

RMAN> backup check logical validate datafile 5;

Starting backup at 30-OCT-2013 06:55:37
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1917         2048            1833264
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      1              131

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_2183.trc for details
Finished backup at 30-OCT-2013 06:55:40

RMAN>

LAX:(SYS@db01)> set lines 200 pages 10000 echo on
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ---------
    5    131      1                  0 CORRUPT

LAX:(SYS@db01)>
LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2         , greatest(e.block_id, c.block#) s_blk#
  3         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6        , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13         , header_block s_blk#
 14         , header_block e_blk#
 15         , 1 blk_corrupt
 16         , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22         , greatest(f.block_id, c.block#) s_blk#
 23         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
 24         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blk_corrupt
 26         , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, s_blk#
 32  ;

OWNER                SEGMENT_TYPE       SEGMENT_NAME              PARTITION_NAME            FILE# S_BLK#    E_DBLK# BLK_CORRUPT DESCRIPTION
-------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
                                                                                                5    131        131           1 Free Block

LAX:(SYS@db01)> drop tablespace corrupt including contents and datafiles;

Tablespace dropped.

LAX:(SYS@db01)> @corrupt_seg.sql
LAX:(SYS@db01)> set lines 200 pages 10000 echo on
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

no rows selected

LAX:(SYS@db01)>

Does anyone have or know of a short cut for this?

SOLVED:

I am getting good at corrupting data.

OWNER                SEGMENT_TYPE       SEGMENT_NAME              PARTITION_NAME            FILE# S_BLK#    E_DBLK# BLK_CORRUPT DESCRIPTION
-------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
                                                                                                5    827        827           1 Free Block
                                                                                                5    835        835           1 Free Block

LAX:(SYS@db01)>
LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/home/oracle
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 30 07:34:39 2013

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

connected to target database: DB01 (DBID=1452485914)

RMAN> validate tablespace corrupt;

Starting validate at 30-OCT-2013 07:34:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=44 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1915         2048            1859108
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              1
  Other      2              132

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_2608.trc for details
Finished validate at 30-OCT-2013 07:34:54

RMAN> list backup summary;

specification does not match any backup in the repository

RMAN> backup check logical tablespace corrupt;

Starting backup at 30-OCT-2013 07:35:48
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf
channel ORA_DISK_1: starting piece 1 at 30-OCT-2013 07:35:49
channel ORA_DISK_1: finished piece 1 at 30-OCT-2013 07:35:52
piece handle=/oracle/backup/bkup_8qonmdml_1_1 tag=TAG20131030T073548 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-OCT-2013 07:35:52

Starting Control File and SPFILE Autobackup at 30-OCT-2013 07:35:52
piece handle=/oracle/flashrecovery/LAX_DB01/autobackup/2013_10_30/o1_mf_s_830158552_9726bdgf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-OCT-2013 07:35:59

RMAN> blockrecover datafile 5 block 827,835;

Starting recover at 30-OCT-2013 07:39:09
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /oracle/backup/bkup_8qonmdml_1_1
channel ORA_DISK_1: piece handle=/oracle/backup/bkup_8qonmdml_1_1 tag=TAG20131030T073548
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 30-OCT-2013 07:39:17

RMAN> validate tablespace corrupt;

Starting validate at 30-OCT-2013 07:39:25
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.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
---- ------ -------------- ------------ --------------- ----------
5    OK     0              1915         2050            1859108
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              1
  Other      0              132

Finished validate at 30-OCT-2013 07:39:27

RMAN> exit

Recovery Manager complete.
[oracle@lax:db01]/home/oracle
$

New Rman Blockrecover command in 11g (Recover corruption list) (Doc ID 1390759.1)

Note: For 11g, recover corruption list can be used to recovery all corruption versus listing them one by one.

I am not sure if blockrecover corruption list would have worked on 10g as well. May be you can try it and let me know?

RMAN> recover corruption list;

Starting recover at 30-OCT-2013 07:22:56
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /oracle/backup/bkup_4gonmcsj_1_1
channel ORA_DISK_1: piece handle=/oracle/backup/bkup_4gonmcsj_1_1 tag=TAG20131030T072154
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 30-OCT-2013 07:23:09

RMAN> validate tablespace corrupt;

Starting validate at 30-OCT-2013 07:23:20
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9724xr04_.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
—- —— ————– ———— ————— ———-
5 OK 0 1916 2050 1856947
File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9724xr04_.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 0
Index 0 0
Other 0 132

Finished validate at 30-OCT-2013 07:23:22

RMAN>

October 27, 2013

Playing with RMAN Merged Incremental Backup

Filed under: RMAN — mdinh @ 10:02 pm

Here are 2 good documents for reference:
Merged Incremental Backup Strategies (Doc ID 745798.1)
Oracle Backup and Recovery for a VLDB

You can find my playing around here

I did find the results of switch database to copy awkward with the data file name.

LAX:(SYS@db01)> show parameter db_create
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /oracle/oradata
db_create_online_log_dest_1          string      /oracle/oradata
db_create_online_log_dest_2          string      /oracle/oradata
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
LAX:(SYS@db01)> alter system set db_create_file_dest='/oracle/datafilecopy';
System altered.
LAX:(SYS@db01)> create tablespace testing datafile size 16m;
Tablespace created.
LAX:(SYS@db01)> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSTEM_FNO-1_pbonf3p6.dbf
/oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSAUX_FNO-2_pconf3pm.dbf
/oracle/datafilecopy/data_D-DB01_I-1452485914_TS-UNDOTBS_FNO-3_p9onf3nk.dbf
/oracle/datafilecopy/data_D-DB01_I-1452485914_TS-USER_DATA_FNO-4_paonf3on.dbf
/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_testing_96v0nk2p_.dbf
LAX:(SYS@db01)>

Is this something that can be lived with?

October 26, 2013

You don’t need set echo on for RMAN

Filed under: RMAN — mdinh @ 11:36 pm

So there I was, reading on the internet about having set echo on for RMAN and started to incorporate it into RMAN scripts.

It was a waste of time since it is not needed according to my test cases.

Lesson learned is that don’t trust what is provided to you without a complete test case.

Here’s the test case.

[oracle@lax:db01]/home/oracle
$ cat bk.rman
connect target;
run {
allocate channel d4 device type disk format '/tmp/%d_%I_%T_%U' maxopenfiles 1;
backup tablespace system;
}
exit

$ nohup rman @bk.rman > rman.log 2>&1 &
[1] 3115
[oracle@lax:db01]/home/oracle
$
[1]+  Done                    nohup rman @bk.rman > rman.log 2>&1

[oracle@lax:db01]/home/oracle
$ cat rman.log

nohup: ignoring input

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Oct 26 16:21:51 2013

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

RMAN> connect target;
2> run {
3> allocate channel d4 device type disk format '/tmp/%d_%I_%T_%U' maxopenfiles 1;
4> backup tablespace system;
5> }
6> exit
connected to target database: DB01 (DBID=1452485914)

using target database control file instead of recovery catalog
allocated channel: d4
channel d4: SID=44 device type=DISK

Starting backup at 26-oct-2013 16:21:52
channel d4: starting compressed full datafile backup set
channel d4: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/oradata/LAX_DB01/datafile/o1_mf_system_8ymzrcgk_.dbf
channel d4: starting piece 1 at 26-oct-2013 16:21:53
channel d4: finished piece 1 at 26-oct-2013 16:22:08
piece handle=/tmp/DB01_1452485914_20131026_k5oncr11_1_1 tag=TAG20131026T162152 comment=NONE
channel d4: backup set complete, elapsed time: 00:00:15
Finished backup at 26-oct-2013 16:22:08

Starting Control File and SPFILE Autobackup at 26-oct-2013 16:22:08
piece handle=/oracle/flashrecovery/LAX_DB01/autobackup/2013_10_26/o1_mf_s_829844528_96rmo452_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-oct-2013 16:22:23
released channel: d4

Recovery Manager complete.

[oracle@lax:db01]/home/oracle
$ rman @bk.rman > rman.log

[oracle@lax:db01]/home/oracle
$ cat rman.log

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Oct 26 16:23:00 2013

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

RMAN> connect target;
2> run {
3> allocate channel d4 device type disk format '/tmp/%d_%I_%T_%U' maxopenfiles 1;
4> backup tablespace system;
5> }
6> exit
connected to target database: DB01 (DBID=1452485914)

using target database control file instead of recovery catalog
allocated channel: d4
channel d4: SID=41 device type=DISK

Starting backup at 26-oct-2013 16:23:01
channel d4: starting compressed full datafile backup set
channel d4: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/oradata/LAX_DB01/datafile/o1_mf_system_8ymzrcgk_.dbf
channel d4: starting piece 1 at 26-oct-2013 16:23:02
channel d4: finished piece 1 at 26-oct-2013 16:23:17
piece handle=/tmp/DB01_1452485914_20131026_k7oncr36_1_1 tag=TAG20131026T162302 comment=NONE
channel d4: backup set complete, elapsed time: 00:00:15
Finished backup at 26-oct-2013 16:23:17

Starting Control File and SPFILE Autobackup at 26-oct-2013 16:23:17
piece handle=/oracle/flashrecovery/LAX_DB01/autobackup/2013_10_26/o1_mf_s_829844597_96rmq960_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-oct-2013 16:23:24
released channel: d4

Recovery Manager complete.
[oracle@lax:db01]/home/oracle
$

October 16, 2013

Renaming Table, Constraints, Indexes

Filed under: 11g,oracle — mdinh @ 5:39 am

So there I was, working on another task to create a data subset. This time, keep data for the current year only.

Here’s a similar post, How to rebuild table when you don’t have TOAD

My code has something like: create table tn_backup as select * from tn;

In a code review, a colleague suggested to rename the table since it would be faster.

Renaming the table is fast, but what about all the constraints and indexes on the table? They all will have to be renamed since duplicates are not allowed.

Since the table was relatively simple and did not have any Foreign Key, I thought I would entertain the idea.

Here is the test case Renaming tables test case

The table used in the test case has Foreign Key to it, but just imagine it didn’t.

Another options is to rename the table and drop all the constraints and indexes.

Option 1:
Rename table. Drop all constraints and indexes on the renamed table. Create new table.

Option 2:
Create backup using CTAS, Drop table. Create new table.

Which one would you chose?

October 13, 2013

What Motivates Me

Filed under: Uncategorized — mdinh @ 12:24 pm

5:18 a.m. in the morning, waken up by another CPU load at 39%. Why do I do this?

Previously, I did a post on motivation here

This is what motivates me!

Hello Michael,

It has been very short tenure working with you at (company). I would like to say thank you for all your help provided on couple of projects working with you. You have been always listening and being respectful of others, welcoming suggestions, opens for new ideas, admitting limitations, and maintaining composure by expressing disagreement calmly. Also you did find the ways to improve the critical issues with happy issue ending. This has been demonstrated during the data fix and flat file adaptor implementation for the WU Digital even though this was the transition period.

You puts thought process behind your words which I believe makes you respected individual at the workplace. Your knowledge of the technologies and its implementation to help the business makes you one of the best IT professional in the industry.

I would like to say thank you once again, wish you all the best for your new assignment.

Thank you,
Pravin (lastname)

Create a free website or blog at WordPress.com.