Oracle几个常见问题

来源：这里教程网时间：2026-03-03 16:56:08 作者：

1、版本兼容问题

12c以上版本导入报用户锁定

在12c的数据库服务器上面的sqlnet.ora文件中添加一行参数，再从新导入，

问题解决。

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10

【故障处理】ORA-28040: No matching authentication protocol

11g

SQLNET.ALLOWED_LOGON_VERSION=8注释掉

11g连接12c

sqlnet.ora

添加

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

不行的话重置密码

12c改以下参数为failes会登录不上

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=TRUE SCOPE=BOTH;

2、重新编译procedure

第一种如果你使用 PL/SQL Developer工具

左侧工具栏中选择“存储过程”-》选择已经失效的procedure-》右键-》选择重新编译即可完成

第二种命令行版

1.查找到无效对象

select 'Alter '||object_type||' '||object_name||' compile;' from user_objects where status = 'INVALID';

2.重新编译存储过程 pro_backup_call 执行下面脚本即可,用指定用户

alter procedure YF_HLZX.P_AUDIT_PFZY compile;

3.连接断开

[20-6-2 8:04:46:743 CST] 00000040 ConnectionEve A J2CA0056I: 连接管理器从资源 dbtrust 的资源适配器接收到致命连接错误。异常：java.sql.SQLException: ORA-01012: 没有登录

sys@tms> show parameter resource

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

resource_limit boolean TRUE

select * from dba_profiles;

DEFAULT IDLE_TIME KERNEL 120

这样的话超时120分钟会自动断开

cat sqlnet.ora

SQLNET.INBOUND_CONNECT_TIMEOUT=0

SQLNET.EXPIRE_TIME=10(此参数用来应用与数据库不同网段，防止60分钟防火墙自动杀空闲连接：每十分钟发一次探查指令)

4.监听不正常

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

show parameter listener

看看是不是默认监听

如果不是置为空

alter system set local_listener='';

注册数据库

alter system register;

5.19c更改pdb服务名

BEGIN

DBMS_SERVICE.CREATE_SERVICE(

SERVICE_NAME =>'TKWIND',

NETWORK_NAME =>'TKWIND');

END;

alter pluggable database pdborcl close immediate;

alter pluggable database all open services=All;

配置完成，启动之后，lsnrctl status 出来的service是NETWORK_NAME

6.Oracle19c有pdb的版本在连接时和11g版本有一些区别：

例子：

url=jdbc:oracle:thin:@localhost:1521/pdborcl

url=jdbc:oracle:thin:@localhost:1521:pdborcl

7.安装包 libelf-0.8.5

安装rac执行?u01/11.2.0/grid/root.sh 脚本的时候报错

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Using configuration parameter file: /u01/11.2.0/grid/crs/install/crsconfig_params

Creating trace directory

User ignored Prerequisites during installation

Installing Trace File Analyzer

Failed to create keys in the OLR, rc = 127, Message:

??/u01/11.2.0/grid/bin/clscfg.bin: error while loading shared libraries: libcap.so.1: cannot open shared object file: No such file or directory?

Failed to create keys in the OLR at /u01/11.2.0/grid/crs/install/crsconfig_lib.pm line 7660.

/u01/11.2.0/grid/perl/bin/perl -I/u01/11.2.0/grid/perl/lib -I/u01/11.2.0/grid/crs/install /u01/11.2.0/grid/crs/install/rootcrs.pl execution failed

说是找不到??libcap.so.1 这个包

解决方法：

[root@node1 lib64]# cd /lib64?

[root@node1 lib64]# ls -lrt libcap?

libcap-ng.so.0 ? ? ?libcap-ng.so.0.0.0 ?libcap.so.2 ? ? ? ? libcap.so.2.16?

? ? ??

[root@node1 lib64]# ls -lrt libcap.so.2?

lrwxrwxrwx. 1 root root 14 12月 23 21:21 libcap.so.2 -> libcap.so.2.16?

[root@web1 node1]#?ln -s libcap.so.2.16 libcap.so.1?

[root@node1 lib64]# ls -lrt libcap*

