有客户由于磁盘坏道导致数据文件访问报ORA-27070 OSD-04016 O/S-Error等相关错误

rman 尝试读取88号文件

RMAN> backup datafile 88 format 'e:\rman\%d_%T_%I.%s%p';

启动 backup 于 05-6月 -24
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=2246 设备类型=DISK
通道 ORA_DISK_1: 正在启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集内的数据文件
输入数据文件: 文件号=00088 名称=E:\APP\ADMINISTRATOR\ORADATA\XFF\XIFENFEI.ORA
通道 ORA_DISK_1: 正在启动段 1 于 05-6月 -24
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: backup 命令 (ORA_DISK_1 通道上, 在 06/05/2024 18:33:43 上) 失败
ORA-19501: 文件 "E:\APP\ADMINISTRATOR\ORADATA\XFF\XIFENFEI.ORA", 块编号 322944 (块大小=8192) 上出现读取错误
ORA-27070: 异步读取/写入失败
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 23) 数据错误(循环冗余检查)。

检查系统日志,发现有报:设备 \Device\Harddisk0\DR0 有一个不正确的区块

到目前为止基本上可以判断是文件系统或者磁盘层面出现坏道导致该问题（磁盘坏道概率更大）,使用工具对损坏数据文件进行强制拷贝,提示少量扇区数据无法拷贝

通过dbv检查恢复出来文件效果

E:\check_db>DBV FILE=D:/XIFENFEI.ORA

DBVERIFY: Release 11.2.0.3.0 - Production on 星期日 6月 9 22:17:28 2024

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

DBVERIFY - 开始验证: FILE = D:\XIFENFEI.ORA
页 323055 标记为损坏
Corrupt block relative dba: 0x1604edef (file 88, block 323055)
Bad header found during dbv:
Data in bad block:
 type: 229 format: 5 rdba: 0xe5e5e5e5
 last change scn: 0xe5e5.e5e5e5e5 seq: 0xe5 flg: 0xe5
 spare1: 0xe5 spare2: 0xe5 spare3: 0xe5e5
 consistency value in tail: 0x4c390601
 check value in block header: 0xe5e5
 computed block checksum: 0x5003



DBVERIFY - 验证完成

检查的页总数: 524288
处理的页总数 (数据): 204510
失败的页总数 (数据): 0
处理的页总数 (索引): 127485
失败的页总数 (索引): 0
处理的页总数 (其他): 3030
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 189262
标记为损坏的总页数: 1
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 184063522 (3470.184063522)

运气不错，就一个数据库block异常,通过dba_extents查询坏块所属对象，运气不太好是一个表数据

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;
输入 file_id 的值:  88
原值    3:  WHERE FILE_ID = &FILE_ID
新值    3:  WHERE FILE_ID = 88
输入 block_id 的值:  323055
原值    4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
新值    4:    AND 323055 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME
------------------ ------------------------------ ------------------------------
XFFUSER
XFFTABLE
TABLE              XFFTBS_TAB2

设置跳过坏块导出数据，然后重命名原表导入数据,完成本次恢复，以前有过类似恢复:

一次侥幸的OSD-04016 O/S-Error异常恢复
 O/S-Error: (OS 23) 数据错误(循环冗余检查) 数据库恢复

异常断电数据库恢复-从ORA-600 2131到ORA-08102: 未找到索引关键字, 对象号 39

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

标题：异常断电数据库恢复-从ORA-600 2131到ORA-08102: 未找到索引关键字, 对象号 39

数据库启动报ORA-600 2131，以前遇到过类似问题:ORA-600 2131故障处理

SQL> alter database mount;
alter database mount
*
第 1 行出现错误:
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []

Tue Jun 04 14:12:18 2024
RECO started with pid=15, OS id=3244 
Tue Jun 04 14:12:18 2024
MMON started with pid=16, OS id=3256 
Tue Jun 04 14:12:18 2024
MMNL started with pid=17, OS id=3432 
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
Tue Jun 04 14:12:22 2024
alter database mount exclusive
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_2536.trc  (incident=427583):
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []
Tue Jun 04 14:12:28 2024
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 exclusive...

重建ctl，然后重试recover 数据库，报ORA-600 kdourp_inorder2和ORA-600 3020错误，这些错误本质都是由于redo信息和block信息不匹配导致

SQL> recover datafile 1;
ORA-00283: 恢复会话因错误而取消
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 74805)
ORA-10564: tablespace SYSTEM
ORA-01110: 数据文件 1: 'E:\ORADATA\XFF\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 8
ORA-00600: 内部错误代码, 参数: [kdourp_inorder2], [16], [3], [0], [108], [], [], [], [], [], [], []


SQL> recover datafile 7;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [7], [385], [29360513], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 7, block# 385, file offset is 3153920 bytes)
ORA-10564: tablespace UNDOTBS2
ORA-01110: 数据文件 7: 'E:\ORADATA\XFF\UNDOTBS2.DBF'
ORA-10560: block type 'KTU UNDO BLOCK'

通过屏蔽一致性,修改文件头scn,强制打开数据库

