01. 在虚拟机上装的oracle数据库
02. 虚拟机重启动后ip地址改变
03. 因为ip地址改变,所以TNSListener无法启动
04. 修改listener.ora,
将ip地址改为localhost
# listener.ora Network Configuration
File:
C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC
=
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
05.
修改后listerer可以启动,但是数据库无法连接,报TNS错误
SQL> conn sys/pcs@hmihd as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service
requested in connect
descriptor
06. 执行lsnrctl
services后发现数据库没有自动注册到listener
C:\Documents and
Settings\luke> lsnrctl services
LSNRCTL for 32-bit Windows: Version
10.2.0.1.0 - Production on 22-SEP-2011 11:54
:16
Copyright (c) 1991, 2005,
Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1
handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
07.
默认状态下oracle的PMON进程会自动注册到listener,不知道为什么linstener的ip地址后,数据库无法自动注册。
08. 解决的办法为:在数据库的参数文件中增加一项
local_listener = "localhost";
09.
由于数据库默认使用的是SPFILE,而SPFILE的内容是无法编辑的,所以需要先将SPFILE转换为PFILE,然后在PFILE中增加参数local_listener,然后再将PFILE转换为SPFILE。在此过程中需要能连接到另一个可以启动的数据库,并以sys用户登录。
10.
先找到SPFILE,默认在目录:C:\oracle\product\10.2.0\db_1\dbs
11. 用sys登录到一个可用数据库,然后执行
SQL> create pfile='d:\luke\tmp\PFILEPCSDBSG.ORA'
from spfile='d:\luke\tmp\SPFILEPCSDBSG.ORA';
生成PIFLE:PFILEPCSDBSG.ORA
12. 编辑PFILEPCSDBSG.ORA增加一行
*.local_listener='localhost'
13. 将PFILE转化为SPFILE
SQL> create spfile='d:\luke\tmp\SPFILEPCSDBSG.ORA'
from pfile='d:\luke\tmp\PFILEPCSDBSG.ORA';
14.
将SPFILE覆盖原来的文件,重新启动数据库,连接正常。