DEV Community: Vahid Yousefzadeh

Automatic Standby PDB Instantiation and Standby Redo Log Creation in Oracle AI Database 26ai(23.26.2)

Vahid Yousefzadeh — Wed, 15 Jul 2026 06:12:38 +0000

Oracle AI Database Release Update 23.26.2 introduces an important enhancement for Data Guard per Pluggable Database (PDB) environments. When a protected PDB is added through Data Guard Broker, Oracle automatically performs several tasks that previously required manual intervention.

Prior to Release Update 23.26.2, creating a standby PDB typically involved multiple manual steps, such as:

Copying datafiles to the standby CDB
Creating Standby Redo Logs (SRLs)
Verifying the standby configuration

Starting with 23.26.2, Data Guard Broker automates these operations, simplifying the deployment process and reducing the risk of configuration errors.

To demonstrate this enhancement, I created a standby PDB named AMOL on the standby database CDB2 using the following Broker command:

DGMGRL>  ADD PLUGGABLE DATABASE 'amol' AT cdb2 SOURCE is 'amol' AT cdb1 PDBFileNameConvert is "'/CDB1/','/CDB2/'";
Pluggable Database "AMOL" added

Notice that no RMAN restore, manual file copy, or standby redo log creation commands are required.

The new PDB initially appears in the MOUNTED state.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 BABOL                          READ WRITE NO
         4 AMOL                           MOUNTED

After opening the PDB,

SQL> alter pluggable database AMOL open;
Pluggable database altered.

it becomes available in READ ONLY mode, which is the expected state for a physical standby PDB.

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 BABOL                          READ WRITE NO
         4 AMOL                           READ ONLY  NO

Next, I switched to the AMOL container and verified that the datafiles had already been created.

SQL> alter session set container=amol;
Session altered.
SQL> select name from v$datafile;
NAME
-------------------------------------------------
/oracle/base/oradata/CDB2/568CEB400DB29D03E0630488200A65CB/datafile/o1_mf_system_o5cv72qp_.dbf
/oracle/base/oradata/CDB2/568CEB400DB29D03E0630488200A65CB/datafile/o1_mf_sysaux_o5cv71nb_.dbf
/oracle/base/oradata/CDB2/568CEB400DB29D03E0630488200A65CB/datafile/o1_mf_undotbs1_o5cv716w_.dbf
/oracle/base/oradata/CDB2/568CEB400DB29D03E0630488200A65CB/datafile/o1_mf_users_o5cv710h_.dbf

This confirms that Oracle automatically copied the datafiles from the primary database during standby PDB instantiation.

Another enhancement introduced in Release Update 23.26.2 is the automatic creation of Standby Redo Logs.

SQL> select group# from v$standby_log;

    GROUP#
----------
         4
         5
         6

No manual ALTER DATABASE ADD STANDBY LOGFILE commands were necessary.

Finally, I validated the standby PDB configuration.

DGMGRL>  VALIDATE PLUGGABLE DATABASE amol AT cdb2;

  Ready for Switchover:    YES

  Data Guard Role:         Physical Standby
  Apply State:             Running
  Standby Redo Log Files:  3
  Source:                  AMOL (con_id 3) at cdb1

The validation confirms that redo apply is running successfully, the standby redo logs are configured correctly, and the standby PDB is ready for switchover.

The following alert log excerpt shows the internal operations performed automatically during standby PDB instantiation.

create pluggable database AMOL as standby from AMOL at cdb1 file_name_convert=('/CDB1/','/CDB2/')
2026-07-14T11:31:34.047911+03:30
AMOL(4):ALTER SYSTEM SET _dgpdb_file_name_convert='/CDB1/','/CDB2/' SCOPE=SPFILE PDB='AMOL';
2026-07-14T11:31:34.116071+03:30
****************************************************************
Pluggable Database AMOL with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000132
****************************************************************
Completed: create pluggable database AMOL as standby from AMOL at cdb1 file_name_convert=('/CDB1/','/CDB2/')
2026-07-14T11:31:34.533442+03:30
 rfs (PID:809423): krsr_rfs_atc: DBROLE:PRIMARY Client is ASYNC DB: (PID:240147) [krsr.c:6137]
 rfs (PID:809423): Foreign redo connection for cdb1(DBID:1212295561), Client is ASYNC (PID:240147) [krsr.c:6918]
 rfs (PID:809423): Disable RFS client RFS LogMiner Client [krsr.c:7033]
 rfs (PID:809423): No SRLs created [krsk.c:5084]
2026-07-14T11:31:34.562849+03:30
 rfs (PID:809423): Opened log for DBID:1212295561 B-1238578121.T-1.S-2.C-0 [krsr.c:19571]
2026-07-14T11:31:35.430667+03:30
 rfs (PID:809423): Archived Log entry 1 added for B-1238578121.T-1.S-2 LOS:0x0000000000200744 NXS:0x0000000000203102 NAB:75800 ID 0x4842e589 LAD:1 [krsp.c:952]
2026-07-14T11:31:36.546432+03:30
 rfs (PID:809435): krsr_rfs_atc: DBROLE:PRIMARY Client is ASYNC DB: (PID:240145) [krsr.c:6137]
 rfs (PID:809435): Foreign redo connection for cdb1(DBID:1212295561), Client is ASYNC (PID:240145) [krsr.c:6918]
2026-07-14T11:31:36.564502+03:30
 rfs (PID:809435): Opened LNO:4 for DBID:1212295561 B-1238578121.T-1.S-3.C-0 [krsr.c:19561]
2026-07-14T11:31:36.954106+03:30
AMOL(4):Restore from backup piece network
AMOL(4):Full restore complete of  datafile 15 /oracle/base/oradata/CDB2/568CEB400DB29D03E0630488200A65CB/datafile/o1_mf_users_o5cv710h_.dbf.  Elapsed time: 0:00:00
AMOL(4):   checkpoint is SCN 2109708 time 07/14/2026 11:30:48 on PDB incarnation 0
AMOL(4):2071166
AMOL(4):Restore from backup piece network
AMOL(4):Full restore complete of  datafile 14 /oracle/base/oradata/CDB2/568CEB400DB29D03E0630488200A65CB/datafile/o1_mf_undotbs1_o5cv716w_.dbf.  Elapsed time: 0:00:00
AMOL(4):   checkpoint is SCN 2109713 time 07/14/2026 11:30:48 on PDB incarnation 0
AMOL(4):2108839
AMOL(4):2107197
AMOL(4):Restore from backup piece network
2026-07-14T11:31:38.597999+03:30
AMOL(4):Full restore complete of  datafile 13 /oracle/base/oradata/CDB2/568CEB400DB29D03E0630488200A65CB/datafile/o1_mf_sysaux_o5cv71nb_.dbf.  Elapsed time: 0:00:01
AMOL(4):   checkpoint is SCN 2109718 time 07/14/2026 11:30:49 on PDB incarnation 0
AMOL(4):2081853
AMOL(4):Restore from backup piece network
AMOL(4):Full restore complete of  datafile 12 /oracle/base/oradata/CDB2/568CEB400DB29D03E0630488200A65CB/datafile/o1_mf_system_o5cv72qp_.dbf.  Elapsed time: 0:00:01
AMOL(4):   checkpoint is SCN 2109723 time 07/14/2026 11:30:50 on PDB incarnation 0
AMOL(4):2085977
AMOL(4):2107197

Oracle 26ai(23.26.2): WAIT and NOWAIT Support for DELETE, UPDATE, INSERT, and MERGE Statements

Vahid Yousefzadeh — Wed, 15 Jul 2026 06:09:05 +0000

Rows modified by DML statements are locked and, if another session attempts to modify the same row, that session must wait until the row becomes available. In previous Oracle Database releases, the WAIT clause was supported only by the SELECT FOR UPDATE statement, enabling users to specify how long to wait for a row lock before the statement is rolled back.

For example:

Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

SQL> select * from tbl for update wait 10;
ORA-30006: resource busy; acquire with WAIT timeout expired

Similarly, the NOWAIT clause can be used to instruct the database to return immediately if the requested row is locked by another transaction:

SQL> select * from tbl for update nowait;

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

If neither WAIT nor NOWAIT is specified, Oracle retains its default behavior and waits indefinitely for the row lock to become available:

SQL> select * from tbl for update;

Executing

Starting with Oracle AI Database 23.26.2 (Oracle AI Database 26ai), support for WAIT and NOWAIT has been extended to all DML statements, including INSERT, UPDATE, DELETE, and MERGE. These clauses enable users to control how the database proceeds when a transaction attempts to lock a row that is already locked by another transaction.

Specifying NOWAIT causes the database to roll back the DML statement immediately if a conflicting lock exists, while the WAIT clause allows users to define how long the database should wait before rolling back the statement.

DELETE

Session 1 acquires a row lock:

SQL> delete tbl where id=1;

1 row deleted

Session 2 attempts to delete the same row and waits for five seconds before timing out:

SQL> delete tbl where id=1 wait 5;

ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction", Description: "Lock held by a transaction to allow other transactions to wait for it") because it is currently held by another session. The resource being locked can be identified by 524315 ("usn<<16 | slot") and 536 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 8.27.536

UPDATE

Session 1 updates a row:

SQL> update tbl set name='Vahid Yousefzadeh' where id=1 nowait;

1 row updated

Session 2 attempts to update the same row using NOWAIT and immediately receives an error:

SQL> update tbl set name='Vahid Yousefzadeh' where id=1 nowait;

ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction", Description: "Lock held by a transaction to allow other transactions to wait for it") because it is currently held by another session. The resource being locked can be identified by 65540 ("usn<<16 | slot") and 531 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 1.4.531

INSERT

Session 1 inserts a row:

SQL> insert into tbl values(1,'Vahid YousefZadeh') wait 2;

1 row inserted

Session 2 attempts to insert a conflicting row and waits for two seconds before the statement is rolled back:

SQL> insert into tbl values(1,'Vahid YousefZadeh') wait 2;

ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction", Description: "Lock held by a transaction to allow other transactions to wait for it") because it is currently held by another session. The resource being locked can be identified by 655369 ("usn<<16 | slot") and 528 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 10.9.528

MERGE

Session 1 modifies the target row:

SQL> update tbl set name='Vahid Yousefzadeh' where id=1;

1 row updated

Session 2 executes a MERGE statement with a WAIT clause:

MERGE INTO tbl t
USING (SELECT 1 id, 'Usef' name FROM dual) s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (s.id, s.name) wait 2;

ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction", Description: "Lock held by a transaction to allow other transactions to wait for it") because it is currently held by another session. The resource being locked can be identified by 196640 ("usn<<16 | slot") and 573 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 3.32.573

Oracle 26ai — Enhancements to Data Dictionary Views for Partitioning

Vahid Yousefzadeh — Sat, 11 Jul 2026 09:13:07 +0000

In data dictionary views related to partitioning, the HIGH_VALUE column is one of the most frequently used attributes. However, its datatype is LONG, which comes with several limitations and restrictions.

To address these limitations, Oracle AI Database 26ai introduces two additional columns, HIGH_VALUE_CLOB and HIGH_VALUE_JSON, allowing partition high values to be represented in more convenient formats.

The following example creates an interval-partitioned table:

SQL> create table tb(
  2    id  number,
  3    date_ date
  4  )
  5  partition by range ( date_ )
  6  interval ( numtoyminterval(1,'YEAR'))
  7  (
  8   PARTITION p2022 VALUES LESS THAN (TO_DATE('2023/01/01', 'YYYY/MM/DD')),
  9   PARTITION p2023 VALUES LESS THAN (TO_DATE('2024/01/01', 'YYYY/MM/DD')),
 10   PARTITION p2024 VALUES LESS THAN (TO_DATE('2025/01/01', 'YYYY/MM/DD'))
 11  );
Table created

To display partition boundary values in CLOB format, the HIGH_VALUE_CLOB column can be queried:

SQL> select partition_name, high_value_clob from user_tab_partitions where  table_name = 'TB' order by 1;
PARTITION_NAME       HIGH_VALUE_CLOB                                          
-------------------- -------------------------------------------------------- 
P2022                TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' 
P2023                TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' 
P2024                TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'

Oracle 26ai also introduces the HIGH_VALUE_JSON column, which exposes the same information in JSON format:

SQL> select partition_name,HIGH_VALUE_JSON  from user_tab_partitions where  table_name = 'TB' order by 1;
PARTITION_NAME       HIGH_VALUE_JSON
-------------------- ---------------------------------------------------------
P2022                {"high_value":"2023-01-01T00:00:00"}
P2023                {"high_value":"2024-01-01T00:00:00"}
P2024                {"high_value":"2025-01-01T00:00:00"}

Restoring a PDB Backup into a New CDB

Vahid Yousefzadeh — Sat, 11 Jul 2026 09:10:58 +0000

According to Oracle Backup and Recovery documentation, restoring a Pluggable Database (PDB) normally requires access to the backup of the Container Database (CDB) that originally hosted the PDB. Without the source CDB metadata, restoring a standalone PDB backup is generally not possible.

However, there is an alternative approach that can sometimes be used to recover a PDB backup and plug it into a different CDB. Although this method is not guaranteed to work in every scenario, it is a useful technique to be aware of when designing backup and recovery strategies.

In this example, we will back up a PDB named RMANPDB, transfer the backup to another server, and restore it into a completely new CDB.

Backing Up the PDB

First, verify that the PDB exists and is open:

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PDB1                           READ WRITE NO
         4 RMANPDB                        READ WRITE NO

Create a RMAN backup of the PDB and include the current control file:

RMAN> backup pluggable database RmanPDB format '/oracle21c/bkp_pdb/%U' current controlfile  format '/oracle21c/bkp_pdb/control0000.bkp';
Starting backup at 16-OCT-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=266 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00068 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_sysaux_jpo62lhz_.dbf
input datafile file number=00067 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_system_jpo62lhv_.dbf
input datafile file number=00069 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_undotbs1_jpo62lj1_.dbf
input datafile file number=00070 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_tbs01_jpo66vjx_.dbf
channel ORA_DISK_1: starting piece 1 at 16-OCT-21
channel ORA_DISK_1: finished piece 1 at 16-OCT-21
piece handle=/oracle21c/bkp_pdb/4p0bo4mu_153_1_1 tag=TAG20211016T052342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 16-OCT-21
channel ORA_DISK_1: finished piece 1 at 16-OCT-21
piece handle=/oracle21c/bkp_pdb/control0000.bkp tag=TAG20211016T052342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-OCT-21

Transferring the Backup

Copy the backup pieces to the target server:

