Logical Standby Database for Reporting

1
Logical Standby Database for Reporting

• Mark Bole
• NoCOUG Nov 10, 2005

2
Introduction
Mark Bole Independent Consultant Oracle, Unix,
Perl since 1991 http//www.bincomputing.com
3
(teaser)
Logical Standby Databases for Reporting - Mark
Bole, BIN ComputingNot quite ready for a data
warehouse, but still want a high performance
reporting environment that doesn't impact your
on-line transaction (OLTP) users? New in Oracle
9i, the Data Guard logical standby database is
one answer. One or more core schemas are
automatically synchronized with your live
database, as frequently as you wish, without
creating any load on the source database. Once
updated, the core data is protected from any
alteration, yet any other tables and schemas can
be created in the same database to extend your
data model and provide reporting performance.
Materialized views, query re-write, and other
typical data warehouse techniques are all
available.
4
Todays Session

• DataGuard Logical Standby going outside the DG
  box
• What is Logical Standby?
• How to set it up
• Reporting/Batch refresh cycle, materialized
  views, maintenance

5
DataGuard vs. Streams

• From the Oracle docco
• While Streams and Data Guard do share some
  common underlying technology, they are both
  independent features that are built to solve
  different business needs. see references
• Oracle Data Guard is designed for protecting
  from data failure and disasters.
• Streams is designed for information sharing and
  distribution but can also provide a very
  efficient high availability solution.

6
Logical Standby Product Placement?

• Integration

High Availability
Basic Replication Advanced Replication
Basic readable standby database Oracle Data Guard
Redo Apply (Physical Standby)
8i 9i 10g
Oracle Streams
Oracle Data Guard SQL Apply (Logical
Standby)
7
Logical Standby Product Placement?

• Integration

High Availability
Basic Replication Advanced Replication
Basic readable standby database Oracle Data Guard
Redo Apply (Physical Standby)
8i 9i 10g
Oracle Streams
Oracle Data Guard SQL Apply (Logical
Standby)
8
Why Not Logstdby for HA?

• Parity, one-to-one-ness, having a failover
  environment that is a mirror image of the
  production site. That is the ideal
  situation. http//asktom.oracle.com

Too Many Ways for the Logical Standby to deviate
from the primary intentional or otherwise!
9
Bugs A Real-Life Story

• The following is an excerpt from a recent posting
  on the oracle-l list.
• Need some help or opinions regarding Oracle
  logical standby database.
• Logical standby, version 10, release x, patch x.
  Primary and standby are tightly coupled (maximum
  availability mode). ... 2) From time to time,
  standby breaks, but this seems due mostly to
  schema changes ... 3) DB Guard leaks memory
  on the primary. This was confirmed via the OS,
  not V-views. . Had to disable it. Not really
  happy about that. 5) Primary is not happy
  about unexpected standby restarts.... 6) To
  sum up something's a bit shaky in my
  environmentt. Should I give up with maximum
  availability mode and reduce the database to
  maximum performance mode? I am interested to hear
  from others.

10
Logical Standby has a documented list of
restrictions

• Unsupported Datatypes (partial list)
• LONG
• LONG RAW
• BFILE
• ROWID
• user-defined types
• varrays
• nested tables

• Unsupported Tables, Sequences, and Views (partial
  list)
• Tables using data segment compression
• Index-organized tables

Note many removed in 10g
11
Evolution of Robustness
Robustness less more
8i 9i 10g
ASM
12
Assumptions Logstdby for Reporting

• Not running DG Manager/Broker
• No intention of ever switching DG roles
• Log transport MAXIMUM PERFORMANCE mode
• Implies existence of separate Physical Standby
  for real recovery
• NOARCHIVELOG mode (all report/batch data can be
  re-created if necessary)
• Service Level less than 24 x 7 (planned
  maintenance outages)
• Not running SQL Apply continuously

13
Todays Session

• DataGuard Logical Standby going outside the DG
  box
• What is Logical Standby?
• How to set it up
• Reporting/Batch refresh cycle, materialized
  views, maintenance

14
The Goallogical, not physical, replication

• Physical standby is an image copy of the primary
  every datafile block is the updated to match as
  of point in time.
• Other existing options for logical
• Export / Import
• Traditional Advanced Replication
• Transportable tablespace (maybe)

15
Previous Solution

• Primary

Physical Standby
Redo apply(once every 24 hours)

• Daily Cycle
• Close Physical Standby
• Mount Physical Standby
• Recover day's worth of redo
• Open Physical Standbyread-only for rest of day
• Run batch and reporting processes using dblink
• Loop to step 1

DB Link
Reporting Database
16
Previous Solution - Problems

