修改oracle spfile，配置oracle listener

修改oracle spfile

安装oracle 11.2.0后shutdown数据库之后无法startup，一直报一下错误：

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'

经过反复查询后发现是由文件ORACLE_HOME/dbs/spfile$ORACLE_SID.ora里的一行：*.local_listener='LISTENER_ORCL' 导致的错误，所以需要将此行删除。

但是spfile文件是二进制文件，不可手工编辑。因此，我们需要先将其转换为可编辑的pfile文件，然后再进行修改。转换方法：

oracle@PVG50830111:~> sqlplus / as sysdba

SQL> create pfile from spfile

以上命令执行成功后在$ORACLE_HOME/dbs目录下会有一个init$ORACLE_SID.ora的文件，编辑该文件将*.local_listener='LISTENER_ORCL' 这一行删除。然后启动数据库：

oracle@PVG50830111:~> sqlplus / as sysdba
SQL>startup pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora'

这样数据库就可以成功启动。

spfile和pfile是可以相互转换的，create spfile from pfile命令就可以将pfile文件转换为spfile文件。

关于pfile和spfile文件的比较请参见：http://orafaq.com/node/5

Oracle的启动/关闭过程请参见：http://www.thegeekstuff.com/2009/01/oracle-database-startup-and-shutdown-procedure/

配置listener

启动database后，从客户端连接oracle一直报错：

ORA-12541: TNS:no listener

但是明明已经启动了listener

oracle@PVG50830111:~> ps -ef|grep tnslsnr
oracle     987     1  0 12:16 ?        00:00:00 /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit

用netstat查看端口结果如下

oracle@PVG50830111:~> netstat -antp|grep 1521
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:1521          0.0.0.0:*               LISTEN      5816/tnslsnr
tcp        0      0 127.0.0.1:60646         127.0.0.1:1521          ESTABLISHED 20054/emagent
tcp        0      0 10.58.183.247:30900     10.58.183.166:1521      ESTABLISHED -
tcp        0      0 10.58.183.247:34235     10.58.183.166:1521      ESTABLISHED -
tcp        0      0 127.0.0.1:17648         127.0.0.1:1521          ESTABLISHED 20054/emagent
tcp        0      0 10.58.183.247:34209     10.58.183.166:1521      ESTABLISHED -
tcp        0      0 127.0.0.1:60576         127.0.0.1:1521          ESTABLISHED 20054/emagent
tcp        0      0 127.0.0.1:60868         127.0.0.1:1521          ESTABLISHED 7802/java
tcp        0      0 127.0.0.1:1521          127.0.0.1:60868         ESTABLISHED 32577/oracleorcl
tcp        0      0 10.58.183.247:1521      10.58.183.247:10619     TIME_WAIT   -
tcp        0      0 127.0.0.1:60867         127.0.0.1:1521          ESTABLISHED 7802/java
tcp        0      0 127.0.0.1:1521          127.0.0.1:60586         ESTABLISHED 32395/oracleorcl
tcp        0      0 127.0.0.1:1521          127.0.0.1:60865         ESTABLISHED 32571/oracleorcl
tcp        0      0 10.58.183.247:1521      10.58.183.166:58909     ESTABLISHED 640/oracleorcl
tcp        0      0 127.0.0.1:1521          127.0.0.1:60646         ESTABLISHED 32423/oracleorcl
tcp        0      0 127.0.0.1:1521          127.0.0.1:60576         ESTABLISHED 32372/oracleorcl
tcp        0      0 127.0.0.1:60586         127.0.0.1:1521          ESTABLISHED 7802/java
tcp        0      0 127.0.0.1:60865         127.0.0.1:1521          ESTABLISHED 7802/java
tcp        0      0 127.0.0.1:1521          127.0.0.1:17648         ESTABLISHED 4094/oracleorcl
tcp        0      0 127.0.0.1:1521          127.0.0.1:60866         ESTABLISHED 32573/oracleorcl
tcp        0      0 127.0.0.1:60866         127.0.0.1:1521          ESTABLISHED 7802/java
tcp        0      0 127.0.0.1:1521          127.0.0.1:60867         ESTABLISHED 32575/oracleorcl
tcp        0      0 10.58.183.247:1521      10.58.183.166:58898     ESTABLISHED 500/oracleorcl

经过一番google后在一篇文章(http://www.getshifting.com/wiki/oraclelistener)中找到解决办法。原来是因为oracle listener shouldn't listen on the long hostname or the host's IP-address but on the hosts short hostname

所以将listener.ora文件从之前的

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /home/oracle/app/oracle

修改为

LISTENER =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = PVG50830111)(PORT = 1521))
    )

ADR_BASE_LISTENER = /home/oracle/app/oracle

其中, PVG50830111是hostname命令的结果。保存修改后重启listener：

lsnrctl stop
lsnrctl start

重启完成后，再用netstat -antp|grep 1521查看端口得：

oracle@PVG50830111:~> netstat -antp|grep 1521
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:60646         127.0.0.1:1521          ESTABLISHED 20054/emagent
tcp        0      0 10.58.183.247:30900     10.58.183.166:1521      ESTABLISHED -
tcp        0      0 127.0.0.1:21518         127.0.0.1:1521          TIME_WAIT   -
tcp        0      0 127.0.0.1:21516         127.0.0.1:1521          TIME_WAIT   -
tcp        0      0 10.58.183.247:34235     10.58.183.166:1521      ESTABLISHED -
tcp        0      0 127.0.0.1:17648         127.0.0.1:1521          ESTABLISHED 20054/emagent
tcp        0      0 10.58.183.247:10619     10.58.183.247:1521      ESTABLISHED 25743/ora_pmon_orcl
tcp        0      0 127.0.0.1:21517         127.0.0.1:1521          TIME_WAIT   -
tcp        0      0 10.58.183.247:34209     10.58.183.166:1521      ESTABLISHED -
tcp        0      0 127.0.0.1:60576         127.0.0.1:1521          ESTABLISHED 20054/emagent
tcp        0      0 :::1521                 :::*                    LISTEN      3610/tnslsnr
tcp        0      0 127.0.0.1:60868         127.0.0.1:1521          ESTABLISHED 7802/java
tcp        0      0 127.0.0.1:1521          127.0.0.1:60868         ESTABLISHED 32577/oracleorcl
tcp        0      0 10.58.183.247:1521      10.58.183.247:10619     ESTABLISHED 3610/tnslsnr
tcp        0      0 127.0.0.1:60867         127.0.0.1:1521          ESTABLISHED 7802/java
tcp        0      0 127.0.0.1:1521          127.0.0.1:60586         ESTABLISHED 32395/oracleorcl
tcp        0      0 127.0.0.1:1521          127.0.0.1:60865         ESTABLISHED 32571/oracleorcl
tcp        0      0 10.58.183.247:1521      10.58.183.166:58909     ESTABLISHED 640/oracleorcl
tcp        0      0 127.0.0.1:1521          127.0.0.1:60646         ESTABLISHED 32423/oracleorcl
tcp        0      0 127.0.0.1:1521          127.0.0.1:60576         ESTABLISHED 32372/oracleorcl
tcp        0      0 127.0.0.1:60586         127.0.0.1:1521          ESTABLISHED 7802/java
tcp        0      0 127.0.0.1:60865         127.0.0.1:1521          ESTABLISHED 7802/java
tcp        0      0 127.0.0.1:1521          127.0.0.1:17648         ESTABLISHED 4094/oracleorcl
tcp        0      0 127.0.0.1:1521          127.0.0.1:60866         ESTABLISHED 32573/oracleorcl
tcp        0      0 127.0.0.1:60866         127.0.0.1:1521          ESTABLISHED 7802/java
tcp        0      0 127.0.0.1:1521          127.0.0.1:60867         ESTABLISHED 32575/oracleorcl
tcp        0      0 10.58.183.247:1521      10.58.183.166:58898     ESTABLISHED 500/oracleorc

与之前的结果相比，可以看到状态为LISTEN的那一行改变了。

再从client连接orace，成功！