根据线上数据库的使用类型，Oracle DG测试环境switchover、failover故障自动切换；mysql 基于binlog闪回演练（my2sqlGO）;xtrabackup（PXB）备份还原；
Oracle DG switchover演练
1.查看主备同步情况
SYS@LHR11G> alter system switch logfile;
System altered.
SYS@LHR11G> alter system switch logfile;
System altered.
SYS@LHR11G> alter system switch logfile;
SYS@LHR11G> @dg_info
THREAD# DEST_ID DEST_NAME TARGET DATABASE_MODE STATUS ERROR RECOVERY_MODE DB_UNIQUE_NAME DESTINATION GAP_STATUS CURRENT_SEQ# LAST_ARCHIVED APPLIED_SEQ# APPLIED_SCN
---------- ---------- -------------------- -------------------------------------------- ------------------------------ ------------------ ---------- ---------------------------------------------- --------------- --------------- ---------- ------------ ------------- ------------ ----------------
1 1 LOG_ARCHIVE_DEST_1 LOCAL PRIMARY OPEN VALID IDLE LHR11G 19 18 0
1 2 LOG_ARCHIVE_DEST_2 PHYSICAL STANDBY OPEN_READ-ONLY VALID MANAGED REAL TIME APPLY LHR11GDG lhr11gdg NO GAP 19 18 17 1488372
SYS@LHR11G> @dg_status
THREAD# NAME SEQUENCE# APPLIED FIRST_TIME
---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ -------------------
1 lhr11gdg 6 YES 2020-10-27 17:21:52
1 lhr11gdg 14 YES 2021-03-17 13:49:59
1 lhr11gdg 15 YES 2021-03-17 13:50:11
1 lhr11gdg 16 YES 2021-03-17 13:50:50
1 lhr11gdg 17 YES 2021-03-17 13:53:12
1 lhr11gdg 18 NO 2021-03-17 13:53:18
1 lhr11gdg 23 YES 2020-10-27 17:17:38
1 lhr11gdg 24 YES 2020-10-27 17:18:23
1 lhr11gdg 25 YES 2020-10-27 17:18:26
9 rows selected.
SYS@LHR11G> create table lhr.testdg as select * from scott.emp;
Table created.
SYS@LHR11G> select count(*) from lhr.testdg;
COUNT(*)
----------
14
备库查看同步信息
SYS@LHR11GDG> @dg_status
THREAD# NAME SEQUENCE# APPLIED FIRST_TIME
---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ -------------------
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_2_hshsjt0w_.arc 2 YES 2020-10-27 17:19:16
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_3_hshsmx1b_.arc 3 YES 2020-10-27 17:19:22
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_4_hshso41g_.arc 4 YES 2020-10-27 17:21:01
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_5_hshsoj22_.arc 5 YES 2020-10-27 17:21:40
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_6_hshszkyt_.arc 6 YES 2020-10-27 17:21:52
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2021_03_17/o1_mf_1_11_j5365r1m_.arc 11 YES 2021-03-17 06:58:36
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2021_03_17/o1_mf_1_12_j5365r3c_.arc 12 YES 2021-03-17 13:45:47
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2021_03_17/o1_mf_1_13_j5365r7b_.arc 13 YES 2021-03-17 13:45:50
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2021_03_17/o1_mf_1_14_j5365r7l_.arc 14 YES 2021-03-17 13:49:59
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2021_03_17/o1_mf_1_15_j5365ttz_.arc 15 YES 2021-03-17 13:50:11
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2021_03_17/o1_mf_1_16_j536b8vt_.arc 16 YES 2021-03-17 13:50:50
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2021_03_17/o1_mf_1_17_j536bgsr_.arc 17 YES 2021-03-17 13:53:12
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2021_03_17/o1_mf_1_18_j536bnt3_.arc 18 IN-MEMORY 2021-03-17 13:53:18
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_23_hshsgzxr_.arc 23 YES 2020-10-27 17:17:38
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_24_hshsh294_.arc 24 YES 2020-10-27 17:18:23
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_25_hshsh5gn_.arc 25 YES 2020-10-27 17:18:26
1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_hshsjq3x_.arc 26 YES 2020-10-27 17:18:29
1 29 YES 2020-10-27 16:56:46
1 30 YES 2020-10-27 16:56:53
1 31 YES 2020-10-27 16:56:55
1 32 YES 2020-10-27 16:56:57
1 33 YES 2020-10-27 16:57:06
1 34 YES 2020-10-27 16:57:24
1 35 YES 2020-10-27 16:58:15
24 rows selected.
SYS@LHR11GDG> select count(*) from lhr.testdg;
COUNT(*)
----------
14
2.接下来使用dgmgrl来验证switchover功能。
[oracle@lhr11g ~]$ dgmgrl sys/lhr@lhr11g
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - LHR11G
Protection Mode: MaxPerformance
Databases:
LHR11G - Primary database
LHR11GDG - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
#做切换
DGMGRL> switchover to 'LHR11GDG'
Performing switchover NOW, please wait...
Operation requires a connection to instance "LHR11GDG" on database "LHR11GDG"
Connecting to instance "LHR11GDG"...
Connected.
New primary database "LHR11GDG" is opening...
Operation requires startup of instance "LHR11G" on database "LHR11G"
Starting instance "LHR11G"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "LHR11GDG"
DGMGRL> show configration
show configration
^
Syntax error before or at "configration"
DGMGRL> show configuration
Configuration - LHR11G
Protection Mode: MaxPerformance
Databases:
LHR11GDG - Primary database
LHR11G - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
切换成功后验证一下主备同步情况，此时的主库已经变成LHR11GDG了
SYS@LHR11GDG> @dg_info
THREAD# DEST_ID DEST_NAME TARGET DATABASE_MODE STATUS ERROR RECOVERY_MODE DB_UNIQUE_NAME DESTINATION GAP_STATUS CURRENT_SEQ# LAST_ARCHIVED APPLIED_SEQ# APPLIED_SCN
---------- ---------- -------------------- -------------------------------------------- ------------------------------ ------------------ ---------- ---------------------------------------------- --------------- --------------- ---------- ------------ ------------- ------------ ----------------
1 1 LOG_ARCHIVE_DEST_1 LOCAL PRIMARY OPEN VALID IDLE LHR11GDG 25 24 0
1 2 LOG_ARCHIVE_DEST_2 PHYSICAL STANDBY OPEN_READ-ONLY VALID MANAGED REAL TIME APPLY LHR11G lhr11g NO GAP 25 24 23 1510118
SYS@LHR11GDG> @dg_status
THREAD# NAME SEQUENCE# APPLIED FIRST_TIME
---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ -------------------
1 lhr11g 3 YES 2020-10-27 17:19:22
1 lhr11g 4 YES 2020-10-27 17:21:01
1 lhr11g 5 YES 2020-10-27 17:21:40
1 lhr11g 20 YES 2021-03-17 14:28:37
1 lhr11g 21 YES 2021-03-17 14:28:42
1 lhr11g 22 YES 2021-03-17 14:28:43
1 lhr11g 23 YES 2021-03-17 14:28:46
1 lhr11g 24 NO 2021-03-17 14:28:55
1 lhr11g 26 YES 2020-10-27 17:18:29
1 LHR11G 28 YES 2020-10-27 16:56:00
10 rows selected.
SYS@LHR11GDG> insert into lhr.testdg select * from lhr.testdg;
14 rows created.
SYS@LHR11GDG> commit;
Commit complete.
此时查看备库有无新增数据
SYS@LHR11G> select count(*) from lhr.testdg;
COUNT(*)
----------
28
SYS@LHR11G> @dg_status
THREAD# NAME SEQUENCE# APPLIED FIRST_TIME
---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ -------------------
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_3_hshso1qq_.arc 3 YES 2020-10-27 17:19:22
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_4_hshso436_.arc 4 YES 2020-10-27 17:21:01
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_5_hshsoj58_.arc 5 YES 2020-10-27 17:21:40
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_6_hshstwmw_.arc 6 YES 2020-10-27 17:21:52
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2021_03_17/o1_mf_1_20_j538f2yx_.arc 20 YES 2021-03-17 14:28:37
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2021_03_17/o1_mf_1_21_j538f2yc_.arc 21 YES 2021-03-17 14:28:42
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_22_hshsfldb_.arc 22 YES 2020-10-27 17:17:20
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2021_03_17/o1_mf_1_22_j538f329_.arc 22 YES 2021-03-17 14:28:43
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2021_03_17/o1_mf_1_23_j538f80d_.arc 23 YES 2021-03-17 14:28:46
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_23_hshsgzsz_.arc 23 YES 2020-10-27 17:17:38
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2021_03_17/o1_mf_1_24_j538fqc9_.arc 24 IN-MEMORY 2021-03-17 14:28:55
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_24_hshsh25x_.arc 24 YES 2020-10-27 17:18:23
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_25_hshsh5bz_.arc 25 YES 2020-10-27 17:18:26
1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_26_hshso1o6_.arc 26 YES 2020-10-27 17:18:29
14 rows selected.
可以看到备库同步正常。
3.接下来验证switchover主库挂了切换到备库承担业务功能，这时当然从备库failover了
[oracle@lhr11g ~]$ dgmgrl sys/lhr@lhr11g
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - LHR11G
Protection Mode: MaxPerformance
Databases:
LHR11GDG - Primary database
LHR11G - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> failover to 'LHR11G'
Performing failover NOW, please wait...
Failover succeeded, new primary is "LHR11G"
DGMGRL> show configuration
Configuration - LHR11G
Protection Mode: MaxPerformance
Databases:
LHR11G - Primary database
LHR11GDG - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show configuration
Configuration - LHR11G
Protection Mode: MaxPerformance
Databases:
LHR11G - Primary database
LHR11GDG - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
切换成功后，需要重新reinstate备库，步骤如下，1.备库强制重启到mount状态，2.然后用DGMGRL连接到主库上执行 REINSTATE DATABASE 'LHR11GDG'
#备库上执行
SYS@LHR11GDG> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@LHR11GDG> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 2437
Session ID: 138 Serial number: 15
SYS@LHR11GDG> startup mount
ORACLE instance started.
Total System Global Area 346562560 bytes
Fixed Size 2253144 bytes
Variable Size 192941736 bytes
Database Buffers 146800640 bytes
Redo Buffers 4567040 bytes
Database mounted.
SYS@LHR11GDG> exit
主库上用DGMGRL连接到主库上执行 REINSTATE DATABASE 'LHR11GDG'
DGMGRL> show configuration
Configuration - LHR11G
Protection Mode: MaxPerformance
Databases:
LHR11G - Primary database
LHR11GDG - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> REINSTATE DATABASE 'LHR11GDG'
Reinstating database "LHR11GDG", please wait...
Operation requires shutdown of instance "LHR11GDG" on database "LHR11GDG"
Shutting down instance "LHR11GDG"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "LHR11GDG" on database "LHR11GDG"
Starting instance "LHR11GDG"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "LHR11GDG" ...
Reinstatement of database "LHR11GDG" succeeded
DGMGRL> show configuration
Configuration - LHR11G
Protection Mode: MaxPerformance
Databases:
LHR11G - Primary database
LHR11GDG - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