• Need to close and re-open physical standby
• Two databases on same server creates
  inefficiencies
• Harder to tune distributed queries
• Cannot use MV logs in standby, hence cannot use
  MV's in reporting database

Physical Standby (open read-only)
DB Link
Reporting Database
17
SQL Apply

• SQL Apply technology first transforms the
  redo data into SQL statements and then executes
  the generated SQL statements on the logical
  standby database.

Block-level changes
redo
logminer
SQL
SQL
18
GUI Log Miner
I\gtOracle\product\10.1.0\Client_1\BIN\oemapp.bat
lmviewer
19
Select archived redo logs to mine
20
Example schema in primary

• create user schema_blue
• identified by xyz
• default tablespace TESTDB_A
• temporary tablespace temp
• quota 100M on TESTDB_A
• quota unlimited on temp
• grant create session, create table to
  schema_blue
• connect schema_blue/xyz_at_binc01.tree

CREATE TABLE test_table (name
varchar2(50), value varchar2(50),
timestamp date, constraint pk_test_table
primary key (name) using index) insert into
test_table values ('test1', 'value1',
sysdate) insert into test_table values
('test2', 'value2', sysdate) commit
21
Logical Change Records
22
Logical Standby Streams Made Simple

• Streams is configured from the bottom up
  individual tables, schemas, capture processes,
  apply processes, queues
• Logical Standby is configured from the top down
  start with entire database, then specify only
  what you dont want
• Less muss, less fuss

23
Todays Session

• DataGuard Logical Standby going outside the DG
  box
• What is Logical Standby?
• How to set it up
• Reporting/Batch refresh cycle, materialized
  views, maintenance

24
Logstdby Pre-requisites

• Enterprise Edition license
• Same operating system and platform architecture
  as primary
• Same RDBMS version (10g supports rolling
  upgrades)
• FORCE LOGGING at database level

25
Supplemental Logging

• If youre going to use supplemental logging, you
  need to be aware that you are actually asking for
  quite a lot of new information to be included in
  the redo stream and all that extra information
  might just be enough to tip LGWR and ARCH over
  into serious performance problems.
• Howard J. Rogers, New Features in Oracle 9i

Much less of a problem if you have a unique or
primary key on every table!
26
Create the Logstdby

• Start with any kind of hot backup
• Mount or quiesce the primary (requires outage,
  restriction removed in 10g)
• EXECUTE DBMS_LOGSTDBY.BUILD to put the LogMiner
  dictionary into the redo stream
• Rename datafiles, online redo logs in standby
• Reset database name DBNEWID utility
• Register archive logfile containing LogMiner
  dictionary, start SQL Apply

27
My favorite LogStdby settings

• INIT.ORA
• remote_archive_enable receive
• standby_archive_dest '/opt/oracle/admin/binc01dg
  /stbyarch'
• DBA_LOGSTDBY_PARAMETERS
• DBMS_LOGSTDBY.APPLY_SET ('TRANSACTION_CONSISTENCY'
  , 'NONE')
• DBMS_LOGSTDBY.APPLY_SET ('MAX_EVENTS_RECORDED',
  '2000')
• DBMS_LOGSTDBY.APPLY_SET ('RECORD_SKIP_DDL',
  'FALSE')
• DBMS_LOGSTDBY.APPLY_SET ('RECORD_APPLIED_DDL',
  'FALSE')
• DBMS_LOGSTDBY.APPLY_SET ('_EAGER_SIZE', 1000)
• alter database guard standby

28
Todays Session

• DataGuard Logical Standby going outside the DG
  box
• What is Logical Standby?
• How to set it up
• Reporting/Batch refresh cycle, materialized
  views, maintenance

29
Previous Solution

• Primary

Physical Standby
Redo apply(once every 24 hours)

• Daily Cycle
• Close Physical Standby
• Mount Physical Standby
• Recover day's worth of redo
• Open Physical Standbyread-only for rest of day
• Run batch and reporting processes using dblink
• Loop to step 1

DB Link
Reporting Database
30
The Logstdby Advantage
SCHEMA_REDTABLESINDEXESPL/SQL SCHEMA_BLUETABLE
SINDEXESPL/SQL SCHEMA_GREENTABLESINDEXESPL/SQ
L
SCHEMA_BLUETABLESINDEXESPL/SQL SCHEMA_ORANGETA
BLESINDEXESPL/SQL
LogStdby
Primary
Reporting/Batch application
OLTP application
31
The Logstdby Advantage
Guard Standby (read-only)
SCHEMA_BLUETABLESMV LOGSINDEXESPL/SQL SCHEMA_O
RANGETABLESINDEXESMATL_VIEWSPL/SQL
LogStdby
Now we can create Materialized View logs directly
in the source schema without impacting the
primary!
Reporting/Batch application
32
Controlling what is replicated

