Data Guard hands-on with a logical standby database creation
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]



