Data Guard on OCI PaaS DB

Oracle has introduced the data guard capability for DBs created with VM shape types. There's an earlier post which shows how to setup a data guard configuration with PaaS DB primary and standby created on IaaS resources (compute instances + block storages). This post looks at new capability introduced for VM shapes on PaaS DBs.
The data guard created on PaaS will have both primary and standby on same region but in a different availability domain, unlike in the previous post where standby and primary were on separate regions. Therefore before creating the data guard association for an existing database create a subnet on the availability domain that will be used for standby. AD1Subnet and AD2Subnets are created for this purpose where AD1 will be used for primary and AD2 will be used for standby.

Network traffic must flow freely between the two subnets on port 1521 (or the port used for listener). Add the security rule to the VCN security list.

These two are the only new things to concern about before creating the primary DB. Once the primary DB is created enable data guard on the primary DB.

This will open the DB system information form to be filled with information for standby system. Once details are submitted the standby system will provisioned.

A red shield icon will be displayed next to the DB that has a data guard association.

At the end of the provisioning a data guard configuration will be created which would also include a DG broker setup. The default setup will be maximum performance mode with async redo transport mode.

DGMGRL> show configuration

Configuration - fraDB_fra1h8_fraDB_fra27c

  Protection Mode: MaxPerformance
  Members:
  fraDB_fra1h8 - Primary database
    fraDB_fra27c - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 37 seconds ago)

DGMGRL> show database fraDB_fra1h8

Database - fraDB_fra1h8

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    fraDB

Database Status:
SUCCESS

The standby will be in mount mode not open for read only.

DGMGRL> show database fraDB_fra27c

Database - fraDB_fra27c

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 6 seconds ago)
  Apply Lag:          0 seconds (computed 6 seconds ago)
  Average Apply Rate: 3.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    fraDB

Database Status:
SUCCESS

Verbose output for database configuration is given at the end.


Automatic backup could be enabled in both primary and standby. However, only the primary DB at the time will execute the backup. Backups will start running on the (old) standby after a role change when it becomes the (new) primary. The DB in standby mode will not execute a backup and as such there will be no backup failures logged on standby unlike previous post.

Switchover could be performed using DGB as before.

DGMGRL> show configuration

Configuration - fraDB_fra1h8_fraDB_fra27c

  Protection Mode: MaxPerformance
  Members:
  fraDB_fra1h8 - Primary database
    fraDB_fra27c - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 27 seconds ago)

DGMGRL> validate database fraDB_fra27c

  Database Role:     Physical standby database
  Primary Database:  fraDB_fra1h8

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    fraDB_fra1h8:  YES
    fraDB_fra27c:  YES

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        1 second (computed 10 seconds ago)
    Apply Delay:      0 minutes

  Log Files Cleared:
    fraDB_fra1h8 Standby Redo Log Files:  Cleared
    fraDB_fra27c Online Redo Log Files:   Not Cleared
    fraDB_fra27c Standby Redo Log Files:  Available


DGMGRL> switchover to fraDB_fra27c;
Performing switchover NOW, please wait...
Operation requires a connection to database "fraDB_fra27c"
Connecting ...
Connected to "fraDB_fra27c"
Connected as SYSDBA.
New primary database "fraDB_fra27c" is opening...
Oracle Clusterware is restarting database "fraDB_fra1h8" ...
Connected to "fraDB_fra1h8"
Connected to "fraDB_fra1h8"
Switchover succeeded, new primary is "fradb_fra27c"
DGMGRL> show configuration

Configuration - fraDB_fra1h8_fraDB_fra27c

  Protection Mode: MaxPerformance
  Members:
  fraDB_fra27c - Primary database
    fraDB_fra1h8 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 15 seconds ago)

DGMGRL> switchover to fraDB_fra1h8
Performing switchover NOW, please wait...
Operation requires a connection to database "fraDB_fra1h8"
Connecting ...
Connected to "fraDB_fra1h8"
Connected as SYSDBA.
New primary database "fraDB_fra1h8" is opening...
Oracle Clusterware is restarting database "fraDB_fra27c" ...
Switchover succeeded, new primary is "fradb_fra1h8"
DGMGRL> show configuration

Configuration - fraDB_fra1h8_fraDB_fra27c

  Protection Mode: MaxPerformance
  Members:
  fraDB_fra1h8 - Primary database
    fraDB_fra27c - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 28 seconds ago)

The switchover and failover could also be done on cloud console. However, at the time of the post, the role reversal is not reflected correclty on the peer role. But the operations listed for the DB are valid for that role. Below it shows switchover and reinstate options for primary DB, even though peer role is listed as standby.

On the other-hand on standby it only gives failover as the option but the peer role is listed as primary.

Even though the default protection mode is maximum performance, this could be changed after setting the redo transport mode compatible with the protection mode begin set.
Update 2021/03/30 As of this update, only maximum performance is supported in OCI DBCS for VMs. Though protection mode could be changed manually as below this won't be supported by Oracle.

DGMGRL> edit database fraDB_fra27c set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database fraDB_fra1h8 set property LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL> edit configuration set protection mode as maxavailability;
Succeeded.

DGMGRL> show configuration

Configuration - fraDB_fra1h8_fraDB_fra27c

  Protection Mode: MaxAvailability
  Members:
  fraDB_fra27c - Primary database
    fraDB_fra1h8 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 22 seconds ago)

Finally the patch applying could be done in a standby first manner.

When a DB system is dropped, the associated DG configuration is also removed at the same time.

Related Post
Data Guard With OCI PaaS DB (DBaaS) Primary and Standby on IaaS



DG Broker verbose output for databases.

DGMGRL> show database verbose fraDB_fra1h8

Database - fraDB_fra1h8

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    fraDB

  Properties:
    DGConnectIdentifier             = 'fraDB_fra1h8'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '8'
    LogArchiveMinSucceedDest        = '1'
    DataGuardSyncLatency            = '0'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    PreferredObserverHosts          = ''
    HostName                        = 'fravm'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fravm)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=fraDB_fra1h8_DGMGRL.ad1subnet.fravcn.oraclevcn.com)(INSTANCE_NAME=fraDB)(SERVER=DEDICATED)))'
    OnlineArchiveLocation           = 'LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY REOPEN=0'
    OnlineAlternateLocation         = ''
    StandbyArchiveLocation          = 'LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY REOPEN=0'
    StandbyAlternateLocation        = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/fradb_fra1h8/fraDB/trace/alert_fraDB.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/fradb_fra1h8/fraDB/trace/drcfraDB.log

Database Status:
SUCCESS

DGMGRL> show database verbose fraDB_fra27c

Database - fraDB_fra27c

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 5 seconds ago)
  Apply Lag:          0 seconds (computed 5 seconds ago)
  Average Apply Rate: 3.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    fraDB

  Properties:
    DGConnectIdentifier             = 'fraDB_fra27c'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '8'
    LogArchiveMinSucceedDest        = '1'
    DataGuardSyncLatency            = '0'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    PreferredObserverHosts          = ''
    HostName                        = 'fravmst'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fravmst)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=fraDB_fra27c_DGMGRL.ad2subnet.fravcn.oraclevcn.com)(INSTANCE_NAME=fraDB)(SERVER=DEDICATED)))'
    OnlineArchiveLocation           = 'LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY REOPEN=0'
    OnlineAlternateLocation         = ''
    StandbyArchiveLocation          = 'LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY REOPEN=0'
    StandbyAlternateLocation        = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/fradb_fra27c/fraDB/trace/alert_fraDB.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/fradb_fra27c/fraDB/trace/drcfraDB.log

Database Status:
SUCCESS

Udev Rules for AWS EBS Volumes

AWS EBS volumes could be used as ASM disks. When ASMLib or AFD is not used, udev rules must be used to set the correct permissions on the EBS block volumes. It's generally good practice to use a unique identifier (UUID) to identify the partitions that require permission being set. It's not a guarantee that partitions or the blocks will be attached to the server in the same order and getting the same name.
Depending on the tool/option used a unique identifier may not be always available. For example the default dos partition table type created using fdisk would not generate any unique ID.

Model: Xen Virtual Block Device (xvd)
Disk /dev/xvdb: 268GB
Sector size (logical/physical): 512B/512B
Partition Table: msdos
Disk Flags:

Number  Start   End    Size   Type     File system  Flags
 1      1049kB  268GB  268GB  primary  ext4

udevadm info --query=property /dev/xvdd1
DEVNAME=/dev/xvdd1
DEVPATH=/devices/vbd-51760/block/xvdd/xvdd1
DEVTYPE=partition
ID_PART_ENTRY_DISK=202:48
ID_PART_ENTRY_NUMBER=1
ID_PART_ENTRY_OFFSET=2048
ID_PART_ENTRY_SCHEME=dos
ID_PART_ENTRY_SIZE=209713152
ID_PART_ENTRY_TYPE=0x83
ID_PART_TABLE_TYPE=dos
MAJOR=202
MINOR=49
SUBSYSTEM=block
TAGS=:systemd:
USEC_INITIALIZED=634695673

On the other-hand if the partition table type was GPT, this could generate several unique IDs which could be used in the udev rule to identify the partition.

fdisk /dev/sdd

Command (m for help): g
Building a new GPT disklabel (GUID: EC7F1589-8BD2-4C94-8F8F-D22013D40406)


Command (m for help): n
Partition number (1-128, default 1):
First sector (2048-20971486, default 2048):
Last sector, +sectors or +size{K,M,G,T,P} (2048-20971486, default 20971486):
Created partition 1


Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

 fdisk -l /dev/sdd
WARNING: fdisk GPT support is currently new, and therefore in an experimental phase. Use at your own discretion.

Disk /dev/sdd: 10.7 GB, 10737418240 bytes, 20971520 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: gpt
Disk identifier: EC7F1589-8BD2-4C94-8F8F-D22013D40406


#         Start          End    Size  Type            Name
 1         2048     20971486     10G  Linux filesyste

udevadm info --query=property /dev/sdd1
...
ID_PART_ENTRY_DISK=8:48
ID_PART_ENTRY_NUMBER=1
ID_PART_ENTRY_OFFSET=2048
ID_PART_ENTRY_SCHEME=gpt
ID_PART_ENTRY_SIZE=20969439
ID_PART_ENTRY_TYPE=0fc63daf-8483-4772-8e79-3d69d8477de4
ID_PART_ENTRY_UUID=573dded4-21f1-48ce-925f-e02c5d94dace
ID_PART_TABLE_TYPE=gpt
ID_PATH=pci-0000:00:0d.0-ata-4.0
ID_PATH_TAG=pci-0000_00_0d_0-ata-4_0
...
SUBSYSTEM=block
TAGS=:systemd:

The partition UUID could be found out using blkid as well.

blkid /dev/sdd1
/dev/sdd1: PARTLABEL="data" PARTUUID="573dded4-21f1-48ce-925f-e02c5d94dace"

The ID_PART_ENTRY_UUID could be used in the udev rule.

KERNEL=="sd?1",ENV{ID_PART_ENTRY_UUID}=="573dded4-21f1-48ce-925f-e02c5d94dace", SYMLINK+="oracleasm/cgdata1", OWNER="oracle", GROUP="asmadmin", MODE="0660"

Similar to fdisk with g (gpt) option, parted could be used to achieve the same.

parted /dev/xvdd
GNU Parted 3.1
Using /dev/xvdd
Welcome to GNU Parted! Type 'help' to view a list of commands.
(parted) mktable gpt
Warning: The existing disk label on /dev/xvdd will be destroyed and all data on this disk will be lost. Do you want to continue?
Yes/No? yes
(parted) mkpart data 0% 100%

(parted) print all
Model: Xen Virtual Block Device (xvd)
Disk /dev/xvdd: 107GB
Sector size (logical/physical): 512B/512B
Partition Table: gpt
Disk Flags:

Number  Start   End    Size   File system  Name  Flags
 1      1049kB  107GB  107GB               data

udevadm info --query=property /dev/xvdd1
DEVLINKS=/dev/disk/by-partlabel/data /dev/disk/by-partuuid/5a081ffa-56e2-467b-82a9-16e3a4f441bd
DEVNAME=/dev/xvdd1
DEVPATH=/devices/vbd-51760/block/xvdd/xvdd1
DEVTYPE=partition
ID_PART_ENTRY_DISK=202:48
ID_PART_ENTRY_NAME=data
ID_PART_ENTRY_NUMBER=1
ID_PART_ENTRY_OFFSET=2048
ID_PART_ENTRY_SCHEME=gpt
ID_PART_ENTRY_SIZE=209711104
ID_PART_ENTRY_TYPE=ebd0a0a2-b9e5-4433-87c0-68b6b72699c7
ID_PART_ENTRY_UUID=5a081ffa-56e2-467b-82a9-16e3a4f441bd
ID_PART_TABLE_TYPE=gpt
MAJOR=202
MINOR=49
SUBSYSTEM=block
TAGS=:systemd:
USEC_INITIALIZED=685327395

AWR Reports on Standby when Active Data Guard is Used

Oracle introduced Remote Management Framework (RMF) in 12.2 which allows creating AWR reports on standby database when active data guard is in use. This post list the steps for setting up the RMF so AWR reports could be generated on standby. The post use the data guard configuration set up on 18.3, which is mentioned in a previous post. Current data guard setup and standby open mode is as follows.

DGMGRL> show configuration

Configuration - paas_iaas_dg

  Protection Mode: MaxAvailability
  Members:
  fradb_fra1kk - Primary database
    londb        - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 11 seconds ago)

DGMGRL> show database londb

Database - londb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    lonDB

Database Status:
SUCCESS

Oracle provide a pre-created user sys$umf (locked by default) which has all the necessary privileges to carry out the RMF related work. Unlock this user from primary DB and set a password.

alter user sys$umf identified by rmfuser account unlock;

Next create two database link that sys$umf user will use to connect to and from the standby DB. The TNS entries used here were created in the previous post. Before creating database link take a note of the global_names parameter. If this is set to true, then DB links are expected to be the same name as the DB they connect to. If not ORA-02085 error could be encountered when trying to use the DB links.

create database link fra_to_lon CONNECT TO sys$umf IDENTIFIED BY rmfuser  using 'LONDBTNS';
create database link lon_to_fra CONNECT TO sys$umf IDENTIFIED BY rmfuser  using 'FRADBTNS';

Check the DB links are working by querying remote instance using them. Run both on primary and standby.

SQL> select instance_name from v$instance@fra_to_lon;

INSTANCE_NAME
----------------
lonDB

SQL>  select instance_name from v$instance@lon_to_fra;

INSTANCE_NAME
----------------
fraDB

On primary run the following to configure the primary node with RMF. Configure node require a unique name for each node configured. If none is provided, the db_unique_name will be used instead.

SQL> exec dbms_umf.configure_node ('fraDB');

PL/SQL procedure successfully completed.

On standby run the following to configure the standby with RMF. In this case a unique name for standby and the db link name from standby to primary is given as inputs.

SQL> exec dbms_umf.configure_node ('lonDB','lon_to_fra');

PL/SQL procedure successfully completed.

Create the RMF topology by running following on primary.

SQL> exec DBMS_UMF.create_topology ('FRA_LON_TOPOLOGY');

PL/SQL procedure successfully completed.

SQL> select * from dba_umf_topology;

TOPOLOGY_NAME         TARGET_ID TOPOLOGY_VERSION TOPOLOGY
-------------------- ---------- ---------------- --------
FRA_LON_TOPOLOGY     1423735874                1 ACTIVE

View the registered nodes. Only primary is registered so far.

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME        NODE_NAME     NODE_ID  NODE_TYPE AS_SO AS_CA STATE
-------------------- ---------- ---------- ---------- ----- ----- --------------------
FRA_LON_TOPOLOGY     fraDB      1423735874          0 FALSE FALSE OK

Register the standby with the topology. The meaning of the input parameters could be found here. Execute the following on primary.

exec DBMS_UMF.register_node ('FRA_LON_TOPOLOGY', 'lonDB', 'fra_to_lon', 'lon_to_fra', 'FALSE', 'FALSE');

Check both nodes are registered.

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME        NODE_NAME     NODE_ID  NODE_TYPE AS_SO AS_CA STATE
-------------------- ---------- ---------- ---------- ----- ----- -----
FRA_LON_TOPOLOGY     fraDB      1423735874          0 FALSE FALSE OK
FRA_LON_TOPOLOGY     lonDB      4041047630          0 FALSE FALSE OK

Register the AWR service on the remote node.

SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'lonDB');

PL/SQL procedure successfully completed.

Verify AWR service is active on the remote node

SQL> select * from dba_umf_service;

TOPOLOGY_NAME           NODE_ID SERVICE
-------------------- ---------- -------
FRA_LON_TOPOLOGY     4041047630 AWR

To generate AWR report create two snapshots on the remote database.

SQL> exec dbms_workload_repository.create_remote_snapshot('lonDB');

PL/SQL procedure successfully completed.

Once a snapshot is created the standby DB is listed in the AWR with the DB ID that is equal to the node ID (highlighted above) in the UMF registration.

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1042410484     1      FRADB        lonDB        lonvm
  4041047630     1      FRADB        lonDB        lonvm
* 1042410484     1      FRADB        fraDB        fravm

Enter value for dbid: 4041047630

The AWR instance report generated would list the role as physical standby.

The AWR control view list the standby database as well.

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID   SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ---------- ---------- ---------- ----------
1042410484 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT             0 1042410484
4041047630 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT               1042410484 lonDB

The remote snapshots will be automatically taken according to snapshot internal.

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1042410484     1      FRADB        lonDB        lonvm
  4041047630     1      FRADB        lonDB        lonvm
* 1042410484     1      FRADB        fraDB        fravm

Enter value for dbid: 4041047630
Using 4041047630 for database Id
Enter value for inst_num: 1
Using 1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

lonDB        FRADB                1  29 Nov 2018 13:11    1
                                  2  29 Nov 2018 13:59    1
                                  3  29 Nov 2018 14:58    1
                                  4  29 Nov 2018 15:58    1

However, after a switchover, when roles changes the automatic snapshot taking will stop, both on new primary (old standby) and new standby (old primary). In order to automatic AWR snapshot to continue do the following after a switchover. As the first step unregistered the new primary (old standby) as a remote database.

SQL> exec DBMS_WORKLOAD_REPOSITORY.UNREGISTER_REMOTE_DATABASE('lonDB','FRA_LON_TOPOLOGY',false);

PL/SQL procedure successfully completed.

Drop the topology

exec DBMS_UMF.drop_topology('FRA_LON_TOPOLOGY');

PL/SQL procedure successfully completed.

Run un-configure procedure on each node

SQL>  exec DBMS_UMF.UNCONFIGURE_NODE;

PL/SQL procedure successfully completed.

Re-create the topology again with new primary and standby. On new primary

SQL> exec dbms_umf.configure_node ('lonDB');

PL/SQL procedure successfully completed.

On new standby

SQL> exec dbms_umf.configure_node ('fraDB','fra_to_lon');

PL/SQL procedure successfully completed.

Same topology name is used

exec DBMS_UMF.create_topology ('FRA_LON_TOPOLOGY');

Register new standby

exec DBMS_UMF.register_node ('FRA_LON_TOPOLOGY', 'fraDB', 'lon_to_fra', 'fra_to_lon', 'FALSE', 'FALSE');

Register remote DB for AWR by specifying the node ID of the DB shown in dba_umf_registration earlier. If the remote DB registration was to be done using DB name as before then "ORA-13516: AWR Operation failed: ORA-13516: AWR Operation failed: Remote source not registered for AWR" would be encountered when snapshots are taken. Doing log switches as suggested by 2409808.1 did not resolve this issue.

exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(1423735874);

PL/SQL procedure successfully completed.

Once remote DB is registered remote snapshots could be taken

SQL> exec dbms_workload_repository.create_remote_snapshot('fraDB');

PL/SQL procedure successfully completed.

After the role reversal, automatic snapshots will continue on the snapshot interval. Run AWR instance reports same as before.

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1042410484     1      FRADB        fraDB        fravm
  1423735874     1      FRADB        fraDB        fravm
* 1042410484     1      FRADB        lonDB        lonvm

Enter value for dbid: 1423735874

The new primary (old standby) will be reflected on the AWR report taken against it.

Useful Metalink Note
How to Generate AWRs in Active Data Guard Standby Databases [ID 2409808.1]

Related Posts
Enabling Automatic AWR Snapshots on PDB

Update on 2020-07-13
Oracle documentation now has a separate section on "Managing ADG Role Transition". The doc shows the use of DBMS_UMF.SWITCH_DESTINATION for role reversal scenarios.