Oracle数据库服务器地址变更与监听配置修改完整指南

# 备份监听器配置文件
cp $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora.bak
cp $ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.ora.bak

# 备份spfile（如使用）
CREATE PFILE='/tmp/pfile.ora' FROM SPFILE;

# 原配置
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.135)(PORT = 1521))
    )
  )

# 修改为
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.59)(PORT = 1521))
    )
  )

-- 检查当前值
SELECT value FROM v$parameter WHERE name = 'local_listener';

-- 修改参数（分步执行更安全）
ALTER SYSTEM SET local_listener='' SCOPE=BOTH;
ALTER SYSTEM SET local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.59)(PORT=1521))' SCOPE=BOTH;

-- 强制服务注册
ALTER SYSTEM REGISTER;

# 原配置
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.135)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

# 修改为
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.59)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

lsnrctl stop
lsnrctl start
lsnrctl status

lsnrctl services

tnsping ORCL
sqlplus system/password@ORCL

-- 先重置参数再设置
ALTER SYSTEM RESET local_listener SCOPE=SPFILE;
ALTER SYSTEM SET local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.59)(PORT=1521))' SCOPE=BOTH;