|
1.
Oracle Streams Overview
Oracle
Streams enables the sharing of data and events in a data stream, either
within a database or from one database to another.
The
replication using streams is implemented in the following way:
-
A
background process is configured to capture changes made to tables,
schemas or the entire database. This process captures changes from
the redo log (using logminer) and formats each captured
change into a logical change record (LCR).
-
The
capture process enqueues LCR events into a queue
-
This
queue is scheduled to send events from one queue to another in a
different database (or the same)
-
A
background process dequeues the events and applies them at the
destination database.
NOTE:
In my example I will replicate the SCOTT.EMP in PAUL schema, in the same
database.
2.
Implementing Streams: set the correct init.ora parameters related
to streams
| Parameter |
Value |
Comment |
| COMPATIBLE |
10.2.0 |
I use Oracle 10.2.0 version |
| GLOBAL_NAMES |
TRUE |
Changes captured by the Streams capture process
automatically include the current global name of the source
database. If the global name must be modified on the
database, do it at a time when NO user changes are possible on
the database so that the Streams configuration can be recreated. |
| JOB_QUEUE_PROCESSES |
> 0 |
DBMS_JOB could be used. |
| AQ_TM_PROCESSES |
> 1 |
Setting the parameter to 1 or more starts the
specified number of queue monitor processes. |
| LOGMNR_MAX_PERSISTENT_SESSIONS
(Was deprecated in release 10.2) |
> 1 |
This parameter specifies the maximum number of
persistent LOGMINER mining sessions.
(Was deprecated in release 10.2: In my example I will not use
it.) |
| LOG_PARALLELISM
(Was deprecated in release 10.2) |
1 |
This parameter must be set to 1 at each database
that captures events.
(Was deprecated in release 10.2: In my example I will not use
it.) |
| PARALLEL_MAX_SERVERS
(Was deprecated in release 10.2) |
4 |
Each capture process and apply process may use
multiple parallel execution servers. The apply process by
default needs two parallel servers.
(Was deprecated in release 10.2: In my example I will not use
it.) |
| SHARED_POOL_SIZE |
|
Each capture process needs 10MB of shared pool
space (Streams is limited to using a maximum of 10% of the
shared pool). So shared_pool_size has to be set to at least
100MB. If you wish to run multiple capture processes, then this
parameter needs to be set to an even higher value. |
| OPEN_LINKS |
> 4 |
Specifies the maximum number of concurrent open
connections to remote databases in one session. Ensure that it
is set to 4 or higher. In my example is no present because the
replication is done on the same database (in scott
schema). |
|
NOTE: The databases involved in Streams
must be running in ARCHIVELOG mode. |
Here
is the initdb10.ora initialization file I use for this case (this
must be done on each side):
compatible='10.2.0.1.0'
global_names=TRUE
job_queue_processes=10
AQ_TM_PROCESSES
= 1
db10.__java_pool_size=4194304
db10.__large_pool_size=4194304
db10.__shared_pool_size=120497472
db10.__streams_pool_size=0
audit_file_dest='F:/admin/db10/adump'
background_dump_dest='F:/admin/db10/bdump'
control_files='F:\oradata\db10\control01.ctl','F:\oradata\db10\control02.ctl','F:\oradata\db10\control03.ctl'
core_dump_dest='F:/admin/db10/cdump'
db_block_size=8192
db_domain=''
db_file_multiblock_read_count=16
db_name='db10'
db_recovery_file_dest='F:/flash_recovery_area'
db_recovery_file_dest_size=2147483648
open_cursors=300
pga_aggregate_target=16777216
processes=150
remote_login_passwordfile='EXCLUSIVE'
sga_target=250772160
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='F:/admin/db10/udump'
3.
Preparing the Destination Database (in my case this is done on the same
database as the source database)
3.1
Create Streams Administrator (connected as SYS or SYSTEM)
create user STRMADMIN
identified by STRMADMIN;
3.2
Grant the necessary privileges to the Streams Administrator (connected
as SYS)
GRANT
CONNECT,
DBA, RESOURCE,
AQ_ADMINISTRATOR_ROLE to
STRMADMIN;
GRANT
SELECT ANY
DICTIONARY
TO
STRMADMIN;
GRANT
EXECUTE ON
DBMS_AQ TO
STRMADMIN;
GRANT
EXECUTE ON
DBMS_AQADM TO
STRMADMIN;
GRANT
EXECUTE ON
DBMS_FLASHBACK TO
STRMADMIN;
GRANT
EXECUTE ON
DBMS_STREAMS_ADM TO
STRMADMIN;
GRANT
EXECUTE ON
DBMS_CAPTURE_ADM TO
STRMADMIN;
GRANT
EXECUTE ON
DBMS_APPLY_ADM TO
STRMADMIN;
GRANT
EXECUTE ON
DBMS_RULE_ADM TO
STRMADMIN;
GRANT
EXECUTE ON
DBMS_PROPAGATION_ADM TO
STRMADMIN;
BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege
=> 'ENQUEUE_ANY',
grantee
=> 'STRMADMIN',
admin_option
=> FALSE);
DBMS_AQADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> 'DEQUEUE_ANY',
grantee
=> 'STRMADMIN',
admin_option
=> FALSE);
DBMS_AQADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> 'MANAGE_ANY',
grantee
=> 'STRMADMIN',
admin_option
=> TRUE);
DBMS_RULE_ADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee
=> 'STRMADMIN',
grant_option
=> TRUE);
DBMS_RULE_ADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee
=> 'STRMADMIN',
grant_option
=> TRUE);
DBMS_RULE_ADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee
=> 'STRMADMIN',
grant_option
=> TRUE);
DBMS_RULE_ADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee
=> 'STRMADMIN',
grant_option
=> TRUE);
DBMS_RULE_ADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee
=> 'STRMADMIN',
grant_option
=> TRUE);
DBMS_RULE_ADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee
=> 'STRMADMIN',
grant_option
=> TRUE);
DBMS_RULE_ADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee
=> 'STRMADMIN',
grant_option
=> TRUE);
DBMS_RULE_ADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee
=> 'STRMADMIN',
grant_option
=> TRUE);
DBMS_RULE_ADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee
=> 'STRMADMIN',
grant_option
=> TRUE);
DBMS_RULE_ADM .GRANT_SYSTEM_PRIVILEGE(
privilege
=> DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee
=> 'STRMADMIN',
grant_option
=> TRUE);
END;
/
3.3
Create streams queue (connected as STRMADMIN)
BEGIN
DBMS_STREAMS_ADM .SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
queue_name => 'STREAMS_QUEUE',
queue_user => 'STRMADMIN');
END;
3.4
Add apply rules for the table at the destination database (connected as
STRMADMIN)
BEGIN
DBMS_STREAMS_ADM .ADD_TABLE_RULES(
table_name
=> 'PAUL.EMP',
streams_type
=> 'APPLY',
streams_name
=> 'STRMADMIN_APPLY',
queue_name
=> 'STRMADMIN.STREAMS_QUEUE',
include_dml
=> true,
include_ddl
=> true,
source_database
=> 'PRD1');
END;
3.5
Specify an 'APPLY USER' at the destination database (connected as
STRMADMIN)
This
is the user who would apply all DML statements and DDL statements. The
user specified in the APPLY_USER parameter must have the necessary
privileges to perform DML and DDL changes on the apply objects. I
choose the owner of the target table as 'APPLY USER'.
BEGIN
DBMS_APPLY_ADM .ALTER_APPLY(
apply_name =>
'STRMADMIN_APPLY',
apply_user =>
'PAUL');
END;
3.6
Set DISABLE_ON_ERROR parameter to 'N' (optional, connected as
STRMADMIN)
When
set to 'N', the apply process will not abort for any error that it
encounters, but the error details would be logged in DBA_APPLY_ERROR.
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'STRMADMIN_APPLY',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );
END;
3.7
Start the Apply process (connected as STRMADMIN)
BEGIN
DBMS_APPLY_ADM .START_APPLY(apply_name
=> 'STRMADMIN_APPLY');
END;
4.
Preparing the Source Database
4.1
Move LogMiner tables from SYSTEM tablespace (connected as SYS)
By
default, all LogMiner tables are created in the SYSTEM tablespace. It
is a good practice to create an alternate tablespace for the LogMiner
tables.
CREATE
TABLESPACE
LOGMNRTS
DATAFILE
'c:\oradata\logmnrts.dbf' SIZE
50M AUTOEXTEND
ON MAXSIZE
UNLIMITED;
BEGIN
DBMS_LOGMNR_D .SET_TABLESPACE
('LOGMNRTS');
END;
4.2
Turn on supplemental logging for EMP table (connected as SYS)
ALTER
TABLE scott.EMP
ADD SUPPLEMENTAL
LOG GROUP
emp_pk(empno)
ALWAYS;
4.3
Create Streams Administrator and Grant the necessary privileges
I
use the same database 3.1, 3.2 already done on source database (In my
case source database = target database)
4.4
Create a database link to the destination database
connected
as STRMADMIN:
SQL>
create
database
link
"PRD2.REGRESS.RDBMS.DEV.US.ORACLE.COM"
connect
to
PAUL
identified
by
paul
using
'PRD2';
4.5
Create streams queue (connected as STRMADMIN)
BEGIN
DBMS_STREAMS_ADM .SET_UP_QUEUE
(
queue_name
=> 'STREAMS_QUEUE',
queue_table
=>'STREAMS_QUEUE_TABLE',
queue_user
=> 'STRMADMIN');
END;
4.6
Add capture rules for the table at the source database (connected as
STRMADMIN)
BEGIN
DBMS_STREAMS_ADM .ADD_TABLE_RULES(
table_name
=> 'SCOTT.EMP',
streams_type
=> 'CAPTURE',
streams_name
=> 'STRMADMIN_CAPTURE',
queue_name
=> 'STRMADMIN.STREAMS_QUEUE',
include_dml
=> true,
include_ddl
=> true,
source_database
=> 'PRD1');
END;
4.7
Add propagation rules for the table at the source database (connected as
STRMADMIN)
This
step will also create a propagation job to the destination database.
BEGIN
DBMS_STREAMS_ADM.add_table_propagation_rules
(table_name => 'SCOTT.EMP',
streams_name => 'STRMADMIN_PROPAGATE',
source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
destination_queue_name
=>
'STRMADMIN.STREAMS_QUEUE@PRD2.REGRESS.RDBMS.DEV.US.ORACLE.COM',
include_dml => TRUE,
include_ddl => TRUE,
source_database =>
'PRD1'
);
END;
5.
Export/ Import the table
6.
Manually Instantiate the table at the destination database (conditional)
7.
Start the Capture Process
More information
about this subject (
How to replicate a table with Oracle Streams ) you can get from
www.in-oracle.com
|