實驗課題：主備切換及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;
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)