simple stream

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE 环境： OS WIN 7

            DB oracle 11.2.0.1

DBNAME :testorcl,db2

 

1.建立表空间和用户

 a.db2

   sqlplus sys@db2 as sysdba

   create tablespace streams datafile 'f:/app/administrator/oradata/db2/streams01.dbf' size 100m;

   create user strmadmin identified by singrape

   default tablespace streams

   temporary tablespace temp

   quota unlimited on streams;

   grant dba to strmadmin;

   begin

     DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(

          grantee =>'strmadmin',

          grant_privileges => TRUE);

   end;

   /

   create directory strms_dir as 'f:/stream';

grantion grant script.:

  begin

     dbms_streams_auth.grant_admin_privilege(

        grantee => 'strmadmin',

        grant_privileges =>false,

        file_name => 'grant_strms_privs.sql',

        directory_name =>'strms_dir');

    end;

    /

execute grant script.:

spool f:/1.txt

@f:/stream/grant_strms_privs.sql

spool off

b. testorcl

   sqlplus sys@testorcl as sysdba

    create tablespace streams datafile 'f:/app/administrator/oradata/testorcl/streams01.dbf' size 100m;

   create user strmadmin identified by singrape

   default tablespace streams

   temporary tablespace temp

   quota unlimited on streams;

   grant dba to strmadmin;

   begin

     DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(

          grantee =>'strmadmin',

          grant_privileges => TRUE);

   end;

   /

   create directory strms_dir as 'f:/stream';

grantion grant script.:

  begin

     dbms_streams_auth.grant_admin_privilege(

        grantee => 'strmadmin',

        grant_privileges =>false,

        file_name => 'grant_strms_privs.sql',

        directory_name =>'strms_dir');

    end;

    /

execute grant script.:

spool f:/1.txt

@f:/stream/grant_strms_privs.sql

spool off

2. create database link between database s using stream

note1:the name of the database link must match the global name of the source database.

a.db2

sqlplus strmadmin@db2

create database link testorcl connect to strmadmin identified by singrape using 'testorcl';

b.testorcl

sqlplus strmadmin@testorcl

create database link db2 connect to strmadmin identified by singrape using 'db2';

note2:

If an RMAN database instantiation is performed, then the database link at the source database is copied to the destination database during instantiation. This copied database link should be dropped at the destination database. In this case, if the replication is bi-directional, and a database link from the destination database to the source database is required, then this database link should be created after the instantiation.

3.ensuring that each source database is i archive log mode

archive log list

4.setting initialization parameters relevant to oracle streams

a.db2

alter system set global_names=true scope=both;

show parameter log_buffer

show parameter open_links ; this value must be 4 or higher

stream pool size rule

·         15 MB for each capture process parallelism

·         250 MB or more for each buffered queue. The buffered queue is where the buffered messages are stored.

·         1 MB for each apply process parallelism

·         1 MB for each XStream outbound server

·         1 MB for each XStream inbound server parallelism

show parameter timed_statistics;this parameter should be true;

alter system set undo_retention=3600 scope=both; this parameter setting more than 3600 seconds.

b. testorcl

alter system set global_names=true scope=both;

show parameter log_buffer

show parameter open_links ; this value must be 4 or higher

stream pool size rule

·         15 MB for each capture process parallelism

·         250 MB or more for each buffered queue. The buffered queue is where the buffered messages are stored.

·         1 MB for each apply process parallelism

·         1 MB for each XStream outbound server

·         1 MB for each XStream inbound server parallelism

show parameter timed_statistics;this parameter should be true;

alter system set undo_retention=3600 scope=both; this parameter setting more than 3600 seconds.

 

5.configure the oracle stremas pol

·         Messages are enqueued into a buffered queue.

Oracle Streams components manipulate messages in a buffered queue. These components include capture processes, propagations, apply processes, XStream outbound servers, and XStream inbound servers. Also, Data Pump export and import operations initialize the Oracle Streams pool because these operations use buffered queues.

·         Messages are dequeued from a persistent queue in a configuration that does not use Oracle Real Application Clusters (Oracle RAC).

The Oracle Streams pool is used to optimize dequeue operations from persistent queues. The Oracle Streams pool is not used to optimize dequeue operations from persistent queues in an Oracle RAC configuration.

·         A capture process is started.

·         A propagation is created.

·         An apply process is started.

·         An XStream outbound server is started.

·         An XStream inbound server is started.

6.supplemental logging

The following procedures in the DBMS_CAPTURE_ADM package automatically specify supplemental logging:

·         BUILD

·         PREPARE_GLOBAL_INSTANTIATION

·         PREPARE_SCHEMA_INSTANTIATION

·         PREPARE_TABLE_INSTANTIATION

The BUILD procedure automatically specifies database supplemental logging by running the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement. In most cases, the BUILD procedure is run automatically when a capture process is created.

The PREPARE_GLOBAL_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, and PREPARE_TABLE_INSTANTIATION procedures automatically specify supplemental logging of the primary key, unique key, bitmap index, and foreign key columns in the tables prepared for instantiation.

Certain procedures in the DBMS_STREAMS_ADM package automatically run a procedure listed previously. See "DBMS_STREAMS_ADM Package Procedures Automatically Prepare Objects" for information.

7.run the maintain_schemas to configure schema level stream

a. testorcl

create user test identified by singrape

default tablespace test

quota unlimited on test;

create directory source_directory as 'f:/source';

b. db2

create user test identified by singrape

default tablespace test

qutoa unlimited on test;

create directory dest_directory as 'f:/dest';

 

IN SOURCE DATABASE run following procedure.

alter system set job_queue_processes=10 scope=both;

begin

         dbms_streams_adm.maintain_schemas(

           schema_names => 'test',

          source_directory_object =>'source_directory',

           destination_directory_object =>'dest_directory',

           source_database=>'testorcl',

           destination_database=>'db2',

           bi_directional =>false);

end ;

/

The MAINTAIN_SCHEMAS procedure can take some time to run because it is performing many configuration tasks. Do not allow data manipulation language (DML) or data definition language (DDL) changes to the replicated database objects at the destination database while the procedure is running.

When a configuration procedure is run, information about its progress is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then see Oracle Streams Replication Administrator's Guide for instructions about using the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package to recover from these errors.

 

转载于:http://blog.itpub.net/9482301/viewspace-765159/