OCP043 Lecture 7 Dealing with Database commit uption

Oracle Bad blocks are divided into Physical Bad blocks and logical Bad blocks, most of which are caused by hardware problems, the cause of logical Bad blocks is generally caused by oracle software bugs or oracle patches not installed.
You can use the following to find oracle Bad blocks:
1: analyze
2: dbverify
3. db_block_checking
4. dbms_repair
Rowid is a pseudo column used to ensure the uniqueness of the row in the table. It does not indicate the physical location of the trip, but can be used to locate the row.
Rowid is 18-bit 64-hexadecimal, A-Z represents 0-25; a-z represents 26-51,0-9 represents 52-61, + represents 62, \ represents the data file corresponding to the tablespace in the first 6 + 3 digits of 63, 10-15 digits represent the block number, and 16-18 digits represent the row number.
Simulate Physical Bad blocks
SQL> select substr (rowid, 10, 6), dbms_rowid.rowid_block_number (rowid) as block_head
2 from hr. employees where rownum <5;

SUBSTR (ROWID, 10, 6) BLOCK_HEAD
----------------------------------
AAAABY 88
AAAABY 88
AAAABY 88
AAAABY 88
 
SQL> select header_block, segment_name from dba_segments s
2 where s. segment_name = 'ployees' and s. owner = 'hr ';

HEADER_BLOCK SEGMENT_NAME
----------------------------------
83 EMPLOYEES


SQL> select table_name, tablespace_name from dba_tables t
2 where t. table_name = 'ployees' and t. owner = 'hr ';

TABLE_NAME TABLESPACE_NAME
------------------------------------------------------------
EMPLOYEES EXAMPLE
 
SQL> select file_name from dba_data_files where tablespace_name = 'example ';

