Maison > base de données > tutoriel mysql > oracle 11g streams各种类型搭建主要步骤

oracle 11g streams各种类型搭建主要步骤

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Libérer: 2016-06-07 15:37:24
original
1485 Les gens l'ont consulté

接前面oracle 11g streams 逻辑修改记录(LCR)示例 想要搭建一个完整的streams环境请看oracle 11g streams搭建 1 、数据库级别 streams 复制 --完整参数示例connect strmadmin/strmadmin@DBXA.WORLDConnected.begin dbms_streams_adm.maintain_global ( sou

接前面"oracle 11g streams 逻辑修改记录(LCR)示例"

想要搭建一个完整的streams环境请看"oracle 11g streams搭建"

1、数据库级别streams复制

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

--完整参数示例

connect strmadmin/strmadmin@DBXA.WORLD

Connected.

begin

    dbms_streams_adm.maintain_global (

        source_database => 'DBXA.WORLD',

        source_directory_object => 'STREAMS_DP_DIR',

        destination_database => 'DBXB.WORLD',

        destination_directory_object => 'STREAMS_DP_DIR',

        capture_name => 'DBXA_CAP',

        capture_queue_name => 'DBXA_CAP_Q',

        capture_queue_table => 'DBXA_CAP_Q_T',

        capture_queue_user => 'STRMADMIN',

        propagation_name => 'DBXA_TO_DBXB_PROP',

        apply_name => 'DBXA_APP',

        apply_queue_name => 'DBXA_APP_Q',

        apply_queue_table => 'DBXA_APP_Q_T',

        apply_queue_user => 'STRMADMIN',

        script_name => 'cr_streams_global.sql',

        script_directory_object => 'STREAMS_DP_DIR',

        dump_file_name => NULL,

        log_file => NULL,

        bi_directional => FALSE,

        include_ddl => TRUE,

        perform_actions => TRUE,

        instantiation => DBMS_STREAMS_ADM.INSTANTIATION_FULL

    );

end;

/

job finished

PL/SQL procedure successfully completed.

 

--如果不需要用户为streams组件定义有意义的名称

connect strmadmin/strmadmin@DBXA.WORLD

Connected.

begin

    dbms_streams_adm.maintain_global (

        source_database => 'DBXA.WORLD',

        source_directory_object => 'STREAMS_DP_DIR',

        destination_database => 'DBXB.WORLD',

        destination_directory_object => 'STREAMS_DP_DIR',

        include_ddl => TRUE

    );

end;

/

job finished

PL/SQL procedure successfully completed.

Copier après la connexion
2、模式级别streams复制

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

--完整参数

connect strmadmin/strmadmin@DBXA.WORLD

Connected.

declare

    schemas dbms_utility.uncl_array;

begin

    schemas(1) := 'SCOTT';

    schemas(2) := 'HR';

    dbms_streams_adm.maintain_schemas (

        schema_names => schemas,

        source_database => 'DBXA.WORLD',

        source_directory_object => 'STREAMS_DP_DIR',

        destination_database => 'DBXB.WORLD',

        destination_directory_object => 'STREAMS_DP_DIR',

        capture_name => 'DBXA_CAP',

        capture_queue_name => 'DBXA_CAP_Q',

        capture_queue_table => 'DBXA_CAP_Q_T',

        propagation_name => 'DBXA_TO_DBXB_PROP',

        apply_name => 'DBXA_APP',

        apply_queue_name => 'DBXA_APP_Q',

        apply_queue_table => 'DBXA_APP_Q_T',

        dump_file_name => 'schemas_expimp.dmp',

        log_file => 'schemas_expimp.log',

        bi_directional => FALSE,

        include_ddl => TRUE,

        perform_actions => TRUE

    );

end;

/

job finished

PL/SQL procedure successfully completed.

 

--如果不需要用户为streams组件定义有意义的名称

connect strmadmin/strmadmin@DBXA.WORLD

Connected.

declare

    schemas dbms_utility.uncl_array;

begin

    schemas(1) := 'SCOTT';

    schemas(2) := 'HR';

    dbms_streams_adm.maintain_schemas (

        schema_names => schemas,

        source_database => 'DBXA.WORLD',

        source_directory_object => 'STREAMS_DP_DIR',

        destination_database => 'DBXB.WORLD',

        destination_directory_object => 'STREAMS_DP_DIR',

        include_ddl => TRUE,

        instantiation => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA_NETWORK

    );

end;

/

PL/SQL procedure successfully completed.

Copier après la connexion
3、表级别streams复制

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

--完整参数

connect strmadmin/strmadmin@DBXA.WORLD

Connected.

declare

    tables dbms_utility.uncl_array;

begin

    tables(1) := 'SCOTT.DEPT';

    tables(2) := 'SCOTT.EMP';

    tables(3) := 'HR.SALGRADE';

    tables(4) := 'HR.BONUS';

 

    dbms_streams_adm.maintain_tables (

        table_names => tables,

        source_database => 'DBXA.WORLD',

        source_directory_object => 'STREAMS_DP_DIR',

        destination_database => 'DBXB.WORLD',

        destination_directory_object => 'STREAMS_DP_DIR',

        capture_name => 'DBXA_CAP',

        capture_queue_name => 'DBXA_CAP_Q',

        capture_queue_table => 'DBXA_CAP_Q_T',

        propagation_name => 'DBXA_TO_DBXB_PROP',

        apply_name => 'DBXA_APP',

        apply_queue_name => 'DBXA_APP_Q',

        apply_queue_table => 'DBXA_APP_Q_T',

        dump_file_name => NULL,

        log_file => NULL,

        bi_directional => FALSE,

        include_ddl => TRUE,

        perform_actions => TRUE,

        instantiation => DBMS_STREAMS_ADM.INSTANTIATION_TABLE

    );

end;

/

job finished

PL/SQL procedure successfully completed.

Copier après la connexion
4、表空间级别streams复制

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

--在源数据库中创建 目录对象

create directory example_ts_dir as '/u01/oradata/DBXA';

Directory created.

 

grant read on directory example_ts_dir to strmadmin;

Grant succeeded

 

--在目标数据库中创建目录对象

create directory example_ts_dir as '/u02/app/oradata/DBXB';

Directory created.

 

--使用maintain_simple_tts过程创建表空间streams复制

connect strmadmin/strmadmin@DBXA.WORLD

Connected.

begin

    dbms_streams_adm.maintain_simple_tts(

        tablespace_name => 'EXAMPLE_TS',

        source_directory_object => 'STREAMS_DP_DIR',

        source_database => 'DBXA.WORLD',

        destination_directory_object => 'EXAMPLE_TS_DIR',

        destination_database => 'DBXB.WORLD',

        perform_actions => TRUE,

        script_name => 'cr_maintain_simple_tts_uni.sql',

        script_directory_object => 'STREAMS_DP_DIR',

        bi_directional => FALSE

    );

end;

/

PL/SQL procedure successfully completed.

 

--使用MAINTAIN_TTS过程创建表空间streams复制

connect strmadmin/strmadmin@DBXA.WORLD

Connected.

declare

    ts_names dbms_streams_tablespace_adm.tablespace_set;

begin

    ts_names(1) := 'DATA_TS';

    ts_names(2) := 'INDEX_TS';

    ts_names(3) := 'EXAMPLE_TS';

 

    dbms_streams_adm.maintain_tts(

        tablespace_names => ts_names,

        source_directory_object => 'STREAMS_DP_DIR',

        destination_directory_object => 'EXAMPLE_TS_DIR',

        source_database => 'DBXA.WORLD',

        destination_database => 'DBXB.WORLD',

        perform_actions => TRUE,

        script_name => 'cr_streams_maintain_tts_uni.sql',

        script_directory_object => 'STREAMS_DP_DIR',

        dump_file_name => 'maint_tts.dmp',

        capture_name => 'DBXA_CAP',

        capture_queue_table => 'DBXA_CAP_Q_T',

        capture_queue_name => 'DBXA_CAP_Q',

        capture_queue_user => 'STRMADMIN',

        propagation_name => 'DBXA_TO_DBXB_PROP',

        apply_name => 'DBXA_APP',

        apply_queue_table => 'DBXA_APP_Q_T',

        apply_queue_name => 'DBXA_APP_Q',

        apply_queue_user => 'STRMADMIN',

        log_file => 'maintain_tts.log',

        bi_directional => FALSE,

        include_ddl => TRUE

    );

end;

/

PL/SQL procedure successfully completed

Copier après la connexion
5、使用MAINTAIN存储过程配置下游捕获进程

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

--存储过程运行在目标数据库DBXB.WORKD中

--不需要传播进程,如果指定了传播进程也不会创建

--捕获进程和应用进程的队列名和队列表名一样

connect strmadmin/strmadmin@DBXB.WORLD

Connected.

declare

    schemas dbms_utility.uncl_array;

begin

    schemas(1) := 'SCOTT';

    schemas(2) := 'HR';

 

    dbms_streams_adm.maintain_schemas (

        schema_names => schemas,

        source_database => 'DBXA.WORLD',

        source_directory_object => 'STREAMS_DP_DIR',

        destination_database => 'DBXB.WORLD',

        destination_directory_object => 'STREAMS_DP_DIR',

        capture_name => 'DBXA_CAP',

        capture_queue_name => 'DBXA_CAP_Q',

        capture_queue_table => 'DBXA_CAP_Q_T',

        propagation_name => 'DBXA_TO_DBXB_PROP',

        apply_name => 'DBXA_APP',

        apply_queue_name => 'DBXA_CAP_Q',

        apply_queue_table => 'DBXA_CAP_Q_T',

        dump_file_name => NULL,

        log_file => NULL,

        bi_directional => FALSE,

        include_ddl => TRUE,

        perform_actions => TRUE

    );

end;

/

job finished

PL/SQL procedure successfully completed.

 

--配置从数据库DBXA.WORLD到DBXB.WORLD的streams复制的方法

--存储过程运行在DBXB.WORLD数据库中

--传播定义从DBXB.WORLD到DBXC.WORLD

--捕获进程和应用进程的队列名和队列表名不同

connect strmadmin/strmadmin@DBXB.WORLD

Connected.

declare

    schemas dbms_utility.uncl_array;

begin

    schemas(1) := 'SCOTT';

    schemas(2) := 'HR';

 

    dbms_streams_adm.maintain_schemas (

        schema_names => schemas,

        source_database => 'DBXA.WORLD',

        source_directory_object => 'STREAMS_DP_DIR',

        destination_database => 'DBXC.WORLD',

        destination_directory_object => 'STREAMS_DP_DIR',

        capture_name => 'DBXA_CAP',

        capture_queue_name => 'DBXA_CAP_Q',

        capture_queue_table => 'DBXA_CAP_Q_T',

        propagation_name => 'DBXB_TO_DBXC_PROP',

        apply_name => 'DBXA_APP',

        apply_queue_name => 'DBXA_APP_Q',

        apply_queue_table => 'DBXA_APP_Q_T',

        dump_file_name => NULL,

        log_file => NULL,

        bi_directional => FALSE,

        include_ddl => TRUE,

        perform_actions => TRUE

    );

end;

/

PL/SQL procedure successfully completed.

Copier après la connexion
6、创建视图来查看streams 存储过程的状态和进度

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

--主要是基于DBA_RECOVERABLE_SCRIPT视图和DBA_RECOVERABLE_SCRIPT_BLOCKS视图创建

connect sys as sysdba

set long 100000000

create or replace view streams_build_status

as

    select to_char(rs.creation_time,'HH24:Mi:SS MM/DD/YY') CREATE_DATE,

        rs.status,

        rs.done_block_num||' of ' ||rs.total_blocks ||' Steps Completed' PROGRESS,

        to_char(to_number(sysdate-rs.creation_time)*86400,9999.99) ELAPSED_SECONDS,

        substr(rsb.forward_block,1,5000) CURRENT_STEP,

        rs.invoking_package||'.'||rs.invoking_procedure PROCEDURE,

        rs.script_id

    from dba_recoverable_script rs,

            dba_recoverable_script_blocks rsb

    where rs.script_id = rsb.script_id

    and rsb.block_num = rs.done_block_num + 1;

     

create public synonym streams_build_status for streams_build_status;

grant select on streams_build_status to public;

 

--查询进度信息(存储过程当前正在执行、一共有14个过程块已经完成13个、已经运行了276秒)

select status,

    progress,

    elapsed_seconds elapsed,

    script_id

from streams_build_status;

STATUS    PROGRESS                  ELAPSED SCRIPT_ID

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

EXECUTING 13 of 14 Steps Completed   276.00 7CC97F3B9169704BE040A8C014006E63

 

--查看正在执行的过程块

SQL> select current_step

2 from streams_build_status;

CURRENT_STEP

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

-- Start capture process DBXA$CAP

--

BEGIN

    dbms_capture_adm.start_capture(

        capture_name => '"DBXA$CAP"'

    );

EXCEPTION WHEN OTHERS THEN

    IF sqlcode = -26666 THEN NULL; -- CAPTURE process already running

    ELSE RAISE;

    END IF;

END;

 

--提取脚本及过程块

set long 10000000

set pages 1000

spool maintain_script.sql

    select '-- Block: ' || block_num,

        forward_block

    from dba_recoverable_script_blocks

    where script_id = '7CC97F3B9169704BE040A8C014006E63'

    order by block_num;

spool off

Copier après la connexion
7、从MAINTAIN存储过程的错误中恢复

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

--下面展示配置模式级别的复制时,对于一个造成MAINTAIN存储过程失败的简单错误处理

connect strmadmin/strmadmin@DBXA.WORLD

Connected.

declare

    schemas dbms_utility.uncl_array;

begin

    schemas(1) := 'SCOTT';

    schemas(2) := 'HR';

 

    dbms_streams_adm.maintain_schemas (

        schema_names => schemas,

        source_database => 'DBXA.WORLD',

        source_directory_object => 'STREAMS_DP_DIR',

        destination_database => 'DBXB.WORLD',

        destination_directory_object => 'STREAMS_DP_DIR',

        include_ddl => TRUE,

        instantiation => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA_NETWORK

    );

end;

/

declare

*

ERROR at line 1:

ORA-23616: Failure in executing block 7 for script

7CD4E8B08BD40E08E040A8C014007723 with

ORA-39001: invalid argument value

ORA-06512: at "SYS.DBMS_RECO_SCRIPT_INVOK", line 139

ORA-06512: at "SYS.DBMS_STREAMS_RPC", line 465

ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 659

ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 682

ORA-06512: at "SYS.DBMS_STREAMS_MT", line 7972

ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2674

ORA-06512: at line 7

 

--通过错误提示的script_id查看错误的具体信息

select forward_block

from dba_recoverable_script_blocks

where script_id = '7CD4E8B08BD40E08E040A8C014007723'

and block_num = 7;

FORWARD_BLOCK

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

--

-- Datapump SCHEMA MODE IMPORT (NETWORK)

--

DECLARE

h1 NUM

......

......

 

--由上可以看出是数据库连接丢失造成的错误

--在创建数据库连接时候作业在恢复之后便可以成功完成

begin

    dbms_streams_adm.recover_operation(

        script_id => '7CD4E8B08BD40E08E040A8C014007723',

        operation_mode => 'FORWARD'

    );

end;

/

PL/SQL procedure successfully completed.

Copier après la connexion
8、含有本地进程的单向复制

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

--配置schema级别的streams,source:DBXA.WORLD、target:DBXB.WORLD

--连接到目标数据库并且创建streams队列和表

conn strmadmin/strmadmin@DBXB.WORLD

Connected.

begin

    dbms_streams_adm.set_up_queue(

        queue_name => 'DBXA_APP_Q',

        queue_table => 'DBXA_APP_Q_T',

        queue_user => 'STRMADMIN'

    );

end;

/

 

--在target数据库中创建应用进程和规则

begin

    dbms_streams_adm.add_schema_rules (

        schema_name => 'SCOTT',

        streams_type => 'APPLY',

        streams_name => 'DBXA_APP',

        queue_name => 'DBXA_APP_Q',

        include_dml => true,

        include_ddl => true,

        inclusion_rule => true,

        include_tagged_lcr => false,

        source_database => 'DBXA.WORLD',

        and_condition => ' :lcr.get_compatible()  'DBXA_CAP_Q',

        queue_table => 'DBXA_CAP_Q_T',

        queue_user => 'STRMADMIN'

    );

end;

/

PL/SQL procedure successfully completed.

 

--在source数据库创建传播进程和传播规则

begin

    dbms_streams_adm.add_schema_propagation_rules (

        schema_name => 'SCOTT',

        streams_name => 'DBXA_TO_DBXB_PROP',

        source_queue_name => 'DBXA_CAP_Q',

        destination_queue_name => 'DBXA_APP_Q@DBXB.WORLD',

        include_dml => true,

        include_ddl => true,

        inclusion_rule => true,

        include_tagged_lcr => false,

        queue_to_queue => true,

        source_database => 'DBXA.WORLD',

        and_condition => ' :lcr.get_compatible()  'SCOTT',

        streams_type => 'CAPTURE',

        streams_name => 'DBXA_CAP',

        queue_name => 'DBXA_CAP_Q',

        include_dml => true,

        include_ddl => true,

        include_tagged_lcr => false,

        inclusion_rule => true,

        source_database => 'DBXA.WORLD',

        and_condition => ' :lcr.get_compatible()  'DBXA_CAP_APP_Q',

        queue_table => 'DBXA_CAP_APP_Q_T',

        queue_user => 'STRMADMIN'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create apply process and rules.

begin

    dbms_streams_adm.add_schema_rules (

        schema_name => 'SCOTT',

        streams_type => 'APPLY',

        streams_name => 'DBXA_APP',

        queue_name => 'DBXA_CAP_APP_Q',

        include_dml => true,

        include_ddl => true,

        inclusion_rule => true,

        include_tagged_lcr => false,

        source_database => 'DBXA.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- No need to create propagation, since apply

-- and capture run in the same database.

 

-- Explicitly create the capture process.

-- Note that use_database_link is set to TRUE.

begin

    dbms_capture_adm.create_capture(

        capture_name => 'DBXA_CAP',

        queue_name => 'DBXA_CAP_APP_Q',

        use_database_link => TRUE,

        source_database => 'DBXA.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Add capture rules.

begin

    dbms_streams_adm.add_schema_rules (

        schema_name => 'SCOTT',

        streams_type => 'CAPTURE',

        streams_name => 'DBXA_CAP',

        queue_name => 'DBXA_CAP_APP_Q',

        include_dml => true,

        include_ddl => true,

        include_tagged_lcr => false,

        inclusion_rule => true,

        source_database => 'DBXA.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

--之后就和前面一样实例化对象和启动应用进程和捕获进程

Copier après la connexion
9、包含下游捕获进程的单向复制(实时下游捕获)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

--source数据库DBXA.WORLD、target数据库DBXB.WORLD

--在source数据库中找到重做日志文件大小和重做日志文件数目

conn sys/manager@DBXA.WORLD as sysdba

Connected.

select group#,

    bytes/1048576 MB

from v$log;

GROUP# MB

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

         1         50

         2         50

         3         50

 

--在下游数据库中创建备份重做日志,并且比source数据库的重做日志多一组(和配置dataguard一样)

conn sys/manager@DBXB.WORLD as sysdba

Connected.

alter database add standby logfile group 4

    ('/u01/oradata/DBXA/standby_logs/standby_redo04.log') size 50M;

Database altered.

 

alter database add standby logfile group 5

    ('/u01/oradata/DBXA/standby_logs/standby_redo05.log') size 50M;

Database altered.

 

alter database add standby logfile group 6

    ('/u01/oradata/DBXA/standby_logs/standby_redo06.log') size 50M;

Database altered.

 

alter database add standby logfile group 7

    ('/u01/oradata/DBXA/standby_logs/standby_redo07.log') size 50M;

Database altered.

 

-- Check created Standby logs

select thread#,

    group#,

    sequence#,

    status,

    archived

from v$standby_log;

THREAD#    GROUP#     SEQUENCE#  STATUS     ARC

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

         0          4          0 UNASSIGNED YES

         0          5          0 UNASSIGNED YES

         0          6          0 UNASSIGNED YES

         0          7          0 UNASSIGNED YES

 

--这步和使用归档日志一样,为source和target数据库配置传输初始化参数    

--和使用归档日志一样,创建捕获进程和应用进程

         

--注意:需要修改捕获进程参数

conn strmadmin/strmadmin@DBXB.WORLD

Connected.

 

-- Modify capture Parameter

begin

    dbms_capture_adm.set_parameter(

        capture_name => 'DBXA_CAP',

        parameter => 'downstream_real_time_mine',

        value => 'Y'

    );

end;

/

PL/SQL procedure successfully completed.

 

conn sys/manager@DBXA.WORLD as sysdba

Connected.

 

alter system archive log current;

System altered.

 

conn sys/manager@DBXB.WORLD as sysdba

Connected.

 

select thread#,

    group#,

    sequence#,

    archived,

    status

from v$standby_log;

THREAD#    GROUP#     SEQUENCE#  ARC STATUS

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

         1          4        289 YES ACTIVE

         0          5          0 YES UNASSIGNED

         0          6          0 YES UNASSIGNED

         0          7          0 YES UNASSIGNED     

          

--之后就和前面一样实例化对象和启动应用进程和捕获进程

Copier après la connexion
10、双向复制

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

-- Connect to DBXB Database.

conn strmadmin/strmadmin@DBXB.WORLD

Connected.

 

-- Create Queue for apply in DBXB.

begin

    dbms_streams_adm.set_up_queue(

        queue_name => 'DBXA_APP_Q',

        queue_table => 'DBXA_APP_Q_T',

        queue_user => 'STRMADMIN'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create Queue for capture in DBXB.

begin

    dbms_streams_adm.set_up_queue(

        queue_name => 'DBXB_CAP_Q',

        queue_table => 'DBXB_CAP_Q_T',

        queue_user => 'STRMADMIN'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create apply process and rules in DBXB.

begin

    dbms_streams_adm.add_schema_rules (

        schema_name => 'SCOTT',

        streams_type => 'APPLY',

        streams_name => 'DBXA_APP',

        queue_name => 'DBXA_APP_Q',

        include_dml => true,

        include_ddl => true,

        inclusion_rule => true,

        include_tagged_lcr => false,

        source_database => 'DBXA.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create capture process and rules in DBXB.

begin

    dbms_streams_adm.add_schema_rules (

        schema_name => 'SCOTT',

        streams_type => 'CAPTURE',

        streams_name => 'DBXB_CAP',

        queue_name => 'DBXB_CAP_Q',

        include_dml => true,

        include_ddl => true,

        include_tagged_lcr => false,

        inclusion_rule => true,

        source_database => 'DBXB.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create propagation in DBXB to send changes to DBXA.

 

begin

    dbms_streams_adm.add_schema_propagation_rules (

        schema_name => 'SCOTT',

        streams_name => 'DBXB_TO_DBXB_PROP',

        source_queue_name => 'DBXB_CAP_Q',

        destination_queue_name => 'DBXB_APP_Q@DBXA.WORLD',

        include_dml => true,

        include_ddl => true,

        inclusion_rule => true,

        include_tagged_lcr => false,

        queue_to_queue => true,

        source_database => 'DBXB.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Connect to DBXA Database

conn strmadmin/strmadmin@DBXA.WORLD

Connected.

 

-- Create Queue for apply in DBXA.

begin

    dbms_streams_adm.set_up_queue(

        queue_name => 'DBXB_APP_Q',

        queue_table => 'DBXB_APP_Q_T',

        queue_user => 'STRMADMIN'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create Queue for capture in DBXA.

begin

    dbms_streams_adm.set_up_queue(

        queue_name => 'DBXA_CAP_Q',

        queue_table => 'DBXA_CAP_Q_T',

        queue_user => 'STRMADMIN'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create apply Process and Rules in DBXA.

begin

    dbms_streams_adm.add_schema_rules (

        schema_name => 'SCOTT',

        streams_type => 'APPLY',

        streams_name => 'DBXB_APP',

        queue_name => 'DBXB_APP_Q',

        include_dml => true,

        include_ddl => true,

        inclusion_rule => true,

        include_tagged_lcr => false,

        source_database => 'DBXB.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create capture process and rules in DBXA.

begin

    dbms_streams_adm.add_schema_rules (

        schema_name => 'SCOTT',

        streams_type => 'CAPTURE',

        streams_name => 'DBXA_CAP',

        queue_name => 'DBXA_CAP_Q',

        include_dml => true,

        include_ddl => true,

        include_tagged_lcr => false,

        inclusion_rule => true,

        source_database => 'DBXA.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create propagation in DBXA to send changes to DBXB.

begin

    dbms_streams_adm.add_schema_propagation_rules (

        schema_name => 'SCOTT',

        streams_name => 'DBXA_TO_DBXB_PROP',

        source_queue_name => 'DBXA_CAP_Q',

        destination_queue_name => 'DBXA_APP_Q@DBXB.WORLD',

        include_dml => true,

        include_ddl => true,

        inclusion_rule => true,

        include_tagged_lcr => false,

        queue_to_queue => true,

        source_database => 'DBXA.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

--接下来将source数据库的对象导入到target数据库中,并设立实例化scn(data pump导入就不演示了)

 

--在source数据库中也实例化scn(对需要的表)。如下手动设置

-- Connect to DBXB database.

conn strmadmin/strmadmin@DBXB.WORLD

Connected.

declare

v_scn number;

begin

    v_scn := dbms_flashback.get_system_change_number();

    dbms_apply_adm.set_schema_instantiation_scn@DBXA.WORLD(

        source_schema_name => 'SCOTT',

        source_database_name => 'DBXB.WORLD',

        instantiation_scn => v_scn,

        recursive => true

    );

end;

/

PL/SQL procedure successfully completed.

 

--以上都操作完之后可以在两个数据库都启动capture和apply进程(每个数据库都要启动这两个进程)

Copier après la connexion
11、使用同步捕获进程的复制

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

-- Connect to Destination Database.

conn strmadmin/strmadmin@DBXB.WORLD

Connected.

 

 

-- Create Streams Queue.

begin

    dbms_streams_adm.set_up_queue(

        queue_name => 'DBXA_APP_Q',

        queue_table => 'DBXA_APP_Q_T',

        queue_user => 'STRMADMIN'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create the Apply Process.

-- The APPLY_CAPTURED is set to FALSE by default.

-- But, it is included for documentation purpose.

--

begin

    dbms_apply_adm.create_apply(

        apply_name => 'DBXA_APP',

        queue_name => 'DBXA_APP_Q',

        apply_captured => FALSE

    );

end;

/

PL/SQL procedure successfully completed.

 

 

begin

    dbms_streams_adm.add_schema_rules (

        schema_name => 'SCOTT',

        streams_type => 'APPLY',

        streams_name => 'DBXA_APP',

        queue_name => 'DBXA_APP_Q',

        include_dml => true,

        source_database => 'DBXA.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

 

-- Connect to the Source Database.

 

conn strmadmin/strmadmin@DBXA.WORLD

Connected.

 

 

-- Create Streams Queue for synchronous capture.

begin

    dbms_streams_adm.set_up_queue(

        queue_name => 'DBXA_SYNC_CAP_Q',

        queue_table => 'DBXA_SYNC_CAP_Q_T',

        queue_user => 'STRMADMIN'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create Synchronous capture process

-- and add capture rules for replicated tables.

 

begin

    dbms_streams_adm.add_table_rules (

        table_name => 'SCOTT.DEPT',

        streams_type => 'SYNC_CAPTURE',

        streams_name => 'DBXA_SYNC_CAP',

        queue_name => 'DBXA_SYNC_CAP_Q',

        include_dml => true,

        inclusion_rule => true,

        source_database => 'DBXA.WORLD'

    );

 

    dbms_streams_adm.add_table_rules (

        table_name => 'SCOTT.EMP',

        streams_type => 'SYNC_CAPTURE',

        streams_name => 'DBXA_SYNC_CAP',

        queue_name => 'DBXA_SYNC_CAP_Q',

        include_dml => true,

        inclusion_rule => true,

        source_database => 'DBXA.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

-- Create propagation Process and add rules.

-- We can add rules at Schema or Global level.

 

begin

    dbms_streams_adm.add_schema_propagation_rules (

        schema_name => 'SCOTT',

        streams_name => 'DBXA_TO_DBXB_PROP',

        source_queue_name => 'DBXA_SYNC_CAP_Q',

        destination_queue_name => 'DBXA_APP_Q@DBXB.WORLD',

        include_dml => true,

        queue_to_queue => true,

        source_database => 'DBXA.WORLD'

    );

end;

/

PL/SQL procedure successfully completed.

 

 

-- Since the Source and Destination tables are

-- already in sync, no need to export/import data.

-- We perform the instantiation manually.

 

declare

v_scn number;

begin

    v_scn := dbms_flashback.get_system_change_number();

    dbms_apply_adm.set_table_instantiation_scn@DBXB.WORLD(

        source_object_name => 'SCOTT.DEPT',

        source_database_name => 'DBXA.WORLD',

        instantiation_scn => v_scn);

        dbms_apply_adm.set_table_instantiation_scn@DBXB.WORLD(

        source_object_name => 'SCOTT.EMP',

        source_database_name => 'DBXA.WORLD',

        instantiation_scn => v_scn

    );

end;

/

PL/SQL procedure successfully completed.

Copier après la connexion
12、还有以下几种配置策略(P228

1)从单个源到多个目标数据库

2)使用队列转发的复制

3)使用应用转发的复制

4)辐射类型复制

5)点对点复制

Étiquettes associées:
Déclaration de ce site Web
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Tutoriels populaires
Plus>
Derniers téléchargements
Plus>
effets Web
Code source du site Web
Matériel du site Web
Modèle frontal