Oracle Data Guard Most Used Commands

Note: Static “_DGMGRL” entries are no longer needed as of Oracle Database 12.1.0.2 and later release in Oracle Data Guard Broker configurations that are managed by Oracle Restart, RAC On Node or RAC as the Broker will use the clusterware to restart an instance. ( Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

DGMGRL> connect sys@testdb
Password:
Connected to "TESTDB"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - dg_testdb

  Protection Mode: MaxPerformance
  Members:
  testdb  - Primary database
    stestdb - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

Switchover

DGMGRL> switchover to stestdb;
Performing switchover NOW, please wait...
New primary database "stestdb" is opening...
Oracle Clusterware is restarting database "testdb" ...
Connected to "TESTDB"
Switchover succeeded, new primary is "stestdb"

-- After switchover, run 'srvctl config database -d", the database role has been changed by Data Guard Broker automatically.

$ srvctl config database -d STESTDB
Database unique name: STESTDB
Database name: testdb
.
Start options: open
Stop options: immediate
Database role: PRIMARY
...
..
.

$  srvctl config database -d testdb
Database unique name: TESTDB
Database name: TESTDB
.
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY

Show Lag

DGMGRL>  SHOW CONFIGURATION  lag

Configuration - dg_testdb

  Protection Mode: MaxPerformance
  Members:
  testdb  - Primary database
    stestdb - Physical standby database
              Transport Lag:      0 seconds (computed 0 seconds ago)
              Apply Lag:          2 seconds (computed 0 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 28 seconds ago)

DGMGRL> show database "stestdb";

Database - stestdb

  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: 322.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    TESTDB1
    TESTDB2 (apply instance)

Database Status:
SUCCESS

TRANSPORT-ON / TRANSPORT-OFF

DGMGRL>  show database "testdb";

Database - testdb

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

Database Status:
SUCCESS

DGMGRL> EDIT DATABASE testdb SET STATE='TRANSPORT-OFF';
Succeeded.
DGMGRL>  show database "testdb";

Database - testdb

  Role:               PRIMARY
  Intended State:     TRANSPORT-OFF
  Instance(s):
    TESTDB1
    TESTDB2

Database Status:
SUCCESS

APPLY-ON / APPLY-OFF

DGMGRL>  show database "stestdb";

Database - stestdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 70 seconds ago)
  Apply Lag:          0 seconds (computed 70 seconds ago)
  Average Apply Rate: 114.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    TESTDB1
    TESTDB2 (apply instance)

Database Status:
SUCCESS

DGMGRL> EDIT DATABASE stestdb SET STATE='APPLY-OFF';
Succeeded.
DGMGRL>   show database "stestdb";

