修改oracle数据库连接数(ora-12516)

错误代码:ora-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程

解决方案:

1.使用dba权限连接数据库 
sqlplus / as sysdba
2.查看当前连接和最大连接数
-- 查询当前连接
select count(*) from v$process;
-- 查询最大连接数
select value from v$parameter where name = 'processes';
3.查看当前session数和最大session数
-- 当前session数
select count(*) from v$session;
-- 查看最大session数
select value from v$parameter where name = 'sessions';
4.根据上面的连接数去扩大对应的参数
-- 修改session
alter system set sessions=400 scope = spfile;
-- 修改processes
alter system set processes = 3000 scope = spfile;

5.修改配置之后需要重启服务
shutdown immediate;
 startup mount;
alter database open;
6.如果使用的是pdb数据库，那么需要启动pdb
-- 查看所有的pdb
select con_id,name,open_mode from v$pdbs;
-- 启动pdb
alter pluggable database pdb名称 open;

下面是完整的处理经过

Windows PowerShell
版权所有 (C) 2014 Microsoft Corporation。保留所有权利。

PS C:\Users\Administrator> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on 星期二 6月 2 13:54:44 2020

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


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count(*) from v$process;

  COUNT(*)
----------
       291

SQL> select value from v$parameter where name = 'processes'
  2  ;

VALUE
--------------------------------------------------------------------------------
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     480
shared_server_sessions               integer
SQL> show parameter processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     2
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 processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     2
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> select count(*) from v$process;

  COUNT(*)
----------
       287


SQL> alter system set processes = 3000 scope = spfile;

系统已更改。

SQL> select count(*) from v$session;

  COUNT(*)
----------
       192

SQL> alter system set sessions=400 scope = spfile;

系统已更改。

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 1.0335E+10 bytes
Fixed Size                  5415672 bytes
Variable Size            2919238920 bytes
Database Buffers         7381975040 bytes
Redo Buffers               28135424 bytes
数据库装载完毕。
SQL>
SQL>
SQL> alter database open;

数据库已更改。

SQL> select name,cdb from v$database;

NAME      CDB
--------- ---
ORCL      YES

SQL> select 1 from dual;

         1
----------
         1

SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 NAME1         				  MOUNTED
         4 NAME2                          MOUNTED


SQL> alter pluggable database NAME1 open;

插接式数据库已变更。

SQL> alter pluggable database NAME2 open;

插接式数据库已变更。

SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 NAME1         				  READ WRITE
         4 NAME2            			  READ WRITE

SQL> EXIT;
从 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开
PS C:\Users\Administrator>