Logical Standby database
Logical standby database is a feature introduced in Version 9iR2. In this configuration, redo data is first converted into SQL statements and then applied to the standby database. This process is called SQL Apply. This method makes it possible to access the standby database permanently and allows read/write while the replication of data is active. Thus, you’re also able to create database objects on the standby database that don’t exist on the primary database. So a logical standby database can be used for many other purposes along with high availability and disaster recovery.
Due to the basics of SQL Apply, a logical standby database will contain the same data as the primary database but in a different structure on the disks.
One discouraging aspect of the logical standby database is the unsupported data types, objects, and DDLs. The following data types are not supported to be replicated in a logical standby environment:
- BFILE
- Collections (including VARRAYS and nested tables)
- Multimedia data types (including Spatial, Image, and Oracle Text)
- ROWID and UROWID
- User-defined types
The standby database doesn’t guarantee to contain all primary data because of the unsupported data types, objects, and DDLs. Also, SQL Apply consumes more hardware resources. Therefore, it certainly brings more performance issues and administrative complexities than Redo Apply.
Primary Machine configuration
SQL> select database_role,name,open_mode,flashback_on,force_logging from
v$database;
DATABASE_ROLE NAME OPEN_MODE FLASHBACK_ON FOR
—————- ——— ——————– —————— — ——
PRIMARY ADMDB READ WRITE YES YES
SQL>
SQL>
SQL> select supplemental_log_data_pk from v$database;
SUP
—
NO
SQL> select supplemental_log_data_ui from v$database;
SUP
—
NO
SQL> alter database add supplemental log data (primary key,unique) columns;
Database altered.
SQL> select owner,table_name from dba_logstdby_not_unique
2 wherebad_column=’Y’
3 and (owner,table_name) not in
4 (select distinct owner,table_name from dba_logstdby_unsupported);
no rows selected
SQL> exec dbms_logstdby.build;
PL/SQL procedure successfully completed.
Standby side settings
SQL> alter database recover to logical standby stbdb;
Database altered.
=>On the same time exec dbms_logstdby.build;
package on primary side to complete above command.
SQL>shu immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 864333824 bytes
Fixed Size 2218232 bytes
Variable Size 234882824 bytes
Database Buffers 620756992 bytes
Redo Buffers 6475776 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> alter database start logical standby apply immediate;
Database altered.
DATA GUARD / BROKER SITE
DGMGRL> connect sys/sys@primary;
Connected.
DGMGRL>
DGMGRL>
DGMGRL> show database stbdb;
Database – stbdb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
stbdb
Database Status:
SUCCESS
DGMGRL> edit database stbdb set state =’APPLY-OFF’;
Succeeded.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show database stbdb;
Database – stbdb
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 32 seconds
Real Time Query: OFF
Instance(s):
stbdb
Database Status:
SUCCESS
DGMGRL> show configuration
Configuration – broker1
Protection Mode: MaxPerformance
Databases:
primary – Primary database
stbdb – Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL> disable configuration;
Disabled.
DGMGRL> remove database stbdb;
Removed database “stbdb” from the configuration
DGMGRL>
DGMGRL>
DGMGRL>
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 ‘stbdb’
>as connect identifier is stbdb
> maintained as logical;
Database “stbdb” added
DGMGRL> show configuration
Configuration – broker1
Protection Mode: MaxPerformance
Databases:
primary – Primary database
stbdb – Logical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> enable database stbdb;
Enabled.
DGMGRL> show configuration;
Configuration – broker1
Protection Mode: MaxPerformance
Databases:
primary – Primary database
stbdb – Logical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
Use for reporting at all times
It’s possible to use a standby database anytime to offload reporting jobs from the primary database because a standby database is always open for user connections. This is also available with the Oracle version 11g physical standby feature of Active Data Guard but it requires an additional license.
By executing the following SQL query on the logical standby, we can check logs with which sequences are being transferred from primary and also which sequences are being archived from the local database online redo logs.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
——— ———— ———- ———- ———- ———-
ARCH CLOSING 1 90 90112 1026
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 91 90112 1026
ARCH CLOSING 1 92 90112 1018
RFS IDLE 0 0 0 0
RFS RECIEVING 1 114 6828 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
Use the following query on the standby database, to check the general SQL Apply status:
SQL> SELECT * FROM V$LOGSTDBY_STATE;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
————— ———- ————— —————
1319333016 1 Y APPLYING
After converting physical standby into standby database the real testing on primary side.
And also check these changes will take effect on Logical standby side machine.
To learn “How to create physical standby database” Physical Standby
Or you can go to oracle-base site.
Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com.
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.
2 thoughts on “LOGICAL STANDBY DATABASE”