分类目录归档：ORA-xxxxx

ORA-00069: cannot acquire lock — table locks disabled for xxxx

发表于 2025 年 2 月 14 日 由 惜分飞

在oracle数据库中删除用户遭遇ORA-00069: cannot acquire lock — table locks disabled for HR_XXX_01错误

SQL>  drop user XFF cascade;
 drop user XFF cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00069: cannot acquire lock -- table locks disabled for HR_XXX_01

关于ORA-00069错误解释

[oracle@xifenfei.com ~]$ oerr ora 00069
00069, 00000, "cannot acquire lock -- table locks disabled for %s"
// *Cause: A command was issued that tried to lock the table indicated in
//         the message. Examples of commands that can lock tables are:
//         LOCK TABLE, ALTER TABLE ... ADD (...), and so on.
// *Action: Use the ALTER TABLE ... ENABLE TABLE LOCK command, and retry
//          the command.

尝试lock表,直接hang,强制终止

SQL> alter table XFF.HR_XXX_01 enable table lock; 



^Calter table XFF.HR_XXX_01 enable table lock
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

查询tab$.flags的值

SQL> col object_name for a30
SQL> set lines 150
SQL> select x. object_name,obj#, flags
  2  from sys.tab$,(
  3  select object_name, object_id
  4  from dba_objects
  5  where owner='XFF'
  6  and object_name in ('HR_XXX_01','HR_XXXCONTROL','XXXLZB_JD1')
  7  and object_type = 'TABLE') x
  8  where obj# = x.object_id;

OBJECT_NAME                          OBJ#      FLAGS
------------------------------ ---------- ----------
XXXLZB_JD1                         246416 1073742353
HR_XXXCONTROL                      246421 1073742353
HR_XXX_01                          246424 1073742359

发现报错表的flags和其他表不一样(其他表为1073742353,而报错表为1073742359),对于这种情况官方给出来的解决方法,关闭库,确保没有任何额外会话连接上来
ora-00069

因为本身要重启库维护,直接把库启动到upgrade模式进行操作