SQL> recover database until cancel;
ORA-00279: 更改 56782359 (在 06/04/2024 14:00:36 生成) 对于线程 1 是必需的
ORA-00289: 建议: E:\APP\ARCHIVELOG\ARC0000005415_1165094245.0001
ORA-00280: 更改 56782359 (用于线程 1) 在序列 #5415 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'E:\ORADATA\XFF\SYSTEM01.DBF'


ORA-01112: 未启动介质恢复


SQL> alter database open resetlogs;

数据库已更改。

尝试导出数据报ORA-08102,导致数据库无法正常导出

C:\Users\Administrator>expdp "'/ as sysdba'" full=y dumpfile=full_20240604_%U.dmp DIRECTORY=expdp_dir 
logfile=full_20240604.log parallel=2 EXCLUDE=STATISTICS,AUDIT

Export: Release 11.2.0.4.0 - Production on 星期二 6月 4 18:40:26 2024

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

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: 作业不存在
ORA-31633: 无法创建主表 "SYS.SYS_EXPORT_FULL_05"
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: 在 "SYS.KUPV$FT", line 1038
ORA-08102: 未找到索引关键字, 对象号 39, 文件 1, 块 97540 (2)

obj 39 为OBJ$的I_OBJ4对象报ORA-08102

SQL> select owner,object_name,object_type from dba_objects where object_id=39
  2  /

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYS                            I_OBJ4                         INDEX

该对象属于bootstrap$中核心对象，无法直接rebuild,参考下面文章处理,然后再尝试导出数据
分享I_OBJ4 ORA-8102故障恢复案例
 使用bbed 修复I_OBJ4 index 报ORA-8102错误
 bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决

C:\Users\Administrator>expdp "'/ as sysdba'" full=y dumpfile=full_20240604_%U.dmp DIRECTORY=expdp_dir 
logfile=full_20240604.log parallel=2 EXCLUDE=STATISTICS,AUDIT

Export: Release 11.2.0.4.0 - Production on 星期二 6月 4 18:43:47 2024

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

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: 作业不存在
ORA-31637: 无法创建作业 SYS_EXPORT_FULL_01 (用户 SYS)
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: 在 "SYS.KUPV$FT_INT", line 798
ORA-39080: 无法为数据泵作业创建队列 "KUPC$C_1_20240604184348" 和 "KUPC$S_1_20240604184348"
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: 在 "SYS.KUPC$QUE_INT", line 1534
ORA-08102: 未找到索引关键字, 对象号 53, 文件 1, 块 97715 (2)

通过类似方法分析确认为CDEF$的I_CDEF1 index,处理方法和I_OBJ4一样,然后导出数据成功,导入到新库中,在这个迁移过程中遭遇Wrapped 加密的package body无效的问题,具体参见:数据泵迁移Wrapped PLSQL之后报PLS-00753

数据泵迁移Wrapped PLSQL之后报PLS-00753

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

标题：数据泵迁移Wrapped PLSQL之后报PLS-00753

记录一个oracle datapump迁移库遇到Wrapped PLSQL无效的Bug 18881811 Data Pump Import of Wrapped PLSQL Corrupts the Body（主要影响版本11.2.0.4/12.1.0.2）

导入时提示:ORA-39082 Object type PACKAGE_BODY:created with compilation warnings.
尝试编译对象提示

SQL> alter package xx.xx compile body;

Warning: Package Body altered with compilation errors.

SQL> show error

PLS-00753: malformed or corrupted wrapped unit

由于涉及的package body 比较多,人工处理比较麻烦,采取临时解决方法
1. drop 掉所有报错的package
2. 使用exp导出所有的对象（rows=n）
3. 使用imp导入
4. 编译无效对象

数据库open报ORA-600 kcratr_scan_lastbwr故障处理

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

标题：数据库open报ORA-600 kcratr_scan_lastbwr故障处理

由于断电，导致数据库正常open报ORA-600 kcratr_scan_lastbwr错误

Wed Jan 17 18:23:26 2024
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 1028618590
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Wed Jan 17 18:23:30 2024
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Hex dump of (file 3, block 144) in trace file /database/oracle/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_66361.trc
Reading datafile '/database/oracle/app/oracle/oradata/xff/datafile/o1_mf_undotbs1_hct7001s_.dbf' 
  for corruption at rdba: 0x00c00090 (file 3, block 144)
Reread (file 3, block 144) found same corrupt data (logically corrupt)
Write verification failed for File 3 Block 144 (rdba 0xc00090)
Errors in file /database/oracle/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_66361.trc  (incident=672241):
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], []
Incident details in: /database/oracle/app/oracle/diag/rdbms/xff/xff/incident/incdir_672241/xff_ora_66361_i672241.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Aborting crash recovery due to error 600
Errors in file /database/oracle/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_66361.trc:
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], []
Errors in file /database/oracle/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_66361.trc:
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...

尝试recover 数据库报ORA-600 3020错误

Wed Jan 17 18:28:38 2024
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 96 slaves
Wed Jan 17 18:28:41 2024
Recovery of Online Redo Log: Thread 1 Group 2 Seq 410864 Reading mem 0
  Mem# 0: /database/oracle/app/oracle/oradata/xff/onlinelog/o1_mf_2_hct740hq_.log
