一, 描述
数据库版本 11g, 单实例 ADG.
操作系统版本为 SUSE 11 SP3.
主库 IP:192.168.1.11
备库 IP:192.168.1.12
二, 需求
主库需要修改 IP 地址, 保证 ADG 环境正常运行.
主库 IP 改为: 192.168.1.10, 备库 IP 不变.
三, 操作步骤
1, 检查主备库当前是否正常运行
- oracle@mmpt-SRJ:~> dgmgrl /
- DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
- DGMGRL> show configuration;
- Configuration - dg_broker
- Protection Mode: MaxPerformance
- Databases:
- mmpt - Primary database
- mmptdg - Physical standby database
- Fast-Start Failover: DISABLED
- Configuration Status:
- SUCCESS
- DGMGRL>
2, 关闭主, 备库
- lsnrctl stop listener
- shutdown immediate;
3, 修改操作系统 IP 地址
mmpt-SRJ:/etc/sysconfig/network # ls
config dhcp if-down.d if-up.d ifcfg-eth0 ifcfg-lo ifcfg.template ifroute-lo providers scripts
- mmpt-SRJ:/etc/sysconfig/network # vi ifcfg-eth0
- BOOTPROTO='static'
- BROADCAST=''ETHTOOL_OPTIONS=''
- IPADDR='192.168.1.10/24'
- MTU=''NAME='82540EM Gigabit Ethernet Controller'NETWORK=''
- REMOTE_IPADDR=''STARTMODE='auto'USERCONTROL='no'
- ~
- ~
- "ifcfg-eth0" 10L, 188C written
- mmpt-SRJ:/etc/sysconfig/network # /etc/init.d/network restart
Shutting down network interfaces:
eth0 device: Intel Corporation 82540EM Gigabit Ethernet Co done
............. 重新使用新 IP 地址连接...................
4, 主库修改监听文件 listener.ora
- oracle@mmpt-SRJ:/fantlam/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin> vi listener.ora
- SID_LIST_LISTENER =
- (SID_LIST =
- (SID_DESC =
- (GLOBAL_DBNAME = mmpt)
- (ORACLE_HOME = /fantlam/oracle/app/oracle/product/11.2.0/dbhome_1)
- (SID_NAME = mmpt)
- )
- )
- LISTENER =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
4, 主库修改监听文件 tnsnames.ora
- oracle@mmpt-SRJ:/fantlam/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin> vi tnsnames.ora
- # tnsnames.ora Network Configuration File: /fantlam/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
- # Generated by Oracle configuration tools.
- MMPT =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = mmpt)
- )
- )
- mmptdg =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = mmptdg)
- )
- )
4, 主库修改 hosts 文件
- mmpt-SRJ:~ # vi /etc/hosts
- #
- # hosts This file describes a number of hostname-to-address
- # mappings for the TCP/IP subsystem. It is mostly
- # used at boot time, when no name servers are running.
- # On small systems, this file can be used instead of a
- # "named" name server.
- # Syntax:
- #
- # IP-Address Full-Qualified-Hostname Short-Hostname
- #
- 127.0.0.1 localhost
- # special IPv6 addresses
::1 localhost ipv6-localhost ipv6-loopback
- fe00::0 ipv6-localnet
- ff00::0 ipv6-mcastprefix
- ff02::1 ipv6-allnodes
- ff02::2 ipv6-allrouters
- ff02::3 ipv6-allhosts
- #192.168.1.11 suse11.site suse11
- 192.168.1.10 mmpt-SRJ mmpt-SRJ
- 192.168.1.12 mmpt-SRJ-dg mmpt-SRJ-dg
5, 备库不需要修改 listener.ora
- oracle@mmpt-SRJ-dg:/fantlam/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin> cat listener.ora
- SID_LIST_LISTENER =
- (SID_LIST =
- (SID_DESC =
- (GLOBAL_DBNAME = mmptdg)
- (ORACLE_HOME = /fantlam/oracle/app/oracle/product/11.2.0/dbhome_1)
- (SID_NAME = mmptdg)
- )
- )
- LISTENER =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
- )
6, 备库修改监听文件 tnsnames.ora
- oracle@mmpt-SRJ-dg:/fantlam/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin> vi tnsnames.ora
- # tnsnames.ora Network Configuration File: /fantlam/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
- # Generated by Oracle configuration tools.
- MMPT =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = mmpt)
- )
- )
- mmptdg =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = mmpt-SRJ-dg)
- )
- )
"tnsnames.ora" 21L, 529C written
7, 备库修改 hosts 文件
- mmpt-SRJ-dg:~ # vi /etc/hosts
- #
- # hosts This file describes a number of hostname-to-address
- # mappings for the TCP/IP subsystem. It is mostly
- # used at boot time, when no name servers are running.
- # On small systems, this file can be used instead of a
- # "named" name server.
- # Syntax:
- #
- # IP-Address Full-Qualified-Hostname Short-Hostname
- #
- 127.0.0.1 localhost
- # special IPv6 addresses
::1 localhost ipv6-localhost ipv6-loopback
- fe00::0 ipv6-localnet
- ff00::0 ipv6-mcastprefix
- ff02::1 ipv6-allnodes
- ff02::2 ipv6-allrouters
- ff02::3 ipv6-allhosts
- 192.168.1.12 mmpt-SRJ-dg mmpt-SRJ-dg
- 192.168.1.10 mmpt-SRJ mmpt-SRJ
- ~
- "/etc/hosts" 24L, 721C written
8, 启动主备库监听, 测试
- lsnrctl start listener
- tnsping mmpt
- tnsping mmpt-dg
输出如下:
- oracle@mmpt-SRJ:~> lsnrctl start
- LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAY-2018 16:31:16
Copyright (c) 1991, 2009, Oracle. All rights reserved.
- Starting /fantlam/oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
- TNSLSNR for Linux: Version 11.2.0.1.0 - Production
- System parameter file is /fantlam/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
- Log messages written to /fantlam/oracle/app/oracle/diag/tnslsnr/mmpt-SRJ/listener/alert/log.xml
- Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.10)(PORT=1521)))
- Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1521)))
- STATUS of the LISTENER
- ------------------------
- Alias LISTENER
- Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
- Start Date 17-MAY-2018 16:31:16
Uptime 0 days 0 hr. 0 min. 0 sec
- Trace Level off
- Security ON: Local OS Authentication
- SNMP OFF
- Listener Parameter File /fantlam/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
- Listener Log File /fantlam/oracle/app/oracle/diag/tnslsnr/mmpt-SRJ/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.10)(PORT=1521)))
Services Summary...
Service "mmpt" has 1 instance(s).
Instance "mmpt", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
- oracle@mmpt-SRJ:~> tnsping mmpt
- TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 17-MAY-2018 16:31:26
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
- Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mmpt)))
- OK (0 msec)
- oracle@mmpt-SRJ:~> tnsping mmptdg
- TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 17-MAY-2018 16:31:31
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
- Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mmptdg)))
- OK (0 msec)
- oracle@mmpt-SRJ:~>
8, 启动主备库
先启动备库, 再启动主库
oracle@mmpt-SRJ-dg:~> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 17 16:32:42 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
- Total System Global Area 784998400 bytes
- Fixed Size 2217464 bytes
- Variable Size 473958920 bytes
- Database Buffers 306184192 bytes
- Redo Buffers 2637824 bytes
- Database mounted.
- Database opened.
- SQL>
9, 验证 ADG
- mmpt-SRJ-dg:~ # su - oracle
- oracle@mmpt-SRJ-dg:~> dgmgrl /
- DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
- DGMGRL> show configuration;
- Configuration - dg_broker
- Protection Mode: MaxPerformance
- Databases:
- mmpt - Primary database
- mmptdg - Physical standby database
- Fast-Start Failover: DISABLED
- Configuration Status:
- SUCCESS
- DGMGRL>
10, 原主库修改
edit database mmpt set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=mmpt)(INSTANCE_NAME=mmpt)(SERVER=DEDICATED)))';
11, 切换测试
- oracle@mmpt-SRJ-dg:~> dgmgrl /
- DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
- DGMGRL> show configuration;
- Configuration - dg_broker
- Protection Mode: MaxPerformance
- Databases:
- mmptdg - Primary database
- mmpt - Physical standby database
- Fast-Start Failover: DISABLED
- Configuration Status:
- SUCCESS
- DGMGRL> connect sys/oracle@mmpt
Connected.
DGMGRL> switchover to mmpt;
Performing switchover NOW, please wait...
New primary database "mmpt" is opening...
Operation requires shutdown of instance "mmptdg" on database "mmptdg"
Shutting down instance "mmptdg"...
- ORA-01109: database not open
- Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "mmptdg" on database "mmptdg"
Starting instance "mmptdg"...
ORACLE instance started.
- Database mounted.
- Database opened.
- Switchover succeeded, new primary is "mmpt"
- DGMGRL>
来源: http://blog.51cto.com/roidba/2117581