oracle 连接时 tns closed,ORA-12537: TNS:connection closed連接關閉，Oracle報錯處理

1. 報錯信息ORA-12537: TNS:connection closed連接關閉

[oracle@pldb236 admin]$ rlwrap sqlplus powerdesk/pd141118@PD236

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 23 14:16:31 2015

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

ERROR:

ORA-12537: TNS:connection closed

Enter user-name:

檢查監聽正常，oracle服務也是正常啟動的，但是登錄不進去。

[oracle@jcapp ~]$ tnsping PD236

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 05-DEC-2017 16:57:47

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:

/opt/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

[oracle@jcapp ~]$

2.解決方案

[oracle@pldb236 bin]$ cd $ORACLE_HOME/bin/

[oracle@pldb236 bin]$

[oracle@pldb236 bin]$

[oracle@pldb236 bin]$ ll oracle

-rwsr-s--x. 1 oracle oinstall 210823844 Jul 31 13:21 oracle

[oracle@pldb236 bin]$

[oracle@pldb236 bin]$ chmod 6571 oracle

[oracle@pldb236 bin]$

[oracle@pldb236 bin]$ ll oracle

-r-srws--x. 1 oracle oinstall 210823844 Jul 31 13:21 oracle

[oracle@pldb236 bin]$

[oracle@pldb236 bin]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 23 14:20:09 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

ok可以連接上了，問題初步解決

3，不過3分鍾后，又不行了，登錄不上去。

去查看lsnrctl狀態：

[oracle@pldb236 bin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-NOV-2015 14:30:33

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.180.236)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 23-NOV-2015 14:30:19

Uptime 0 days 0 hr. 0 min. 13 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /oracle/app/oracle/diag/tnslsnr/pldb236/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.180.236)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "powerdes" has 1 instance(s).

Instance "powerdes", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@pldb236 bin]$

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

看到后台alert的日志報錯如下：

Mon Nov 23 14:32:00 2015

ORA-00020: maximum number of processes 150 exceeded

ORA-20 errors will not be written to the alert log for

the next minute. Please look at trace files to see all

the ORA-20 errors.

Mon Nov 23 14:32:47 2015

Process m000 submission failed with error = 20

Mon Nov 23 14:33:02 2015

ORA-00020: maximum number of processes 150 exceeded

ORA-20 errors will not be written to the alert log for

the next minute. Please look at trace files to see all

the ORA-20 errors.

Mon Nov 23 14:34:03 2015

ORA-00020: maximum number of processes 150 exceeded

ORA-20 errors will not be written to the alert log for

the next minute. Please look at trace files to see all

the ORA-20 errors.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

解決方案1：

lsnrctl stop 5分鍾后，再lsnrctl start起來，問題解決了，是應用程序一直不停的連接數據庫，占滿了連接池導致的。

解決方案2：

查看oracle的連接數，果然為150

SQL> show parameter processes;

NAME TYPE VALUE

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

aq_tm_processes integer 0

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 150

SQL>

SQL>

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

分析原因：

SQL> select count(1) from v$session t where t.status='INACTIVE' and t.username='PLAS';

COUNT(1) ----------

88

SQL>

SQL>

SQL> select count(1) from v$session t where t.status='INACTIVE' and t.username='PLAS';

COUNT(1) ----------

5

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

修改配置文件：

[oracle@pldb236 ~]$find /oracle -name *init.ora*

/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/init.ora

/oracle/app/oracle/product/11.2.0/dbhome_1/srvm/admin/init.ora

/oracle/app/oracle/admin/powerdes/pfile/init.ora.7112015171232

[oracle@pldb236 ~]$

1

2

3

4

5

改動連接數，並且寫入參數文件

alter system set processes=500 scope = spfile;

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

System altered.

SQL> create pfile from spfile;

File created.

SQL>

1

2

3

4

5

6

7

8

9

10

關閉重啟oracle實例，啟動就可以看到最大連接數已經變成了500，問題解決

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@pldb236 ~]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 23 23:09:00 2015

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

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 6680915968 bytes

Fixed Size 2213936 bytes

Variable Size 4362078160 bytes

Database Buffers 2281701376 bytes

Redo Buffers 34922496 bytes

Database mounted.

Database opened.

SQL>

SQL>

SQL> show parameter processes;

NAME TYPE VALUE

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

aq_tm_processes integer 0

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 500

SQL>

問題解決。

ORA-12162: TNS:net service name is incorrectly specified 如何解決

今天使用oracle用戶登錄sqlplus出現“ORA-12162: TNS:net service name is incorrectly specified”。

如下：

[oracle@oracle ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 29 19:19:26 2012

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

Enter user-name: sys

Enter password:

ERROR:

ORA-12162: TNS:net service name is incorrectly specified

網上查詢是沒有設置ORACLE_SID環境變量原因。

1、查看是否設置環境變量，用“echo  $ORACLE_SID”查詢，如下：

[oracle@oracle ~]$ echo $ORACLE_SID

[oracle@oracle ~]$

2、設置環境變量，用"vi .bash_profile"編輯命令，在其后加 "export  ORACLE_SID=oracle13g"。

3、使用“source .bash_profile”命令，使環境變量生效。

4、再使用oracle用戶登錄sqlplus，成功。如下：

[oracle@oracle ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 29 19:41:43 2012

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

SQL> conn /as sysdba

Connected to an idle instance.