利用dg 闪回数据库特性 -m6米乐安卓版下载

原创心在梦在 2022-12-27

927

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

一、背景

客户想利用停线机会，进行灾难演练，模拟正式库出问题后，将应用切换至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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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人已赞赏