利用DG 闪回数据库特性 - 模拟灾难演练

原创心在梦在 2022-12-27

994

一、背景

客户想利用停线机会，进行灾难演练，模拟正式库出问题后，将应用切换至DG环境，验证DG架构。

环境：11.2.0.4 单机 + DG （单机）

二、演练思路

将正式环境关闭，模拟主库不可用，将DG激活成主库给应用使用。但是如果直接将DG Failover成主库，会破坏原有的DG架构，需要重新搭建DG，这是我们不愿意的，通过DG闪回数据库特性，可以解决该问题。
将DG Failover成主库测试完成后，重新将DG闪回到激活主库之前的状态，重新同步应用日志。

三、灾难演练步骤

1. 开启DG闪回数据库

--1) 设置闪回区
SQL> alter system set db_recovery_file_dest='/oradata1/fast_recovery_area' scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest_size=100G scope=spfile;

System altered.

SQL> alter database recover managed standby database cancel; 

Database altered.

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area 1.2527E+10 bytes
Fixed Size                  2264856 bytes
Variable Size            2348810472 bytes
Database Buffers         1.0167E+10 bytes
Redo Buffers                8658944 bytes
Database mounted.
Database opened.

--2) 开启闪回数据库
SQL> alter database flashback on;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


--3) 检查闪回数据库是否已开启
SQL> select open_mode,flashback_on from v$database;

OPEN_MODE            FLASHBACK_ON
-------------------- ------------------
READ ONLY WITH APPLY YES

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata1/fast_recovery_area
db_recovery_file_dest_size           big integer 100G

SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

2. 创建保证还原点

创建保证还原点，确保我们测试完成后，可以还原到该还原点。

--1) 检查DG同步情况
SQL> set line222
SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CLOSING               1     347109
RFS       IDLE                  0          0
RFS       IDLE                  1     347110
RFS       IDLE                  0          0
MRP0      APPLYING_LOG          1     347110

8 rows selected.


--2) 创建保证还原点
SQL> create restore point before_application_test guarantee flashback database;
create restore point before_application_test guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_APPLICATION_TEST'.
ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database cancel; 

Database altered.

SQL> create restore point before_application_test guarantee flashback database;

Restore point created.

SQL> set numwidth 20
SQL> set line222
SQL> col name for a30 
SQL> col time for a40
SQL> select scn,name,guarantee_flashback_database,time from v$restore_point; 

                 SCN NAME                           GUA TIME
-------------------- ------------------------------ --- ----------------------------------------
       6474039336229 BEFORE_APPLICATION_TEST        YES 26-DEC-22 01.02.16.000000000 PM

3. 关闭主库，模拟主库宕机

--1) 关闭监听
[oracle@MESDB backup]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-DEC-2022 13:04:47
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MESDB)(PORT=1521)))
The command completed successfully

--2) 关闭数据库实例
[oracle@MESDB backup]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 26 13:04:51 2022
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

4. 激活DG为主库

SQL> alter database recover managed standby database finish force;

Database altered.

SQL> select name,open_mode,database_role,switchover_status from v$database;

NAME                           OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
mesdb                         READ ONLY            PHYSICAL STANDBY TO PRIMARY

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,open_mode,database_role,switchover_status from v$database;

NAME                           OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
mesdb                         READ WRITE           PRIMARY          RESOLVABLE GAP

5. 应用测试

此时，已经将DG激活成主库，应用可以通过修改tns 或者新的主库修改ip、service_name等方式，连接新的主库进行应用测试。

--1) 这里我们选择增加一个service_name，应用修改tns ip地址即可
SQL> alter system set service_names='mesdg','mesdb';

System altered.

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@MESDG ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-DEC-2022 13:07:59
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MESDG)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                31-OCT-2022 21:25:54
Uptime                    55 days 15 hr. 42 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/MESDG/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MESDG)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "mesdb" has 1 instance(s).
  Instance "mesdg", status READY, has 1 handler(s) for this service...
