Author Archives: 惜分飞

resetlogs强制拉库失败并使用备份system文件还原数据库故障处理

Posted on by

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

标题：resetlogs强制拉库失败并使用备份system文件还原数据库故障处理

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

接手一个库,在open的过程中遭遇到ORA-600 2662错误

Sun May 26 10:15:54 2024
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 84303583
Clearing online redo logfile 1 /data/OracleData/xff/redo01.log
Clearing online log 1 of thread 1 sequence number 8330
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /data/OracleData/xff/redo02.log
Clearing online log 2 of thread 1 sequence number 8327
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /data/OracleData/xff/redo03.log
Clearing online log 3 of thread 1 sequence number 8329
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 /data/OracleData/xff/redo04.log
Clearing online log 4 of thread 1 sequence number 8328
Clearing online redo logfile 4 complete
Resetting resetlogs activation ID 1431370398 (0x5550fa9e)
Online log /data/OracleData/xff/redo01.log: Thread 1 Group 1 was previously cleared
Online log /data/OracleData/xff/redo02.log: Thread 1 Group 2 was previously cleared
Online log /data/OracleData/xff/redo03.log: Thread 1 Group 3 was previously cleared
Online log /data/OracleData/xff/redo04.log: Thread 1 Group 4 was previously cleared
Sun May 26 10:15:59 2024
Setting recovery target incarnation to 3
Sun May 26 10:15:59 2024
Read of datafile '/data/OracleData/xff/temp01.dbf' (fno 201) header failed with ORA-01200
Rereading datafile 201 header failed with ORA-01200
Errors in file /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_dbw0_1563.trc:
ORA-01186: file 201 failed verification tests
ORA-01122: database file 201 failed verification check
ORA-01110: data file 201: '/data/OracleData/xff/temp01.dbf'
ORA-01200: actual file size of 3711 is smaller than correct size of 3712 blocks
File 201 not verified due to error ORA-01122
Sun May 26 10:15:59 2024
Assigning activation ID 1509069065 (0x59f29109)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /data/OracleData/xff/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun May 26 10:15:59 2024
SMON: enabling cache recovery
Errors in file /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_1590.trc  (incident=163897):
ORA-00600: internal error code, arguments: [2662], [0], [84303590], [0], [84314659], [12583040] 
Incident details in:/data/u01/app/oracle/diag/rdbms/xff/xff/incident/incdir_163897/xff_ora_1590_i163897.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 /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_1590.trc:
ORA-00600: internal error code, arguments: [2662], [0], [84303590], [0], [84314659], [12583040] 
Errors in file /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_1590.trc:
ORA-00600: internal error code, arguments: [2662], [0], [84303590], [0], [84314659], [12583040] 
Error 600 happened during db open, shutting down database
USER (ospid: 1590): terminating the instance due to error 600

然后客户使用备份的system01.dbf文件替换了被resetlogs之后文件,导致数据库后续操作无法继续

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf'

这个问题比较简单,通过bbed或者Oracle Recovery Tools修改文件头相关信息,然后open数据库成功
重建控制文件丢失数据文件导致悲剧
Oracle Recovery Tools快速恢复ORA-19909

SQL> recover datafile 1;
Media recovery complete.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

但是由于system文件有大量坏块导致数据库无法正常登录和导出

[oracle@et-dbserver ~]$ exp "'/ as sysdba'" owner=USERNAME  file=/tmp/2user.dmp log=/tmp/2user.log 

Export: Release 11.2.0.4.0 - Production on Sun May 26 13:00:50 2024

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

EXP-00056: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 86500)
ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf'
Username: / as sysdba

EXP-00056: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 86500)
ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf'
Username:
Password:

EXP-00056: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 86500)
ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf'
ORA-01017: invalid username/password; logon denied
EXP-00005: all allowable logon attempts failed
EXP-00000: Export terminated unsuccessfully

通过dbv检查system数据文件

DBVERIFY: Release 11.2.0.4.0 - Production on Sun May 26 12:33:28 2024

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


DBVERIFY - Verification starting : FILE = /data/OracleData/xff/system01.dbf
Page 1044 is influx - most likely media corrupt
Corrupt block relative dba: 0x00400414 (file 1, block 1044)
Fractured block found during dbv: 
Data in bad block:
 type: 0 format: 2 rdba: 0x00400414
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1d7f550b
 check value in block header: 0xa354
 computed block checksum: 0x6830

Page 1103 is marked corrupt
Corrupt block relative dba: 0x0040044f (file 1, block 1103)
Bad header found during dbv: 
Data in bad block:
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x508f.5f74492e seq: 0x53 flg: 0x0c
 spare1: 0xc spare2: 0xa6 spare3: 0xc757
 consistency value in tail: 0x00000001
 check value in block header: 0x8925
 computed block checksum: 0x5d3b

Page 1143 is marked corrupt
Corrupt block relative dba: 0x00400477 (file 1, block 1143)
Bad header found during dbv: 
Data in bad block:
 type: 0 format: 0 rdba: 0x00000001
 last change scn: 0x65c4.52eb202e seq: 0x28 flg: 0x0e
 spare1: 0xe spare2: 0xe2 spare3: 0xfa46
 consistency value in tail: 0x00000001
 check value in block header: 0x6405
 computed block checksum: 0x28b1

………………

Page 124805 is influx - most likely media corrupt
Corrupt block relative dba: 0x0041e785 (file 1, block 124805)
Fractured block found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x0041e785
 last change scn: 0x0000.0434fc6c seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1991255b
 check value in block header: 0x6386
 computed block checksum: 0x1384


DBVERIFY - Verification complete

Total Pages Examined         : 130560
Total Pages Processed (Data) : 95634
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 14949
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1774
Total Pages Processed (Seg)  : 1669
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 16251
Total Pages Marked Corrupt   : 283
Total Pages Influx           : 149
Total Pages Encrypted        : 0
Highest block SCN            : 84314727 (0.84314727)

对于这样问题,通过Oracle Recovery Tools实战批量坏块修复,实现数据库可以完美导出数据

truncate IDL_UB1$导致数据库open hang

Posted on by

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

标题：truncate IDL_UB1$导致数据库open hang

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

在一次数据库恢复中,发现IDL_UB1$表被truncate,然后数据库在open过程中会hang住,而且不报任何错误,这里通过试验进行重现.对于这类问题,以前有过类似处理测试：truncate IDL_UB1$恢复
试验数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select object_name,object_type,owner,object_id,data_object_id,
2   last_ddl_time from dba_objects where object_name like 'IDL_UB%';

OBJECT_NAME   OBJECT_TYPE         OWNER     OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM
------------- ------------------- -------- ---------- -------------- ------------
IDL_UB1$      TABLE               SYS             225            225 24-AUG-13
IDL_UB2$      TABLE               SYS             227            227 24-AUG-13

truncate IDL_UB1$表

SQL> truncate table IDL_UB1$;
truncate table IDL_UB1$
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 1090
Session ID: 125 Serial number: 5

alert日志报错信息

Sat May 25 20:23:11 2024
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x18] [PC:0x98DA2B7, hshuid()+273] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_1090.trc  (incident=60156):
ORA-07445: exception encountered: core dump [hshuid()+273] [SIGSEGV] [ADDR:0x18] [PC:0x98DA2B7] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_60156/xifenfei_ora_1090_i60156.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

检查确认IDL_UB1$表被truncate成功

SQL> select count(1) from IDL_UB1$;

  COUNT(1)
----------
         0

SQL> select object_id,data_object_id,last_ddl_time from dba_objects where object_name like 'IDL_UB%';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------

       225          87377

重启数据库hang住
open_hang

新会话查询信息
数据库open状态,但是无法执行ddl,无法执行exp/expdp等导出操作

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 25 20:29:01 2024

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> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

--操作hang住
SQL> create table t1 as select * from dba_objects;

--exp操作hang住
[oracle@xifenfei ~]$ exp "'/ as sysdba'" tables=obj$

Export: Release 11.2.0.4.0 - Production on Sat May 25 20:39:03 2024

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

--expdp操作hang住
[oracle@xifenfei ~]$ expdp "'/ as sysdba'" tables=obj$

Export: Release 11.2.0.4.0 - Production on Sat May 25 20:39:35 2024

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

数据库在open 被hang的过程中等待事件信息
一直卡在db file sequential read等待事件上，而且等待的block信息一直不变，读取对象为IDL_UB2$

SQL> select username,program,event,P1,P2,P3 from v$session where wait_class#<>6 and username='SYS'
  2  AND SID NOT IN(SELECT DISTINCT SID FROM V$MYSTAT);

USERNAME                       PROGRAM
------------------------------ ------------------------------------------------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
        P2         P3
---------- ----------
SYS                            sqlplus@xifenfei (TNS V1-V3)
db file sequential read                                                   1
      1531          1


SQL> /

USERNAME                       PROGRAM
------------------------------ ------------------------------------------------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
        P2         P3
---------- ----------
SYS                            sqlplus@xifenfei (TNS V1-V3)
db file sequential read                                                   1
      1531          1


SQL> /

USERNAME                       PROGRAM
------------------------------ ------------------------------------------------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
        P2         P3
---------- ----------
SYS                            sqlplus@xifenfei (TNS V1-V3)
db file sequential read                                                   1
      1531          1

--读取对象
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 1
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 1
Enter value for block_id: 1531
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 1531 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME
------------------ ------------------------------ ------------------------------
SYS
IDL_UB2$
TABLE              SYSTEM

trace数据库启动过程发现卡在select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece# 语句部分.

