oracle连接错误20,连接数据库时报错ORA-12520和ORA-12516错误

在本地恢复了一个RAC数据库，通过设置连接数据库报错ORA-12520。

其中本地tnsnames.ora已经进行了设置：

RACTEST2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ser2-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ractest.us.oracle.com)

(INSTANCE_NAME = ractest2)

)

)

RACTEST1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ser1-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ractest.us.oracle.com)

(INSTANCE_NAME = ractest1)

)

)

LISTENERS_RACTEST =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = ser1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = ser2-vip)(PORT = 1521))

)

RACTEST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ser1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = ser2-vip)(PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ractest.us.oracle.com)

)

)

RAC11G2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ser1-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rac11g.us.oracle.com)

(INSTANCE_NAME = rac11g2)

)

)

RAC11G1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ser1-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rac11g.us.oracle.com)

(INSTANCE_NAME = rac11g1)

)

)

LISTENERS_RAC11G =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = ser1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = ser2-vip)(PORT = 1521))

)

RAC11G =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ser1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = ser2-vip)(PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rac11g.us.oracle.com)

)

)

而listener也监听了对应的实例：

$ lsnrctl status

LSNRCTL for Solaris: Version 11.1.0.6.0 - Production on 05-9月-2008 11:05:52

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

正在连接到(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

LISTENER的STATUS

------------------------别名LISTENER_SER2版本TNSLSNR for Solaris: Version 11.1.0.6.0 - Production启动日期02-9月-2008 15:46:25正常运行时间2天19小时19分28秒跟踪级别off安全性ON: Local OS Authentication

SNMP                      OFF监听程序参数文件/data/oracle/product/11.1/database/network/admin/listener.ora监听程序日志文件/data/oracle/diag/tnslsnr/ser2/listener_ser2/alert/log.xml监听端点概要...

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

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

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

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ser2)(PORT=8800))(Presentation=HTTP)(Session=RAW))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ser2)(PORT=7788))(Presentation=FTP)(Session=RAW))服务摘要..服务"+ASM"包含1个例程。例程"+ASM2",状态READY,包含此服务的1个处理程序...服务"+ASM_XPT"包含1个例程。例程"+ASM2",状态READY,包含此服务的1个处理程序...服务"rac11g.us.oracle.com"包含2个例程。例程"rac11g1",状态READY,包含此服务的1个处理程序...例程"rac11g2",状态READY,包含此服务的1个处理程序...服务"rac11gXDB.us.oracle.com"包含2个例程。例程"rac11g1",状态READY,包含此服务的1个处理程序...例程"rac11g2",状态READY,包含此服务的1个处理程序...服务"rac11g_XPT.us.oracle.com"包含2个例程。例程"rac11g1",状态READY,包含此服务的1个处理程序...例程"rac11g2",状态READY,包含此服务的1个处理程序...服务"ractest.us.oracle.com"包含2个例程。例程"ractest1",状态READY,包含此服务的1个处理程序...例程"ractest2",状态READY,包含此服务的2个处理程序...服务"ractestXDB.us.oracle.com"包含2个例程。例程"ractest1",状态READY,包含此服务的1个处理程序...例程"ractest2",状态READY,包含此服务的1个处理程序...服务"ractest_XPT.us.oracle.com"包含2个例程。例程"ractest1",状态READY,包含此服务的1个处理程序...例程"ractest2",状态READY,包含此服务的2个处理程序...命令执行成功

而通过RAC11G1、RAC11G2和RAC11G都无法连接数据库：

SQL> conn system/test@rac11g1

ERROR:

ORA-12520: TNS:监听程序无法为请求的服务器类型找到可用的处理程序

SQL> conn system/test@rac11g2

ERROR:

ORA-12520: TNS:监听程序无法为请求的服务器类型找到可用的处理程序

SQL> conn system/test@rac11g

ERROR:

ORA-12520: TNS:监听程序无法为请求的服务器类型找到可用的处理程序

SQL> conn system/test@ractest1已连接。SQL> conn system/test@ractest2已连接。SQL> conn system/test@ractest已连接。

本地原有的RAC环境可以访问，新恢复的RAC存在问题，尝试通过10g的简便方式连接数据库报错如下：

SQL> conn system/test@172.0.2.63/rac11g.us.oracle.com

ERROR:

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

警告:您不再连接到ORACLE。

最后通过lsnrctl service命令发现了问题：

$ lsnrctl services

LSNRCTL for Solaris: Version 11.1.0.6.0 - Production on 05-9月-2008 11:11:27

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

正在连接到(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))服务摘要..服务"+ASM"包含1个例程。例程"+ASM2",状态READY,包含此服务的1个处理程序...处理程序:

"DEDICATED"已建立:10已拒绝:0状态:ready

LOCAL SERVER服务"+ASM_XPT"包含1个例程。例程"+ASM2",状态READY,包含此服务的1个处理程序...处理程序:

