在初始化参数文件中加上这两句：
event = "10231 trace name context forever,level 10"
event = "10233 trace name context forever,level 10"
头一个事件指定在进行全表扫描时跳过损坏的块，第二个事件指定在进行index range scan时跳过损坏的块。然后把表倒出。另使用dbms_repair也可以，还有使用bbed也可以，这是Oracle本身提供的工具，不过很烦的。如果能上metalink的话，可以到那儿的论坛上找一下ora-01578
event = "10231 trace name context forever,level 10"
event = "10233 trace name context forever,level 10"
头一个事件指定在进行全表扫描时跳过损坏的块，第二个事件指定在进行index range scan时跳过损坏的块。然后把表倒出。另使用dbms_repair也可以，还有使用bbed也可以，这是Oracle本身提供的工具，不过很烦的。如果能上metalink的话，可以到那儿的论坛上找一下ora-01578
1、首先确认是什么坏了（索引、表、回滚段还是临时段）,及哪个段坏了。
以dba用户连入oracle
SQL>Select owner,segment_name,segment_type from dba_extents where file_id=<F> and <B> between block_id and block_id+blocks-1;(<F>和<B>分别是ORA-01578报出的坏块出现的文件号和块号)
2、如果是索引段或临时段坏了，删了重建就可以，这里不说了，看你的情况也不是回滚段坏了，下面对表中出现坏块的处理方法说一下。
3、对表坏块的处理。
a、以表的owner连入oracle
b、sql>ALTER SYSTEM SET EVENTS ‘10231 trace name context forever,level 10’;
c、创建一个临时表：SQL>create table errortemp as select * from error;(error是坏表的表名)
d、rename坏表，把临时表rename成坏表的表名
sql>alter table error rename to error_bak;
sql>alter table errortemp rename to error;
e、在表创建索引、约束、授权、trigger等对象.
4、利用表之间的业务关系，把坏块中的数据补足。
- OS problems
- Oracle problems
- Recovering through "UNRECOVERABLE" or "NOLOGGING" database actions
(in which case ORA-1578 is expected behaviour - see below) The point in time when an Oracle error is raised may be much later than
when any corruption initially occurred. As the root cause is not usually known at the time the corruption is
encountered, and as in most cases the key requirement is to get up
and running again, then the steps used tackle corruption problems in
this article are: 1) Determine the extent of the corruption problems
and also determine if the problems are permanent or transient. If the problem is widespread or the errors move about
then focus on identifying the cause first (check hardware
etc..). This is important as there is no point recovering
a system if the underlying hardware is faulty. 2) Replace or move away from any faulty or suspect hardware. 3) Determine which database objects are affected. 4) Choose the most appropriate database recovery / data salvage
option.
For all steps above it is sensible to collect evidence and
document exactly what actions are being taken. The 'Evidence>>'
tags in this article list the information which should be collected
to assist with identifying the root cause of the problem.
Corruption due to NOLOGGING or UNRECOVERABLE
If a NOLOGGING (or UNRECOVERABLE) operation is performed on an
object and the datafile containing that object is subsequently
recovered then the data blocks affected by the NOLOGGING operation
are ed as corrupt and will signal an ORA-1578 error when
accessed. In Oracle8i an ORA-26040 is also signalled
("ORA-26040: Data block was loaded using the NOLOGGING option" )
which makes the cause fairly obvious, but earlier releases have no
additional error message. If a block is corrupt due to recovery
through a NOLOGGING operation then you can use this article from
Section 3 "Which Objects are Affected ?" onwards but note that:
(a) Recovery cannot retrieve the NOLOGGING data
(b) No data is salvagable from inside the block
(1) Determine the Extent of the Corruption Problem Whenever a corruption error occurs note down the FULL error message/s
and look in the instance's alert log and trace files for any associated
errors. It is important to do this first to assess whether this is
a single block corruption, an error due to an UNRECOVERABLE operation
or a more severe issue. It is a good idea to scan affected files (and any important files)
with DBVERIFY to check for other corruptions in order to determine
the extent of the problem.
For details of using DBVERIFY see <Note:35512.1> Once you have determined a list of corrupt file/block combinations
then the steps below can be used to help determine what action
can be taken. Evidence>>
- Record the original error in full, along with details of
the application which encountered the error.
- Save an extract from the alert log from a few hours before
the FIRST recorded problem up to the current point in time.
- Save any tracefiles mentioned in the alert log.
- Record any recent OS problems you have encountered.
- Note if you are using any special features - Eg: ASYNC IO,
fast write disk options etc..
- Record your current BACKUP position (Dates, Type etc...)
- Note if your database is in ARCHIVELOG mode or not
Eg: Issue "ARCHIVE LOG LIST" in Server Manager or SQL*Plus.
If there is a hardware fault or a suspect component then it is sensible
to either repair the problem, or make disk space available on a
separate disk sub-system prior to proceeding with a recovery option. IMPORTANT: If there are multiple errors (which are NOT due to NOLOGGING)
OR You have OS level errors against the affected file
OR The errors are transient and keep moving about
then there is little point proceeding until the underlying problem
has been addressed or space is available on alternative disks.
Get your hardware vendor to check the system over and contact
Oracle Support with details of all errors. You can move datafiles about using the following steps: 1. Make sure the file to be relocated is either OFFLINE or
the instance is in the MOUNT state (not open) 2. Physically restore (or copy) the datafile to its new location
eg: /newlocation/myfile.dbf 3. Tell Oracle the new location of the file.
eg: ALTER DATABASE RENAME FILE '/oldlocation/myfile.dbf'
TO '/newlocation/myfile.dbf';
(Note that you cannot RENAME a TEMPFILE - TEMPFILEs should
be dropped and recreated at the new location) (3) Which Objects are Affected ? It is best to determine which objects are affected BEFORE making any
decisions about how to recover - this is because the corruption/s may be
on object/s which can easily be re-created.
Eg: For a corruption on a 5 row lookup table it may be far quicker to
drop and recreate the table than to perform a recovery. For each corruption collect the information in the following table.
The steps to do this are explained below. *
Information to Record for each Corruption Original
Error Absolute
File#
<AFN> Relative
File#
<RFN> Block# <BL> Tablespace Segment
Type Segment
Owner.Name Related
Objects Recovery
Options
anyway , in general , you can take hrb_qiuyb(大森林)'s solution
如果alter system报错，将alter system 改为alter session.