Oracle – Database SOS

客户一个比较久远系统,由于长期没有人维护,导致硬件故障,客户找人进行了硬件恢复之后,发现大量数据文件为0kb

客户这个系统是17年上线,19年进行了一次升级,提出要求,只要能够恢复到19年升级之后的系统状态即可(因为是制造业系统,大量配置信息在里,至于后续产生的数据,无所谓),基于目前的数据文件情况,肯定无法恢复出来(因为字典数据在system01.dbf中)
基于这种情况,我这边在客户恢复的整个目录文件中,再三查找,发现了一个类似rman备份的文件(是21年的),对其进行还原尝试

在还原过程中发现大量坏块,没有办法,最后只能采用一些方法强制rman还原出来备份中的部分文件

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中)中的数据恢复到导出的字典中,完成本次数据恢复,客户远程验证业务,运行正常,客户需要的配置信息都在其中.

表dml操作权限授权给public,导致只读用户失效

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

标题：表dml操作权限授权给public,导致只读用户失效

最近一个客户和我反馈,他们创建了一个只读用户(之时给了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,这样会导致登录该数据库的所有用户都具有这个权限,风险不可控

当前主流数据库版本服务支持周期-202503

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

标题：当前主流数据库版本服务支持周期-202503

在最新的”当前数据库版本的发行时间表 (Doc ID 1626244.1)”文档中,oracle官方更新的数据库产品的支持周期,最重要的一点就是oracle 19c标准服务延期到2029年12月31日
DBROADMAP12-2-2024

版本

补丁结束日期

注意和例外

23ai

Long Term Release

Premier Support – 2032年12月31日

Extended Support - 时间待定

由于此次发布中突破性人工智能技术的重要性，我们将其从 Oracle 数据库 23c 重新命名为 Oracle 数据库 23ai
现在可在云上、Oracle Exadata 和 Oracle Database Appliance 中使用。请参阅下方的 Oracle 数据库日程表中的详细信息
Premier Support将于 2031 年 12 月 31 日结束
通过扩展支持或者ULA进行错误修正/补丁的截止日期待定

21c

Innovation Release

2027年7月31日

错误更正/补丁有效期至2027年7月31日
21c没有资格申请 Extended Support(ES)
21c只提供 Release Updates (RUs) 补丁
21c不适用于Exadata Database Service

19c

Long Term Release

2029年12月31日，没有ES/ULA

2032年12月31日，有ES/ULA

Premier Support（PS）将于2029年12月31日结束。扩展支持（ES）将从2030年1月1日持续到2032年12月31日。
错误更正/补丁，付费的ES可到2032年12月31日；没有付费的ES，有效期到2029年12月31日
从 2022 年 10 月的补丁周期开始，19.17.0 及更高版本将不再提供 19c RUR。在 2023 年 1 月交付 Oracle Database 19c RUR 19.16.2 之后，不会在任何平台上交付额外的 RUR。更多细节请参考文档 Sunsetting of 19c RURs and FAQ (Doc ID 2898381.1))
为了让客户更频繁地访问推荐和经过良好测试的补丁集合，Oracle 从 2022 年 11 月开始推出 Monthly Recommended Patches (MRP)。 MRP 仅支持 Linux x86-64 平台。(更多细节请参考文档 Introducing Monthly Recommended Patches (MRPs) and FAQ (Doc ID 2898740.1))

18c

Innovation Release

2021年6月30日

错误更正/补丁有效期至2021年6月30日，18c已进入 Sustaining Support 阶段。
18c没有资格申请 Extended Support(ES)
18c 在 Exadata Database Service、Base Database Service 或 Exadata Cloud@Customer 上不受支持。

12.2.0.1

2022年3月31日

Upgrade Support (Restricted Availability) Jan 1, 2024- Dec 31, 2025 - 具体请联系 CSS

这个版本的错误更正/补丁已经结束
12.2.0.1 没有资格申请 Extended Support(ES)
在 Exadata Database Service、Base Database Service 或 Exadata Cloud@Customer 上运行 12.2.0.1 需要购买 Upgrade Support (Restricted Availability)(旧称MDS) 服务

12.1.0.2

最终版本

2022年7月31日，有付费的ES, ULA, 或者减免费用的 EBS

Dec 31, 2025 (Upgrade Support (Restricted Availability)- 具体请联系 CSS)

这个版本的错误更正/补丁已经结束
Premier Support(PS)截止至2018年7月31日，为期一年的免费 Extended Support(ES)有效期至2019年7月31日
从 2019年8月1日至 2022年7月31日，需要ES费用或ULA. 没有付费的 ES or ULA, 补丁截止于 2019年7月31日
我们为电子商务客户提供全球ES uplift 费用减免，详情和到期日期见: Extended Support Fee Waiver for Oracle Database 12.1 and 11.2 for Oracle E-Business Suite (Doc ID 2522948.1) 或技术支持政策文件

Apple Macintosh 平台补丁结束日期为2021年7月31日
微软Windows平台: 对于 12.1.0.2 Database, Oracle 在 Microsoft Windows 2008 上运行 12.1.0.2 Database。这个平台的 end-of-life support 是 January 14, 2020。 甲骨文做出了合理的努力，在2022年7月之前为Windows上的数据库12.1.0.2提供补丁，但这种支持已经过期。
在 Exadata Database Service、Base Database Service 或 Exadata Cloud@Customer 上运行 12.1.0.2 需要购买 Upgrade Support (Restricted Availability)(旧称MDS) 服务

12.1.0.1

2016年8月31日

这个版本的错误更正/补丁已经结束
12.1.0.1 没有资格申请 Extended Support (ES)
12.1.0.1 是 Standard Edition (SE) 和 Standard Edition One (SE1) 的最后一个版本

11.2.0.4

最终版本 for 11.2

2020年12月31日（有偿扩展支持或 ULA扩展支持或减免费用的EBS）
2025年12月31日（Upgrade Support (Restricted Availability) - 具体请联系 CSS)
2021年12月31日适用于OpenVMS平台

Premier Support (PS)截止到2015年1月31日，而为期一年的免费Extended Support(ES)持续到2018年12月31日。
从2019年1月1日开始到2020年12月31日，将需要ES费用或ULA。
Oracle为电子商务客户提供全球ES uplift 费用减免，详情和到期日期见: Extended Support Fee Waiver for Oracle Database 12.1 and 11.2 for Oracle E-Business Suite (Doc ID 2522948.1) 或技术支持政策文件。
第1代 ExaCC, OCC DBCS, and ODA 将拥有额外3个月的支持周期. 这些平台上的数据库的支持周期截止到: 2021年3月31日。可以创建新实例，直到扩展支持终止为止。但是，Oracle不承诺在支持终止后任何11.2.0.4 DBCS实例将继续运行。
市场驱动支持（Market Driven Support）提供以下数据库云服务：第1代和第2代ExaCC，OCC DBCS，OCI DBCS，OCI ExaCS。直到市场驱动支持终止（2021年12月31日），可以创建新实例。 Oracle不承诺在Upgrade Support(旧称MDS)支持终止后任何11.2.0.4 DBCS实例将继续运行。市场驱动支持不适用于PSM-based OCI DBCS,OCI-C DBCS和OCI-C ExaCS。
在 Exadata Database Service、Base Database Service 或 Exadata Cloud@Customer 上运行 11.2.0.4 需要购买 Upgrade Support (Restricted Availability)(旧称MDS) 服务
11.2.0.4是OpenVMS上的最终版本。在2021日历年中，除了标准的专业支持费用之外无需其他费用，客户能够收到重要度1的修复程序和安全更新。涵盖范围不包括新认证，第三方产品或任何Java/JDK功能（包括数据库中嵌入的Java组件）。涵盖范围还不包括与加密和网络加密有关的任何更新。此供应不包括标准的安全补丁更新（SPU）。

aix磁盘损坏oracle数据库恢复

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

标题：aix磁盘损坏oracle数据库恢复

客户aix环境硬盘异常导致系统无法启动,初步判断是数据文件存放在本地磁盘的空间中(本地两个盘都异常,系统无法启动),通过硬件恢复厂商镜像出来,但是通过aix文件系统直接挂载提示需要fsck,但是做fsck之后,提示大量文件丢失(最关键的数据文件和备份文件都被自动删除)
dmp-remove
fsck-remove

基于这种情况,采用镜像主机挂载的方式肯定不行,考虑直接采用软件直接解析,能够看到软件,可惜由于大量的文件系统元数据损坏,解析出来的数据文件和dmp也不可用(大量损坏和空块)
QQ20250307-122939

基于上述情况,只能采用碎片级别恢复出来数据文件
QQ20250307-123123

然后使用dul工具把数据恢复到表中,实现最大限度抢救客户数据
QQ20250307-123653

对于数据库级别恢复,这个是理论上的终极恢复方法

使用sid方式直接访问pdb(USE_SID_AS_SERVICE_LISTENER)

