Pickleball spielen

The proper way to add a Logical Standby database when the to be converted Physical Standby is already part of a 12c Data Guard configuration is a bit tricky. This is how my configuration looks initially:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxAvailability
  Databases:
  prima - Primary database
    sbdb  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Now I want to convert sbdb into a Logical Standby database and have the Data Guard Broker manage it. Redo Apply needs to be stopped on the Physical Standby now:

DGMGRL> edit database sbdb set state=apply-off;
Succeeded.
DGMGRL> show database sbdb;

Database - sbdb

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-OFF
  Transport Lag:     0 seconds (computed 0 seconds ago)
  Apply Lag:         0 seconds (computed 0 seconds ago)
  Apply Rate:        (unknown)
  Real Time Query:   OFF
  Instance(s):
    sbdb

Database Status:
SUCCESS

Important point here is that the Redo Transport is not stopped, just the Apply. Now I create the Logical Standby Dictionary on the Primary – no mistake possible here because the Standby is not opened READ WRITE. After that, I convert sbdb into a Logical Standby database. Unlike a Physical Standby database which has always the same DB_NAME as the Primary, it gets its own database name:

SQL> exec dbms_logstdby.build

PL/SQL procedure successfully completed.

SQL> connect sys/oracle@sbdb as sysdba
Connected.
SQL> alter database recover to logical standby sbdb;

Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2290264 bytes
Variable Size             364907944 bytes
Database Buffers          146800640 bytes
Redo Buffers                7938048 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select name,dbid from v$database;

NAME            DBID
--------- ----------
SBDB      1219633322

Now I encounter an expected problem when I try to remove sbdb from the configuration that can be resolved easy:

DGMGRL> remove database sbdb;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Failed.
DGMGRL> edit configuration set protection mode as maxperformance;
Succeeded.
DGMGRL> remove database sbdb;
Removed database "sbdb" from the configuration

The next problem took me some more time to resolve.

DGMGRL> add database sbdb as connect identifier is sbdb;
Error: ORA-16803: unable to query a database table or fixed view

Failed.

The solution here is (for me at least) a bit counter-intuitive: Logical Standby Apply needs to be running. That is odd in so far as the Broker way to do that is not yet possible. Instead (on the Standby):

SQL> alter database start logical standby apply immediate;

Database altered.

Now the new Logical Standby can be added:

DGMGRL> add database sbdb as connect identifier is sbdb;
Database "sbdb" added
DGMGRL> enable database sbdb;
Enabled.

The previous remove also removed the database property logxptmode, which was sync. Therefore:

DGMGRL> edit configuration set protection mode as maxavailability;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Failed.
DGMGRL> edit database sbdb set property logxptmode=sync;
Property "logxptmode" updated
DGMGRL> edit configuration set protection mode as maxavailability;
Succeeded.
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxAvailability
  Databases:
  prima - Primary database
    sbdb  - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Succeeded in the end. Hope you find it useful when you ever encounter this task yourself. As always: Don’t believe it, test it! 🙂