How to add a Logical Standby to an existing #Oracle Data Guard Configuration

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! :-)

About these ads

  1. #1 by TIROU on October 2, 2013 - 10:43

    That’s what I’m searching for; our database version is 11gR2 with a physical standby configuration. Do you think there won’t be any issue ?
    Thanking you in advance.

  2. #2 by Uwe Hesse on October 2, 2013 - 10:53

    Tirou, I suppose it is even easier in your case. You will probably not encounter ORA-16803 when you go through the sequence
    remove database, add database, enable database
    even without starting SQL Apply manually. The broker is able to do that in 11gR2 – at least it was when I checked it today on 11.2.0.1
    The posting was about 12.1.0.1

    In short: You should expect no (serious) problem :-)

  3. #3 by TIROU on October 2, 2013 - 11:38

    Uwe, Thanks a lot for having analysed my interrogation and your fast reply. I will write you again if any problem arise.

  4. #4 by ORADB on April 10, 2014 - 22:43

    Uwe,

    Will this work if GLOBAL_NAMES is set to TRUE? I am asking because you said, “The table that need to get inserts is reached via a database link that points to the Primary – modifying it there.”

  5. #5 by Uwe Hesse on April 11, 2014 - 08:58

    ORADB, where did I say that? Not in this posting, apparently.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,600 other followers

%d bloggers like this: