How To Move Data Guard Broker Configuration Files Onto ASM in 19c

By default, the broker configuration files of Oracle Data Guard are created onto local filesystem. In GI environment, we’d like those files are located in ASM filesystem.

Check Current Configurations

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/dr1TESTDB.dat
dg_broker_config_file2               string      /u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/dr2TESTDB.dat

Stop the Broker

SQL> alter system set dg_broker_start = FALSE;

Set the dg_broker_config_file1 & 2 parameters to the appropriate location required

SQL> alter system set dg_broker_config_file1 = '+DATAC1/TESTDB/dr1TESTDB.dat';
SQL> alter system set dg_broker_config_file2 = '+DATAC1/TESTDB/dr2TESTDB.dat';

Restart the Broker

SQL> alter system set dg_broker_start = TRUE

Finally, the broker configuration files will be created in the new ASM location.

ASMCMD> pwd
+DATAC1/TESTDB
ASMCMD> ls -l dr*
Type             Redund  Striped  Time             Sys  Name
DATAGUARDCONFIG  HIGH    COARSE   JAN 13 11:00:00  N    dr1testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.979.1126004761
DATAGUARDCONFIG  HIGH    COARSE   JAN 13 11:00:00  N    dr2testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.980.1126004761
ASMCMD>

Process RSM0, PID = 23456, will be killed

Standby database DG log:

11/09/2022 11:53:34
Creating process RSM0
11/09/2022 11:53:37
Process RSM0 re-created with PID = 20918
11/09/2022 11:59:37
Process RSM0, PID = 20918, will be killed
11/09/2022 11:59:51

Primary database DG log:

Data Guard Broker Status Summary:
Type Name Severity Status
Configuration DG_CDRDB Warning ORA-16607
Primary Database PCDRDB Error ORA-16778
Physical Standby database SCDRDB Error ORA-16810

SOLUTION

  1. Check and resolve network issues between primary db server and standby server.
  2. Increase the OperationTimeout property value :
DGMGRL> show configuration OperationTimeout;
  OperationTimeout = '30'
 
DGMGRL> EDIT CONFIGURATION SET PROPERTY OperationTimeout=90;
Property "operationtimeout" updated

DGMGRL>  show configuration OperationTimeout;
  OperationTimeout = '90'

Site StandbyDb returned ORA-16664

The ORA-16664 occur4es in Data Guard log files as following:

...
..
.
Site StandbyDb returned ORA-16664.
...
..
.

Solution

a) Check both alert and data guard logs of primary database

b) Check both alert and data guard logs of standby database with following errors:

ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925

...
..

.
OS Audit file could not be created; failing after 6 retries

...

After cleaned the old audit files, then everything is fine.

DGMGRL> show database verbose "STANDBYDB";

...
..
.
  Role:                    PHYSICAL STANDBY
  Intended State:          APPLY-ON
  Transport Lag:           0 seconds (computed 0 seconds ago)
  Apply Lag:               0 seconds (computed 1 second ago)
  Average Apply Rate:      4.38 MByte/s
  Active Apply Rate:       1.11 MByte/s
  Maximum Apply Rate:      46.39 MByte/s
..
..
.
Database Status:
SUCCESS

DGMGRL>

ORA-00308: cannot open archived log

Standby database recovery stopped with the following errors:

Wed Aug 17 10:43:35 2022
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 8 slaves
Waiting for all non-current ORLs to be archived…
All non-current ORLs have been archived.
Media Recovery Log /fra/PRDB/archivelog/2022_08_11/o1_mf_1_68911_kh8kkwp0_.arc
Error opening /fra/PRDB/archivelog/2022_08_11/o1_mf_1_68911_kh8kkwp0_.arc
Attempting refetch
MRP0: Background Media Recovery terminated with error 308
Errors in file /u01/app/oracle/diag/rdbms/prdb/PRDB/trace/PRDB_pr00_17264.trc:
ORA-00308: cannot open archived log '/fra/PRDB/archivelog/2022_08_11/o1_mf_1_68911_kh8kkwp0_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
MRP0: Background Media Recovery process shutdown (PRDB)

Check the missing archive log does exist:

$ ls -ltr /fra/PRDB/archivelog/2022_08_11/o1_mf_1_68911_*_.arc
-rw-r-----. 1 oracle oinstall 910116352 Aug 11 09:58 /fra/PRDB/archivelog/2022_08_11/o1_mf_1_68911_kh8kjvh9_.arc

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

ORA-10485: Real-Time Query cannot be enabled while applying migration redo

After upgraded databases using DataGuard rolling method, then standby database reports ORA-10485 error:

ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Recovery interrupted!
stopping change tracking
2022-04-29T21:52:31.513013+10:00
Errors in file /u01/app/oracle/diag/rdbms/stboemrep/STBOEMREP/trace/STBOEMREP_mrp0_23330.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
2022-04-29T21:52:31.513045+10:00
Background Media Recovery process shutdown (STBOEMREP)

Oracle support explains this ORA error:

$ oerr ora 10485
10485, 00000, "Real-Time Query cannot be enabled while applying migration redo."
// *Cause:  The Real-Time Query feature was enabled when an attempt was made
//          to recover through migration redo generated during primary upgrades or
//          downgrades.
// *Action: Close the standby database in order to recover through
//          migration redo. Reenable the Real-Time Query feature afterwards.

CAUSE

Real-time query has been ON when upgrading the primary database.

SOLUTION

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 3221223152 bytes
Fixed Size                  9139952 bytes
Variable Size             687865856 bytes
Database Buffers         2516582400 bytes
Redo Buffers                7634944 bytes
Database mounted.

SQL> alter database recover managed standby database disconnect from session;

Database altered.


SQL> alter database recover managed standby database cancel;

Database altered.


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

Database altered.

SQL> alter database open;

Database altered.

DGMGRL>  show configuration;

Configuration - dg_oemrep

  Protection Mode: MaxPerformance
  Members:
  oemrep    - Primary database
    stboemrep - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 7 seconds ago)

DGMGRL> show database verbose "stboemrep";

Database - stboemrep

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 12.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    ON
  Instance(s):
    STBOEMREP

  Properties:
    DGConnectIdentifier             = 'stboemrep'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'stbnode1.virtuallab'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=stbnode1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBOEMREP_DGMGRL)(INSTANCE_NAME=STBOEMREP)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/stboemrep/STBOEMREP/trace/alert_STBOEMREP.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/stboemrep/STBOEMREP/trace/drcSTBOEMREP.log

Database Status:
SUCCESS

DGMGRL>