Logical Standby

Redo information shipped  from the primary (i.e. archived redo log information) is transformed into SQL by using Log Miner technology and then applied to the database.

The reader process reads redo records from the archived redo logs.
The preparer process converts the block changes into table changes or logical change records (LCR’s)
The builder process assembles completed transactions from the LCR’s
The analyzer process examines the records possibly eliminating transactions and identifying dependencies between the dirrerent transactions.
The coordinator process (LSP)
assigns transactions
monitors dependencies between transactions and coordinates scheduling
authorizes the commitment of changes to the logical standby database.
The applier process
Applies the LCR’s to the database.
Asks the coordinator process to approve transactions with unresolved dependencies
Commits the transactions

Unsupported Objects:
Tables and sequences in the SYS schema
Tables using table compression
Tables used to support materialized views
Global temporary tables
Tables with unsupported datatypes.

Unsupported Datatypes:
BFILE, ROWID and UROWID
User-defined types
Multimedia data types (Spatial, Image, Oracle text)
Collections (VARRAYS and nested tables)
BINARY XML

SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED_TABLE order by owner;

SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED order by owner;

COMMANDS NOT EXECUTED ON A LOGICAL STANDBY FROM A PRIMARY DATABASE
alter database ………….EXPLAIN
alter session …………..LOCK TABLE
alter materialized view ….SET CONSTRAINTS
alter materialized view log.SET ROLE
alter system ……………SET TRANSACTION
create control file
create database
create database link
create pfile from spfile
create schema authorization
create materialized view
create materialized view log
create spfile from pfile
drop database link
drop materialized view
drop materialized view log

It is best (but not necessary) if all tables have unique keys on them.
SELECT * FROM DBA_LOGSTDBY_NOT_UNIQUE;
alter table hr.employees add primary key (id, name) rely disable;

STEPS
=====
create a physical standby database
stop redo apply on the standby
prepare primary db to support a logical standby
build a logminer dictionary in the redo data
transition to a logical standby db
open logical standby db
verify logical standby db

stop redo apply on the standby
==============================
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

prepare primary db to support a logical standby
==============================
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=(‘LOCATION= VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=pc00prmy’;
set UNDO_RETENTION=3600

build a logminer dictionary in the redo data
==============================
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

transition to a logical standby db (on standby)
==============================
ALTER DATABASE R£ECOVER TO LOGICAL STANDBY db_name;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
change LOG_ARCHIVE_DEST_n parameters to ensure redo from the new database and redo from the primary are not in the same destination.

open logical standby db
==============================
ALTER DTABASE OPEN RESETLOGS;
ALTER DATBASE START LOGICAL STANDBY APPLY IMMEDIATE;

Use DGMGRL to add a logical standby to an existing data guard configuration:
DGMGRL> ADD DATABASE ‘pc00sby2’ AS CONNECT IDENTIFIER IS ‘pc00sby2’;

verify logical standby db
==============================
select sequence#, first_time, next_time, dict_begin, dict_end FROMm DBA_LOGSTDBY_LOG ORDER BY sequence#;
select name, value FROM V$LOGSTDBY_STATS WHERE name = ‘coordinator state’;
select sid, serial#, spid, type, high_scn FROM V$LOGSTDBY_PROCESS;
select applied_scn, latest_scn FROM V$LOGSTDBY_PROGRESS;

Securing the Logical standby
============================
ALTER DATABASE GUARD ALL – prevents users making any changes – DEFAULT
ALTER DATABASE GUARD STANDBY – prevents users making changes to data maintained by data guard sql apply
ALTER DATABASE GUARD NONE – normal security
SELECT GUARD_STATUS FROM V$DATABASE;
applies to all users except SYS.

Archived redo logs are automatically deleted after apply.
use LOG_AUTO_DELETE parameter (TRUE / FALSE)
LOG_AUTO_DEL_RETENTION_TARGET – only used if flash recovery area is not being used to store remote archived logs
execute DBMS_LOGSTDBY.APPLY_SET
execute DBMS_LOGSTDBY.APPLY_UNSET

MANAGING SQL APPLY FILTERING
============================
LsbyASkipCfgPr – SQL apply should ignore (skip) SQL statements as specified
LsbyDSkipCfgPr – delete
LsbyASkipErrorCfgPr – SQL apply should ignore (skip) errors as specified
LsbyDSkipErrorCfgPr – delete
LsbyASkipTxnCfgPr – SQL apply should ignore (skip) transactions as specified
LsbyDSkipTxnCfgPr – delete
SELECT ERROR, STATEMENT_OPT, NAME, USE_LIKE, ESC, PROC FROM DBA_LOGSTDBY_SKIP WHERE OWNER = ‘HR’;

DBMS_SCHEDULER ON A LOGSTDBY
============================
scheduler jobs have default local role.
Activate existing jobs by using the DATABASE_ROLE attribute of DBMS_SCHEDULER.SET_ATTRIBUTE where:
PRIMARY = job runs only when the db is in primary role
LOGICAL STANDBY = job runs only when the db is in logical standby role