在搭建DG环境的过程中,我尝试通过duplicate database方式来创建standby数据库,在测试TNS连通性时,遇到了错误
[oracle@voel5 admin]$ export ORACLE_SID=STDBY
[oracle@voel5 admin]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 11 18:29:48 2013Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn sys/Oracle123@STDBY
ERROR:ORA-12528: TNS:listener: all appropriate instances are blocking new connections
问题的原因
测试conn 连接的standby database 当前状态为nomount状态,且数据库的监听程序配置为自动注册,那么就需要实例自动注册进监听,而只有当数据库启动到mount状态且pmon进程启动后,实例才会自动注册进监听。因此,当前nomount状态的数据库其实就没有注册到监听程序中,所以连接被拒绝。
如果在listener.ora文件中添加静态监听配置条目也可以解决该问题。
问题的解决
方案1:
使用oracle10g引入的一个特性,在tnsnames.ora文件中的CONNECT_DATA里添加(UR=A),如:
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STDBY)(UR=A)
)
)
方案2:
在监听程序的配置文件listener.ora文件中添加静态监听配置,如:
[grid@standby admin]$ cat listener.ora
# listener.ora Network Configuration File: /g01/app/grid/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STDBY)
(ORACLE_HOME = /s01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = STDBY)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /g01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
注意:参照 方案2 调整后,需要重启监听程序才生效。
在进行数据库登陆,发现就正常了:
[oracle@standby ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 14 23:37:07 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn sys/Oracle123@STDBY AS SYSDBA
Connected.
SQL>