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_ 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
解决办法
打补丁 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:
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
--------- ---------- ---------------- ------------- -------- -----------
52505 149 SCOTT oracle 5333 3
由上面可见，正是SID为149的用户scott在操作此表，可以通过查询v$session得到详细信息：
SQL> select sid,serial#,username,process from v$session where sid=149;
SID SERIAL# USERNAME PROCESS
--------- ---------- -------------- ------------
149 136 SCOTT 5333