ITPub博客

首页 > Linux操作系统 > Linux操作系统 > STRM-Configure oracle 9i Streams replication step by step

STRM-Configure oracle 9i Streams replication step by step

原创 Linux操作系统 作者:vongates 时间:2019-03-22 10:33:06 0 删除 编辑
在9i 中推出了streams replication这个新的HA架构。但在9i中很少有实施此新特性在生产环境中的。因为新的需求最近需要测试一些报告出来。下面帖上作业的步骤:

oracle 9i stream replication step by step
Version: 9206
source db name: gates
destination db name: clone

1. Configure instance initional file (All streams instances)
alter system set job_queue_processes=1 scope=both;
alter system set aq_tm_processes=1 scope=both;
alter system set global_names=true scope=both;

2 Archive log (source db is needed configure archivelog mode)
alter system set log_archive_start=true scope=spfile;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

3 Configure streams administrator @source db
conn
sys/oracle@gates as sysdba

create user strmadmin identified by oracle
default tablespace tools;
grant connect, resource, select_catalog_role to strmadmin;

grant execute on dbms_aqadm to strmadmin;
grant execute on dbms_capture_adm to strmadmin;
grant execute on dbms_propagation_adm to strmadmin;
grant execute on dbms_streams_adm to strmadmin;
grant execute on dbms_apply_adm to strmadmin;
grant execute on dbms_flashback to strmadmin;

conn sys/oracle@gates as sysdba
begin
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.create_rule_set_obj,
grantee => 'strmadmin',
grant_option => false);
end;
/
begin
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.create_rule_obj,
grantee => 'strmadmin',
grant_option => false);
end;
/
connect
strmadmin/oracle@gates
exec dbms_streams_adm.set_up_queue();--@destination DB the strmadmin is must exist
create database link clone.vongates.com connect to strmadmin identified by oracle using 'clone'

4 Configure streams administrator @destination db
conn
sys/oracle@clone as sysdba

create user strmadmin identified by oracle
default tablespace tools;
grant connect, resource, select_catalog_role to strmadmin;

grant execute on dbms_aqadm to strmadmin;
grant execute on dbms_capture_adm to strmadmin;
grant execute on dbms_propagation_adm to strmadmin;
grant execute on dbms_streams_adm to strmadmin;
grant execute on dbms_apply_adm to strmadmin;
grant execute on dbms_flashback to strmadmin;

conn sys/oracle@clone as sysdba
begin
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.create_rule_set_obj,
grantee => 'strmadmin',
grant_option => false);
end;
/
begin
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.create_rule_obj,
grantee => 'strmadmin',
grant_option => false);
end;
/
connect
strmadmin/oracle@clone;
exec dbms_streams_adm.set_up_queue();
--@source DB the strmadmin is must exist
create database link gates.vongates.com connect to strmadmin identified by oracle using 'gates'

5 Create replication schema and objects @source & distination db
connect / as sysdba;
create user scott identified by tiger default tablespace tools;
grant connect,resource to scott;

connect scott/tiger;
create table dept(id int primary key,name varchar2(20));
grant all on dept to strmadmin;

6 Create LogMinor Tablespace @source db
conn
sys/oracle@gates as sysdba;
CREATE TABLESPACE logmnrstrm DATAFILE 'c:oradatagateslogmnrstrm01.dbf'
SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
execute dbms_logmnr_d.set_tablespace('logmnrstrm');

7 Supplemental Logging at db or objects level @source db
conn
sys/oracle@gates as sysdba;
alter table scott.dept add supplemental log group log_group_dept_pk (id) always;

8 Configure Propagation Process @source DB
connect
strmadmin/oracle@gates
begin
dbms_streams_adm.add_table_propagation_rules(
table_name => 'scott.dept',
streams_name => 'gates_to_clone',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name =>
'strmadmin.streams_queue@clone',
include_dml => true,
include_ddl => true,
source_database => 'gates');
end;
/

select schema_user,job,
to_char(last_date, 'dd-mon-yyyy hh24:mi:ss') last_date,
to_char(next_date, 'dd-mon-yyyy hh24:mi:ss') next_date,
what
from dba_jobs;

9 Configure Capture Process @source DB
connect
strmadmin/oracle@gates
begin
dbms_streams_adm.add_table_rules(
table_name => 'scott.dept',
streams_type => 'capture',
streams_name => 'capture_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true);
end;
/
10 Configure Instantiation SCN by imp and exp tools
exp
userid=scott/tiger@gates FILE='c:dept_instant.dmp' TABLES=dept OBJECT_CONSISTENT=y ROWS=n
imp
userid=scott/tiger@clone FILE='c:dept_instant.dmp' IGNORE=y COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y FULL=Y

conn sys/oracle@clone as sysdba
alter table scott.dept drop supplemental log group log_group_dept_pk;

11 Alternatively the instantiation SCN can be set using the DBMS_APPLY_ADM package @source DB
connect
strmadmin/oracle@gates
declare
v_scn number;
begin
v_scn := dbms_flashback.get_system_change_number();
clonedbms_apply_adm.set_table_instantiation_scn@(
source_object_name => 'scott.dept',
source_database_name => 'gates',
instantiation_scn => v_scn);
end;
/
12 Configure Apply Process @destination db
connect
strmadmin/oracle@clone;
begin
dbms_streams_adm.add_table_rules(
table_name => 'scott.dept',
streams_type => 'apply',
streams_name => 'apply_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'gates');
end;
/
13 Start Apply Process @destination db
connect
strmadmin/oracle@clone;
begin
dbms_apply_adm.set_parameter(
apply_name => 'apply_simp',
parameter => 'disable_on_error',
value => 'n');
dbms_apply_adm.start_apply(
apply_name => 'apply_simp');
end;
/
14 Start Capture Process @source DB
connect
strmadmin/oracle@gates
begin
dbms_capture_adm.start_capture(
capture_name => 'capture_simp');
end;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29987/viewspace-51945/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-09-11

  • 博文量
    448
  • 访问量
    290112