分类目录归档：数据库

ORA-600 12807（CON$.CON#达到最大值） 处理

发表于 2023 年 10 月 14 日 由 惜分飞

这次阳了有点严重，客户现场打patch无法去，在家里远程值守,在电脑前面闲着就查询和重现了最近朋友和我说的他们的客户遇到ORA-600 12807的故障.查询了下mos,基本上可以确认是由于CON$.CON#达到理论最大值无法继续增加从而报该错误,参考文档:
Mechanism to Recycle Database Constraint Identifiers (Doc ID 2925056.1)
Bug 13781691 – ORA-600 [12807] if CON$.CON# very high due to bug 13784384 (Doc ID 13781691.8)
Bug 25343563 – Mechanism to Implement Constraint Identifier (con#) Recycling (Doc ID 25343563.8)
在12及其之后的版本中oracle发布了patch 25343563 并设置event启用该patch进行解决.但是如果是12c之前版本,官方没有提供直接的解决方案.最基本的解决方法就是进行数据逻辑迁移,以及避免频繁创建约束导致con$.con#消耗太大

通过试验重现该错误

SQL> create table t_xff  (id number not null,name varchar2(100) not null);
create table t_xff  (id number not null,name varchar2(100) not null)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12807], [], [], [], [], [], [], [],
[], [], [], []

SQL>  create table t_xff  (id number,name varchar2(100));

Table created.

SQL> alter table t_xff add primary key(id);
alter table t_xff add primary key(id)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12807], [], [], [], [], [], [], [],
[], [], [], []


SQL> select con# from sys.con$ where name='_NEXT_CONSTRAINT';

      CON#
----------
4294967294

通过一些底层分析,并对部分底层基表进行patch实现数据库可以继续创建约束

SQL>  alter table t_xff add primary key(id);

Table altered.

使用临时的patch方法,可以快速的恢复业务,后续找适当时间点安排迁移.
在此提醒:对于一些创建中间对象或者临时对象频繁的系统(特别是大量主键,not null等)注意检查该值距离天花板距离,如果比较接近了最好安排一次逻辑迁移和找出来原因(是oracle bug还是应用触发)

发表在 ORA-xxxxx | 标签为 , , , | 评论关闭

非tns方式登录pdb方法

发表于 2023 年 10 月 9 日 由 惜分飞

非tns方式登录pdb方法之—-alter session set container

[oracle@db01 ~]$ env|grep ORA
ORACLE_SID=DCXFF
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19c/db_1
[oracle@db01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 9 22:10:34 2023
Version 19.20.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XFFPDB1                        READ WRITE NO
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=XFFPDB1;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
XFFPDB1
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

非tns方式登录pdb方法之—-ORACLE_PDB_SID

[oracle@db01 ~]$ export ORACLE_PDB_SID=XFFPDB1
[oracle@db01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 9 22:11:30 2023
Version 19.20.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

SQL> SHOW pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 XFFPDB1                        READ WRITE NO
SQL> show con_name;

CON_NAME
------------------------------
XFFPDB1
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

参考:How To Connect to PDB By OS Authentication In 19c Container Database Environment (Doc ID 2729416.1)

发表在 ORACLE 19C | 标签为 | 评论关闭

ORA-07445: exception encountered: core dump [kdxlin()+4088]处理

发表于 2023 年 9 月 23 日 由 惜分飞

abort方式关闭数据库，启动报错

Tue Sep 19 21:52:56 2023
NOTE: dependency between database orcl and diskgroup resource ora.DATA.dg is established
Tue Sep 19 21:52:57 2023
Reconfiguration started (old inc 4, new inc 6)
List of instances:
 1 (myinst: 1) 
 Global Resource Directory frozen
 * dead instance detected - domain 0 invalid = TRUE 
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
Tue Sep 19 21:52:57 2023
Tue Sep 19 21:52:57 2023
 LMS 3: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Tue Sep 19 21:52:57 2023
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Tue Sep 19 21:52:57 2023
 LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
 Set master node info 
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Post SMON to start 1st pass IR
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
 Fix write in gcs resources
Reconfiguration complete
Tue Sep 19 21:53:05 2023
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_28917.trc  (incident=492333):
ORA-00600: internal error code, arguments: [2131], [33], [32], [], [], [], [], [], [], [], [], []
Incident details in:/u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_492333/orcl1_ora_28917_i492333.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 /* db agent *//* {1:34652:2} */...

重建ctl之后，尝试recover数据库报错ORA-600 3020和ORA-07445 kdxlin等错误

SQL> recover database;
ORA-00600: internal error code, arguments: [3020], [41], [3142531],
[175108995], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 41, block# 3142531, file
offset is 4268777472 bytes)
ORA-10564: tablespace XIFENFEI
ORA-01110: data file 41: '+DATA/orcl/datafile/xifenfei07.dbf'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'

Wed Sep 20 00:15:00 2023
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 64 slaves
Wed Sep 20 00:15:02 2023
Recovery of Online Redo Log: Thread 2 Group 6 Seq 67008 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_6.268.942097791
Recovery of Online Redo Log: Thread 1 Group 2 Seq 81767 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_2.262.942097651
Recovery of Online Redo Log: Thread 1 Group 5 Seq 81768 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_5.263.942097651
Wed Sep 20 00:15:08 2023
Hex dump of (file 41, block 3142531) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pr1m_45463.trc
Reading datafile '+DATA/orcl/datafile/ts_his3bz07.dbf' for corruption at rdba: 0x0a6ff383 (file 41, block 3142531)
Reread (file 41, block 3142531) found different corrupt data (logically corrupt)
Hex dump of (file 41, block 3142531) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pr1m_45463.trc
Wed Sep 20 00:15:08 2023
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0xC] [PC:0x95FB582, kdxlin()+4088][flags: 0x0,count:1]
Wed Sep 20 00:15:08 2023
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0xC] [PC:0x95FB582, kdxlin()+4088][flags: 0x0,count:1]
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pr10_45419.trc  (incident=564584):
ORA-07445: exception encountered:core dump [kdxlin()+4088][SIGSEGV][ADDR:0xC][PC:0x95FB582][Address not mapped to object]
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_564640/orcl1_pr17_45433_i564640.trc

尝试随机恢复文件，也遭遇ORA-07445 kdxlin异常

SQL> recover datafile 34;
ORA-00283: recovery session canceled due to errors
ORA-10562: Error occurred while applying redo to data block (file# 34, block#
1999809)
ORA-10564: tablespace XIFENFEI
ORA-01110: data file 34: '+DATA/orcl/datafile/xifeifenfei06'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 97961
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kdxlin()+4088] [SIGSEGV]
[ADDR:0xC] [PC:0x95FB582] [Address not mapped to object] []

出现这种情况是由于redo和数据文件块不一致导致无法正常应用日志,人工对于异常的block进行处理,数据库open成功,然后遭遇undo回滚段异常,对其进行规避,数据库open并且稳定运行

发表在 Oracle备份恢复 | 标签为 , , | 评论关闭