Oracle streams How to create a schema-level replication environment

Schema-level replication can be done through DBMS_STREAMS_ADM. Maintain_schemas created, after the space-level trial of the previous table, the creation of a schema-level replication environment is relatively straightforward.

The examples in this chapter continue to follow the environment in the previous chapter (primarily the Oracle environment, where the previous replication environment has been purged) and set the environment as follows:

Source database Sid:jssweb, Target library: jssstr;

Copy schema:member, synchronous DML,DDL operation;

Local capture, bidirectional synchronization (again changed Yo, and changed to local capture);

Dbms_streams_adm. Maintain_tts Direct generation configuration;

The STREAMS administrator has created a database chain for source and target-side communication.

Since the previous use of Maintain_tts to complete the table space-level synchronization, so the preparation work can be omitted, if you do not follow the instructions in this article in order to perform the test, you must first refer to the first chapter table space-level synchronization of the preparation.

If you are testing exactly the steps you are going to take, don't forget to clear the previous replication environment and delete all objects under target member before performing this step.

1. Perform creation

In addition to clear the previous replication environment, delete the original target side schema objects, there is nothing else to do, directly execute the creation, create very simple, basically is a process of things.

First at the source end with Strmadmin landing:

Jssweb> Conn Strmadmin/strmadmin

Connected.

Jssweb> BEGIN

2 Dbms_streams_adm. Maintain_schemas (

3 Schema_names =>¨member¨,

4 Source_directory_object =>¨mydt_source¨,

5 Destination_directory_object =>¨mydt_dest¨,

6 Source_database =>¨jssweb.jss.cn¨,

7 Destination_database =>¨jssstr.jss.cn¨,

8 Perform_actions => True,

9 Dump_file_name =>¨export_member.dmp¨,

Ten Log_file =>¨export_member_expdp.log¨,

One bi_directional => true,

Include_ddl => True,

Instantiation => Dbms_streams_adm. Instantiation_schema);

End;

15/

Pl/sql procedure successfully completed.

This procedure brings up a new parameter: instantiation, which specifies whether to perform the instantiation, with the following values:

Dbms_streams_adm. Instantiation_schema: Initializes the data by EXPDP/IMPDP, and performs an instantiation when the data is imported on the target side, which is the value by default.

Dbms_streams_adm. INSTANTIATION_SCHEMA_NETWORK:IMPDP transmits data directly over the network without generating DMP files. The instantiation is performed when the data is imported.

Dbms_streams_adm. Instantiation_none: No instantiation is performed, this property value is valid only if the Perform_actions parameter is set to False.

Attention:

Since we choose to configure bidirectional replication here, the target side should not do any DML/DDL to the object in member in order to avoid data loss when executing this procedure.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

The Maintain_schemas process supports multiple schemas at the same time, such as having multiple schemas that need to be replicated, declaring a dbms_utility before the begin. Uncl_array array variables, for example:

DECLARE

Tab_schema Dbms_utility.uncl_array;

Begin

Tab_schema (1): =¨scott¨;

Tab_schema (2): =¨member¨;

................

Dbms_streams_adm. Maintain_schemas (

Schema_names => Tab_schema,

.............

.............

2. Test

Source-Side Execution:

jssweb> desc MEMBER.DT_TBL1;

Name Null? Type

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

ID not NULL number

NAME not NULL VARCHAR2 (30)

VL VARCHAR2 (20)

Jssweb> Select COUNT (0) from MEMBER.DT_TBL1;

COUNT (0)

----------

10 2

jssweb> INSERT into MEMBER.DT_TBL1 values (103,¨c¨,null);

1 row created.

jssweb> ALTER TABLE MEMBER.DT_TBL1 drop column VL;

Table altered.

jssweb> alter system switch logfile;

System altered.

Switch to target side view:

Jssstr> Select COUNT (0) from MEMBER.DT_TBL1;

COUNT (0)

----------

103

jssstr> desc MEMBER.DT_TBL1;

is the name empty? Type