Data Guard hands-on with a logical standby database creation

 

Preamble

A logical standby is an exact copy of your primary database and is open in read write mode (multiple segregation level do exists anyway) and let you offload your reporting activity. A logical standby is also candidate standby for switchover and failover.

A logical standby also accepts creation of indexes and materialized views to speed up reports generation.

All is not perfect anyway as restrictions do exist on supported data types, objects and DDL commands. Those restrictions tend to disappear with latest Oracle releases…

The starting point of your logical standby is a physical standby that’s why this post starts right after creation of a physical standby we have already seen in Data Guard hands on with a physical standby database creation post.

Same as before, primary rac1 database is running on server1.domain.com and standby rac2 database is running on server2.domain.com.

Primary preparation

Check for unsupported table (obviously nothing in my small test database):

SQL> SELECT * FROM dba_logstdby_unsupported_table;
 
no ROWS selected

Check for unsupported data types:

SQL> SELECT * FROM dba_logstdby_unsupported;
 
no ROWS selected

Check unique rows identifier. As your logical standby is a completely standalone different database, source rowid and target rowid could be different so SQL Apply process need another unique identifier to apply the changes. This is anyway not mandatory but strongly suggested (also for performance reason) and BAD_COLUMN column of DBA_LOGSTDBY_NOT_UNIQUE table is key to tell you if primary or unique key is mandatory or if Oracle will manage to uniquely identify the target row to update:

SQL> SELECT * FROM dba_logstdby_not_unique;
 
OWNER                          TABLE_NAME                     B
------------------------------ ------------------------------ -
YJAQUIER                       TEST                           N

My small test table has not primary/unique key but this is not mandatory (BAD_COLUMN=’Y’). You could also create one with RELY DISABLE if uniqueness is manage by application. Let’s solve the issue with a standard primary key creation:

SQL> ALTER TABLE yjaquier.test ADD CONSTRAINT test_idx_pk PRIMARY KEY (val);
 
TABLE altered.
 
SQL> SELECT * FROM dba_logstdby_not_unique;
 
no ROWS selected

Stop redo apply on physical standby:

DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL> show database rac2;
 
Database - rac2
 
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    rac2
 
Database Status:
SUCCESS
 
DGMGRL> edit database rac2 set state='apply-off';
Succeeded.
DGMGRL> show database rac2;
 
Database - rac2
 
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds
  Apply Lag:       15 seconds
  Real Time Query: OFF
  Instance(s):
    rac2
 
Database Status:
SUCCESS

Oracle is suggesting to set:

LOG_ARCHIVE_DEST_2= 'LOCATION=directory VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=standby'

If like me you already have:

log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES, ALL_ROLES)'

Then there is nothing to do… If you are working with filesystems then it could be interesting to have distinct archived log directories for your online log and standby log files.

At least set undo retention to 3600 for LogMiner dictionary creation so primary database only. But as usual in case of switchover better to do it for all standby databases (a LogMiner dictionary is re-generated when performing a switchover):

SQL> ALTER SYSTEM SET undo_retention=3600;
 
SYSTEM altered.

Create the LogMiner dictionary:

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
 
PL/SQL PROCEDURE successfully completed.

Remark:
This command require a bit of shred pool so to avoid it failing (same situation when performing a switchover) don’t be skinflint when allocating memory…

If this occur the following two commands may help you after having changed memory allocated to logical standby instance:

ALTER DATABASE STOP logical standby apply;
ALTER DATABASE START logical standby apply IMMEDIATE;

Standby conversion

Convert your physical standby to a logical standby with:

SQL> ALTER DATABASE recover TO logical standby rac2;
 
DATABASE altered.

Shutdown it, start it in MOUNT state and start SQL apply process:

SQL> shutdown IMMEDIATE
ORA-01507: DATABASE NOT mounted
 
 
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total SYSTEM Global Area  208769024 bytes
Fixed SIZE                  2225064 bytes
Variable SIZE             150998104 bytes
DATABASE Buffers           50331648 bytes
Redo Buffers                5214208 bytes
DATABASE mounted.
SQL> ALTER DATABASE OPEN resetlogs;
 
DATABASE altered.
 
SQL> ALTER DATABASE START logical standby apply IMMEDIATE;
 
DATABASE altered.

Then for DGB configuration even if it sounds crazy I have not found any other option than removing and adding back again the standby database, unless DGB complains for:

DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
ERROR

So still DGB thinks you have a physical standby, perform following steps to solve it:

DGMGRL> remove database rac2 preserve destinations;
Removed database "rac2" from the configuration
DGMGRL> add database 'rac2' as connect identifier is rac2;
Database "rac2" added
DGMGRL> enable database rac2;
Enabled.
DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Logical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL> show database rac2
 
