联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户exsi系统被勒索病毒加密,拷贝出来磁盘文件,通过工具分析,磁盘文件均有部分被破坏,恢复工具无法自动识别
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：expdp dmp 导出不完整导入ORA-39059 ORA-39246 故障抢救数据
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户一套nc系统,由于安装时候把库建在了比较小的分区上,运行一些时间之后,出现空间不足,现场技术人员对oracle不太熟悉,经过一系列操作(删除业务表空间,复制pdb,创建表空间等等操作),无法恢复数据库,准备使用备份的dmp进行还原,结果分析发现仅保留的最后一份dmp,是一份导出不完全的dmp文件,无法正常导入(以前处理过一个类似case:ORA-39773: parse of metadata stream failed故障处理,尝试导入报ORA-39246错:
C:\Users\XFF>impdp system/oracle@127.0.0.1/orapdb directory=expdp_dir dumpfile=xxxxx_2025-12-01_0230.dmp logfile=1.log Import: Release 19.0.0.0.0 - Production on 星期三 12月 3 21:00:19 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. 连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-39002: 操作无效 ORA-39059: 转储文件集不完整 ORA-39246: 无法在提供的转储文件中定位主表
分析当时当初的dmp日志,由于expdp的job表所在表空间不足导致expdp导出失败
TABLE:"XIFENFEI"."EOM_MEASURE_POINT" ORA-30032: 挂起的 (可恢复) 语句已超时 ORA-01691: Lob 段 XIFENFEI.SYS_LOB0000161267C00111$$ 无法通过 32 (在表空间 NNC_DATA01 中) 扩展 ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: 在 "SYS.KUPW$WORKER", line 12620 ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: 在 "SYS.KUPW$WORKER", line 11414 ----- PL/SQL Call Stack ----- object line object handle number name 0xda5dae50 33476 package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION 0xda5dae50 12641 package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR 0xda5dae50 11602 package body SYS.KUPW$WORKER.CREATE_OBJECT_ROWS 0xda5dae50 15268 package body SYS.KUPW$WORKER.FETCH_XML_OBJECTS 0xda5dae50 3907 package body SYS.KUPW$WORKER.UNLOAD_METADATA 0xda5dae50 13736 package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS 0xda5dae50 2429 package body SYS.KUPW$WORKER.MAIN 0x6524a4f0 2 anonymous block KUPW: Object row index into parse items is: 1 KUPW: Parse item count is: 19 KUPW: In function CHECK_FOR_REMAP_NETWORK KUPW: Nothing to remap KUPW: In procedure BUILD_OBJECT_STRINGS - non-base info KUPW: In procedure BUILD_SUBNAME_LIST with TABLE:XIFENFEI.EOM_MEASURE_POINT KUPW: In function NEXT_PO_NUMBER KUPW: PO number assigned: 34198 FORALL KUPW: In procedure DETERMINE_FATAL_ERROR with ORA-30032: 挂起的 (可恢复) 语句已超时 ORA-01691: Lob 段 XIFENFEI.SYS_LOB0000161267C00111$$ 无法通过 32 (在表空间 NNC_DATA01 中) 扩展 作业 "XIFENFEI"."SYS_EXPORT_SCHEMA_01" 因致命错误于 星期一 12月 1 06:33:21 2025 elapsed 0 04:03:18 停止
从导出日志看,在导出大量”0 KB 0 行”记录之后提示表空间不足,expdp的job表无法扩展导致导出挂起然后超时导出终止(这个导出操作没有完全完成),从而在导入的时候出现了ORA-39059: 转储文件集不完整 ORA-39246: 无法在提供的转储文件中定位主表 的错误.对于这种故障,分析导出日志,发现运气不错,所有有数据的表都导出完成,基于这个心中就有了第一层底气,所有表数据不会丢失(因为都导出到了这个dmp中),但是非表的字典数据不完整,要想业务完整跑起来,需要找到一个完整的业务字典信息.对于大量的备份dmp被删除,然后对应分区还写入了很多数据,只能尝试看运气,通过对磁盘文件镜像,然后进行反删除恢复,找出来一个11月26日的dmp的压缩文件是完整的
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle dul 最新版(12.2.0.2.10)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
Oracle官方的数据文件离线unload工具Data UnLoader(DUL),依旧保持强盛生命力,虽然开发者Bernard van Duijnen已经退休,但是产品依旧保持更新,这个唯一一款从Oracle 6开始支持的离线unload数据的产品.
[oracle@www.xifenfei.com dul]$ ./dul
Data UnLoader: 12.2.0.2.10 - Internal Only - on Sun Aug 31 21:04:44 2025
with 64-bit io functions and the decompression option
Copyright (c) 1994 2025 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal Use Only
DUL: Warning: ulimit process stack size is only 33554432
Found db_id = 1780931490
Found db_name = XIFENFEI
DUL> bootstrap;
Probing file = 1, block = 520
. unloading table BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
60 rows unloaded
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 11
OBJ$: segobjno 18, file 1 block 240
TAB$: segobjno 2, tabno 1, file 1 block 144
COL$: segobjno 2, tabno 5, file 1 block 144
USER$: segobjno 10, tabno 1, file 1 block 208
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$ 86361 rows unloaded
. unloading table TAB$ 2898 rows unloaded
. unloading table COL$ 94610 rows unloaded
. unloading table USER$ 86 rows unloaded
Reading USER.dat 86 entries loaded
Reading OBJ.dat 86361 entries loaded and sorted 86361 entries
Reading TAB.dat 2898 entries loaded
Reading COL.dat 94610 entries loaded and sorted 94610 entries
Reading BOOTSTRAP.dat 60 entries loaded
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 11
OBJ$: segobjno 18, file 1 block 240
TAB$: segobjno 2, tabno 1, file 1 block 144
COL$: segobjno 2, tabno 5, file 1 block 144
USER$: segobjno 10, tabno 1, file 1 block 208
TABPART$: segobjno 591, file 1 block 4000
INDPART$: segobjno 596, file 1 block 4040
TABCOMPART$: segobjno 613, file 1 block 4176
INDCOMPART$: segobjno 618, file 1 block 4216
TABSUBPART$: segobjno 603, file 1 block 4096
INDSUBPART$: segobjno 608, file 1 block 4136
IND$: segobjno 2, tabno 3, file 1 block 144
ICOL$: segobjno 2, tabno 4, file 1 block 144
LOB$: segobjno 2, tabno 6, file 1 block 144
COLTYPE$: segobjno 2, tabno 7, file 1 block 144
TYPE$: segobjno 518, tabno 1, file 1 block 3464
COLLECTION$: segobjno 518, tabno 2, file 1 block 3464
ATTRIBUTE$: segobjno 518, tabno 3, file 1 block 3464
LOBFRAG$: segobjno 624, file 1 block 4264
LOBCOMPPART$: segobjno 627, file 1 block 4288
UNDO$: segobjno 15, file 1 block 224
TS$: segobjno 6, tabno 2, file 1 block 176
PROPS$: segobjno 98, file 1 block 800
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
86361 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
2898 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
94610 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
86 rows unloaded
. unloading table TABPART$ 104 rows unloaded
. unloading table INDPART$ 126 rows unloaded
. unloading table TABCOMPART$ 1 row unloaded
. unloading table INDCOMPART$ 0 rows unloaded
. unloading table TABSUBPART$ 32 rows unloaded
. unloading table INDSUBPART$ 0 rows unloaded
. unloading table IND$ 4931 rows unloaded
. unloading table ICOL$ 7644 rows unloaded
. unloading table LOB$ 1031 rows unloaded
. unloading table COLTYPE$ 2565 rows unloaded
. unloading table TYPE$ 2909 rows unloaded
. unloading table COLLECTION$ 1002 rows unloaded
. unloading table ATTRIBUTE$ 11328 rows unloaded
. unloading table LOBFRAG$ 1 row unloaded
. unloading table LOBCOMPPART$ 0 rows unloaded
. unloading table UNDO$ 21 rows unloaded
. unloading table TS$ 6 rows unloaded
. unloading table PROPS$ 36 rows unloaded
Reading USER.dat 86 entries loaded
Reading OBJ.dat 86361 entries loaded and sorted 86361 entries
Reading TAB.dat 2898 entries loaded
Reading COL.dat 94610 entries loaded and sorted 94610 entries
Reading TABPART.dat 104 entries loaded and sorted 104 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 126 entries loaded and sorted 126 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 4931 entries loaded
Reading LOB.dat
DUL: Notice: Increased the size of DC_LOBS from 1024 to 8192 entries
1031 entries loaded
Reading ICOL.dat 7644 entries loaded
Reading COLTYPE.dat 2565 entries loaded
Reading TYPE.dat 2909 entries loaded
Reading ATTRIBUTE.dat 11328 entries loaded
Reading COLLECTION.dat 1002 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 6 entries loaded
Reading PROPS.dat 36 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
DUL> unload table sys.obj$;
. unloading table OBJ$ 86361 rows unloaded
DUL>
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：joxcsys: release mismatch, 19.0.0.0.0 1.8 in database (classes.bin)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
expdp导出数据报ORA-39127 ORA-29548错误
ORA-39127: 调用 BEGIN :1 := SYS.DBMS_JAVA.START_EXPORT(:2, :3, :4, :5, :6, :7, :8, :9 ,:10); END; 时发生意外错误 ORA-29548: Java 系统类报告: release of Java system classes in the database (19.0.0.0.0 1.8) does not match that of the o racle executable (19.3.0.0.0 1.8) ORA-06512: 在 "SYS.DBMS_JAVA", line 520 ORA-06512: 在 line 1 ORA-06512: 在 "SYS.DBMS_JAVA", line 520 ORA-06512: 在 line 1 ORA-06512: 在 "SYS.DBMS_METADATA", line 11827 ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
对应的alert日志报错
2025-08-14T11:37:10.886745+08:00 joxcsys: release mismatch, 19.0.0.0.0 1.8 in database (classes.bin) vs 19.3.0.0.0 1.8 in executable pid 2624 cid 0 joxcsys: release mismatch, 19.0.0.0.0 1.8 in database (classes.bin) vs 19.3.0.0.0 1.8 in executable pid 2624 cid 0 2025-08-14T11:37:11.379211+08:00 Resize operation completed for file# 68, old size 83776K, new size 84800K Resize operation completed for file# 68, old size 84800K, new size 85824K 2025-08-14T11:37:12.333138+08:00 joxcsys: release mismatch, 19.0.0.0.0 1.8 in database (classes.bin) vs 19.3.0.0.0 1.8 in executable pid 2624 cid 0 joxcsys: release mismatch, 19.0.0.0.0 1.8 in database (classes.bin) vs 19.3.0.0.0 1.8 in executable pid 2624 cid 0 joxcsys: release mismatch, 19.0.0.0.0 1.8 in database (classes.bin) vs 19.3.0.0.0 1.8 in executable pid 2624 cid 0 joxcsys: release mismatch, 19.0.0.0.0 1.8 in database (classes.bin) vs 19.3.0.0.0 1.8 in executable pid 2624 cid 0
数据库版本（19.3版本,没有打任何其他patch）
C:\Users\Administrator>sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on 星期四 8月 14 11:17:08 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. 连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
出现这个错误一般是由于Oracle JVM组件和Oracle JVM库不同步导致,对于这个初始版本（没有打任何patch）,理论上不应该发生该问题,既然已经发生了,那就解决问题
SQL> startup upgrade;
ORACLE 例程已经启动。
Total System Global Area 1.5435E+10 bytes
Fixed Size 16124312 bytes
Variable Size 4563402752 bytes
Database Buffers 1.0838E+10 bytes
Redo Buffers 17428480 bytes
数据库装载完毕。
数据库已经打开。
SQL> alter system set java_jit_enabled = FALSE;
系统已更改。
SQL> alter system set "_system_trig_enabled"=FALSE;
系统已更改。
SQL> alter system set JOB_QUEUE_PROCESSES=0;
系统已更改。
SQL> create or replace java system
2 /
Java 已创建。
SQL> alter system set java_jit_enabled = true;
系统已更改。
SQL> alter system set "_system_trig_enabled"=TRUE;
系统已更改。
SQL> alter system set JOB_QUEUE_PROCESSES=100;
系统已更改。
SQL> @?/rdbms/admin/utlrp.sql
会话已更改。
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2025-08-14 12:42:59
……………………
PL/SQL 过程已成功完成。
SQL> SELECT dbms_java.longname('TEST') long_name FROM dual;
LONG_NAME
--------------------------------------------------------------------------------
TEST
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 1.5435E+10 bytes
Fixed Size 16124312 bytes
Variable Size 4563402752 bytes
Database Buffers 1.0838E+10 bytes
Redo Buffers 17428480 bytes
数据库装载完毕。
数据库已经打开。
如果有其他情况,具体参考:ORA-29548 Error While Running Java Code or Datapatch In Database (Doc ID 2934546.1)文档
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
最近一个运行在win平台的rac,由于断电之后,集群两个节点均无法正常启动,客户进行了一系列尝试,结果到了ora-600 kclchkblk_4错误无法继续.
通过对数据库日志分析,回溯了故障大概的原因,启动的时候报错为:
节点1启动报错
Sun Aug 03 15:21:22 2025 alter database open This instance was first to open Beginning crash recovery of 2 threads parallel recovery started with 32 processes Started redo scan Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_7108.trc: ORA-00314: 日志 11 (用于线程 2) 要求的 sequence# 147717 与 147541 不匹配 ORA-00312: 联机日志 11 线程 2: 'D:\REDOLOG\REDO011.LOG' Abort recovery for domain 0 Aborting crash recovery due to error 314 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_7108.trc: ORA-00314: 日志 11 (用于线程 2) 要求的 sequence# 147717 与 147541 不匹配 ORA-00312: 联机日志 11 线程 2: 'D:\REDOLOG\REDO011.LOG' Abort recovery for domain 0 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_7108.trc: ORA-00314: 日志 11 (用于线程 2) 要求的 sequence# 147717 与 147541 不匹配 ORA-00312: 联机日志 11 线程 2: 'D:\REDOLOG\REDO011.LOG' ORA-314 signalled during: alter database open...
节点2启动报错
Sat Aug 02 15:45:43 2025
Successful mount of redo thread 2, with mount id 1735887907
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Lost write protection disabled
Completed: ALTER DATABASE MOUNT /* db agent *//* {1:47460:124} */
ALTER DATABASE OPEN /* db agent *//* {1:47460:124} */
This instance was first to open
Beginning crash recovery of 2 threads
Sat Aug 02 15:45:49 2025
parallel recovery started with 32 processes
Started redo scan
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_3444.trc:
ORA-00314: ?? 1 (???? 1) ??? sequence# 67782 ? 60818 ???
ORA-00312: ???? 1 ?? 1: 'D:\REDOLOG\REDO01.LOG'
Abort recovery for domain 0
Aborting crash recovery due to error 314
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_3444.trc:
ORA-00314: ?? 1 (???? 1) ??? sequence# 67782 ? 60818 ???
ORA-00312: ???? 1 ?? 1: 'D:\REDOLOG\REDO01.LOG'
Abort recovery for domain 0
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_3444.trc:
ORA-00314: ?? 1 (???? 1) ??? sequence# 67782 ? 60818 ???
ORA-00312: ???? 1 ?? 1: 'D:\REDOLOG\REDO01.LOG'
ORA-314 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:47460:124} */...
看到这两个报错信息得出两个结论：
1）比较明显节点1需要thead 2的 group 11 sequence为147717,但是实际group 11文件的sequence为147541；而节点2启动需要thread 1的group 1 sequence为67782，但是实际中group 1文件的sequnece为60818,这两个都相差比较多,属于非正常的情况,很可能是文件本身有问题
2）这是一套win的rac架构,理论上redo应该在共享文件系统（一般是asm中)，而这个第一感觉很可能是本地文件系统中
客户当时恢复之时查询信息截图
查看了两个节点的最后redo切换信息
--节点1 Sat Aug 02 10:49:31 2025 Thread 1 advanced to log sequence 67782 (LGWR switch) Current log# 1 seq# 67782 mem# 0: D:\REDOLOG\REDO01.LOG --节点2（redo每组2G,节点2长时间没跑业务,之时做数据库导出操作,所以切换时间比较久远） Sat Jul 26 16:56:42 2025 Thread 2 advanced to log sequence 147717 (LGWR switch) Current log# 11 seq# 147717 mem# 0: D:\REDOLOG\REDO011.LOG
并查看两个机器d:/redolog信息(客户自行resetlogs之后的,非第一现场,但是可以确认两个节点各自有一份redo文件
本来这个是一个比较小的故障,只要把节点2的thread 1的redo拷贝到到节点1或者节点1的thread 2的redo拷贝到节点2,然后正常open库即可,现场恢复对rac不太熟悉,直接按照互联网上检索的处理方法,加上_allow_resetlogs_corruption然后强制拉库,结果不太幸运,拉库失败报ORA-600 kclchkblk_4错误
Sun Aug 03 18:59:24 2025 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 21497084214 Resetting resetlogs activation ID 1543012633 (0x5bf88119) Sun Aug 03 18:59:46 2025 Setting recovery target incarnation to 3 Sun Aug 03 18:59:46 2025 This instance was first to open Picked broadcast on commit scheme to generate SCNs Sun Aug 03 18:59:48 2025 Assigning activation ID 1735960667 (0x6778a85b) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: D:\REDOLOG\REDO01.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sun Aug 03 18:59:49 2025 SMON: enabling cache recovery Instance recovery: looking for dead threads Instance recovery: lock domain invalid but no dead threads Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_8508.trc (incident=728324): ORA-00600: 内部错误代码, 参数: [kclchkblk_4], [5], [200595988], [5], [22247740], [], [], [], [], [], [], [] Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\incident\incdir_728324\orcl1_ora_8508_i728324.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Sun Aug 03 18:59:53 2025 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_8508.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00600: 内部错误代码, 参数: [kclchkblk_4], [5], [200595988], [5], [22247740], [], [], [], [], [], [], [] Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_8508.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00600: 内部错误代码, 参数: [kclchkblk_4], [5], [200595988], [5], [22247740], [], [], [], [], [], [], [] Error 704 happened during db open, shutting down database USER (ospid: 8508): terminating the instance due to error 704 Sun Aug 03 18:59:54 2025 opiodr aborting process unknown ospid (9480) as a result of ORA-1092 Sun Aug 03 19:00:09 2025 Instance terminated by USER, pid = 8508 ORA-1092 signalled during: alter database open resetlogs... opiodr aborting process unknown ospid (8508) as a result of ORA-1092
这个故障之后,客户那边无法自行恢复,让我这边介入处理,对于这个错误以前处理比较多,一般就是scn问题,通过Patch SCN小工具快速解决
数据库open成功之后主要报一些ORA-600 4137,ORA-600 6006等错误
Database Characterset is ZHS16GBK Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_8300.trc (incident=1176205): ORA-00600: 内部错误代码, 参数: [4137], [1.14.2713957], [0], [0], [], [], [], [], [], [], [], [] Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\incident\incdir_1176205\orcl1_smon_8300_i1176205.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Fri Aug 08 19:03:14 2025 ORACLE Instance orcl1 (pid = 25) - Error 600 encountered while recovering transaction (1, 14). Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_8300.trc: ORA-00600: 内部错误代码, 参数: [4137], [1.14.2713957], [0], [0], [], [], [], [], [], [], [], [] Fri Aug 08 19:03:15 2025 ORACLE Instance orcl1 (pid = 25) - Error 600 encountered while recovering transaction (5, 19). Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_8300.trc: ORA-00600: 内部错误代码, 参数: [4137], [5.19.2318502], [0], [0], [], [], [], [], [], [], [], [] Starting background process MMON Fri Aug 08 19:03:18 2025 MMON started with pid=29, OS id=4624 Fri Aug 08 19:03:19 2025 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_8300.trc (incident=1176207): ORA-00600: 内部错误代码, 参数: [6006], [1], [], [], [], [], [], [], [], [], [], [] Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\incident\incdir_1176207\orcl1_smon_8300_i1176207.trc Starting background process MMNL Fri Aug 08 19:03:19 2025 MMNL started with pid=30, OS id=8344 Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. ORACLE Instance orcl1 (pid = 25) - Error 600 encountered while recovering transaction (46, 28) on object 197344. Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_8300.trc: ORA-00600: 内部错误代码, 参数: [6006], [1], [], [], [], [], [], [], [], [], [], []
通过重建undo解决该错误,数据库稳定运行,没有再crash和报明显错误,导出核心数据,完成本次恢复任务.
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户一个比较久远系统,由于长期没有人维护,导致硬件故障,客户找人进行了硬件恢复之后,发现大量数据文件为0kb
Corrupt block 653695 found during reading backup piece, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, corr_type=-2 Reread of blocknum=653695, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, found same corrupt data Reread of blocknum=653695, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, found same corrupt data Reread of blocknum=653695, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, found same corrupt data Reread of blocknum=653695, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, found same corrupt data Reread of blocknum=653695, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, found same corrupt data Continuing reading piece H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, no other copies available. Fri Jun 06 14:23:26 2025 Cannot read block 1 from S:\DBFILES\BACKUP\ORA_DF1080446471_S8590_S1 - restore failover to read from H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1 ORA-19505: 无法识别文件"S:\DBFILES\BACKUP\ORA_DF1080446471_S8590_S1" ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Full restore complete of datafile 2 to datafile copy H:\BAIDUNETDISK\BACKUP\BACKUP\2_SYSAUX01.DBF.Elapsed time: 0:00:04 checkpoint is 16694678523790 Full restore complete of datafile 1 to datafile copy H:\BAIDUNETDISK\BACKUP\BACKUP\1_SYSTEM01.DBF.Elapsed time: 0:00:05 checkpoint is 16694678523790 Undo Optimization current scn is 16694646809619 Fri Jun 06 14:23:47 2025 Datafile rdba reconstruction error, expected block greater than 3305201, got 3304960 for datafile 4 Corrupt block 3746806 found during reading backup piece, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, corr_type=4 Datafile tail reconstruction error, expected tail of 0, got -1601108480 for datafile 4 ……………… Corrupt block 4290319 found during reading backup piece, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, corr_type=-2 Reread of blocknum=4290319, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, found same corrupt data Reread of blocknum=4290319, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, found same corrupt data Reread of blocknum=4290319, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, found same corrupt data Reread of blocknum=4290319, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, found same corrupt data Reread of blocknum=4290319, file=H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, found same corrupt data Continuing reading piece H:\BAIDUNETDISK\ORA_DF1080446471_S8590_S1, no other copies available. Fri Jun 06 16:01:21 2025 Hex dump of (file 4, block 1) in trace file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_15808.trc Corrupt block relative dba: 0x01000001 (file 4, block 1) Bad check value found during deleting datafile copy Data in bad block: type: 0 format: 2 rdba: 0x01000001 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000001 check value in block header: 0x0 computed block checksum: 0xa601 Reread of blocknum=1, file=H:\BAIDUNETDISK\BACKUP\BACKUP\4_USERS01.DBF. found valid data Switch of datafile 4 complete to datafile copy checkpoint is 16126
很明显还原出来的system/sysaux文件可能还可以使用,但是users01.dbf肯定不行(从checkpoint is SCN)可以判断出来(users01.dbf是初始化出来的),基于这种情况,利用当前的system和sysaux打开数据库
Fri Jun 13 22:05:31 2025 Media Recovery failed with error 1610 Fri Jun 13 22:05:31 2025 Signalling error 1152 for datafile 1! Signalling error 1152 for datafile 2! Signalling error 1152 for datafile 3! Signalling error 1152 for datafile 4! Checker run found 5 new persistent data failures Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER database until cancel ... Fri Jun 13 22:05:49 2025 ALTER DATABASE RECOVER database using backup controlfile Media Recovery Start started logmerger process Parallel Media Recovery started with 20 slaves Fri Jun 13 22:05:49 2025 Warning: Datafile 3 (H:\BAIDUNETDISK\BACKUP\BACKUP\3_UNDOTBS01.DBF) is offline during full database recovery and will not be recovered ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile ALTER DATABASE RECOVER CANCEL Media Recovery Canceled Completed: ALTER DATABASE RECOVER CANCEL Fri Jun 13 22:06:04 2025 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 16694678523790 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员 ORA-00312: 联机日志 1 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO01.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 2 (用于线程 1) 的成员 ORA-00312: 联机日志 2 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO02.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员 ORA-00312: 联机日志 3 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO03.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员 ORA-00312: 联机日志 1 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO01.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Clearing online redo logfile 1 H:\BAIDUNETDISK\BACKUP\BACKUP\REDO01.LOG Clearing online log 1 of thread 1 sequence number 33772 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员 ORA-00312: 联机日志 1 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO01.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员 ORA-00312: 联机日志 1 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO01.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Clearing online redo logfile 1 complete Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 2 (用于线程 1) 的成员 ORA-00312: 联机日志 2 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO02.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Clearing online redo logfile 2 H:\BAIDUNETDISK\BACKUP\BACKUP\REDO02.LOG Clearing online log 2 of thread 1 sequence number 33773 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 2 (用于线程 1) 的成员 ORA-00312: 联机日志 2 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO02.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 2 (用于线程 1) 的成员 ORA-00312: 联机日志 2 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO02.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Clearing online redo logfile 2 complete Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员 ORA-00312: 联机日志 3 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO03.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Clearing online redo logfile 3 H:\BAIDUNETDISK\BACKUP\BACKUP\REDO03.LOG Clearing online log 3 of thread 1 sequence number 33771 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员 ORA-00312: 联机日志 3 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO03.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc: ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员 ORA-00312: 联机日志 3 线程 1: 'H:\BAIDUNETDISK\BACKUP\BACKUP\REDO03.LOG' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Clearing online redo logfile 3 complete Resetting resetlogs activation ID 1596759182 (0x5f2c9c8e) Online log H:\BAIDUNETDISK\BACKUP\BACKUP\REDO01.LOG: Thread 1 Group 1 was previously cleared Online log H:\BAIDUNETDISK\BACKUP\BACKUP\REDO02.LOG: Thread 1 Group 2 was previously cleared Online log H:\BAIDUNETDISK\BACKUP\BACKUP\REDO03.LOG: Thread 1 Group 3 was previously cleared Fri Jun 13 22:06:05 2025 Setting recovery target incarnation to 2 Fri Jun 13 22:06:05 2025 Assigning activation ID 1908542329 (0x71c20b79) LGWR: STARTING ARCH PROCESSES Fri Jun 13 22:06:05 2025 ARC0 started with pid=21, OS id=3372 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Fri Jun 13 22:06:06 2025 ARC1 started with pid=22, OS id=14764 Fri Jun 13 22:06:06 2025 ARC2 started with pid=23, OS id=9156 Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: H:\BAIDUNETDISK\BACKUP\BACKUP\REDO01.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Fri Jun 13 22:06:06 2025 ARC3 started with pid=24, OS id=24080 ARC1: Archival started ARC2: Archival started ARC2: Becoming the 'no FAL' ARCH ARC2: Becoming the 'no SRL' ARCH ARC1: Becoming the heartbeat ARCH Fri Jun 13 22:06:07 2025 SMON: enabling cache recovery Undo initialization finished serial:0 start:160589734 end:160589750 diff:16 (0 seconds) Dictionary check beginning File #3 is offline, but is part of an online tablespace. data file 3: 'H:\BAIDUNETDISK\BACKUP\BACKUP\3_UNDOTBS01.DBF' File #4 is offline, but is part of an online tablespace. data file 4: 'H:\BAIDUNETDISK\BACKUP\BACKUP\4_USERS01.DBF' Fri Jun 13 22:06:07 2025 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_dbw0_8352.trc: ORA-01157: ????/?????? 201 - ??? DBWR ???? ORA-01110: ???? 201: 'H:\BAIDUNETDISK\BACKUP\BACKUP\TEMP01.DBF' ORA-27041: ?????? OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件 Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_dbw0_8352.trc: ORA-01186: ?? 201 ?????? ORA-01157: ????/?????? 201 - ??? DBWR ???? ORA-01110: ???? 201: 'H:\BAIDUNETDISK\BACKUP\BACKUP\TEMP01.DBF' File 201 not verified due to error ORA-01157 Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Re-creating tempfile H:\BAIDUNETDISK\BACKUP\BACKUP\TEMP01.DBF Database Characterset is AL32UTF8 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Fri Jun 13 22:06:07 2025 QMNC started with pid=25, OS id=20288 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Completed: alter database open resetlogs
导出需要的业务用户字典信息,然后把客户那边提供的users01.dbf文件(users02.dbf是客户在21年之后增加的,原则上客户要的数据都在users01.dbf中)中的数据恢复到导出的字典中,完成本次数据恢复,客户远程验证业务,运行正常,客户需要的配置信息都在其中.
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
最近一个客户和我反馈,他们创建了一个只读用户(之时给了create session和select表权限),但是其中有部分表可以执行dml操作,我登录系统进行确认
SQL> SELECT PRIVILEGE, ADMIN_OPTION 2 FROM DBA_SYS_PRIVS 3 WHERE GRANTEE = 'ALL_READONLY' 4 UNION 5 SELECT PRIVILEGE, ADMIN_OPTION 6 FROM ROLE_SYS_PRIVS 7 WHERE ROLE IN 8 (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'ALL_READONLY') 9 UNION 10 SELECT PRIVILEGE, ADMIN_OPTION 11 FROM ROLE_SYS_PRIVS 12 WHERE ROLE IN (SELECT GRANTED_ROLE 13 FROM ROLE_ROLE_PRIVS 14 WHERE ROLE IN (SELECT GRANTED_ROLE 15 FROM DBA_ROLE_PRIVS 16 WHERE GRANTEE = 'ALL_READONLY')); PRIVILEGE ADM ---------------------------------------- --- CREATE SESSION NO
尝试对一个表做dml操作,确实可以对u1.t1表进行dml操作
SQL> conn all_readonly/PASSWORD Connected. SQL> update U1.T1 set SNAME='111_test' where sid='www.xifenfei.com'; 1 row updated. SQL> rollback; Rollback complete.
查看这个表的相关授权,关于all_readonly(只读用户)的授权,也确实只是授权了查询权限
SQL> SELECT GRANTEE,PRIVILEGE,OWNER,TABLE_NAME FROM dba_TAB_PRIVS WHERE TABLE_NAME ='T1' and GRANTEE='ALL_READONLY' GRANTEE PRIVILEGE OWNER TABLE_NAME -------------------- ---------------------------------------- -------------------- -------------------- ALL_READONLY SELECT U1 T1
既然t1这个表可以被dml操作,那是这个表是否还有其他授权,进一步查询该表授权(不限于ALL_REAONLY用户)
SQL> SELECT GRANTEE,PRIVILEGE,OWNER,TABLE_NAME FROM dba_TAB_PRIVS WHERE TABLE_NAME ='T1'; GRANTEE PRIVILEGE OWNER TABLE_NAME -------------------- ---------------------------------------- -------------------- -------------------- PUBLIC ALTER U1 T1 PUBLIC DELETE U1 T1 PUBLIC INDEX U1 T1 PUBLIC INSERT U1 T1 PUBLIC SELECT U1 T1 PUBLIC UPDATE U1 T1 PUBLIC REFERENCES U1 T1 PUBLIC ON COMMIT REFRESH U1 T1 PUBLIC QUERY REWRITE U1 T1 PUBLIC DEBUG U1 T1 PUBLIC FLASHBACK U1 T1 ALL_READONLY SELECT U1 T1 14 rows selected.
这下明确了,由于授权了u1.t1表的(insert,delete,update等)权限给public,导致其他用户也可以对这些表进行授权给public的所有操作.
不管任何原因,都不建议授权表/对象的操作给public,这样会导致登录该数据库的所有用户都具有这个权限,风险不可控
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在最新的”当前数据库版本的发行时间表 (Doc ID 1626244.1)”文档中,oracle官方更新的数据库产品的支持周期,最重要的一点就是oracle 19c标准服务延期到2029年12月31日
| 版本 | 补丁结束日期 | 注意和例外 |
| 23ai
Long Term Release |
Premier Support – 2032年12月31日
Extended Support - 时间待定 |
|
| 21c
Innovation Release |
2027年7月31日 |
|
| 19c
Long Term Release |
2029年12月31日，没有ES/ULA
2032年12月31日，有ES/ULA
|
|
| 18c
Innovation Release
|
2021年6月30日 |
|
| 12.2.0.1
|
2022年3月31日
Upgrade Support (Restricted Availability) Jan 1, 2024- Dec 31, 2025 - 具体请联系 CSS |
|
| 12.1.0.2
最终版本 |
2022年7月31日，有付费的ES, ULA, 或者减免费用的 EBS
Dec 31, 2025 (Upgrade Support (Restricted Availability)- 具体请联系 CSS) |
|
| 12.1.0.1 | 2016年8月31日 |
|
| 11.2.0.4
最终版本 for 11.2 |
|
|
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户aix环境硬盘异常导致系统无法启动,初步判断是数据文件存放在本地磁盘的空间中(本地两个盘都异常,系统无法启动),通过硬件恢复厂商镜像出来,但是通过aix文件系统直接挂载提示需要fsck,但是做fsck之后,提示大量文件丢失(最关键的数据文件和备份文件都被自动删除)
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：使用sid方式直接访问pdb(USE_SID_AS_SERVICE_LISTENER)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有些应用,因为特殊原因,需要通过sid来访问数据库,在pdb环境中原则上都是通过服务名访问的,可以通过一定的监听配置实现使用pdb名的sid来访问该pdb
在pdb0中创建u_test用户并授权
[oracle@ora19c:/u01/app/oracle/product/19.3.0/db/network/admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 17 22:01:54 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
sys@ORA19C 22:01:54> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB0 READ WRITE NO
4 PDBXXX MOUNTED
sys@ORA19C 22:01:56> alter session set container=pdb0;
Session altered.
Elapsed: 00:00:00.16
sys@ORA19C 22:02:07> create user u_test identified by oracle;
User created.
Elapsed: 00:00:00.29
sys@ORA19C 22:02:21> grant dba to u_test;
Grant succeeded.
Elapsed: 00:00:00.01
监听的配置和状态
[oracle@ora19c:/home/oracle]$ cat /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
[oracle@ora19c:/home/oracle]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2025 22:07:12
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 17-FEB-2025 22:06:39
Uptime 0 days 0 hr. 0 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "21b067cbda1dbcd4e0630100007f12b6" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "22394b20557aff3ee0630100007fafe0" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19c" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19cXDB" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "pdb0" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "pdbxxx" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
The command completed successfully
创建pdb0基于服务和sid的tns(pdb0,pdb0_sid)
[oracle@ora19c:/u01/app/oracle/product/19.3.0/db/network/admin]$ cat tnsnames.ora
pdb0 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb0)
)
)
pdb0_sid =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(sid = pdb0)
)
)
[oracle@ora19c:/u01/app/oracle/product/19.3.0/db/network/admin]$ tnsping pdb0
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2025 22:03:00
Copyright (c) 1997, 2024, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.3.0/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb0)))
OK (0 msec)
[oracle@ora19c:/u01/app/oracle/product/19.3.0/db/network/admin]$ tnsping pdb0_sid
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2025 22:03:10
Copyright (c) 1997, 2024, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.3.0/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (sid = pdb0)))
OK (0 msec)
分别测试pdb0和pdb0_sid访问数据库
测试证明基于服务名的方式可以正常访问pdb,基于sid的方式无法访问pdb
[oracle@ora19c:/home/oracle]$ sqlplus u_test/oracle@pdb0 SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 17 22:08:35 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Mon Feb 17 2025 22:06:11 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0 u_test@PDB0 22:08:35> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0 [oracle@ora19c:/home/oracle]$ sqlplus u_test/oracle@pdb0_sid SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 17 22:08:39 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. ERROR: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
在listener.ora中增加USE_SID_AS_SERVICE_LISTENER = ON,并reload加载
注意:USE_SID_AS_SERVICE_LISTENER 中的LISTENER根据不同的监听名字而发生改变
[oracle@ora19c:/home/oracle]$ cat /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
USE_SID_AS_SERVICE_LISTENER = ON
[oracle@ora19c:/home/oracle]$ lsnrctl reload
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2025 22:12:13
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
The command completed successfully
[oracle@ora19c:/home/oracle]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2025 22:13:05
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 17-FEB-2025 22:06:39
Uptime 0 days 0 hr. 6 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "21b067cbda1dbcd4e0630100007f12b6" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "22394b20557aff3ee0630100007fafe0" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19c" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19cXDB" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "pdb0" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "pdbxxx" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
The command completed successfully
尝试tns名字为pdb0和pdb0_sid名字登录数据库
在listener.ora文件中设置了USE_SID_AS_SERVICE_LISTENER = ON之后,基于sid的方式可以直接访问pdb
[oracle@ora19c:/home/oracle]$ sqlplus u_test/oracle@pdb0_sid SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 17 22:12:16 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Mon Feb 17 2025 22:08:35 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0 u_test@PDB0 22:12:16> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0 [oracle@ora19c:/home/oracle]$ sqlplus u_test/oracle@pdb0 SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 17 22:12:28 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Mon Feb 17 2025 22:12:16 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0 u_test@PDB0 22:12:28>