oracle数据库常见问题处理总结3

欢迎大家来到IT世界,在知识的湖畔探索吧!

1、ORA-28040: No matching authentication protocol

将$ORACLE_HOME/network/admin/sqlnet.ora文件添加如下两参数

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

欢迎大家来到IT世界,在知识的湖畔探索吧!

2、不同表空间数据导入失败

–修改用户 orcl 具有 test_data 数据表空间的权限(方法不止这一种)

欢迎大家来到IT世界,在知识的湖畔探索吧!revoke unlimited tablespace from orcl;
alter user orcl quota unlimited on test_data;

3、oracle单个实例下多用户，B用户想读取A用户某表的权限

用system登录，将用户A的表授权给public或者B

grant select on  A.table to public;
grant select on A.table to B;

4、使用oracle数据库，多用户同时对一个表进行增加，删除，修改，查看等操作，会不会有影响？

问题1、各操作间或者性能上会不会有影响？如果有该如何解决？

分析：

多用户操作的影响主要是回锁定记录，oracle数据库提供行级锁，也就是说用户操作数据时，oracle为记录行加锁，当然如果用户一次性操作的数据过多，oracle的锁资源过大，或者锁定数据长时间没有释放（例如几百万条记录更新移植没有提交）。oracle的资源不够，会升级为表锁。至于性能，仍然跟锁定的资源大小有关系。

多用户操作的解决方法：

一次更新量不要太大，记得及时提交结果，养成sql语句后面立即commit或者rollback的习惯。

多人操作表是有加锁的也就是表是共享的行是独占的，你正在操作的行别人只能查不能一起做修改的操作，你commit或rollback之后就会释放锁别人就可以操作了.

oracle并发可以解决这个问题，只要在增、删、改时记得及时commit或rollback就行了.

问题2、两个用户同时对一个表进行增加操作，是不是要等一个增加完之后另一个才可以执行增加操作啊？

分析：

一个用户加数据，如果加完数据立即做事务处理，另一个用户就不用太久等待，甚至可能没有感觉，再如果一个用户加数据加锁后不做事务处理，那另一个用户就会一直等待，直到第一个用户commit后才可以执行第二个用户的操作。

其他知识点：

１）、oracle的数据模式是：用户建在表空间上，表建在用户上

２）、一个用户的表就像自己的私有财产一样，没有自己或管理员授权别的用户是不能查询或修改的；

３）、对于不同用户下的同名表，都是独立的数据对象，如user1.table1和user2.table1是相

互独立的，用户分别操作自己的表是不影响其他用户的；

４）、对于同一用户下的同一个表，所有有权限的用户对其进行数据操作时，是会相互影响的，

如对user1.table1来说，user1修改了它的一行，user２又对该表的该行进行了修改，那么该表的该行的实际内容是在user１修改后基础上user2修改的结果（注意，所有修改以最后成功提交修改请求的用户的内容为准），多个用户对同一个用户下的同一个表的同时修改和锁定会造成锁等待。

5、TNS-12535: TNS:operation timed out、TNS-00505: Operation timed out

该参数可以通过设置为0来禁用，在服务端：

1)、设置sqlnet.ora文件：

欢迎大家来到IT世界,在知识的湖畔探索吧!SQLNET.INBOUND_CONNECT_TIMEOUT=0；

2)、设置listener.ora文件：

INBOUND_CONNECT_TIMEOUT_listenername=0；

3)、然后reload或者重启监听。

说明：这是由于连接超时所产生的问题，在10.2.0.1.0版本中

sqlnet.inbound_connect_timeout参数默认为60秒，即如果连接时间超过60秒则提示超时，

而在其他版本中这两个参数默认为0，即无限制。

6、oracle进程过多

–INACTIVE会话过多导致无法链接，使用如下脚本进行删除对应用户INACTIVE进程

begin
  for rec in (select sid,
                     serial#,
                     module,
                     status
                from v$session s
               where s.username is not null
                 and s.status = 'INACTIVE'
                 and username = upper('&数据库用户')) loop
    execute immediate 'alter system disconnect session ''' || rec.sid || ', ' || rec.serial# || '''immediate';
  end loop;

7、Oracle用户被锁原因及办法

在登陆时被告知test用户被锁

(1)、用dba角色的用户登陆，进行解锁，先设置具体时间格式，以便查看具体时间

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

(2)、查看具体的被锁时间

SQL> select username,lock_date from dba_users where username='TEST';

(3)、解锁

SQL> alter user test account unlock;

(4)、查看是哪个ip造成的test用户被锁

SQL> select username,lock_date from dba_users where username='EHC';

然后查看下是哪个IP造成的被锁（路径具体看情况）

find $ORACLE_HOME/ -name listener.log
tail -100f $ORACLE_HOME/listener.log

(5)、查看FAILED_LOGIN_ATTEMPTS的值

SQL> select * from dba_profiles

修改为30次

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS 30;

修改为无限次（为安全起见，不建议使用）

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

8、oracle sqlplus 连接时，connection to 显示的是问号

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 209:19:56 2012
Copyright (c) 1982, 2009, Oracle.  All rightsreserved.
???:  应该是Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0- 64bit Production
With the Partitioning, Real Application Clusters,Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

解决办法：

SQL> show parameter nls_la
 
NAME                                TYPE       VALUE
------------------------------------ -----------------------------------------
nls_language                        string      SIMPLIFIEDCHINESE

然后修改环境变量：NLS_LANG

[oracle@rac2 ~]$ exportNLS_LANG=american_america.zhs16gbk

如果要使之永久生效，可以添加这个变量到oracle 用户环境 .bash_profile中。

9、Centos7.2配置RemoveIPC特性，在oracle用户下定时备份任务结束时，oracle用户异常退出，导致oracle数据库退出

