oracle修复sysaux表空间,记一次sysaux表空间坏块修复

最新推荐文章于 2022-12-14 21:42:03 发布

孙秀龙

最新推荐文章于 2022-12-14 21:42:03 发布

阅读量1k

点赞数

文章标签： oracle修复sysaux表空间

正常巡检一个库时，发现定时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

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)

---------------------------------------------

获取块号(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

另外，标记坏块需要格式化才能去掉，这里就不多说了。

孙秀龙

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle修复sysaux表空间,记一次sysaux表空间坏块修复

正常巡检一个库时，发现定时exp有问题：EXP-00008: ORACLE error 25153 encounteredORA-25153: Temporary Tablespace is EmptyORA-06512: at "WMSYS.LT_EXPORT_PKG", line 1379ORA-06512: at line 1EXP-00083: The previous problem o...
复制链接

扫一扫