Turning a Physical Standby and its Broker into a Logical Standby

Turning a Physical Standby into a Logical Standby is well documented. Check Oracle® Data Guard Concepts and Administration - 11g Release 2 (11.2) - Creating a Logical Standby Database for all the details. Nevertheless, the documentation assumes you set up the log transport service manually and doesn't explain how to do it with the broker in place. You'll find below what needs to be done to perform that change with the broker in place. It's, by far, easier than by setting the log_archive_dest_n parameters.

Notes:

• It's a pity you cannot convert a physical Standby into a logical Standby from one-only dgmgrl command (yet!).
• This post has been tested with Oracle Database 11g Release 2 (11.2.0.1) on Linux x86.
• Before you proceed, make sure the temporary files specified correctly.

A Physical Standby

To begin, let's consider a data guard configuration made of 2 databases; BLACK is the primary and WHITE the physical standby, we'll transform the later into a logical standby database. The first step consists in stopping the redo apply process and checking the database is mounted:

edit configuration
set PROTECTION MODE AS MaxPerformance;

Succeeded.

edit database white
set state=APPLY-OFF;

Succeeded.

show database white;

Database - white

Role:            PHYSICAL STANDBY
Intended State:  APPLY-OFF
Transport Lag:   0 seconds
Apply Lag:       0 seconds
Real Time Query: OFF
Instance(s):
WHITE

Database Status:
SUCCESS

exit

Checking for datatypes and tables without keys

Before you transform the standby database, you can check the database doesn't contain tables without unique keys and tables that cannot be maintained by the logical standby. Run the following queries on BLACK:

select owner, table_name
  from dba_logstdby_not_unique
 where (owner, table_name) not in
       (select distinct owner, table_name
          from dba_logstdby_unsupported)
           and bad_column='Y';

no rows selected    
select distinct owner, table_name
         from dba_logstdby_unsupported;
[...]

Capturing the dictionary on the primary

To proceed with the logical standby setup, you have to capture the dictionary on the source database (BLACK) and make sure there is no pending transactions. For that purpose, you can use the dbms_logstdby package like below:

. oraenv
BLACK

sqlplus / as sysdba

exec dbms_logstby.build;

exit;

Applying the logs on the standby

The standby database can now be synchronized to the point where the physical standby should be turned into a logical standby; to proceed with that step, proceed like below:

. oraenv
WHITE

sqlplus / as sysdba

alter database recover
to logical standby WHITE;

Database altered.

Opening the Physical Standby with ResetLogs

The next step consists in opening the standby database with resetlogs:

shutdown
ORA-01507: database not mounted
ORACLE instance shut down.

startup mount
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size      1335892 bytes
Variable Size    104861100 bytes
Database Buffers   150994944 bytes
Redo Buffers      6447104 bytes
Database mounted.

alter database open resetlogs;

Database altered.

exit;

Rebuilding the broker configuration

You can now change the data guard broker configuration to remove the physical standby configuration and add the logical standby instead:

. oraenv
BLACK

dgmgrl /

remove database white
      preserve destinations;

Removed database "white" from the configuration 

show configuration

Configuration - worldwide

Protection Mode: MaxPerformance
Databases:
black - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

add database white
  as connect identifier is white
  maintained as logical;

Database "white" added

enable configuration;

Enabled.

show configuration;

Configuration - worldwide

Protection Mode: MaxPerformance
Databases:
black - Primary database
white - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

enable configuration;

Enabled.

show configuration

Configuration - worldwide

Protection Mode: MaxPerformance
Databases:
black - Primary database
white - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Note:
It can take a few minutes for the standby and primary to get to the right state. However, if it still fails after a few minutes, check the alert.log for any unexpected errors.

Changing Properties

You can change some properties to ease the switchover and change the protection mode like below:

edit database white
     set property StaticConnectIdentifier=white;

 Property "staticconnectidentifier" updated

edit database black
     set property StaticConnectIdentifier=black;

 Property "staticconnectidentifier" updated

edit configuration
     set protection mode as MaxAvailability;

 Succeeded.

show configuration;

Configuration - worldwide

Protection Mode: MaxAvailability
Databases:
black - Primary database
white - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Testing the Logical Standby

Once the configuration done, you can test it; make sure your changes to the primary database are replicated to the logical standby database:

. oraenv
BLACK

sqlplus / as sysdba

update scott.emp
    set sal=10000
  where ename='KING';

1 row updated.

commit;

Commit complete.

exit;

. oraenv
WHITE

sqlplus / as sysdba

select sal
  from scott.emp
 where ename='KING';

SAL
-----
10000

You can also test the switchover:

. oraenv
BLACK

dgmgrl /
switchover to white;

show configuration;

Conclusion

You may wonder what is the link between Streams and the Logical Standby. Obviously they both rely on the same technology but, in fact that's not the main reason why I'm investigating logical standby. No; instead that's because, with 11.2 you can now configure a Streams capture from a logical standby as described in Oracle® Data Guard Concepts and Administration - 11g Release 2 (11.2) - 10.6.6 Running an Oracle Streams Capture Process on a Logical Standby Database.