ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)
本帖最后由 saup007 于 2014-5-26 11:53 编辑内部使用的数据库，之前断电了，undo坏块了，尝试使用隐藏参数，库启来了。（虽然有备份）但，最后还是用备份恢复一次。
今天早上来公司，alter还是有坏块！Fri May 23 22:00:05 2014
GATHER_STATS_JOB encountered errors. Check the trace file.
Fri May 23 22:00:05 2014
Errors in file /u01/app/oracle/admin/MYSID/bdump/MYSID_j001_16658.trc:
ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)
ORA-01110: data file 3: '/u01/oradata/MYSID/sysaux.dbf'
Sat May 24 06:00:05 2014
Errors in file /u01/app/oracle/admin/MYSID/bdump/MYSID_j001_12393.trc:
ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)
ORA-01110: data file 3: '/u01/oradata/MYSID/sysaux.dbf'从trace文件可知，是收集统计信息是，报的错：$ vim /u01/app/oracle/admin/MYSID/bdump/MYSID_j001_12393.trc
/u01/app/oracle/admin/MYSID/bdump/MYSID_j001_12393.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: MYSID1
Release: 2.6.9-55.ELsmp
Version: #1 SMP Fri Apr 20 17:03:35 EDT 2007
Machine: i686
Instance name: MYSID
Redo thread mounted by this instance: 1
Oracle process number: 31
Unix process pid: 12393, image: oracle@MYSID1 (J001)
*** ACTION NAME:(GATHER_STATS_JOB) 2014-05-24 06:00:05.678
*** MODULE NAME:(DBMS_SCHEDULER) 2014-05-24 06:00:05.678
*** SERVICE NAME:(SYS$USERS) 2014-05-24 06:00:05.678
*** SESSION ID:(1090.6978) 2014-05-24 06:00:05.678
ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)
ORA-01110: data file 3: '/u01/oradata/MYSID/sysaux.dbf'
*** 2014-05-24 06:00:05.679
GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"WRH$_COMP_IOSTAT"','""', ...)
ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)
ORA-01110: data file 3: '/u01/oradata/MYSID/sysaux.dbf'查看坏块：SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ---------------
2 2 1 0 CORRUPT
3 50481 1 0 CORRUPT
SQL>
SQL>
SQL>
SQL> SELECT tablespace_name, segment_type, owner, segment_name
2 FROM dba_extents
3 WHERE file_id = 3
4 and 50481 between block_id AND block_id + blocks - 1;
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
------------------------------ ------------------ ------------------------------ ------------------------------------
SYSAUX TABLE SYS WRH$_COMP_IOSTAT
SQL> SELECT tablespace_name, segment_type, owner, segment_name
2 FROM dba_extents
3 WHERE file_id = 2
4 and 2 between block_id AND block_id + blocks - 1;
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
------------------------------ ------------------ ------------------------------ ---------------------------------------------------------------------------
file_id =2 应该是原来undo上的坏块，但现在已经没有对应的数据库对象了，只有sysaux有一条记录...$ dbv file=/u01/oradata/MYSID/sysaux.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on Mon May 26 11:52:27 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oradata/MYSID/sysaux.dbf
DBV-00200: Block, DBA 12633393, already marked corrupt
DBVERIFY - Verification complete
Total Pages Examined : 86016
Total Pages Processed (Data) : 38261
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 24716
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14080
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 8959
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 1608730663 (0.1608730663) 本帖最后由 saup007 于 2014-5-26 11:46 编辑
sys@MYSID> select count(1) from sys.WRH$_COMP_IOSTAT; --走索引，所以能count
COUNT(1)
----------
1542
sys@MYSID> select * from sys.WRH$_COMP_IOSTAT; -- 这种走全表了
...........
...........
ERROR:
ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)
ORA-01110: data file 3: '/u01/oradata/EUIS/sysaux.dbf'
600 rows selected.
我需要按PO主的文章：http://www.askmaclean.com/archives/how-rebuild-create-sysaux-tablespace.html
重建SYSAUX上的对象吗？
或许有其他方法吗？
非常感谢。 RMAN 好象不能恢复！RMAN> blockrecover datafile 3 block 50481;
Starting blockrecover at 26-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1094 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1080 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 00003
channel ORA_DISK_1: reading from backup piece /u01/backup/rmanbak/bak_level0/data_level0_20140525_MYSID_16568_1_1.bak1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u01/backup/rmanbak/bak_level0/data_level0_20140525_MYSID_16568_1_1.bak1 tag=LEVEL0_DATA
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:26
failover to previous backup
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00003
channel ORA_DISK_1: reading from backup piece /u01/saup/rman_standby/orcl_3tp8ri6c_1_1_20140521.dbf
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u01/saup/rman_standby/orcl_3tp8ri6c_1_1_20140521.dbf tag=TAG20140521T135940
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:35
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 05/26/2014 10:34:41
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN> exit 操作步骤：
show parameter cluster_database
show parameter statistics_level
show parameter sga_target
create pfile='?/dbs/init@.ora.20140527' from spfile;
alter system set shared_pool_size = 200m scope = spfile;
alter system set db_cache_size = 300m scope = spfile;
alter system set java_pool_size = 100 scope = spfile;
alter system set large_pool_size = 50 scope = spfile;
--alter system reset sga_target scope= spfile; --这二个在10g不能执行，memory_target是11g参数！改为设置sga_target为0
--alter system reset memory_target scope= spfile;
alter system set statistics_level=basic scope=spfile;
alter system set sga_target=0 scope=spfile;
shutdown immediate
startup restrict
@?/rdbms/admin/catnoawr.sql
alter system flush shared_pool;
@?/rdbms/admin/catawrtb.sql 「@?/rdbms/admin/catsvrm.sql –in the script had calls catawrtb.sql，我看了下，11g中catsvrm.sql不包含catawrtb.sql」
此处报二个type已经存在，此时我忽略了
create type AWRSQRPT_TEXT_TYPE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
create type AWRSQRPT_TEXT_TYPE_TABLE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
编译
start ?/rdbms/admin/utlrp.sql
select owner,object_name from dba_objects where status='INVALID';
参考(文档 ID 782974.1) 检查下
shutdown immediate
create spfile from pfile='?/dbs/init@.ora.20140527' ;
startup
测试
exec dbms_workload_repository.create_snapshot;
隔几分钟
exec dbms_workload_repository.create_snapshot;
@?/rdbms/admin/awrrpt.sql
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
MYSID MYSID 1 27 May 2014 15:39 1
2 27 May 2014 15:42 1
rman检查下：
RMAN> backup validate check logical datafile 3;
记录依然存在，这个可能是BUG
sys@MYSID> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
2 2 1 0 CORRUPT
3 50481 1 0 CORRUPT
收集此表统计信息没有问题了
begin
dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRH$_COMP_IOSTAT' ,estimate_percent => 5 ,cascade => true);
end;
/
页:
[1]