数据库open报ORA-07445 kglsget错误处理

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：数据库open报ORA-07445 kglsget错误处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有朋友反馈,数据库服务器断电之后,强制拉库无法open成功

Wed Apr 19 18:13:30 2023
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 35 KB redo, 20 data blocks need recovery
Started redo application at
 Thread 1: logseq 4, block 3
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
  Mem# 0: E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Completed redo application of 0.02MB
Completed crash recovery at
 Thread 1: logseq 4, block 73, scn 157134992
 20 data blocks read, 20 data blocks written, 35 redo k-bytes read
Thread 1 advanced to log sequence 5 (thread open)
Thread 1 opened at log sequence 5
  Current log# 2 seq# 5 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
Starting background process QMNC
Wed Apr 19 18:13:34 2023
QMNC started with pid=56, OS id=6380 
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x12] [PC:0x9068BE9, kglsget()+547]
ERROR: Unable to normalize symbol name for the following short stack (at offset 199):
dbgexProcessError()+193<-dbgeExecuteForError()+65<-dbgePostErrorKGE()+1726
<-dbkePostKGE_kgsf()+75<-kgeade()+560<-kgerev()+125<-kgerec5()+60<-sss_xcpt_EvalFilterEx()+1869
<-sss_xcpt_EvalFilter()+174<-.1.6_8+59<-00007FFB13102316<-00007FFB1311398D<-00007FFB130D93A7
<-00007FFB13112B1A<-kglsget()+547<-qcdolci()+80<-qcdoloi()+167<-qcdlgcd()+940<-kkdlgcd()+129
<-PGOSF158_kkmfbtcn()+17<-qcsIsColInFro()+474<-qcsRslvColWithinQbc()+304<-qcsStrongColRslv()+632
<-qcsRslvName()+212<-qcsridn()+104<-qcsraic()+736<-qcspqbDescendents()+485<-qcspqb()+154<-kkmdrv()+200
<-opiSem()+2560<-opiDeferredSem()+565<-opitca()+391<-PGOSF525_kksFullTypeCheck()+27<-rpiswu2()+2757
<-kksLoadChild()+9357<-kxsGetRuntimeLock()+2320<-kksfbc()+15225<-kkspsc0()+2117<-kksParseCursor()+181
<-opiosq0()+2538<-opiall0()+6705<-opikpr()+699<-opiodr()+1662<-rpidrus()+862<-rpidru()+154<-rpiswu2()+2757
<-kprball()+1610<-qmCheckIfXdbInstalled()+1059<-qm_open_db()+11<-kscnfy()+778
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_6036.trc  (incident=113085):
ORA-07445: 出现异常错误: 核心转储 [kglsget()+547] [ACCESS_VIOLATION] [ADDR:0x12] [PC:0x9068BE9] [UNABLE_TO_READ] []
Incident details in: e:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_113085\orcl_ora_6036_i113085.trc
Wed Apr 19 18:13:36 2023
Trace dumping is performing id=[cdmp_20230419181336]
Wed Apr 19 18:13:39 2023
PMON (ospid: 6624): terminating the instance due to error 397
Instance terminated by PMON, pid = 6624

查看trace文件

*** 2023-04-21 17:29:42.077
*** SESSION ID:(898.359) 2023-04-21 17:29:42.077
*** CLIENT ID:() 2023-04-21 17:29:42.077
*** SERVICE NAME:(SYS$USERS) 2023-04-21 17:29:42.077
*** MODULE NAME:(sqlplus.exe) 2023-04-21 17:29:42.077
*** ACTION NAME:() 2023-04-21 17:29:42.077
 
Dump continued from file: e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_9648.trc
ORA-07445: 出现异常错误: 核心转储 [kglsget()+547] [ACCESS_VIOLATION] [ADDR:0x12] [PC:0x9138BE9] [UNABLE_TO_READ] []

========= Dump for incident 444285 (ORA 7445 [kglsget()+547]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x12] [PC:0x9138BE9, kglsget()+547]

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Process Id: 0x00000a8c  Thread Id : 0x000025b0    Time : Fri Apr 21 17:29:42 
Excp. Code: 0xc0000005  Excp. Type: ACCESS_VIO    Flags: 0x00000000

------------------- Registers ----------------------------
ip=0000000009138BE9 sp=00000000276FA470 rp=0000000A0BF280D0
r1=000000001089AFE0 r2=0000000000000000 r3=0000000000000101 
r4=00000009BDFE40B0 r5=00000000276FA470 r6=0000000A0BF280D0 r7=00000000000005C0 
r8=00000009B9DC4F48 r9=0000000A0BF280D0 r10=0000000000000200 r11=00000000276FA590 
r12=00000009ED85F4C0 r13=000000000CB3C070 r14=0000000000000001 r15=0000000000000001 
------------------- End of Registers ---------------------


*** 2023-04-21 17:29:42.077
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=gtf6tgc2ycgxx) -----
select count(*) from XDB.XDB$SCHEMA s where s.xmldata.schema_url = 'http://xmlns.oracle.com/xdb/XDBSchema.xsd'

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
kglsget()+547                 0000000000000000     009962700 00996270C 000000000
                                                   0276DCCA0
qcdolci()+80         CALL???  kglsget()+347        9C5F9E7D0 009138AE2 000000018
                                                   000000001
qcdoloi()+167        CALL???  qcdolci()            00000DC7E 008D52DEF 000000006
                                                   00CB5BEE8
qcdlgcd()+940        CALL???  qcdoloi()            000000000 000000000 000000000
                                                   000000000
kkdlgcd()+129        CALL???  qcdlgcd()            000000000 9B9DC6C78 000000000
                                                   01089AA20
__PGOSF158_kkmfbtcn  CALL???  kkdlgcd()            01089AFE0 00CB5BEE8 000000000
()+17                                              000000000
qcsIsColInFro()+474  CALL???  __PGOSF158_kkmfbtcn  000000001 9BDFE41E0 000000000
                              ()                   000000001
qcsRslvColWithinQbc  CALL???  qcsIsColInFro()      900000000 01089AFE0 0276FCFC0
()+304                                             742E204F1B71
qcsStrongColRslv()+  CALL???  qcsRslvColWithinQbc  000000010 27F00000000
632                           ()+73                000000000 000000000
qcsRslvName()+212    CALL???  qcsStrongColRslv()+  0276FA9E0 9C3F136C0 00764E4E0
                              516                  0093A505B
qcsridn()+104        CALL???  qcsRslvName()        101000000000000 000000000
                                                   000000000 0276FAE90
qcsraic()+736        CALL???  qcsridn()            0276FACB0 0276FACB0 000000000
                                                   000000001
qcspqbDescendents()  CALL???  qcsraic()            0276FAE90 01089AFE0 00CB5D030
+485                                               00CB5CEE8
qcspqb()+154         CALL???  qcspqbDescendents()  00000002A 008D53029 01089AFE0
                                                   000000000
kkmdrv()+200         CALL???  qcspqb()             000000029 00895C700 0276FAF90
                                                   00895C700
opiSem()+2560        CALL???  kkmdrv()             00CB31730 0108B7820 000000001
                                                   9C3F13730
opiDeferredSem()+56  CALL???  opiSem()             0276FCFC0 A09FA3D68 00000006E
5                                                  000000001
opitca()+391         CALL???  opiDeferredSem()     000000001 000000000 006F7AC18
                                                   000000000
__PGOSF525_kksFullT  CALL???  opitca()             037615F10 9C3F138D0 0277000A0
ypeCheck()+27                                      00895C700
rpiswu2()+2757       CALL???  __PGOSF525_kksFullT  0276FEB98 000004018 0108A04E0
                              ypeCheck()           0108A0700
kksLoadChild()+9357  CALL???  rpiswu2()            9F86A81C8 000000000 9C5F9D1E4
                                                   000000002
kxsGetRuntimeLock()  CALL???  kksLoadChild()       01089AFE0 A09FA39D8 0276FF400
+2320                                              A09FA39D8
kksfbc()+15225       CALL???  kxsGetRuntimeLock()  01089AFE0 037615F10 0276FF400
                                                   000000103
kkspsc0()+2117       CALL???  kksfbc()             037615F10 000000003 A00000108
                                                   0076D1C80
kksParseCursor()+18  CALL???  kkspsc0()            00CAD1DA8 0076D1C80 00000006F
1                                                  000000003
opiosq0()+2538       CALL???  kksParseCursor()     00CACF990 000000000 00895C700
                                                   027700940
opiall0()+6705       CALL???  opiosq0()            000000003 00000000E 0277010C0
                                                   000000020
opikpr()+699         CALL???  opiall0()            000000065 000000022 0277018C8
                                                   000000000
opiodr()+1662        CALL???  opikpr()             000000065 90000001C 027703208
                                                   01089AA20
rpidrus()+862        CALL???  opiodr()             000000065 00000001C 027703208
                                                   000000000
rpidru()+154         CALL???  rpidrus()            0277027F8 000000000 000000000
                                                   000000000
rpiswu2()+2757       CALL???  rpidru()             027703030 000000000 000000000
                                                   000000000
kprball()+1610       CALL???  rpiswu2()            9F86A81C8 000000000 027702E50
                                                   000000002
qmCheckIfXdbInstall  CALL???  kprball()            027703208 000000100 000000002
ed()+1059                                          00CAEC188
qm_open_db()+11      CALL???  qmCheckIfXdbInstall  000000000 000000000 000000000
                              ed()                 00895C700
kscnfy()+778         CALL???  qm_open_db()         657FC5CD00000019 000000000
                                                   000000000 100000000
adbdrv()+45489       CALL???  kscnfy()             000000019 000000000 000000000
                                                   000000000
opiexe()+20842       CALL???  adbdrv()             000000023 000000003 A00000102
                                                   000000000
opiosq0()+5129       CALL???  opiexe()+16981       000000004 000000000 02770A8C0
                                                   009121AB3
kpooprx()+357        CALL???  opiosq0()            000000003 00000000E 02770AB90
                                                   0000000A4
kpoal8()+940         CALL???  kpooprx()            01089AFE0 0090CC0A8 01089AFE0
                                                   000000001
opiodr()+1662        CALL???  kpoal8()             00000005E 00000001C 02770E100
                                                   00ABBF224
ttcpip()+1325        CALL???  opiodr()             00000005E 00000001C 02770E100
                                                   4100200000000000
opitsk()+2040        CALL???  ttcpip()             0108B4D00 000000000 000000000
                                                   000000000
opiino()+1258        CALL???  opitsk()             00000001E 000000000 000000000
                                                   02770F9F8
opiodr()+1662        CALL???  opiino()             00000003C 000000004 02770FAB0
                                                   000000000
opidrv()+864         CALL???  opiodr()             00000003C 000000004 02770FAB0
                                                   615C3A6500000000
sou2o()+98           CALL???  opidrv()+150         00000003C 000000004 02770FAB0
                                                   000000000
opimai_real()+158    CALL???  sou2o()              064425745 000000000 000000000
                                                   02770FBA4
opimai()+191         CALL???  opimai_real()        7FFA241388C0 7FFA24138A49
                                                   000000000 00895C700

*** 2023-04-21 17:29:42.343
OracleThreadStart()  CALL???  opimai()             000401452 000000002 0085DFE20
+724                                               0000025B0
00007FFA246713D2     CALL???  OracleThreadStart()  00FCAFF18 000000000 000000000
                                                   000000000
00007FFA26D403C4     CALL???  00007FFA246713B0     7FFA246713B0 000000000
                                                   000000000 000000000
 

--------------------- Binary Stack Dump ---------------------

确定报错是在select count(*) from XDB.XDB$SCHEMA s where s.xmldata.schema_url = ‘http://xmlns.oracle.com/xdb/XDBSchema.xsd’这个sql语句中,对其数据库启动过程进行跟踪，没有发现该语句而是只有报错

EXEC #6:c=0,e=181,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3755742892,tim=22655713465
FETCH #6:c=0,e=12,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,plh=3755742892,tim=22655713503
CLOSE #6:c=0,e=6,dep=2,type=3,tim=22655713532
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x12] [PC:0x9138BE9, kglsget()+547]
 dump file: e:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_452285\orcl_ora_9848_i452285.trc
ORA-07445: 出现异常错误: 核心转储 [kglsget()+547] [ACCESS_VIOLATION] [ADDR:0x12] [PC:0x9138BE9] [UNABLE_TO_READ] []

证明ORA-07445 kglsget错误的语句没有到sql执行阶段,而是在解析阶段就开始报错.通过对oracle启动过程进行处理(因为该语句不是数据库启动个必须的语句)，规避掉该sql执行,实现数据库正常open
20230421223825

12.1.0.2最新patch信息—202304

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：12.1.0.2最新patch信息—202304

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

Database Patch Set Update (PSU)

Release Date Version Download link Included in Windows Bundle
18-Apr-2023 12.1.0.2.230418 (Apr 2023) Database Patch Set Update (DB PSU) Patch 34998358 NA – out of error correction
17-Jan-2023 12.1.0.2.230117 (Jan 2023) Database Patch Set Update (DB PSU) Patch 34677720 NA – out of error correction
18-Oct-2022 12.1.0.2.221018 (Oct 2022) Database Patch Set Update (DB PSU) Patch 34386266 NA – out of error correction
19-Jul-2022 12.1.0.2.220719 (Jul 2022) Database Patch Set Update (DB PSU) Patch 34057742 12.1.0.2.220719 WIN DB BP Patch 33883271
19-Apr-2022 12.1.0.2.220419 (Apr 2022) Database Patch Set Update (DB PSU) Patch 33711081 12.1.0.2.220419 WIN DB BP Patch 33777450
18-Jan-2022 12.1.0.2.220118 (Jan 2022) Database Patch Set Update (DB PSU) Patch 33477199 12.1.0.2.220118 WIN DB BP Patch 33492893
19-Oct-2021 12.1.0.2.211019 (Oct 2021) Database Patch Set Update (DB PSU) Patch 33128590 12.1.0.2.211019 WIN DB BP Patch 33174365
20-Jul-2021 12.1.0.2.210720 (Jul 2021) Database Patch Set Update (DB PSU) Patch 32768233 12.1.0.2.210720 WIN DB BP Patch 32774982
20-Apr-2021 12.1.0.2.210420 (Apr 2021) Database Patch Set Update (DB PSU) Patch 32328635 12.1.0.2.210420 WIN DB BP Patch 32396181
19-Jan-2021 12.1.0.2.210119 (Jan 2021) Database Patch Set Update (DB PSU) Patch 31985579 12.1.0.2.210119 WIN DB BP Patch 32000405
20-Oct-2020 12.1.0.2.201020 (Oct 2020) Database Patch Set Update (DB PSU) Patch 31550110 12.1.0.2.201020 WIN DB BP Patch 31658987
14-Jul-2020 12.1.0.2.200714 (Jul 2020) Database Patch Set Update (DB PSU) Patch 31113348 12.1.0.2.200714 WIN DB BP Patch 31211574
14-Apr-2020 12.1.0.2.200414 (Apr 2020) Database Patch Set Update (DB PSU) Patch 30700212 12.1.0.2.200414 WIN DB BP Patch 30861721
14-Jan-2020 12.1.0.2.200114 (Jan 2020) Database Patch Set Update (DB PSU) Patch 30340202 12.1.0.2.200114 WIN DB BP Patch 30455401
15-Oct-2019 12.1.0.2.191015 (Oct 2019) Database Patch Set Update (DB PSU) Patch 29918340 12.1.0.2.191015 WIN DB BP Patch 30049606
16-Jul-2019 12.1.0.2.190716 (Jul 2019) Database Patch Set Update (DB PSU) Patch 29494060 12.1.0.2.190716 WIN DB BP Patch 29831650
16-Apr-2019 12.1.0.2.190416 (Apr 2019) Database Patch Set Update (DB PSU) Patch 29141015 12.1.0.2.190416 WIN DB BP Patch 29413116
15-Jan-2019 12.1.0.2.190115 (Jan 2019) Database Patch Set Update (DB PSU) Patch 28729169 12.1.0.2.190115 WIN DB BP Patch 28810679
16-Oct-2018 12.1.0.2.181016 (Oct 2018) Database Patch Set Update (DB PSU) Patch 28259833 12.1.0.2.181016 WIN DB BP Patch 28563501
17-Jul-2018 12.1.0.2.180717 (Jul 2018) Database Patch Set Update (DB PSU) Patch 27547329 12.1.0.2.180717 WIN DB BP Patch 27937907
17-Apr-2018 12.1.0.2.180417 (Apr 2018) Database Patch Set Update (DB PSU) Patch 27338041 12.1.0.2.180417 WIN DB BP Patch 27440294
16-Jan-2018 12.1.0.2.180116 (Jan 2018) Database Patch Set Update (DB PSU) Patch 26925311 12.1.0.2.180116 WIN DB BP Patch 27162953
17-Oct-2017 12.1.0.2.171017 (Oct 2017) Database Patch Set Update (DB PSU) Patch 26713565 12.1.0.2.171017 WIN DB BP Patch 26720785
14-Aug-2017 12.1.0.2.170814 (Jul 2017) Database Patch Set Update (DB PSU) Patch 26609783 12.1.0.2.170814 WIN DB BP Patch 26161726
18-Jul-2017 12.1.0.2.170718 (Jul 2017) Database Patch Set Update (DB PSU) Patch 25755742 12.1.0.2.170718 WIN DB BP Patch 26161724
18-Apr-2017 12.1.0.2.170418 (Apr 2017) Database Patch Set Update (DB PSU) Patch 25171037 12.1.0.2.170418 WIN DB BP Patch 25632533
17-Jan-2017 12.1.0.2.170117 (Jan 2017) Database Patch Set Update (DB PSU) Patch 24732082 12.1.0.2.170117 WIN DB BP Patch 25115951
18-Oct-2016 12.1.0.2.161018 (Oct 2016) Database Patch Set Update (DB PSU) Patch 24006101 12.1.0.2.161018 WIN DB BP Patch 24591642
19-Jul-2016 12.1.0.2.160719 (Jul 2016) Database Patch Set Update (DB PSU) Patch 23054246 12.1.0.2.160719 WIN DB BP Patch 23530387
19-Apr-2016 12.1.0.2.160419 (Apr 2016) Database Patch Set Update (DB PSU) Patch 22291127 12.1.0.2.160419 WIN DB BP Patch 22809813
19-Jan-2016 12.1.0.2.160119 (Jan 2016) Database Patch Set Update (DB PSU) Patch 21948354 12.1.0.2.160119 WIN DB BP Patch 22310559
20-Oct-2015 12.1.0.2.5 (Oct 2015) Database Patch Set Update (DB PSU) Patch 21359755 12.1.0.2 Patch 10 Patch 21821214
14-Jul-2015 12.1.0.2.4 (Jul 2015) Database Patch Set Update (DB PSU) Patch 20831110 12.1.0.2 Patch 7 Patch 21126814
14-Apr-2015 12.1.0.2.3 (Apr 2015) Database Patch Set Update (DB PSU) Patch 20299023 12.1.0.2 Patch 4 Patch 20684004
20-Jan-2015 12.1.0.2.2 (Jan 2015) Database Patch Set Update (DB PSU) Patch 19769480 12.1.0.2 Patch 1 Patch 19720843
14-Oct-2014 12.1.0.2.1 (Oct 2014) Database Patch Set Update (DB PSU) Patch 19303936 none

