ORACLE的system.dbf文件损坏，可否恢复数据库？

ALLSTARS_ORACLE

版主，救命。
如果ORACLE的SYSTEM.DBF文件坏了，有什么办法恢复先前的数据么？

没有恢复，也没有归档，还有办法吗？

ALLSTARS_ORACLE

1. select owner,segment_name
   
2. from dba_extents
   
3. where file_id = 1 and 41928 between block_id and block_id + blocks - 1;
   
4. 
   
5. OWNER      SEGMENT_NAME
   
6. ---------- ------------------
   
7. SYS        I_HH_OBJ#_INTCOL#

复制代码

JOB 8950报错。
Wed Apr 11 22:00:10 2012

Errors in file /oradw/app/oracle/admin/sxdw/bdump/sxdw_ora_29295.trc:
ORA-12012: error on auto execute of job 8950
ORA-01578: ORACLE data block corrupted (file # ORA-01578: ORACLE data block corrupted (file # 1, block # 41928)
ORA-01110: data file 1: 'system01.dbf'
ORA-06512: at "SYS.DBMS_STATS", line 18938
ORA-06512: at "SYS.DBMS_STATS", line 19267
, block # )

ALLSTARS_ORACLE

请执行以下操作并上传输出结果：

1. SQL> select owner, segment_name, segment_type from dba_extents where fild_id = 1 and 41928 between block_id and block_id + blocks -1;

复制代码

如果类型是table或者index，请执行以下命令：

1. SQL> analyze <table or index> validate structure;

复制代码

同时请使用DBV检查这个datafile:
DBVERIFY - Database file Verification Utility (7.3.2 - 11.2) (Doc ID 35512.1)

SQL> select owner, segment_name, segment_type from dba_extents where file_id = 1 and 41928 between block_id and block_id + blocks -1;
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SYS
I_HH_OBJ#_INTCOL#
INDEX
如果类型是table或者index，请执行以下命令：
SQL> analyze <table or index> validate structure;
SQL> analyze index SYS.I_HH_OBJ#_INTCOL# validate structure;
analyze index SYS.I_HH_OBJ#_INTCOL# validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 41928)
ORA-01110: data file 1: '/dw1/sxdw/system01.dbf'
SQL>
同时请使用DBV检查这个datafile:
DBVERIFY - Database file Verification Utility (7.3.2 - 11.2) (Doc ID 35512.1)
select 'dbv file=' || name || ' blocksize='|| block_size || ' logfile=' ||
substr(name, instr(name, '/', -1, 1) +1) ||
'.' || file# || '.log' from v$datafile
where name='/dw1/sxdw/system01.dbf'
/
dbv file=/dw1/sxdw/system01.dbf blocksize=32768 logfile=system01.dbf.1.log


$ dbv file=/dw1/sxdw/system01.dbf blocksize=32768 logfile=system01.dbf.1.log
DBVERIFY: Release 10.2.0.4.0 - Production on Fri Apr 13 09:37:26 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBV-00200: Block, DBA 4236232, already marked corrupt
$
$ more system01.dbf.1.log
DBVERIFY: Release 10.2.0.4.0 - Production on Fri Apr 13 09:37:26 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /dw1/sxdw/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 65536
Total Pages Processed (Data) : 31223
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 9204
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1691
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 23418
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 2734516194 (2972.2734516194)
$

ALLSTARS_ORACLE

1. 请问问题什么时候开始出现的? 三个月前出现。
2. 有没有rman备份？
没有
3. 请检查如下
SELECT table_owner, table_name
FROM dba_indexes WHERE owner='SYS'
AND index_name='I_HH_OBJ#_INTCOL#' ;
SYS HIST_HEAD$
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints WHERE owner='SYS'
AND constraint_name='I_HH_OBJ#_INTCOL#';
空结果

See the following error: Wed Mar 21 22:00:06 2012
Errors in file ora_8952.trc:
ORA-12012: error on auto execute of job 8950
ORA-01578: ORACLE data block corrupted (file # ORA-01578: ORACLE data block corrupted (file # 1, block # 41928)
ORA-01110: data file 1: 'system01.dbf'
ORA-06512: at "SYS.DBMS_STATS", line 18938
ORA-06512: at "SYS.DBMS_STATS", line 19267
, block # )
SQL> select owner, segment_name, segment_type from dba_extents where file_id = 1 and 41928 between block_id and block_id + blocks -1;
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SYS
I_HH_OBJ#_INTCOL#
INDEX
如果类型是table或者index，请执行以下命令：
SQL> analyze <table or index> validate structure;
SQL> analyze index SYS.I_HH_OBJ#_INTCOL# validate structure;
analyze index SYS.I_HH_OBJ#_INTCOL# validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 41928)
ORA-01110: data file 1: 'system01.dbf'
SELECT table_owner, table_name
FROM dba_indexes WHERE owner='SYS'
AND index_name='I_HH_OBJ#_INTCOL#' ;

keyword:Block Corruptions
Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g (Doc ID 28814.1)
Keyword:EVENT: 38003
EVENT:
38003 "CBO Disable column stats for the dictionary objects in recursive
SQL" - "Mark objects as non-bootstrapped" (Doc ID 279797.1)

Based upon the recent ORA-08103 trace, the I_HH_OBJ#_INTCOL# index is corrupt within the c_obj#_intcol# cluster.
The hist_head$ table is part of that cluster.
To drop and recreate this index, please exactly follow the following steps.
1. shutdown immediate
2. take a cold backup
3. startup migrate
4. alter session set events "38003 trace name context forever, level 10";
(Note: Event 38003 is used to set these objects as non-bootstrapped on startup migrate)
5. drop index i_hh_obj#_col#;
6. create index i_hh_obj#_col# on hist_head$(obj#, col#) storage (maxextents unlimited);
7.NOTE: ONLY NEEDED FOR 8i SQL> alter session set "_DB_ALWAYS_CHECK_SYSTEM_TS"=false;
For corruption on Index I_HH_OBJ#_INTCOL#
Edit your init@SID.ora and add
EVENT="38003 trace name context forever, level 10"
Bounce the instance.
Get the index DDL/Definition by running:
set long 100000
set linesize 200 trimspool on
set heading off pagesize 0
spool cridx.sql
select dbms_metadata.get_ddl('INDEX','I_HH_OBJ#_INTCOL#','SYS') from dual;
spool off
Then Drop/Recreate Index SYS.I_HH_OBJ#_INTCOL#



To drop and recreate this index, please exactly follow the following steps.
For corruption on Index I_HH_OBJ#_INTCOL#
1. shutdown immediate
2. take a cold backup of the database
3.startup and create a pfile
For example:
SQL> create pfile='/tmp/p10204.ora' from spfile;
Add the following in the pfile
EVENT="38003 trace name context forever, level 10"
4. Restart the instance.
SQL> shutdown immediate
SQL> startup pfile='/tmp/p10204.ora';
5. Get the index DDL/Definition by running:
set long 100000
set linesize 200 trimspool on
set heading off pagesize 0
spool cridx.sql
select dbms_metadata.get_ddl('INDEX','I_HH_OBJ#_INTCOL#','SYS') from dual;
6. Then Drop/Recreate Index SYS.I_HH_OBJ#_INTCOL#


spool off
I tried to rebuild the index by my test environment but got the errors without setting EVENT: 38003
SQL> drop index sys.I_HH_OBJ#_INTCOL# ;
drop index sys.I_HH_OBJ#_INTCOL#
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SQL> alter index sys.I_HH_OBJ#_INTCOL# rebuild online;
alter index sys.I_HH_OBJ#_INTCOL# rebuild online
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SQL> alter index sys.I_HH_OBJ#_INTCOL# rebuild;
alter index sys.I_HH_OBJ#_INTCOL# rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
EVENT: 38003 "CBO Disable column stats for the dictionary objects in recursive SQL" - "Mark objects as non-bootstrapped" (Doc ID 279797.1)
EVENT: 38003 "CBO Disable column stats for the dictionary objects in recursive SQL" - "Mark objects as non-bootstrapped" (Doc ID 279797.1)


@ INTERNAL ONLY!
@
@ This event should not be used by customers unless specifically asked.
@ Keeping distribution as internal, ie. not visible to customers.
Event:38003
~~~~~~~~~~~
Version/Use:
9.2.0 - "CBO Disable column stats for the dictionary objects in recursive SQL"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTE: Events should NEVER be set by customers unless advised to do so by
Oracle Support Services. Read Note:75713.1 before setting any event.
Summary Syntax:
EVENT="38003 trace name context forever, level 10"
** IMPORTANT: Do **NOT** use ALTER SESSION SET EVENTS or ORADEBUG
syntax to set this event in sessions.
See bug:2230243.
Levels:
Setting the level to any non-zero value will activate the event
Description/Steps:
The event is to disable the fix done in bug:2230243, in case of unexpected problems.
Event 38003 affects the bootstrap process of loading the fixed cache in
kqlblfc(). Per default certain objects are marked as bootstrap objects
(even though they are not defined as such in sys.bootstrap$) but by setting
the event they will be left as non-bootstrapped.
The objects affected are defined by kqlrtbso:


hist_head$
histgrm$
i_hh_obj#_col#
i_hh_obj#_intcol#
i_obj#_intcol#
i_h_obj#_col#
c_obj#_intcol#
From 10.1 the following objects have been added:
fixed_obj$
tab_stats$
ind_stats$
i_fixed_obj$_obj#
i_tab_stats$_obj#
i_ind_stats$_obj#
object_usage
From 11.1 the following object has been added:
partobj$
The event can be handy when fixing eg. an index corruption on c_obj#_intcol#.
Otherwise error "ORA-701 object necessary for warm starting database cannot be altered"
is produced if any DDL is applied to any of those objects.
Another approach to allow DDL on these objects would be to start the database
in migrate mode.