sqlnet.ora文件引起的TNS-00583对dataguard的影响

探讨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 文件：
打开主库的 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/