Wed Jan 17 18:28:42 2024
ORA-00600: internal error code, arguments: [3020], [3], [240], [12583152], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 240, file offset is 1966080 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: '/database/oracle/app/oracle/oradata/xff/datafile/o1_mf_undotbs1_hct7001s_.dbf'
ORA-10560: block type 'KTU SMU HEADER BLOCK'
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Slave exiting with ORA-600 exception
Errors in file /database/oracle/app/oracle/diag/rdbms/xff/xff/trace/xff_pr19_68212.trc:
ORA-00600: internal error code, arguments: [3020], [3], [240], [12583152], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 240, file offset is 1966080 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: '/database/oracle/app/oracle/oradata/xff/datafile/o1_mf_undotbs1_hct7001s_.dbf'
ORA-10560: block type 'KTU SMU HEADER BLOCK'
Errors in file /database/oracle/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_68038.trc  (incident=672243):
ORA-00600: internal error code, arguments: [3020], [3], [240], [12583152], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 240, file offset is 1966080 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: '/database/oracle/app/oracle/oradata/xff/datafile/o1_mf_undotbs1_hct7001s_.dbf'
ORA-10560: block type 'KTU SMU HEADER BLOCK'
Wed Jan 17 18:28:43 2024
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Recovery Slave PR19 previously exited with exception 600
Wed Jan 17 18:28:43 2024
Sweep [inc][672865]: completed
Media Recovery failed with error 448
Errors in file /database/oracle/app/oracle/diag/rdbms/xff/xff/trace/xff_pr00_68115.trc:
ORA-00283: recovery session canceled due to errors
ORA-00448: normal completion of background process
ORA-600 signalled during: ALTER DATABASE RECOVER  database  ...

加上隐含参数尝试强制拉库

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 6165467436
Clearing online redo logfile 1 /database/oracle/app/oracle/oradata/xff/onlinelog/o1_mf_1_hct740fp_.log
Clearing online log 1 of thread 1 sequence number 0
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /database/oracle/app/oracle/oradata/xff/onlinelog/o1_mf_2_hct740hq_.log
Clearing online log 2 of thread 1 sequence number 0
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /database/oracle/app/oracle/oradata/xff/onlinelog/o1_mf_3_hct740k7_.log
Clearing online log 3 of thread 1 sequence number 0
Clearing online redo logfile 3 complete
Online log /database/oracle/app/oracle/oradata/xff/onlinelog/o1_mf_1_hct740fp_.log: Thread 1 Group 1 was previously cleared
Online log /database/oracle/app/oracle/oradata/xff/onlinelog/o1_mf_2_hct740hq_.log: Thread 1 Group 2 was previously cleared
Online log /database/oracle/app/oracle/oradata/xff/onlinelog/o1_mf_3_hct740k7_.log: Thread 1 Group 3 was previously cleared
Fri Jan 19 09:24:59 2024
Setting recovery target incarnation to 2
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Warning - High Database SCN: Current SCN value is 6165467439, threshold SCN value is 0
If you have not previously reported this warning on this database,
  please notify Oracle Support so that additional diagnosis can be performed.
Fri Jan 19 09:24:59 2024
Assigning activation ID 1028784413 (0x3d52011d)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /database/oracle/app/oracle/oradata/xff/onlinelog/o1_mf_1_hct740fp_.log
Successful open of redo thread 1
Fri Jan 19 09:24:59 2024
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Jan 19 09:24:59 2024
SMON: enabling cache recovery
Errors in file /database/oracle/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_84860.trc  (incident=1344255):
ORA-00600: internal error code, arguments: [2662], [1], [1870500147], [1], [1870515285], [12583040]
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 /database/oracle/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_84860.trc:
ORA-00600: internal error code, arguments: [2662], [1], [1870500147], [1], [1870515285], [12583040]
Errors in file /database/oracle/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_84860.trc:
ORA-00600: internal error code, arguments: [2662], [1], [1870500147], [1], [1870515285], [12583040]
Error 600 happened during db open, shutting down database
USER (ospid: 84860): terminating the instance due to error 600
Instance terminated by USER, pid = 84860
ORA-1092 signalled during: alter database open resetlogs...

客户自行恢复到这一步,后面无法处理,接手之后进行恢复，其实后面比较简单了，就是修改下数据库scn,数据库就可以open起来,然后处理异常的undo和对象即可,可以参考以前类似文章:
ORA-600 2662快速恢复之Patch scn工具
 硬件故障导致ORA-600 2662错误处理
 ORA-00600 [2662]和ORA-00600 [4194]恢复
更多参考:惜分飞blog中ORA-600 2662文章

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

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

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

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

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

O/S-Error: (OS 23) 数据错误(循环冗余检查)—故障处理

11.2.0.4 Database Patch Set Update

11.2.0.4 Grid Infrastructure Patch Set Update

11.2.0.4 OJVM Patch Set Update

Database PSU COMBO: 11.2.0.4 Database Patch Set Update and 11.2.0.4 OJVM Patch Set Update

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