Configuring streams on Oracle 10g Database [Unidirectional ]
=================================================================
1.Some useful DB parameter which need to be set .
> COMPATIBLE
> GLOBAL_NAMES
> JOB_QUEUE_PROCESSES
> STREAMS_POOL_SIZE
2.Create the "stradmin" user on both the source and destination database and grant the necessary privileges.
SQL> create user stradmin identified by stradmin;
SQL>grant connect, DBA, imp_full_database, exp_full_database, aq_administrator_role to stradmin;
SQL>exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRADMIN');
As an option, it is possible to create a separate tablespace for each streams administrator schema (STRADMIN) at each participating Streams
database.This tablespace will be used for any objects created in the streams administrator schema, including any spillover of messages from
the in-memory queue.
SQL> Create tablespace streams datafile ‘/disk1/oradata/prod/streams.dbf’ size 40m autoextend on;
SQL> alter user streams default tablespace streams;
3. Source Database
========================
Turn on Supplemental logging at database level, or table level.
connect SYS/password as SYSDBA
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (primary key, unique, foreign key) COLUMNS;
SQL>ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP pk_emp (id) ALWAYS;
select log_group_name, table_name from dba_log_groups where owner='SCOTT';
4.Create DB link
====================
Create Database Links between the stream administrator users in the 2 databases.
SQL> CREATE DATABASE LINK TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO STREAMADMIN IDENTIFIED BY streamadmin USING 'TEST_REP2';
Database link created.
5. Create the 2- Queues , one is for capture and second one for apply
===================================================================
Create Stream Queues under the streamadmin user to apply and capture the database changes to be replicated.
This also needs to run on both the databases as streamadmin.
SQL> show user
USER is "STREAMADMIN"
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'apply_q', queue_name => 'apply_q', queue_user => 'streamadmin');
PL/SQL procedure successfully completed.
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'capture_q',queue_name => 'capture_q',queue_user => 'streamadmin');
PL/SQL procedure successfully completed.
6. Create schema rule on Capture source DB
=======================================
conn streams administrator/password@Source database
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'schema to be streamed',
streams_type => 'Capture',
streams_name => 'streams_Capture',
queue_name => 'streams_capture_queue',
include_dml => true,
include_ddl => true,
source_database => 'source database db link');
END;
/
7.Create the capture process
===============================
begin
dbms_capture_adm.create_capture(
queue_name => 'STRMADMIN.MAIN_OT_QUEUE',
capture_name => 'CAPTURE_MAIN',
rule_set_name => 'STRMADMIN.RULESET$_13',
first_scn => 1103875820);
end;
/
8. Creating propagation process on source DB
==============================================
BEGIN
DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
propagation_name => 'strm02_propagation',
source_queue => 'strmadmin.strm03_queue',
destination_queue => 'strmadmin.strm04_queue',
destination_dblink => 'dbs2.example.com',
rule_set_name => 'strmadmin.strm01_rule_set',
queue_to_queue => TRUE);
END;
/
9. Creating the apply process on destination DB
===============================================
BEGIN
DBMS_APPLY_ADM.CREATE_APPLY(
queue_name => 'strm03_queue',
apply_name => 'strm03_apply',
rule_set_name => 'strmadmin.strm03_rule_set',
message_handler => NULL,
ddl_handler => 'strmadmin.history_ddl',
apply_user => 'hr',
apply_database_link => NULL,
apply_tag => HEXTORAW('5'),
apply_captured => true,
negative_rule_set_name => NULL,
source_database => 'dbs1.net');
END;
/
10 .Doing Data exp/imp
===========================
Export, import and instantiation of tables from Source to Destination Database
If the objects are not present in the destination database, perform an export of the objects from the source database and import them
into the destination database Export from the Source Database:
Specify the OBJECT_CONSISTENT=Y clause on the export command. By doing this, an export is performed that is consistent for each individual
object at a particular system change number (SCN).
exp USERID=SYSTEM/manager@prod.com OWNER=SCOTT FILE=scott.dmp LOG=exportTables.log OBJECT_CONSISTENT=Y STATISTICS = NONE
Import into the Destination Database:
Specify STREAMS_INSTANTIATION=Y clause in the import command. By doing this, the streams metadata is updated with the appropriate
information in the destination database corresponding to the SCN that is recorded in the export file.
imp USERID=SYSTEM@prod2.com FULL=Y CONSTRAINTS=Y FILE=scott.dmp IGNORE=Y COMMIT=Y LOG=importTables.log STREAMS_INSTANTIATION=Y
If the objects are already present in the desination database, there re two ways of instanitating the objects at the destination site.
1. By means of Metadata-only export/import :
Specify ROWS=N during Export
Specify IGNORE=Y during Import along with above import parameters.
2. By Manaually instantiating the objects
Get the Instantiation SCN at the source database:
connect STRADMIN/STRADMIN@source
set serveroutput on
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
END;
Instantiate the objects at the destination database with this SCN value. The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table are to be applied by the apply process. If the commit SCN of an LCR from the source database is less than or equal to this instantiation SCN, then the apply process discards the LCR. Else, the apply process applies the LCR.
connect STRADMIN/STRADMIN@destination
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
SOURCE_SCHEMA_NAME => 'SCOTT',
source_database_name => 'prod.com',
instantiation_scn => &iscn );
END;
Enter value for iscn:65876799945
now start all the process
=================================================================
1.Some useful DB parameter which need to be set .
> COMPATIBLE
> GLOBAL_NAMES
> JOB_QUEUE_PROCESSES
> STREAMS_POOL_SIZE
2.Create the "stradmin" user on both the source and destination database and grant the necessary privileges.
SQL> create user stradmin identified by stradmin;
SQL>grant connect, DBA, imp_full_database, exp_full_database, aq_administrator_role to stradmin;
SQL>exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRADMIN');
As an option, it is possible to create a separate tablespace for each streams administrator schema (STRADMIN) at each participating Streams
database.This tablespace will be used for any objects created in the streams administrator schema, including any spillover of messages from
the in-memory queue.
SQL> Create tablespace streams datafile ‘/disk1/oradata/prod/streams.dbf’ size 40m autoextend on;
SQL> alter user streams default tablespace streams;
3. Source Database
========================
Turn on Supplemental logging at database level, or table level.
connect SYS/password as SYSDBA
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (primary key, unique, foreign key) COLUMNS;
SQL>ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP pk_emp (id) ALWAYS;
select log_group_name, table_name from dba_log_groups where owner='SCOTT';
4.Create DB link
====================
Create Database Links between the stream administrator users in the 2 databases.
SQL> CREATE DATABASE LINK TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO STREAMADMIN IDENTIFIED BY streamadmin USING 'TEST_REP2';
Database link created.
5. Create the 2- Queues , one is for capture and second one for apply
===================================================================
Create Stream Queues under the streamadmin user to apply and capture the database changes to be replicated.
This also needs to run on both the databases as streamadmin.
SQL> show user
USER is "STREAMADMIN"
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'apply_q', queue_name => 'apply_q', queue_user => 'streamadmin');
PL/SQL procedure successfully completed.
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'capture_q',queue_name => 'capture_q',queue_user => 'streamadmin');
PL/SQL procedure successfully completed.
6. Create schema rule on Capture source DB
=======================================
conn streams administrator/password@Source database
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'schema to be streamed',
streams_type => 'Capture',
streams_name => 'streams_Capture',
queue_name => 'streams_capture_queue',
include_dml => true,
include_ddl => true,
source_database => 'source database db link');
END;
/
7.Create the capture process
===============================
begin
dbms_capture_adm.create_capture(
queue_name => 'STRMADMIN.MAIN_OT_QUEUE',
capture_name => 'CAPTURE_MAIN',
rule_set_name => 'STRMADMIN.RULESET$_13',
first_scn => 1103875820);
end;
/
8. Creating propagation process on source DB
==============================================
BEGIN
DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
propagation_name => 'strm02_propagation',
source_queue => 'strmadmin.strm03_queue',
destination_queue => 'strmadmin.strm04_queue',
destination_dblink => 'dbs2.example.com',
rule_set_name => 'strmadmin.strm01_rule_set',
queue_to_queue => TRUE);
END;
/
9. Creating the apply process on destination DB
===============================================
BEGIN
DBMS_APPLY_ADM.CREATE_APPLY(
queue_name => 'strm03_queue',
apply_name => 'strm03_apply',
rule_set_name => 'strmadmin.strm03_rule_set',
message_handler => NULL,
ddl_handler => 'strmadmin.history_ddl',
apply_user => 'hr',
apply_database_link => NULL,
apply_tag => HEXTORAW('5'),
apply_captured => true,
negative_rule_set_name => NULL,
source_database => 'dbs1.net');
END;
/
10 .Doing Data exp/imp
===========================
Export, import and instantiation of tables from Source to Destination Database
If the objects are not present in the destination database, perform an export of the objects from the source database and import them
into the destination database Export from the Source Database:
Specify the OBJECT_CONSISTENT=Y clause on the export command. By doing this, an export is performed that is consistent for each individual
object at a particular system change number (SCN).
exp USERID=SYSTEM/manager@prod.com OWNER=SCOTT FILE=scott.dmp LOG=exportTables.log OBJECT_CONSISTENT=Y STATISTICS = NONE
Import into the Destination Database:
Specify STREAMS_INSTANTIATION=Y clause in the import command. By doing this, the streams metadata is updated with the appropriate
information in the destination database corresponding to the SCN that is recorded in the export file.
imp USERID=SYSTEM@prod2.com FULL=Y CONSTRAINTS=Y FILE=scott.dmp IGNORE=Y COMMIT=Y LOG=importTables.log STREAMS_INSTANTIATION=Y
If the objects are already present in the desination database, there re two ways of instanitating the objects at the destination site.
1. By means of Metadata-only export/import :
Specify ROWS=N during Export
Specify IGNORE=Y during Import along with above import parameters.
2. By Manaually instantiating the objects
Get the Instantiation SCN at the source database:
connect STRADMIN/STRADMIN@source
set serveroutput on
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
END;
Instantiate the objects at the destination database with this SCN value. The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table are to be applied by the apply process. If the commit SCN of an LCR from the source database is less than or equal to this instantiation SCN, then the apply process discards the LCR. Else, the apply process applies the LCR.
connect STRADMIN/STRADMIN@destination
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
SOURCE_SCHEMA_NAME => 'SCOTT',
source_database_name => 'prod.com',
instantiation_scn => &iscn );
END;
Enter value for iscn:65876799945
now start all the process
No comments:
Post a Comment