Oracle 19c New Features - Automatic flashback standby database when Primary database is flashed back

Prior to Oracle 19c, when using the "Flashback Database" feature on a primary database in a data guard environment would result in a standby database which is no longer in sync with the primary database; in previous releases, in order to ensure both primary and secondary are synced, then a manual procedure to flash back to standby database was required.

Starting with Oracle 19c, a standby database that is in a mounted state can automatically follow the primary database after a RESETLOGS operation on the primary. This simplifies standby management after a RESETLOGS operation on the primary.

This process is handled by the MRP (Managed Recovery Process) process that will automatically do the work for us.

For more information, see: 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

How to mitigate the performance/protection trade-off in Oracle Data Guard - Part 2 (Axxana)

Introduction

In the previous blog post titled "How to mitigate the performance/protection trade-off in Oracle Data Guard"  I reviewed the common performance vs. protection trade-off between the various data protection modes in Data Guard : Maximum performance (default), maximum availability and maximum protection.

In that blog post I mentioned how far syn can be used in order to achieve both performance (due to the smaller network latency between primary and local far sync instance) while allowing for higher data protection as the far sync instance is always synced with the primary database.

The Challenge
As the far sync instance is located in a near location to the primary database (probably the same data center), what happens if the entire data center is down due to an unexpected site disaster?
In that case there is a good chance that some of the transactions are not protected as there is an asynchronous replication between the far sync instance and the standby instance. In other words, even when using Far sync, it's not possible to guarantee RPO-0 (zero data loss).

Optional Solution
One solution that I came across in several Oracle conferences (such as Oracle OpenWorld and IOUG Collaborate) which you may consider is Axxana
Axxana is like an Oracle Database "black box" which provides a protected storage unit with DR capabilities Installed at the production site, the Black Box is designed to withstand a wide variety of extreme conditions that may occur during a disaster.

According to the official Azxxana website: "Axxana’s Phoenix for Oracle Far Sync, supported by Oracle, is a self-contained, physically indestructible solution that houses, protects, and supports the Oracle Far Sync instance from within the primary (production) data center. Because a nearby data center is not needed, the risk of a nearby synchronous site failing (e.g., due to a power outage or downed communication lines) no longer exists. Zero data loss is guaranteed, increasing the probability of successful failover. In addition, because the instance can reside at the primary site, communication lines are a matter of feet instead of miles and network latency is negligible. Given that it easily and cost-effectively resolves the challenges associated with standalone versions of Far Sync, there is simply no good reason to use the Far Sync software on any other platform—or for that matter, to use three data centers. IT leaders owe it to their company and their customers to take advantage of the risk reduction, cost savings, and other opportunities that Phoenix for Oracle Far Sync offers."

How to mitigate the performance/protection trade-off in Oracle Data Guard

Introduction
The most know trade-off in Oracle Data Guard is the performance vs. protection.
Oracle Data Guard brings several protection modes, which brings companies the flexibility in choosing the right configuration in order to meet the application SLA policies.

Data Protection Modes
There are 3 protection modes in Oracle Data Guard, which can be summarized in the following table:

Data Protection Modes - Key Considerations

•  Maximum Performance = compromise on protection 
•  Maximum Protection = compromise on performance (usually)
•  Better to use at least 2 standby databases when running maximum protection
•  NET_TIMEOUT parameter of LOG_ARCHIVE_DEST_n=30 seconds (default)

Mitigating the performance/protection trade-off in Oracle Data Guard
Oracle 12cR1 introduced a feature which can enable both performance and protection for far standby environments. For example, when the standby database is very far from the primary database, setting a "maximum protection" configuration will likely affect the performance due to the network lag and it may take time to acknowledge commit back to the primary database.
In order to address this challenge, Oracle introduced a feature  called "Far Sync". Far Sync enables having a lightweight instance which resides close to the primary database and is synchronized with the primary database. This light-wight instance has only control files, standby redo logs and archived redo logs. It does not have data files and therefore it cannot be opened for access. The redo entries will be transported from the near "far sync" instance to the "regular" standby database asynchronously.
This feature enables achieving both performance (due to the smaller network latency between primary and far sync instance) while allowing for higher data protection as the far sync instance is always synced with the primary database.

Here is an illustration of this new feature:

Licensing

This feature requires having the Active Data Guard license which is an extra option in Oracle Enterprise Edition. For more information about licensing: https://docs.oracle.com/en/database/oracle/oracle-database/18/dblic/Licensing-Information.html#GUID-B6113390-9586-46D7-9008-DCC9EDA45AB4

Useful Links

• Oracle Documentation: https://docs.oracle.com/database/121/SBYDB/create_fs.htm#SBYDB5416
• White Paper: https://www.oracle.com/technetwork/database/availability/farsync-2267608.pdf

IOUG Collaborate 2018 - Slide Decks are Available!

Last week I presented 3 sessions at my favorite Oracle conference - the IOUG Collaborate.
The slide decks for my presentations are now available:


Data Guard for Beginners
ASM Conepts, Architecture and Best Practices
Get the Oracle Performance Diagnostics Capabilities You Need without Spending a Fortune


I'd like to thank everybody who attended my sessions! Hope to see you again next year.

SYSBACKUP and SYSDG permissions in Oracle 12c

Introduction

In many companies there is a clear separation of duties for various Oracle Database related tasks such as administering ASM and backing up/restoring Oracle databases.

In the past, DBAs used SYSDBA permission for administering ASM and RMAN. As you probably know, SYSDBA is the most powerful permission in Oracle Database which even allows viewing all the application data.

Oracle realized that they need to address the separation of duties requirement of many customers and therefore they have provided in Oracle 11g a dedicated permission for administering ASM - I've written a dedicated blog post in the past for this matter. The SYSASM permission cannot access application data, but it can perform various ASM related management tasks (such as altering diskgroup, adding disks, etc.)

What about RMAN?

Until Oracle Database version 12cR1, there wasn't a good solution from a separation of duties when it comes to RMAN backups as users had to use SYSDBA which also allows them to access any application data (as well as other strong permissions).

In Oracle 12cR1, Oracle introduced the SYSBACKUP permission which allows a user to perform backup and recovery operations either from Oracle Recovery Manager (RMAN) or SQL*Plus.

You can view here the full list of operations allowed by this administrative privilege

And what about Data Guard?

Very similar to RMAN, Oracle also introduced in version 12cR1 a dedicated privilege named SYSDG which can be used with the Data Guard Broker and the DGMGRL command-line interface. 

Demo

First, we can connect to a 12c instance and look for those accounts. Next step would be to connect / AS SYSBACKUP since I'm logged with a user that has OS permissions to connect without any username and password

SQL> SELECT username, account_status
  FROM dba_users
 WHERE username LIKE '%SYS%';

USERNAME   ACCOUNT_STATUS
---------- --------------------------------
SYS        OPEN
SYSTEM     OPEN
SYS$UMF    EXPIRED & LOCKED
APPQOSSYS  EXPIRED & LOCKED
GGSYS      EXPIRED & LOCKED
WMSYS      EXPIRED & LOCKED
SYSBACKUP  EXPIRED & LOCKED
SYSRAC     EXPIRED & LOCKED
AUDSYS     EXPIRED & LOCKED
SYSKM      EXPIRED & LOCKED
SYSDG      EXPIRED & LOCKED

SQL> connect / as sysbackup
Connected.
SQL> show user
USER is "SYSBACKUP"

I can also create a new user and grant him the SYSBACKUP or SYSDG permissions

SQL> connect / as sysdba
Connected.

SQL> create user C##PINI identified by PINI;
User created.

SQL> grant SYSBACKUP to C##PINI;
Grant succeeded.

SQL> select username,SYSBACKUP, SYSDG from V$PWFILE_USERS;

