2013年（350）
分类： Oracle
2013-04-27 09:44:19
本节演示添加一个对象到现有的复制环境中，延续第4或第5步中创建的环境，将jssweb站点中的usr_user表也复制到jssbak中。
１、 停止jssbak端的apply进程JSSBAK> EXEC DBMS_APPLY_ADM.STOP_APPLY('JSSBAK_APPLY');
PL/SQL procedure successfully completed.JSSBAK> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
3 table_name => 'member.usr_user',
4 streams_type => 'apply',
5 streams_name => 'jssbak_apply',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'jssweb.jss.cn',
10 inclusion_rule => true);
11 END;
12 /
PL/SQL procedure successfully completed.JSSSTR> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
3 table_name => 'member.usr_user',
4 streams_name => 'str_to_bak',
5 source_queue_name => 'strmadmin.streams_queue',
6 destination_queue_name => 'strmadmin.streams_queue@jssbak.jss.cn',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'jssweb.jss.cn',
10 inclusion_rule => true);
11 END;
12 /
PL/SQL procedure successfully completed.JSSWEB> EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('member.usr_user');
PL/SQL procedure successfully completed.JSSBAK> SET SERVEROUTPUT ON
JSSBAK> DECLARE
2 h1 NUMBER; -- Data Pump job handle
3 sscn NUMBER; -- Variable to hold current source SCN
4 job_state VARCHAR2(30); -- To keep track of job state
5 js ku$_JobStatus; -- The job status from GET_STATUS
6 sts ku$_Status; -- The status object returned by GET_STATUS
7 job_not_exist exception;
8 pragma exception_init(job_not_exist, -31626);
9 BEGIN
10 -- Create a (user-named) Data Pump job to do a table-level import.
11 h1 := DBMS_DATAPUMP.OPEN(
12 operation => 'IMPORT',
13 job_mode => 'TABLE',
14 remote_link => 'jssweb.jss.cn',
15 job_name => 'dp_sing3');
16 -- A metadata filter is used to specify the schema that owns the tables
17 -- that will be imported.
18 DBMS_DATAPUMP.METADATA_FILTER(
19 handle => h1,
20 name => 'SCHEMA_EXPR',
21 value => '=''MEMBER''');
22 -- A metadata filter is used to specify the tables that will be imported.
23 DBMS_DATAPUMP.METADATA_FILTER(
24 handle => h1,
25 name => 'NAME_EXPR',
26 value => 'IN(''USR_USER'')');
27 -- Get the current SCN of the source database, and set the FLASHBACK_SCN
28 -- parameter to this value to ensure consistency between all of the
29 -- objects included in the import.
30 sscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@jssweb.jss.cn();
31 DBMS_DATAPUMP.SET_PARAMETER(
32 handle => h1,
33 name => 'FLASHBACK_SCN',
34 value => sscn);
35 -- Start the job.
36 DBMS_DATAPUMP.START_JOB(h1);
37 -- The import job should be running. In the following loop, the job
38 -- is monitored until it completes.
39 job_state := 'UNDEFINED';
40 BEGIN
41 WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP
42 sts:=DBMS_DATAPUMP.GET_STATUS(
43 handle => h1,
44 mask => DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR +
45 DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS +
46 DBMS_DATAPUMP.KU$_STATUS_WIP,
47 timeout => -1);
48 js := sts.job_status;
49 DBMS_LOCK.SLEEP(10);
50 job_state := js.state;
51 END LOOP;
52 -- Gets an exception when job no longer exists
53 EXCEPTION WHEN job_not_exist THEN
54 DBMS_OUTPUT.PUT_LINE('Data Pump job has completed');
55 DBMS_OUTPUT.PUT_LINE('Instantiation SCN: ' ||sscn);
56 END;
57 END;
58 /
Data Pump job has completed
Instantiation SCN: 679231
PL/SQL procedure successfully completed.JSSBAK> EXEC DBMS_APPLY_ADM.START_APPLY('jssbak_apply');
PL/SQL procedure successfully completed.首先在jssweb端操作member.usr_user表，插入一条记录：
JSSWEB> insert into member.usr_user values (21,'ANN');
1 row created.
JSSWEB> commit;
Commit complete.
JSSWEB> alter system switch logfile;
System altered.查询jssstr端是否同步：
JSSWEB> select * from member.users@jssstr.jss.cn;
USER_ID USERNAME
---------- ------------------------------
10 ALLEN
20 SMITH
21 ANN疑惑？为啥这里要查询users?黑黑，因为前面配置环境时做了指向嘛！
然后连接到jssbak端，查询member.usr_user表：
JSSBAK> select * from member.usr_user;
USER_ID USERNAME
---------- ------------------------------
21 ANN
10 ALLEN
20 SMITH同步成功鸟。
通过前面的演示，相信大家已经对的复制原理认识更加深入，看完前面的示例，如果你有心，可以尝试自己添加一个新的到现有复制环境中。
==============================================
