Oracle streams How to create a tables-level replication environment

You should still have the impression that we have demonstrated the creation of a table-level replication environment in the second section of the first section, comparing the whole library/table space level or schema level replication, and now looking back, you might think how the table-level replication environment is so complex? This is not the case, not the table-level replication environment, and the first part of the second chapter is the process of STREAMS actual configuration (most of the steps), if you use Dbms_streams_adm directly. The maintain_tables process is created, similar to schemas replication, that is, the execution of a process.

Here on the one hand to give you a demonstration, on the other hand is to deepen everyone's understanding, in this part of the last configuration, think twice decide to take the build script, rather than directly execute the configuration of the way, so that everyone can have the opportunity to learn more about the streams configuration of the actual implementation steps.

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;

Copying objects in Schema:member, partially synchronizing DML, partially synchronizing DDL operations;

Local capture, one-way synchronization;

Dbms_streams_adm. Maintain_tables generate configuration scripts;

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

1. Generate Create Script

Since this is also borrowed from the previous set of environmental testing, so ready to work all over the province, we directly execute Maintain_tables can:

Jssweb> Conn Strmadmin/strmadmin

Connected.

Jssweb> DECLARE

2 TBLs dbms_utility. Uncl_array;

3 BEGIN

4 TBLs (1): =¨member.dt_tmp¨;

5 TBLs (2): =¨member.dt_tbl1¨;

6 TBLs (3): =¨member.dt_tbl2¨;

7 Dbms_streams_adm. Maintain_tables (

8 Table_names => TBLs,

9 Source_directory_object =>¨mydt_source¨,

Ten Destination_directory_object =>¨mydt_dest¨,

One source_database =>¨jssweb.jss.cn¨,

Destination_database =>¨jssstr.jss.cn¨,

Perform_actions => False,

Dump_file_name =>¨export_tbls.dmp¨,

Log_file =>¨export_tbls_expdp.log¨,

Script_name =>¨configure_rep.sql¨,

Script_directory_object =>¨mydt_source¨,

Bi_directional => False,

Include_ddl => True,

Instantiation => Dbms_streams_adm. instantiation_table);

End;

22/

Pl/sql procedure successfully completed.

Here are some parameters you have seen many times, some parameters that you have never contacted:

Table_names: Nothing to say, just a hint, if you want to copy only one table, you do not need to define dbms_utility. Uncl_array an array variable, specifying table_names equals the table name when calling Maintain_tables directly.

Script_name: Because the replication environment is not directly configured here, the configuration script is generated, which is the name of the script that specifies the build.

Script_directory_object: This parameter specifies the directory in which the generated script is located (Oracle directory object, as mentioned earlier).

Instantiation: This parameter is previously seen in the configuration schemas replication environment, but for table-level replication, this parameter value is slightly different from the instantiation schemas replication, that is, change the schema to table, Each parameter value represents the same meaning as the schema, and is no longer detailed.

2. View the Build script

When the configuration is performed earlier, the script is built into the directory object Mydt_source to first view the operating system path that the directory actually points to:

Jssweb> SELECT * from dba_directories where Directory_name=¨mydt_source¨;

OWNER Directory_name Directory_path

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

SYS mydt_source/data/oradata/jssweb/dmp

The above query determines that the script is located in the operating system:/data/oradata/jssweb/dmp directory, directly to the directory to view the generated script file, execute:

[Oracle@yans1 ~]$ More/data/oradata/jssweb/dmp/configure_rep.sql

The script is longer, not all posted here, you can browse in detail in your test environment, if you need to modify, directly modify the script file.

3. Execute script

After the script is modified, the source end is logged in Strmadmin and executed, as follows:

Jssweb> Conn Strmadmin/strmadmin

Connected.

Jssweb> Spool Configure_rep.log

Jssweb> @/data/oradata/jssweb/dmp/configure_rep.sql

Jssweb> SET VERIFY off

Jssweb> whenever SQLERROR EXIT SQL. SQLCODE;

Jssweb>

Jssweb>

Jssweb>

Jssweb>-------------------------------------------------------------------

Jssweb>--Get tnsname and streams Admin user details for both the databases

Jssweb>--------------------------------------------------------------------