修改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,成功!