Database - rac2
 
  Role:            LOGICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Instance(s):
    rac2
 
Database Status:
SUCCESS

Testing

You have multiple DBA and V$ views to check your logical standby status and apply process progress:

SQL> SELECT SEQUENCE#, first_time, next_time, dict_begin, dict_end, applied FROM dba_logstdby_log ORDER BY SEQUENCE#;
 
 SEQUENCE# FIRST_TIME         NEXT_TIME          DIC DIC APPLIED
---------- ------------------ ------------------ --- --- --------
        42 31-MAY-11          31-MAY-11          YES YES YES
        43 31-MAY-11          31-MAY-11          NO  NO  YES
        44 31-MAY-11          31-MAY-11          NO  NO  YES
        45 31-MAY-11          31-MAY-11          NO  NO  YES
        46 31-MAY-11          31-MAY-11          NO  NO  YES
        47 31-MAY-11          31-MAY-11          NO  NO  YES
        48 31-MAY-11          07-JUN-11          NO  NO  YES
        49 07-JUN-11          07-JUN-11          NO  NO  YES
        50 07-JUN-11          07-JUN-11          NO  NO  YES
 
9 ROWS selected.
SQL> SELECT * FROM v$logstdby_progress;
 
APPLIED_SCN APPLIED_TIME       RESTART_SCN RESTART_TIME       LATEST_SCN LATEST_TIME        MINING_SCN MINING_TIME        RESETLOGS_ID
----------- ------------------ ----------- ------------------ ---------- ------------------ ---------- ------------------ ------------
    1003419 07-JUN-11              1003421 07-JUN-11             1003423 07-JUN-11             1003422 07-JUN-11             752236283
SQL> SELECT * FROM v$logstdby_state;
 
PRIMARY_DBID SESSION_ID REALTIME_APPLY                                                   STATE
------------ ---------- ---------------------------------------------------------------- ----------------------------------------------------------------
   656357419          1 Y                                                                IDLE

You can query the logical standby and you see same picture as primary database:

SQL> SELECT * FROM yjaquier.test ORDER BY val;
 
       VAL DESCR
---------- --------------------
         1 One
         2 Two
         3 Three

Insert a row on the primary database (and commit !):

SQL> INSERT INTO yjaquier.test VALUES(4,'Four');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.

Execute same query on logical standby:

SQL> SELECT * FROM yjaquier.test ORDER BY val;
 
       VAL DESCR
---------- --------------------
         1 One
         2 Two
         3 Three
         4 Four

For tables access rights you have 3 level: ALL (default) only SYS can change data, STANDBY users can change data not managed by Data Guard, NONE all is permitted. Obviously ALL default mode is really restrictive while NONE must be avoided if you don’t want your logical standby diverge from primary and/or have apply errors.

With default mode (ALL) and standard account:

SQL> SELECT guard_status FROM v$database;
 
GUARD_S
-------
ALL
 
SQL> INSERT INTO yjaquier.test VALUES(5,'Five');
INSERT INTO yjaquier.test VALUES(5,'Five')
                     *
ERROR AT line 1:
ORA-16224: DATABASE Guard IS enabled
 
SQL> CREATE INDEX yjaquier.test_idx_val ON yjaquier.test(val);
CREATE INDEX yjaquier.test_idx_val ON yjaquier.test(val)
                                               *
ERROR AT line 1:
ORA-16224: DATABASE Guard IS enabled
 
SQL> CREATE TABLE yjaquier.test1 AS SELECT * FROM yjaquier.test;
CREATE TABLE yjaquier.test1 AS SELECT * FROM yjaquier.test
                                                      *
ERROR AT line 1:
ORA-01031: insufficient PRIVILEGES

Change Data Guard mode with:

SQL> ALTER DATABASE guard standby;
 
DATABASE altered.
 
SQL> SELECT guard_status FROM v$database;
 
GUARD_S
-------
STANDBY

Now you can create objects with a normal user but still data modification and index creation on objects maintained by Data Guard is still forbidden:

SQL> SELECT guard_status FROM v$database;
 
GUARD_S
-------
STANDBY
 
SQL> INSERT INTO yjaquier.test VALUES(5,'Five');
INSERT INTO yjaquier.test VALUES(5,'Five')
                     *
ERROR AT line 1:
ORA-16224: DATABASE Guard IS enabled
 
SQL> CREATE TABLE test1 AS SELECT * FROM test;
 
TABLE created.

References

  • Step by Step Guide on How to Create Logical Standby [ID 738643.1]
  • Creating a Logical Standby with Minimal Production Downtime [ID 278371.1]
  • Troubleshooting Logical Standby [ID 215020.1]
This entry was posted in Oracle and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>