Oracle bidirectional Stream configuration practices

Oracle bidirectional Stream configuration practices

Abstract: This article describes how to use the Oracle Stream replication function for Bidirectional Stream replication. It is mainly used for real-time database synchronization and backup. Oracle Stream is designed to improve Database Availability. Oracle Stream uses advanced Queue Technology to parse archived logs and parse archived logs into DDL and DML statements to synchronize data between databases. This technology can replicate objects in the entire database and database to another database. By using Stream technology, archiving logs can be mined without any pressure on the main system, synchronize database objects and even the entire database.

Bidirectional Stream Configuration

This example uses two oracle10g servers, stream1 (192.168.1.101) and stream2 (192.168.1.102), to configure two-way stream.

1. oracle archive mode, append log, global_name

1.1. oracle archiving Mode

Confirm that oracle is in archive mode. If not, change it to archive mode.

Check whether oracle is in archive mode through the following:

SQL> archive log list

Database log mode Archive Mode

The above is the archive mode. For non-archive mode, you can use the following command to change it to the archive mode,

Sqlplus '/as sysdba'

Alter system set log_archive_dest_1 = 'location =/opt/test' scope = spfile;

Alter system set log_archive_start = TRUE scope = spfile;

Alter system set log_archive_format = 'arch % t _ % s _ % r. arc' scope = spfile;

Shutdown immediate;

Startup mount;

Alter database archivelog;

Alter database open;

1.2. oracle append log

Log on with an Administrator Account

Logs are used for stream replication when you modify a Table without a Primary Key. You can enable lemental Log at the Database or Table level ).

You can directly enable the basic append log of the database.

SQL: alter database add supplemental log data;

1.3. oracle global_name

Log on with an Administrator Account

Modify global_name on two machines

Stream1:
SQL> alter system set global_names = true scope = both;
SQL> alter database rename global_name to stream1.home;

Stream2:
SQL> alter system set global_names = true scope = both;
SQL> alter database rename global_name to stream2.home;

2. Configure tnsname

Configure tnsname on both machines to access each other.

In stream1:

Stream2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.102) (PORT = 1521 ))

)

(CONNECT_DATA =

(SID = orcl)

(SERVER = DEDICATED)

)

)

In stream2:

Stream1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.101) (PORT = 1521 ))

)

(CONNECT_DATA =

(SID = orcl)

(SERVER = DEDICATED)

)

)

 

3. Create a Stream management user

Create the same management user for both machines.

Create a tablespace streams_tbs first

Create strmadmin to manage users and assign DBA and stream permissions.

SQL> CREATE USER strmadmin IDENTIFIED BY pword DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;

Result: User created.

SQL> GRANT DBA TO strmadmin;

Result: Grant succeeded.

SQL> BEGIN

DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE (

Grantee => 'strmadmin ',
Grant_privileges => true );
END;
/

Result: PL/SQL procedure successfully completed.

4. Create DB_LINK and STREAM queue

Use strmadmin to log on

4.1. Create DB_LINK

Create on stream1:

Create database link stream2.home connect to strmadmin identified by strmadminpw USING 'stream2 ';

Create on stream2:

Create database link stream1.home connect to strmadmin identified by strmadminpw USING 'stream1 ';

4.2. Create a Stream queue

Run

SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE ();

PL/SQL procedure successfully completed.

5. synchronize data structures and data of stream1 and stream2

The main purpose is to import the user data structure and data of the primary database to the slave database. So that the data structure and data of the two databases are consistent.

You can use the primary database user to export dmp, import from the database or directly import from the database through db_link.

6. stream1-> stream2 Configuration

Use strmadmin to log on

 

6.1. Create propagation on stream1

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (

Schema_name => 'c4 ',

Streams_name => 'stream1 _ to_stream2 ',

Source_queue_name => 'strmadmin. streams_queue ',

Destination_queue_name => 'strmadmin. streams_queue@stream2.home ',

Include_dml => true,

Include_ddl => true,

Source_database => 'stream1. home ',

Required sion_rule => true,

Queue_to_queue => true );

END;

 

Schema_name => 'c4 'is the data object for configuring stream replication, that is, the oracle user. Schema_name in the following configuration has the same meaning.

 

You can view the result through dba_propagations:

SQL> select PROPAGATION_NAME, SOURCE_QUEUE_NAME, DESTINATION_QUEUE_NAME, DESTINATION_DBLINK, STATUS from dba_propagation;

PROPAGATION_NAME SOURCE_QUEUE_NAME DESTINATION_QUEUE_NAME DESTINATION_DBL STATUS

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

STREAM1_TO_STREAM2 STREAMS_QUEUE STREAM2.HOME ENABLED

 

If the STATUS is ENABLED, the creation is successful.

 

6.2. Create a Capture process on stream1.

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (

Schema_name => 'hr ',

Streams_type => 'capture ',

Streams_name => 'capture _ stream1 ',

Queue_name => 'strmadmin. streams_queue ',

Include_dml => true,

Include_ddl => true,

Inclusion_rule => true );

END;

You can view the following information through dba_capture:

SQL> select CAPTURE_NAME, QUEUE_NAME, START_SCN, STATUS, CAPTURE_TYPE from dba_capture;

 

If you find that the record whose CAPTURE_NAME is CAPTURE_STREAM1 is created successfully, the process has not been started, so the STATUS will be DISABLED.

 

6.3. Synchronize SCN with stream2 in stream1

DECLARE

V_scn NUMBER;

BEGIN

V_scn: = DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER ();

DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@stream2.home (

Source_schema_name => 'csv ',

Source_database_name => 'stream1. home ',

Instantiation_scn => v_scn,

Recursive => true );

END;

 

6.4. Create the apply process on stream2

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (

Schema_name => 'c4 ',

Streams_type => 'application ',

Streams_name => 'apply _ stream2 ',

Queue_name => 'strmadmin. streams_queue ',

Include_dml => true,

Include_ddl => true,

Source_database => 'stream1. home ',

Inclusion_rule => true );

END;

 

Select * from dba_apply using the query statement;

You can query the records whose APPLY_NAME is APPLY_STREAM2.

7. stream2-> stream1 Configuration

Use strmadmin to log on

 

7.1. Create propagation on stream2

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (

Schema_name => 'c4 ',

Streams_name => 'stream2 _ to_stream1 ',

Source_queue_name => 'strmadmin. streams_queue ',

Destination_queue_name => 'strmadmin. streams_queue@stream1.home ',

Include_dml => true,

Include_ddl => true,

Source_database => 'stream2. home ',

Required sion_rule => true,

Queue_to_queue => true );

END;

 

Schema_name => 'c4 'is the data object for configuring stream replication, that is, the oracle user. Schema_name in the following configuration has the same meaning.

 

You can view the result through dba_propagations:

SQL> select PROPAGATION_NAME, SOURCE_QUEUE_NAME, DESTINATION_QUEUE_NAME, DESTINATION_DBLINK, STATUS from dba_propagation;

PROPAGATION_NAME SOURCE_QUEUE_NAME DESTINATION_QUEUE_NAME DESTINATION_DBL STATUS

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

STREAM2_TO_STREAM1 STREAMS_QUEUE STREAM1.HOME ENABLED

 

If the STATUS is ENABLED, the creation is successful.

 

7.2. Create a Capture process on stream2

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (

Schema_name => 'hr ',

Streams_type => 'capture ',

Streams_name => 'capture _ stream2 ',

Queue_name => 'strmadmin. streams_queue ',

Include_dml => true,

Include_ddl => true,

Inclusion_rule => true );

END;

You can view the following information through dba_capture:

SQL> select CAPTURE_NAME, QUEUE_NAME, START_SCN, STATUS, CAPTURE_TYPE from dba_capture;

 

If you find that the record whose CAPTURE_NAME is CAPTURE_STREAM2 is created successfully, the process has not been started, so the STATUS will be DISABLED.

 

7.3. Synchronize SCN with stream1 in stream2

DECLARE

V_scn NUMBER;

BEGIN

V_scn: = DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER ();

DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@stream1.home (

Source_schema_name => 'csv ',

Source_database_name => 'stream2. home ',

Instantiation_scn => v_scn,

Recursive => true );

END;

 

7.4. Create the apply process on stream1

BEGIN

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (

Schema_name => 'c4 ',

Streams_type => 'application ',

Streams_name => 'apply _ stream1 ',

Queue_name => 'strmadmin. streams_queue ',

Include_dml => true,

Include_ddl => true,

Source_database => 'stream2. home ',

Inclusion_rule => true );

END;

 

Select * from dba_apply using the query statement;

You can query the records whose APPLY_NAME is APPLY_STREAM1.

 

8. Start

8.1. stream1-> stream2

All use strmadmin User Logon

Start apply on stream2:

SQL>

BEGIN

DBMS_APPLY_ADM.SET_PARAMETER (

Apply_name => 'apply _ stream2 ',

Parameter => 'Disable _ on_error ',

Value => 'n ');

END;

 

Result: PL/SQL procedure successfully completed.

 

SQL>

BEGIN

DBMS_APPLY_ADM.START_APPLY (

Apply_name => 'apply _ stream2 ');

END;

 

Result: PL/SQL procedure successfully completed.

Query the status using the following SQL statement:

SQL> select apply_name, queue_name, status from dba_apply;

APPLY_NAME QUEUE_NAME STATUS

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

APPLY_STREAM2 STREAMS_QUEUE ENABLED

 

Start capture on stream1:

 

SQL>

BEGIN

DBMS_CAPTURE_ADM.START_CAPTURE (

Capture_name => 'capture _ stream1 ');

END;

 

Query the startup status using the following SQL statement:

SQL> select capture_name, status from dba_capture;

CAPTURE_NAME STATUS

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

CAPTURE_STREAM1 ENABLED

 

8.2. stream2-> stream1

Start apply on stream1:

SQL>

BEGIN

DBMS_APPLY_ADM.SET_PARAMETER (

Apply_name => 'apply _ stream1 ',

Parameter => 'Disable _ on_error ',

Value => 'n ');

END;

 

PL/SQL procedure successfully completed.

 

SQL>

BEGIN

DBMS_APPLY_ADM.START_APPLY (

Apply_name => 'apply _ stream1 ');

END;

 

PL/SQL procedure successfully completed.

 

SQL> select apply_name, queue_name, status from dba_apply;

APPLY_NAME QUEUE_NAME STATUS

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

APPLY_STREAM1 STREAMS_QUEUE ENABLED

 

Start capture on stream2:

SQL>

BEGIN

DBMS_CAPTURE_ADM.START_CAPTURE (

Capture_name => 'capture _ stream2 ');

END;

SQL> select capture_name, status from dba_capture;

CAPTURE_NAME STATUS

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

CAPTURE_STREAM2 ENABLED

After the instance is started, the DML operations can be synchronized in real time by performing DDL operations on the two databases to verify that the configuration is successful.