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状态,但是无法执行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—实现新老密码短期共存

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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故障说明

收集统计信息报ORA-00600 16515问题解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：收集统计信息报ORA-00600 16515问题解决

在一套win 11.2.0.1的数据库在收集统计信息报ORA-600 16515错误

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1


SQL> exec dbms_stats.gather_database_stats(estimate_percent=>100,degree=>4,cascade=>true,granularity=>'ALL');
BEGIN dbms_stats.gather_database_stats(estimate_percent=>100,degree=>4,cascade=>true,granularity=>'ALL'); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 25335
ORA-06512: ?? "SYS.DBMS_STATS", line 25511
ORA-06512: ?? "SYS.DBMS_STATS", line 25467
ORA-06512: ?? line 1

确认报错对象

SQL> select owner, object_name, object_type
  2  from sys.dba_objects
  3  where object_id =6375;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
SYS
WRI$_SQLMON_USAGE
TABLE

对对象进行处理,确认对象本身没有问题,尝试收集统计信息依旧报错

SQL> alter table WRI$_SQLMON_USAGE move;

表已更改。

SQL> select index_name from dba_indexes where table_name='WRI$_SQLMON_USAGE';

未选定行

SQL> SELECT COUNT(1) FROM WRI$_SQLMON_USAGE;

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

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1

删除统计再尝试

SQL> exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname=>'WRI$_SQLMON_USAGE');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1

使用ANALYZE收集统计信息正常

SQL> ANALYZE TABLE  WRI$_SQLMON_USAGE  COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

表已分析。

删除hist_head$中对应报错行记录,ORA-00600 16515问题解决

SQL> SELECT rowid,obj#,intcol#,timestamp# FROM hist_head$ WHERE obj#=6376 AND intcol#=1;

ROWID                    OBJ#    INTCOL# TIMESTAMP#
------------------ ---------- ---------- --------------
AAAAHAAABAAAIELABQ       6376          1 01-4?  -24

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系统已更改。

SQL>  DELETE FROM hist_head$ WHERE ROWID='AAAAHAAABAAAIELABQ';

已删除 1 行。

SQL> COMMIT;

提交完成。

SQL> begin
  2  dbms_stats.delete_table_stats ( ownname => user, tabname => 'WRI$_SQLMON_USAGE', cascade_parts => true, cascade_col
umns => true, cascade_indexes => true, force => true);
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_database_stats_job_proc();

PL/SQL 过程已成功完成。

参考文档:DBMS_STATS.DELETE_TABLE_STATS Fails With ORA-600 [16515] (Doc ID 1233745.1)

rm -rf误删Oracle数据库恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：rm -rf误删Oracle数据库恢复

有客户把虚拟化环境中装有oracle数据库的linux操作系统,由于操作失误在/下面执行了rm -rf *，导致所有文件被删除,系统无法启动.客户希望要求恢复出其中的Oracle数据库.由于是虚拟化环境,然后客户直接从虚拟化平台下载下来磁盘文件,通过工具加载和分析确认是一个xfs的文件系统

使用工具进一步扫描分析,找到部分数据文件

这里可以获取到两个信息:
1. 尝试恢复oracle的control01.ctl文件,然后通过该文件尝试分析其他数据文件位置,运气不错该文件恢复出来是好的,直接加载到新库查询v$datafile,分析出来所有数据文件信息
2. 这里有一个非常不幸的信息,oracle最核心的system01.dbf文件大小明显异常,进一步分析该文件信息,结论是该文件无法通过反删除方式进行恢复

先把可以os层面可以恢复的数据恢复出来,并且检查坏块情况

对于异常的system文件,有两个处理方法:
1. 通过阅览被删除的文件，发现客户有5月14日1点左右的rman备份,通过恢复软件中完整度提示,大概率应该没有什么问题,但是分析发现部分归档日志损坏无法完整恢复
2. 通过对磁盘做碎片,恢复出来该数据文件,参考以往文章:
dbca删除库和rm删库恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
通过这个方法运气不错,恢复出来该库的system01.dbf文件非常完整0丢失

[oracle@localhost oradata]$ dbv file=system01.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on Thu May 15 23:26:57 2024

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