整改方法是配置修改，将RemoveIPC修改为no，具体操作如下：

# vim /etc/systemd/logind.conf
RemoveIPC=no
# systemctl daemon-reload
# systemctl restart systemd-logind

10、切换oracle账号，报-bash: ulimit: open files: cannot modify limit: Operation not permitted

# vi /etc/security/limits.conf
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft memlock 4000000
oracle hard memlock 4000000

11、12c的安装部署不允许主机名包含下划线

SEVERE: [FATAL] [INS-30131] Initial setup required for the execution of installer validations failed.
   CAUSE: Failed to access the temporary location.
   ACTION: Ensure that the current user has required permissions to access the temporary location.
*ADDITIONAL INFORMATION:*
Exception details
 - PRVG-11322 : One or more node names "yqjdzjk_v08" contain one or more of the following invalid characters "_"

解决办法：

修改hostname去除下划线

hostnamectl set-hostname yqjdzjk-v08

12、ORA-27123: unable to attach to shared memory segment

[root@jzhdb1 bin]stat oracle
Access: (6751/-rwsr-s--x)  Uid: (  502/    grid)   Gid: ( 1001/oinstall)
[root@jzhdb2 bin]stat oracle
Access: (0751/-rwxr-x--x)  Uid: (  502/    grid)   Gid: ( 1001/oinstall)

解决办法：

节点一是6751,节点二是0751，接下来将节点二的oracle文件权限改6751

[root@jzhdb2 bin]# chmod 6751 oracle

13、ORA-12516 解决方法

oracle服务器上某个数据库出现’ ORA-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程’错误,要解决该问题首先查看一下数据库现有的进程数，是否已经达到参数processes的大小。

# 取得数据库目前的进程数
select count(*) from v$process;
# 取得进程数的上限
select value from v$parameter where name = 'processes';
# 取得数据库目前的会话数
 select count(*) from v$session; 
 show parameter sessions;
 show parameter processes;

解决方法：修改会话数与进程数，重启oracle服务

oracle11g的公式：sessions=1.1*processes+5
oracle12c的公式：sessions=1.1*processes+22
export ORACLE_SID=orcl
sqlplus / as sysdba
alter system set processes=2000 scope=spfile;
alter system set sessions=2205 scope=spfile;
shutdown immediate;
startup;

14、ORA-12154 TNScould not resolve the connect identifier specified

问题分析：

即无法解析指定的连接标识符。这说明缺少了一个环境变量，TNS_ADMIN 。

windows的解决方法:

右击 我的电脑 -> 属性 -> 高级 -> 环境变量 -> Administrator 的用户变量 -> 新建 ，

变量名为：TNS_ADMIN
变量值为：%ORACLE_HOME%/NETWORK/ADMIN/

其中 %ORACLE_HOME% 即 Orcale 的安装路径， 其实，设置的该环境变量的值为 tnsnames.ora文件所在路径，特别是重装后或其它操作，忘了设置TNS_ADMIN 变量，PL/SQL 登陆就会报“无法解析指定的连接标识符”的错误 ，而设置TNS_ADMIN变量是为了能够找到 tnsnames.ora。如果本机上安装了ORACLE，并且设置了 ORACLE_HOME 环境变量，那么会自动在%ORACLE_HOME%/NETWORK/ADMIN/位置查找 tnsnames.ora 文件。

linux的处理方法：

oracle@jkttest:/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin$cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_EPOS =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.102.0.240)(PORT = 1521))
EPOS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.102.0.240)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = epos)
    )
  )
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.102.0.240)(PORT = 1521))
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.102.0.240)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

15、ORA-12012,ORA-06502,ORA-06512错误

Sat Sep 29 16:00:00 2018
Errors in file /u01/app/oracle/diag/rdbms/XXX/XXX/trace/XXX_j000_3516.trc:
ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073
ORA-06512: at line 1
Sat Sep 29 16:03:17 2018
Thread 1 advanced to log sequence 137530 (LGWR switch)

查找MOS： ORA-12012: Error on Auto Execute of job SYS.BSLN_MAINTAIN_STATS_JOB (文档 ID 1413756.1) 发现为bug

原因：

The same is addressed in 
Bug 13637859 - "BSLN_MAINTAIN_STATS_JOB FAILS WITH ORA-06502 
which is marked as a duplicate of
Bug 10110625 - DBSNMP.BSLN_INTERNAL RECEIVES ORA-06502

解决方法：

This issue is fixed in 11.2.0.4.
For versions below 11.1.0.7 apply the patch 6282324.Bug 6282324 is fixed in 11.1.0.7 .
For 11.2.0.2 and 11.2.0.3, please apply  Patch 10110625 (if available for your platform and database version).
-- Login as sys user.
 
SQL> sqlplus / as sysdba
 
-- From the sqlplus execute the following:
 
-- Drop the DBSNMP user by executing catnsnmp.sql script.
 
SQL> @$ORACLE_HOME/rdbms/admin/catnsnmp.sql
 
-- Create the DBSNMP user by executing catsnmp.sql
 
SQL> @$ORACLE_HOME/rdbms/admin/catsnmp.sql

16、ORA-12514 TNS：监听程序当前无法识别连接描述符中请求的服务

解决：编辑listener.ora，添加连接配置

  (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = E:\app\oracle\product\12.1.0\dbhome_1)  
      (SID_NAME = ORCL)
    )

完整配置如下：

# listener.ora Network Configuration File: E:\app\oracle\product\12.1.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\app\oracle\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
  (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = E:\app\oracle\product\12.1.0\dbhome_1)  
      (SID_NAME = ORCL)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = iZwwv52zvkplxtZ)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )