Oracle Streams is a tool which replicates information between databases using Advance Queuing in almost real-time. It has three main components: Capture Process, Propagation Process and Apply Process. Capture is the process which you capture the data in the source database. Propagation is the process which transfers the data from the source database to the target database. The Apply Process copies the data into the target database, once the data arrives to the target database. Oracle Streams is easy to setup and use, and would be the perfect candidate to have data available in two or more database systems in sync for whatever reason.
There are multiple ways to configure Oracle Streams but it seems to be only two ways to capture the database changes implicitly: the Asynchronous Redo Log Capture and the Synchronous Capture. In this case we are going to use the second way because we are going to capture only the DML changes.
Now we are going to take baby steps over the configuration of Oracle 11g Streams by using the Synchronous Capture. For the exercise, first, we will configure two databases; the first instance called SOURCEDB for the source database and the second instance called TARGETDB for the target database (the databases are located in two different servers, Oracle Linux in my case). Second, we are going to create the users for streams and the tables for replication and, finally, we are going to create two administrator schemas on those databases and the processes for the capture, propagation and apply.
Step by step configuration
1. Configure the tnsnames file in the source server
In this step, we are going to configure the local and remote connections in the source server. I have selected the next names: LOCAL_SOURCE_CONNECTION for the local connection in the source server and REMOTE_TARGET_CONNECTION to perform the connection with the target database from the source server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/orcl/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LOCAL_SOURCE_CONNECTION =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ODIGettingStarted)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
REMOTE_TARGET_CONNECTION =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.57.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
|
2. Configure the tnsnames file in the target server
In this step, we are going to configure the local and remote connections in the target. I have selected the next names: LOCAL_TARGET_CONNECTION for the local connection in the target server and REMOTE_SOURCE_CONNECTION to perform the connection with the source database from the target server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/orcl/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LOCAL_TARGET_CONNECTION =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ODIGettingStarted)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
REMOTE_SOURCE_CONNECTION =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.57.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
|
3. Create the user and table for replication on both databases
In this step, as sysdba, we are going to create the user u_owner and the table test1 on SOURCEDB and TARGETDB, which they will be used for the replication.
CREATE USER U_OWNER IDENTIFIED BY U_OWNER DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CONNECT, RESOURCE TO u_owner; CREATE TABLE u_owner.test1 ( firstname VARCHAR2(255) NOT NULL, lastname VARCHAR2(255) NOT NULL );
4. Create the user for streams on both databases
Now, as sysdba, we are going to create the user stradmin on SOURCEDB and TARGETDB with its own streams tablespace.
CREATE TABLESPACE streams_tbs DATAFILE 'streams.dbf' SIZE 25M AUTOEXTEND ON MAXSIZE 256M; CREATE USER stradmin IDENTIFIED BY stradmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs; GRANT DBA TO stradmin;
5. Create a dblink on source database
In this step, as stradmin, we are going to create a dblink to connect from source database to target database.
CREATE DATABASE LINK TARGETDB CONNECT TO stradmin IDENTIFIED BY stradmin USING 'REMOTE_TARGET_CONNECTION';
6. Create a dblink on target database
In this step, as stradmin, we are going to create a dblink to connect from target database to source database.
CREATE DATABASE LINK SOURCEDB CONNECT TO stradmin IDENTIFIED BY stradmin USING 'REMOTE_SOURCE_CONNECTION';
7. Create the capture process on source database
Now, as stradmin, we are going to create the capture process on SOURCEDB by using Oracle Advanced Queuing to capture the DML changes. First, we are going to create a queue and next we are going to add a table which we want to capture the changes from. Remember, in synchronous capture, we won't start the capture process because it starts automatically once you have created the table rule.
Setting up the queue:
BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_table => 'stradmin.SOURCEDB_queue_table', queue_name => 'stradmin.SOURCEDB_queue', queue_user => 'stradmin'); END; /
Adding the table rules:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name => 'u_owner.test1', streams_type => 'SYNC_CAPTURE', streams_name => 'SYNC_CAPTURE', queue_name => 'stradmin.SOURCEDB_queue', include_dml => true, include_ddl => false, include_tagged_lcr => false, inclusion_rule => true, source_database => 'ORCL'); END; /
If you want to remove the queue, you can run the next:
EXEC DBMS_STREAMS_ADM.REMOVE_QUEUE('stradmin.SOURCEDB_queue', TRUE, TRUE);
8. Create the propagation process on source database
In this step, as stradmin, we are going to create the propagation between source database and target database. Then, we add the table propagation rules on SOURCEDB.
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(table_name => 'u_owner.test1', streams_name => 'SOURCEDB_propagation', source_queue_name => 'stradmin.SOURCEDB_queue', destination_queue_name => 'stradmin.TARGETDB_queue@TARGETDB', include_dml => TRUE, include_ddl => FALSE, include_tagged_lcr => FALSE, source_database => 'ORCL', inclusion_rule => TRUE, queue_to_queue => TRUE); END; /
You can start the propagation again if this is stopped for any reason:
BEGIN DBMS_PROPAGATION_ADM.START_PROPAGATION(propagation_name => 'SOURCEDB_propagation'); END; /
To stop the propagation, you can run this:
BEGIN DBMS_PROPAGATION_ADM.STOP_PROPAGATION(propagation_name => 'SOURCEDB_propagation', force => true); END; /
To remove the propagation, you can run the next:
BEGIN DBMS_PROPAGATION_ADM.DROP_PROPAGATION(propagation_name => 'SOURCEDB_propagation', drop_unused_rule_sets => true); END; /
To see the errors:
SELECT propagation_name, status, error_message FROM dba_propagation;
9. Create the apply process on target database
Now, as stradmin on TARGETDB, we are going to create a queue and the rules for the apply process.
Setting up the queue:
BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_table => 'stradmin.TARGETDB_queue_table', queue_name => 'stradmin.TARGETDB_queue', queue_user => 'stradmin'); END; /
Creating the apply process:
BEGIN DBMS_APPLY_ADM.CREATE_APPLY(queue_name => 'stradmin.TARGETDB_queue', apply_name => 'sync_apply', apply_captured => false); END; /
Creating the rules for the apply process:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name => 'u_owner.test1', streams_type => 'APPLY', streams_name => 'SYNC_APPLY', queue_name => 'stradmin.TARGETDB_queue', include_dml => true, include_ddl => false, include_tagged_lcr => false, source_database => 'ORCL'); END; /
Adding a subscriber for the target queue (to solve ORA-24033 no recipients for message):
DECLARE subscriber sys.aq$_agent; BEGIN subscriber := sys.aq$_agent('SOURCEDB_queue', 'stradmin.SOURCEDB_queue@SOURCEDB', null); DBMS_AQADM.ADD_SUBSCRIBER( queue_name => 'stradmin.TARGETDB_queue', subscriber => subscriber); END; /
10. Insert data into source table
In this step, we are going to insert some data into source table before to begin the replication.
Inserting the data:
INSERT INTO u_owner.test1 VALUES ('Cristiano', 'Ronaldo'); 1 row created. COMMIT; Commit complete.
As you can see, the capture queue have captured one record.
SELECT COUNT(1) FROM SOURCEDB_queue_table; COUNT(1) ---------- 1
11. Configure the target database for replication
In this step, as stradmin on TARGETDB, we are going to instantiate the target database "replica" table with the SCN number. This creates a point in time on the target table to start the acceptance of the changes incoming from the source database table and inserts the old data immediately.
Creating a point of no turning back:
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER APPLY_SCN FROM DUAL; APPLY_SCN ---------- 4261994 INSERT INTO u_owner.test1 (SELECT * FROM u_owner.test1@SOURCEDB AS OF SCN 4261994); 1 row created. COMMIT; Commit complete. BEGIN DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'u_owner.test1', source_database_name => 'ORCL', instantiation_scn => 4261994); END; /
Checking old data have been copied:
SELECT * FROM u_owner.test1; FIRSTNAME -------------------------------------------------------------------------------- LASTNAME -------------------------------------------------------------------------------- Cristiano Ronaldo
If you need to delete any instantiation, you only pass null in the instantiation_scn parameter as follow:
BEGIN DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'u_owner.test1', source_database_name => 'ORCL', instantiation_scn => null); END; /
Also, you can confirm that the instantiation is complete by selecting rows from dba_apply_instantiated_objects table.
12. Start the apply process and test the entire process
Finally, we are going to start the apply process and test the entire process. We will insert or update some data into source table and then we will check the data has been replicated into the target table.
Starting the apply process:
BEGIN DBMS_APPLY_ADM.START_APPLY('SYNC_APPLY'); END; /
Inserting data into source table:
INSERT INTO u_owner.test1 VALUES ('Lionel', 'Messi'); 1 row created. COMMIT; Commit complete.
Checking the replication:
SELECT * FROM u_owner.test1; FIRSTNAME -------------------------------------------------------------------------------- LASTNAME -------------------------------------------------------------------------------- Lionel Messi Cristiano Ronaldo
To stop the apply, you can run this:
EXEC DBMS_APPLY_ADM.STOP_APPLY('SYNC_APPLY');
To drop the apply, you can run the next:
EXEC DBMS_APPLY_ADM.DROP_APPLY('SYNC_APPLY', TRUE);
To see the errors:
SELECT apply_name, queue_name, error_message FROM dba_apply_error;
Additionally, if you need to delete all errors generated by the apply process, you can run this:
EXEC DBMS_APPLY_ADM.DELETE_ALL_ERRORS('sync_apply');
The replication should have worked correctly. If everything went alright, your DML will be propagated across from source to target, as a logical change record (LCR).
