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

One day, a bad block appeared in the AWR-related table of tablespace SYSAUX in the customer's database. Through the truncate table (the data is not important), restore the data files from the backup, and repair the bad blocks after recovery.

After the database OPEN, an error occurred in the customer business. Check the alarm log and there is:

Errors in file / u01/app/oracle/diag/rdbms/test/test/trace/test_ora_51465.trc (incident=279339):

ORA-00600: internal error code. Parameter: [kdsgrp1], []

Incident details in: / u01/app/oracle/diag/rdbms/test/test/incident/incdir_279339/test_ora_51465_i279339.trc

As you can see from trace, some SQL caused this error. This error mainly refers to the corresponding index ROWID, and no record is found in the data table, which indicates that there is a data consistency problem. Get the SQL that caused the error from the trace file and execute it as follows:

Continuing to look at the trace file, you can navigate to the following record

* * 2019-03-30 2200 Fringe 06.323

* SESSION ID: (1802.759) 2019-03-30 2200purl 06.323

* CLIENT ID: () 2019-03-30 22 purl 06.323

* SERVICE NAME: (ysnc) 2019-03-30 2200purl 06.323

* MODULE NAME: (sqlservr.exe) 2019-03-30 2200purl 06.323

* ACTION NAME: () 2019-03-30 22 purl 06.323

* kdsgrp1-1: *

Row 0x030b33a7.0 continuation at

0x030b33a7.0 file# 12 block# 734119 slot 0 not found

KDSTABN_GET: 0. Ntab: 0

CurSlot: 0. Nrows: 0

Kdsgrp-dump CR block dba=0x030b33a7

Block header dump: 0x030b33a7

Object id on Block? Y

Seg/obj: 0x29761 csc: 0x00.53475f8c itc: 2 flg: e typ: 1-DATA

Brn: 0 bdba: 0x30b3300 ver: 0x01 opc: 0

Inc: 0 exflg: 0

From this, you get the data file number 12 and the block number, 734119. You can use SQL to locate the object that went wrong.

Select owner,segment_name,segment_type from dba_extents where file_id= 12 and block_id= 734119

After the object is queried, an attempt is made to rebuild the index of the object.

Error ORA-00600 13004 occurred while rebuilding.

It has to be indexed by drop and then created by create.

After the index reconstruction is complete, the SQL executes again without error.

In addition, ORA-08103 Object no longer exists later occurred in this customer's database.

An error has occurred when querying the table, and data loss is inevitable here.

The following script rescue data is found on MOS:

REM Create a new table based on the table that is producing errors with no rows:

Create table. (table name > _ 20180331

As

Select *

From. (table name >

Where 1: 2

REM Create the table to keep track of ROWIDs pointing to affected rows:

Create table. Bad _ rows (row_id rowid, oracle_error_code number)

Set serveroutput on

DECLARE

TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER

CURSOR C1 IS select / * + index_ffs (tab1) parallel (tab1) * / rowid

From. (table name > tab1

Where pk_flow is NOT NULL

Order by rowid

R RowIDTab

Rows NATURAL: = 20000

Bad_rows number: = 0

Errors number

Error_code number

Myrowid rowid

BEGIN

OPEN c1

LOOP

FETCH c1 BULK COLLECT INTO r LIMIT rows

EXIT WHEN r.count=0

BEGIN

FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS

Insert into. (table name > _ 20180331

Select / * + ROWID (A) * / a.*

From. (table name > A where rowid = r (I)

EXCEPTION

When OTHERS then

BEGIN

Errors: = SQL%BULK_EXCEPTIONS.COUNT

FOR err1 IN 1..errors LOOP

Error_code: = SQL%BULK_EXCEPTIONS (err1) .ERROR_CODE

If error_code in (1410, 8103, 1578) then

Myrowid: = r (SQL%BULK_EXCEPTIONS (err1) .ERROR_INDEX)

Bad_rows: = bad_rows + 1

Insert into. Bad _ rows values (myrowid, error_code)

Else

Raise

End if

END LOOP

END

END

Commit

END LOOP

Commit

CLOSE c1

Dbms_output.put_line ('Total Bad Rows:' | | bad_rows)

END

Fortunately, only six of the more than 400,000 pieces of data were lost and two documents were affected. The business recovers the data by making up the documents.

Reference: http://www.eygle.com/archives/2011/07/ora-600_kdsgrp1.html

Document ID 1527738.1

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.