正常巡检一个库时,发现定时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 OWNERSEGMENT_NAME
------------------ ----------------- ---------- ----------------------------
SYSAUX LOBSEGMENT SYSSYS_LOB0000006207C00004$$
发现是LOBSEGMENT,看看剩下的坏块,发现都是LOBSEGMENT,集中在以下2个SEGMENT上:
TABLESPACE_NAME SEGMENT_TYPE OWNERSEGMENT_NAME
------------------ ----------------- ---------- ----------------------------
SYSAUX LOBSEGMENT SYSSYS_LOB0000006207C00004$$
SYSAUX LOBSEGMENT SYSSYS_LOB0000006213C00038$$
找到表名和LOB字段,主要是以下2个:
SQL> select table_name, column_name from dba_lobs where segment_name = '&SEGMENT_NAME' and owner = 'SYS';
TABLE_NAMECOLUMN_NAME
------------------------------
WRH$_SQLTEXTSQL_TEXT
TABLE_NAMECOLUMN_NAME
------------------------------
WRH$_SQL_PLANOTHER_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
另外,标记坏块需要格式化才能去掉,这里就不多说了。