FILE_NAME
-------------------------------------
/U01/app/oradata/ora10g/example01.dbf
 
 
[Oracle @ rhel6 ~] $ Dd of =/u01/app/oradata/ora10g/example01.dbf bs = 8192 conv = notrunc seek = 83 <EOF
> Physical upload uption...
> EOF
0 + 1 records in
0 + 1 records out
22 bytes (22 B) copied, 0.00979513 s, 2.2 kB/s
 
 
SQL> select * from hr. employees;
Select * from hr. employees
*
ERROR at line 1:
ORA-01578: ORACLE data block upted (file #5, block #83)
ORA-01110: data file 5: '/u01/app/oradata/ora10g/example01.dbf'
An error will be reported when exp is exported.
[Oracle @ rhel6 ~] $ Exp userid = hr/hr table = employees
LRM-00101: unknown parameter name 'table'
EXP-00019: failed to process parameters, type 'exp HELP = y' for help
EXP-00000: Export terminated unsuccessfully
[Oracle @ rhel6 ~] $ Exp userid = hr/hr table = employees file = emp. dump
LRM-00101: unknown parameter name 'table'
EXP-00019: failed to process parameters, type 'exp HELP = y' for help
EXP-00000: Export terminated unsuccessfully
[Oracle @ rhel6 ~] $ Exp userid = hr/hr tables = employees file = emp. dump
Export: Release 10.2.0.1.0-Production on Thu Jul 21 14:06:52 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in UTF8 character set and UTF8 NCHAR character set
Server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path...
.. Exporting table EMPLOYEES
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block upted (file #5, block #83)
ORA-01110: data file 5: '/u01/app/oradata/ora10g/example01.dbf'
Export terminated successfully with warnings.
The expdp command can export the table structure for Physical Bad blocks and cannot export data.
[Oracle @ rhel6 01] $ expdp hr/hr directory = tmp01 dumpfile = emp. dmp tables = employees
Export: Release 10.2.0.1.0-64bit Production on Thursday, 21 July, 2011 14:16:51
Copyright (c) 2003,200 5, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "HR". "SYS_EXPORT_TABLE_01": hr/********* directory = tmp01 dumpfile = emp. dmp tables = employees
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
.. Exported "HR". "EMPLOYEES" 0 KB 0 rows
Master table "HR". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded
**************************************** **************************************
Dump file set for HR. SYS_EXPORT_TABLE_01 is:
/Tmp/01/emp. dmp
Job "HR". "SYS_EXPORT_TABLE_01" successfully completed at 14:17:10
Analyze command to find Physical Bad blocks
SQL> analyze table employees validate structure cascade;
Analyze table employees validate structure cascade
*
ERROR at line 1:
ORA-01578: ORACLE data block upted (file #5, block #83)
ORA-01110: data file 5: '/u01/app/oradata/ora10g/example01.dbf'
 
[Oracle @ rhel6 ~] $ Dbv file =/u01/app/oradata/ora10g/example01.dbf
DBVERIFY: Release 10.2.0.1.0-Production on Tue Jul 19 16:45:21 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.
DBVERIFY-Verification starting: FILE =/u01/app/oradata/ora10g/example01.dbf
Page 83 is marked upt
Invalid upt block relative dba: 0x01400053 (file 5, block 83)
Bad header found during dbv:
Data in bad block:
Type: 112 format: 0 rdba: 0x206c6163
Last change scn: 0x7075. 72726f63 seq: 0x74 flg: 0x69
Spare1: 0x79 spare2: 0x69 spare3: 0x2e2e
Consistency value in tail: 0xc5232301
Check value in block header: 0x6e6f
Block checksum disabled
 
DBVERIFY-Verification complete
Total Pages Examined: 12800
Total Pages Processed (Data): 4403
Total Pages Failing (Data): 0
Total Pages Processed (Index): 1322
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1527
Total Pages Processed (Seg): 0
Total Pages Failing (Seg): 0
Total Pages Empty: 5547
Total Pages Marked upt: 1
Total Pages Influx: 0
Highest block SCN: 9022372 (0.9022372)
If the db_block_checking parameter is set to false, the system tablespace is not checked for logical Bad blocks.
SQL> show parameter db_block_checking;

NAME TYPE VALUE
--------------------------------------------------------
Db_block_checking string FALSE
 
 
[Oracle @ rhel6 ~] $ Tail-f/u01/app/admin/ora10g/bdump/alert_ora10g.log
Last change scn: 0x0000. 003ec523 seq: 0x1 flg: 0x04
Spare1: 0x31 spare2: 0x31 spare3: 0x0
Consistency value in tail: 0xc5232301
Check value in block header: 0x638b
Computed block checksum: 0xe841
Reread of blocknum = 83, file =/u01/app/oradata/ora10g/example01.dbf. found same encrypt upt data
Reread of blocknum = 83, file =/u01/app/oradata/ora10g/example01.dbf. found same encrypt upt data
Hex dump of (file 5, block 83) in trace file/u01/app/admin/ora10g/bdump/ora10g_smon_9224.trc
Invalid upt block relative dba: 0x01400053 (file 5, block 83)
Bad header found during buffer read
Data in bad block:
Type: 49 format: 1 rdba: 0x01400a31
Last change scn: 0x0000. 003ec523 seq: 0x1 flg: 0x04
Spare1: 0x31 spare2: 0x31 spare3: 0x0
Consistency value in tail: 0xc5232301
Check value in block header: 0x638b
Computed block checksum: 0xe841
Reread of rdba: 0x01400053 (file 5, block 83) found same specified upted data
Corrupt Block Found
TSN = 6, TSNAME = EXAMPLE
RFN = 5, BLK = 83, RDBA = 20971603.
OBJN =-1, OBJD = 51258, OBJECT =, SUBOBJECT =
Segment owner =, segment type =

SQL> select tablespace_id, header_file, header_block
2 from sys_dba_segs s
3 where s. segment_name = 'ployees' and s. owner = 'hr ';

TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------------------------------
6 5 83

 
RMAN> backup validate tablespace example;
RMAN> backup as copy validate tablespace example;
 
SQL> select file #, block #, corruption_type from v $ backup_corruption;

FILE # BLOCK # CORRUPTION_TYPE
-----------------------------------
5 83 bytes upt

SQL> select file #, block #, corruption_type from v $ copy_corruption;

FILE # BLOCK # CORRUPTION_TYPE
-----------------------------------
5 83 bytes upt

SQL> select * from v $ database_block_corruption;

FILE # BLOCK # BLOCKS corrcorruption_change # CORRUPTION_TYPE
---------------------------------------------------------------
5 83 1 0 upt
5 83 1 0 upt
When there is a bad block backup, The blockrecover command automatically skips the backup.
RMAN> run {
2> set maxcompute upt for datafile 5 to 1;
3> backup as copy tablespace example;
4>}
 
RMAN> blockrecover datafile 5 block 83;
Starting blockrecover at 2011-07-21-13:55:58
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 138 devtype = DISK
Channel ORA_DISK_1: restoring block (s) from datafile copy
/U01/app/flash_recovery_area/ORA10G/datafile/o1_mf_example_72f9vxcn _. dbf
Failover to previous backup
Verify repair
[Oracle @ rhel6 ~] $ Dbv userid = hr/hr segment_id = 6.5.83
DBVERIFY: Release 10.2.0.1.0-Production on Thu Jul 21 13:28:35 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.
DBVERIFY-Verification starting: SEGMENT_ID = 6.5.83
 
DBVERIFY-Verification complete
Total Pages Examined: 8
Total Pages Processed (Data): 5
Total Pages Failing (Data): 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2
Total Pages Processed (Seg): 1
Total Pages Failing (Seg): 0
Total Pages Empty: 0
Total Pages Marked success upt: 0
Total Pages Influx: 0
Highest block SCN: 8773930 (0.8773930)

 
SQL> select count (*) from hr. employees;
 
COUNT (*)
----------
110
 
This article is from the "yueda tianchong" blog