联系：手机/微信(+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>

ORA-600 [4000] [a]相关bug

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

标题：ORA-600 [4000] [a]相关bug

ORA-600 [4000 ] [a]一般是这样的报错格式,其中[a] Undo segment number,类似错误主要bug以及对应的修复版本列表

Bug	Fixed	Description
26966120	18.18, 18.3, 19.1	PDML workload reports ORA-7445 [kdmsfree] / ORA-00600 [4000]
16761566	11.2.0.3.9, 11.2.0.3.BP22, 11.2.0.4, 12.1.0.2, 12.2.0.1	Instance fails to start with ORA-600 [4000] [usn#]
13910190	11.2.0.3.BP15, 11.2.0.4, 12.1.0.1	ORA-600 [4000] from plugged in tablespace in Exadata
37173201	Hitting ORA-600 [4000] during shutdown
36440495	19.26	SECURE FILE LOB CAUSING ORA-00600:[4000]
34547607	19.23, 23.4	[TXN MGMT LOCAL] ORA-600 [ktugct: corruption detected] w/ Compression & RAC DB Instances Crash
32800248	19.24, 23.4	DB:DISTRIB: Avoid ORA-600[4000]/ORA-600[4097] in the DB background RECO scenario.
35143304	19.24	consider converting ORA-600 [4000] to pdb-specific assert or soft assert
33343993	19.16	Convert ORA-600 [4000] to PDB Specific Assert and Crash Only the Affected PDB
32156194	19.12	ORA-600 [25027] during the select on x$ktcxb
32765471	aim:ORA-600 [4000] – kccpb_sanity_check
23030488	18.1	ORA-00600 [4000] During First Open of PDB After Undo Mode Switch
22610979	18.1	ORA-00600 [4000] On DB Close of STANDBY Due to MMON Process
21770222	12.2.0.1	ORA-600: [4000] in CDB
21379969	12.2.0.1	ORA-00600 [4000] after a tablespace is transported and plugged into another DB
20427315	12.2.0.1	ORA-600 [4000] While Performing DMLs In Freelist Segment
20407770	12.2.0.1	ORA-00600 [4000] error in CDB and DDL operations in PDBs
19352922	12.2.0.1	IMC: ORA-600[4000] may occur on HCC block
14741727	11.2.0.2.9, 11.2.0.2.BP19, 11.2.0.3.BP12, 11.2.0.3.BP13, 11.2.0.4, 12.1.0.1	Fixes for bug 12326708 and 14624146 can cause problems – backout fix
12619529	11.2.0.3.BP18, 11.2.0.4, 12.1.0.1	ORA-600[kdsgrp1] from SELECT on plugged in tablespace with FLASHBACK
10425010	11.2.0.3, 12.1.0.1	Stale data blocks may be returned by Exadata FlashCache
9145541	11.1.0.7.4, 11.2.0.1.2, 11.2.0.2, 12.1.0.1	OERI[25027]/OERI[4097]/OERI[4000]/ORA-1555 in plugged datafile after CREATE CONTROLFILE in 11g
12353983	11.2.0.1	ORA-600 [4000] with XA in RAC
7687856	11.2.0.1	ORA-600 [4000] from DML on transported ASSM tablespace
2917441	11.1.0.6	OERI [4000] during startup
3115733	9.2.0.5, 10.1.0.2	OERI[4000] / index corruption can occur during index coalesce
2959556	9.2.0.5, 10.1.0.2	STARTUP after an ORA-701 fails with OERI[4000]
1371820	8.1.7.4, 9.0.1.4, 9.2.0.1	OERI:4506 / OERI:4000 possible against transported tablespace
434596	7.3.4.2, 8.0.3.0	ORA-600[4000] from altering storage of BOOTSTRAP$

如何判断数据文件是否处于begin backup状态

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

标题：如何判断数据文件是否处于begin backup状态

在数据库恢复中,经常会遇到由于某种原因对数据库执行了begin backup,但是没有执行end backup,然后导致库无法启动的例子,那么怎么判断当前的库是存在这种情况呢？有两种方法可以对其进行判断:
1. 通过查询v$backup表来确认

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 * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 NOT ACTIVE         1.1210E+12 09-FEB-25

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL>  select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 ACTIVE             1.1210E+12 09-FEB-25

SQL>  alter tablespace users end backup;

Tablespace altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 NOT ACTIVE         1.1210E+12 09-FEB-25

v$backup.status=’ACTIVE’表示该文件处于begin backup状态

2. 通过bbed查看kcvfh.kcvfhsta值
确认所有数据文件都没有处于begin backup状态

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 NOT ACTIVE         1.1210E+12 09-FEB-25

list内容列表

[oracle@iZbp11c0qyuuo1gr7j98upZ ~]$ cat /home/oracle/list.txt 
         1 /u01/app/oracle/oradata/xifenfei/system01.dbf
         2 /u01/app/oracle/oradata/xifenfei/sysaux01.dbf
         3 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
         4 /u01/app/oracle/oradata/xifenfei/users01.dbf

bbed查看kcvfh.kcvfhsta值

[oracle@iZbp11c0qyuuo1gr7j98upZ ~]$ bbed listfile=/home/oracle/list.txt
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Feb 9 21:20:15 2025

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

************* !!! For Oracle Internal Use only !!! ***************

BBED> set file 1
        FILE#           1

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x2004 (KCVFHOFZ)

BBED> set file 2
        FILE#           2

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

BBED> set file 3
        FILE#           3

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

BBED> set file 4
        FILE#           4

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

执行database begin backup

SQL> alter database begin backup;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 ACTIVE             1.1210E+12 09-FEB-25
         2 ACTIVE             1.1210E+12 09-FEB-25
         3 ACTIVE             1.1210E+12 09-FEB-25
         4 ACTIVE             1.1210E+12 09-FEB-25

再次bbed查看kcvfh.kcvfhsta值

BBED> set file 1
        FILE#           1

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x2001 (KCVFHHBP)

BBED> set file 2
        FILE#           2

BBED>  p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0001 (KCVFHHBP)

BBED> set file 3
        FILE#           3

BBED>  p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0001 (KCVFHHBP)

BBED> set file 4
        FILE#           4

BBED>  p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0001 (KCVFHHBP)

对于非system文件kcvfh.kcvfhsta=0×0001表示begin backup状态
对于system文件kcvfh.kcvfhsta=0×2001表示begin backup状态

_gc_undo_affinity=FALSE触发ORA-01558

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

标题：_gc_undo_affinity=FALSE触发ORA-01558

最近有客户遭遇非系统回滚段报ORA-01558的故障,类似:ORA-01558: out of transaction ID’s in rollback segment _SYSSMU4_1254879796$,在之前的恢复case中遇到两次system rollback报ORA-01558而不能正常启动的案例.(ORA-01092 ORA-00604 ORA-01558故障处理和ORA-01558: out of transaction ID’s in rollback segment SYSTEM),这次是业务回滚段,出来起来相对比较简单,直接重建该回滚段所在undo表空间即可.遭遇该问题的主要原因是由于19c rac中由于禁用drm,设置了_gc_undo_affinity=FALSE参数导致.
gc_undo_affinity_ora-1558

还有一个类似bug,需要注意:Bug 19700135 ORA-600 [4187] when the undo segment wrap# is close to the max value of 0xffffffff,主要影响版本为：

关于该bug的描述

ORA-600 [4187] can occur for undo segments where wrap# is close to the max value of 0xffffffff (KSQNMAXVAL).
This normally affects databases with high transaction rate that have existed for a relatively long time.
 
To identify undo segments causing the above error and others that may potentially cause it 
in the future, run the next query:
 
 select b.segment_name, b.tablespace_name 
         ,a.ktuxeusn "Undo Segment Number"
         ,a.ktuxeslt "Slot"
         ,a.ktuxesqn "Wrap#"
   from  x$ktuxe a, dba_rollback_segs b
   where a.ktuxesqn > -429496730 and a.ktuxesqn < 0
       and a.ktuxeusn = b.segment_id;
 
Then drop the undo segments or the undo tablespace from the output above.
 
With this fix in place an error ORA-1558 is eventually produced for the affected undo segment
which still requires dropping the undo segment:
  ORA-1558 "out of transaction ID's in rollback segment %s"
   Cause: All the available transaction id's have been used
   Action: Shutdown the instance and restart using other rollback segment(s),
                then drop the rollback segment that has no more transaction id's.

public授权语句

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

标题：public授权语句

在上一篇文章中写到数据泵迁移导致sys授权丢失(impdp导入数据丢失sys授权问题分析),这次进一步完善在有些系统中,会出现对部分权限授权给public的操作,使用逻辑方式(exp/imp,expdp/impdp)进行迁移,可能会导致这个部分权限丢失,从而使得系统部分功能异常,可以通过类似sql查询出来授权语句,在新库上执行

select 'grant ' || privilege || ' on ' || '"' || OWNER || '"."' ||
       table_name || '"' || ' to ' || grantee || ';' "GRANTS"
  from dba_tab_privs
 where privilege not in ('READ', 'WRITE')
   and table_name not like '%/%'
   and owner not in ('SYSTEM',
                     'WMSYS',
                     'XDB',
                     'CTXSYS',
                     'MDSYS',
                     'EXFSYS',
                     'APEX_030200',
                     'ORDSYS',
                     'ORDPLUGINS',
                     'DBSNMP',
                     'OLAPSYS',
                     'ORDDATA')
   and grantee in ('PUBLIC')
 order by 1;