-rwxr-xr-x. 1 root root 18672 Jun 25 ?2011 libcap-ng.so.0.0.0

-rwxr-xr-x. 1 root root 19016 Dec ?8 ?2011 libcap.so.2.16

lrwxrwxrwx. 1 root root ? ?14 Oct ?9 11:14 libcap.so.2 -> libcap.so.2.16

lrwxrwxrwx. 1 root root ? ?18 Oct ?9 11:14 libcap-ng.so.0 -> libcap-ng.so.0.0.0

lrwxrwxrwx ?1 root root ? ?14 Nov 24 13:17 libcap.so.1 -> libcap.so.2.16

8.11g版本安装包用途

1、p13390677_112040_MSWIN-x86-64_1of7.zip

2、p13390677_112040_MSWIN-x86-64_2of7.zip

3、p13390677_112040_MSWIN-x86-64_3of7.zip

4、p13390677_112040_MSWIN-x86-64_4of7.zip

5、p13390677_112040_MSWIN-x86-64_5of7.zip

6、p13390677_112040_MSWIN-x86-64_6of7.zip

7、p13390677_112040_MSWIN-x86-64_7of7.zip

其中1、2表示Database, 用于安装、升级数据库；

3为grid infrastructure，用来升级RAC。如果要使用Clusterware、ASM、ACFS、ASM动态卷等功能时都需要先安装此包。

4表示客户端（Client）；

5为Gateway software，gateways是指透明网关，如果要从oracle访问其它数据库系统(sqlserver,sybase…)则需要安装Gateway；

6表示 examples, 是示例文件安装包；

7为deinstall，是Oracle自带的界面化卸载工具；

9.报错

Errors in file /d12/app/oracle/diag/rdbms/test/test/trace/test_j001_4981.trc:

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_21"

ORA-20001: Statistics Advisor: Invalid task name for the current user

ORA-06512: at "SYS.DBMS_STATS", line 47207

ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201

ORA-06512: at "SYS.DBMS_STATS", line 47197

大概是每隔10分钟报一次

MOS对应的文章：ORA-12012 Error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_<NN> in 12.2 Database (Doc ID 2127675.1)

可能是在建库的时候没有执行dbms_stats.init_package()

$ sqlplus / as sysdba

SQL> EXEC dbms_stats.init_package();

PL/SQL procedure successfully completed.

column name format A35

set linesize 120

select name, ctime, how_created from sys.wri$_adv_tasks where owner_name = 'SYS' and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

NAME CTIME HOW_CREATED

----------------------------------- --------- ------------------------------

AUTO_STATS_ADVISOR_TASK 17-JUN-17 CMD

INDIVIDUAL_STATS_ADVISOR_TASK 17-JUN-17 CMD

10.报错

Unable to obtain current patch information due to error: 20003, ORA-20003: Configuring job Load_opatch_inventory_1on node and on instancefailed

ORA-06512: at "SYS.DBMS_QOPATCH", line 777

ORA-06512: at "SYS.DBMS_QOPATCH", line 479

ORA-06512: at "SYS.DBMS_QOPATCH", line 455

ORA-06512: at "SYS.DBMS_QOPATCH", line 574

ORA-06512: at "SYS.DBMS_QOPATCH", line 2247

解决办法

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=163919018891010&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=2364768.1&_afrWindowMode=0&_adf.ctrl-state=ytgxd12h2_221

打补丁 23333567 或者忽略

11.redhat7.*安装oracl11g报错

Error in invoking target 'agent nmhs' of makefile '/home/oracle/app/oracle/product/11.2.0/db_1/sysman/lib/ins_emagent.mk'

处理：

[oracle@centos ~]$ cd $ORACLE_HOME/sysman/lib

[oracle@centos lib]$ cp ins_emagent.mk ins_emagent.mk.bak

[oracle@centos lib]$ vim ins_emagent.mk

/NMECTL

SQL

#===========================# emdctl#===========================

$(SYSMANBIN)emdctl:

$(MK_EMAGENT_NMECTL) -lnnz11 在此处修改添加

官方让跳过，打19692824补丁

