实验课题：主备切换及IP互换.
应用场景：当服务器系统版本配置跟不上业务需求，此时就需要给该库搭建配置较高的备库，然后主备切换再进行IP互换，使得客户端访问新主库时不修改任何参数.这种需求在实际生产中是常有的.
1、原始数据
主库IP：192.168.133.150(需改为192.168.133.151)
备库IP：192.168.133.151(需改为192.168.133.150)
[oracle@oel ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.133.150 oel
[oracle@oeldg ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.133.151 oeldg
主库
SQL> show parameter convert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
log_file_name_convert string
备库
SQL> show parameter convert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /u01/app/oracle/oradata/orcl15
0/, /u01/app/oracle/oradata/or
cl151/
log_file_name_convert string /u01/app/oracle/oradata/orcl15
0/, /u01/app/oracle/oradata/or
cl151/, /u01/app/oracle/fast_r
ecovery_area/orcl150/, /u01/ap
p/oracle/fast_recovery_area/or
cl151/
2、convert参数
因后续涉及switchover切换，先在主库设置convert参数.
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl151/','/u01/app/oracle/oradata/orcl150/' scope=spfile;
SQL> alter system set log_file_name_convert='/u01/app/oracle/fast_recovery_area/orcl151/','/u01/app/oracle/fast_recovery_area/orcl150/',' /u01/app/oracle/oradata/orcl151/','/u01/app/oracle/oradata/orcl150/' scope=spfile;
SQL> shutdown immediate
SQL> startup
3、switchover切换
主切备
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> shutdown abort
SQL> startup mount
备切主
SQL> alter database commit to switchover to primary with session shutdown;
SQL> alter database open;
4、生成pfile
主备库生成pfile文件，以便后续修改.
5、IP互换
说明：IP互换前，主备库停数据库、监听.
5.1、新主库IP修改
[root@oeldg ~]# vi /etc/sysconfig/network-scripts/ifcfg-ens33
……
IPADDR=192.168.133.150
……
说明：将IPADDR=192.168.133.151修改为IPADDR=192.168.133.150
5.2、新备库IP修改
[root@oel ~]# vi /etc/sysconfig/network-scripts/ifcfg-ens33
……
IPADDR=192.168.133.151
……
将IPADDR=192.168.133.150修改为IPADDR=192.168.133.151
5.3、重启网卡
说明：主备库重启网卡.
[root@oeldg ~]# systemctl restart network.service
Connection was reset.
Last login: Sun Aug 28 16:24:00 2022 from 192.168.133.1
注意：此时需要用修改后的IP重新连接服务器，主机oeldg对应的192.168.133.151此时变成192.168.133.150去连接.
6、修改hosts
使用新IP登陆主备库，修改/etc/hosts文件
6.1、新主库
[root@oeldg ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.133.150 oeldg
将192.168.133.151修改为192.168.133.150
6.2、新备库
[root@oel ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.133.151 oel
将192.168.133.150修改为192.168.133.151
7、TNS文件
修改tnsnames.ora文件.
7.1、主库
[oracle@oeldg admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL150 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl150)
)
)
ORCL151 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl151)
)
)
说明：不做修改.
7.2、备库
[oracle@oel admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL150 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl150)
)
)
ORCL151 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl151)
)
)
说明：不做修改.
8、监听文件
修改监听listener.ora文件
8.1、新主库
[oracle@oeldg admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl150)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl150)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.150)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
说明：将GLOBAL_DBNAME = orcl151修改为GLOBAL_DBNAME = orcl150，HOST = 192.168.133.151修改为HOST = 192.168.133.150，SID_NAME = orcl151修改为SID_NAME = orcl150。
8.2、新备库
[oracle@oel admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl151)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl151)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.151)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
说明：蓝色为新增，将HOST = 192.168.133.150修改为HOST = 192.168.133.151
9、修改pfile
在主备库修改pfile文件后以该文件生成spfile启动数据库.
以下为新主库修改的参数.
*.log_archive_dest_1='location=/u01/app/oracle/fast_recovery_area/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl150'
*.db_unique_name='ORCL150'
*.log_archive_dest_2='service=ORCL151 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl151'
注意：备库*.audit_trail='db'参数需要设置为none.
10、启动服务
启动主备库监听、数据库.
[oracle@oeldg admin]$ lsnrctl start
[oracle@oel admin]$ lsnrctl start
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl151.ora';
SQL> create spfile from pfile;
SQL> shutdown immediate
SQL> startup
SQL> alter database recover managed standby database using current logfile disconnect from session;