"DEDICATED"已建立:10已拒绝:0状态:ready

LOCAL SERVER服务"rac11g.us.oracle.com"包含2个例程。例程"rac11g1",状态READY,包含此服务的1个处理程序...处理程序:

"DEDICATED"已建立:0已拒绝:0状态:blocked

REMOTE SERVER

(ADDRESS=(PROTOCOL=TCP)(HOST=172.0.2.58)(PORT=1521))例程"rac11g2",状态READY,包含此服务的1个处理程序...处理程序:

"DEDICATED"已建立:0已拒绝:0状态:blocked

REMOTE SERVER

(ADDRESS=(PROTOCOL=TCP)(HOST=172.0.2.59)(PORT=1521))服务"rac11gXDB.us.oracle.com"包含2个例程。例程"rac11g1",状态READY,包含此服务的1个处理程序...处理程序:

"D000"已建立:0已被拒绝:0当前: 0最大: 1022状态: ready

DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=ser1)(PORT=39999))例程"rac11g2",状态READY,包含此服务的1个处理程序...处理程序:

"D000"已建立:0已被拒绝:0当前: 0最大: 1022状态: ready

DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=ser2)(PORT=37818))服务"rac11g_XPT.us.oracle.com"包含2个例程。例程"rac11g1",状态READY,包含此服务的1个处理程序...处理程序:

"DEDICATED"已建立:0已拒绝:0状态:blocked

REMOTE SERVER

(ADDRESS=(PROTOCOL=TCP)(HOST=172.0.2.58)(PORT=1521))例程"rac11g2",状态READY,包含此服务的1个处理程序...处理程序:

"DEDICATED"已建立:0已拒绝:0状态:blocked

REMOTE SERVER

(ADDRESS=(PROTOCOL=TCP)(HOST=172.0.2.59)(PORT=1521))服务"ractest.us.oracle.com"包含2个例程。例程"ractest1",状态READY,包含此服务的1个处理程序...处理程序:

"DEDICATED"已建立:1已拒绝:0状态:ready

REMOTE SERVER

(ADDRESS=(PROTOCOL=TCP)(HOST=ser1)(PORT=1521))例程"ractest2",状态READY,包含此服务的2个处理程序...处理程序:

"DEDICATED"已建立:1已拒绝:0状态:ready

LOCAL SERVER

"DEDICATED"已建立:0已拒绝:0状态:ready

REMOTE SERVER

(ADDRESS=(PROTOCOL=TCP)(HOST=ser2)(PORT=1521))服务"ractestXDB.us.oracle.com"包含2个例程。例程"ractest1",状态READY,包含此服务的1个处理程序...处理程序:

"D000"已建立:0已被拒绝:0当前: 0最大: 1022状态: ready

DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=ser1)(PORT=42266))例程"ractest2",状态READY,包含此服务的1个处理程序...处理程序:

"D000"已建立:0已被拒绝:0当前: 0最大: 1022状态: ready

DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=ser2)(PORT=39883))服务"ractest_XPT.us.oracle.com"包含2个例程。例程"ractest1",状态READY,包含此服务的1个处理程序...处理程序:

"DEDICATED"已建立:1已拒绝:0状态:ready

REMOTE SERVER

(ADDRESS=(PROTOCOL=TCP)(HOST=ser1)(PORT=1521))例程"ractest2",状态READY,包含此服务的2个处理程序...处理程序:

"DEDICATED"已建立:1已拒绝:0状态:ready

LOCAL SERVER

"DEDICATED"已建立:0已拒绝:0状态:ready

REMOTE SERVER

(ADDRESS=(PROTOCOL=TCP)(HOST=ser2)(PORT=1521))命令执行成功

服务RAC11G指向了源数据库地址，而不是当前的主机地址。

检查数据库中初始化参数设置：

SQL> show parameter local_listener

NAME                   TYPE        VALUE

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

local_listener         string      (ADDRESS=(PROTOCOL=TCP)(HOST=172.0.2.58)(PORT=1521))

这里的设置果然有问题，在恢复的过程中，没有设置为本机的IP地址。

修改数据库参数并重启：

SQL> alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=172.0.2.62)(PORT=1521))'

2  scope = spfile sid = 'rac11g1';

系统已更改。

SQL> alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=172.0.2.63)(PORT=1521))'

2  scope = spfile sid = 'rac11g2';

系统已更改。

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

ORACLE例程已经启动。

Total System Global Area 1.7108E+10 bytes

Fixed Size                  2101632 bytes

Variable Size            3814182528 bytes

Database Buffers         1.3288E+10 bytes

Redo Buffers                4431872 bytes数据库装载完毕。数据库已经打开。

另外的节点也重启数据库服务器。

SQL> conn system/test@rac11g已连接。SQL> conn system/test@rac11g1已连接。SQL> conn system/test@rac11g2已连接。

问题解决。