PARSING IN CURSOR #140737276517064 len=132 dep=2 uid=0 oct=3 lid=0 tim=1716640552642714 hv=4260389146 ad='80963c78' sqlid='cvn54b7yz0s8u'
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT
PARSE #140737276517064:c=17,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1716640552642713
BINDS #140737276517064:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7ffff35f73a0  bln=22  avl=03  flg=05
  value=1310
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7ffff35f7370  bln=24  avl=01  flg=05
  value=0
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7ffff35f7340  bln=24  avl=06  flg=05
  value=184549376
EXEC #140737276517064:c=72,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1716640552642819
FETCH #140737276517064:c=4,e=5,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1716640552642832
STAT #140737276517064 id=1 cnt=0 pid=0 pos=1 obj=225 op='TABLE ACCESS BY INDEX ROWID IDL_UB1$ (cr=1 pr=0 pw=0 time=5 us cost=3 size=44 card=2)'
STAT #140737276517064 id=2 cnt=0 pid=1 pos=1 obj=236 op='INDEX RANGE SCAN I_IDL_UB11 (cr=1 pr=0 pw=0 time=4 us cost=2 size=0 card=2)'
CLOSE #140737276517064:c=2,e=2,dep=2,type=0,tim=1716640552642858
=====================
PARSING IN CURSOR #140737276517064 len=135 dep=2 uid=0 oct=3 lid=0 tim=1716640552642876 hv=1115215392 ad='808c4c68' sqlid='c6awqs517jpj0'
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT
PARSE #140737276517064:c=12,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1319326155,tim=1716640552642875
BINDS #140737276517064:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7ffff35f73a0  bln=22  avl=03  flg=05
  value=1310
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7ffff35f7370  bln=24  avl=01  flg=05
  value=0
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7ffff35f7340  bln=24  avl=06  flg=05
  value=184549376
EXEC #140737276517064:c=73,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1319326155,tim=1716640552642977
WAIT #140737276517064: nam='db file sequential read' ela= 10 file#=1 block#=1610 blocks=1 obj#=237 tim=1716640552643001
WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1522 blocks=1 obj#=226 tim=1716640552643027
WAIT #140737276517064: nam='db file sequential read' ela= 8 file#=1 block#=1521 blocks=1 obj#=226 tim=1716640552643051
FETCH #140737276517064:c=81,e=80,p=3,cr=5,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643065
WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1524 blocks=1 obj#=226 tim=1716640552643124
WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=1523 blocks=1 obj#=226 tim=1716640552643143
FETCH #140737276517064:c=40,e=40,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643150
WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1526 blocks=1 obj#=226 tim=1716640552643216
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1525 blocks=1 obj#=226 tim=1716640552643232
FETCH #140737276517064:c=36,e=36,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643239
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8200 blocks=1 obj#=226 tim=1716640552643405
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1527 blocks=1 obj#=226 tim=1716640552643420
FETCH #140737276517064:c=31,e=31,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643427
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8202 blocks=1 obj#=226 tim=1716640552643583
WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=8201 blocks=1 obj#=226 tim=1716640552643600
FETCH #140737276517064:c=34,e=35,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643608
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8204 blocks=1 obj#=226 tim=1716640552643766
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8203 blocks=1 obj#=226 tim=1716640552643785
FETCH #140737276517064:c=35,e=35,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643792
WAIT #140737276517064: nam='db file sequential read' ela= 8 file#=1 block#=8206 blocks=1 obj#=226 tim=1716640552644038
WAIT #140737276517064: nam='db file sequential read' ela= 8 file#=1 block#=8205 blocks=1 obj#=226 tim=1716640552644062
FETCH #140737276517064:c=49,e=49,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552644070
WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=8208 blocks=1 obj#=226 tim=1716640552644296
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8207 blocks=1 obj#=226 tim=1716640552644313
FETCH #140737276517064:c=36,e=35,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552644320
WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=8209 blocks=1 obj#=226 tim=1716640552644479
FETCH #140737276517064:c=20,e=20,p=1,cr=3,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552644487
FETCH #140737276517064:c=2,e=1,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=1319326155,tim=1716640552644545
STAT #140737276517064 id=1 cnt=9 pid=0 pos=1 obj=226 op='TABLE ACCESS BY INDEX ROWID IDL_CHAR$ (cr=20 pr=10 pw=0 time=53 us cost=3 size=21 card=1)'
STAT #140737276517064 id=2 cnt=9 pid=1 pos=1 obj=237 op='INDEX RANGE SCAN I_IDL_CHAR1 (cr=11 pr=1 pw=0 time=46 us cost=2 size=0 card=1)'
CLOSE #140737276517064:c=3,e=3,dep=2,type=0,tim=1716640552644579
=====================
PARSING IN CURSOR #140737276517064 len=132 dep=2 uid=0 oct=3 lid=0 tim=1716640552644609 hv=1684122946 ad='80924938' sqlid='39m4sx9k63ba2'
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT
PARSE #140737276517064:c=17,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2317816222,tim=1716640552644609
BINDS #140737276517064:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7ffff35f73a0  bln=22  avl=03  flg=05
  value=1310
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7ffff35f7370  bln=24  avl=01  flg=05
  value=0
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7ffff35f7340  bln=24  avl=06  flg=05
  value=184549376
