Oracle Data Guard 11g - Overview Logical Standby Database

Logical Standby  Database is one part of Oracle Database Guard.

The benefits are obvious: the standby database can not only be open for reads, but it can also support additional entities such as indexes and materialized views that can be too expensive to maintain at the primary database. In addition, you can add other tables or even entire schemas to a logical standby database and have complete read-write access to those tables as they are not maintained by SQL Apply.

Logical standby databases are support all role transition operations that are available in the context of a physical standby database.

Creating Logical  Standby Database.

For creating logical standby database we are need create a physical standby database, then convert to logical standby.

I had created broker-managed configuration admdg with admdb – Primary database, admts – Physical  standby database.

1. Created Physical Standby database

On primary database :

DGMGRL> show configuration 

Configuration - admdg

  Protection Mode: MaxPerformance
  Databases:
    admdb - Primary database
    admts – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> 

2.  Supplemental logging enabled
The supplemental logging information ensures each update contains enough information to logically identify each row that is modified by the statement. We can check supplemental logging for primary key, and unique columns.
On primary database :

SQL> select supplemental_log_data_pk from  v$database;

SUP
---
YES

If supplemental logging is not enabled then you can use  bellow command.

On primary  database:
SQL> alter database add supplemental log data (primary key, unique) columns;

3. Build LogMiner Dictionary

A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo.

On primary database :

SQL>execute dbms_logstdby.build;

4. Transition to Logical Standby Database

On physical standby database call  command when  physical  standby database is mount mode.

On physical  standby database:
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY admts

Note: if log miner dictionary  is not created then this command  wait receive information from primary.

5. Open logical  standby database with  reset logs.

After transition to logical standby database we can open logical standby database with reset logs

On logical standby database:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT; 
SQL> ALTER DATABASE OPEN RESETLOGS; 

On opened logical standby database we can start apply service with :
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Now database name changed to  admts and database role is Logical Standby.

On logical standby database:

SQL> select database_role, name from v$database;

DATABASE_ROLE     NAME
---------------- ---------
LOGICAL STANDBY  ADMTS

6.  Adding logical  standby  database to broker-managed configuration

After creating logical  standby  database we can see old physical  standby  database admts is return error on broker –managed configuration. We must remove  this physical standby  database and must add new logical  standby  database to  configuration .

On primary database server :

DGMGRL> show configuration 

Configuration - admdg

  Protection Mode: MaxPerformance
  Databases:
    admdb - Primary database
    admts – Physical standby database
      Error : ORA–16810 multiple errors or warnings detected for database 
Fast-Start Failover: DISABLED

Configuration Status:
ERROR

DGMGRL> 

Remove database from configuration

DGMGRL> remove database admts
Removed database "admts" from the configuration

After this command :

DGMGRL> show configuration 

Configuration - admdg

  Protection Mode: MaxPerformance
  Databases:
    admdb - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Adding logical standby database to configuration with add database on data guard manager command line.

DGMGRL> help add 

Adds a standby database to the broker configuration

Syntax:

  ADD DATABASE <database name>
    [AS CONNECT IDENTIFIER IS <connect identifier>]
    [MAINTAINED AS {PHYSICAL|LOGICAL}];


DGMGRL> add database admts as connect identifier is admts maintained as logical;
Database "admts" added

DGMGRL> show configuration

Configuration - admdg

  Protection Mode: MaxPerformance
  Databases:
    admdb - Primary database
    admts - Logical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> enable database admts
Enabled.


DGMGRL> show configuration 

Configuration - admdg

  Protection Mode: MaxPerformance
  Databases:
    admdb - Primary database
    admts - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS 

Logical  standby  database already  created successfully.

7. Perform switchover
Now  we can perform switchover to logical to standby  database with switchover to …  command on Data Guard manager command line tool.

DGMGRL> switchover to admts
Performing switchover NOW, please wait...
Switchover succeeded, new primary is "admts"
DGMGRL> 

Now show configuration command output as following

DGMGRL> show configuration

Configuration - admdg

  Protection Mode: MaxPerformance
  Databases:
    admts - Primary database
    admdb - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

admts – is primary  database, admdb – is logical  standby database.

We can  make tests and monitoring  on logical  standby database.
I showed a lot of tests and monitoring on logical standby database on video tutorial. 

You can watch on youtube :
http://www.youtube.com/watch?v=2C0ETPuSECo

Regards
Mahir M. Quluzade