Tag: databases

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

Convert RAC Physical Standby To Snapshot Standby

Note: For snapshot standby, you do not need Flashback enabled at database level

Convert physical standby to snapshot standby

On Standby Database

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- -----------
TESTDB    MOUNTED

SQL> alter database convert to snapshot standby;
alter database convert to snapshot standby
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_02/28/2025
21:40:27'.
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database convert to snapshot standby;

Database altered.

SQL>  alter database open;

Database altered.

SQL>  select name, guarantee_flashback_database from v$restore_point;

NAME                                            GURA
----------------------------------------------  -------------

SNAPSHOT_STANDBY_REQUIRED_02/28/2025 21:41:04   YES


SQL> create table student(id number(2), name varchar2(10));

Table created.

SQL>  insert into student values(1,'John');

1 row created.

SQL> insert into student values (2,'James');

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from student;

       ID  NAME
---------- ----------
         1 John
         2 James

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> select name, open_mode from gv$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             MOUNTED
PDB1                 MOUNTED

SQL>  alter pluggable database pdb1 open instances=all;

Pluggable database altered.

SQL> select name, open_mode from gv$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             MOUNTED
PDB1                 MOUNTED

Have to restart database for all PDB open.

$ srvctl stop database -d TESTDB -o immediate

$ srvctl start database -d TESTDB -o open

SQL>  col NAME format a20
SQL>  select name, open_mode from gv$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             READ ONLY
PDB1                 READ WRITE

SQL> select name, open_mode, database_role from v$database;

NAME                 OPEN_MODE            DATABASE_ROLE
-------------------- -------------------- ----------------
TESTDB               READ WRITE           SNAPSHOT STANDBY

Revert back snapshot standby to physical standby

SQL>  select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ETSTDB    MOUNTED              SNAPSHOT STANDBY

SQL> alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:
ORA-38777: database must not be started in any other instance

$ srvctl stop  instance  -d TESTDB -i TESTDB2 -o immediate

SQL>  alter database convert to physical standby;

Database altered.

SQL>  select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB    MOUNTED              PHYSICAL STANDBY

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

$ srvctl start  database -d TESTDB -o mount

SQL> alter database recover managed standby database disconnect;

Database altered.