ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)

saup007

内部使用的数据库，之前断电了，undo坏块了，尝试使用隐藏参数，库启来了。（虽然有备份）但，最后还是用备份恢复一次。

今天早上来公司，alter还是有坏块！

1. Fri May 23 22:00:05 2014
   
2. GATHER_STATS_JOB encountered errors.  Check the trace file.
   
3. Fri May 23 22:00:05 2014
   
4. Errors in file /u01/app/oracle/admin/MYSID/bdump/MYSID_j001_16658.trc:
   
5. ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)
   
6. ORA-01110: data file 3: '/u01/oradata/MYSID/sysaux.dbf'         
   
7. 
   
8. 
   
9. Sat May 24 06:00:05 2014
   
10. Errors in file /u01/app/oracle/admin/MYSID/bdump/MYSID_j001_12393.trc:
    
11. ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)
    
12. ORA-01110: data file 3: '/u01/oradata/MYSID/sysaux.dbf'

复制代码

从trace文件可知，是收集统计信息是，报的错：

1. $ vim /u01/app/oracle/admin/MYSID/bdump/MYSID_j001_12393.trc
   
2. /u01/app/oracle/admin/MYSID/bdump/MYSID_j001_12393.trc
   
3. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
   
4. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
5. ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
   
6. System name:    Linux
   
7. Node name:      MYSID1
   
8. Release:        2.6.9-55.ELsmp
   
9. Version:        #1 SMP Fri Apr 20 17:03:35 EDT 2007
   
10. Machine:        i686
    
11. Instance name: MYSID
    
12. Redo thread mounted by this instance: 1
    
13. Oracle process number: 31
    
14. Unix process pid: 12393, image: oracle@MYSID1 (J001)
    
15. 
    
16. *** ACTION NAME:(GATHER_STATS_JOB) 2014-05-24 06:00:05.678
    
17. *** MODULE NAME:(DBMS_SCHEDULER) 2014-05-24 06:00:05.678
    
18. *** SERVICE NAME:(SYS$USERS) 2014-05-24 06:00:05.678
    
19. *** SESSION ID:(1090.6978) 2014-05-24 06:00:05.678
    
20. ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)
    
21. ORA-01110: data file 3: '/u01/oradata/MYSID/sysaux.dbf'
    
22. *** 2014-05-24 06:00:05.679
    
23. GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"WRH$_COMP_IOSTAT"','""', ...)
    
24. ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)
    
25. ORA-01110: data file 3: '/u01/oradata/MYSID/sysaux.dbf'

复制代码

查看坏块：

1. SQL> select * from v$database_block_corruption;
   
2. 
   
3.      FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
   
4. ---------- ---------- ---------- ------------------ ---------------
   
5.          2          2          1                  0 CORRUPT
   
6.          3      50481          1                  0 CORRUPT
   
7. 
   
8. SQL>
   
9. SQL>
   
10. SQL>
    
11. SQL> SELECT tablespace_name, segment_type, owner, segment_name
    
12.   2    FROM dba_extents
    
13.   3   WHERE file_id = 3
    
14.   4     and 50481 between block_id AND block_id + blocks - 1;
    
15.   
    
16. 
    
17. TABLESPACE_NAME                SEGMENT_TYPE       OWNER                          SEGMENT_NAME
    
18. ------------------------------ ------------------ ------------------------------ ------------------------------------
    
19. SYSAUX                         TABLE              SYS                            WRH$_COMP_IOSTAT
    
20. SQL> SELECT tablespace_name, segment_type, owner, segment_name
    
21.   2    FROM dba_extents
    
22.   3   WHERE file_id = 2
    
23.   4     and 2 between block_id AND block_id + blocks - 1;
    
24. 
    
25. TABLESPACE_NAME                SEGMENT_TYPE       OWNER                          SEGMENT_NAME
    
26. ------------------------------ ------------------ ------------------------------ ---------------------------------------------------------------------------
    

复制代码

file_id =2 应该是原来undo上的坏块，但现在已经没有对应的数据库对象了，只有sysaux有一条记录...

1. $ dbv file=/u01/oradata/MYSID/sysaux.dbf blocksize=8192
   
2. 
   
3. DBVERIFY: Release 10.2.0.4.0 - Production on Mon May 26 11:52:27 2014
   
4. 
   
5. Copyright (c) 1982, 2007, Oracle.  All rights reserved.
   
6. 
   
7. DBVERIFY - Verification starting : FILE = /u01/oradata/MYSID/sysaux.dbf
   
8. 
   
9. DBV-00200: Block, DBA 12633393, already marked corrupt
   
10. 
    
11. 
    
12. DBVERIFY - Verification complete
    
13. 
    
14. Total Pages Examined         : 86016
    
15. Total Pages Processed (Data) : 38261
    
16. Total Pages Failing   (Data) : 0
    
17. Total Pages Processed (Index): 24716
    
18. Total Pages Failing   (Index): 0
    
19. Total Pages Processed (Other): 14080
    
20. Total Pages Processed (Seg)  : 0
    
21. Total Pages Failing   (Seg)  : 0
    
22. Total Pages Empty            : 8959
    
23. Total Pages Marked Corrupt   : 1
    
24. Total Pages Influx           : 0
    
25. Highest block SCN            : 1608730663 (0.1608730663)

复制代码

saup007

1. sys@MYSID> select count(1) from sys.WRH$_COMP_IOSTAT; --走索引，所以能count
   
2. 
   
3.   COUNT(1)
   
4. ----------
   
5.       1542
   
6. 
   
7. sys@MYSID> select * from sys.WRH$_COMP_IOSTAT;  -- 这种走全表了
   
8. 
   
9. ...........
   
10. ...........
    
11. ERROR:
    
12. ORA-01578: ORACLE data block corrupted (file # 3, block # 50481)
    
13. ORA-01110: data file 3: '/u01/oradata/EUIS/sysaux.dbf'
    
14. 600 rows selected.
    
15. 
    

复制代码

我需要按PO主的文章：http://www.askmaclean.com/archives/how-rebuild-create-sysaux-tablespace.html

重建SYSAUX上的对象吗？

或许有其他方法吗？

非常感谢。

saup007

RMAN 好象不能恢复！

1. RMAN> blockrecover datafile 3 block 50481;
   
2. 
   
3. Starting blockrecover at 26-MAY-14
   
4. using target database control file instead of recovery catalog
   
5. allocated channel: ORA_DISK_1
   
6. channel ORA_DISK_1: sid=1094 devtype=DISK
   
7. allocated channel: ORA_DISK_2
   
8. channel ORA_DISK_2: sid=1080 devtype=DISK
   
9. 
   
10. channel ORA_DISK_1: restoring block(s)
    
11. channel ORA_DISK_1: specifying block(s) to restore from backup set
    
12. restoring blocks of datafile 00003
    
13. channel ORA_DISK_1: reading from backup piece /u01/backup/rmanbak/bak_level0/data_level0_20140525_MYSID_16568_1_1.bak1
    
14. channel ORA_DISK_1: restored block(s) from backup piece 1
    
15. piece handle=/u01/backup/rmanbak/bak_level0/data_level0_20140525_MYSID_16568_1_1.bak1 tag=LEVEL0_DATA
    
16. channel ORA_DISK_1: block restore complete, elapsed time: 00:00:26
    
17. failover to previous backup
    
18. 
    
19. channel ORA_DISK_1: restoring block(s)
    
20. channel ORA_DISK_1: specifying block(s) to restore from backup set
    
21. restoring blocks of datafile 00003
    
22. channel ORA_DISK_1: reading from backup piece /u01/saup/rman_standby/orcl_3tp8ri6c_1_1_20140521.dbf
    
23. channel ORA_DISK_1: restored block(s) from backup piece 1
    
24. piece handle=/u01/saup/rman_standby/orcl_3tp8ri6c_1_1_20140521.dbf tag=TAG20140521T135940
    
25. channel ORA_DISK_1: block restore complete, elapsed time: 00:00:35
    
26. failover to previous backup
    
27. 
    
28. RMAN-00571: ===========================================================
    
29. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    
30. RMAN-00571: ===========================================================
    
31. RMAN-03002: failure of blockrecover command at 05/26/2014 10:34:41
    
32. RMAN-06026: some targets not found - aborting restore
    
33. RMAN-06023: no backup or copy of datafile 3 found to restore
    
34. 
    
35. RMAN> exit

复制代码

saup007

1. 操作步骤：
   
2. 
   
3. show parameter cluster_database
   
4. show parameter statistics_level
   
5. show parameter sga_target
   
6. 
   
7. create pfile='?/dbs/init@.ora.20140527' from spfile;
   
8. 
   
9. alter system set shared_pool_size = 200m scope = spfile;
   
10. alter system set db_cache_size = 300m scope = spfile;
    
11. alter system set java_pool_size = 100 scope = spfile;
    
12. alter system set large_pool_size = 50 scope = spfile;
    
13. --alter system reset sga_target scope= spfile;        --这二个在10g不能执行，memory_target是11g参数！改为设置sga_target为0
    
14. --alter system reset memory_target scope= spfile;  
    
15. alter system set statistics_level=basic scope=spfile;
    
16. 
    
17. alter system set sga_target=0 scope=spfile;
    
18. 
    
19. shutdown immediate
    
20. startup restrict
    
21. 
    
22. @?/rdbms/admin/catnoawr.sql
    
23. 
    
24. alter system flush shared_pool;
    
25. 
    
26. @?/rdbms/admin/catawrtb.sql     「@?/rdbms/admin/catsvrm.sql –in the script had calls catawrtb.sql，我看了下，11g中catsvrm.sql不包含catawrtb.sql」
    
27. 
    
28. 
    
29. 此处报二个type已经存在，此时我忽略了
    
30. 
    
31. create type AWRSQRPT_TEXT_TYPE
    
32.             *
    
33. ERROR at line 1:
    
34. ORA-00955: name is already used by an existing object
    
35. 
    
36. 
    
37. create type AWRSQRPT_TEXT_TYPE_TABLE
    
38.             *
    
39. ERROR at line 1:
    
40. ORA-00955: name is already used by an existing object
    
41. 
    
42. 
    
43. 编译
    
44. 
    
45. start ?/rdbms/admin/utlrp.sql
    
46. 
    
47. select owner,object_name from dba_objects where status='INVALID';
    
48. 
    
49. 参考(文档 ID 782974.1) 检查下
    
50. 
    
51. shutdown immediate
    
52. 
    
53. create spfile from pfile='?/dbs/init@.ora.20140527' ;
    
54. 
    
55. startup
    
56. 
    
57. 测试
    
58. 
    
59. exec dbms_workload_repository.create_snapshot;
    
60. 
    
61. 隔几分钟
    
62. 
    
63. exec dbms_workload_repository.create_snapshot;
    
64. 
    
65. @?/rdbms/admin/awrrpt.sql
    
66. 
    
67. 
    
68. 
    
69.                                                         Snap
    
70. Instance     DB Name        Snap Id    Snap Started    Level
    
71. ------------ ------------ --------- ------------------ -----
    
72. MYSID         MYSID                 1 27 May 2014 15:39      1
    
73.                                   2 27 May 2014 15:42      1
    
74. 
    
75. 
    
76. 
    
77. rman检查下：
    
78. 
    
79. RMAN> backup validate check logical datafile 3;
    
80. 
    
81. 记录依然存在，这个可能是BUG
    
82. 
    
83. sys@MYSID> select * from v$database_block_corruption;
    
84. 
    
85.      FILE#     BLOCK#       BLOCKS CORRUPTION_CHANGE# CORRUPTIO
    
86. ---------- ---------- ---------- ------------------ ---------
    
87.      2         2            1            0 CORRUPT
    
88.      3     50481            1            0 CORRUPT
    
89. 
    
90. 收集此表统计信息没有问题了
    
91. 
    
92. begin
    
93.   dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRH$_COMP_IOSTAT' ,estimate_percent => 5  ,cascade => true);
    
94. end;
    
95. /

复制代码