Oracle 19c新特性-RESTORE POINTS FROM PRIMARY TO STANDBY和AUTOMATIC FLASHBACK OF...

Oracle 19c新特性-RESTORE POINTS FROM PRIMARY TO STANDBY和AUTOMATIC FLASHBACK OF STANDBY DATABASE

版权声明：本文为Buddy Yuan原创文章，转载请注明出处。原文地址：Oracle 19c新特性-RESTORE POINTS FROM PRIMARY TO STANDBY和AUTOMATIC FLASHBACK OF STANDBY DATABASE
在Oracle 19c上的data guard，可以帮助我们自动复制主库上的还原点到备库上。同时还可以帮助我们在主库直接flashback database，open resetlogs之后，备库也能应用应用。这简化了RESETLOGS在主服务器上进行操作后的备用服务器管理。
在使用还原点这个新特性需要注意以下几点限制：
1.主数据库和备用数据库上的兼容参数都必须为19.0.0或更大
2.由于通过重做日志进行了还原点复制，因此主数据库必须是OPEN状态下的。而备用数据库上的MRP进程需要运行才能进行复制。
3.备用数据库上不应有任何同名的还原点。

我们来首先测试一下这些功能，首先在主库创建一个还原点test_respoint。

[oracle@primary ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 10 14:49:46 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name,database_role from v$database;
NAME					 DATABASE_ROLE
---------------------------------------- ----------------
DB193H1 				 PRIMARY

SQL> create restore point test_respoint guarantee flashback database;
Restore point created.

SQL> select scn,name,time,replicated from v$restore_point;

       SCN NAME 			  TIME					   REP
---------- ------------------------------ ---------------------------------------- ---
   3379448 TEST_RESPOINT		  11-JUN-20 08.24.55.000000000 AM	   NO

在查看备库，可以发现test_respoint已经被复制过来。在v$restore_point视图中有个字段叫REPLICATED，主库是NO，备库显示YES。而且备库的还原点名字自动加上了“_PRIMARY”后缀。

SQL> set linesize 175 pagesize 1000
SQL> col name format a40
SQL> col time format a40

SQL> select name,database_role from v$database;
NAME					 DATABASE_ROLE
---------------------------------------- ----------------
DB193H1 				 PHYSICAL STANDBY

SQL> select scn,name,time,replicated from v$restore_point;

       SCN NAME 				    TIME				     REP
---------- ---------------------------------------- ---------------------------------------- ---
   3379448 TEST_RESPOINT_PRIMARY		    11-JUN-20 08.24.55.000000000 AM	     YES

接下来我们来试试flashback database的功能。首先我们需要看一下主库和备库的FLASHBACK是否都处于打开状态，如果不是我们可以先打开这个功能。

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
YES

停止我们的主库，执行lashback database to RESTORE POINT操作，open resetlogs一切顺利。

[oracle@primary admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 10 15:20:41 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

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

Total System Global Area 2147481656 bytes
Fixed Size		    8898616 bytes
Variable Size		  486539264 bytes
Database Buffers	 1644167168 bytes
Redo Buffers		    7876608 bytes
Database mounted.

SQL> flashback database to RESTORE POINT test_respoint;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

此时查看备库一直报错ORA-19909。日志也没办法应用。通过查看ora-19909发现基本上都是data guard产生了新的化身（new incarnation），一般需这种错误是主库中的化身和备库不一致导致的，需要要我们到RMAN下面指定和主库一样的化身（incarnation），

2020-06-11T08:33:36.277971+08:00
Errors in file /u01/app/oracle/diag/rdbms/db193h1_stdby/DB193H1/trace/DB193H1_mrp0_7375.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u02/oradata/DB193H1_STDBY/datafile/o1_mf_system_hg2y0646_.dbf'

[oracle@primary ~]$ rman target /
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB193H1  2407161464       PARENT  1          17-APR-19guangfang
2       2       DB193H1  2407161464       PARENT  1920977    03-JUN-20
3       3       DB193H1  2407161464       PARENT  3158799    10-JUN-20
4       4       DB193H1  2407161464       PARENT  3269801    10-JUN-20
5       5       DB193H1  2407161464       CURRENT 3379450    11-JUN-20

[oracle@standby onlinelog]$ rman target /
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB193H1  2407161464       PARENT  1          17-APR-19
2       2       DB193H1  2407161464       PARENT  1920977    03-JUN-20
3       3       DB193H1  2407161464       PARENT  3158799    10-JUN-20
4       4       DB193H1  2407161464       PARENT  3269801    10-JUN-20
5       5       DB193H1  2407161464       CURRENT 3379450    11-JUN-20

而此时怪异就在主库和备库的化身（incarnation）基本是一致的。于是我们又到官方文档上查了一下，发现文档中说“A standby database that is in a mounted state can automatically follow the primary database after a RESETLOGS operation on the primary. ”此时我们的主库不是mount状态。对啊，只有在mount的状态我们才能执行flashback database命令。我真是脑子不好使，于是我把备库停下来再启动到mount状态，神奇的事情发生了。观察日志上面的报错已经不报了，此时会显示Flashback Restore Start和Flashback Media Recovery Start。

2020-06-11T08:33:56.283705+08:00
MRP0 (PID:7375): Recovery coordinator performing automatic flashback of database to SCN:0x00000000003390f8 (3379448)
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
2020-06-11T08:33:56.508797+08:00
Setting recovery target incarnation to 4
2020-06-11T08:33:56.518677+08:00
 Started logmerger process
2020-06-11T08:33:56.556674+08:00
Parallel Media Recovery started with 2 slaves
2020-06-11T08:33:56.666413+08:00
stopping change tracking
2020-06-11T08:33:56.715869+08:00
Media Recovery Log /u01/app/oracle/DB193H1_STDBY/archivelog/2020_06_11/o1_mf_1_13_hg2ythg4_.arc
2020-06-11T08:33:56.875276+08:00
Incomplete Recovery applied until change 3379448 time 06/11/2020 08:24:55
2020-06-11T08:33:56.878279+08:00
Flashback Media Recovery Complete
2020-06-11T08:33:56.933695+08:00
stopping change tracking
2020-06-11T08:33:56.949951+08:00
Setting recovery target incarnation to 5
2020-06-11T08:33:56.964968+08:00
 Started logmerger process
2020-06-11T08:33:56.974818+08:00
PR00 (PID:7410): Managed Standby Recovery starting Real Time Apply
2020-06-11T08:33:57.009092+08:00
Parallel Media Recovery started with 2 slaves
2020-06-11T08:33:57.117122+08:00
Media Recovery start incarnation depth : 1, target inc# : 5, irscn : 3379449
stopping change tracking
2020-06-11T08:33:57.137816+08:00
TT02 (PID:7416): LOG_FILE_NAME_CONVERT is not defined, stop clearing ORLs
2020-06-11T08:33:57.282084+08:00
PR00 (PID:7410): Media Recovery Log /u01/app/oracle/DB193H1_STDBY/archivelog/2020_06_11/o1_mf_1_13_hg2ythg4_.arc
2020-06-11T08:33:57.394111+08:00
PR00 (PID:7410): Media Recovery Log /u01/app/oracle/DB193H1_STDBY/archivelog/2020_06_11/o1_mf_1_1_hg2ytgrw_.arc
2020-06-11T08:33:57.480722+08:00
PR00 (PID:7410): Media Recovery Log /u01/app/oracle/DB193H1_STDBY/archivelog/2020_06_11/o1_mf_1_2_hg2yyrwy_.arc
PR00 (PID:7410): Media Recovery Waiting for T-1.S-3 (in transit)
2020-06-11T08:33:57.678427+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 3 Reading mem 0
  Mem# 0: /u02/oradata/DB193H1_STDBY/onlinelog/o1_mf_5_hg2y142y_.log

成功之后，我在开启了实时应用和read only，此时在查询v$dataguard_stats，apply lag和transport lag都显示正常。

SQL> select * from v$dataguard_stats;
SOURCE_DBID SOURCE_DB_UNIQU NAME		      VALUE		   UNIT 			  TIME_COMPUTED 		 DATUM_TIME		  CON_ID
----------- --------------- ------------------------- -------------------- ------------------------------ ------------------------------ -------------------- ----------
 2407161464 DB193H1	    transport lag	      +00 00:00:00	   day(2) to second(0) interval   06/11/2020 08:41:19		 06/11/2020 08:41:18	       0
 2407161464 DB193H1	    apply lag		      +00 00:00:00	   day(2) to second(0) interval   06/11/2020 08:41:19		 06/11/2020 08:41:18	       0
 2407161464 DB193H1	    apply finish time	      +00 00:00:00.000	   day(2) to second(3) interval   06/11/2020 08:41:19					       0
	  0		    estimated startup time    8 		   second			  06/11/2020 08:41:19					       0

此时在查询DG Broker的状态，也显示正常。

DGMGRL> SHOW CONFIGURATION;

Configuration - db_broker_config

  Protection Mode: MaxPerformance
  Members:
  db193h1       - Primary database
    db193h1_stdby - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 30 seconds ago)

具体的细节可以参考官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/managing-oracle-data-guard-physical-standby-databases.html#GUID-252097AC-3070-43B6-88D8-919AE27F97AD

Oracle 19c新特性-RESTORE POINTS FROM PRIMARY TO STANDBY和AUTOMATIC FLASHBACK OF STANDBY DATABASE

Recommend

关于19c自动索引的名字

记一次19c补丁安装问题

一个节点挂导致另外一个节点也跟着挂的案例一则

lck hang和enq: IV – contention的问题处理

Chrome Headless 模式

Abstract Syntax Tree 抽象语法树简介

前端工程化：构建、部署、灰度

喜鹊开发者（The Magpie Developer）

前端工程化：Docker、k8s、Serverless

前端模块化的发展

About Joyk