[oracle@xifenfei.com ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 14 20:29:28 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

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 4.2950E+10 bytes
Fixed Size                 23149944 bytes
Variable Size            9529458688 bytes
Database Buffers         3.3286E+10 bytes
Redo Buffers              111067136 bytes
Database mounted.
Database opened.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 4.2950E+10 bytes
Fixed Size                 23149944 bytes
Variable Size            9529458688 bytes
Database Buffers         3.3286E+10 bytes
Redo Buffers              111067136 bytes
Database mounted.
Database opened.
SQL>  drop user XFF cascade;
 drop user FZHR cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00069: cannot acquire lock -- table locks disabled for HR_XXX_01


SQL> alter table XFF.HR_XXX_01 enable table lock; 

Table altered.

SQL>  drop user XFF cascade;

User dropped.

SQL>
发表在 ORA-xxxxx | 标签为 | 留下评论

ORA-65088: database open should be retried

发表于 2024 年 9 月 18 日 由 惜分飞

在12.2以及后续的cdb版本中,如果重建ctl并且resetlogs库,很可能会遇到ORA-65088: database open should be retried错误

SQL> startup nomount force pfile='/<path>/<filename>.ora';
ORACLE instance started.

Total System Global Area 1593835520 bytes
Fixed Size                  8793256 bytes
Variable Size             402654040 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7983104 bytes
SQL> !vi ctl.sql

SQL> @ctl.sql

Control file created.

SQL> select count(*) ,fhsta from x$kcvfh group by fhsta;

  COUNT(*)      FHSTA
---------- ----------
        11      32768
         4      40960

SQL> select count(*) ,FHSCN from x$kcvfh group by FHSCN;

  COUNT(*) FHSCN
---------- --------------------
         3 1820866
         4 2281969
         4 2281978
         4 2281982

SQL> select file#,error from v$datafile_header where length(error)>=1;

no rows selected

SQL> select count(*) ,fhrba_seq from x$kcvfh group by fhrba_seq;

  COUNT(*)  FHRBA_SEQ
---------- ----------
         3         20
        12         32

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2281978 generated at 09/19/2018 00:52:00 needed for thread 1
ORA-00289: suggestion : /<path>/1_32_981800889.dbf
ORA-00280: change 2281978 for thread 1 is in sequence #32


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/<path>/1_32_981800889.dbf
ORA-00279: change 2282008 generated at 09/19/2018 00:52:13 needed for thread 1
ORA-00289: suggestion : /<path>/1_33_981800889.dbf
ORA-00280: change 2282008 for thread 1 is in sequence #33
ORA-00278: log file '/<path>/1_32_981800889.dbf' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.    << Expected message "Media recovery complete." !!
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 _###_UNKNOWN_PDB_#_3           MOUNTED
         4 _###_UNKNOWN_PDB_#_4           MOUNTED
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-65088: database open should be retried
Process ID: 32688
Session ID: 10 Serial number: 38416

alert日志类似错误

 Dictionary check beginning
 Pluggable Database <pdb_name_1> (#3) found in data dictionary,
 but not in the control file. Adding it to control file.
 Pluggable Database <pdb_name_2> (#4) found in data dictionary,
 but not in the control file. Adding it to control file.
 Tablespace '<tablespace_name>' #3 found in data dictionary,
 but not in the controlfile. Adding to controlfile.
 --
 File 8 not verified due to error ORA-01122
 File 9 not verified due to error ORA-01122
 File 11 not verified due to error ORA-01122
 File 16 not verified due to error ORA-01122
 File 17 not verified due to error ORA-01122
 File 18 not verified due to error ORA-01122
 File 19 not verified due to error ORA-01122
 File 20 not verified due to error ORA-01122
  --
 ORA-65088: database open should be retried
 2018-09-19T01:00:54.083814+05:30
 Errors in file /<path>/trace/<oracle_sid>_ora_12412.trc:
 ORA-65088: database open should be retried
 Error 65088 happened during db open, shutting down database
 Errors in file /<path>/trace/<oracle_sid>_ora_12412.trc  (incident=12289) (PDBNAME=CDB$ROOT):
 ORA-00603: ORACLE server session terminated by fatal error
 ORA-01092: ORACLE instance terminated. Disconnection forced
 ORA-65088: database open should be retried

出现这类故障的原因是由于:
we see that the created controlfile is not aware of PDB and open resetlogs process trying to add information in newly created file . Hence, recovery process ,in newly created controlfile didn’t applied the archives to datafiles part of PDB which says later it will ask for recovery once controlfile is aware of PDB files During the resetlogs process, its pushing the required information to controlfile and shutting the database with suggestion to re-try opening the DB.

$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 19 01:34:01 2018

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

Connected to an idle instance.

SQL> startup nomount force pfile='/<path>/<filename>.ora';
ORACLE instance started.

Total System Global Area 1593835520 bytes
Fixed Size                  8793256 bytes
Variable Size             402654040 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7983104 bytes
SQL> alter database mount;

Database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB1                           MOUNTED
         4 APDB                           MOUNTED
        
SQL> select count(*) ,FHSCN from x$kcvfh group by FHSCN;

  COUNT(*) FHSCN
---------- --------------------
         3 1820866
         4 2281969
         4 2281982
         4 2282012        
        
//* Here , we see controlfile is aware of PDB



$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 19 01:02:13 2018

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

Connected to an idle instance.

SQL>  startup nomount force pfile='/<path>/<filename>.ora';
ORACLE instance started.

Total System Global Area 1593835520 bytes
Fixed Size                  8793256 bytes
Variable Size             402654040 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7983104 bytes
SQL> alter database mount;

Database altered.

SQL> recover database;
ORA-00279: change 2281969 generated at 09/19/2018 00:51:35 needed for thread 1
ORA-00289: suggestion : /<path>/1_32_981800889.dbf
ORA-00280: change 2281969 for thread 1 is in sequence #32


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/<path>/1_32_981800889.dbf
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

官方的进一步解释:
We clearly see that the recovery steps applies the same archivelog file twice. When a controlfile is recreated, the recovery initiated will apply archivelog files to only the CDB datafiles, not to the PDB. Once the database open returns the ORA-65088 error, the next database re-start will apply the archivelog files to the PDB for the sake of database consistency.This should explain why Oracle is looking to apply the same archivelog sequence a second time. The following bugs report similar issues. They have both been closed as ‘not a bug’ as this is expected behavior:
BUG 24951417 – ERROR OPENING DATABASE WITH RESETLOGS AFTER CREATE CONTROLFILE
BUG 25172530 – MULTITENANT RESTORE FAILED WITH ORA-65088: DATABASE OPEN
参考:ORA-65088 while opening DB with resetlogs for multi-tenant DB in 12.2 (Doc ID 2449591.1)

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

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 | 标签为 , , , | 评论关闭