[oracle@RAC2 ~]$ mkdir -p /oracle21c/bkp_pdb
[oracle@oLinux7 ~]$ scp /oracle21c/bkp_pdb/* 192.168.1.20:/oracle21c/bkp_pdb/
4p0bo4mu_153_1_1                                      100%  518MB  68.8MB/s   00:07    
control0000.bkp                                       100%   30MB  81.1MB/s   00:00

Creating a Minimal Instance

On the destination server, create a minimal initialization parameter file:

[oracle@RAC2 ~]$ vi p_1400_07_24.ora
*.db_name=db21c
*.db_create_file_dest='/oracle21c/recoverdb'
Start the instance in NOMOUNT mode:

SQL> startup nomount pfile='/home/oracle/p_1400_07_24.ora';

Restoring the Control File

Restore the control file from the backup:

RMAN> restore controlfile from '/oracle21c/bkp_pdb/control0000.bkp';
Starting restore at 16-OCT-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4262 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle21c/recoverdb/DB21C/controlfile/o1_mf_jpo703jv_.ctl
Finished restore at 16-OCT-21
Mount the database:

RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed

At this point, the metadata required to locate the PDB backup is available.

Restoring the PDB

Restore the PDB datafiles:

RMAN> RESTORE PLUGGABLE DATABASE RMANPDB;
Starting restore at 16-OCT-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=571 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00067 to /oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_system_jpo62lhv_.dbf
channel ORA_DISK_1: restoring datafile 00068 to /oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_sysaux_jpo62lhz_.dbf
channel ORA_DISK_1: restoring datafile 00069 to /oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_undotbs1_jpo62lj1_.dbf
channel ORA_DISK_1: restoring datafile 00070 to /oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_tbs01_jpo66vjx_.dbf
channel ORA_DISK_1: reading from backup piece /oracle21c/bkp_pdb/4p0bo4mu_153_1_1
channel ORA_DISK_1: piece handle=/oracle21c/bkp_pdb/4p0bo4mu_153_1_1 tag=TAG20211016T052342
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 16-OCT-21

RMAN restores all datafiles belonging to the PDB.

It is important to understand that only the datafiles have been restored. The PDB itself is not yet usable because it is not associated with an operational CDB.

Identifying the Restored Datafiles

Determine the location of the restored datafiles:

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         4 RMANPDB                        MOUNTED
         5 PDB1                           MOUNTED
SQL> select name from v$datafile where con_id=4;

/oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_system_jpo72qsh_.dbf
/oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_sysaux_jpo72ppx_.dbf
/oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_undotbs1_jpo72s8q_.dbf
/oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_tbs01_jpo72ssg_.dbf

Generating the XML

To plug the restored PDB into another CDB, an XML file must be created.

Connect to an open CDB and use the DBMS_PDB.RECOVER procedure:

[oracle@RAC2 ~]$ srvctl start database -db db21c
[oracle@RAC2 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 16 13:23:36 2021
Version 21.3.0.0.0
BEGIN
   DBMS_PDB.RECOVER (
      pdb_descr_file    =>   '/home/oracle/RMANPDB.xml',
      pdb_name          =>   'RMANPDB',
      filenames         =>   '
/oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_system_jpo72qsh_.dbf,
/oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_sysaux_jpo72ppx_.dbf,
/oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_undotbs1_jpo72s8q_.dbf,
/oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_tbs01_jpo72ssg_.dbf
'
   );
END;
/
PL/SQL procedure successfully completed.

The procedure creates an XML manifest describing the PDB.

A portion of the generated file looks like this:

[oracle@RAC2 ~]$ less /home/oracle/RMANPDB.xml
<?xml version="1.0" encoding="UTF-8"?>
<PDB>
  <xmlversion>1</xmlversion>
  <pdbname>RMANPDB</pdbname>
  <cid>4</cid>
  <byteorder>0</byteorder>
  <vsn>0</vsn>
  <vsns>
    <vsnnum>0.0.0.0.0</vsnnum>
    <cdbcompt>0.0.0.0.0</cdbcompt>
    <pdbcompt>21.0.0.0.0</pdbcompt>
    <vsnlibnum>0.0.0.0.0</vsnlibnum>
    <vsnsql>0</vsnsql>
    <vsnbsv>0.0.0.0.0</vsnbsv>
  </vsns>
  <dbid>3948388369</dbid>
  <ncdb2pdb>0</ncdb2pdb>
  <cdbid>246486353</cdbid>
  <guid>CE75C2880CCD2A3FE0530488200A29C8</guid>

Plugging the PDB into a New CDB

Create the PDB using the XML :

SQL> CREATE PLUGGABLE DATABASE RMANPDB USING '/home/oracle/RMANPDB.xml' NOCOPY;
Pluggable database created.

Verify that the PDB has been created:

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 RMANPDB                        MOUNTED

Opening the PDB

The PDB may initially open in migration mode and generate warnings.

SQL> ALTER DATABASE UPGRADE SYNC OFF;
Database altered.
SQL>  alter pluggable database RMANPDB open ;
Warning: PDB altered with errors.
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 RMANPDB               READ WRITE YES

Validating the Recovery

Connect to the restored PDB and verify that application objects are accessible:

[oracle@RAC2 ~]$ sqlplus "usef/a@192.168.1.20:1521/RMANPDB"
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> select count(*) from usef.tbl1;
  COUNT(*)
----------
        91
SQL> create table usef.tbl2 as select * from tbl1;
Table created.

Oracle Data Guard 26ai: Restricting Switchover and Failover Targets with PrimaryDatabaseCandidates

Vahid Yousefzadeh — Tue, 09 Jun 2026 16:04:29 +0000

Oracle AI Database 26ai introduces a useful enhancement for Data Guard environments that contain multiple standby databases. The new PrimaryDatabaseCandidates Data Guard Broker property allows administrators to explicitly define which databases are eligible to assume the primary role during switchover or failover operations.

This feature is particularly valuable in environments where some standby databases are dedicated to disaster recovery while others are used for reporting, testing, cloning, or other non-production purposes.

*The PrimaryDatabaseCandidates property is available starting with Oracle Database 19.26 Release Update (RU).

Test Environment

For this test, I configured a Data Guard environment consisting of one primary database and two physical standby databases.

DGMGRL> show configuration

Configuration - DB2

  Protection Mode: MaxPerformance
  Members:
  DB2 - Primary database
    dg1 - Physical standby database
    dg2 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

In this configuration:

DB2 is the primary database.
dg1 is intended to be the only standby eligible to become primary.
dg2 should remain a standby database and must not participate in switchover or failover operations.

Configuring PrimaryDatabaseCandidates

To restrict role transitions, I configured the PrimaryDatabaseCandidates property as follows:

DGMGRL> edit configuration set property PrimaryDatabaseCandidates='dg1';
Warning: ORA-16588: The primary database was added to property PrimaryDatabaseCandidates
Property "primarydatabasecandidates" updated

Attempting a Switchover to a Restricted Standby

Next, I attempted to perform a switchover to dg2, which is not included in the candidate list.

DGMGRL> switchover to dg2;
2026-02-25T15:42:39.854+03:30
Performing switchover NOW, please wait...
Error: ORA-16529: Switchover and failover operations are restricted to databases specified in the PrimaryDatabaseCandidates configuration property.
Failed.
2026-02-25T15:42:39.859+03:30
Unable to switchover, primary database is still "DB2"

As expected, Data Guard Broker blocked the operation and returned:

Error: ORA-16529: Switchover and failover operations are restricted to databases specified in the PrimaryDatabaseCandidates configuration property.

The primary database remained unchanged, and the switchover was not performed.

26ai- Using Tags in Oracle Data Guard Broker

Vahid Yousefzadeh — Fri, 29 May 2026 13:43:47 +0000

Oracle AI Database 26ai adds tagging support to Oracle Data Guard Broker, allowing administrators to attach custom metadata to configuration members.
With tags, you can attach your own information to Data Guard members. Think of it as adding labels to databases so they become easier to identify later.

For example, you may want to store:

Deployment location
Environment type
Build date
Owner team
Compliance information

Before using tags, the feature must be enabled.

DGMGRL> EDIT CONFIGURATION SET PROPERTY TagMode=1;
Property "tagmode" updated

In this demo, I added two tags to the member dg1.

The first tag stores the location of the database:

DGMGRL>  EDIT MEMBER dg1 SET TAG location='BABOL';
Tag "location" updated in "dg1".

The second tag stores a configuration date:

DGMGRL>  EDIT MEMBER dg1 SET TAG configuation_date='2026-02-25';
Tag "configuation_date" updated in "dg1".

Now we can query V$DG_BROKER_TAG and verify the tags:

SQL> select member,name,value from V$DG_BROKER_TAG;

MEMBER  NAME                VALUE
------- ------------------- --------------------
dg1     location            BABOL
dg1     configuation_date   2026-02-25

Automatic temp file creation on the standby - Oracle AI Database 26ai

Vahid Yousefzadeh — Thu, 26 Feb 2026 06:35:29 +0000

In Oracle Database 19c, when a tempfile is added on the primary database, it is not automatically created on the physical standby database. This is because Oracle does not generate redo for tempfile-related DDL operations (such as creating, adding, resizing, or dropping tempfiles).

Behavior in Oracle Database 19c

For example, when creating a temporary tablespace on the primary database(19c):

— Primary

SQL> create temporary tablespace TEMP1404;
Tablespace created.

SQL> select ts# from v$tablespace where NAME='TEMP1404';
       TS#
----------
        15

SQL> select name from v$tempfile where ts#=15;
NAME
--------------------------------------------------------------------------------
+DATA/HUME/B3AE49A3735D8867E053088A210A341D/TEMPFILE/temp1404.807.1225812657

On the standby database, although the tablespace metadata exists, the tempfile itself is not created:

— Data Guard

SQL> select ts# from v$tablespace where NAME='TEMP1404';
       TS#
----------
        15

SQL> select name from v$tempfile where ts#=15;
no rows selected

If we add another tempfile on the primary:

— Primary

SQL> alter tablespace TEMP1404 add tempfile size 100m;
Tablespace altered.

SQL> select name from v$tempfile where ts#=15;
NAME
--------------------------------------------------------------------------------
+DATA/HUME/B3AE49A3735D8867E053088A210A341D/TEMPFILE/temp1404.807.1225812657
+DATA/HUME/B3AE49A3735D8867E053088A210A341D/TEMPFILE/temp1404.808.1225812823

Again, on the standby database:

— Data Guard

SQL> select name from v$tempfile where ts#=15;
no rows selected

Automatic Tempfile Creation in Oracle AI Database 26ai

Starting with Oracle AI Database 26ai, tempfile creation is automatically handled on the standby database.

To enable this feature, the following parameters must be configured on the standby:

standby_file_management = AUTO
db_create_file_dest must be defined

— Data Guard

SQL> alter system set standby_file_management=AUTO;
System altered.

SQL> alter system set db_create_file_dest='/oracle/base/oradata';
System altered.

Now, when creating a temporary tablespace on the primary:

— Primary

SQL> create temporary tablespace TEMP1404;
Tablespace created.

SQL> select ts# from v$tablespace where NAME='TEMP1404';
       TS#
----------
         5

SQL> select name from v$tempfile where ts#=5;
NAME
--------------------------------------------------------------------------------
/oracle/base/oradata/DB2/4A3B7527123A1205E0630288200AA7B8/datafile/o1_mf_temp1404_nsx3wdt7_.tmp

The tempfile is automatically created on the standby database:

— Data Guard

SQL> select name from v$tempfile where ts#=5;
NAME
--------------------------------------------------------------------------------
/oracle/base/oradata/DG1/4A3B7527123A1205E0630288200AA7B8/datafile/o1_mf_temp1404_nsx3wbyx_.dbf

This enhancement eliminates the need for manual synchronization of tempfile configurations between primary and standby databases, simplifying Data Guard administration.

Managing Alert Log Size in Oracle AI Database 26ai

Vahid Yousefzadeh — Mon, 23 Feb 2026 10:32:09 +0000

Starting with Oracle AI Database 26ai (Release Update 23.9), it is possible to control the maximum size of the alert log using the ALERT_LOG_MAX_SIZE parameter. The default value for this parameter is 1000 MB, which means the XML alert log can grow up to a maximum of 20 segments, each 50 MB in size.

In my test, I configured the alert log maximum size to 100 MB:

SQL> ALTER SYSTEM SET alert_log_max_size = 100M;
System altered.

***This parameter is modifiable using ALTER SYSTEM, but it is not modifiable at the PDB level.

To increase the size of the alert log and force file generation, I executed the following PL/SQL block to continuously write messages into the alert log:

begin
  for i in 1 .. 1000000 loop
    begin
      sys.dbms_system.ksdwrt(2, 'Vahid Yousefzadeh');
    end;
  end loop;
end;
/

After running this block, there are several XML files in the alert directory and each file size is approximately 50MB.

[root@OEL9-DB2 alert]# ls -lh
-rw-r----- 1 oracle oinstall 51M Feb 23 11:57 log_20.xml
-rw-r----- 1 oracle oinstall 51M Feb 23 11:57 log_21.xml
-rw-r----- 1 oracle oinstall 16M Feb 23 11:57 log.xml

In addition, I checked the trace directory and observed multiple alert text log files:

[root@OEL9-DB2 trace]# ls -lh alert*.log
-rw-r----- 1 oracle oinstall 4.1M Feb 23 11:57 alert_db26ai_20.log
-rw-r----- 1 oracle oinstall 4.1M Feb 23 11:57 alert_db26ai_21.log
-rw-r----- 1 oracle oinstall 1.3M Feb 23 11:57 alert_db26ai.log

This behavior indicates that the size and number of alert text log files are driven by the XML log files.

Controlling XML Segment Size

There is also a hidden parameter called: _alert_segment_size

This parameter allows us to control the size of each XML alert log segment.

SQL> ALTER SYSTEM SET "_alert_segment_size" = 65;
System altered.

SQL> ALTER SYSTEM SET alert_log_max_size = 100M;
System altered.

After generating alert activity again, the results were:

Alert (XML) directory:

[root@OEL9-DB2 alert]# ll
-rw-r----- 1 oracle oinstall 68157489 Feb 23 12:05 log_25.xml
-rw-r----- 1 oracle oinstall 68157489 Feb 23 12:05 log_26.xml
-rw-r----- 1 oracle oinstall  2656020 Feb 23 12:05 log.xml

Trace directory:

[root@OEL9-DB2 trace]# ls -lh alert*.log
-rw-r----- 1 oracle oinstall 5.3M Feb 23 12:05 alert_db26ai_25.log
-rw-r----- 1 oracle oinstall 5.3M Feb 23 12:05 alert_db26ai_26.log
-rw-r----- 1 oracle oinstall 211K Feb 23 12:05 alert_db26ai.log

As shown above, each XML segment is now approximately 66 MB, which reflects the new _alert_segment_size value.

Disabling the Alert Log Size Limit

If we want to return to unlimited alert log growth, we can set the parameter to 0:

SQL> ALTER SYSTEM SET alert_log_max_size = 0;
System altered.

Excluding LOB Columns Data in Data Pump

Vahid Yousefzadeh — Fri, 20 Feb 2026 02:17:58 +0000

We intend to create a dump from a table in which some columns contain LOB data. However, due to the large volume of LOB data in this table — and for reasons such as higher speed, better security, or reduced space allocation — we want to prevent this data from being included in the dump file, or at least prevent it from being restored during the import process.

What is the solution?

Excluding LOB column data during the export operation in Oracle 12c can be easily controlled. This can be done by creating a view and using the VIEWS_AS_TABLES parameter. However, if the dump file already contains LOB data, this method cannot be used during import. In such cases, the REMAP_DATA parameter is the solution.

Previously, we explained that the REMAP_DATA parameter can be used for data masking and data manipulation during export and import operations. In this article, we will see how the same parameter can be used to exclude LOB column data during both export and import processes.

Let’s look at the following example.

Example
We want to exclude the data stored in the ADDRESS and RESUME columns of the PERSON table during expdp.

SQL> desc person

Name           Type       

——-  ————————

ID              NUMBER     

NAME       VARCHAR2(10)

ADDRESS   CLOB       

RESUME    CLOB    


SQL> select * from person;

ID    NAME       ADDRESS      RESUME

—- ———- ———-  ———–

1      usef      LOB_DATA    LOB_DATA

To achieve this, we create a package that returns NULL for each field in these two columns:

SQL> create or replace package without_CLOB as

  2   function null_clob (soton_clob in clob) return clob;

  3   end;

  4  /

Package created


SQL> create or replace package body without_CLOB as

  2   function null_clob (soton_clob in clob) return clob

  3   is

  4    nulllll   clob := empty_clob ();

  5  begin

  6      return  nulllll;

  7   end;

  8  end;

  9  /

Package body created

After creating the WITHOUT_CLOB package, we generate a dump from the PERSON table using the REMAP_DATA parameter:

[oracle@ol7 ~]$ expdp directory=tk dumpfile=dmp_without_LOB tables=usef.person REMAP_DATA=usef.person.ADDRESS:usef.without_CLOB.null_clob REMAP_DATA=usef.person.RESUME:usef.without_CLOB.null_clob

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Starting “SYS”.”SYS_EXPORT_TABLE_01″:  sys/******** AS SYSDBA directory=tk dumpfile=dmp_without_LOB tables=usef.person REMAP_DATA=usef.person.ADDRESS:usef.without_CLOB.null_clob REMAP_DATA=usef.person.RESUME:usef.without_CLOB.null_clob

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported “USEF”.”PERSON”                             6.398 KB       1 rows

Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  /home/oracle/dmp_without_LOB.dmp

Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon Jul 6 07:38:24 2020 elapsed 0 00:00:42

The export completes successfully, and the dump file is created.

Now, when we import the table again:

[oracle@ol7 ~]$ impdp directory=tk dumpfile=dmp_without_LOB tables=usef.person remap_table=person:person2

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported “USEF”.”PERSON2″                            6.398 KB       1 rows


SQL> select ADDRESS from usef.person2;

No Rows Selected.


SQL> select * from usef.person2;

        ID NAME       ADDRESS    RESUME

———- ———- ———- ———-

         1 usef

As shown, the ADDRESS and RESUME columns are empty.

Excluding LOB columns during the impdp operation is also possible.

Restoring a Datafile Using Archivelogs

Vahid Yousefzadeh — Tue, 10 Feb 2026 16:48:12 +0000

Suppose a datafile that was recently added to the database is lost for some reasons. In this case, if the archive logs from the time the datafile was created are available, it is possible to recover that datafile.

However, as mentioned, this operation has a very strict requirement:

“All archive logs from the time the datafile was created must be available.”
Below, we will walk through a scenario related to this topic.

SQL> archive log list;

Database log mode              Archive Mode

SQL> alter tablespace USEF_TBS add datafile ‘/acfs/test2.dbf’ size 10m;

With the following commands, we create a new table that uses space from the new datafile so that after recovery we can verify that no data was lost.

SQL> create table usef_tbl1  tablespace usef_tbs as select * from dba_tables where 1=2;

SQL>  alter  table usef_tbl1 ALLOCATE EXTENT(DATAFILE ‘/acfs/test2.dbf’  size 5m);

Table altered.

SQL> insert into usef_tbl1 select * from dba_tables;

2323 rows created.

SQL> commit;

SQL> select count(*) from usef_tbl1;

      2323

At this stage, we delete the datafile and restart the database.

rm –rf   /acfs/test2.dbf

SQL> startup

ORACLE instance started.

Total System Global Area 8351150080 bytes

Fixed Size                  2701528 bytes

Variable Size            7348422440 bytes

Database Buffers          989855744 bytes

Redo Buffers               10170368 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 – see DBWR trace file

ORA-01110: data file 6: ‘/acfs/test2.dbf’

SQL> alter database datafile  ‘/acfs/test2.dbf’  offline;

Database altered.

SQL> alter database open;

Database altered

As the following command shows, the created table is not accessible.

SQL> select count(*) from usef_tbl1;

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: ORA-01110: data file 6: ‘/acfs/test2.dbf’

SQL> alter database create datafile ‘/acfs/test2.dbf’  ;

Database altered.
The recovery is performed with the following command:

SQL> recover datafile  ‘/acfs/test2.dbf‘;

Media recovery complete.

SQL> alter database datafile  ‘/acfs/test2.dbf‘ ONLINE;

Database altered.

SQL> select count(*) from usef_tbl1;

      2323

Oracle AI Database 26ai — Managing AWR in Active Data Guard

Vahid Yousefzadeh — Mon, 09 Feb 2026 14:58:41 +0000

Starting with Oracle Database 12.2, generating AWR reports on an Active Data Guard (ADG) became possible by using the Remote Management Framework (RMF). In this model, AWR data had to be manually transported from the standby to a primary database. Although functional, the setup was complex and required multiple configuration steps, making AWR analysis on physical standby databases operationally challenging for DBAs.

With Oracle AI Database 26ai, AWR support for Active Data Guard Standby databases is fully integrated and enabled by default. AWR snapshots are automatically collected on the ADG standby and transparently transported to the primary database, without any manual intervention or RMF configuration. By default, snapshots are taken once per hour, and the retention period is 8 days.

This enhancement allows DBAs to work with AWR on an Active Data Guard standby in the same way as on the primary database.

AWR Architecture for ADG in 26ai

Each Active Data Guard standby is automatically assigned a unique AWR ID.
This AWR ID is stored in the DBID column of the AWR tables and is used to distinguish AWR data coming from different databases.

The AWR ID of the current database can be obtained using:

— ADG Standby:

SQL> select dbms_workload_repository.get_awr_id() awr_id;
 AWR_ID
----------
3586104896

Although AWR data is physically stored in the primary database, it becomes visible and usable on the standby after redo apply.

Generating an AWR Report on ADG Standby

An AWR report can be generated directly on the Active Data Guard standby using the standard awrrpt.sql script. During report generation, Oracle automatically lists all available AWR repositories and prompts for the appropriate DBID, including the standby AWR ID.

SQL> @?/rdbms/admin/awrrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.
'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type:
Type Specified:  html


Current Instance
~~~~~~~~~~~~~~~~
DB Id          DB Name        Inst Num       Instance       Container Name
-------------- -------------- -------------- -------------- --------------
 4152038052     DB26AI                      1 DG1            CDB$ROOT


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  3586104896     1      DB26AI       DG1          DG1
  4152038052     1      DB26AI       db26ai       OEL9-DB2


Using 3586104896 for database Id
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 <return> without
specifying a number lists all completed snapshots.
Enter value for num_days:
Listing all Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------
DG1          DB26AI               1  09 Feb 2026 10:30     1
                                  2  09 Feb 2026 10:40     1

A portion of the AWR report output is shown below.

Viewing AWR Settings for ADG Standby

Each ADG standby can have its own AWR snapshot settings.
These settings are visible in the AWR_CDB_WR_CONTROL view on the primary database.

Example:

SQL> select c.dbid,c.snap_interval,c.retention,c.topnsql,c.tablespace_name from awr_cdb_wr_control c where src_dbname='DG1';
      DBID SNAP_INTERVAL        RETENTION            TOPNSQL    TABLESPACE_NAME
---------- -------------------- -------------------- ---------- ------------------------------
3586104896 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT    SYSAUX

This confirms the default 1-hour snapshot interval and 8-day retention

Modifying Snapshot Settings on ADG Standby

Snapshot settings can be modified directly on the physical standby.

For example, to change the snapshot interval from 60 minutes to 30 minutes:

— ADG Standby:

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 30);
PL/SQL procedure successfully completed.

SQL>  select c.dbid,c.snap_interval,c.retention,c.topnsql,c.tablespace_name from awr_cdb_wr_control c where src_dbname='DG1';
      DBID SNAP_INTERVAL        RETENTION            TOPNSQL    TABLESPACE_NAME
---------- -------------------- -------------------- ---------- ------------------------------
3586104896 +00000 00:30:00.0    +00008 00:00:00.0    DEFAULT    SYSAUX

Modifying AWR Retention from the Primary Database

AWR settings for an ADG standby can also be modified from the primary database by specifying the standby’s AWR ID.

For example, to change the retention period from 8 days to 60 days:

— Primary Database

SQL> exec dbms_workload_repository.modify_snapshot_settings(dbid => 3586104896,retention => 86400);
PL/SQL procedure successfully completed

After redo apply, the updated settings are visible on the standby:

— ADG Standby

SQL>  select c.dbid,c.snap_interval,c.retention,c.topnsql,c.tablespace_name from awr_cdb_wr_control c where src_dbname='DG1';
      DBID SNAP_INTERVAL        RETENTION            TOPNSQL    TABLESPACE_NAME
---------- -------------------- -------------------- ---------- ------------------------------
3586104896 +00000 00:30:00.0    +00060 00:00:00.0    DEFAULT    SYSAUX

Creating Manual AWR Snapshots on ADG Standby

In addition to automatic snapshots, manual AWR snapshots can be created on an Active Data Guard standby, just like on a primary database:

— ADG Standby

SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.

Generating AWR Reports in an Active Data Guard using RMF(12.2)

Vahid Yousefzadeh — Mon, 09 Feb 2026 14:53:14 +0000

As you know, generating AWR reports in an Active Data Guard (ADG) environment is not normally possible. This is logically due to the inability to create snapshots on a database that is in read-only mode. In Oracle 12c Release 2, a solution was introduced, and it became possible to generate AWR reports for ADG databases.

In this new feature, a snapshot is first created and workload data from the Data Guard database is stored in a database that is in read-write mode (there is no mandatory requirement to use the primary database for this). After a defined time interval, a second snapshot is created, and the data collection is completed. Finally, the AWR report can be generated. In the following sections, we explain how to perform this process.

This capability (remote snapshot) uses the Remote Management Framework (RMF). Before generating the AWR report, several steps are required to create and configure the RMF topology. These steps, along with the other required procedures, are described below.

Step 1: Check the Status of the SYS$UMF User

SQL> select username,account_status from dba_users where username=‘SYS$UMF’;

USERNAME ACCOUNT_STATUS
SYS$UMF EXPIRED & LOCKED

As shown, this user is expired and locked by default. In the first step, it must be opened:

SQL> alter user sys$umf identified by a account unlock;

User altered

Step 2: Create Two-Way Database Links Between Primary and ADG

For this test, the primary database is used as the source (read-write database).

First, configure the tnsnames.ora file on both the primary and ADG databases:

ADG=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = ADG_HOST)(PORT = 1521))

    )

    (CONNECT_DATA =

      (sid=ADG)

    )

  )

PRIM=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = PRIM_HOST)(PORT = 1521))

    )

    (CONNECT_DATA =

      (sid=PRIM)

    )

  )

After configuring tnsnames.ora, create the database links:

–PRIMARY Database

SQL> create database link PRIM_TO_ADG CONNECT TO sys$umf IDENTIFIED BY root using ‘ADG’;

Database link created.

SQL> create database link ADG_TO_PRIM CONNECT TO sys$umf IDENTIFIED BY root using ‘PRIM’;

Database link created.

Test the database links:

SQL>  select database_role from v$database@PRIM_TO_ADG;

DATABASE_ROLE

—————-

PHYSICAL STANDBY

SQL> select database_role from v$database@ADG_TO_PRIM;

DATABASE_ROLE

—————-

PRIMARY

Step 3: Configure RMF Nodes

Before creating the RMF topology, each database must be assigned a unique node name.

— primary database:

SQL> exec dbms_umf.configure_node (‘PRIM_NODE’);

PL/SQL procedure successfully completed.
**— ADG Standby:**

SQL> exec dbms_umf.configure_node(‘ADG_NODE’,’ADG_TO_PRIM’);

PL/SQL procedure successfully completed.

Step 4: Create the RMF Topology

— primary database:

SQL> exec DBMS_UMF.create_topology (‘TOPOL1’);

PL/SQL procedure successfully completed.

To view the list of topologies:

select * from dba_umf_topology;

TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY_STATE
TOPOL1 3967923579 1 ACTIVE

Step 5: Register the Standby Node in the Topology

SQL> exec DBMS_UMF.register_node (‘TOPOL1’, ‘ADG_NODE’, ‘PRIM_TO_ADG’, ‘ADG_TO_PRIM’, ‘FALSE’, ‘FALSE’);

PL/SQL procedure successfully completed.

Step 6: Enable the AWR Service for Data Guard

SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>’ADG_NODE’);

PL/SQL procedure successfully completed.
Verify the topology status:

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SOURCE AS_CANDIDATE_TARGET STATE
TOPOL1 PRIM_NODE 3967923579 0 FALSE FALSE OK
TOPOL1 ADG_NODE 1195199270 0 FALSE FALSE OK

SQL> select * from dba_umf_service;

TOPOLOGY_NAME NODE_ID SERVICE_ID
TOPOL1 1195199270 AWR

SQL> select * from dba_umf_link;

TOPOLOGY_NAME FROM_NODE_ID TO_NODE_ID LINK_NAME
TOPOL1 3967923579 1195199270 PRIM_TO_ADG
TOPOL1 1195199270 3967923579 ADG_TO_PRIM

Step 7: Create Remote Snapshots from ADG

SQL> exec dbms_workload_repository.create_remote_snapshot(‘ADG_NODE’);

PL/SQL procedure successfully completed.

The first snapshot for ADG is now created in the primary environment. After generating workload on ADG and waiting for a suitable interval, create the second snapshot:

SQL> exec dbms_workload_repository.create_remote_snapshot(‘ADG_NODE’)

PL/SQL procedure successfully completed.

Step 8: Generate the AWR Report

SQL> @?/rdbms/admin/awrrpti.sql

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

AWR reports can be generated in the following formats.  Please enter the

name of the format at the prompt. Default value is ‘html’.

   ‘html’          HTML format (default)

   ‘text’          Text format

   ‘active-html’   Includes Performance Hub active report

Enter value for report_type:

Type Specified: html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  DB Id      Inst Num   DB Name      Instance     Host

———— ———- ———    ———-   ——

  1195199270     1      ORCL          ADG         ADG_HOST

* 1112303074     1      ORCL          PRIM        PRIM_HOST

Enter value for dbid: 1195199270

Using 1195199270 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 <return> 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

———— ———— ———- —————— ———-

ADG           ORCL                1  30 Sep 2018 15:47    1

                                  2  30 Sep 2018 15:55    1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1

Enter value for end_snap: 2

End   Snapshot Id specified: 2

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_1_2.html.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/awrstb970708.html

Using the report name /home/oracle/awrstb970708.html

A portion of the AWR report output is shown below.