linux oracle流复制,配置Oracle 10g 双向流复制

如何设置:

select * from v$streams_pool_advice;

/* 查询v$streams_pool_advice视图了解不同streams_Pool_size情况下的estd_spill_time */

alter system set streams_pool_size=500M;

完成以上设置后建议重启实例以便让全部参数生效，2边都要做。

4.为scott schema下的对象创建追加日志(supplemental log)，可以使用dbms_capture_adm包的prepare_schema_instantiation存储过程为指定模式创建追加日志:

NAME

prepare_schema_instantiation()

FUNCTION

prepare a schema for instantiation

PARAMETERS

schema_name            - (IN)  the name of the schema to prepare

supplemental_logging   - (IN)  supplemental logging level

('NONE', 'KEYS', or 'ALL')

NOTES

KEYS means PRIMARY KEY, UNIQUE INDEX, and FOREIGN KEY levels combined.

----------------------------------------------------------------------------*/

PROCEDURE prepare_schema_instantiation(

schema_name            IN VARCHAR2,

supplemental_logging   IN VARCHAR2 DEFAULT 'KEYS');

http://www.Oracledatabase12g.com/archives/how-setup-oracle-bi-directional-streams.html

/* 其默认supplemental_logging选项为Key，即为PRIMARY KEY, UNIQUE INDEX, and FOREIGN KEY等键

创建IMPLICIT的追加日志 */

/* 在10g或以上版本中prepare_xxx_instantiation存储过程也会隐式地创建追加日志组了

(In versions 10g and above,prepare_xxx_instantiation procedure implicitly creates supplemenal log groups.

Type of supplemental logging that is enabled implicitly using this command can be checked

using the sql in the following link to the documentation. However, additional supplemental

logging might need to be enabled depending on the requirements as mentioned above)。

可以通过以下查询了解其追加日志组的属性:

SELECT SCHEMA_NAME,

SUPPLEMENTAL_LOG_DATA_PK log_pk,

SUPPLEMENTAL_LOG_DATA_FK log_fk,

SUPPLEMENTAL_LOG_DATA_UI log_ui,

SUPPLEMENTAL_LOG_DATA_ALL log_all

FROM DBA_CAPTURE_PREPARED_SCHEMAS;

SCHEMA_NAME                    LOG_PK   LOG_FK   LOG_UI   LOG_ALL

------------------------------ -------- -------- -------- --------

SCOTT                          IMPLICIT IMPLICIT IMPLICIT NO

5.

在源库上创建到目标库strmadmin用户的database link:

conn strmadmin/strmadmin;

Connected.

create database link clinicb.rh3.oracle.com connect to strmadmin identified by strmadmin using 'clinicb.rh3.oracle.com';

Database link created.

/* 其中clinicb.rh3.oracle.com为目标库的全局数据库名，clinicb为db_name，rh3.oracle.com为domain_name */

在目标库上创建到源库strmadmin用户的database link:

conn strmadmin/strmadmin;

Connected.

create database link clinica.rh2.oracle.com connect to strmadmin identified by strmadmin using 'clinica.rh2.oracle.com';

Database link created.

/* 其中clinica.rh2.oracle.com为源库的全局数据库名，clinica为数据库名，rh2.oracle.com为domain_name */

6.

在源库中分别为capture和apply创建队列queue:

begin

dbms_streams_adm.set_up_queue(

queue_table => 'apply_srctab',

queue_name => 'apply_src',

queue_user => 'strmadmin');

end;

/

PL/SQL procedure successfully completed.

begin

dbms_streams_adm.set_up_queue(

queue_table => 'capture_srctab',

queue_name => 'capture_src',

queue_user => 'strmadmin');

end;

/

PL/SQL procedure successfully completed.

在目标库分别为capture和apply创建队列queue:

conn strmadmin/strmadmin@clinicb.rh3.oracle.com

Connected.

begin

dbms_streams_adm.set_up_queue(

queue_table => 'apply_desttab',

queue_name => 'apply_dest',

queue_user => 'strmadmin');

end;

/

PL/SQL procedure successfully completed.

begin

dbms_streams_adm.set_up_queue(

queue_table => 'capture_desttab',

queue_name => 'capture_dest',

queue_user => 'strmadmin');

end;

/

PL/SQL procedure successfully completed.

8.

在源库clinica上为scott模式创建capture process:

conn strmadmin/strmadmin@clinica.rh2.oracle.com

Connected.

begin

dbms_streams_adm.add_schema_rules (

schema_name => 'scott',

streams_type => 'capture',

streams_name => 'captures_src',

queue_name => 'capture_src',

include_dml => true,

include_ddl => true,

inclusion_rule => true);

end;

/

PL/SQL procedure successfully completed.

9.

在源库clinica上创建apply process:

conn strmadmin/strmadmin@clinica.rh2.oracle.com

Connected.

begin

dbms_streams_adm.add_schema_rules (

schema_name => 'scott',

streams_type => 'apply',

streams_name => 'applys_src',

queue_name => 'apply_src',

include_dml => true,

include_ddl => true,

source_database => 'clinicb.rh3.oracle.com');

end;

/

PL/SQL procedure successfully completed.