EXEC #140737276517064:c=92,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2317816222,tim=1716640552644735
WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1618 blocks=1 obj#=238 tim=1716640552644761
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1530 blocks=1 obj#=227 tim=1716640552644778
WAIT #140737276517064: nam='db file sequential read' ela= 10 file#=1 block#=1529 blocks=1 obj#=227 tim=1716640552644799
FETCH #140737276517064:c=60,e=60,p=3,cr=5,cu=0,mis=0,r=1,dep=2,og=4,plh=2317816222,tim=1716640552644807
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1532 blocks=1 obj#=227 tim=1716640552644924
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1531 blocks=1 obj#=227 tim=1716640552644940
FETCH #140737276517064:c=33,e=33,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=2317816222,tim=1716640552644947

PASSWORD_ROLLOVER_TIME—实现新老密码短期共存

Posted on by

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

标题：PASSWORD_ROLLOVER_TIME—实现新老密码短期共存

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

在oracle老版本中(特别是11g版本),你可能多少都遇到过应用修改密码,但是由于系统运行应用较多,在修改应用密码的过程中有业务忘记修改密码,从而导致业务无法正常使用,甚至由于密码延迟认证特性导致数据库hang住,对于这些问题,oracle 从19.12开始引入了PASSWORD_ROLLOVER_TIME,可以在这个profile限制时间内,新老密码都可以登录数据库,避免了上述问题.
在手上有的23ai的数据库中进行测试,创建一个测试用户,使用的是default profile,PASSWORD_ROLLOVER_TIME为0(没有启用)

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

SQL> create user xff identified by oracle;

User created.

SQL> grant dba to xff;

Grant succeeded.

SQL> select profile from dba_users where username='XFF';   

PROFILE
------------------------------
DEFAULT

SQL> select profile,limit from dba_profiles where resource_name='PASSWORD_ROLLOVER_TIME';

PROFILE                        LIMIT
------------------------------ ------------------------------
DEFAULT                        0
ORA_CIS_PROFILE                DEFAULT
ORA_STIG_PROFILE               DEFAULT

尝试修改密码,尝试登录(老密码无法登录成功)

[oracle@192 oradata]$ sqlplus xff/oracle@127.0.0.1/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 12:58:31 2024
Version 23.4.0.24.05

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


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> alter user xff identified by xifenfei;

User altered.

SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
[oracle@192 oradata]$ sqlplus xff/oracle@127.0.0.1/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 12:58:49 2024
Version 23.4.0.24.05

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

ERROR:
ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/


Enter user-name: 
ERROR:
ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/


Enter user-name: 
ERROR:
ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
Help: https://docs.oracle.com/error-help/db/sp2-0157/

修改PASSWORD_ROLLOVER_TIME和密码尝试登录(新老密码都可以登录)

[oracle@192 oradata]$ sqlplus xff/xifenfei@127.0.0.1/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 12:58:58 2024
Version 23.4.0.24.05

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

Last Successful login time: Fri May 24 2024 12:58:31 +00:00

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> alter profile default limit password_rollover_time  1/24;   ----修改值单位为天,最小1小时

Profile altered.

SQL> select profile,limit from dba_profiles where resource_name='PASSWORD_ROLLOVER_TIME';

PROFILE
--------------------------------------------------------------------------------
LIMIT
--------------------------------------------------------------------------------
DEFAULT
.0416                                 ----显示值也为天

ORA_CIS_PROFILE
DEFAULT

ORA_STIG_PROFILE
DEFAULT




SQL> alter user xff identified by orasos;

User altered.

SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
[oracle@192 oradata]$ sqlplus xff/xifenfei@127.0.0.1/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 13:02:49 2024
Version 23.4.0.24.05

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

Last Successful login time: Fri May 24 2024 12:58:58 +00:00

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
[oracle@192 oradata]$ sqlplus xff/orasos@127.0.0.1/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 13:02:56 2024
Version 23.4.0.24.05

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

Last Successful login time: Fri May 24 2024 13:02:49 +00:00

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL>

禁用该用户PASSWORD_ROLLOVER_TIME功能(强制禁止老用户登录,新用户依然可以登录)

[oracle@192 oradata]$ sqlplus xff/orasos@127.0.0.1/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 13:02:56 2024
Version 23.4.0.24.05

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

Last Successful login time: Fri May 24 2024 13:02:49 +00:00

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> ALTER USER xff   EXPIRE PASSWORD ROLLOVER PERIOD;

User altered.

SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
[oracle@192 oradata]$ sqlplus xff/xifenfei@127.0.0.1/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 13:04:50 2024
Version 23.4.0.24.05

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

ERROR:
ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/


Enter user-name: ^C^C
[oracle@192 oradata]$ sqlplus xff/orasos@127.0.0.1/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 24 13:04:56 2024
Version 23.4.0.24.05

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

Last Successful login time: Fri May 24 2024 13:02:56 +00:00

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL>

11.2.0.4最新psu-202404

Posted on by

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

标题：11.2.0.4最新psu-202404

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

最近发现11.2.0.4的版本在2024年4月份又更新了新的psu,证明在即将进入23ai的时代该版本还有万千的生命力

11.2.0.4 Database Patch Set Update

Release Date Version Download link Included in Windows Bundle
16-Apr-2024 11.2.0.4.240416 (Apr 2024) Database Patch Set Update (DB PSU) Patch 36222299 * Out of Error Correction
16-Jan-2024 ** * Out of Error Correction
17-Oct-2023 11.2.0.4.231017 (Oct 2023) Database Patch Set Update (DB PSU) Patch 35574075 * Out of Error Correction
18-Jul-2023 11.2.0.4.230718 (Jul 2023) Database Patch Set Update (DB PSU) Patch 35269283 * Out of Error Correction
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

11.2.0.4 Grid Infrastructure Patch Set Update

Release Date Version Download link
16-Apr-2024 11.2.0.4.240416 (Apr 2024) Grid Infrastructure Patch Set Update (GI PSU) * Patch 36360774
16-Jan-2024 **
17-Oct-2023 11.2.0.4.231017 (Oct 2023) Grid Infrastructure Patch Set Update (GI PSU) * Patch 35685688
18-Jul-2023 11.2.0.4.230718 (Jul 2023) Grid Infrastructure Patch Set Update (GI PSU) * Patch 35369867
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
16-Apr-2024 11.2.0.4.240416 (Apr 2024) OJVM Component Patch Set Update Patch 36222352 * Out of Error Correction
16-Jan-2024 11.2.0.4.240116 (Jan 2024) OJVM Component Patch Set Update Patch 35926733 * Out of Error Correction
17-Oct-2023 11.2.0.4.231017 (Oct 2023) OJVM Component Patch Set Update Patch 35685663 * Out of Error Correction
18-Jul-2023 11.2.0.4.230718 (Jul 2023) OJVM Component Patch Set Update Patch 35349843 * Out of Error Correction
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 PSU COMBO: 11.2.0.4 Database Patch Set Update and 11.2.0.4 OJVM Patch Set Update

