修改SQLNET.ORA导致数据库无法启动

修改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

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

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

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

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

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

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

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

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再次尝试启动数据库，问题消失。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-688877/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-688877/