修改sqlnet.ora這種貌似完全無害的檔案，也可能導致資料庫無法啟動。

今天在一個RAC資料庫上進行測試，在嘗試重啟時，碰到了引數錯誤：

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENERS_RAC'

開始懷疑是有人手工修改了REMOTE_LISTENER引數，但是目前的引數值LISTENERS_RAC應該是在tnsnames.ora中進行的配置。莫非是tnsnames.ora檔案被修改。

檢查$ORACLE_HOME/network/admin/tnsnames.ora檔案：

$ more tnsnames.ora
# tnsnames.ora.db210-rac1 Network Configuration File: /opt/oracle/product/10.2.0/db10g/network/admin/tnsnames.ora.db210-rac1
# Generated by Oracle configuration tools.

LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db210-rac1vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = db210-rac2vip)(PORT = 1521))
)

RAC2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db210-rac2vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)
      (INSTANCE_NAME = rac2)
    )
)

RAC1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db210-rac1vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)
      (INSTANCE_NAME = rac1)
    )
)

從語法上看不到什麼錯誤，為什麼Oracle無法解析這個簡單監聽服務名呢，透過tnsping命令檢查語法錯誤：

$ tnsping listeners_rac

TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 04-MAR-2011 20:37:19

Used parameter files:
/opt/oracle/product/10.2.0/db10g/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

嘗試測試其他服務名，錯誤依舊：

$ tnsping rac2

TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 04-MAR-2011 20:40:09

Used parameter files:
/opt/oracle/product/10.2.0/db10g/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

看來問題不是不可見字元引起的，應該是其他的原因。觀察提示資訊中存在的sqlnet.ora配置檔案，於是檢查了一下。

$ more sqlnet.ora
NAMES.DIRECTORY_PATH= (EZCONNECT)

有人手工修改了sqlnet.ora，將連線方式設定為簡化連線這一種方式了。

顯然，這導致了Oracle不去解析tnsnames.ora檔案。

修改檔案，註釋掉sqlnet.ora中的配置：

$ vi sqlnet.ora
#NAMES.DIRECTORY_PATH= (EZCONNECT)

再次嘗試服務名：

$ tnsping rac2

TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 04-MAR-2011 20:08:23

Used parameter files:
/opt/oracle/product/10.2.0/db10g/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db210-rac2vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rac) (INSTANCE_NAME = rac2)))
OK (10 msec)
$ tnsping listeners_rac

TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 04-MAR-2011 20:09:10

Used parameter files:
/opt/oracle/product/10.2.0/db10g/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = db210-rac1vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = db210-rac2vip)(PORT = 1521)))
OK (10 msec)

這次Oracle可以正確的解析服務名的配置，嘗試啟動資料庫：

SQL> startup
ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENERS_RAC'
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
[oracle@db210-rac1 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 4 20:09:30 2011

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2041976 bytes
Variable Size             687871880 bytes
Database Buffers         1442840576 bytes
Redo Buffers               14729216 bytes
Database mounted.
Database opened.

直接啟動，仍然出現同樣的錯誤，退出sqlplus再次嘗試啟動資料庫，問題消失。

修改SQLNET.ORA導致資料庫無法啟動

相關文章