Explain how to process bad Oracle 9i data blocks

I SELECT a table in a production test database when a ORA-01578, a block damage, previously learned how to deal with block damage, to really have not met, today finally let me met, it's still a production test database, so you don't have to be nervous.

The database version is 9.2.0.4. The RMAN command of Oracle9i has a blockrecover command to fix bad Blocks online. The following describes how to fix Bad blocks using RMAN.

SQL> conn owi/owi Connected. SQL> select * from dpa_history; select * from dpa_history * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 15, block # 18) ORA-01110: data file 15: '/d01/app/oracle/oradata/dpa/dpa01.dbf'

Report ORA-01578 data block corruption, the following use RMAN command to query whether blockrecover command can be used to recover and how to recover

Use rman to log on to the catalog Database

[ora9@rmanserver ~]$ rman target sys/oracle@dpa catalog rman/rman Recovery Manager: Release 9.2.0.8.0 - Production Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved. connected to target database: DPA (DBID=843495022) connected to recovery catalog database

Search for the latest full backup of datafile 15. This afternoon, we just performed a full backup of RMAN.

RMAN> list backup of datafile 15;

List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
643     Full    64K        DISK        00:00:27     16-MAR-09      
BP Key: 650   Status: AVAILABLE   Tag: TAG20090316T154352
Piece Name: /d02/fullbackup/20090316_data_24_1
List of Datafiles in backup set 643
File LV Type Ckp SCN    Ckp Time  Name
---- -- ---- ---------- --------- ----
15      Full 11856250905 16-MAR-09 /d01/app/oracle/oradata/dpa/dpa01.dbf

Check whether archivelog after SCN 11856250905 has been backed up.

RMAN> list backup of archivelog scn from 11856250905 List of Backup Sets =================== BS Key  Size       Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 680     265K       DISK        00:00:00     16-MAR-09       BP Key: 681   Status: AVAILABLE   Tag: TAG20090316T154731 Piece Name: /d02/fullbackup/20090316_arch_28 List of Archived Logs in backup set 680 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time ---- ------- ---------- --------- ---------- --------- 1    109     11856250805 16-MAR-09 11856251483 16-MAR-09 1    110     11856251483 16-MAR-09 11856251487 16-MAR-09

Check whether archivelog after sequence 110 has been backed up.

RMAN> list copy of archivelog from sequence 110; List of Archived Log Copies Key Thrd Seq S Low Time Name -------------------------------- 694 1 111 A 16-MAR-09/d02/arch/127111.dbf 695 1 112 A 16-MAR-09/d02/arch/127112.dbf Query online archive logs SQL> select sequence #, members, archived, status from v $ log; SEQUENCE # MEMBERS ARC STATUS --------------------------------------- 113 1 NO CURRENT 111 1 YES INACTIVE 112 1 YES INACTIVE

From the above query, we can see that datafile 15 has one recent full backup, and all archivelog and online redo log since the full backup
The following code starts blockreocver. The command is actually very simple.

RMAN> blockrecover datafile 15 block 18;
Starting blockrecover at 16-MAR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00015
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/d02/fullbackup/20090316_data_24_1 tag=TAG20090316T154352 params=NULL
channel ORA_DISK_1: block restore complete
starting media recovery
archive log thread 1 sequence 111 is already on disk as file /d02/arch/1_111.dbf
archive log thread 1 sequence 112 is already on disk as file /d02/arch/1_112.dbf
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=109
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=110
channel ORA_DISK_1: restored backup piece 1
piece handle=/d02/fullbackup/20090316_arch_28 tag=TAG20090316T154731 params=NULL
channel ORA_DISK_1: restore complete
media recovery complete
Finished blockrecover at 16-MAR-09

SELECT the DPA_HISTORY table.

SQL> select * from dpa_history;
PRODLINEID BARCODE                        PA
---------- ------------------------------ --
7          S*33040-D8311050149512B        03
7          S*33040-D8311050143512B        03
7          S*33040-D8311050140512B        03
7          S*33040-D8311050144512B        03
7          S*33040-D8311050151512B        03
7          S*33040-D8311050262512B        03
7          S*33040-D8311050552512B        03
7          S*33040-D8311050345512B        03
7          S*33040-D8311050170512B        03

1. Batch install Oracle clients with Instant client
2. Auto-tuning in Oracle 11g
3. In-depth analysis of Oracle data block principles