ANBOB™

有时需要应用版本上线做一些测试，希望做完数据库操作后利用restore point回滚点或做了基于时间点的恢复后，闪回数据库到修改以前时间点，然后standby继续应用日志恢复DG。因为在flashback后因为需要open resetlogs打开，在有dataguard的环境需要注意,  如果不想重建DG。同时oracle 19c引入了新特性，standby可以自动闪回数据库。

1, 利用standby + failover+ flashback database

# 开启闪回数据库

SQL> alter database flashback on;

# 闪回保留时间

SQL> show parameter db_flashback_retention_target

# 确认闪回时间

SQL> select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

# 确认转换时间

SQL> select STANDBY_BECAME_PRIMARY_SCN from v$database;

# 激活standby

alter database recover managed standby database finish;
alter database commit to switchover to primary with session shutdown;

— do some thing

# 闪加standby 到failover前

SQL> flashback database to scn 【above became primary scn】
SQL> alter database convert to physical standby;
SQL> shutdown immediate;

2, 利用primary +flashback database

# 确认主与备库开启了flashback database
— PRIMARY DB

SQL> select name,database_role,flashback_on from v$database;
NAME DATABASE_ROLE FLASHBACK_ON
--------- ---------------- ------------------
PRIMDB PRIMARY YES

-- STANDBY DB
SQL> select name,database_role,flashback_on from v$database;

NAME DATABASE_ROLE FLASHBACK_ON
--------- ---------------- ------------------
STDBY PHYSICAL STANDBY YES

NAME DATABASE_ROLE FLASHBACK_ON
——— —————- ——————
STDBY PHYSICAL STANDBY YES

# 在primary创建restore point

create restore point BEFORE_TEST GUARANTEE FLASHBACK DATABASE;

— do something

# 关闭primary 重启到mount

SQL> shutdown immediate
SQL> startup mount

# 闪回primary database 到回restore point

SQL> flashback database to restore point BEFORE_TEST;

# 打开primary 数据库 open resetlogs

SQL> alter database open resetlogs;

# 停止standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

# 闪回standby database

SQL> select scn,NAME from v$restore_point; 
 SQL> FLASHBACK STANDBY DATABASE TO nnn;

# standby 继续应用日志

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered

NOTE:
如果遇到下面的错误，需要重建standby controlfile
Sequence nnn does not yet exist in new incarnation, and it has been already applied in the old.

3, 19c new feature auto standby flash back

19c中引入新特性当在Oracle Data Guard配置中,对primary 数据库执行闪回或时间点恢复时对standby数据库也执行相同的操作;在早期版本中，我们必须在主数据库上获得RESETLOGS SCN＃，然后在备用数据库上手动发出FLASHBACK DATABASE命令，以启用托管恢复并继续重做应用过程。Oracle 19c的另一个新功能是，当我们在主数据库上创建一个还原点时，它将在备用数据库上也自动创建一个还原点。这个restore points叫做Replicated Restore Points 并且还原点名字后缀 “_PRIMARY”.

# on primary 
SQL> select flashback_on from v$database;
SQL> create restore point orcl_grp guarantee flashback database;

# on standby 
SQL> select name from v$restore_point;
NAME
--------------------------------------------------------------------------------
ORCL_GRP_PRIMARY
SQL> select NAME,REPLICATED from v$restore_point;

NAME			       REP
------------------------------ ---
ORCL_GRP_PRIMARY	       YES

— do some thing

# on primary
SQL> shutdown immediate
SQL> startup mount;
SQL> flashback database to restore point orcl_grp;
SQL> alter database open resetlogs;

# on standyb
当把数据库关闭启动到mount模式后，会看到mrp自动执行闪回数据库的操作。 当alert 日志中显示“Flashback Media Recovery Complete” 后，就可以open read only standby, 继续应用日志了。

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered

打赏

微信扫一扫，打赏作者吧～