Stream multi-source replication in Oracle Database 10gR2

In 10gR2, oracle simplifies the whole process of stream configuration. In 9iR2 and 10gR1, You need to configure stream capture, propagation, and application processes.

It has been encapsulated by the new process (pre_instantiation_setup/post_instantiation_setup) provided by dbms_streams_adm,

To configure stream, you only need to call two stored procedures. This is so exciting. Next we will briefly introduce the usage of the new dbms_streams_adm process.

1. Make some preparations before configuring stream.

Set initialization parameters for source database a and target database

Alter system set aq_tm_processes = 4 scope = spfile;

Alter system set job_queue_processes = 5 scope = spfile;

Alter system set global_names = true scope = spfile;

Alter system set streams_pool_size = 51 m scope = spfile;

It indicates that streams_pool_size is better than 200 MB in the production environment.

Configure tnsnames. ora for source database B and target database B

Make sure it is correct and use tnsping

Create a copy administrator for the c source database and target database

Create user strmadmin identified by strmadminpw

Default tablespace & tbs_name quota unlimited on & tbs_name;

Grant connect, resource, dba to strmadmin;

D. Create an interconnected data link between the source database and the target database

Connect;

Create database link test99.net connect to strmadmin

Identified by strmadminpw using test99;

Connect;

Create database link test96.net connect to strmadmin

Identified by strmadminpw using test96;

Note: The database links of both parties must be accessible.

When pre_instantiation_setup/post_instantiation_setup is used

Db link must be in db_name.domain format

E. The source and target databases must be in the archive mode.

Shutdown immediate;

Startup mount;

Alter database archivelog;

Alter database open;

2. Execute the pre_instantiation_setup Process

When you call the pre_instantiation_setup/post_instantiation_setup process of dbms_streams_adm, they must appear in pairs. During the pre_instantiation_setup process, the maintain_mode parameter can be set to GLOBAL and transportable tablespaces, otherwise, you can define the tablespace to be copied. If the perform_actions parameter is TRUE, the source script generated for configuration will be recorded in the dba_recoverable _ * dictionary table. If an error occurs during execution of pre_instantiation_setup, you can run the dbms_steams_adm

The recover_operation process continues to execute the replication configuration after correcting the error; source_database/destination_database is the dblink we have created and must be correct; otherwise the pre_instantiation_setup process will fail and a ORA-23621 error is reported, the solution will be introduced later. When the bi_directional parameter is set to true, it indicates multi-source replication, that is, bidirectional synchronization between the target and the source database; otherwise, data is synchronized only from the source database to the target database; the exclude_schemas parameter specifies the tablespace to be excluded from full-Database Synchronization. Separate multiple tablespaces with commas,

* The tablespace that already exists in the Database when stream is configured is excluded;

The start_processes parameter indicates that the capture and application process are started after the configuration is complete.

SQL> connect;

Declare

Empty_tbs dbms_streams_tablespace_adm.tablespace_set;

Begin

Dbms_streams_adm.pre_instantiation_setup (

Maintain_mode => GLOBAL,

Tablespace_names => empty_tbs,

Source_database => test96.net,

Destination_database => test99.net,

Optional m_actions => true,

Bi_directional => true,

Include_ddl => true,

Start_processes => true,

Exclude_schemas => WMSYS, STRMADMIN, DBSNMP, TSMSYS ,,

Exclude_flags => dbms_streams_adm.exclude_flags_unsupported +

Dbms_streams_adm.exclude_flags_dml + dbms_streams_adm.exclude_flags_ddl );

End;

/

If the source database and target database are correctly configured, the process ends successfully,

In strmadmin mode, create a system for copying queues and dictionary tables. If the db link configuration fails,

When the pre_instantiation_setup process is executed, the following error is reported:

ORA-23621: Operation corresponding.

3. Use rman to copy the source database to the target database

A. Back up the source database using rman, copy the backup set, and archive the generated data to the target database,

And the target database is down to start the nomount status.

Rman nocatalog target/

Rman> backup database;

Rman> sqlalter system archive log current;

B. Obtain the scn of the source database.

SQL> connect;

SQL> set serveroutput on size 1000000

SQL> declare

Until_scn number;

Begin

Until_scn: = dbms_flashback.get_system_change_number;

Dbms_output.put_line (until scn: | until_scn );

End;

/

Until scn: 429596

C. Use rman to copy the source database to the target database.

Rman nocatalog target/

Rman> connect auxiliary;

Rman> run

{

Set until scn 429596;

Duplicate target database to TEST

Nofilenamecheck

Open restricted;

}

D. Rename the global_name of the target database.

Alter database rename global_name to test99.net;

E. Recreate the db link of the target database.

Connect;

Create database link test96.net connect to strmadmin

Identified by strmadminpw using test96;

4. Execute the post_instantiation_setup Process

Post_instantiation_setup is also executed in the source database. The parameter instantiation_scn?

Its value is the scn value obtained from the source database-1 = 429595.

SQL> connect;

SQL> declare

Empty_tbs dbms_streams_tablespace_adm.tablespace_set;

Begin

Dbms_streams_adm.post_instantiation_setup (

Maintain_mode => GLOBAL,

Tablespace_names => empty_tbs,

Source_database => test96.net,

Destination_database => test99.net,

Optional m_actions => true,

Bi_directional => true,

Include_ddl => true,

Start_processes => true,

Instantiation_scn = & gt; 429595,

Exclude_schemas => *,

Exclude_flags => dbms_streams_adm.exclude_flags_unsupported +

Dbms_streams_adm.exclude_flags_dml + dbms_streams_adm.exclude_flags_ddl );

End;

/

Disable restricted session in the target Library

SQL> connect as sysdba

SQL> alter system disable restricted session;

5. Test the stream configuration result

A creates a schema on test96 and creates some objects under the schema. You can see it on test99.

B creates a schema on test99 and creates some objects under the schema. You can see it on test96.

6 about avoid change processing in bidirectional Replication

View the tag of the apply process of the target database:

COLUMN APPLY_NAME HEADING Apply Process Name FORMAT A30

COLUMN APPLY_TAG HEADING Tag Value FORMAT A30

SQL> connect as sysdba;

SELECT APPLY_NAME, APPLY_TAG FROM DBA_APPLY;

Apply Process Name Tag Value

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

APPLY $ _ TEST96_42 010781

View the tag of the apply process of the source database:

COLUMN APPLY_NAME HEADING Apply Process Name FORMAT A30

COLUMN APPLY_TAG HEADING Tag Value FORMAT A30

SQL> connect as sysdba;

SELECT APPLY_NAME, APPLY_TAG FROM DBA_APPLY;

Apply Process Name Tag Value

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

APPLY $ _ TEST99_15 010498

Note: The recursive problem in multi-source replication is eliminated. stream already has a good elimination mechanism,

The tag of the redo entry written by the source job is NULL.

The generated redo entry will contain the tag flag in the redo entry so that the source capture process can

In the captured redo entry, if the tag is NULL, the change Handling ing can be eliminated.

10gR2stream full-Database Replication

The above section describes how to configure full-database replication in the pre_instantiation_setup/post_instantiation_setup process. The following describes how to configure full-database replication for stream in the maintain_global process of dbms_streams_adm, applicable to versions 10gR2 and later.

1l. Make some preparations before configuring stream.

Set initialization parameters for source database a and target database

Alter system set aq_tm_processes = 4 scope = spfile;

Alter system set job_queue_processes = 5 scope = spfile;

Alter system set global_names = true scope = spfile;

Alter system set streams_pool_size = 51 m scope = spfile;

It indicates that streams_pool_size is better than 200 MB in the production environment.

Configure tnsnames. ora for source database B and target database B

Make sure it is correct and use tnsping

Create a copy administrator for the c source database and target database

Create user strmadmin identified by strmadminpw

Default tablespace & tbs_name qu