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
開啟主庫的sqlnet.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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Dataguard】Oracle多租戶環境對Dataguard的影響Oracle
- profile檔案對sysdba使用者的影響
- 監聽狀態對dataguard及其日誌傳輸的影響
- oracle 11g active dataguard switch over 對ogg的影響Oracle
- Oracle 12.2.0.1.0 PDB丟失資料檔案對CDB的影響Oracle
- 企業檔案加密對員工工作效率的影響有多大？加密
- 【DataGuard】主庫是否為OMF管理方式對使用Grid Control部署物理DataGuard的影響
- 【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響OracleMIT索引
- 檔案傳輸中斷怎麼辦？對檔案會有什麼影響？
- 【DATAGUARD 學習】管理影響備庫的主庫事件事件
- 專案管理對工程質量的影響和對策(轉)專案管理
- shrink 操作對索引的影響索引
- Update操作對索引的影響索引
- LGWR和ARCH下密碼檔案不一致對FAL的影響密碼
- Linux讀寫執行許可權對目錄和檔案的影響Linux
- 臨時資料檔案 offline 對於匯入匯出的影響
- ASM時的OFM特性對影的建資料檔名的影響及為SYSTEM表空間的資料檔案使用別名ASM
- (原)ERP專案小組的人員對專案的影響
- unusable index對DML/QUERY的影響Index
- Arraysize 對consistent get的影響
- mysql event對主從的影響MySql
- 新增欄位對SQL的影響SQL
- 語言對思維的影響
- 源資料檔案(.csv)中的空格串和空串對pandas讀取結果的影響
- append HINT 的對事務的影響APP
- 專案資料視覺化對甲方客戶的影響視覺化
- 充分認識企業文化對專案管理的影響（轉）專案管理
- 充分認識企業文化對專案管理的影響 （轉）專案管理
- nid 對RMAN備份的影響--- 控制檔案中備份記錄全部丟失
- 絕對定位對margin外邊距的影響
- shrink 與rebuild對索引高度的影響對比Rebuild索引
- rman開啟備份優化對備份歸檔的影響優化
- RMAN備份恢復之歸檔日誌對BLOCKRECOVER的影響BloC
- 遊戲暗示對於遊戲玩家的影響遊戲
- INDEX建立方式對SQL的影響IndexSQL
- 關於OPcache對Swoole影響的理解opcache
- 物聯網對企業的影響
- Web-Scale IT：對企業的影響Web