Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the three random special recovery methods in the database". In the daily operation, it is believed that many people have doubts about what are the three random special recovery methods in the database. The editor consulted all kinds of data and sorted out simple and useful operation methods. I hope it will be helpful for you to answer the question of "what are the three random special recovery methods in the database?" Next, please follow the editor to study!

Recovery process

First of all, get the character set of the system file, database name, and then create a parameter file, rebuild the control file, here is not too much to say, first try to start the database.

The first Ray.

SQL > startup nomount pfile='/gauss/init.ora'

ORACLE instance started.

Total System Global Area 396668928 bytes

Fixed Size 2253624 bytes

Variable Size 125832392 bytes

Database Buffers 264241152 bytes

Redo Buffers 4341760 bytes

SQL > @ cf

Control file created.

ORA-00279: change 4936537 generated at 04/21/2020 00:03:57 needed for thread 1

ORA-00289: suggestion:

/ guass/app/oracle/product/11.2.0/db_1/dbs/arch2_41_1033397865.dbf

ORA-00280: change 4936537 for thread 1 is in sequence # 41

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

Media recovery cancelled.

SQL > alter database open resetlogs

Alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01578: ORACLE data block corrupted (file # 1, block # 338)-- Bad Block

ORA-01110: data file 1:'/ gauss/system.dbf'

Process ID: 32245

Session ID: 1 Serial number: 3

Data startup report file 1, block 338 has a bad block error, let's check the corresponding object of this block.

TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME

--

SYSTEM INDEX SYS I_OBJ1

What is this object Ireaders OBJ1?

SQL > select * from bootstrap$ where SQL_TEXT like'% IPCC OBJ1% 'order by LINE#

LINE# OBJ# SQL_TEXT

-

36 36 CREATE UNIQUE INDEX I_OBJ1 ON OBJ$ (OBJ#,OWNER#,TYPE#) PCTFREE 10 INITRANS 2 MAXT

RANS 255STORAGE (INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PC

TINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 336)

I_OBJ1 is an index of the core base table OBJ$. When a bad block occurs in an ordinary index, we can deal with it by rebuilding, but OBJ# copy file 6 block 338 to file 1 block 338

However, if there are a large number of bad blocks in I_OBJ1, the efficiency of cp is relatively low.

Method two

Delete I_OBJ1, but instead of deleting the I_OBJ1 index in IND$, we create it when we restart it again. Here we need to delete the index in sys.bootstrap.

BBED > x / rnnc * kdbr [1]

Rowdata [3681] @ 7322

-

Flag@7322: 0x2c (KDRHFL, KDRHFF, KDRHFH)

Lock@7323: 0x01

Cols@7324: 3

Col 0 [2] @ 7325: 36

Col 1 [2] @ 7328: 36

Col 2 [208] @ 7331: CREATE UNIQUE INDEX I_OBJ1 ON OBJ$ (OBJ#,OWNER#,TYPE#) P

CTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (INITIAL 64K NEXT 1024K MINEXTE

NTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 33

6))

BBED > assign / x offset 7322 = 0x3c-- Delete index

Ub1 rowdata [0] @ 7322 0x3c

BBED > x / rnnc dba 1523 * kdbr [1]

Rowdata [3681] @ 7322

-

Flag@7322: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)

Lock@7323: 0x01

Cols@7324: 0-deleted

BBED > sum apply

Check value for File 1, Block 523:

Current = 0x7e06, required = 0x7e06

Method three

[ora11@zdata bin] $strings $ORACLE_HOME/bin/oracle | wc-l1341571

The execution file of 11.2.0.4 contains 1341571 functions, which can be handled by modifying the oracle execution file specification to bypass the index I_OBJ1.

The second Ray.

Through the above two ways to line up the first mine, we successfully reached the second level. Hurry up and try to open it and see what will go wrong.

SQL > oradebug event 10046 trace name context forever,level 12

Statement processed.

SQL > alter database open resetlogs

Alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number 27 with name

"$" too small

Process ID: 17397

Session ID: 1 Serial number: 5

ORA-01555 snapshot reported the old error, this fault is familiar and unfamiliar, try to recommend the promotion of low-level scn, found no effect, it seems that the mine is still very deep.

When oracle reads a block, there are many situations in which undo is required to complete the consistent read:

1. Itl has an active transaction. If you look back at the undo segment header and find that the transaction is indeed active, then you must need undo to do consistent reading.

2. Itl has an active transaction. If you check the undo segment header and find that the transaction has been committed or rolled back, you need to do block cleanup to determine the commit scn. If query scn assign ktbbhitl [0] .ktbitbas = 0x004b531fub4 ktbitbas @ 64 0x004b531f

Method two

Since the low position is the biggest, no matter how much it can be pushed, it will not surpass the 0xffffffff, so let's try poke to promote the high scn.

SQL > oradebug setmypid

SQL > oradebug dumpvar sga kcsgscn_

Kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 000000000000000000000000 6001AB50 00000000

SQL > oradebug poke 0x06001AE74 4 0x50

BEFORE: [06001AE70, 06001AE78) = 00000000 00000000

AFTER: [06001AE70, 06001AE78) = 004B60E0 00000000

The third Ray.

SQL > alter database open resetlogs

Alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01173: data dictionary indicates missing data file from system tablespace

Process ID: 26639

Session ID: 1 Serial number: 3

Seeing this error report shows that we have successfully entered the third level.

Method one

Set the hidden trouble parameters of *. _ corrupted_rollback_segments by one-click script to shield the rollback segment.

*. _ corrupted_rollback_segments='_SYSSMU28_79026890 $','_ SYSSMU24_100127047 $','_ SYSSMU28_79026890 $','_ SYSSMU21_1449495591 $','_ SYSSMU24_100127047 $','_ SYSSMU21_1449495591 $','_ SYSSMU30_493042799 $','_ SYSSMU30_493042799 $','_ SYSSMU23_1725104698 $','_ SYSSMU23_1725104698 $','_ SYSSMU22_3628056578 $','_ SYSSMU22_3628056578 $','_ SYSSMU25_3360715651 $','_ SYSSMU22_3628056578 $','_ SYSSMU25_3360715651 $','_ SYSSMU22_36280565.

Method two

SQL > select OWNER,SEGMENT_NAME,FILE_ID,BLOCK_ID from dba_extents where segment_name='UNDO$'

OWNER SEGMENT_NAME FILE_ID BLOCK_ID

--

SYS UNDO$ 1 224

BBED > p ktetb

Struct ktetb [0], 8 bytes @ 108

First address of ub4 ktetbdba @ 108 0x004000e1--extent

Ub4 ktetbnbk @ 112 0x00000007 muri-7 consecutive blocks

BBED > x / rnc * kdbr [0

Rowdata [2337] @ 8146

-

Flag@8146: 0x2c (KDRHFL, KDRHFF, KDRHFH)

Lock@8147: 0x00

Cols@8148: 17

Col 0 [1] @ 8149: 0

Col 1 [6] @ 8151: SYSTEM

Col 2 [1] @ 8158:.

Col 3 [2] @ 8160:.

. Omit

BBED > x / rnc * kdbr [1

Rowdata [2267] @ 8076

-

Flag@8076: 0x2c (KDRHFL, KDRHFF, KDRHFH)

Lock@8077: 0x00

Cols@8078: 17

Col 0 [2] @ 8079: 1

Col 1 [19] @ 8082: _ SYSSMU1_770609302 $--needs to be deleted

Col 2 [2] @ 8102:.

Col 3 [2] @ 8105:.

Col 4 [3] @ 8108:...

. Omit

When locating a row of data, the Oracle database service process adopts the "centralized-decentralized" storage mode of data dictionary + segment header extent map, specifically:

1. The physical location of the data segment (corresponding table or partition) header in the data dictionary

2. The physical location of all extent (that is, consecutive block collections) in the segment header is stored.

Ktetb is actually the extent map of UNDO$, and this structure records the first address of the extent in segment.

Through an one-click script to find dba 0x004000e1, that is, 1Magee 225, and then 7 blocks in a row, delete all the other rollback segments except system, this method is too troublesome to operate, you can challenge if you are interested. The fourth thunder waded through the first three thunder, congratulations on successfully reaching the fourth thunder. This thunderbolt is not good, after all, it is the ultimate BOSS. After dealing with this, we will go through customs smoothly, and victory is just around the corner.

SQL > alter database open resetlogs

Alter database open resetlogs

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 26280

Session ID: 1 Serial number: 3

This error report, the database directly hung up, and there is no error, set a 10046 event to track down.

SQL > oradebug event 10046 trace name context forever,level 12

SQL > alter database open resetlogs

Alter database open resetlogs

-- error report of background alert

SMON: enabling cache recovery

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xD0C5CD7] [PC:0x97DF62E, kgebse () + 776] [flags: 0x2, count: 2]

Fri Apr 24 06:42:13 2020

PMON (ospid: 19501): terminating the instance due to error

-- file#=1 block#=140 needs attention

WAIT # 140452738872120: nam='db file sequential read' ela= 670 file#=1 block#=140 blocks=1 obj#=0 tim=1587681730297305

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xD0C5CD7] [PC:0x97E0BBA, kgegpa () + 40] [flags: 0x0, count: 1]

DDE previous invocation failed before phase II

DDE was called in a'No Invocation Mode'

-Start Diag Diagnostic Dump-

Diag diagnostic dump is performed due to an error in the diagfw code during error handling.

DDE is switched to protected mode during the diagnostic dump to prevent recursive errors in the error hadnling code.

In fact, you can see from SMON: enabling cache recovery that the database is doing some rollback operations. File 1 block 140corresponds to the system rollback segment. When the database is accessed to 140 chunks, the database goes down directly.

TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME--SYSTEM ROLLBACK SYS SYSTEM

I'm here to find out what's wrong with file 1 block 140.

BBED > p dba 1140 ktubh

Struct ktubh, 22 bytes @ 20

Struct ktubhxid, 8 bytes @ 20

Ub2 kxidusn @ 20 0x0000

Ub2 kxidslt @ 22 0x003b

Ub4 kxidsqn @ 24 0x0000002b

Ub2 ktubhseq @ 28 0x0025-- seq is 0x0025

Ub1 ktubhcnt @ 30 0x03

Ub1 ktubhirb @ 31 0x03

Ub1 ktubhicl @ 32 0x00

Ub1 ktubhflg @ 33 0x00

BBED > p dba 1128 ktuxc

Struct ktuxc, 104 bytes @ 4148

Struct ktuxcscn, 8 bytes @ 4148

Ub4 kscnbas @ 4148 0x004996f7

Ub2 kscnwrp @ 4152 0x0000

. Omit

Struct ktuxcfbp [0], 12 bytes @ 4192

Struct ktufbuba, 8 bytes @ 4192

Ub4 kubadba @ 4192 0x0040008c

Ub2 kubaseq @ 4196 0x0030-- seq is 0x0030

Ub1 kubarec @ 4198 0x03

Sb2 ktufbext @ 4200 1

Sb2 ktufbspc @ 4202 7340

Here it feels like the offset 4196 of 128RMB has been changed from 0x0025 to 0x0030. There are three ways to restore it.

Change the 128RMB offset 4196 from 0x0030 to 0x0025

BBED > assign dba 1128 4196=0x0025

Ub2 kubaseq @ 4196 0x0025

Method two

Ktuxcnfb and ktuxcfbp [1] need to be modified. Change ktuxcnfb to 0 uba in [1] ktuxcfbp to 0, which means that when rollback, oracle thinks that there is no undo block that can be allocated, which is equivalent to no rollback.

BBED > modify / x 00 offset 4168

BBED > modify / x 000000 offset 4192

Method three

Through the strace trace, you get the following trace.

01:13:27 write (14, "[32]: ktuiup []", 15) = 15-- ktuiup

01:13:27 write (15, "! gF\ n", 4) = 4

01:13:27 lseek (14,0, SEEK_CUR) = 3425947

01:13:27 write (14, "\ n", 1) = 1

01:13:27 write (15, "! A1\ n", 4) = 4

01:13:27 lseek (14,0, SEEK_CUR) = 3425948

01:13:27 write (14, "[33]: ktuini []", 15) = 15-notice the ktuini function here

01:13:27 write (15, "! gF\ n", 4) = 4

01:13:27 lseek (14,0, SEEK_CUR) = 3425963

01:13:27 write (14, "\ n", 1) = 1

01:13:27 write (15, "! A1\ n", 4) = 4

01:13:27 lseek (14,0, SEEK_CUR) = 3425964

01:13:27 write (14, "[34]: adbdrv []", 15) = 15

01:13:27 write (15, "! gF\ n", 4) = 4

01:13:27 lseek (14,0, SEEK_CUR) = 3425979

01:13:27 write (14, "\ n", 1) = 1

01:13:27 write (15, "! A1\ n", 4) = 4

01:13:27 lseek (14,0, SEEK_CUR) = 3425980

01:13:27 write (14, "[35]: opiexe []", 15) = 15

01:13:27 write (15, "! gF\ n", 4) = 4

01:13:27 lseek (14,0, SEEK_CUR) = 3425995

01:13:27 write (14, "\ n", 1) = 1

Note the ktuini function here, and SEEK_CUR represents the relative current location of the file. Here, you can also see that the database does some more recovery actions, calls the ktuiup function, and starts to recover the dead transactions on the rollback segment.

Since the database calls the function ktuini to report an error, we set a breakpoint by using this function, and then export the data.

-- session 1

(gdb) break ktuini

Breakpoint 1 at 0xf21352

(gdb) c

Continuing.

-- session 2

Export data

Export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Exp\'/ as sysdba\ 'file=/home/ora11/meta.dmp ROWS=n buffer=102400000

At this point, the study of "what are the three random special recovery methods in the database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.