探讨sqlnet.ora文件在dataguard中的影响:
事情是这样的:由于自己很长的一段时间没有打开过自己虚拟机里的DG,自从16年10月搭建好之后。
中间也多多少少利用了主库的节点作为其他的测试,比如EZCONNECT的测试,当中就创建了sqlnet.ora文件。
这么久的时间之后,当我再次开启这个DG的时候,主备库能正常开启,但是不能切换,其中主备库的状态如下视图所示:
----2.2:主库
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE FAILED DESTINATION PRIMARY
----2.4:备库
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ ONLY MAXIMUM PERFORMANCE NOT ALLOWED PHYSICAL STANDBY
[oracle@host01 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-MAR-2017 14:42:07
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/host01/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host01)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
TNS-00583: Valid node checking: unable to parse configuration parameters
Listener failed to start. See the error message(s) above...
关于监听的错误:
TNS-12560: TNS:protocol adapter error
TNS-00583: Valid node checking: unable to parse configuration parameters
打开主库的 sql net.ora查看,里面
[oracle@host01 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/app/oracle/product/10.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)
~
第一的感觉以为是sqlnet.ora文件里面的参数的影响,接着就是把文件里面的所有参数注释掉:如下
[oracle@host01 admin]$ vi sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/app/oracle/product/10.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
#NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)
~
"sqlnet.ora" 6L, 200C written
后面再次尝试去打开主库的监听,结果还是出现同样的报错,依然不能正常启动主库的监听。把 sqlnet.ora文件里所有的内容注释掉都不凑效,只能怀疑是 sqlnet.ora文件的存在了,因为刚才备库
没有这个文件,它的监听能够正常地启动。
后面去把主库的 sqlnet.ora文件删除之后,去启动主库的监听, 最后可以了正常打开了。
再次去查看主库的状态时,变成了一下的状态:
--主库实例:
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE TO STANDBY PRIMARY
#这也就是说DG体系恢复正常状态,可以正常切换。
说了这么多,为了进一步探讨sqlnet.ora对DG的影响,直接影响就会监听的影响,进行了一下的测试:
同时给备库添加sqlnet.ora文件,验证备库的监听是否能再次正常的启动。
---主库主机上:
[oracle@host01 admin]$ ls
listener.ora samples shrept.lst sqlnet.ora sqlnet.ora.bak20170312 sqlnet.ora.bak201703122 tnsnames.ora
[oracle@host01 admin]$
[oracle@host01 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-MAR-2017 14:42:07
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/host01/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host01)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
TNS-00583: Valid node checking: unable to parse configuration parameters
Listener failed to start. See the error message(s) above...
[oracle@host01 admin]$
---备库主机上:
[oracle@oracle admin]$ ls
listener.ora listener.ora.bak listener.ora.ok samples shrept.lst sqlnet.ora sqlnet.ora.bak201703122 tnsnames.ora
[oracle@oracle admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-MAR-2017 14:45:19
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
TNS-00583: Valid node checking: unable to parse configuration parameters
Listener failed to start. See the error message(s) above...
[oracle@oracle admin]$
----分别在主备库中删除sqlnet.ora文件,在重启主备库主机的监听:
--主库上:
[oracle@host01 admin]$ rm sqlnet.ora
[oracle@host01 admin]$ ls
listener.ora samples shrept.lst sqlnet.ora.bak20170312 sqlnet.ora.bak201703122 tnsnames.ora
[oracle@host01 admin]$
[oracle@host01 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-MAR-2017 14:49:35
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/host01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 12-MAR-2017 14:49:35
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/host01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA11GR2" has 1 instance(s).
Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...
Service "SBDB" has 1 instance(s).
Instance "SBDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
--备库上:
[oracle@oracle admin]$ rm sqlnet.ora
[oracle@oracle admin]$ ls
listener.ora listener.ora.bak listener.ora.ok samples shrept.lst sqlnet.ora.bak201703122 tnsnames.ora
[oracle@oracle admin]$
[oracle@oracle admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-MAR-2017 14:48:36
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 12-MAR-2017 14:48:36
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ENMO.oracle.com" has 1 instance(s).
Instance "ENMO", status UNKNOWN, has 1 handler(s) for this service...
Service "ORA11GR2" has 1 instance(s).
Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle admin]$
--以上可以看到,sqlnet.ora无论在主库还是备库,都直接影响对应主机监听的正常启动。DG主备库的监听不相通,就直接影响
DG的正常状态,阻碍主备库之间的日志传输,阻碍DG主备库的角色的切换。当然sqlnet.ora只是对DG上的监听引起TNS-00583,单实例
的库的监听启动并不受sqlnet.ora影响。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2135280/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2135280/