Release Date Version Download Link
16-Apr-2024 Combo OJVM PSU 11.2.0.4.240416 and DB PSU 11.2.0.4.240416 * Patch 36517603
16-Jan-2024 **
17-Oct-2023 Combo OJVM PSU 11.2.0.4.231017 and DB PSU 11.2.0.4.231017 * Patch 35742488
18-Jul-2023 Combo OJVM PSU 11.2.0.4.230718 and DB PSU 11.2.0.4.230718 * Patch 35370078
18-Apr-2023 Combo OJVM PSU 11.2.0.4.230418 and DB PSU 11.2.0.4.230418 * Patch 35084468
17-Jan-2023 Combo OJVM PSU 11.2.0.4.230117 and DB PSU 11.2.0.4.230117 * Patch 34773608
18-Oct-2022 Combo OJVM PSU 11.2.0.4.221018 and DB PSU 11.2.0.4.221018 * Patch 34449295
19-Jul-2022 Combo OJVM PSU 11.2.0.4.220719 and DB PSU 11.2.0.4.220719 * Patch 34163705
19-Apr-2022 Combo OJVM PSU 11.2.0.4.220419 and DB PSU 11.2.0.4.220419 * Patch 33881679
18-Jan-2022 Combo OJVM PSU 11.2.0.4.220118 and DB PSU 11.2.0.4.220118 * Patch 33560092
19-Oct-2021 Combo OJVM PSU 11.2.0.4.211019 and DB PSU 11.2.0.4.211019 * Patch 33248641
20-Jul-2021 Combo OJVM PSU 11.2.0.4.210720 and DB PSU 11.2.0.4.210720 * Patch 32900228
20-Apr-2021 Combo OJVM PSU 11.2.0.4.201020 and DB PSU 11.2.0.4.210420 * Patch 32579111
19-Jan-2021 Combo OJVM PSU 11.2.0.4.201020 and DB PSU 11.2.0.4.210119 * Patch 32126939
20-Oct-2020 Combo OJVM PSU 11.2.0.4.201020 and DB PSU 11.2.0.4.201020 Patch 31720776
14-Jul-2020 Combo OJVM PSU 11.2.0.4.200714 and DB PSU 11.2.0.4.200714 Patch 31326405
14-Apr-2020 Combo OJVM PSU 11.2.0.4.200414 and DB PSU 11.2.0.4.200414 Patch 30783889
14-Jan-2020 Combo OJVM PSU 11.2.0.4.200114 and DB PSU 11.2.0.4.200114 Patch 30463718
15-Oct-2019 Combo OJVM PSU 11.2.0.4.191015 and DB PSU 11.2.0.4.191015 Patch 30133482
16-Jul-2019 Combo OJVM PSU 11.2.0.4.190716 and DB PSU 11.2.0.4.190716 Patch 29699294
16-Apr-2019 Combo OJVM PSU 11.2.0.4.190416 and DB PSU 11.2.0.4.190416 Patch 29252186
15-Jan-2019 Combo OJVM PSU 11.2.0.4.190115 and DB PSU 11.2.0.4.190115 Patch 28980129
16-Oct-2018 Combo OJVM PSU 11.2.0.4.181016 and DB PSU 11.2.0.4.181016 Patch 28689165
17-Jul-2018 Combo OJVM PSU 11.2.0.4.180717 and DB PSU 11.2.0.4.180717 Patch 28317183
17-Apr-2018 Combo OJVM PSU 11.2.0.4.180417 and DB PSU 11.2.0.4.180417 Patch 27726500
16-Jan-2018 Combo OJVM PSU 11.2.0.4.180116 and DB PSU 11.2.0.4.180116 Patch 27011017
17-Oct-2017 Combo OJVM PSU 11.2.0.4.171017 and DB PSU 11.2.0.4.171017 Patch 26636031
18-Jul-2017 Combo OJVM PSU 11.2.0.4.170718 and DB PSU 11.2.0.4.170718 Patch 26031209
18-Apr-2017 Combo OJVM PSU 11.2.0.4.170418 and DB PSU 11.2.0.4.170418 Patch 25440428
17-Jan-2017 Combo OJVM PSU 11.2.0.4.170117 and DB PSU 11.2.0.4.170117 Patch 24918033
18-Oct-2016 Combo OJVM PSU 11.2.0.4.161018 and DB PSU 11.2.0.4.161018 Patch 24436313
19-Jul-2016 Combo OJVM PSU 11.2.0.4.160719 and DB PSU 11.2.0.4.160719 Patch 23615392
19-Apr-2016 Combo OJVM PSU 11.2.0.4.160419 and DB PSU 11.2.0.4.160419 Patch 22738777
19-Jan-2016 Combo OJVM PSU 11.2.0.4.160119 and DB PSU 11.2.0.4.160119 Patch 22378146
20-Oct-2015 Combo OJVM PSU 11.2.0.4.5 and DB PSU 11.2.0.4.8 Patch 21744343
14-Jul-2015 Combo OJVM PSU 11.2.0.4.4 and DB PSU 11.2.0.4.7 Patch 21150851
14-Apr-2015 Combo OJVM PSU 11.2.0.4.3 and DB PSU 11.2.0.4.6 Patch 20834611
20-Jan-2015 Combo OJVM PSU 11.2.0.4.2 and DB PSU 11.2.0.4.5 Patch 20132580
14-Oct-2014 Combo OJVM PSU 11.2.0.4.1 and DB PSU 11.2.0.4.4 Patch 19791364

Grid Infrastructure COMBO: 11.2.0.4 Grid Infrastructure Patch Set Update and 11.2.0.4 OJVM Patch Set Update

Release Date Version Download Link
16-Apr-2024 Combo OJVM PSU 11.2.0.4.240416 and GI PSU 11.2.0.4.240416 * Patch 36517615
16-Jan-2024 **
17-Oct-2023 Combo OJVM PSU 11.2.0.4.231017 and GI PSU 11.2.0.4.231017 * Patch 35742504
18-Jul-2023 Combo OJVM PSU 11.2.0.4.230718 and GI PSU 11.2.0.4.230718 * Patch 35370071
18-Apr-2023 Combo OJVM PSU 11.2.0.4.230418 and GI PSU 11.2.0.4.230418 * Patch 35084475
17-Jan-2023 Combo OJVM PSU 11.2.0.4.230117 and GI PSU 11.2.0.4.230117 * Patch 34773624
18-Oct-2022 Combo OJVM PSU 11.2.0.4.221018 and GI PSU 11.2.0.4.221018 * Patch 34449296
19-Jul-2022 Combo OJVM PSU 11.2.0.4.220719 and GI PSU 11.2.0.4.220719 * Patch 34163709
19-Apr-2022 Combo OJVM PSU 11.2.0.4.220419 and GI PSU 11.2.0.4.2200419 * Patch 33881691
18-Jan-2022 Combo OJVM PSU 11.2.0.4.220118 and GI PSU 11.2.0.4.220118 * Patch 33560103
19-Oct-2021 Combo OJVM PSU 11.2.0.4.211019 and GI PSU 11.2.0.4.211019 * Patch 33248615
20-Jul-2021 Combo OJVM PSU 11.2.0.4.210720 and GI PSU 11.2.0.4.210720 * Patch 32900245
20-Apr-2021 Combo OJVM PSU 11.2.0.4.201020 and GI PSU 11.2.0.4.210420 * Patch 32579106
19-Jan-2021 Combo OJVM PSU 11.2.0.4.201020 and GI PSU 11.2.0.4.210119 * Patch 32126942
20-Oct-2020 Combo OJVM PSU 11.2.0.4.201020 and GI PSU 11.2.0.4.201020 Patch 31720783
14-Jul-2020 Combo OJVM PSU 11.2.0.4.200714 and GI PSU 11.2.0.4.200714 Patch 31326410
14-Apr-2020 Combo OJVM PSU 11.2.0.4.200414 and GI PSU 11.2.0.4.200414 Patch 30783890
14-Jan-2020 Combo OJVM PSU 11.2.0.4.200114 and GI PSU 11.2.0.4.200114 Patch 30463729
15-Oct-2019 Combo OJVM PSU 11.2.0.4.191015 and GI PSU 11.2.0.4.191015 Patch 30133506
16-Jul-2019 Combo OJVM PSU 11.2.0.4.190716 and GI PSU 11.2.0.4.190716 Patch 29699309
16-Apr-2019 Combo OJVM PSU 11.2.0.4.190416 and GI PSU 11.2.0.4.190416 Patch 29252208
15-Jan-2019 Combo OJVM PSU 11.2.0.4.190115 and GI PSU 11.2.0.4.190115 Patch 28980134
16-Oct-2018 Combo OJVM PSU 11.2.0.4.181016 and GI PSU 11.2.0.4.181016 Patch 28689170
17-Jul-2018 Combo OJVM PSU 11.2.0.4.180717 and GI PSU 11.2.0.4.180717 Patch 28317141
17-Apr-2018 Combo OJVM PSU 11.2.0.4.180417 and GI PSU 11.2.0.4.180417 Patch 27726505
16-Jan-2018 Combo OJVM PSU 11.2.0.4.180116 and GI PSU 11.2.0.4.180116 Patch 27282436
17-Oct-2017 Combo OJVM PSU 11.2.0.4.171017 and GI PSU 11.2.0.4.171017 Patch 26636028
18-Jul-2017 Combo OJVM PSU 11.2.0.4.170718 and GI PSU 11.2.0.4.170718 Patch 26030870
18-Apr-2017 Combo OJVM PSU 11.2.0.4.170418 and GI PSU 11.2.0.4.170418 Patch 25440422
17-Jan-2017 Combo OJVM PSU 11.2.0.4.170117 and GI PSU 11.2.0.4.170117 Patch 24918228
18-Oct-2016 Combo OJVM PSU 11.2.0.4.161018 and GI PSU 11.2.0.4.161018 Patch 24436346
19-Jul-2016 Combo OJVM PSU 11.2.0.4.160719 and GI PSU 11.2.0.4.160719 Patch 23615403
19-Apr-2016 Combo OJVM PSU 11.2.0.4.160419 and GI PSU 11.2.0.4.160419 Patch 22738793
19-Jan-2016 Combo OJVM PSU 11.2.0.4.160119 and GI PSU 11.2.0.4.160119 Patch 22378167
20-Oct-2015 Combo OJVM PSU 11.2.0.4.5 and GI PSU 11.2.0.4.8 Patch 21744348
14-Jul-2015 Combo OJVM PSU 11.2.0.4.4 and GI PSU 11.2.0.4.7 Patch 21150864
14-Apr-2015 Combo OJVM PSU 11.2.0.4.3 and GI PSU 11.2.0.4.6 Patch 20834621
20-Jan-2015 Combo OJVM PSU 11.2.0.4.2 and GI PSU 11.2.0.4.5 Patch 20132615
14-Oct-2014 Combo OJVM PSU 11.2.0.4.1 and GI PSU 11.2.0.4.4 Patch 19791420

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

ORA-600 2131故障处理

Posted on by

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

标题：ORA-600 2131故障处理

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

数据库启动报ORA-600 2131错误,查看alert日志发现是在mount过程报错

Fri May 17 20:58:28 2024
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 16
Number of processor cores in the system is 8
Number of processor sockets in the system is 1
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =249
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Windows NT Version V6.2  
CPU                 : 16 - type 8664, 8 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:93799M/97925M, Ph+PgF:78891M/112261M 
Using parameter settings in server-side spfile E:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEXFF.ORA
System parameters with non-default values:
  processes                = 1500
  sessions                 = 2272
  nls_language             = "SIMPLIFIED CHINESE"
  nls_territory            = "CHINA"
  sga_target               = 29440M
  control_files            = "E:\ORADATA\xff\CONTROL01.CTL"
  db_block_size            = 8192
  compatible               = "11.2.0.4.0"
  log_archive_dest_1       = "LOCATION=e:\app\archivelog\"
  log_archive_format       = "ARC%S_%R.%T"
  undo_tablespace          = "UNDOTBS2"
  sec_case_sensitive_logon = FALSE
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=xffXDB)"
  audit_file_dest          = "E:\APP\ADMINISTRATOR\ADMIN\xff\ADUMP"
  audit_trail              = "NONE"
  db_name                  = "xff"
  open_cursors             = 300
  pga_aggregate_target     = 9792M
  diagnostic_dest          = "E:\APP\ADMINISTRATOR"