12.12c dg库备库hang住，报

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=92

打补丁28423598

报错：

13.在主库添加数据文件时，备库alert所有数据文件报错，官方文档2322290.1，

Errors in file /home/oracle/ora12c/diag/rdbms/tkhdstb/tkhddb/trace/tkhddb_m000_26149.trc:

ORA-01110: data file 3: '/oradata/tkhddb/sysaux01.dbf'

打补丁：

24844841 PHSB:CDB M000 REPORTED ORA-1110 ON ADG WHEN A DATAFILE IS ADDED ON PRIMARY

14.重启库后开启从库standby后从库状态不正常

startup nomount

alter database mount standby database ;

alter database open read only ;

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> Select controlfile_type,open_mode from v$database;

CONTROL OPEN_MODE

------- --------------------

STANDBY READ ONLY WITH APPLY

15.备库临时表空间(查询dba_temp_files)

ERROR at line 1:

ORA-01187: cannot read from file because it failed verification tests

ORA-01110: data file 201: '/oradata/tkhddb/temp01.dbf'

查询原temp大小及数据文件

select name,bytes/1024/1024,status from v$tempfile;

删除临时文件：

alter database tempfile '/oradata/tkhddb/temp01.dbf' drop;

Database altered.

重新添加原大小的数据文件：

alter tablespace temp add tempfile '/oradata/tkhddb/temp01.dbf' size 32m autoextend on next 200m;

Tablespace altered.

SQL> select name,bytes/1024/1024,status from v$tempfile;

NAME BYTES/1024/1024 STATUS

------------------------------ --------------- -------

/oradata/seven/temp01.dbf 30 ONLINE

由于场景是DG环境，所以temp的name和bytes的值最好保持主备库一致

16.>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=87

打补丁28423598

17.报错：

ORA-39083: Object type REF_CONSTRAINT failed to create with error:

ORA-02298: cannot validate (WORKBRAIN.FK_RCPMSG_MSG_ID) - parent keys not found

a) 加参数CONTENT=METADATA_ONLY 先导入元数据，执行如下SQL找到需要禁止的外键关联

select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||';'

from user_constraints WHERE CONSTRAINT_TYPE='R';

b) 执行(a)的结果SQL

c) 再重新取消CONTENT=METADATA_ONLY导入后，执行如下SQL找到需要恢复的外键关联

select 'ALTER TABLE '||TABLE_NAME||' ENABLE NOVALIDATE CONSTRAINT '||constraint_name||';'

from user_constraints WHERE CONSTRAINT_TYPE='R';

生产备份失败

sql statement: alter system archive log current

Starting backup at 2019-11-01 18:35:21

current log archived

released channel: c1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 11/01/2019 18:35:22

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

ORA-19625: error identifying file /arclog/1_3163_878380255.arc

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

连接到catalog库执行：

crosscheck archivelog all;

crosscheck backup;

RMAN-06207: WARNING: 2 objects could not be deleted for DISK channel(s) due

RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status

RMAN-06210: List of Mismatched objects

RMAN-06211: ==========================

RMAN-06212: Object Type Filename/Handle

RMAN-06213: --------------- ---------------------------------------------------

RMAN-06214: Datafile Copy /home/oracle/ora11g/oradata/tklchat/users01.dbf

RMAN-06214: Backup Piece /home/oracle/ora11g/fast_recovery_area/TKLCHAT/autobackup/2019_11_04/o1_mf_n_1023466073_gvzq2ty4_.bkp

Session371和session310发起的两个事务分别包含上述标红的sql语句，相互等待对方资源的释放，造成死锁；

首先注意到的是Deadlock graph中的资源占有情况，可以看到两个session都hold了一个SX类型的锁，同时在等待SSX类型的锁，而且引发的是一个删除语句，并且这个表是系统的一个关键表，大部分的表的外键都引用自此表的主键。因此猜测是碰到了外键引发的死锁。

建议1：

应用程序开发人员检查表FORECAST_AIRPORT和表FORECAST_KEYPOINT之间的关系（如父表FORECAST_KEYPOINT、子表FORECAST_AIRPORT 具体参照哪一列），有哪些约束、索引等；

