 |
|
11g Oracle Streams and
Advanced Queuing (AQ)
Oracle 11g New Features Tips by Burleson
Consulting
July 12, 2008 |
Oracle 11g SQL New Features Tips
Oracle Streams is a high-speed messaging system
that allows synchronization between Oracle databases, and also to
other varied databases. With 11g Oracle Streams are expected to
operate about twice as fast as in previous releases. Oracle Streams
is an indispensable tool for companies with a heterogeneous
application environment in which the business runs on applications
from multiple different vendors. This type of application
environment is very common and has its benefits (i.e. best of
breed). However this atmosphere brings with it integration
challenges. Oracle Streams is a tool that can help Remote DBAs integrate
and replicate data among multiple systems and databases.
With version 10g of the database, Oracle
integrated Advanced Queuing (AQ) into Streams. Oracle Streams has
three basic functions known as Capture, Stage and Consume. Capture
works by extracting DML and DDL from the REDO log and pushes these
updates to the Staging area. Once in the staging area these changes
are then consumed by an application or applied to the destination
database. The Capture process runs in the source
database and the Consume/Apply process runs at the destination
database. Figure 9.4 shows this very high level architecture
of how a stream functions.
Just as in version 10, Oracle Streams is
configurable via wizards in Oracle Enterprise Manager. In the
following example the schema SCOTT is replicated from the source
database DEV11 to the destination database DG.
First click the Data Movement tab in OEM, and
then click Setup under Streams. Since this is a new database a
wizard can be used to create the Streams Administrator as shown in
Figure 9.5.
% The username is
case-sensitive because OEM uses quotes around the username when it generates scripts with the connect syntax (i.e.
connect
"STREAMS"/password@dev11)
The next step is to configure streams to talk to
the destination database as shown in Figure 9.6.
The next screen is where the Remote DBA configures a
replication method, the Capture, Propogate and Apply processes, the
directories to use and whether to replicate DDL, DML or both. This
is illustrated in Figure 9.7 below.
Depending on the option taken for Replication in
the previous step the following screen (Figure 9.8) is displayed for
the Remote DBA to choose objects to be replicated from the source to
destination database.
The final screen displayed before OEM creates
the Streams job is the Review screen seen in Figure 9.9 below.
The next step is for Oracle to create a job to
setup the queue, export and import the data from source to
destination and finally startup the processes. OEM is saving the
Remote DBA from creating tedious replication scripts. To achieve the same
replication manually would require a script similar to the one
below. Only a small portion of the 164 lines are included:
--**********************
--SETUP
--**********************
connect
streams/password@dev11;
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => '"STREAMS_CAPTURE2_QT"',
queue_name => '"STREAMS_CAPTURE2_Q"',
queue_user => 'streams');
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => '"SCOTT"',
streams_type => 'capture',
streams_name => '"STREAMS_CAPTURE2"',
queue_name => 'streams."STREAMS_CAPTURE2_Q"',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
inclusion_rule => true);
END;
/
.
.
.
--**********************
--Export and Import
--**********************
connect streams/password@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORCLDEV)(PORT=1521)))
(CONNECT_DATA=(SID=DG)(SERVER=DEDICATED)))";set
serverout on;
execute dbms_output.enable(50000);
DECLARE
handle1 number;
ind number;
percent_done number;
job_state VARCHAR2(30);
le ku$_LogEntry;
js ku$_JobStatus;
jd ku$_JobDesc;
sts ku$_Status;
BEGIN
handle1 := DBMS_DATAPUMP.OPEN('IMPORT','SCHEMA', 'DEV11');
DBMS_DATAPUMP.ADD_FILE(handle1, 'StreamImport_1194809001302.log',
'DATA_PUMP_DIR',
'', DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.METADATA_FILTER(handle1, 'SCHEMA_EXPR', 'IN
(''SCOTT'')');
DBMS_DATAPUMP.SET_PARAMETER(handle1, 'INCLUDE_METADATA', 1);
.
.
.
connect streams/password@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORCLDEV)(PORT=1521)))
(CONNECT_DATA=(SID=DG)(SERVER=DEDICATED)))";
set serverout on;
--***********************************************************
--START CAPTURING AT SOURCE AND START APPLYING AT DESTINATION
--***********************************************************
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name => '"SCOTT"',
source_database_name => 'DEV11',
instantiation_scn => 4669145,
recursive => true);
END;
/
DECLARE
v_started number;
BEGIN
.
.
.
11g adds a new feature with Streams for the Remote DBA to quickly see the
source databases and where they are being replicated to. This can
be very beneficial when several databases are being replicated.
Figure 9.10 below shows a simple configuration.
 |
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important
New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J.
Jain, Brian Carr.
You can buy it direct from the publisher
for 30% off. |