Fri May 17 20:58:29 2024
PMON started with pid=2, OS id=6696 
Fri May 17 20:58:29 2024
PSP0 started with pid=3, OS id=2424 
Fri May 17 20:58:30 2024
VKTM started with pid=4, OS id=5472 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Fri May 17 20:58:30 2024
GEN0 started with pid=5, OS id=5764 
Fri May 17 20:58:30 2024
DIAG started with pid=6, OS id=372 
Fri May 17 20:58:30 2024
DBRM started with pid=7, OS id=2992 
Fri May 17 20:58:30 2024
DIA0 started with pid=8, OS id=4960 
Fri May 17 20:58:30 2024
MMAN started with pid=9, OS id=6036 
Fri May 17 20:58:30 2024
DBW0 started with pid=10, OS id=4724 
Fri May 17 20:58:30 2024
DBW1 started with pid=11, OS id=2652 
Fri May 17 20:58:30 2024
LGWR started with pid=12, OS id=5320 
Fri May 17 20:58:30 2024
CKPT started with pid=13, OS id=5732 
Fri May 17 20:58:30 2024
SMON started with pid=14, OS id=936 
Fri May 17 20:58:30 2024
RECO started with pid=15, OS id=2192 
Fri May 17 20:58:30 2024
MMON started with pid=16, OS id=5576 
Fri May 17 20:58:30 2024
MMNL started with pid=17, OS id=5748 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = E:\app\Administrator
Fri May 17 20:58:31 2024
ALTER DATABASE   MOUNT
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_5452.trc  (incident=403399):
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []
Incident details in: E:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_403399\xff_ora_5452_i403399.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: ALTER DATABASE   MOUNT...

这个错误是由于controlfile损坏导致,有这个库以前部署过rman备份，解决起来比较简单，使用rman还原控制文件,并尝试recover

RMAN> restore controlfile from 'E:\rmanback\rmanfile\CTL_20240517_A62R067K_1_1.RMAN';

启动 restore 于 17-5月 -24
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在还原控制文件
通道 ORA_DISK_1: 还原完成, 用时: 00:00:01
输出文件名=E:\ORADATA\XFF\CONTROL01.CTL
完成 restore 于 17-5月 -24

RMAN>

RMAN>

RMAN> alter database mount;

数据库已装载
释放的通道: ORA_DISK_1

RMAN> recover database;

启动 recover 于 17-5月 -24
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=996 设备类型=DISK

正在开始介质的恢复

线程 1 序列 4100 的归档日志已作为文件 E:\ORADATA\XFF\REDO02.LOG 存在于磁盘上
线程 1 序列 4101 的归档日志已作为文件 E:\ORADATA\XFF\REDO03.LOG 存在于磁盘上
线程 1 序列 4102 的归档日志已作为文件 E:\ORADATA\XFF\REDO01.LOG 存在于磁盘上
归档日志文件名=E:\APP\ARCHIVELOG\ARC0000004025_1165094245.0001 线程=1 序列=4025
归档日志文件名=E:\APP\ARCHIVELOG\ARC0000004026_1165094245.0001 线程=1 序列=4026
…………
归档日志文件名=E:\APP\ARCHIVELOG\ARC0000004099_1165094245.0001 线程=1 序列=4099
归档日志文件名=E:\ORADATA\XFF\REDO02.LOG 线程=1 序列=4100
归档日志文件名=E:\ORADATA\XFF\REDO03.LOG 线程=1 序列=4101
归档日志文件名=E:\ORADATA\XFF\REDO01.LOG 线程=1 序列=4102
介质恢复完成, 用时: 00:00:22
完成 recover 于 17-5月 -24

RMAN> exit


恢复管理器完成。

E:\oradata\XFF>

这种恢复情况下,如果现在要打开库,需要resetlogs方式,考虑通过创建ctl直接打开（不想用resetlogs）

SQL> shutdown immediate;
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area 3.0732E+10 bytes
Fixed Size                  2296264 bytes
Variable Size            3825206840 bytes
Database Buffers         2.6844E+10 bytes
Redo Buffers               61206528 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 876
  7  LOGFILE
  8    GROUP 1 'E:\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'E:\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'E:\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'E:\ORADATA\XFF\SYSTEM01.DBF',
 14    'E:\ORADATA\XFF\SYSAUX01.DBF',
 15    'E:\ORADATA\XFF\USERS01.DBF',
 16    'E:\ORADATA\XFF\XFF_DATA01.DBF',
 17    'E:\ORADATA\XFF\XFF_INDEX01.DBF',
 18    'E:\ORADATA\XFF\UNDOTBS2.DBF'
 19  CHARACTER SET ZHS16GBK
 20  ;

控制文件已创建。

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

数据库已更改。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORADATA\XFF\TEMP01.DBF' REUSE;

表空间已更改。

至此本次恢复晚上,由于arch,redo和数据文件没有损坏,恢复非常完美，参考以前类似说明：ORA-600 2131故障说明