DBVERIFY - Verification starting : FILE = /u01/oradata/system01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 199680
Total Pages Processed (Data) : 113988
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 26869
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 40253
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 18570
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 658228557 (0.658228557)

完成上述恢复工作之后,目前确认只有sysaux01.dbf有8026个block损坏,但是该表空间不涉及业务数据,尝试在新的系统中直接修改路径并open库

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database


SQL> alter database flashback off;

Database altered.

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

Database altered.

运气不错,数据库直接open成功,现在处理sysaux01.dbf中的损坏文件:
1. 确认该文件具体坏块开始位置:

2. 由于坏块在文件中比较靠后,分析实际存储数据最后的位置

SQL> select max(block_id+blocks) from dba_extents where file_id=3;

MAX(BLOCK_ID+BLOCKS)
--------------------
             3493120

最后存储数据的位置小于坏块的位置,证明坏块部分是没有存储数据的,直接resize掉坏块部分

SQL> alter database datafile '/u01/oradata/sysaux01.dbf' resize 27290m;

Database altered.

然后dbv该数据文件,确认没有任何问题

[oracle@localhost trace]$ dbv file=/u01/oradata/sysaux01.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on Wed May 15 22:43:00 2024

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

DBVERIFY - Verification starting : FILE = /u01/oradata/sysaux01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 3493120
Total Pages Processed (Data) : 1516833
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1868832
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 56577
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 32107
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 18771
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 658223915 (0.658223915)

使用rman检测全库,也确定没有任何问题

[oracle@localhost trace]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 15 22:43:58 2024
Version 19.15.0.0.0

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

connected to target database: XIFENFEI (DBID=2912535091)

RMAN> 

RMAN> 

RMAN> backup validate check logical database skip inaccessible;

Starting backup at 15-MAY-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=278 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
………………
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
32   OK     0              6273         6400            370625094 
  File Name: /u01/oradata/xff_com.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              127             

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
33   OK     0              163752       832000          627920639 
  File Name: /u01/oradata/XFF_DATA_202312231.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              374296          
  Index      0              285002          
  Other      0              8950            

Finished backup at 15-MAY-24

[oracle@localhost trace]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 15 22:47:44 2024
Version 19.15.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.15.0.0.0

SQL> select * from v$database_block_corruption ;

no rows selected

SQL>

