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

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?
• How to set it up
• Reporting/Batch: refresh cycle, materialized
views, maintenance
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.”
Logical Standby Product Placement?
Integration
8i
9i
10g
Basic Replication
Advanced
Replication
Oracle
Streams
High Availability
Basic readable standby database
Oracle Data Guard – Redo
Apply (Physical Standby)
Oracle Data Guard –
SQL Apply (Logical
Standby)
Logical Standby Product Placement?
Integration
8i
9i
10g
Basic Replication
Advanced
Replication
Oracle
Streams
High Availability
Basic readable standby database
Oracle Data Guard – Redo
Apply (Physical Standby)
Oracle Data Guard –
SQL Apply (Logical
Standby)
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!
“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.
Logical Standby has a
documented list of
restrictions*
Unsupported
Unsupported Tables,
Sequences, and Views
(partial list)
Datatypes (partial list)
LONG
LONG RAW
•Tables using data
BFILE
segment compression
ROWID
user-defined types
•Index-organized
varrays
tables
nested tables
*Note: many removed in 10g
Evolution of Robustness
8i
9i
10g
Robustness:
less
more
RMAN
RAC
SQL Apply
ASM
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
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
•
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)
Previous Solution
Primary
Physical Standby
Redo apply
(once every 24 hours)
Daily Cycle:
1.
2.
3.
4.
5.
6.
Close Physical Standby
Mount Physical Standby
Recover day's worth of redo
Open Physical Standby
read-only for rest of day
Run batch and reporting processes
using dblink
Loop to step 1
DB Link
Reporting Database
Previous Solution - Problems
• Need to close and reopen 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
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.”
Physical
SQL
redo
Block-level
changes
logminer
Logical
SQL
GUI Log Miner
I:\>Oracle\product\10.1.0\Client_1\BIN\oemapp.bat lmviewer
Select archived redo logs to mine
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;
CREATE TABLE test_table
(name
varchar2(50),
value
varchar2(50),
timestamp
date,
constraint pk_test_table primary
key (name)
using index);
grant create session,
create table to
schema_blue;
insert into test_table values
('test1', 'value1', sysdate);
connect
schema_blue/[email protected]
insert into test_table values
('test2', 'value2', sysdate);
commit;
Logical Change Records
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
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
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
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!
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
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;
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
Previous Solution
Primary
Physical Standby
Redo apply
(once every 24 hours)
Daily Cycle:
1.
2.
3.
4.
5.
6.
Close Physical Standby
Mount Physical Standby
Recover day's worth of redo
Open Physical Standby
read-only for rest of day
Run batch and reporting processes
using dblink
Loop to step 1
DB Link
Reporting Database
The Logstdby Advantage
SCHEMA_RED
TABLES
INDEXES
PL/SQL
Primary
SCHEMA_BLUE
TABLES
INDEXES
PL/SQL
SCHEMA_GREEN
TABLES
INDEXES
PL/SQL
SCHEMA_BLUE
TABLES
INDEXES
PL/SQL
SCHEMA_ORANGE
TABLES
INDEXES
PL/SQL
LogStdby
DG SQL Apply
OLTP
application
Reporting/Batch
application
The Logstdby Advantage
Guard Standby
(read-only)
SCHEMA_BLUE
TABLES
MV LOGS
INDEXES
PL/SQL
SCHEMA_ORANGE
TABLES
INDEXES
MATL_VIEWS
PL/SQL
Now we can create
Materialized View
logs directly in the
“source” schema
without impacting
the primary!
LogStdby
Reporting/Batch
application
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)
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.
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.
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
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
The Logstdby Advantage
Guard Standby
(read-only)
SCHEMA_BLUE
TABLES
MV LOGS
INDEXES
PL/SQL
SCHEMA_ORANGE
TABLES
INDEXES
MATL_VIEWS
PL/SQL
Now we can create
Materialized View
logs directly in the
“source” schema
without impacting
the primary!
LogStdby
Reporting/Batch
application
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!
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
References
Creating a Logical Standby with Minimal
Production Downtime
Note:278371.1
Oracle Data Guard Readme for SQL Apply
Release 9.2.0.6
Note:286787.1
Oracle Data Guard Readme for SQL Apply
Release 10.1.0.3
Note:304059.1
SQL Apply Best Practices (9i)
http://otn.oracle.com/deploy/availability/pdf/DataGuard
SQLApplyBestPractices.pdf
SQL Apply Best Practices (10g)
http://www.oracle.com/technology/deploy/availability/p
df/MAA_WP_10gSQLApplyBestPractices.pdf
Oracle10g Data Guard SQL Apply
Troubleshooting
Note:312434.1
ORA-1 Occurring on Logical Standby
Note:257356.1
Synchronizing tables in a Logical Standby
Database
Note:271455.1
Troubleshooting 9i Data Guard Network
Issues
Note:241925.1
Streams and Data Guard Role Transitions
http://www.oracle.com/technology/deploy/availability/p
df/MAA_WP_10gDataGuardRoleTransitionsStreams.p
df
Thank you!
Copy of presentation will be
available at NoCOUG web site
Questions?