如果子表上的外键约束列没有建立索引，删除父表记录时不得不对子表加表级锁，防止其他删除操作对该表的操作，要解决这种死锁问题则需要在子表的外键约束列上建立相应的索引；

这样，当对子表的外键列添加索引后，死锁可以被消除，因为这时删除父表记录不需要对子表加表级锁。（后面有模拟实验可以验证）

创建索引：create index index_name on schema.table_name(column_name);

create index index_name on pdmcompard.FORECAST_AIRPORT (FORECAST_ID);

create index index_name on datamgr.FORECAST_AIRPORT (FORECAST_ID);

建议2：也有可能是循环删除的问题（如果第1个建议没有成功，可以试试此方法）

如果可以，改成delete from t_user where ID in (:1,:2...)《调整业务逻辑，修改应用程序，合理分配资源》

查询主外键：

SELECT a.table_name, a.column_name FROM dba_cons_columns a, dba_constraints b

WHERE a.constraint_name = b.constraint_name

AND b.constraint_type = 'R' AND a.column_name = 'EVENT_ID';

SELECT a.table_name, a.column_name FROM dba_cons_columns a, dba_constraints b

WHERE a.constraint_name = b.constraint_name

AND b.constraint_type = 'P' AND a.column_name = 'CHARGE_ID';

select a.TABLE_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,b.column_name

from dba_constraints a,dba_cons_columns b

where a.constraint_name=b.constraint_name

and a.table_name in ('SALES','CUSTOMERS','CHANNELS','TIMES')

and a.constraint_type in ('P','R')

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","TYPE$","KGLS^7df02ca3","kglHeapInitialize:temp")

5. 10.129.24.2 tkirm 04031后续：

5.1 同一台机器上另一个实例TKTPOP高峰期间占用I/O特别多的SQL语句调优（增加索引）

5.2 设置filesystem_options=setall (tkirm/tktpop)，已改，需重启生效

5.3 官方回复：set "_enable_shared_pool_durations" = false &

简单白话文就是说，共享池Shared Pool只能扩展，不能再次被收缩.(因为从共享池移动到缓冲区缓存。但是，从缓冲区高速缓存到共享池的粒度移动仍然是可能的)，可能造成在某些情况下，shared pool一直扩展，Buffer cache不断的被缩小...造成缓冲区热点数据过少，数据库物理读过高现象

您好，

这个问题和下面的note是比较相似的。可以根据这个文档的建议disable duration，同时建议增大shared_pool_size的大小到3000MB，因为从问题发生之前的AWR看，shared pool已经有2500MB。然后重启db。

ORA-04031: Unable To Allocate 32 Bytes Of Shared Memory ("shared pool","select tablespace_id, rfno, ...","SQLA","tmp")" ( Doc ID 1986741.1 )

SQL> alter system set "_enable_shared_pool_durations"=false scope=spfile;

SQL> alter system set shared_pool_size=3000M scope=spfile;

- restart the database

increase shared pool size （requires database restart）

CLSRSC-400: A system reboot is required to continue installing.

The command ‘/u01/app/grid/product/12.2.0/grid/perl/bin/perl -I/u01/app/grid/product/12.2.0/grid/perl/lib

-I/u01/app/grid/product/12.2.0/grid/crs/install /u01/app/grid/product/12.2.0/grid/crs/install/rootcrs.pl ‘ execution failed

查询mos发下:ACFS Drivers Install reports CLSRSC-400: A system reboot is required to continue installing (Doc ID 2025056.1),主要是由于12c gi开始，acfs默认是安装的，由于acfs在redhat 7.3中不支持导致上述的错误信息.

[grid@xifenfei01 ~]$ acfsdriverstate -orahome $ORACLE_HOME supported

ACFS-9459: ADVM/ACFS is not supported on this OS version: '3.10.0-514.el7.x86_64'

ACFS-9201: Not Supported

. Unzip Grid Infrastructure Gold Image on local node as 'grid' user

2. Apply the patch by running:

<GI Home>/gridSetup.sh -applyOneOffs patch location

An example is:

$ ./gridSetup.sh -applyOneOffs /u01/patch/25078431/25078431

Preparing the home to patch...

Applying the patch /u01/patch/25078431/...

Successfully applied the patch.

(Any warnings about X11 that are displayed may safely be ignored)

3. Follow the normal install process, including running gridSetup.sh. Upon completion of the installation process, 'opatch lspatches' should show the patch is applied:

$ ./opatch lspatches

25078431;ACFS Interim patch for 25078431

OPatch succeeded.

a）查看碎片率

SELECT table_name,

ROUND ( (blocks * 8), 2) "高水位空间 k",

ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",

ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",

ROUND (( blocks * 8 - (num_rows * avg_row_len / 1024)- blocks * 8 * 10 / 100),2)"浪费空间 k",

ROUND (ROUND (( blocks * 8 -( num_rows * avg_row_len / 1024)- blocks * 8 * 10 / 100),15)/ROUND ( (blocks * 8), 15),2) "浪费率",

LAST_ANALYZED "统计信息收集时间"

FROM dba_tables

WHERE temporary = 'N' and OWNER='LIS' and NUM_ROWS<>0 and table_name in

('LJAGETOTHER');

首先判定是否ORA-04031 错误是由共享池保留空间中的库高速缓冲的碎片产生的。提交下的查询：

SELECT free_space, avg_free_size,used_space, avg_used_size, request_failures,

last_failure_size

FROM v$shared_pool_reserved;

如果:

REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC

那么ORA-04031 错误就是因为共享池保留空间缺少连续空间所致。要解决这个问题,可以考虑加大SHARED_POOL_RESERVED_MIN_ALLOC 来降低缓冲进共享池保留空间的对象数目，并增大 SHARED_POOL_RESERVED_SIZE 和 SHARED_POOL_SIZE 来加大共享池保留空间的可用内存。

如果：

REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

或者

REQUEST_FAILURES 等于0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

那么是因为在库高速缓冲缺少连续空间导致ORA-04031 错误。

第一步应该考虑降低SHARED_POOL_RESERVED_MIN_ALLOC 以放入更多的对象到共享池保留空间中并且加大SHARED_POOL_SIZE。

insert 报600 ID 1608861.1

参考MOS文档：

ORA-600 [ktsplbfmb-dblfree] During Insert in to a LOB (文档 ID 783593.1)

ORA-600 [25027] (文档 ID 284433.1)

ORA-00600 [25027] [x] [0] Raised by Insert into LOB (文档 ID 1608861.1)

IF: ORA-1555 on LOB Data (文档 ID 1950896.1)

LOB Corruption With ORA-22924 And ORA-01555 After The LOB Shrink Space Operations When DB Has Fix Of 21246723 (文档 ID 2377683.1)

LOBs and ORA-01555 troubleshooting (文档 ID 846079.1)

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due

RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status

RMAN-06210: List of Mismatched objects

RMAN-06211: ==========================

RMAN-06212: Object Type Filename/Handle

RMAN-06213: --------------- ---------------------------------------------------

RMAN-06214: Datafile Copy /oradata1/samba/tbs_data_059.dbf

rman target / catalog rmanuser/ rmanpass@rman.tkcloud.com

crosscheck copy;

ORA-14450: attempt to access a transactional temp table already in use

--找到表对象

select * from dba_objects where object_name='TMP_DA_GMS'

--找到该对象导致的锁的Session会话，并编写语句

select 'alter system kill session '''||SID||','||SERIAL#||''';' from V$session where SID in ( select sid from v$enqueue_lock t where t.type='TO' and id1='99879' )

ORA-4031 Troubleshooting Tool (Doc ID 1521925.1)

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

故障重现了。

针对这样的问题，下面用dba身份进行如下操作：

首先确定操作对象的object_id:

SQL> select object_id from dba_objects where object_name='T1';

OBJECT_ID

----------

52505

SQL> select object_id,session_id,oracle_username,os_user_name,process,locked_mode from v$locked_object where object_id=52505;

OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE