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! 🙂
#1 von TIROU am Oktober 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 von Uwe Hesse am Oktober 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 220.127.116.11
The posting was about 18.104.22.168
In short: You should expect no (serious) problem 🙂
#3 von TIROU am Oktober 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 von ORADB am April 10, 2014 - 22:43
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 von Uwe Hesse am April 11, 2014 - 08:58
ORADB, where did I say that? Not in this posting, apparently.