記一次sysaux表空間壞塊修復

spectre2發表於2015-09-15
UX
正常巡檢一個庫時，發現定時exp有問題：
EXP-00008: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "WMSYS.LT_EXPORT_PKG", line 1379
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling WMSYS.LT_EXPORT_PKG.system_info_exp
EXP-00008: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 664
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 1878
ORA-06512: at "SYS.DBMS_SCHED_ATTRIBUTE_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_ATTRIBUTE_EXPORT.create_exp for object 12196
。。。
這是temp表空間的問題，但是為什麼會出這個問題呢？
1、檢查alert_orcl.log
Errors in file /opt/app/oracle/diag/rdbms/data/orcl/trace/orcl_m000_22700.trc  (incident=557043):
ORA-01578: ORACLE data block corrupted (file # 2, block # 68896)
ORA-01110: data file 2: '/opt/app/oracle/oradata/data/sysaux01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /opt/app/oracle/diag/rdbms/data/orcl/incident/incdir_557043/orcl_m000_22700_i557043.trc
Errors in file /opt/app/oracle/diag/rdbms/data/orcl/trace/orcl_m000_22700.trc  (incident=557044):
ORA-01578: ORACLE data block corrupted (file # ORA-01578: ORACLE data block corrupted (file # 2, block # 68896)
ORA-01110: data file 2: '/opt/app/oracle/oradata/data/sysaux01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
, block # )
Incident details in: /opt/app/oracle/diag/rdbms/data/orcl/incident/incdir_557044/orcl_m000_22700_i557044.trc
Mon Sep 14 22:00:13 2015
Trace dumping is performing id=[cdmp_20150914220013]
Mon Sep 14 22:00:13 2015
Sweep [inc][557044]: completed
Sweep [inc][557043]: completed
Sweep [inc2][557044]: completed
Sweep [inc2][557043]: completed
確定是資料塊損壞。
2、使用dbv檢查一下：
~]$dbv file='/opt/app/oracle/oradata/data/sysaux01.dbf' blocksize=8192
DBVERIFY: Release 11.2.0.1.0 - Production on Tue Sep 15 11:50:11 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /opt/app/oracle/oradata/data/sysaux01.dbf
DBV-00201: Block, DBA 8457504, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8457565, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8457569, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459538, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459542, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459546, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459550, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459551, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459554, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459584, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459588, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459592, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459615, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459619, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459623, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459627, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459631, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459632, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459635, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459636, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459639, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459643, marked corrupt for invalid redo application
DBV-00201: Block, DBA 8459647, marked corrupt for invalid redo application

DBVERIFY - Verification complete
Total Pages Examined         : 256000
Total Pages Processed (Data) : 30735
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 17923
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 9562
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 19617
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 178163
Total Pages Marked Corrupt   : 23
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 552361684 (27.552361684)

有23個標記壞塊，檢視一下都分佈在哪些segment上：
在11g下:
通過以下方法獲取其 file_id 和 block_id.
獲取檔案號（file_id)的方法：
SQL>  select dbms_utility.data_block_address_file(&Block) from dual;
Enter value for block: 8457504
old   1: select dbms_utility.data_block_address_file(&Block) from dual
new   1: select dbms_utility.data_block_address_file(8457504) from dual

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(8457504)
---------------------------------------------
    2

獲取塊號(block_id)的方法：
SQL>  select dbms_utility.data_block_address_block(&Block) from dual;
Enter value for block: 8457504
old   1: select dbms_utility.data_block_address_block(&Block) from dual
new   1: select dbms_utility.data_block_address_block(8457504) from dual

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(8457504)
----------------------------------------------
 68896

通過以上兩個命令可以看到壞塊所在的位置為檔案號(file_id)為：2 ，塊號(block_id)為：68896。
用同樣的方法找出其它標記壞塊的檔案號和塊號:
檔案號均為2；塊號分別是：68896 68957 68961 70930 70934 70938 70943 70946 70976 70980 70984 71007 71011 71015 71019 71023 71024 71027 71028 71031 71035 71039 。
3、尋求恢復
需要檢查損壞的物件，使用以下SQL，對於不同的情況需要區別對待：
SQL> select tablespace_name,segment_type,owner,segment_name from dba_extents
 where file_id=&file_id and &block_id between block_id and block_id+blocks-1;
old   2:  where file_id=&file_id and &block_id between block_id and block_id+blocks-1
new   2:  where file_id=2 and 68896 between block_id and block_id+blocks-1

TABLESPACE_NAME    SEGMENT_TYPE      OWNER SEGMENT_NAME
------------------ ----------------- ---------- ----------------------------
SYSAUX    LOBSEGMENT      SYS SYS_LOB0000006207C00004$$
發現是LOBSEGMENT，看看剩下的壞塊，發現都是LOBSEGMENT，集中在以下2個SEGMENT上：
TABLESPACE_NAME    SEGMENT_TYPE      OWNER SEGMENT_NAME
------------------ ----------------- ---------- ----------------------------
SYSAUX    LOBSEGMENT      SYS SYS_LOB0000006207C00004$$
SYSAUX    LOBSEGMENT      SYS SYS_LOB0000006213C00038$$

找到表名和LOB欄位，主要是以下2個：
SQL> select table_name, column_name from dba_lobs where segment_name = '&SEGMENT_NAME' and owner = 'SYS';

TABLE_NAME COLUMN_NAME
----------------- -------------
WRH$_SQLTEXT SQL_TEXT

TABLE_NAME COLUMN_NAME
----------------- -------------
WRH$_SQL_PLAN OTHER_XML

找到壞塊的bad rowid,使用以下plsql指令碼
SQL> create table bad_rows (row_id ROWID,oracle_error_code number);

SQL> set concat off
SQL> set serveroutput on


SQL> declare
n number;
error_code number;
bad_rows number := 0;
ora1578 EXCEPTION;
PRAGMA EXCEPTION_INIT(ora1578, -1578);
begin
for cursor_lob in (select rowid rid, &&lob_column from &&table_owner.&table_with_lob) loop
begin
n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
exception
when ora1578 then
bad_rows := bad_rows + 1;
insert into bad_rows values(cursor_lob.rid,1578);
commit;
when others then
error_code:=SQLCODE;
bad_rows := bad_rows + 1;
insert into bad_rows values(cursor_lob.rid,error_code);
commit; 
end;
end loop;
dbms_output.put_line('Total Rows identified with errors in LOB column: '||bad_rows);
end;
/

Enter value for lob_column: SQL_TEXT
Enter value for table_owner: SYS
Enter value for table_with_lob: WRH$_SQLTEXT
可以查詢bad rowid
SQL> select * from bad_rows;
我這裡查詢是no rows selected，有很多可能，不過WRH$是AWR使用的，對資料庫影響不大，因為沒有bad rowid，所以我這裡直接新建了備份表:
SQL> create table WRH$_SQLTEXT_bak as select * from WRH$_SQLTEXT;
SQL> create table WRH$_SQL_PLAN_bak as select * from WRH$_SQL_PLAN;
注意下，做這些操作前要先備份，因為sysaux有壞塊，所以exp table是不行的，但可以exp TABLESPACE，在exp過程中可能會報： EXP-00056: ORA-25153: Temporary Tablespace is Empty之類的錯誤，就是本文開頭的報錯，估計是sysaux壞塊導致的temp表空間資料檔案為空的問題，重新給temp新增資料檔案：ALTER TABLESPACE "TEMP" ADD TEMPFILE '/opt/app/oracle/oradata/data/temp01.dbf' SIZE 100M REUSE;即可。
建立完備份表，我們給原表改名：
SQL> alter table WRH$_SQLTEXT rename to del_WRH$_SQLTEXT;
SQL> alter table WRH$_SQL_PLAN rename to del_WRH$_SQL_PLAN;
給備份表改名：
SQL> alter table WRH$_SQLTEXT_bak rename to WRH$_SQLTEXT;
SQL> alter table WRH$_SQL_PLAN_bak rename to WRH$_SQL_PLAN;
至此，sysaux表空間可以使用了。
可以用exp table 來測試一下。
為了穩妥起見，也可以重新建立一個AWR（自動負載資訊庫）表：
SQL> sqlplus /nolog 
SQL> connect / as sysdba 
SQL> @?/rdbms/admin/catnoawr 
SQL> alter system flush shared_pool; 
SQL> @?/rdbms/admin/catawr.sql
SQL> @?/rdbms/admin/utlrp 
另外，標記壞塊需要格式化才能去掉，這裡就不多說了。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14184018/viewspace-1799950/，如需轉載，請註明出處，否則將追究法律責任。

相關文章