分类目录归档：Oracle

Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium – ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 [krr_process_read_error_2]

发表于 2025 年 1 月 21 日由惜分飞

Applies to:
Oracle Database – Enterprise Edition – Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Oracle Database Cloud Schema Service – Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) – Version N/A and later
Oracle Cloud Infrastructure – Database Service – Version N/A and later
Oracle Database Cloud Exadata Service – Version N/A and later
IBM AIX on POWER Systems (64-bit)

Description
Oracle 12c introduces a new default feature of using multiple LGWRs which may lead to DEADLOCK / Database Hang or ORA-742 “Log read detects lost write” or ORA-600 [kcrfrgv_nextlwn_scn] during instance OPEN or ORA-600 [krr_process_read_error_2] during Recovery on IBM AIX and potentially on HPUX Itanium 64bit.
The database may become unusable and fail to be OPEN.

Occurrence
This issue is specific to RDBMS version 12c (12.1.0.1 or 12.1.0.2) where the new default feature of using multiple LGWRs is introduced.
It affects databases on IBM AIX and potentially on HPUX Itanium 64bit

Symptoms
ORACLE on IBM AIX or HPUX Itanium 64bit with RDBMS Version 12c.

DEADLOCK or ORA-742 “Log read detects lost write” or ORA-600 [kcrfrgv_nextlwn_scn] during instance OPEN or ORA-600 [krr_process_read_error_2] during Recovery caused by bug 21915719.

PMON may terminate the instance while extensive block recovery is being performed.

A DEADLOCK example is with LG0[n] waiting on ‘LGWR worker group ordering’. Example from a System State Dump trace file:

PROCESS 18: LG01
SO: 0x7000101f95ad720, type: 4, owner: 0x7000101f84195f8, flag: INIT/-/-/0x00
if: 0x3 c: 0x3
   proc=0x7000101f84195f8, name=session, file=ksu.h LINE:13590 ID:, pg=0
conuid=0
  (session) sid: 865 ser: 1 trans: 0x0, creator: 0x7000101f84195f
 Current Wait Stack:
   0: waiting for 'LGWR worker group ordering'
      lwn_id=0x58, phase=0x1, =0x0
      wait_id=4947 seq_num=4948 snap_id=1
      wait times: snap=13 min 21 sec, exc=13 min 21 sec, total=13 min 21 sec
      wait times: max=infinite, heur=13 min 21 sec
      wait counts: calls=1 os=267
      in_wait=1 iflags=0x5a0
  There is at least one session blocking this session.
    Dumping 1 direct blocker(s):
      inst: 1, sid: 817, ser: 1
    Dumping final blocker:
      inst: 1, sid: 817, ser: 1
  There are 730 sessions blocked by this session.
.
.
PROCESS 17: LG00
SO: 0x7000101f85bcc60, type: 4, owner: 0x7000101f93eeb20, flag: INIT/-/-/0x00
if: 0x3 c: 0x3
   proc=0x7000101f93eeb20, name=session, file=ksu.h LINE:13590 ID:, pg=0
conuid=0
  (session) sid: 817 ser: 1 trans: 0x0, creator: 0x7000101f93eeb20
  ksuxds FALSE at location: 0
  service name: SYS$BACKGROUND
  Current Wait Stack:
   0: waiting for 'LGWR worker group ordering'
      lwn_id=0x56, phase=0x1, =0x0
      wait_id=1630680 seq_num=57841 snap_id=1
      wait times: snap=13 min 21 sec, exc=13 min 21 sec, total=13 min 21 sec
      wait times: max=infinite, heur=13 min 21 sec
      wait counts: calls=2 os=268
      in_wait=1 iflags=0x15a0
  There is at least one session blocking this session.
    Dumping 1 direct blocker(s):
      inst: 1, sid: 865, ser: 1
    Dumping final blocker:
      inst: 1, sid: 865, ser: 1

The instance may fail to OPEN with errors ORA-600 [kcrfrgv_nextlwn_scn] and/or ORA-600 [krr_process_read_error_2]:

Recovery Session Failed with:

ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [krr_process_read_error_2],


Alter database open fails with:

ORA-00600: internal error code, arguments: [kcrfrgv_nextlwn_scn] .....
ORA-600 signalled during: ALTER DATABASE OPEN...

Workaround
Disable the new feature of multiple LGWR worker processes by proactively setting _use_single_log_writer=true.

Setting _use_single_log_writer = true is a safe workaround; it is the behavior before 12c where multiple LGWR worker groups were not available.

ALTER SYSTEM SET "_use_single_log_writer"=TRUE SID='*' SCOPE=SPFILE;
-- Restart the database or all instances of the RAC database

Note that while _use_single_log_writer=true is not set, then error ORA-600 [kcrfrgv_nextlwn_scn] might be produced avoiding the database to OPEN. Once the problem is introduced, _use_single_log_writer=true may not fix it. _use_single_log_writer = true prevents inconsistencies in the redo log to be introduced which causes that error.
If the parameter does not help, because the problem was already introduced when _use_single_log_writer=true had not been proactively set, then Point in Time Recovery (PITR) or Flashback Database are the options to recover from this situation.
参考:ALERT: Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium – ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 [krr_process_read_error_2] (Doc ID 1957710.1)

发表在 Oracle | 标签为 kcrfrgv_nextlwn_scn, krr_process_read_error_2, _use_single_log_writer | 留下评论

ORA-600 ktuPopDictI_1恢复

发表于 2025 年 1 月 20 日由惜分飞

数据库启动报ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4]错误

[oracle@ora19c:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 20 21:42:28 2025
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to an idle instance.

sys@ORA19C 21:38:22> startup
ORACLE instance started.

Total System Global Area  763359928 bytes
Fixed Size                  9183928 bytes
Variable Size             457179136 bytes
Database Buffers          289406976 bytes
Redo Buffers                7589888 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
Process ID: 3254475
Session ID: 410 Serial number: 22754

数据库alert日志报错

2025-01-20T21:38:30.411924+08:00
ALTER DATABASE OPEN
2025-01-20T21:38:30.437769+08:00
Smart fusion block transfer is disabled:
  instance mounted in exclusive mode.
2025-01-20T21:38:30.445071+08:00
Crash Recovery excluding pdb 2 which was cleanly closed.
2025-01-20T21:38:30.445125+08:00
Crash Recovery excluding pdb 3 which was cleanly closed.
2025-01-20T21:38:30.445172+08:00
Crash Recovery excluding pdb 4 which was cleanly closed.
Endian type of dictionary set to little
2025-01-20T21:38:30.459107+08:00
LGWR (PID:3254425): STARTING ARCH PROCESSES
2025-01-20T21:38:30.466458+08:00
TT00 (PID:3254477): Gap Manager starting
Starting background process ARC0
2025-01-20T21:38:30.474126+08:00
ARC0 started with pid=39, OS id=3254479 
2025-01-20T21:38:30.484228+08:00
LGWR (PID:3254425): ARC0: Archival started
LGWR (PID:3254425): STARTING ARCH PROCESSES COMPLETE
2025-01-20T21:38:30.484325+08:00
ARC0 (PID:3254479): Becoming a 'no FAL' ARCH
ARC0 (PID:3254479): Becoming the 'no SRL' ARCH
2025-01-20T21:38:30.495886+08:00
Redo log for group 3, sequence 447 is not located on DAX storage
Thread 1 opened at log sequence 447
  Current log# 3 seq# 447 mem# 0: /data/oradata/ORA19C/redo03.log
Successful open of redo thread 1
2025-01-20T21:38:30.512816+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
Undo initialization recovery: Parallel FPTR complete: start:2947972792 end:2947972831 diff:39 ms (0.0 seconds)
Undo initialization recovery: err:0 start: 2947972791 end: 2947972831 diff: 40 ms (0.0 seconds)
[3254475] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 2947972831 end: 2947972933 diff: 102 ms (0.1 seconds)
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
Incident details in: /data/app/oracle/diag/rdbms/ora19c/ora19c/incident/incdir_38705/ora19c_ora_3254475_i38705.trc
2025-01-20T21:38:31.482586+08:00
TMON (PID:3254467): STARTING ARCH PROCESSES
Starting background process ARC1
2025-01-20T21:38:31.491744+08:00
ARC1 started with pid=41, OS id=3254483 
Starting background process ARC2
2025-01-20T21:38:31.500274+08:00
ARC2 started with pid=42, OS id=3254485 
Starting background process ARC3
2025-01-20T21:38:31.508426+08:00
ARC3 started with pid=43, OS id=3254487 
TMON (PID:3254467): ARC1: Archival started
TMON (PID:3254467): ARC2: Archival started
TMON (PID:3254467): ARC3: Archival started
TMON (PID:3254467): STARTING ARCH PROCESSES COMPLETE
2025-01-20T21:38:31.715480+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-01-20T21:38:31.798619+08:00
Errors in file /data/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_3254475.trc:
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
2025-01-20T21:38:31.798666+08:00
Errors in file /data/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_3254475.trc:
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
Incident details in: /data/app/oracle/diag/rdbms/ora19c/ora19c/incident/incdir_38706/ora19c_ora_3254475_i38706.trc
opiodr aborting process unknown ospid (3254475) as a result of ORA-603
2025-01-20T21:38:32.356148+08:00
ORA-603 : opitsk aborting process
License high water mark = 1
USER(prelim) (ospid: 3254475): terminating the instance due to ORA error 600

分析trace信息

[TOC00001]
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []

[TOC00001-END]
[TOC00002]
========= Dump for incident 38706 (ORA 603) ========

*** 2025-01-20T21:38:31.823904+08:00
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00003]
----- Current SQL Statement for this session (sql_id=1h50ks4ncswfn) -----
ALTER DATABASE OPEN
[TOC00003-END]

[TOC00004]
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+95         call     kgdsdst()            7FFEA4EB7360 000000002
                                                   7FFEA4EB15B0 ? 7FFEA4EB16C8 ?
                                                   000000000 000000000
ksedst()+58          call     ksedst1()            000000000 000000001
                                                   7FFEA4EB15B0 ? 7FFEA4EB16C8 ?
                                                   000000000 ? 000000000 ?
dbkedDefDump()+2434  call     ksedst()             000000000 000000001 ?
7                                                  7FFEA4EB15B0 ? 7FFEA4EB16C8 ?
                                                   000000000 ? 000000000 ?
ksedmp()+577         call     dbkedDefDump()       000000003 000000002
                                                   7FFEA4EB15B0 ? 7FFEA4EB16C8 ?
                                                   000000000 ? 000000000 ?
dbgexPhaseII()+2092  call     ksedmp()             0000003EB 000000002 ?
                                                   7FFEA4EB15B0 ? 7FFEA4EB16C8 ?
                                                   000000000 ? 000000000 ?
dbgexProcessError()  call     dbgexPhaseII()       7F2A059ED6D8 7F2A002BF148
+1871                                              7FFEA4EB8E00 7FFEA4EB16C8 ?
                                                   000000000 ? 000000000 ?
dbgePostErrorKGE()+  call     dbgexProcessError()  7F2A059ED6D8 7F2A002BF148
1851                                               000000001 000000000
                                                   000000000 ? 000000000 ?
dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   7F2A05A2D9C0 7F2A058D0050
71                                                 00000025B 000000000 ?
                                                   000000000 ? 000000000 ?
kgeade()+339         call     dbkePostKGE_kgsf()   7F2A05A2D9C0 7F2A058D0050
                                                   00000025B 000000000 ?
                                                   000000000 ? 000000000 ?
kgefecl()+184        call     kgeade()             7F2A05A2D9C0 ? 7F2A05A2DC08 ?
                                                   7F2A058D0050 ? 00000025B ?
                                                   000000000 000000000
adbdrv_options()+48  call     kgefecl()            7F2A05A2D9C0 7F2A058D0050
548                                                000000444 000000001 ?
                                                   014971F9C ? 014973858 ?
opiexe()+31984       call     adbdrv_options()     000000000 7F2A058D0050 ?
                                                   000000444 ? 000000001 ?
                                                   014971F9C ? 014973858 ?
opiosq0()+4560       call     opiexe()             000000004 7F2A058D0050 ?
                                                   7FFEA4EC16D0 000000001 ?
                                                   014971F9C ? 014973858 ?
kpooprx()+287        call     opiosq0()            000000003 7F2A058D0050 ?
                                                   7F2A05A2D9C0 ? 0000000A4
                                                   000000000 000000023
kpoal8()+838         call     kpooprx()            7FFEA4EC5824 7FFEA4EC2F40
                                                   000000013 000000001 000000000
                                                   0000000A4
opiodr()+1253        call     kpoal8()             00000005E 000000026
                                                   7FFEA4EC5820 000000001 ?
                                                   000000000 ? 0000000A4 ?
ttcpip()+1216        call     opiodr()             00000005E 000000026
                                                   7FFEA4EC5820 ? 000000000
                                                   000000000 ? 0000000A4 ?
opitsk()+1916        call     ttcpip()             7F2A05A57B30 ? 000000026 ?
                                                   7FFEA4EC5820 000000000 ?
                                                   7FFEA4EC5280 7FFEA4EC5A80 ?
opiino()+936         call     opitsk()             000000000 000000000
                                                   7FFEA4EC5820 ? 000000000 ?
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
opiodr()+1253        call     opiino()             00000003C 000000004
                                                   7FFEA4EC7418 000000000 ?
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
opidrv()+1067        call     opiodr()             00000003C 000000004
                                                   7FFEA4EC7418 ? 000000000
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
sou2o()+165          call     opidrv()             00000003C 000000004
                                                   7FFEA4EC7418 000000000 ?
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
opimai_real()+422    call     sou2o()              7FFEA4EC73F0 00000003C
                                                   000000004 7FFEA4EC7418
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
ssthrdmain()+417     call     opimai_real()        000000000 7FFEA4EC7C08
                                                   000000004 ? 7FFEA4EC7418 ?
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
main()+256           call     ssthrdmain()         000000000 000000002
                                                   7FFEA4EC7C08 000000001
                                                   000000000 7FFEA4EC5A80 ?
__libc_start_main()  call     main()               000000002 7FFEA4EC7E58
+243                                               7FFEA4EC7C08 ? 000000001 ?
                                                   000000000 ? 7FFEA4EC5A80 ?
_start()+46          call     __libc_start_main()  000DFEF50 000000002
                                                   7FFEA4EC7E58 00746DD60 ?
                                                   000000000 ? 7FFEA4EC5A80 ?
[TOC00004-END]


[TOC00005]
--------------------- Binary Stack Dump ---------------------

对启动过程进行跟踪,确认报错具体位置

PARSING IN CURSOR #140457326129448 len=45 dep=1 tim=11537999627952 hv=2164165332 ad='69329ae8'sqlid='8su8qaa0gx2qn'
select dataobj# from obj$ where name like :1
END OF STMT
PARSE #140457326129448:c=15,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999627952
BINDS #140457326129448:

 Bind#0
  oacdty=01 mxl=32(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=20 fl2=0000 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7fbec507eb20  bln=32  avl=07  flg=05
  value="I_UNDO2"
EXEC #140457326129448:c=51,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999628044
FETCH #140457326129448:c=175,e=175,p=0,cr=11,cu=0,mis=0,r=1,dep=1,og=4,plh=1478545678,tim=11537999628226
STAT #140457326129448 id=1 cnt=1 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=11  card=1)'
STAT #140457326129448 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX SKIP SCAN I_OBJ2 (cr=10 time=177 us cost=26 size=0 card=1)'
CLOSE #140457326129448:c=40,e=40,dep=1,type=1,tim=11537999628290
PARSE #140457326129448:c=5,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999628302
BINDS #140457326129448:

 Bind#0
  oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
  oacflg=20 fl2=0000 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7fbec507eb20  bln=32  avl=09  flg=05
  value="UNDOHIST$"
EXEC #140457326129448:c=31,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999628342
WAIT #140457326129448: nam='db file sequential read' ela= 6 file#=1 block#=243 blocks=1 obj#=18 tim=11537999628370
FETCH #140457326129448:c=33,e=33,p=1,cr=5,cu=0,mis=0,r=1,dep=1,og=4,plh=1478545678,tim=11537999628381
CLOSE #140457326129448:c=4,e=4,dep=1,type=3,tim=11537999628409
PARSE #140457326129448:c=6,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999628430
BINDS #140457326129448:

 Bind#0
  oacdty=01 mxl=32(11) mxlc=00 mal=00 scl=00 pre=00
  oacflg=20 fl2=0000 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7fbec507eb20  bln=32  avl=11  flg=05
  value="I_UNDOHIST1"
EXEC #140457326129448:c=39,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999628485
WAIT #140457326129448: nam='db file sequential read' ela= 649 file#=1 block#=78527 blocks=1 obj#=37 tim=11537999629346
WAIT #140457326129448: nam='db file sequential read' ela= 747 file#=1 block#=78505 blocks=1 obj#=37 tim=11537999630122
WAIT #140457326129448: nam='db file sequential read' ela= 628 file#=1 block#=23459 blocks=1 obj#=37 tim=11537999630775
WAIT #140457326129448: nam='db file sequential read' ela= 470 file#=1 block#=78552 blocks=1 obj#=37 tim=11537999631268
WAIT #140457326129448: nam='db file sequential read' ela= 502 file#=1 block#=92776 blocks=1 obj#=37 tim=11537999631797
WAIT #140457326129448: nam='db file sequential read' ela= 512 file#=1 block#=78585 blocks=1 obj#=37 tim=11537999632331
WAIT #140457326129448: nam='db file sequential read' ela= 538 file#=1 block#=78557 blocks=1 obj#=37 tim=11537999632892
WAIT #140457326129448: nam='db file sequential read' ela= 520 file#=1 block#=19641 blocks=1 obj#=37 tim=11537999633440
WAIT #140457326129448: nam='db file sequential read' ela= 502 file#=1 block#=23486 blocks=1 obj#=37 tim=11537999633971
WAIT #140457326129448: nam='db file sequential read' ela= 465 file#=1 block#=23504 blocks=1 obj#=37 tim=11537999634461
WAIT #140457326129448: nam='db file sequential read' ela= 656 file#=1 block#=23488 blocks=1 obj#=37 tim=11537999635141
WAIT #140457326129448: nam='db file sequential read' ela= 429 file#=1 block#=23501 blocks=1 obj#=37 tim=11537999635596
WAIT #140457326129448: nam='db file sequential read' ela= 621 file#=1 block#=92686 blocks=1 obj#=37 tim=11537999636240
WAIT #140457326129448: nam='db file sequential read' ela= 1027 file#=1 block#=92678 blocks=1 obj#=37 tim=11537999637307
WAIT #140457326129448: nam='db file sequential read' ela= 494 file#=1 block#=92680 blocks=1 obj#=37 tim=11537999637831
WAIT #140457326129448: nam='db file sequential read' ela= 515 file#=1 block#=92682 blocks=1 obj#=37 tim=11537999638377
WAIT #140457326129448: nam='db file sequential read' ela= 559 file#=1 block#=92684 blocks=1 obj#=37 tim=11537999638975
WAIT #140457326129448: nam='db file sequential read' ela= 478 file#=1 block#=92683 blocks=1 obj#=37 tim=11537999639502
WAIT #140457326129448: nam='db file sequential read' ela= 402 file#=1 block#=92687 blocks=1 obj#=37 tim=11537999639951
WAIT #140457326129448: nam='db file sequential read' ela= 465 file#=1 block#=92691 blocks=1 obj#=37 tim=11537999640453
WAIT #140457326129448: nam='db file sequential read' ela= 629 file#=1 block#=92694 blocks=1 obj#=37 tim=11537999641112
WAIT #140457326129448: nam='db file sequential read' ela= 507 file#=1 block#=109829 blocks=1 obj#=37 tim=11537999641651
WAIT #140457326129448: nam='db file sequential read' ela= 467 file#=1 block#=109831 blocks=1 obj#=37 tim=11537999642150
WAIT #140457326129448: nam='db file sequential read' ela= 525 file#=1 block#=109833 blocks=1 obj#=37 tim=11537999642695
WAIT #140457326129448: nam='db file sequential read' ela= 823 file#=1 block#=109837 blocks=1 obj#=37 tim=11537999643540
WAIT #140457326129448: nam='db file sequential read' ela= 553 file#=1 block#=109834 blocks=1 obj#=37 tim=11537999644111
WAIT #140457326129448: nam='db file sequential read' ela= 509 file#=1 block#=109835 blocks=1 obj#=37 tim=11537999644650
FETCH #140457326129448:c=1777,e=16184,p=27,cr=38,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999644675
2025-01-20T21:40:02.951778+08:00
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []

<error barrier> at 0x7ffe9566b3c0 placed dbsdrv.c@5141
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
<error barrier> at 0x7ffe9566b3c0 placed dbsdrv.c@5141
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
2025-01-20T21:40:04.951374+08:00
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []

通过上述定位确认是select dataobj# from obj$ where name like :1这个sql在查询记录时报错,通过一些技巧绕过该sql,实现数据库正常open

发表在 Oracle备份恢复 | 标签为 ktuPopDictI_1, ORA-600 ktuPopDictI_1 | 留下评论

impdp导入数据丢失sys授权问题分析

发表于 2025 年 1 月 20 日由惜分飞

在使用expdp/impdp迁移的过程中,偶尔会遇到用户中关于sys对象的授权丢失导致不少pl/sql程序无效,通过测试重现sys授权丢失现象
创建用户并进行sys对象授权给该用户

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 12:04:22 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user xff identified by oracle;

User created.

SQL> grant resource,connect to xff;

Grant succeeded.

SQL> grant select on sys.obj$ to xff;

Grant succeeded.

SQL> grant execute on sys.dbms_lock to xff;

Grant succeeded.

SQL> grant select on sys.v_$session to xff;

Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

使用exp导出数据

C:\Users\XFF>expdp "'/ as sysdba'" dumpfile=xff.dmp schemas=xff

Export: Release 11.2.0.4.0 - Production on Sun Jan 19 12:05:35 2025

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_04":  "/******** AS SYSDBA" dumpfile=xff.dmp schemas=xff
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is:
  C:\APP\XFF\ADMIN\ORCL\DPDUMP\XFF.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Sun Jan 19 12:05:37 2025 elapsed 0 00:00:02

使用impdp导入数据

C:\Users\XFF>impdp "'/ as sysdba'" dumpfile=xff.dmp remap_schema=xff:nxff

Import: Release 11.2.0.4.0 - Production on Sun Jan 19 12:06:22 2025

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" dumpfile=xff.dmp remap_schema=xff:nxff
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sun Jan 19 12:06:23 2025 elapsed 0 00:00:00

验证用户的权限

C:\Users\XFF>sqlplus xff/oracle

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 12:09:21 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user;
USER is "XFF"
SQL> select count(1) from v$session;

  COUNT(1)
----------
        26

SQL> select count(1) from sys.obj$;

  COUNT(1)
----------
     90656

SQL> desc sys.dbms_lock
PROCEDURE ALLOCATE_UNIQUE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKNAME                       VARCHAR2                IN
 LOCKHANDLE                     VARCHAR2                OUT
 EXPIRATION_SECS                NUMBER(38)              IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
PROCEDURE SLEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SECONDS                        NUMBER                  IN

SQL> conn nxff/oracle
Connected.
SQL> desc sys.dbms_lock
ERROR:
ORA-04043: object sys.dbms_lock does not exist


SQL> select count(1) from sys.obj$;
select count(1) from sys.obj$
                         *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(1) from v$session;
select count(1) from v$session
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

确认通过impdp迁移过去的nxff用户没有之前xff用户里面sys授权的对象的访问权限.通过sqlfile查看expdp导出的dmp文件中ddl内容,确认确实没有sys部分的授权
lost-sys-grant

出现这个问题的原因是由于expdp不会导出sys中对象,所以就丢失了这部分授权信息,可以通过获取语句获取权限,然后执行补全

SQL> select 'grant ' || privilege || ' on ' ||'"'||table_name ||'"'||
  2        ' to ' || grantee || ';' "GRANTS"
  3   from dba_tab_privs
  4  where owner = 'SYS' and privilege not in ('READ', 'WRITE')
  5    and grantee in ('XFF')
  6  order by 1;

GRANTS
--------------------------------------------------------------------------------
grant EXECUTE on "DBMS_LOCK" to XFF;
grant SELECT on "OBJ$" to XFF;
grant SELECT on "V_$SESSION" to XFF;

发表在逻辑备份/恢复 | 标签为 impdp sys授权 | 留下评论

17813235971

QQ 咨询

分类目录归档：Oracle

Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium – ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 [krr_process_read_error_2]

ORA-600 ktuPopDictI_1恢复

impdp导入数据丢失sys授权问题分析

文章分类

最近发表

分类目录归档：Oracle

Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium – ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 [krr_process_read_error_2]

ORA-600 ktuPopDictI_1恢复

impdp导入数据丢失sys授权问题分析

标签云

文章分类

最近发表