Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Oracle standby's ORA-01578 ORA-01110 ORA-26040, bullshit NOLOGGING.

2025-04-05 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Exception:

DB: Oracle 11.2.0.1-the version is low enough

After switching between switch over master and slave for user DB during May Day holiday, the error reported in the new master library DB after the switch is found as follows:

Wed May 08 09:44:14 2019

Errors in file / u01/product/diag/rdbms/new/orcl/trace/orcl_ora_100843.trc (incident=50865):

ORA-01578: ORACLE database editing (file editing no. 126, block editing no. 4969)

ORA-01110: data file 126:'/ data/orcl/smt_idx01.dbf'

ORA-26040: the NOLOGGING option has been used to import the folder

Incident details in: / u01/product/diag/rdbms/new/orcl/incident/incdir_50865/orcl_ora_100843_i50865.trc

= Dump for incident 50865 (ORA 1578) =

* * 2019-05-08 09 Fraser 4414. 254

DbkedDefDump (): Starting incident default dumps (flags=0x2, level=3, mask=0x0)

-Current SQL Statement for this session (sql_id=52s3v0xvc21j8)-

SELECT

ROWID, STATION_NUMBER, MACHINE_CODE, PRODUCT_NO

VER, EMP_NO, FEEDER_NO

KEY_PART_NO, WORK_TIME, SN

LINE_NAME, MO_NO, SIDE

LOT_NO, VENDOR, DATE_CODE

FEEDER_ID, KEY_PART_QTY, HH_PN

PACKED_QTY, MFG_PN, PKG_ID

CPL_ID, END_TIME, BOM_NO

CUST_PN, DIFFERENCE_QTY, USED_QTY

FROM SFISM4.R_SMT_LOG

Where

PKG_ID = 'VCI3011808R05ZI'

Analysis:

ORA-01578, ORA-01110 's first reaction is that there are bad blocks of data.

Use DBV to check for bad blocks

Dbv file=/data/orcl/smt_idx01.dbf BLOCKSIZE=16384 DBVERIFY: Release 11.2.0.1.0-Production on Wed May 8 16:15:12 2019 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. DBVERIFY-Verification starting: FILE = / data/orcl/smt_idx01.dbf DBV-00201: Block, DBA 528482373, marked corrupt for invalid redo application DBV-00201: Block, DBA 528482374, marked corrupt for invalid redo application DBV-00201: Block, DBA 528482375, marked corrupt for invalid redo application

....

DBVERIFY-Verification complete

Total Pages Examined: 294400

Total Pages Processed (Data): 0

Total Pages Failing (Data): 0

Total Pages Processed (Index): 259171

Total Pages Failing (Index): 0

Total Pages Processed (Other): 19965

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 15264

Total Pages Marked Corrupt: 3

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 2390574971 (2791.2390574971)

DBV-00201 means that part of the redo from the main library to the standby library is not applied to datafile.

Check the main library before the switch (the standby library now). Sure enough, datafile'/ data/orcl/smt_idx01.dbf' is not applied.

SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE

Where UNRECOVERABLE_CHANGE# > 0

This kind of problem is usually due to the failure of redo to apply to the standby database due to some nologging operations in the main library.

This problem is basically confirmed in combination with the previous alert.log error "ORA-26040: imported into the data section using the NOLOGGING option".

Doesn't data guard turn on to force logging mode and cause similar append operations to fail to synchronize?

Select force_logging from v$database

It is useless to query force_logging for NO to enable force logging....

Resolve:

Check that the NOLOGGING affects the segment corresponding to the synchronization datafile:

Select * from dba_extents

Where file_id=126 and 4969 between block_id AND block_id + blocks-1

Fortunately, all segment can be solved by using index,rebuild index.

Note: if it is table or other files, you need to restore the datafile backup of the original main library (existing library) to the existing main library (original library).

Finally, when it's a clich é about setting up standby, be sure to turn on mandatory archiving to avoid problems:

Alter database force logging

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report