ora-01578,ora-01110,ora-26040错误

今天检查alert日志发现了大量的报错,数据库为RAC环境,数据库版本为11.2.0.4.0,日志如下:

Errors in file /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/trace/NXDSS1_j003_27143.trc  (incident=589701):
ORA-01578: ORACLE data block corrupted (file # 657, block # 111610)
ORA-01110: data file 657: '+DG_DATA01/nx_dss/datafile/tbs_neweba_657'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/incident/incdir_589701/NXDSS1_j003_27143_i589701.trc
Fri May 13 22:33:38 2016
Dumping diagnostic data in directory=[cdmp_20160513223338], requested by (instance=1, osid=27143 (J003)), summary=[incident=589701
].
Fri May 13 22:33:38 2016
DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/trace/NXDSS1_j003_27143.trc:
ORA-20011: Approximate NDV failed: ORA-01578: ORACLE data block corrupted (file # 657, block # 111610)
ORA-01110: data file 657: '+DG_DATA01/nx_dss/datafile/tbs_neweba_657'
ORA-26040: Data block was loaded using the NOLOGGING option
Fri May 13 22:33:40 2016
Sweep [inc][589701]: completed
Sweep [inc2][589701]: completed
Errors in file /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/trace/NXDSS1_j003_27143.trc  (incident=589702):
ORA-01578: ORACLE data block corrupted (file # 342, block # 13094)
ORA-01110: data file 342: '+DG_DATA01/nx_dss/datafile/tbs_neweba_342'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/incident/incdir_589702/NXDSS1_j003_27143_i589702.trc
Dumping diagnostic data in directory=[cdmp_20160513223345], requested by (instance=1, osid=27143 (J003)), summary=[incident=589702
].
Errors in file /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/trace/NXDSS1_j003_27143.trc  (incident=589703):

随后使用dbv命令检查上述显示的数据库文件如下:

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     16384
SQL>

 

$jyfx1/home/oracle>dbv userid=sys/oracle file=+DG_DATA01/nx_dss/datafile/tbs_neweba_657 blocksize=16384

DBVERIFY: Release 11.2.0.4.0 - Production on Mon May 16 10:43:02 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +DG_DATA01/nx_dss/datafile/tbs_neweba_657

DBV-00201: Block, DBA -1539197958, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197622, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197621, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197620, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197606, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197605, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197604, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197602, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197601, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197600, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197586, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197585, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197584, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197582, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197581, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197580, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197574, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197573, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197572, marked corrupt for invalid redo application


DBVERIFY - Verification complete

Total Pages Examined         : 519940
Total Pages Processed (Data) : 518066
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1680
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 194
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 31
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

如上在输出中出现一部分损坏的块被标记为无效的redo 应用,且database address 地址被标记为(-) ,正常输出应该是如下另外一个数据文件校验输出如下:

$jyfx1/home/oracle>  dbv userid=sys/oracle file=+DG_DATA01/nx_dss/datafile/tbs_dm_2_1153  blocksize=16384

DBVERIFY: Release 11.2.0.4.0 - Production on Mon May 16 15:54:31 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +DG_DATA01/nx_dss/datafile/tbs_dm_2_1153

DBV-00201: Block, DBA 546059486, marked corrupt for invalid redo application

DBV-00201: Block, DBA 546059487, marked corrupt for invalid redo application

DBV-00201: Block, DBA 546059488, marked corrupt for invalid redo application

DBV-00201: Block, DBA 545470281, marked corrupt for invalid redo application

DBV-00201: Block, DBA 545470282, marked corrupt for invalid redo application

DBV-00201: Block, DBA 545470283, marked corrupt for invalid redo application

DBV-00201: Block, DBA 545470284, marked corrupt for invalid redo application


DBVERIFY - Verification complete

Total Pages Examined         : 968992
Total Pages Processed (Data) : 941489
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 6446
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 428
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 20629
Total Pages Marked Corrupt   : 7
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)
$jyfx1/home/oracle>

这个稍后会做说明

通过如下脚本把DBV检测出的corrupt的block 的database address (DBA)转换为文件号和块号,如下:

CREATE OR REPLACE PROCEDURE cdba ( iblock VARCHAR2, imode VARCHAR2 ) AS

x       NUMBER;
digits# NUMBER;
results NUMBER := 0;
file#   NUMBER := 0;
block#  NUMBER := 0;
cur_digit CHAR(1);
cur_digit# NUMBER;

BEGIN
      IF upper(imode) = 'H' THEN
           digits# := length( iblock );
           FOR x  IN 1..digits#  LOOP
                cur_digit := upper(substr( iblock, x, 1 ));
                IF cur_digit IN ('A','B','C','D','E','F')  THEN
                     cur_digit# := ascii( cur_digit ) - ascii('A') +10;
                ELSE
                     cur_digit# := to_number(cur_digit);
                END IF;
                results := (results *16) + cur_digit#;
           END LOOP;
      ELSE
           IF upper(imode) = 'D' THEN
                results := to_number(iblock);
           ELSE
                dbms_output.put_line('H = Hex Input ... D = Decimal Input');
                RETURN;
           END IF;
      END IF;

      file#  := dbms_utility.data_block_address_file(results);
      block# := dbms_utility.data_block_address_block(results);

      dbms_output.put_line('.');
      dbms_output.put_line( 'The file is ' || file# );
      dbms_output.put_line( 'The block is ' || block# );
END;
/

上述脚本可以在测试环境中建立测试环境中建立然后提取转换,如下:

SQL> connect as owner of procedure cdba(使用创建cdba的存储过程的用户进行登录)
SQL> set serveroutput on
SQL> exec cdba(546059486,’D’);

The file is 130
The block is 799966
PL/SQL procedure successfully completed.
SQL>

使用上述的file number,block number 查找对象属性,如下:

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = 130
and 799966 between block_id AND block_id + blocks – 1;

如果是表,查看表的logging 属性。

select owner, table_name, logging
from dba_tables
where table_name in
(‘<list of tables from previous query>’);

如果是索引,如下查看logging属性。

select owner, index_name, logging, table_owner, table_name
from dba_indexes
where index_name in (‘<list of indexes from previous query>’);

在nologging 属性的对象上执行恢复,会导致在dbv校验时输出DBV-201错误,在查询查询对象时,会在alert日志文件中报类似于如下的错误:

ORA-01578: ORACLE data block corrupted (file # 657, block # 111610)
ORA-01110: data file 657: ‘+DG_DATA01/nx_dss/datafile/tbs_neweba_657’
ORA-26040: Data block was loaded using the NOLOGGING option

类似于报上述错误的对象,需要对对象进程重建,并在重建的过程中更改对象logging 属性为LOGGING=YES。(如果想不在出现这种逻辑上的错误再次发生,需要进行更改,可以使用CTAS,新建表默认logging=yes),一般情况下,这种nologging 大多数是临时表,像上述中DBA -1539197604,这种情况,是块的所属对象已经被删除的结果。

Oracle 官方文档:ID 453278.1, ID 113005.1。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注