Table of contents
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]
