1 / 40

Logical Standby Database for Reporting

Logical Standby Database for Reporting. Mark Bole NoCOUG Nov 10, 2005. Introduction. Mark Bole Independent Consultant Oracle, Unix, Perl since 1991 http://www.bincomputing.com. Today ’ s Session. DataGuard Logical Standby — going outside the DG box What is Logical Standby?

theophilus
Download Presentation

Logical Standby Database for Reporting

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. Today’s 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

  4. 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.”

  5. Logical Standby Product Placement? High Availability Integration 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)

  6. Logical Standby Product Placement? High Availability Integration 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. 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!

  8. “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.

  9. 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

  10. RMAN RAC SQL Apply Evolution of Robustness Robustness: less more 8i 9i 10g ASM

  11. 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

  12. Today’s 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

  13. The Goal:logical, 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)

  14. 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

  15. 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

  16. Physical Logical 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

  17. GUI Log Miner I:\>Oracle\product\10.1.0\Client_1\BIN\oemapp.bat lmviewer

  18. Select archived redo logs to mine

  19. Example: schema in primary 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; 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@binc01.tree

  20. Logical Change Records

  21. 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 don’t want • Less muss, less fuss

  22. Today’s 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

  23. 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

  24. Supplemental Logging “If you’re 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!

  25. 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

  26. 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;

  27. Today’s 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

  28. 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

  29. DG SQL Apply The Logstdby Advantage SCHEMA_REDTABLESINDEXESPL/SQL SCHEMA_BLUETABLESINDEXESPL/SQL SCHEMA_GREENTABLESINDEXESPL/SQL SCHEMA_BLUETABLESINDEXESPL/SQL SCHEMA_ORANGETABLESINDEXESPL/SQL LogStdby Primary Reporting/Batch application OLTP application

  30. The Logstdby Advantage Guard Standby (read-only) SCHEMA_BLUETABLESMV LOGSINDEXESPL/SQL SCHEMA_ORANGETABLESINDEXESMATL_VIEWSPL/SQL LogStdby Now we can create Materialized View logs directly in the “source” schema without impacting the primary! Reporting/Batch application

  31. 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)

  32. Example: MV logs schema_blue> select table_name from user_tables; TEST_TABLE schema_blue> delete from test_table where name = 'test1'; ORA-01031: insufficient privileges <= table is guarded schema_blue> CREATE MATERIALIZED VIEW LOG on test_table 2 with sequence, primary key, rowid 3 (value, timestamp) 4 INCLUDING NEW VALUES; Materialized view log created.

  33. Example: Materialized View schema_orange> 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.

  34. Fast Refreshable? Check! schema_orange> 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

  35. Query Rewrite? Check! schema_orange> exec DBMS_MVIEW.EXPLAIN_REWRITE ('select count(*) from schema_blue.test_table', 'mv_test_table'); PL/SQL procedure successfully completed. schema_orange> commit; schema_orange> 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

  36. The Logstdby Advantage Guard Standby (read-only) SCHEMA_BLUETABLESMV LOGSINDEXESPL/SQL SCHEMA_ORANGETABLESINDEXESMATL_VIEWSPL/SQL LogStdby Now we can create Materialized View logs directly in the “source” schema without impacting the primary! Reporting/Batch application

  37. 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!

  38. 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 < specified recovery window) done, successful if (exceeded timeout) done, unsuccessful, wake me up End alter database stop logical standby apply Disconnect

  39. References

  40. Thank you! Copy of presentation will be available at NoCOUG web site Questions?

More Related