Service "mesdbXDB" has 1 instance(s).
  Instance "mesdg", status READY, has 1 handler(s) for this service...
Service "mesdg" has 2 instance(s).
  Instance "mesdg", status UNKNOWN, has 1 handler(s) for this service...
  Instance "mesdg", status READY, has 1 handler(s) for this service...
The command completed successfully

6. 闪回至DG模式

应用测试完成后，将数据库重新闪回至DG模式。

--1) 删除多余的service name 
SQL> alter system set service_names='mesdg';

System altered.

--2) 闪回到保证还原点
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup mount; 
ORACLE instance started.

Total System Global Area 1.2527E+10 bytes
Fixed Size                  2264856 bytes
Variable Size            2348810472 bytes
Database Buffers         1.0167E+10 bytes
Redo Buffers                8658944 bytes
Database mounted.
SQL> 
SQL> flashback database to restore point before_application_test;

Flashback complete.

--3）注意：虽然，此时我们已经成功将将数据库闪回到了测试前状态，但是数据库角色仍然是primary database，需要进行角色转化。
SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.2527E+10 bytes
Fixed Size                  2264856 bytes
Variable Size            2348810472 bytes
Database Buffers         1.0167E+10 bytes
Redo Buffers                8658944 bytes

SQL> alter database mount ;

Database altered.

SQL> alter database convert to physical standby;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.

--3) 删除保证还原点，否则会导在闪回区不能自动删除，DG同步受到影响
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.2527E+10 bytes
Fixed Size                  2264856 bytes
Variable Size            2348810472 bytes
Database Buffers         1.0167E+10 bytes
Redo Buffers                8658944 bytes

SQL> alter database mount ;

Database altered.

SQL> drop restore point before_application_test;

Restore point dropped.

SQL> alter database open;

Database altered.

--4）启动MRP日志应用进程
SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

--4）查看数据库状态，角色为PHYSICAL STANDBY
SQL> select name,open_mode,database_role,switchover_status from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
mesdb    READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

7. 开启主库

--1) 启动监听
[oracle@MESDB backup]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-DEC-2022 07:27:16
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/mesdb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MESDB)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-DEC-2022 15:03:16
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/mesdb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MESDB)(PORT=1521)))
The listener supports no services
The command completed successfully

--2) 启动数据库实例
[oracle@MESDB backup]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 26 15:04:51 2022
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORACLE instance started.

Total System Global Area 1.2527E+10 bytes
Fixed Size                  2264856 bytes
Variable Size            2348810472 bytes
Database Buffers         1.0167E+10 bytes
Redo Buffers                8658944 bytes
Database mounted.
Database opened.

8. 检查DG同步情况

-- 检查DG同步情况
SQL> set line222
SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  1     347117
MRP0      APPLYING_LOG          1     347114

9 rows selected.

9. 关闭DG闪回功能

这里，我们没有关闭DG 闪回功能，如果空间够用，建议DG开启闪回数据库，这样的话，如果主库出现误删除数据，且undo也没办法进行闪回查询，此时我们就可以利用DG闪回数据库特性，将DG闪回到误删除之前，查找到被误删除的数据，之后再已同样的方式恢复DG同步架构，要比我们通过备份恢复快很多。

如果需要关闭，可以通过脚本进行关闭：

SQL> alter database flashback off;

墨力计划 dg 闪回数据库故障演练

最后修改时间：2023-01-02 22:02:19

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

利用DG 闪回数据库特性 - 模拟灾难演练

一、背景

二、演练思路

三、灾难演练步骤

1. 开启DG闪回数据库

2. 创建保证还原点

3. 关闭主库，模拟主库宕机

4. 激活DG为主库

5. 应用测试

6. 闪回至DG模式

7. 开启主库

8. 检查DG同步情况

9. 关闭DG闪回功能

评论