分类目录归档：数据库

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授权 | 留下评论

impdp 创建index提示ORA-00942: table or view does not exist

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

在好几次的expdp/impdp迁移数据的过程中都遇到了index创建的过程中提示表不存在的现象提示类似：
impdp-ora-00942

通过观察可以发现index和table不在同一个用户下面,猜测是由于index的用户本身没有访问表的权限,而是通过其他用户比如sys/system或者有dba权限等有访问表和对index所在用户写入数据的用户操作导致,通过试验重现了该现象
创建两个用户,一个有dba权限（用来存放表数据）【xff1】，一个是resource权限用来创建index【xff2】

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 11:36:07 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 XFF1 IDENTIFIED BY oracle ;

User created.

SQL> grant dba to xff1;

Grant succeeded.

SQL> CREATE USER XFF2 IDENTIFIED BY oracle ;

User created.

SQL> grant connect,resource to xff2;

Grant succeeded.

SQL> create table xff1.t_object as select * from dba_objects;

Table created.

SQL> create index xff2.i_object on xff1.t_object(object_id);

Index created.

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

使用expdp导出数据

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

Export: Release 11.2.0.4.0 - Production on Sun Jan 19 11:37:02 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=xff1,xff2
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11 MB
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
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "XFF1"."T_OBJECT"                           8.774 MB   90627 rows
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 11:37:05 2025 elapsed 0 00:00:02

使用impdp导入数据（把xff用户映射到u中）

C:\Users\XFF>impdp "'/ as sysdba'" dumpfile=xff.dmp remap_schema=xff1:u1,xff2:u2

Import: Release 11.2.0.4.0 - Production on Sun Jan 19 11:37:16 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=xff1:u1,xff2:u2
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
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U1"."T_OBJECT"                             8.774 MB   90627 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE INDEX "U2"."I_OBJECT" ON "U1"."T_OBJECT" ("OBJECT_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE
(INITIAL 65536 NEXT  1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 1
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"U2"."I_OBJECT" creation failed
Job "SYS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Sun Jan 19 11:37:17 2025 elapsed 0 00:00:00

重现了ORA-39083 ORA-00942错误,根据经验确认可能是由于impdp导入的时候,创建index切换到当前index所属用户进行,而该用户没有访问需要创建index对应的表的权限导致,通过impdp sqlfile解析出阿里.sql文件进行分析,确认是该情况导致.
index

建议一般情况下,不要把表和index创建到不同用户(schema)下面,更不要使用第三用户来操作.

发表在逻辑备份/恢复 | 标签为 impdp ORA-00942 | 留下评论

17813235971

QQ 咨询

分类目录归档：数据库

ORA-600 ktuPopDictI_1恢复

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

impdp 创建index提示ORA-00942: table or view does not exist

文章分类

最近发表

分类目录归档：数据库

ORA-600 ktuPopDictI_1恢复

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

impdp 创建index提示ORA-00942: table or view does not exist

标签云

文章分类

最近发表