至此对于这次rm -rf /*的故障实现了Oracle数据库完美恢复,数据0丢失.

分布式存储故障导致数据库无法启动故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：分布式存储故障导致数据库无法启动故障处理

国内xx医院使用了国外医疗行业龙头的pacs系统,由于是一个历史库,存放在分布式存储中,由于存储同时多个节点故障,导致数据库多个文件异常,数据库无法启动,三方维护人员尝试通通过rman归档进行应用日志,结果发现日志有损坏报ORA-00354 ORA-00353,无法记录恢复,希望我们给予支持

Mon Apr 29 13:28:40 2024
Media Recovery failed with error 354
Mon Apr 29 13:28:40 2024
Errors in file F:\XXXXXX_DB\ORACLE\ADMIN\diag\rdbms\xxx\msxxx1\trace\xxx_pr00_4568.trc:
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 487424 change 8737273868 time 04/01/2024 01:38:25
ORA-00334: archived log: 'F:\XXXXXX_DB\ORADATA\XXX\LOGARC0000052184_0922116268.0001'
ORA-283 signalled during: alter database recover logfile 'F:\XXXXXX_DB\ORADATA\XXX\LOGARC0000052184_0922116268.0001'...

接手故障之后,通过尝试恢复发现除该错误之外,还有ORA-600 4552之类错误

跳过这些异常文件恢复,最终确认异常文件有如下部分

这些文件由于日志无法正常应用(有日志损坏无法应用，有日志和数据文件block不匹配导致无法应用),这样的情况直接通过自研的Oracle Recovery Tools小工具直接修改文件头信息

然后尝试OPEN数据库结果报ORA-1207

对于这个故障可以通过rectl或者using backup ctl方式处理,然后open数据库成功

由于该系统是历史库,不会有新业务写入,通过对异常表和索引进行处理之后,客户测试业务可以正常访问,完成本次恢复

read_me_recover_tn勒索恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：read_me_recover_tn勒索恢复

最近有客户被MySQL删库勒索,现象如下:
1. 删除掉以前的库,并创建一个同名库,并且会创建一个read_me_recover_tn库,类似下图:

2. 在read_me_recover_tn库中有一个readme表,每个被删除然后创建的库里面也有一个readme表

3. 每个readme表内容类似信息类似:

mysql> desc readme
    -> ;
+-----------------+------+------+-----+---------+-------+
| Field           | Type | Null | Key | Default | Extra |
+-----------------+------+------+-----+---------+-------+
| id              | int  | NO   | PRI | NULL    |       |
| Message         | text | YES  |     | NULL    |       |
| Bitcoin_Address | text | YES  |     | NULL    |       |
+-----------------+------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from readme\G;
*************************** 1. row ***************************
             id: 1
        Message: I have backed up all your databases. To recover them you must
 pay 0.008 BTC (Bitcoin) to this address: 15f9vdGBeT1NCMp6z9NxrQEEUxnYqRPvyC . 
Backup List: xxxx_db, xxxx_db_test. After your payment email me at 
dbrestore3195@onionmail.org with your server IP (xx.xx.xx.xx) and transaction 
ID and you will get a download link to your backup. Emails without transaction 
ID and server IP will be ignored.
Bitcoin_Address: 15f9vdGBeT1NCMp6z9NxrQEEUxnYqRPvyC
1 row in set (0.00 sec)

这类勒索和我以前介绍相关文章类似:
RECOVER_YOUR_DATA勒索恢复
 A____Z____RECOVER____DATA勒索恢复

处理办法也完全相同:
建议先对系统进行镜像或者快照,然后按照先os层面恢复,在block级别恢复的方法处理,如果无法自行解决,可以联系我们进行技术支持,最大限度抢救和数据,减少损失
电话/微信:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
另外建议加强系统和mysql安全加固,数据库尽量不要暴露在公网上

WINDOWS 下用dg broker搭建ADG（单机to单机）

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：WINDOWS 下用dg broker搭建ADG（单机to单机）

环境

#主备库 C:\Windows\System32\drivers\etc\hosts 文件
192.168.11.10  dg1
192.168.11.11  dg2
#环境
主库主机名：dg1 现有实例orcl
备库主机名：dg2 只安装软件

一，主库配置
–主库设置强制日志，保证所有的操作都记录到日志文件
–查看当前force\_logging的设置

#主库如果已开启归档，不需要停机
sqlplus / as sysdba
select force_logging from v$database;
select flashback_on from v$database;
alter database force logging; -- 开启强制日志模式
#######################################################
#如果没开归档
sqlplus / as sysdba
shudown immediate;
startup mount;
alter database archivelog; -- 开启归档模式
alter database force logging; -- 开启强制日志模式
#alter database flashback on;  -- 开启闪回，不是必须，推荐开启
#######################################################
#主库添加standby日志组
#查看日志文件大小 select bytes/1024/1024 from v$log;这里是50M
alter database add standby logfile group 10 ('D:\app\Administrator\oradata\orcl\standby_redo01.log') size 50m;
alter database add standby logfile group 11 ('D:\app\Administrator\oradata\orcl\standby_redo02.log') size 50m;
alter database add standby logfile group 12 ('D:\app\Administrator\oradata\orcl\standby_redo03.log') size 50m;
alter database add standby logfile group 13 ('D:\app\Administrator\oradata\orcl\standby_redo04.log') size 50m;
########################################################
#设置文件管理自动
alter system set standby_file_management=auto;

二、主备库网络设置

#主库listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_DGMGRL)  #用于dg broker的静态监听
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
#主库tnsnames.ora
ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  
#备库listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_stby_DGMGRL) #用于dg broker的静态监听
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
#备库tnsnames.ora
ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
#主库监听reload
lsnrctl reload
#备库启动监听
lsnrctl start

三、备库配置

#创建一个临时参数文件如d:\pfile.txt内容如下
*.db_name='orcl'
#创建密码文件，或者从主库拷贝一个
orapwd file=D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\database\PWDorcl.ora password=oracle entries=10
#创建备库所需目录
mkdir D:\app\Administrator\oradata\orcl\
mkdir D:\app\Administrator\admin\orcl\adump\
mkdir D:\app\Administrator\fast_recovery_area\orcl\
#用ORADIM创建实例
oradim -new -sid orcl
#用临时参数文件启动
sqlplus / as sysdba
starup nomount pfile='d:\pfile.txt';

四、RMAN duplicate创建备库

#tnsping测试互通性
tnsping orcl
tnsping orcl_stby
#主库连接备库
sqlplus sys/oracle@stddb as sysdba
#备库连接主库
sqlplus sys/oracle@orcl as sysdba
#########################################################
#备库执行，连接主备库
rman target sys/oracle@orcl auxilary sys/oracle@orcl_stby
#创建dg备库，这里假设主备库路径相同
duplicate target database
  for standby
  from active database
  dorecover
  spfile
    set db_unique_name='orcl_stby'
  nofilenamecheck;
#########################################################
#如果主备库路径不同
duplicate target database
  for standby
  from active database
  dorecover
  spfile
    set db_unique_name='orcl_stby'
    set db_file_name_convert='orcl','orcl_stby'
    set log_file_name_convert='orcl','orcl_stby'
    set job_queue_processes='0'
  nofilenamecheck;
#开启ADG
sqlplus / as sysdba
alter database open read only;
alter database recover managed standby database disconnect from session;

五、配置DG BROKER

#主备库两边执行
alter system set dg_broker_start=true;
#主库连接dgmgrl
dgmgrl sys/oracle@orcl
#创建dg broker配置
create configuration dg_config as primary database is orcl connect identifier is orcl;
#添加备库到配置文件
add database orcl_stby as connect identifier is orcl_stby;
#启用配置
enable configuration;

############################################################################
#显示DG配置信息
show configuration
show configuration verbose
#显示主备库信息
show database orcl
show database orcl_stby
show database verbose orcl
show database verbose orcl_stby

六、一些测试

#测试Database Switchover
dgmgrl sys/oracle@orcl
switchover to orcl_stby;
show configuration
#切换回来
switchover to orcl;
show configuration
###########################################################################
#测试Database Failover，此时dg关系已经打破
dgmgrl sys/oracle@orcl
failover to orcl_stby;
#如果主库开启了flashback，执行以下语句自动重建主库
 reinstate database orcl;
#如果没有开启flashback，删除重建主库，重新建立dg关系
############################################################################
#测试快照备库
dgmgrl sys/oracle@orcl
convert database orcl_stby to snapshot standby;
show configuration;
#快照转成正常备库
convert database orcl_stby to physical standby;
show configuration;

七、总结
优点在于除监听设置外主备库都不需要做过多的设置，备库临时参数文件只需要一个dbname，其余dg有关的参数dg broker会自动设置。
八、参考资料
ORACLE-BASE – Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 11g Release 2

存储故障后oracle报—ORA-01122/ORA-01207故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：存储故障后oracle报—ORA-01122/ORA-01207故障处理

客户存储异常,通过硬件恢复解决存储故障之后,oracle数据库无法正常启动(存储cache丢失),尝试recover数据库报ORA-00283 ORA-01122 ORA-01110 ORA-01207错误
以前处理过比较类似的存储故障case:
又一起存储故障导致ORA-00333 ORA-00312恢复
 存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理

SQL> recover database until cancel;
ORA-00283: 恢复会话因错误而取消
ORA-01122: 数据库文件 536 验证失败
ORA-01110: 数据文件 536: '+DATA/orcl/dt_img_dat511.ora'
ORA-01207: 文件比控制文件更新 - 旧的控制文件

Sun May 05 00:09:03 2024
ALTER DATABASE RECOVER  database until cancel  
Media Recovery Start
 started logmerger process
Sun May 05 00:09:10 2024
SUCCESS: diskgroup FRA was mounted
Sun May 05 00:09:10 2024
NOTE: dependency between database orcl and diskgroup resource ora.FRA.dg is established
Sun May 05 00:09:14 2024
WARNING! Recovering data file 1 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
Media Recovery failed with error 1122
Slave exiting with ORA-283 exception
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_pr00_8048.trc:
ORA-00283: 恢复会话因错误而取消
ORA-01122: 数据库文件 536 验证失败
ORA-01110: 数据文件 536: '+DATA/orcl/dt_img_dat511.ora'
ORA-01207: 文件比控制文件更新 - 旧的控制文件
Sun May 05 00:09:16 2024
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database until cancel  ...

using backup controlfile进行恢复

SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 18646239951 (在 04/25/2024 17:14:50 生成) 对于线程 1 是必需的
ORA-00289: 建议:
+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003886.199435.1167240505
ORA-00280: 更改 18646239951 (用于线程 1) 在序列 #1003886 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: 更改 18646239951 (在 04/25/2024 17:11:40 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+FRA/orcl/archivelog/2024_04_25/thread_2_seq_677876.199531.1167239807
ORA-00280: 更改 18646239951 (用于线程 2) 在序列 #677876 中


ORA-00279: 更改 18646255791 (在 04/25/2024 17:16:46 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+FRA/orcl/archivelog/2024_04_25/thread_2_seq_677877.199472.1167240099
ORA-00280: 更改 18646255791 (用于线程 2) 在序列 #677877 中
ORA-00278: 此恢复不再需要日志文件
'+FRA/orcl/archivelog/2024_04_25/thread_2_seq_677876.199531.1167239807'


ORA-00279: 更改 18646295647 (在 04/25/2024 17:21:38 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+FRA/orcl/archivelog/2024_04_25/thread_2_seq_677878.199379.1167240623
ORA-00280: 更改 18646295647 (用于线程 2) 在序列 #677878 中
ORA-00278: 此恢复不再需要日志文件
'+FRA/orcl/archivelog/2024_04_25/thread_2_seq_677877.199472.1167240099'


ORA-00279: 更改 18646331784 (在 04/25/2024 17:28:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003887.199320.1167241507
ORA-00280: 更改 18646331784 (用于线程 1) 在序列 #1003887 中
ORA-00278: 此恢复不再需要日志文件
'+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003886.199435.1167240505'


ORA-00308: 无法打开归档日志
'+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003887.199320.1167241507'
ORA-17503: ksfdopn: 2 未能打开文件
+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003887.199320.1167241507
ORA-15012: ASM file
'+FRA/orcl/archivelog/2024_04_25/thread_1_seq_1003887.199320.1167241507' does not exist


ORA-10879: error signaled in parallel recovery slave
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: '+DATA/orcl/system01.dbf'

通过分析,确认由于cache丢失导致thread_1_seq_1003887这个日志丢失（而且redo已经被覆盖）

数据库无法通过正常recover的思路解决.通过屏蔽一致性,强制打开数据库,alert日志报ORA-600 2662错误

Sat May 04 17:23:00 2024
Redo thread 2 internally disabled at seq 1 (CKPT)
ARC1: Archiving disabled thread 2 sequence 1
Archived Log entry 2 added for thread 2 sequence 1 ID 0x0 dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_ora_3684.trc  (incident=47066):
ORA-00600: ??????, ??: [2662], [4], [1466533588], [4], [1466584862], [12583040], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_ora_3684.trc:
ORA-00600: ??????, ??: [2662], [4], [1466533588], [4], [1466584862], [12583040], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_ora_3684.trc:
ORA-00600: ??????, ??: [2662], [4], [1466533588], [4], [1466584862], [12583040], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 3684): terminating the instance due to error 600
Instance terminated by USER, pid = 3684
ORA-1092 signalled during: alter database open resetlogs...

通过修改数据库scn,open数据库报ORA-600 4137

Sun May 05 00:12:41 2024
replication_dependency_tracking turned off (no async multimaster replication found)
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open resetlogs
Sun May 05 00:12:56 2024
Trace dumping is performing id=[cdmp_20240505001256]
Sun May 05 00:12:56 2024
ORACLE Instance orcl1 (pid = 22) - Error 600 encountered while recovering transaction (28, 21).
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl1\trace\orcl1_smon_5896.trc:
ORA-00600: ??????, ??: [4137], [28.21.42965783], [0], [0], [], [], [], [], [], [], [], []

这个错误比较明显,由于28号回滚段异常导致,对异常回滚段进行处理,重建undo,数据库恢复主要工作完成