USERNAME   SYSBA SYSDG
---------- ----- -----
SYS        FALSE FALSE
SYSDG      FALSE TRUE
SYSBACKUP  TRUE  FALSE
SYSKM      FALSE FALSE
C##PINI    TRUE  FALSE

Note that in order to connect to the database as either SYSDG or SYSBACKUP using a password, there must be a password file for it because it is possible to connect even when the database is not up and running, as follows

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

Total System Global Area 1644167168 bytes
Fixed Size                  8793400 bytes
Variable Size             989856456 bytes
Database Buffers          637534208 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> connect c##pini/pini
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
Warning: You are no longer connected to ORACLE.

SQL> connect c##pini/pini as SYSBACKUP;
Connected.

Summary

In this post we've reviewed the SYSDG and SYSBACKUP users and permissions in Oracle 12c which could be useful in case that in your company there is a requirement to have a separation of duties for backup/recovery as well as for Data Guard related administration tasks. I hope you find it useful for you.

How to detect gaps in your Data Guard environments

In the OTN Data Guard Forum, a user was trying to understand if there's a gap between the Primary and the Standby database so he executed the following query:

select count(*) from v$archived_log where applied = 'NO'

The output of the query was higher than 3000, my response was that the fact that his query returns more than 3000  doesn't necessarily tell that there's any problem becuase when you query v$archived_log it will always report "NO" for the local archive log destinations, therefore he should check for APPLIED = 'NO' and also standby_dest = 'YES'.

Link to the Discussion:

https://community.oracle.com/thread/3781242?sr=stream

I decided to enhance the query even more so it will dipslay per each standby archive log destinaion the following information:

• INST_ID - The Instance ID, relevant for RAC with Data Guard configurations
• DEST_ID - The ID of the Archive Log Destination
• Status - Will indicate whether the standby destination status is valid or not
• Destination - The name of the Archive Log Destination
• Applied_Gap - The applied gap between the Primary and the Standby
• Received_Gap - The received gap between the Primary and the Standby
• Last_Received_Seq - Last archive log received in the standby destination
• Last_Applied_Seq - Last archive log applied in the standby destination

The following query should always be executed on the Primary database:

select ar.inst_id "inst_id",
       ar.dest_id "dest_id",
       ar.status "dest_status",
       ar.destination "destination",
         (select MAX (sequence#) highiest_seq
            from v$archived_log val, v$database vdb
           where     val.resetlogs_change# = vdb.resetlogs_change#
                 and thread# = ar.inst_id
                 and dest_id = ar.dest_id)
       - NVL (
            (select MAX (sequence#)
               from v$archived_log val, v$database vdb
              where     val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'
                    and applied = 'YES'),
            0)
          "applied_gap",
         (SELECT MAX (sequence#) highiest_seq
            from v$archived_log val, v$database vdb
           where     val.resetlogs_change# = vdb.resetlogs_change#
                 AND thread# = ar.inst_id)
       - NVL (
            (SELECT MAX (sequence#)
               from v$archived_log val, v$database vdb
              where     val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'),
            0)
          "received_gap",
       NVL (
          (SELECT MAX (sequence#)
             from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'),
          0)
          "last_received_seq",
       NVL (
          (SELECT MAX (sequence#)
             from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'
                  and applied = 'YES'),
          0)
          "last_applied_seq"
  from (SELECT DISTINCT dest_id,
                        inst_id,
                        status,
                        target,
                        destination,
                        error
          from sys.gv_$archive_dest
         where target = 'STANDBY' and STATUS <> 'DEFERRED') ar

Example of the SQL output:

I executed the query in one of our data guard environments.
As you can see from the example output, the sequence# of the last archive log that was sent to the standby destination is 9699 and the last one that was applied is 9698, the applied gap is 1 which is definitely OK because I'm working with a standby redo log therefore the last sequence# should always be the standby redolog that Oracle is currently writing to.

Bottom line:
As long as the applied and the received gap are lower than 2 the it means that there are no gaps in your Data Guard environment.