Oracle Active Dataguard Options

Oracle Database options, the Oracle Database offerings for which each option is available, and the features that are included with each option.
You must be licensed for an option in order to use any of its features.



If a license for the Oracle Active Data Guard option has been purchased, Redo Apply can be active while the physical standby database is open,
thus allowing queries to return results that are identical to what would be returned from the primary database.
This capability is known as the real-time query feature.
If a license for the Oracle Active Data Guard option has not been purchased, a physical standby database cannot be open while Redo Apply is active,
so the following rules must be observed when opening a physical standby database instance or starting Redo Apply:

Redo Apply must be stopped before any physical standby database instance is opened.
If one or more physical standby instances are open, those instances must be stopped or restarted in a mounted state before starting Redo Apply.

Real-time Query

The COMPATIBLE database initialization parameter must be set to 11.0 or higher to use the real-time query feature of the Oracle Active Data Guard option.

A physical standby database instance cannot be opened if Redo Apply is active on a mounted instance of that database.
Use the following SQL statements to stop Redo Apply, open a standby instance read-only, and restart Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Demonstration
SQL> @name

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ ONLY	       PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET	  READ ONLY	       PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> @name

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET	  READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE

Check same thing from Primary as Below 
SQL> col DEST_ID for 9
col dest_name for a30
select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2;
SQL> SQL> 
DEST_ID DEST_NAME		       STATUS	 TYPE		  SRL RECOVERY_MODE
------- ------------------------------ --------- ---------------- --- ----------------------------------
      2 LOG_ARCHIVE_DEST_2	       VALID	 PHYSICAL	  YES MANAGED REAL TIME APPLY WITH QUERY

AUTOMATIC BLOCK MEDIA RECOVERY.

Automatic Block Media Reovery in ACTIVE DATAGUARD

Automatic Block Repair

Automatic block repair allows corrupt data blocks to be automatically repaired as soon as the corruption is detected.

Automatic block repair requires the use of the Oracle Active Data Guard option so that you can open a physical standby database for read-write I/O.
Also, note that this feature requires that Oracle Data Guard is running in maximum availability mode and has the LOG_ARCHIVE_DEST_n initialization parameter set to the SYNC redo transport mode.

If a corrupt data block is encountered when a primary database is accessed, it is automatically replaced with an uncorrupted copy of that block from a physical standby database.

This requires the following conditions:
=>The physical standby database must be operating in real-time query mode, which requires an Active Data Guard license.
=>The physical standby database must be running real-time apply.

Also keep the following in mind:

    Automatic repair is supported with any Data Guard protection mode. However, the effectiveness of repairing a corrupt block at

  the primary using the noncorrupt version of the block from the standby depends on how closely the standby apply is synchronized with the redo generated by the primary.
    When an automatic block repair has been performed, a message is written to the database alert log.
    If automatic block repair is not possible, an ORA-1578 error is returned

ON PRIMARY

SQL> alter system set log_archive_Dest_2=’service=DEMOST SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DEMOS’;
System altered.

SQL> ALTER SYSTEM set log_archive_Dest_STATE_2=ENABLE;
System altered.

SQL> select name,database_role,protection_mode from v$database;

NAME DATABASE_ROLE PROTECTION_MODE
——— —————- ——————–
DEMOP PRIMARY MAXIMUM AVAILABILITY

SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE

6 rows selected.

SQL> create tablespace DEMO_CORRUPT datafile ‘/u01/app/oradata/DEMOP/demo_c_01.dbf’ size 500m;
Tablespace created.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
————–
85
SQL> create table t (int int) tablespace DEMO_CORRUPT;
Table created.

SQL> insert into t values (1);
1 row created.
..
..

SQL> commit;
Commit complete.

SQL> select count(*) from t;
COUNT(*)
———-
12

SQL> select min(dbms_rowid.rowid_block_number(rowid))from t;
MIN(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
—————————————–
132

SQL> select name from v$datafile;
NAME
——————————————————————————–
/u01/app/oradata/DEMOP/system01.dbf
/u01/app/oradata/DEMOP/sysaux01.dbf
/u01/app/oradata/DEMOP/undotbs01.dbf
/u01/app/oradata/DEMOP/users01.dbf
/u01/app/oradata/DEMOP/example01.dbf
/u01/app/oradata/DEMOP/demo_c_01.dbf

6 rows selected.

SQL> exit

[oracle@DGPRIM BACKUP]$ su –
Password:
[root@DGPRIM ~]#
[root@DGPRIM ~]# dd if=/dev/zero of=/u01/app/oradata/DEMOP/demo_c_01.dbf bs=8192 conv=notrunc seek=134 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 3.9642e-05 s, 207 MB/s
[root@DGPRIM ~]#
[root@DGPRIM ~]# logout

[oracle@DGPRIM BACKUP]$ dbv file=/u01/app/oradata/DEMOP/demo_c_01.dbf

DBVERIFY – Verification starting : FILE = /u01/app/oradata/DEMOP/demo_c_01.dbf
Page 134 is marked corrupt
Corrupt block relative dba: 0x01800086 (file 6, block 134)
Completely zero block found during dbv:

DBVERIFY – Verification complete

Total Pages Examined : 64000
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 127
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 63872
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1160745 (0.1160745)

[oracle@DGPRIM BACKUP]$ sqlplus “/as sysdba”

SQL> alter system flush buffer_cache;
System altered.

SQL>analyze table t validate structure cascade;

It takes much time to analyze .

SQL> select count(*) from t;
COUNT(*)
———-
12

SQL> select * from v$recover_file;
no rows selected

SQL> exit

ON STANDBY

SQL> select name,database_role,protection_mode from v$database;

NAME DATABASE_ROLE PROTECTION_MODE
——— —————- ——————–
DEMOP PHYSICAL STANDBY MAXIMUM AVAILABILITY

SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE

6 rows selected.

SQL> show parameter standby

NAME TYPE VALUE
———————————— ———– ——————————
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
——————–
MOUNTED

SQL> alter database open;
Database altered.

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

SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
DEMO_CORRUPT
7 rows selected.

SQL> select max(sequence#) from v$archived_log where applied=’YES’;
MAX(SEQUENCE#)
————–
84

SQL> select count(*) from t;
COUNT(*)
———-
12

ALERT LOG OF PRIMARY DATABASE

Sun May 27 09:57:09 2018
ALTER SYSTEM: Flushing buffer cache
Hex dump of (file 6, block 134) in trace file /u01/app/oracle/diag/rdbms/demop/DEMOP/trace/DEMOP_ora_3275.trc
Corrupt block relative dba: 0x01800086 (file 6, block 134)
Completely zero block found during multiblock buffer read
Reading datafile ‘/u01/app/oradata/DEMOP/demo_c_01.dbf’ for corruption at rdba: 0x01800086 (file 6, block 134)
Reread (file 6, block 134) found same corrupt data (no logical check)
Starting background process ABMR
Sun May 27 09:57:19 2018
ABMR started with pid=30, OS id=3277
Automatic block media recovery service is active.
Automatic block media recovery requested for (file# 6, block# 134)
Sun May 27 09:58:07 2018
ALTER SYSTEM SET log_archive_dest_state_2=’ENABLE’ SCOPE=BOTH;
Sun May 27 09:58:07 2018
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
******************************************************************
LGWR: Setting ‘active’ archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Standby redo logfile selected to archive thread 1 sequence 91
LGWR: Standby redo logfile selected for thread 1 sequence 91 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 91 (LGWR switch)
Current log# 1 seq# 91 mem# 0: /u01/app/oradata/DEMOP/redo01.log
Sun May 27 09:58:07 2018
Archived Log entry 148 added for thread 1 sequence 90 ID 0xe849771f dest 1:
Sun May 27 09:58:07 2018
ARC3: Standby redo logfile selected for thread 1 sequence 90 for destination LOG_ARCHIVE_DEST_2
Sun May 27 09:58:08 2018
Automatic block media recovery successful for (file# 6, block# 134)
Sun May 27 09:58:08 2018
Automatic block media recovery successful for (file# 6, block# 134)

Active Data Guard DML Redirection

You can run DML operations on Active Data Guard standby databases. This enables you to run read-mostly applications, which occasionally execute DMLs, on the standby database.

DML operations on a standby can be transparently redirected to and run on the primary database.
This includes DML statements that are part of PL/SQL blocks.
The Active Data Guard session waits until the corresponding changes are shipped to and applied to the Active Data Guard standby.
Read consistency is maintained during the DML operation and the standby database on which the DML is run can view its uncommitted changes.
However, all the other standby database instances can view these changes only after the transaction is committed.

Note:

Avoid running too may DML operations on Active Data Guard standby databases.

Automatic redirection of DML operations to the primary can be configured at the system level or the session level.

To configure automatic redirection of DML operations for all standby sessions in an Active Data Guard environment:

    Set the ADG_REDIRECT_DML initialization parameter to TRUE.

To configure automatic redirection of DML operations for the current session, use the following command:

    ALTER SESSION ENABLE ADG_REDIRECT_DML;

Demonstration

On Primary Side

[oracle@racnoden1 ~]$
[oracle@racnoden1 ~]$ sqlplus "/as sysdba"

SQL> @name

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET      READ WRITE           PRIMARY        MAXIMUM PERFORMANCE
DBSET      READ WRITE           PRIMARY        MAXIMUM PERFORMANCE

SQL> create user ABHI_TEST identified by Oracle_4U;

User created.

SQL> grant connect,resource to ABHI_tEST;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO ABHI_TEST;

Grant succeeded.

SQL> show parameter ADG_REDIRECT_DML

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
adg_redirect_dml             boolean     FALSE
SQL> EXIT
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

[oracle@racnoden1 ~]$ sqlplus ABHI_TEST/Oracle_4U

SQL> create table DML_ON_STDBY (id int, from_d varchar2(20));

Table created.

SQL> insert into DML_ON_STDBY values (1,'from Prim');
1 row created.

SQL> commit;

Commit complete.

SQL> select * from DML_ON_STDBY;

    ID FROM_D
---------- --------------------
     1 from Prim
SQL> exit

On Standby Side

[oracle@racsetn1 trace]$ sqlplus  ABHI_TEST@TNSTAR

SQL> insert into DML_ON_STDBY values (2,'from STDBY');
insert into DML_ON_STDBY values (2,'from STDBY')
            *
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

[oracle@racsetn1 trace]$ sqlplus  ABHI_TEST@TNSTAR
SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;

Session altered.

SQL> insert into DML_ON_STDBY values (2,'from STDBY');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Check on Primary If Data is Avaialble Now.

[oracle@racnoden1 ~]$ sqlplus ABHI_TEST/Oracle_4U

SQL> select * from DML_ON_STDBY;

    ID FROM_D
---------- --------------------
     1 from Prim
     1 from Prim
     2 from STDBY
     2 from STDBY

Automatic Recompliation of Modified PL/SQL Objects

PL/SQL objects that are run on standby instances can be recompiled, if they are invalid.

PL/SQL objects become invalid when their dependent objects are modified or dropped. Starting with Oracle Database Release 19c,
invalidated PL/SQL objects run on a standby database can be automatically recomplied by setting the ADG_REDIRECT_DML initialization parameter to TRUE.
The DDL corresponding to these PL/SQL objects is redirected to and executed on the primary database. The standby session waits until the operation is completed.

Automatically Recompiling Modified PL/SQL Objects

On the primary database in an Active DataGuard environment, a procedure named insert_empl is created. This procedure is used to update the employees table.

CREATE OR REPLACE PROCEDURE update_sal (emp_id IN NUMBER,sal IN NUMBER)
AS BEGIN
   UPDATE employees SET salary=sal WHERE employee_id=emp_id);
END;

The structure of the employees table is subsequently modified using an ALTER TABLE command. This invalidates the update_sal procedure.

On a standby database in the Active DataGuard environment, you want to use the update_sal procedure to update the salary of an employee.
Run the following commands on the physical standby:

SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;
SQL> exec update_sal(105,6000);

The user running these commands has been granted permission to execute the update_sal procedure.