• -- workaround for bug in 9.2.0.5
• exec DBMS_LOGSTDBY.SKIP('PROCEDURE', 'XYZ', '',
  null)
• exec DBMS_LOGSTDBY.SKIP('SCHEMA_DDL',
  'VCS_MONITOR', '', null)
• exec DBMS_LOGSTDBY.SKIP('DML', 'VCS_MONITOR',
  '', null)
• -- NON_SCHEMA_DDL (for grants)

33
Example MV logs

• schema_bluegt select table_name from user_tables
• TEST_TABLE
• schema_bluegt delete from test_table where name
  'test1'
• ORA-01031 insufficient privileges lt table is
  guarded
• schema_bluegt CREATE MATERIALIZED VIEW LOG on
  test_table
• 2 with sequence, primary key, rowid
• 3 (value, timestamp)
• 4 INCLUDING NEW VALUES
• Materialized view log created.

34
Example Materialized View

• schema_orangegt create materialized view
  mv_test_table
• 2 build immediate
• 3 refresh fast on demand
• 4 ENABLE QUERY REWRITE
• 5 AS
• 6 SELECT
• 7 count() cnt,
• 8 count(name) cnt_name,
• 9 tt.value,
• 10 trunc(tt.timestamp) trunc_timestamp
• 11 FROM
• 12 schema_blue.test_table tt
• 13 group by
• 14 tt.value,
• 15 trunc(tt.timestamp)
• Materialized view created.

35
Fast Refreshable? Check!

• schema_orangegt SELECT OWNER,
• 2 MVIEW_NAME,
• 3 FAST_REFRESHABLE
• 4 FROM DBA_MVIEWS
• 5 /
• OWNER MVIEW_NAME
  FAST_REFRESHABLE
• -------------------- ---------------
  ------------------
• SCHEMA_ORANGE MV_TEST_TABLE DIRLOAD_DML

36
Query Rewrite? Check!
schema_orangegt exec DBMS_MVIEW.EXPLAIN_REWRITE
('select count() from schema_blue.test_table',
'mv_test_table') PL/SQL procedure successfully
completed. schema_orangegt commit schema_orangegt
SELECT MV_OWNER, MV_NAME, QUERY, MESSAGE, PASS
FROM REWRITE_TABLE headings
omitted SCHEMA_ORANGE
MV_TEST_TABLE select count() from
schema_blue.test_table QSM-01033 query rewritten
with materialized view, MV_TEST_TABLE YES
37
The Logstdby Advantage
Guard Standby (read-only)
SCHEMA_BLUETABLESMV LOGSINDEXESPL/SQL SCHEMA_O
RANGETABLESINDEXESMATL_VIEWSPL/SQL
LogStdby
Now we can create Materialized View logs directly
in the source schema without impacting the
primary!
Reporting/Batch application
38
SQL Apply can be slow

• Redo contains uncommitted transactions
• Necessary overhead of logical vs. physical in
  general
• Be sure to read and follow Best Practices
  document
• Be prepared, in worst case, to recreate your
  Logical Standby for reporting!

39
Recover Perl script

• recover_logstdby.pl - recover logical standby
• Connect to database
• alter database start logical standby apply
• Die on error unless ORA-16105 Logical Standby is
  already running in background
• While (BehindTime
• select
• trunc( (newest_time - applied_time)1440 )
• from
• dba_logstdby_progress)
• if (BehindTime lt specified recovery window)
• done, successful
• if (exceeded timeout)
• done, unsuccessful, wake me up
• End
• alter database stop logical standby apply
• Disconnect

40
References
Creating a Logical Standby with Minimal Production Downtime Note278371.1
Oracle Data Guard Readme for SQL Apply Release 9.2.0.6 Note286787.1
Oracle Data Guard Readme for SQL Apply Release 10.1.0.3 Note304059.1
SQL Apply Best Practices (9i) http//otn.oracle.com/deploy/availability/pdf/DataGuardSQLApplyBestPractices.pdf
SQL Apply Best Practices (10g) http//www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gSQLApplyBestPractices.pdf
Oracle10g Data Guard SQL Apply Troubleshooting Note312434.1
ORA-1 Occurring on Logical Standby Note257356.1
Synchronizing tables in a Logical Standby Database Note271455.1
Troubleshooting 9i Data Guard Network Issues Note241925.1
Streams and Data Guard Role Transitions http//www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gDataGuardRoleTransitionsStreams.pdf
41
Thank you!
Copy of presentation will be available at NoCOUG
web site Questions?