问题描述
使用 sqldeveloper 工具登录数据库时出现如下报错:
问题定位
1.首先怀疑可能是监听有问题或者防火墙处于开启状态,但是报错应该是ORA-28040 或ORA-12170。
为了保险起见,还是检查了监听和防火墙的状态:
$ lsnrctl status;
# firewall-cmd --state
2.然后怀疑可能是连接数达到上限,于是查询相关参数。
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 300
SQL> show parameter sessions;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
sessions integer 472
shared_server_sessions integer
SQL> select count(*) from v$process ;
COUNT(*)
----------
299
SQL> select count(*) from v$session;
COUNT(*)
----------
288
所以,基本可以定位出原因是process满了。
处理方法
修改参数值
SQL> alter system set processes=1000 scope=spfile;
System altered.
需要重启数据库后生效
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 4999610368 bytes
Fixed Size 2934648 bytes
Variable Size 1258293384 bytes
Database Buffers 3724541952 bytes
Redo Buffers 13840384 bytes
Database mounted.
Database opened.
再次查看参数值发现已经更新成功
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 1000
SQL> show parameter sessions;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
sessions integer 1522
shared_server_sessions integer
效果验证
再次尝试用sqldeveloper登录数据库,可成功登录。
连接Oracle 12c R2 报错ORA-28040:No matching authentication protocal