Grid Infrastructure Patch Set Update (PSU)

Release Date Version Download link
18-Apr-2023 12.1.0.2.230418 (Apr 2023) Grid Infrastructure Patch Set Update (GI PSU) Patch 35058268
17-Jan-2023 12.1.0.2.230117 (Jan 2023) Grid Infrastructure Patch Set Update (GI PSU) Patch 34774519
18-Oct-2022 12.1.0.2.221018 (Oct 2022) Grid Infrastructure Patch Set Update (GI PSU) Patch 34535560
19-Jul-2022 12.1.0.2.220719 (Jul 2022) Grid Infrastructure Patch Set Update (GI PSU) Patch 34204576
19-Apr-2022 12.1.0.2.220419 (Apr 2022) Grid Infrastructure Patch Set Update (GI PSU) Patch 33829718
18-Jan-2022 12.1.0.2.220118 (Jan 2022) Grid Infrastructure Patch Set Update (GI PSU) Patch 33575274
19-Oct-2021 12.1.0.2.211019 (Oct 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 33248367
20-Jul-2021 12.1.0.2.210720 (Jul 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32917447
20-Apr-2021 12.1.0.2.210420 (Apr 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32495126
19-Jan-2021 12.1.0.2.210119 (Jan 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32131261
20-Oct-2020 12.1.0.2.201020 (Oct 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 31718737
14-Jul-2020 12.1.0.2.200714 (Jul 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 31305174
14-Apr-2020 12.1.0.2.200414 (Apr 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 30805421
14-Jan-2020 12.1.0.2.200114 (Jan 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 30464119
15-Oct-2019 12.1.0.2.191015 (Oct 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 30070257
16-Jul-2019 12.1.0.2.190716 (Jul 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 29698592
16-Apr-2019 12.1.0.2.190416 (Apr 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 29176115
15-Jan-2019 12.1.0.2.190115 (Jan 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 28813884
16-Oct-2018 12.1.0.2.181016 (Oct 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 28349311
17-Jul-2018 12.1.0.2.180717 (Jul 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 27967747
17-Apr-2018 12.1.0.2.180417 (Apr 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 27468957
16-Jan-2018 12.1.0.2.180116 (Jan 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 27010872
17-Oct-2017 12.1.0.2.171017 (Oct 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 26635815
14-Aug-2017 12.1.0.2.170814 (Jul 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 26610308
18-Jul-2017 12.1.0.2.170718 (Jul 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 25901062
18-Apr-2017 12.1.0.2.170418 (Apr 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 25434003
17-Jan-2017 12.1.0.2.170117 (Jan 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 24917825
18-Oct-2016 12.1.0.2.161018 (Oct 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 24412235
19-Jul-2016 12.1.0.2.160719 (Jul 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 23273629
19-Apr-2016 12.1.0.2.160419 (Apr 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 22646084
19-Jan-2016 12.1.0.2.160119 (Jan 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 22191349
20-Oct-2015 12.1.0.2.5 (Oct 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 21523234
14-Jul-2015 12.1.0.2.4 (Jul 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 20996835
14-Apr-2015 12.1.0.2.3 (Apr 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 20485724
20-Jan-2015 12.1.0.2.2 (Jan 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 19954978
14-Oct-2014 12.1.0.2.1 (Oct 2014) Grid Infrastructure Patch Set Update (GI PSU) Patch 19392646

OJVM Component Patch Set Update (PSU)

Release Date Version Unix PSU Patch Windows Bundle Patch
18-Apr-2023 12.1.0.2.230418 (Apr 2023) OJVM Component Patch Set Update Patch 35077439 NA – out of error correction
17-Jan-2023 12.1.0.2.230117 (Jan 2023) OJVM Component Patch Set Update Patch 34763317 NA – out of error correction
18-Oct-2022 12.1.0.2.221018 (Oct 2022) OJVM Component Patch Set Update Patch 34412250 NA – out of error correction
19-Jul-2022 12.1.0.2.220719 (Jul 2022) OJVM Component Patch Set Update Patch 34086863 Patch 34185253
19-Apr-2022 12.1.0.2.220419 (Apr 2022) OJVM Component Patch Set Update Patch 33808385 Patch 33881387
18-Jan-2022 12.1.0.2.220118 (Jan 2022) OJVM Component Patch Set Update Patch 33561268 Patch 33577533
19-Oct-2021 12.1.0.2.211019 (Oct 2021) OJVM Component Patch Set Update Patch 33192628 Patch 33248785
20-Jul-2021 12.1.0.2.210720 (Jul 2021) OJVM Component Patch Set Update Patch 32876425 Patch 32905878
20-Apr-2021 12.1.0.2.210420 (Apr 2021) OJVM Component Patch Set Update Patch 32473164 Patch 32427683
19-Jan-2021 12.1.0.2.210119 (Jan 2021) OJVM Component Patch Set Update Patch 32119956 Patch 32142066
20-Oct-2020 12.1.0.2.201020 (Oct 2020) OJVM Component Patch Set Update Patch 31668915 Patch 31740134
14-Jul-2020 12.1.0.2.200714 (Jul 2020) OJVM Component Patch Set Update Patch 31219939 Patch 31465095
14-Apr-2020 12.1.0.2.200414 (Apr 2020) OJVM Component Patch Set Update Patch 30805558 Patch 31037459
14-Jan-2020 12.1.0.2.200114 (Jan 2020) OJVM Component Patch Set Update Patch 30502041 Patch 30671054
15-Oct-2019 12.1.0.2.191015 (Oct 2019) OJVM Component Patch Set Update Patch 30128197 Patch 30268189
16-Jul-2019 12.1.0.2.190716 (Jul 2019) OJVM Component Patch Set Update Patch 29774383 Patch 29837393
16-Apr-2019 12.1.0.2.190416 (Apr 2019) OJVM Component Patch Set Update Patch 29251241 Patch 29447962
15-Jan-2019 12.1.0.2.190115 (Jan 2019) OJVM Component Patch Set Update Patch 28790654 Patch 28994063
16-Oct-2018 12.1.0.2.181016 (Oct 2018) OJVM Component Patch Set Update Patch 28440711 Patch 28412299
17-Jul-2018 12.1.0.2.180717 (Jul 2018) OJVM Component Patch Set Update Patch 27923320 Patch 28135126
17-Apr-2018 12.1.0.2.180417 (Apr 2018) OJVM Component Patch Set Update Patch 27475603 Patch 27440294
16-Jan-2018 12.1.0.2.180116 (Jan 2018) OJVM Component Patch Set Update Patch 27001733 Patch 27001733
17-Oct-2017 12.1.0.2.171017 (Oct 2017) OJVM Component Patch Set Update Patch 26635845 Patch 26792364
18-Jul-2017 12.1.0.2.170718 (Jul 2017) OJVM Component Patch Set Update Patch 26027162 Patch 26182439
18-Apr-2017 12.1.0.2.170418 (Apr 2017) OJVM Component Patch Set Update Patch 25437695 Patch 25590993
17-Jan-2017 12.1.0.2.170117 (Jan 2017) OJVM Component Patch Set Update Patch 24917972 Patch 25112498
18-Oct-2016 12.1.0.2.161018 (Oct 2016) OJVM Component Patch Set Update Patch 24315824 Patch 24591630
19-Jul-2016 12.1.0.2.160719 (Jul 2016) OJVM Component Patch Set Update Patch 23177536 Patch 23515290
19-Apr-2016 12.1.0.2.160419 (Apr 2016) OJVM Component Patch Set Update Patch 22674709 Patch 22839633
19-Jan-2016 12.1.0.2.160119 (Jan 2016) OJVM Component Patch Set Update Patch 22139226 Patch 22311086
20-Oct-2015 12.1.0.2.5 (Oct 2015) OJVM Component Patch Set Update Patch 21555660 Patch 21788394
14-Jul-2015 12.1.0.2.4 (Jul 2015) OJVM Component Patch Set Update Patch 21068507 Patch 21153530
14-Apr-2015 12.1.0.2.3 (Apr 2015) OJVM Component Patch Set Update Patch 20415564 Patch 20391199
20-Jan-2015 12.1.0.2.2 (Jan 2015) OJVM Component Patch Set Update Patch 19877336 Patch 20225938
14-Oct-2014 12.1.0.2.1 (Oct 2014) OJVM Component Patch Set Update Patch 19282028 -

参考：Database 12.1.0.2 Proactive Patch Information (Doc ID 2285558.1)

11.2.0.4最新patch信息—202304

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：11.2.0.4最新patch信息—202304

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

Database Patch Set Update

Release Date Version Download link Included in Windows Bundle
18-Apr-2023 11.2.0.4.230418 (Apr 2023) Database Patch Set Update (DB PSU) Patch 34998337 * Out of Error Correction
17-Jan-2023 11.2.0.4.230117 (Jan 2023) Database Patch Set Update (DB PSU) Patch 34677698 * Out of Error Correction
18-Oct-2022 11.2.0.4.221018 (Oct 2022) Database Patch Set Update (DB PSU) Patch 34386237 * Patch 34474433
19-Jul-2022 11.2.0.4.220719 (Jul 2022) Database Patch Set Update (DB PSU) Patch 34057724 * Patch 33883353
19-Apr-2022 11.2.0.4.220419 (Apr 2022) Database Patch Set Update (DB PSU) Patch 33711103 * Patch 33776715
18-Jan-2022 11.2.0.4.220118 (Jan 2022) Database Patch Set Update (DB PSU) Patch 33477185 * Patch 33488457
19-Oct-2021 11.2.0.4.211019 (Oct 2021) Database Patch Set Update (DB PSU) Patch 33128584 * Patch 33174351
20-Jul-2021 11.2.0.4.210720 (Jul 2021) Database Patch Set Update (DB PSU) Patch 32758711 * Patch 32775108
20-Apr-2021 11.2.0.4.210420 (Apr 2021) Database Patch Set Update (DB PSU) Patch 32328626 * Patch 32392141
19-Jan-2021 11.2.0.4.210119 (Jan 2021) Database Patch Set Update (DB PSU) Patch 31983472 Patch 32003403
20-Oct-2020 11.2.0.4.201020 (Oct 2020) Database Patch Set Update (DB PSU) Patch 31537677 Patch 31659823
14-Jul-2020 11.2.0.4.200714 (Jul 2020) Database Patch Set Update (DB PSU) Patch 31103343 Patch 31169916
14-Apr-2020 11.2.0.4.200414 (Apr 2020) Database Patch Set Update (DB PSU) Patch 30670774 Patch 31169916
14-Jan-2020 11.2.0.4.200114 (Jan 2020) Database Patch Set Update (DB PSU) Patch 30298532 Patch 30502376
15-Oct-2019 11.2.0.4.191015 (Oct 2019) Database Patch Set Update (DB PSU) Patch 29913194 Patch 30151661
16-Jul-2019 11.2.0.4.190716 (Jul 2019) Database Patch Set Update (DB PSU) Patch 29497421 Patch 29596609
16-Apr-2019 11.2.0.4.190416 (Apr 2019) Database Patch Set Update (DB PSU) Patch 29141056 Patch 29218820
15-Jan-2019 11.2.0.4.190115 (Jan 2019) Database Patch Set Update (DB PSU) Patch 28729262 Patch 28761877
16-Oct-2018 11.2.0.4.181016 (Oct 2018) Database Patch Set Update (DB PSU) Patch 28204707 Patch 28265827
17-Jul-2018 11.2.0.4.180717 (Jul 2018) Database Patch Set Update (DB PSU) Patch 27734982 Patch 27695940
17-Apr-2018 11.2.0.4.180417 (Apr 2018) Database Patch Set Update (DB PSU) Patch 27338049 Patch 27426753
16-Jan-2018 11.2.0.4.180116 (Jan 2018) Database Patch Set Update (DB PSU) Patch 26925576 Patch 26925576
17-Oct-2017 11.2.0.4.171017 (Oct 2017) Database Patch Set Update (DB PSU) Patch 26392168 Patch 26581376
14-Aug-2017 11.2.0.4.170814 (Jul 2017) Database Patch Set Update (DB PSU) Patch 26609445 Patch 26194138
18-Jul-2017 11.2.0.4.170718 (Jul 2017) Database Patch Set Update (DB PSU) Patch 25869727 Patch 26194136
18-Apr-2017 11.2.0.4.170418 (Apr 2017) Database Patch Set Update (DB PSU) Patch 24732075 Patch 25632525
17-Jan-2017 **
18-Oct-2016 11.2.0.4.161018 (Oct 2016) Database Patch Set Update (DB PSU) Patch 24006111 Patch 24591646
19-Jul-2016 11.2.0.4.160719 (Jul 2016) Database Patch Set Update (DB PSU) Patch 23054359 Patch 23530402
19-Apr-2016 11.2.0.4.160419 (Apr 2016) Database Patch Set Update (DB PSU) Patch 22502456 Patch 22839608
19-Jan-2016 11.2.0.4.160119 (Jan 2016) Database Patch Set Update (DB PSU) Patch 21948347 Patch 22310544
20-Oct-2015 11.2.0.4.8 (Oct 2015) Database Patch Set Update (DB PSU) Patch 21352635 Patch 21821802
14-Jul-2015 11.2.0.4.7 (Jul 2015) Database Patch Set Update (DB PSU) Patch 20760982 Patch 20833831
14-Apr-2015 11.2.0.4.6 (Apr 2015) Database Patch Set Update (DB PSU) Patch 20299013 Patch 20544696
20-Jan-2015 11.2.0.4.5 (Jan 2015) Database Patch Set Update (DB PSU) Patch 19769489 Patch 20127071
14-Oct-2014 11.2.0.4.4 (Oct 2014) Database Patch Set Update (DB PSU) Patch 19121551 Patch 19651773
14-Jul-2014 11.2.0.4.3 (Jul 2014) Database Patch Set Update (DB PSU) Patch 18522509 Patch 18842982
15-Apr-2014 11.2.0.4.2 (Apr 2014) Database Patch Set Update (PSU) Patch 18031668 Patch 18296644
15-Jan-2014 11.2.0.4.1 (Jan 2014) Database Patch Set Update (PSU) Patch 17478514 Patch 17987366

Grid Infrastructure Patch Set Update

Release Date Version Download link
18-Apr-2023 11.2.0.4.230418 (Apr 2023) Grid Infrastructure Patch Set Update (GI PSU) * Patch 35058300
17-Jan-2023 11.2.0.4.230117 (Jan 2023) Grid Infrastructure Patch Set Update (GI PSU) * Patch 34774506
18-Oct-2022 11.2.0.4.221018 (Oct 2022) Grid Infrastructure Patch Set Update (GI PSU) * Patch 34536853
19-Jul-2022 11.2.0.4.220719 (Jul 2022) Grid Infrastructure Patch Set Update (GI PSU) * Patch 34204533
19-Apr-2022 11.2.0.4.220419 (Apr 2022) Grid Infrastructure Patch Set Update (GI PSU) * Patch 33829709
18-Jan-2022 11.2.0.4.220118 (Jan 2022) Grid Infrastructure Patch Set Update (GI PSU) * Patch 33575261
19-Oct-2021 11.2.0.4.211019 (Oct 2021) Grid Infrastructure Patch Set Update (GI PSU) * Patch 33248354
20-Jul-2021 11.2.0.4.210720 (Jul 2021) Grid Infrastructure Patch Set Update (GI PSU) * Patch 32917428
20-Apr-2021 11.2.0.4.210420 (Apr 2021) Grid Infrastructure Patch Set Update (GI PSU) * Patch 32495145
19-Jan-2021 11.2.0.4.210119 (Jan 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32131250
20-Oct-2020 11.2.0.4.201020 (Oct 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 31718723
14-Jul-2020 11.2.0.4.200714 (Jul 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 31305209
14-Apr-2020 11.2.0.4.200414 (Apr 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 30805461
14-Jan-2020 11.2.0.4.200114 (Jan 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 30501155
15-Oct-2019 11.2.0.4.191015 (Oct 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 30070097
16-Jul-2019 11.2.0.4.190716 (Jul 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 29698727
16-Apr-2019 11.2.0.4.190416 (Apr 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 29255947
15-Jan-2019 11.2.0.4.190115 (Jan 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 28813878
16-Oct-2018 11.2.0.4.181016 (Oct 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 28429134
17-Jul-2018 11.2.0.4.180717 (Jul 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 27967757
17-Apr-2018 11.2.0.4.180417 (Apr 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 27475913
16-Jan-2018 11.2.0.4.180116 (Jan 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 27107360
17-Oct-2017 11.2.0.4.171017 (Oct 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 26635745
14-Aug-2017 11.2.0.4.170814 (Jul 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 26610246
18-Jul-2017 11.2.0.4.170718 (Jul 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 26030799
18-Apr-2017 11.2.0.4.170418 (Apr 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 25476126
17-Jan-2017 **
18-Oct-2016 11.2.0.4.161018 (Oct 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 24436338
19-Jul-2016 11.2.0.4.160719 (Jul 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 23274134
19-Apr-2016 11.2.0.4.160419 (Apr 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 22646198
19-Jan-2016 11.2.0.4.160119 (Jan 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 22191577
20-Oct-2015 11.2.0.4.8 (Oct 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 21523375
14-Jul-2015 11.2.0.4.7 (Jul 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 20996923
14-Apr-2015 11.2.0.4.6 (Apr 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 20485808
20-Jan-2015 11.2.0.4.5 (Jan 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 19955028
14-Oct-2014 11.2.0.4.4 (Oct 2014) Grid Infrastructure Patch Set Update (GI PSU) Patch 19380115
15-Jul-2014 11.2.0.4.3 (Jul 2014) Grid Infrastructure Patch Set Update (GI PSU) Patch 18706472
15-Apr-2014 11.2.0.4.2 (Apr 2014) Grid Infrastructure Patch Set Update (GI PSU) Patch 18139609

11.2.0.4 OJVM Patch Set Update

Release Date Version Unix PSU Patch Windows Bundle Patch
18-Apr-2023 11.2.0.4.230418 (Apr 2023) OJVM Component Patch Set Update Patch 35039831 * Out of Error Correction
17-Jan-2023 11.2.0.4.230117 (Jan 2023) OJVM Component Patch Set Update Patch 34763334 * Out of Error Correction
18-Oct-2022 11.2.0.4.221018 (Oct 2022) OJVM Component Patch Set Update Patch 34412266 * Patch 34513867
19-Jul-2022 11.2.0.4.220719 (Jul 2022) OJVM Component Patch Set Update Patch 34085652 * Patch 34185082
19-Apr-2022 11.2.0.4.220419 (Apr 2022) OJVM Component Patch Set Update Patch 33829826 * Patch 33881335
18-Jan-2022 11.2.0.4.220118 (Jan 2022) OJVM Component Patch Set Update Patch 33561250 * Patch 33577510
19-Oct-2021 11.2.0.4.211019 (Oct 2021) OJVM Component Patch Set Update Patch 33192642 * Patch 33248711
20-Jul-2021 11.2.0.4.210720 (Jul 2021) OJVM Component Patch Set Update Patch 32876451 * Patch 32905855
20-Apr-2021 11.2.0.4.210420 (Apr 2021) OJVM Component Patch Set Update Patch 32671980 * Patch 32428494
19-Jan-2021 11.2.0.4.210119 (Jan 2021) OJVM Component Patch Set Update ** Patch 32145687
20-Oct-2020 11.2.0.4.201020 (Oct 2020) OJVM Component Patch Set Update Patch 31668908 Patch 31740195
14-Jul-2020 11.2.0.4.200714 (Jul 2020) OJVM Component Patch Set Update Patch 31219953 Patch 31169916
14-Apr-2020 11.2.0.4.200414 (Apr 2020) OJVM Component Patch Set Update Patch 30805543 Patch 31169933
14-Jan-2020 11.2.0.4.200114 (Jan 2020) OJVM Component Patch Set Update Patch 30503372 Patch 30671044
15-Oct-2019 11.2.0.4.191015 (Oct 2019) OJVM Component Patch Set Update Patch 30132974 Patch 30268157
16-Jul-2019 11.2.0.4.190716 (Jul 2019) OJVM Component Patch Set Update Patch 29610422 Patch 30012911
16-Apr-2019 11.2.0.4.190416 (Apr 2019) OJVM Component Patch Set Update Patch 29251270 Patch 29447971
15-Jan-2019 11.2.0.4.190115 (Jan 2019) OJVM Component Patch Set Update Patch 28790660 Patch 28994059
16-Oct-2018 11.2.0.4.181016 (Oct 2018) OJVM Component Patch Set Update Patch 28440700 Patch 28412269
17-Jul-2018 11.2.0.4.180717 (Jul 2018) OJVM Component Patch Set Update Patch 27923163 Patch 28135121
17-Apr-2018 11.2.0.4.180417 (Apr 2018) OJVM Component Patch Set Update Patch 27475598 Patch 27381640
16-Jan-2018 11.2.0.4.180116 (Jan 2018) OJVM Component Patch Set Update Patch 26925532 Patch 27163009
17-Oct-2017 11.2.0.4.171017 (Oct 2017) OJVM Component Patch Set Update Patch 26635834 Patch 26792358
18-Jul-2017 11.2.0.4.170718 (Jul 2017) OJVM Component Patch Set Update Patch 26027154 Patch 26182425
18-Apr-2017 11.2.0.4.170418 (Apr 2017) OJVM Component Patch Set Update Patch 25434033 Patch 25590979
17-Jan-2017 11.2.0.4.170117 (Jan 2017) OJVM Component Patch Set Update Patch 24917954 Patch 25043019
18-Oct-2016 11.2.0.4.161018 (Oct 2016) OJVM Component Patch Set Update Patch 24315821 Patch 24591637
19-Jul-2016 11.2.0.4.160719 (Jul 2016) OJVM Component Patch Set Update Patch 23177551 Patch 23515277
19-Apr-2016 11.2.0.4.160419 (Apr 2016) OJVM Component Patch Set Update Patch 22674697 Patch 22839614
19-Jan-2016 11.2.0.4.160119 (Jan 2016) OJVM Component Patch Set Update Patch 22139245 Patch 22311053
20-Oct-2015 11.2.0.4.5 (Oct 2015) OJVM Component Patch Set Update Patch 21555791 Patch 21788344
14-Jul-2015 11.2.0.4.4 (Jul 2015) OJVM Component Patch Set Update Patch 21068539 Patch 21153498
14-Apr-2015 11.2.0.4.3 (Apr 2015) OJVM Component Patch Set Update Patch 20406239 Patch 20225988
20-Jan-2015 11.2.0.4.2 (Jan 2015) OJVM Component Patch Set Update Patch 19877440 Patch 20225982
14-Oct-2014 11.2.0.4.1 (Oct 2014) OJVM Component Patch Set Update Patch 19282021 Patch 19799291

参考：Database 11.2.0.4 Proactive Patch Information (Doc ID 2285559.1)

数据库启动报ORA-600 kcbgtcr_13处理

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：数据库启动报ORA-600 kcbgtcr_13处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库发生故障,经过第三方处理过,接手之后,尝试open库报ORA-01190错误

Thu Apr 20 16:51:25 2023
alter database open upgrade
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_4818.trc:
ORA-01190: control file or data file 3 is from before the last RESETLOGS
ORA-01110: data file 3: '/data/topprod/undotbs01.dbf'
ORA-1190 signalled during: alter database open upgrade...

这个问题是由于resetlogs的时候有文件遗漏导致resetlogs scn和其他数据文件/ctl不匹配导致,以前类似处理文章:
bbed解决ORA-01190
12C sysaux 异常恢复—ORA-01190错误恢复
Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障

数据库启动报ORA-600 kcbgtcr_13错

</tmp> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 20 17:05:24 2023

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


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

SQL> recover database ;
Media recovery complete.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbgtcr_13], [], [], [], [], [],
[], [], [], [], [], []
Process ID: 5492
Session ID: 861 Serial number: 19

alert日志报错

Thu Apr 20 17:05:37 2023
SMON: enabling cache recovery
[5492] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:800184 end:800294 diff:110 (1 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_smon_4770.trc  (incident=2390097):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u2/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_2390097/xifenfei_smon_4770_i2390097.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_5492.trc  (incident=2390129):
ORA-00600: internal error code, arguments: [kcbgtcr_13], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u2/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_2390129/xifenfei_ora_5492_i2390129.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Block recovery from logseq 2, block 56 to scn 8615223701
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: /u2/oracle/oradata/xifenfei/redo02.log
Block recovery completed at rba 2.60.16, scn 2.25289110
Block recovery from logseq 2, block 56 to scn 8615223600
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: /u2/oracle/oradata/xifenfei/redo02.log
Block recovery completed at rba 2.59.16, scn 2.25289009
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_smon_4770.trc:
ORA-01595: error freeing extent (3) of rollback segment (5))
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_5492.trc:
ORA-00600: internal error code, arguments: [kcbgtcr_13], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_5492.trc:
ORA-00600: internal error code, arguments: [kcbgtcr_13], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 5492): terminating the instance due to error 600
Thu Apr 20 17:05:40 2023
Instance terminated by USER, pid = 5492
ORA-1092 signalled during: alter database open upgrade...
opiodr aborting process unknown ospid (5492) as a result of ORA-1092

这个错误比较明显是由于undo异常导致,规避掉undo问题,数据库启动成功

SQL> startup mount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 4.2758E+10 bytes
Fixed Size                  2237776 bytes
Variable Size            3.7447E+10 bytes
Database Buffers         5234491392 bytes
Redo Buffers               74444800 bytes
Database mounted.
SQL> alter database open ;

Database altered.

然后逻辑方式导出数据,导入到新库即可,对于此类问题在2014年处理过类似的case:
记录一次ORA-600 kccpb_sanity_check_2和ORA-600 kcbgtcr_13 错误恢复

win平台 UtilSession 失败: Prerequisite check “CheckActiveFilesAndExecutables” failed. 处理

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：win平台 UtilSession 失败: Prerequisite check “CheckActiveFilesAndExecutables” failed. 处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

win平台打patch,提示UtilSession 失败: Prerequisite check “CheckActiveFilesAndExecutables” failed.

C:\Users\Administrator\Desktop\nmgOracle补丁\34474433>E:\app\Administrator\produ
ct\11.2.0\dbhome_1\OPatch\opatch apply ./
Oracle 临时补丁程序安装程序版本 11.2.0.3.40
版权所有 (c) 2023, Oracle Corporation。保留所有权利。


Oracle 主目录       ：E:\app\ADMINI~1\product\112~1.0\dbhome_1
主产品清单：C:\Program Files\Oracle\Inventory
   来自           ：
OPatch 版本    ：11.2.0.3.40
OUI 版本       ：11.2.0.4.0
日志文件位置：E:\app\ADMINI~1\product\112~1.0\dbhome_1\cfgtoollogs\opatch\opatch
2023-04-20_18-05-20下午_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:

Following active files/executables/libs are used by ORACLE_HOME :E:\app\ADMINI~1
\product\112~1.0\dbhome_1
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraxml11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orahasgen11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraocr11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraocrb11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraocrutl11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oran11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orantcp11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oranl11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orancrypt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orapls11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orageneric11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraclient11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oracommon11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\rdbms\mesg\diaus.msb
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraplc11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraplp11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orannzsbb11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orazt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraztkg11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraldapclnt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oracore11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oracell11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraasmclnt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orannzsbb11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orazt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraztkg11.dll

UtilSession 失败: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: E:\app\ADMINI~1\product\112~1.0\dbhome_1\cfgtoollogs\opatch\o
patch2023-04-20_18-05-20下午_1.log

OPatch failed with error code = 73

通过tasklist命令找出来占用的进程名称和进程号

E:\app\Administrator\product\11.2.0\dbhome_1\OPatch>tasklist /M ora*

映像名称                       PID 模块
========================= ======== ============================================
esfdaemon.exe                 1296 oracleclientsvc.dll, oracleengine.dll,
                                   oracledatasource.dll, oraclecore.dll,
                                   OraClient11.Dll, oracore11.dll,
                                   oranls11.dll, oraunls11.dll, orauts.dll,
                                   oracommon11.dll, orasnls11.dll,
                                   orageneric11.dll, oraxml11.dll, oran11.dll,
                                   oranl11.dll, orantcp11.dll, oranldap11.dll,
                                   orannzsbb11.dll, oraldapclnt11.dll,
                                   orancrypt11.dll, orazt11.dll, oranro11.dll,
                                   oranhost11.dll, orancds11.dll,
                                   orantns11.dll, oraztkg11.dll, orapls11.dll,
                                   oraslax11.dll, oravsn11.dll, oraplp11.dll,
                                   orahasgen11.dll, oraocr11.dll,
                                   oraocrb11.dll, oraasmclnt11.dll,
                                   oracell11.dll, oraocrutl11.dll,
                                   orasql11.dll, OraPlc11.Dll, oranbeq11.dll
plsqldev.exe                 29840 OraClient11.Dll, oracore11.dll,
                                   oranls11.dll, oraunls11.dll, orauts.dll,
                                   oracommon11.dll, orasnls11.dll,
                                   orageneric11.dll, oraxml11.dll, oran11.dll,
                                   oranl11.dll, orantcp11.dll, oranldap11.dll,
                                   orannzsbb11.dll, oraldapclnt11.dll,
                                   orancrypt11.dll, orazt11.dll, oranro11.dll,
                                   oranhost11.dll, orancds11.dll,
                                   orantns11.dll, oraztkg11.dll, orapls11.dll,
                                   oraslax11.dll, oravsn11.dll, oraplp11.dll,
                                   orahasgen11.dll, oraocr11.dll,
                                   oraocrb11.dll, oraasmclnt11.dll,
                                   oracell11.dll, oraocrutl11.dll,
                                   orasql11.dll, OraPlc11.Dll

可以在任务管理器中直接把相关进程kill掉，也可以通过taskkill /F /im 进程名方式杀掉进程,然后再次检查占用情况,无任何进程占用的情况下,就可以直接打patch

E:\app\Administrator\product\11.2.0\dbhome_1\OPatch>tasklist /M ora*
信息: 没有运行的任务匹配指定标准。

Oracle Recovery Tools快速恢复断电引起的无法正常启动数据库(ORA-01555,MISSING000等问题）

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：Oracle Recovery Tools快速恢复断电引起的无法正常启动数据库(ORA-01555,MISSING000等问题）

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

由于异常断电,数据库启动报错ORA-01113和ORA-01110，ORA-00322和ORA-00312以及ORA-00314和ORA-00312错误

Mon Apr 17 09:35:04 2023
ALTER DATABASE OPEN
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_10192.trc:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1113 signalled during: ALTER DATABASE OPEN...
Mon Apr 17 09:35:05 2023
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_9788.trc:
ORA-00322: 日志 6 (用于线程 1) 不是最新副本
ORA-00312: 联机日志 6 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO06.LOG'
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_9788.trc:
ORA-00314: 日志 7 (用于线程 1) 要求的 sequence# 15257 与 15248 不匹配
ORA-00312: 联机日志 7 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO07.LOG'

客户那边技术尝试强制拉库之后,报ORA-00704、ORA-01555等错误,数据库启动失败

Mon Apr 17 10:10:49 2023
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 3159276957
Resetting resetlogs activation ID 1558785994 (0x5ce92fca)
Mon Apr 17 10:11:11 2023
Setting recovery target incarnation to 3
Mon Apr 17 10:11:11 2023
Assigning activation ID 1662170916 (0x6312b724)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Mon Apr 17 10:11:11 2023
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Apr 17 10:11:12 2023
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.bc4ebda4):
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9324.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号 10 (名称为 "_SYSSMU10_3905543278$") 过小
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9324.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号 10 (名称为 "_SYSSMU10_3905543278$") 过小
Error 704 happened during db open, shutting down database
USER (ospid: 9324): terminating the instance due to error 704
Instance terminated by USER, pid = 9324
ORA-1092 signalled during: alter database open resetlogs...

然后尝试一系列操作,比如重建ctl,再次拉库等等,均未绕过该错误.对于ORA-704 ORA-01555错误,使用开发的小工具Oracle Recovery Tools可以一键式解决,参考处理过的类似文章:Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障,不过最近发布的版本中增加了直接修改内存SCN功能,可以更加快速的解决给问题
20230417161757

修复之后,数据库open成功

SQL> alter database open;

数据库已更改。

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile='d:/pfile.txt' mount;
ORACLE 例程已经启动。

Total System Global Area 1.7170E+10 bytes
Fixed Size                  2262088 bytes
Variable Size            1.3153E+10 bytes
Database Buffers         3959422976 bytes
Redo Buffers               54947840 bytes
数据库装载完毕。
SQL> select name from v$datafile where name like '%MISSING%';

NAME
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00012
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00013

已选择2行。

很不幸技术在尝试重建ctl的恢复的过程中遗漏了两个数据库文件,通过Oracle Recovery Tools再次进行恢复
20230417230141

在sqlplus中恢复这两个文件成功

SQL> alter database rename file 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00012' 
   2 to 'D:\oradata\XIFENFEI_04.DBF';

数据库已更改。

SQL> alter database rename file 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00013' 
   2 to 'D:\oradata\XIFENFEI_5.DBF';

数据库已更改。


SQL> alter database datafile 12,13 online;

数据库已更改。

SQL>
SQL>
SQL> alter database open ;
alter database open 
*
第 1 行出现错误:
ORA-01113: 文件 12 需要介质恢复
ORA-01110: 数据文件 12: 'D:\ORADATA\XIFENFEI_04.DBF'


SQL> recover datafile 12;
完成介质恢复。
SQL> recover datafile 13;
完成介质恢复。
SQL> alter database open;

数据库已更改。

至此数据库基本上恢复完成,剩余工作就是逻辑导出导入.
软件下载:OraRecovery工具
使用说明:Oracle_Recovery_Tools说明

login trigger导致ORA-16191问题

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：login trigger导致ORA-16191问题

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

主库突然报ORA-16191错，主库无法传输日志到备库

at Apr 08 19:56:26 2023
Thread 1 advanced to log sequence 32548 (LGWR switch)
  Current log# 1 seq# 32548 mem# 0: /u01/app/oracle/oradata/orcl/redo01_a.rdo
  Current log# 1 seq# 32548 mem# 1: /oracle/fast_recovery_area/orcl/redo01_b.rdo
Sat Apr 08 19:56:26 2023
Archived Log entry 61796 added for thread 1 sequence 32547 ID 0x5fc1b26f dest 1:
Sat Apr 08 19:56:27 2023
LNS: Standby redo logfile selected for thread 1 sequence 32548 for destination LOG_ARCHIVE_DEST_2
Sat Apr 08 20:21:29 2023
NSA:  Error 3135 archiving log 1 to 'orcldg'
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_nsa2_26205.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 1 thread 1 sequence 32548 (3135)
Sat Apr 08 20:26:26 2023
Thread 1 advanced to log sequence 32549 (LGWR switch)
  Current log# 2 seq# 32549 mem# 0: /u01/app/oracle/oradata/orcl/redo02_a.rdo
  Current log# 2 seq# 32549 mem# 1: /oracle/fast_recovery_area/orcl/redo02_b.rdo
Sat Apr 08 20:26:26 2023
Archived Log entry 61798 added for thread 1 sequence 32548 ID 0x5fc1b26f dest 1:
Sat Apr 08 20:26:55 2023
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------
PING[ARC2]: Heartbeat failed to connect to standby 'orcldg'. Error is 16191.
Sat Apr 08 20:27:57 2023
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------
PING[ARC2]: Heartbeat failed to connect to standby 'orcldg'. Error is 16191.

备库报错

Sat Apr 08 19:29:09 2023
Media Recovery Waiting for thread 1 sequence 32548 (in transit)
Recovery of Online Redo Log: Thread 1 Group 12 Seq 32548 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl/std_redo12.log
Sat Apr 08 21:52:19 2023
RFS[9]: Possible network disconnect with primary database

检查主备库remote_login_passwordfile参数,都是为EXCLUSIVE,确认没有问题

--备库
SQL> show parameter pass;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

--主库
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> show parameter pass;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

检查主备库密码文件,证明主备库密码文件一致,也不是密码文件引起不同步

--主库
[oracle@oracle1 trace]$ cd $ORACLE_HOME/dbs
[oracle@oracle1 dbs]$ echo $ORACLE_SID
orcl
[oracle@oracle1 dbs]$ ls -ltr orapw*
-rw-r-----. 1 oracle oinstall 1536 Sep 16  2022 orapworcl
[oracle@oracle1 dbs]$ md5sum orapworcl 
d27c234b5131b4d2e13b1eeb4388f0eb  orapworcl

--备库
[oracle@oracle2 trace]$ cd $ORACLE_HOME/dbs
[oracle@oracle2 dbs]$ echo $ORACLE_SID
orcl
[oracle@oracle2 dbs]$ ls -l orapw*
-rw-r----- 1 oracle oinstall 1536 Sep 16  2022 orapworcl
[oracle@oracle2 dbs]$ md5sum orapworcl 
d27c234b5131b4d2e13b1eeb4388f0eb  orapworcl

查看数据库有logon触发器

SQL> select owner,trigger_name from dba_triggers where triggering_event LIKE '%LOGON%';

OWNER                          TRIGGER_NAME
------------------------------ ------------------------------
DBTOOLS                        TRIGGER_LOGINLIMIT

根据Error 1017 / ORA-16191 In Standby Alertlog (Doc ID 2225190.1)中描述logon触发器可能导致dg传输日志失败.
trigger-ORA-16191

V$Wait_Chains定位阻塞源头

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：V$Wait_Chains定位阻塞源头

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

从11.1开始,查询会话阻塞,V$Wait_Chains是一个非常好的视图,通过他可以快速找到阻塞源头.进行一个简单的测试
测试表create table t1 as select * from dba_objects;
会话1 

SQL> delete from t1 where object_id>100 and object_id<10000;

9712 rows deleted.

会话2

SQL> delete from t1 where object_id<200;
---hang住

会话3

SQL> delete from t1 where object_id>88 and object_id<150;
--hang住

会话4进行查询分析

SQL> set lines 150
SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30) FROM v$wait_chains; 

  CHAIN_ID NUM_WAITERS IN_WAIT_SECS OSID                      BLOCKER_OSID              SUBSTR(WAIT_EVENT_TEXT,1,30)
---------- ----------- ------------ ------------------------- ------------------------- ----------------------------------
         1           0          438 17072                     17005                     enq: TX - row lock contention
         1           1          483 17005                     16930                     enq: TX - row lock contention
         1           2          505 16930                                               SQL*Net message from client

SQL> set pages 1000
SQL>  set lines 120
SQL>  set heading off
SQL>  column w_proc format a50 tru
SQL>  column instance format a20 tru
SQL>  column inst format a28 tru
SQL>  column wait_event format a50 tru
SQL>  column p1 format a16 tru
SQL>  column p2 format a16 tru
SQL>  column p3 format a15 tru
SQL>  column Seconds format a50 tru
SQL>  column sincelw format a50 tru
SQL>  column blocker_proc format a50 tru
SQL>  column waiters format a50 tru
SQL>  column chain_signature format a100 wra
SQL>  column blocker_chain format a100 wra
SQL>  
SQL>  SELECT *
  2   FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
  3   'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)||
  4   ' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
  5   'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
  6   'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
  7   'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
  8   '<none>',blocker_chain_id) blocker_chain
  9   FROM v$wait_chains wc,
 10   v$instance i
 11   WHERE wc.instance = i.instance_number (+)
 12   AND ( num_waiters > 0
 13   OR ( blocker_osid IS NOT NULL
 14   AND in_wait_secs > 10 ) )
 15   ORDER BY chain_id,
 16   num_waiters DESC)
 17   WHERE ROWNUM < 101;

Current Process: 16930                             SID orcl             INST #: 1
Blocking Process: <none> from Instance             Number of waiters: 2
Wait Event: SQL*Net message from client            P1: 1650815232   P2: 1            P3: 0
Seconds in Wait: 140                               Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

Current Process: 17005                             SID orcl             INST #: 1
Blocking Process: 16930 from Instance 1            Number of waiters: 1
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 524290       P3: 8984
Seconds in Wait: 119                               Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

Current Process: 17072                             SID orcl             INST #: 1
Blocking Process: 17005 from Instance 1            Number of waiters: 0
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 458768       P3: 8720
Seconds in Wait: 74                                Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

SQL> set pages 1000
SQL> set lines 120
SQL> set heading off
SQL> column w_proc format a50 tru
SQL> column instance format a20 tru
SQL> column inst format a28 tru
SQL> column wait_event format a50 tru
SQL> column p1 format a16 tru
SQL> column p2 format a16 tru
SQL> column p3 format a15 tru
SQL> column Seconds format a50 tru
SQL> column sincelw format a50 tru
SQL> column blocker_proc format a50 tru
SQL> column fblocker_proc format a50 tru
SQL> column waiters format a50 tru
SQL> column chain_signature format a100 wra
SQL> column blocker_chain format a100 wra
SQL> SELECT *
  2  FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
  3   'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)||
  4   ' from Instance '||blocker_instance BLOCKER_PROC,
  5   'Number of waiters: '||num_waiters waiters,
  6   'Final Blocking Process: '||decode(p.spid,null,'<none>',
  7   p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC,
  8   'Program: '||p.program image,
  9   'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
 10   'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
 11   'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
 12   '<none>',blocker_chain_id) blocker_chain
 13  FROM v$wait_chains wc,
 14   gv$session s,
  15  gv$session bs,
 16   gv$instance i,
 17   gv$process p
 18  WHERE wc.instance = i.instance_number (+)
 19   AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
 20   and wc.sess_serial# = s.serial# (+))
 21   AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
 22   AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
 23   AND ( num_waiters > 0
 24   OR ( blocker_osid IS NOT NULL
 25   AND in_wait_secs > 10 ) )
 26  ORDER BY chain_id,
 27   num_waiters DESC)
 28  WHERE ROWNUM < 101;

Current Process: 16930                             SID orcl             INST #: 1
Blocking Process: <none> from Instance             Number of waiters: 2
Final Blocking Process: <none> from Instance       Program:
Wait Event: SQL*Net message from client            P1: 1650815232   P2: 1            P3: 0
Seconds in Wait: 177                               Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

Current Process: 17005                             SID orcl             INST #: 1
Blocking Process: 16930 from Instance 1            Number of waiters: 1
Final Blocking Process: 16930 from Instance 1      Program: oracle@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3)
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 524290       P3: 8984
Seconds in Wait: 155                               Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

Current Process: 17072                             SID orcl             INST #: 1
Blocking Process: 17005 from Instance 1            Number of waiters: 0
Final Blocking Process: 16930 from Instance 1      Program: oracle@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3)
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 458768       P3: 8720
Seconds in Wait: 110                               Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

SQL> col SES for a15
SQL> set linesize 150
SQL> WITH BLOCKED AS
  2   (SELECT *
  3    FROM (SELECT INSTANCE,
  4                   SID,
  5                   SESS_SERIAL#,
  6                   BLOCKER_INSTANCE,
  7                   BLOCKER_SID,
  8                   BLOCKER_SESS_SERIAL#,
  9                   LEVEL LV,
 10                   NUM_WAITERS,
 11                   BLOCKER_CHAIN_ID
 12              FROM V$WAIT_CHAINS
 13            CONNECT BY PRIOR SID = BLOCKER_SID
 14                   AND PRIOR SESS_SERIAL# = BLOCKER_SESS_SERIAL#
 15                   AND PRIOR INSTANCE = BLOCKER_INSTANCE
 16             START WITH BLOCKER_IS_VALID = 'FALSE')
 17     WHERE NUM_WAITERS > 0
 18        OR BLOCKER_SID IS NOT NULL)
 19  SELECT INSTANCE,
 20         LPAD(' ', 2 * (LV - 1)) || B.SID SES,
 21         B.SESS_SERIAL#,
 22         B.BLOCKER_INSTANCE,
 23         B.BLOCKER_SID,
 24         B.BLOCKER_SESS_SERIAL#
 25    FROM BLOCKED B ;

  INSTANCE SES             SESS_SERIAL# BLOCKER_INSTANCE BLOCKER_SID BLOCKER_SESS_SERIAL#
---------- --------------- ------------ ---------------- ----------- --------------------
         1 42                      1819
         1   32                   52659                1          42                 1819
         1     39                 39865                1          32                52659

能够快速的定位到阻塞会话的源头,以及阻塞的级联关系

update user$报ORA-01031错误

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：update user$报ORA-01031错误

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在oracle的有些版本中,为了安全不允许用户直接update USER$表

[oracle@dbserver1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 11 12:51:55 2023
Version 19.16.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> UPDATE USER$ SET NAME='XFF' WHERE NAME='XIFENFEI';
UPDATE USER$ SET NAME='XFF' WHERE NAME='XIFENFEI'
       *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> show user; 
USER is "SYS"
SQL> create table t_user$ as select * from user$;

Table created.

SQL> UPDATE USER$ SET NAME='XFF' WHERE NAME='XIFENFEI';
UPDATE USER$ SET NAME='XFF' WHERE NAME='XIFENFEI'
       *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> create user test identified by oracle;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/oracle
Connected.

SQL> update sys.user$ SET NAME='XFF' WHERE NAME='XIFENFEI';
update sys.user$ SET NAME='XFF' WHERE NAME='XIFENFEI'
           *
ERROR at line 1:
ORA-01031: insufficient privileges

官方说明:CANNOT UPDATE SYS.USER$ AS SYS AFTER APPLYING OCTOBER 2020 PSU/RU, ORA-01031 IS OBTAINED (Doc ID 2746319.1)

删除ora.asmgroup资源offline记录

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：删除ora.asmgroup资源offline记录

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

采用了fix asm之后,查看集群状态的时候会有一个ora.asmgroup相关是offline状态,可以通过srvctl modify asm -count 2命令强制把asm count设置为2从而就不会有offline的资源存在

[grid@dbserver1 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.chad
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.net1.network
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.ons
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.proxy_advm
               OFFLINE OFFLINE      dbserver1                STABLE
               OFFLINE OFFLINE      dbserver2                STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.ASMNET2LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.FRA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.SYSDG.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                Started,STABLE
      2        ONLINE  ONLINE       dbserver2                Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet2.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.dbserver1.vip
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.dbserver2.vip
      1        ONLINE  ONLINE       dbserver2                STABLE
ora.xff.db
      1        ONLINE  ONLINE       dbserver1                Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
      2        ONLINE  ONLINE       dbserver2                Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       dbserver1                STABLE
--------------------------------------------------------------------------------
[grid@dbserver1 ~]$ srvctl modify asm -count 2
[grid@dbserver1 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.chad
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.net1.network
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.ons
               ONLINE  ONLINE       dbserver1                STABLE
               ONLINE  ONLINE       dbserver2                STABLE
ora.proxy_advm
               OFFLINE OFFLINE      dbserver1                STABLE
               OFFLINE OFFLINE      dbserver2                STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.ASMNET2LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.FRA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.SYSDG.dg(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                Started,STABLE
      2        ONLINE  ONLINE       dbserver2                Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.asmnet2.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       dbserver1                STABLE
      2        ONLINE  ONLINE       dbserver2                STABLE
ora.cvu
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.dbserver1.vip
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.dbserver2.vip
      1        ONLINE  ONLINE       dbserver2                STABLE
ora.xff.db
      1        ONLINE  ONLINE       dbserver1                Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
      2        ONLINE  ONLINE       dbserver2                Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       dbserver1                STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       dbserver1                STABLE
--------------------------------------------------------------------------------
[grid@dbserver1 ~]$