Tuesday, September 14, 2021

Oracle 19c Restore Point Replication From Primary Database To Physical Standby Database

Oracle Active Data Guard and Flashback enhancements

1. Today, restore points are set on each Data Guard database individually

    Requires multiple operations if the same restore point across the configuration is desired

2. With Oracle Database 19c, the primary restore points are automatically created on each standby

    Identified by a suffix to the name of “_PRIMARY”


Environment:

Primary RAC: Oracle 19c (19.3) Grid Infrastructure and 19.12 RU
                  Oracle 19c (19.3) RDBMS and 19.12 RU

Physical Standby RAC: Oracle 19c (19.3) Grid Infrastructure and 19.12 RU
                                  Oracle 19c (19.3) RDBMS and 19.12 RU

SQL> select distinct instance_name,database_role,protection_mode,flashback_on,status 
from gv$database,gv$instance;

INSTANCE_N DATABASE_ROLE   PROTECTION_MODE      FLASHBACK_ON         STATUS
---------- --------------- -------------------- -------------------- ----------
orcl2      PRIMARY         MAXIMUM PERFORMANCE  NO                   OPEN
orcl1      PRIMARY         MAXIMUM PERFORMANCE  NO                   OPEN
SQL>

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
[oracle@oel70-base1 ~]$

[oracle@oel70-base1 ~]$ srvctl stop database -d orcl

[oracle@oel70-base1 ~]$ srvctl start database -d orcl -startoption mount

[oracle@oel70-base1 ~]$ . oraenv
ORACLE_SID = [orcl1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel70-base1 ~]$

[oracle@oel70-base1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 15:45:09 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter database flashback on;
Database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
[oracle@oel70-base1 ~]$

[oracle@oel70-base1 ~]$ srvctl stop database -d orcl
[oracle@oel70-base1 ~]$

[oracle@oel70-base1 ~]$ srvctl start database -d orcl
[oracle@oel70-base1 ~]$

[oracle@oel70-base1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 15:50:25 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> set lines 100 pages 100
col status format a10
col instance_name format a15
col database_role format a15
col protection_mode format a20
col flashback_on format a20

SQL> select distinct instance_name,database_role,protection_mode,flashback_on,status 
from gv$database,gv$instance;

INSTANCE_NAME   DATABASE_ROLE   PROTECTION_MODE      FLASHBACK_ON         STATUS
--------------- --------------- -------------------- -------------------- ----------
orcl2           PRIMARY         MAXIMUM PERFORMANCE  YES                  OPEN
orcl1           PRIMARY         MAXIMUM PERFORMANCE  YES                  OPEN

SQL> alter system switch all logfile;
System altered.

SQL> alter system switch all logfile;
System altered.

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

SQL> set lines 100 pages 100
SQL> col SCN format 99999999
SQL> col GUARANTEE_FLASHBACK_DATABASE format a10
SQL> col TIME format a20
SQL> col NAME format a10
SQL> col REPLICATED format a10
SQL> col TIME format a32

SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED 
from v$restore_point;

      SCN GUARANTEE_ TIME                             NAME       REPLICATED
--------- ---------- -------------------------------- ---------- ----------
  3190291 YES        14-SEP-21 04.07.09.000000000 PM  GRP_TEST   NO

Physical Standby Database:
-----------------------------------

SQL> select distinct instance_name,database_role,protection_mode,flashback_on,status 
from gv$database,gv$instance;

INSTANCE_NAME   DATABASE_ROLE        PROTECTION_MODE      FLASHBACK_ON    STATUS
--------------- -------------------- -------------------- --------------- ----------
orcls2          PHYSICAL STANDBY     MAXIMUM PERFORMANCE  NO              OPEN
orcls1          PHYSICAL STANDBY     MAXIMUM PERFORMANCE  NO              OPEN

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
[oracle@oel70-base3 ~]$

[oracle@oel70-base3 ~]$ srvctl stop database -d orcls

[oracle@oel70-base3 ~]$ srvctl start database -d orcls -startoption mount

[oracle@oel70-base3 ~]$ . oraenv
ORACLE_SID = [orcls1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel70-base3 ~]$

[oracle@oel70-base3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 15:58:11 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
[oracle@oel70-base3 ~]$

[oracle@oel70-base3 ~]$ srvctl stop database -d orcls

[oracle@oel70-base3 ~]$ srvctl start database -d orcls
[oracle@oel70-base3 ~]$

[oracle@oel70-base3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 16:02:02 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> set lines 100 pages 100
col status format a10
col instance_name format a15
col database_role format a15
col protection_mode format a20
col flashback_on format a20

SQL> select distinct instance_name,database_role,protection_mode,flashback_on,status 
from gv$database,gv$instance;

INSTANCE_NAME   DATABASE_ROLE        PROTECTION_MODE      FLASHBACK_ON         STATUS
--------------- -------------------- -------------------- -------------------- ----------
orcls1          PHYSICAL STANDBY     MAXIMUM PERFORMANCE  YES                  OPEN
orcls2          PHYSICAL STANDBY     MAXIMUM PERFORMANCE  YES                  OPEN

SQL> set lines 200
col DATABASE_HOST for a25;
col DB_UNIQUE_NAME for a25
col HOST_NAME for a15;
col DATABASE_ROLE for a10
col OPEN_MODE for a10
col STARTUP_TIME for a20

SQL> SELECT i.HOST_NAME "DATABASE_HOST" ,i.INSTANCE_NAME "DB_NAME",d.db_unique_name "DB_UNIQUE_NAME", 
d.DATABASE_ROLE " DATABASE_ROLE", d.OPEN_MODE " OPEN_MODE ", STARTUP_TIME
from GV$DATABASE d, gv$instance i where i.INST_ID=d.INST_ID;
select inst_id,process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process='MRP0';
select a.thread#, (select max (sequence#) 
                            from v$archived_log 
                            where archived='YES' and thread#=a.thread#) archived,max(a.sequence#) applied,
                          (select max(sequence#) 
                           from v$archived_log 
                   where archived='YES' and thread#=a.thread#)-max(a.sequence#)gap
from v$archived_log a 
where a.applied='YES' 
group by a.thread# 
order by thread# 

DATABASE_HOST             DB_NAME          DB_UNIQUE_NAME             DATABASE_ROLE    OPEN_MODE           STARTUP_TIME
------------------------- ---------------- ------------------------- ---------------- -------------------- -------------------
oel70-base3               orcls1           orcls                     PHYSICAL STANDBY READ ONLY WITH APPLY 14-SEP-21
oel70-base4               orcls2           orcls                     PHYSICAL STANDBY READ ONLY WITH APPLY 14-SEP-21

SQL>
   INST_ID PROCESS   STATUS        THREAD#  SEQUENCE#     BLOCK#     BLOCKS
---------- --------- ---------- ---------- ---------- ---------- ----------
         1 MRP0      APPLYING_L          1         41       7668     409600
                     OG

   THREAD#   ARCHIVED    APPLIED        GAP
---------- ---------- ---------- ----------
         1         40         40          0
         2         30         30          0
SQL>

SQL> set lines 100 pages 100
col SCN format 99999999
col GUARANTEE_FLASHBACK_DATABASE format a10
col TIME format a33
col NAME format a10
col REPLICATED format a10
SQL> col name format a20

SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED 
from v$restore_point;

      SCN GUARANTEE_ TIME                              NAME                 REPLICATED
--------- ---------- --------------------------------- -------------------- ----------
  3190291 NO         14-SEP-21 04.07.09.000000000 PM   GRP_TEST_PRIMARY     YES
SQL>


DGMGRL> show configuration lag;
Configuration - dg_19c
  Protection Mode: MaxPerformance
  Members:
  orcl  - Primary database
    orcls - Physical standby database
            Transport Lag:      0 seconds (computed 0 seconds ago)
            Apply Lag:          0 seconds (computed 1 second ago)
Fast-Start Failover:  Disabled
Configuration Status:
SUCCESS   (status updated 31 seconds ago)
DGMGRL>

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)