Database - stestdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-OFF
  Transport Lag:      0 seconds (computed 215 seconds ago)
  Apply Lag:          0 seconds (computed 215 seconds ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    TESTDB1
    TESTDB2 (apply instance)

Database Status:
SUCCESS

Change apply instance

DGMGRL> edit database  TESTDB set state='apply-off';
Succeeded.

DGMGRL> edit database TESTDB set state='apply-on' with apply instance ='TESTDB1';

Succeeded.

Monitorable (Read-Only) Properties

DGMGRL>   show database verbose testdb;
...
..
.
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
...
..
.

DGMGRL> show database testdb 'InconsistentLogXptProps';
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE

Configurable (Changeable) Properties

DGMGRL>  SHOW DATABASE testdb  LogXptMode;
  LogXptMode = 'ASYNC'

DGMGRL>  EDIT DATABASE testdb SET PROPERTY LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL> SHOW DATABASE testdb  LogXptMode;
  LogXptMode = 'SYNC'

Validate database spfile

DGMGRL> validate database  TESTDB  spfile;
This command cannot be used for the primary database.

DGMGRL>  validate database STESTDB  spfile;
...
..
.

processes:
testdb (PRIMARY) : 3000
stestdb          : 6400
...
..
.

Validate static connect identifier

DGMGRL> validate static connect identifier for all
Oracle Clusterware on database "testdb" is available for database restart.

Oracle Clusterware on database "stestdb" is available for database restart.

validate network configuration for all

DGMGRL> validate network configuration for all
Oracle Clusterware on database "testdb" is available for database restart.

Oracle Clusterware on database "stestdb" is available for database restart.

DGMGRL> validate network configuration for all
Connecting to instance "TESTDB2" on database "testdb" ...
Connected to "TESTDB"
Checking connectivity from instance "TESTDB2" on database "testdb" to instance "STESTDB2" on database "stestdb"...
Succeeded.
Checking connectivity from instance "TESTDB2" on database "testdb" to instance "STESTDB" on database "stestdb"...
Succeeded.
Connecting to instance "TESTDB1" on database "testdb" ...
Connected to "TESTDB"
Checking connectivity from instance "TESTDB1" on database "testdb" to instance "STESTDB2" on database "stestdb"...
Succeeded.
Checking connectivity from instance "TESTDB1" on database "testdb" to instance "STESTDB" on database "stestdb"...
Succeeded.
Connecting to instance "STESTDB2" on database "stestdb" ...
Connected to "STESTDB"
Checking connectivity from instance "STESTDB2" on database "stestdb" to instance "TESTDB2" on database "testdb"...
Succeeded.
Checking connectivity from instance "STESTDB2" on database "stestdb" to instance "TESTDB1" on database "testdb"...
Succeeded.
Connecting to instance "STESTDB" on database "stestdb" ...
Connected to "STESTDB"
Checking connectivity from instance "STESTDB" on database "stestdb" to instance "TESTDB2" on database "testdb"...
Succeeded.
Checking connectivity from instance "STESTDB" on database "stestdb" to instance "TESTDB1" on database "testdb"...
Succeeded.

Oracle Clusterware on database "testdb" is available for database restart.

Oracle Clusterware on database "stestdb" is available for database restart.

DGMGRL>

Validate primary and standby database

DGMGRL> validate database testdb

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    ttestdb:  Off

  Managed by Clusterware:
    testdb:  YES

DGMGRL>  validate database stestdb

  Database Role:     Physical standby database
  Primary Database:  testdb

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

  Flashback Database Status:
    testdb:  Off
    stestdb:  Off

  Managed by Clusterware:
    testdb:  YES
    stestdb:  YES

Oracle ASM Thin Provisioned Attribute and Reclaim Storage Space

ASMFD Installation & Configuration

Verify the ASM Filer Driver (AFD) Kernel module has been loaded.

$ lsmod | grep afd
oracleafd             226702  1

To confirm ASM Filter Driver has already been installed, loaded, is supported and versions, use afddriverstate installed | loaded | supported | version.

$ afddriverstate installed
AFD-9203: AFD device driver installed status: 'true'

$ afddriverstate loaded
AFD-9205: AFD device driver loaded status: 'true'

$ afddriverstate supported
AFD-9200: Supported

$ afddriverstate version
AFD-9325:     Driver OS kernel version = 3.10.0-862.el7.x86_64.
AFD-9326:     Driver build number = RELEASE.
AFD-9231:     Driver build version = 19.0.0.0.0 (19.24.0.0.0).
AFD-9547:     Driver available build number = RELEASE.
AFD-9232:     Driver available build version = 19.0.0.0.0 (19.24.0.0.0).

To check the ASMFD filtering status:

$ asmcmd afd_state
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'RACNODE1'

Check and list ASMFD disks.

$ asmcmd afd_lsdsk

Oracle ASM Thin Provisioned Attribute

The ASM Thin Provisioned attribute enables or disables the functionality to discard unused storage space after a diskgroup rebalance is completed.

Note: the ASM Thin Provisioned attribute only works with the ASM Filter Driver, setting it on non-ASMFD diskgroups has no effect.

The attribute value can be true to enable or false to disable the functionality. The default value is false.

Oracle ASM informs the storage array which space is no longer used and can be repurposed. If a rebalance is not running, a rebalance can be triggered manually with the ALTER DISKGROUP … REBALANCE SQL statement.

How to check ASM ‘thin_provisioned’ attribute ?

set linesize 250
 
column diskgroup heading 'Diskgroup' format a15
column name heading 'Attribute Name' format a25
column value heading 'Value' format a15
column read_only heading 'Read Only' format a15
 
SELECT
  SUBSTR(dg.name,1,12) AS diskgroup, SUBSTR(a.name,1,24) AS name,
  SUBSTR(a.value,1,24) AS value, read_only 
FROM
  V$ASM_DISKGROUP dg, 
  V$ASM_ATTRIBUTE a 
WHERE
  dg.group_number = a.group_number AND
  a.name NOT LIKE '%template%' AND
  a.name LIKE '%thin%'
/ 

Diskgroup       Attribute Name            Value           Read Only
--------------- ------------------------- --------------- ---------------
DATA2           thin_provisioned          true            N
FRA             thin_provisioned          FALSE           N
OCR             thin_provisioned          FALSE           N
REDO            thin_provisioned          true            N

$ asmcmd lsattr -G FRA | grep thin
thin_provisioned

We can easily change the ‘thin_provisioned’ attribute using ASMCA or using ‘alter disk group <DISK_GROUP> set attribute ‘thin_provisioned’=’TRUE’;’

SQL> alter diskgroup FRA set attribute 'thin_provisioned'='TRUE';
Diskgroup altered.

Since Oracle 18c the phase option includes the keywords WITH or WITHOUT, removing the need to use the underscore parameters, check your version documentation for limitations.

SQL> ALTER DISKGROUP DATA REBALANCE WITHOUT COMPACT;
Diskgroup altered.

SQL > alter diskgroup data2 rebalance with balance compact;
Diskgroup altered.

SQL> ALTER DISKGROUP DATA REBALANCE WITH BALANCE COMPACT POWER 1024 WAIT;
Diskgroup altered.

SQL>select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from gv$asm_operation;

INST_ID OPERA STAT POWER  SOFAR   EST_WORK   EST_RATE EST_MINUTES
------- ----- ---- ----- ------ ---------- ---------- -----------
      2 REBAL WAIT     1
      2 REBAL WAIT     1
      2 REBAL WAIT     1
      1 REBAL RUN      1   1951    8634894       2475        3488
      1 REBAL DONE     1   1017       1017          0           0
      1 REBAL DONE     1      0          0          0           0

6 rows selected.

How to Manually Apply OCW Release Update onto Database Home

Found OCW patch was not applied onto database home after applied 19.24 RU, so manual applying is required.

$ORACLE_HOME/OPatch/opatch lspatches
...
..
.
30159782;OCW Interim patch for 30159782

For 19.24, the OCW patch id should be “36587798;OCW RELEASE UPDATE 19.24.0.0.0 (36587798)”. Let’s manually apply it.

$ cd 36582629/36587798
$ /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.44
Copyright (c) 2024, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.44
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-11-26_11-01-26AM_1.log

Verifying environment and performing prerequisite checks...

--------------------------------------------------------------------------------
Start OOP by Prereq process.
Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.44
Copyright (c) 2024, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.44
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-11-26_11-01-58AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   36587798

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '36587798' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.has.crs, 19.0.0.0.0 ] , [ oracle.rhp.crs, 19.0.0.0.0 ] , [ oracle.xag, 19.0.0.0.0 ] , [ oracle.has.cvu, 19.0.0.0.0 ] , [ oracle.has.crs.cvu, 19.0.0.0.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.rhp.common, 19.0.0.0.0...

Patching component oracle.has.common, 19.0.0.0.0...

Patching component oracle.has.common.cvu, 19.0.0.0.0...

Patching component oracle.rhp.db, 19.0.0.0.0...

Patching component oracle.has.db, 19.0.0.0.0...

Patching component oracle.has.db.cvu, 19.0.0.0.0...

Patching component oracle.has.rsf, 19.0.0.0.0...
Patch 36587798 successfully applied.
Sub-set patch [30159782] has become inactive due to the application of a super-set patch [36587798].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-11-26_11-01-58AM_1.log

OPatch succeeded.

$ /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch lspatches

...
..
.
36587798;OCW RELEASE UPDATE 19.24.0.0.0 (36587798)

How to Connect Oracle GoldenGate Veridata Agent to Autonomous Data Warehouse

This post demonstrate how to connect the Oracle GoldenGate Veridata to Autonomous Data Warehouse (ADW) and Autonomous Transaction Processing (ATP) by using the Oracle Wallet.

Downloaded the Wallet_ADWTESTDB.zip from the ADW/ATP console.

Unzip the Wallet in the location

$ unzip Wallet_ADWTESTDB.zip

Archive: Wallet_ADWTESTDB.zip

inflating: cwallet.sso

inflating: tnsnames.ora

inflating: truststore.jks

inflating: ojdbc.properties

inflating: sqlnet.ora

inflating: ewallet.p12
inflating: keystore.jks

Add the following into sqlnet.ora

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/oracle/cert/Wallet_ADWTESTDB")))

SSL_SERVER_DN_MATCH=no

Remove the comment for the following line in ojdbc.properties

oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))

Copy the jdbc jars into Oracle GoldenGate Veridata Agent installed location

$ ls -ltr /u01/app/oracle/product/veridata23c_agent/agent/drivers/ojdbc8.jar
-rw-r----- 1 oracle oinstall 4535290 Nov 2 2:20 /u01/app/oracle/product/veridata23c_agent/agent/drivers/ojdbc8.jar

Point the Veridata Agent to pick the jdbc 18.3 jars with the following entries in agent.properties

server.jdbcDriver=ojdbc8.jar 
server.driversLocation = /u01/app/oracle/product/veridata23c_agent/agent/drivers

Change the database url

database.url=jdbc:oracle:thin:@adwtestdb_low?TNS_ADMIN=/home/oracle/cert/Wallet_ADWTESTDB

Start the agent

$ ./agent.sh stop
$ ./agent.sh start agent.properties