Select Streams Global, Schema, Table and Subset Replication Wizard and click Continue.

Specify the Streams Administration username and password.

OR

If your designated Streams Administration user does not already exist in the source master database, click Create Streams Administrator button.

Enter a SYSDBA username and password, and the username and password for the Streams Administration user. Take note of the privileges that will be granted to the new user.

Click OK.

This will return you to the specify Streams Administrator screen, click NEXT.

Next, you will specify information for the destination or target database.

Again, if the Streams Administrator user does not exist on the target database, click Create Streams Administrator button.

Specify the appropriate information and click OK.

This will take you back to the configure destination database page. Click Next.

Directory objects

The Streams setup wizard will use Data Pump to instantiate the destination/target database. This is where you specify where the resulting dump and log files will be written/read. You can use an existing directory (click on the flashlight to select from a list), or create one. To make life easy, let's use one that already exists for Data Pump purposes. It's the DATA_PUMP_DIR (convenient isn't it?)

Options

This is where we specify what changes get sent (DML and/or DDL) and the direction in which they are sent. By default, the page selects only DML and bi-directional (master-to-master). In our example, we want a single-source master. So, we will uncheck Setup Bi-directional replication. We want to replicate DDL changes to any of our HR schema objects as well as DML (data) changes, so we will check Capture, Propagate, and Apply data definition language (DDL) changes.

Click Next.

Object selection

This is where we specify any or all objects in the database schema that we do or do not want to replicate. What you see presented to you on this page will depend on what replication rule you selected on the previous page (global, schema, table). You can select any or all of the schemas listed to replicate. Please note that the table shows only 10 at a time, so if you don't see the schema you want to at first, use the table navigation button to traverse the list. If you wish to exclude any tables that belong to your selected schemas, you can do so by adding them to the Exclude Tables list in the next section (click Add).

In our case, we want just the HR schema with all its tables.

Click NEXT.

This brings us to our Review summary sheet.

Review

The Summary of Operations lists the tasks in the order that the Setup wizard will accomplish them, based on the configuration parameters you have specified. A nice feature here is the Save Scripts button. This will allow you to save a copy of the generated SQL code to a file (default name is setup.sql).

In this example, the saved script will contain the PL/SQL to create the STRM_ADMIN user on both STRM1 and STRM2 instances and grant the necessary privileges, create the necessary database links, and then call the DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS procedure passing in the parameters you defined in the wizard. It is this DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS procedure that actually generates and executes the PL/SQL to build streams. We will see more on this in our "The code behind the curtain" section.

The Save operation will prompt you for the save confirmation and location. It then returns you to the Review page.

Click Finish.

You can choose to either set up streams immediately or schedule the setup for a later time (perhaps at night when users are not using the system).

In our case, we will run the setup Immediately. Note that you will also need to specify Host Credentials. This would be an O/S user and password. Please note that the user must have appropriate privileges to run jobs through the EM. For more information on the required privileges, please refer to your O/S specific Oracle maintenance manual. Click Submit.

This will bring you to the job confirmation page. It provides a link to the job monitoring page if you wish to monitor the progress of the job.

Click OK, and you will return to the initial Streams setup page.

When the job is complete, you can verify your Streams environment from the Streams Management page. Navigate to the Data Movement page and select Manage under Streams.

This will take you to the Overview page. Click on Capture.

Notice that the Capture process is enabled. This means your process is currently capturing changes. To stop/start (disable/enable) the process, click the radio button under the select column for the Capture process, and then click Start or Stop.

You can view process statistics by clicking on Statistics.

Click on the Streams breadcrumb link to return to the Capture page.

Click on the Propagation link. Verify the status is Enabled.

You can view the information and propagation rule sets by clicking on View.

Click on the Streams breadcrumb link to return to the Propagation page.

We then go to our destination site EM DBConsole and we see our Apply process and verify that it is enabled.

At our destination site (STRM2), the absence of a Capture process for the schema and a Propagation process back to STRM1 ensures uni-directional single-source master replication. However, it does not keep changes from being made directly to the destination site. So, it is still possible for data to diverge, and lead to data conflicts. To avoid this, we recommend implementing site priority conflict resolution error handling at the destination site (see Chapter 5, N-Way Replication, for conflict resolution).

When you use the EM Streams setup wizard to generate the code to configure your single source, EM generates and runs PL/SQL that does the following:

The call to a DBMS_STREAMS_ADM.MAINTAIN_* procedure sets parameters that tell the procedure what to capture, propagation, and to apply queues and processes to create and where to create them, as well as script naming and location specification. You can also direct the procedure to only generate the the necessary scripts and not run them. For specific details on the DBMS_STREAMS_ADM.MAINTAIN_* procedures, please refer to your Oracle PL/SQL Packages and Types Reference Manual.

In our example, it is the DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS procedure.

The DBMS_STREAMS_ADM.MAINTAIN_* procedure in turn will generate and run scripts that set up single-source replication by doing the following:

It is possible to run the DBMS_STREAMS_ADM.MAINTAIN_* procedure to generate the PL/SQL to build the Streams, but not actually run it. This is done by setting the perform_actions parameter to "FALSE".

To generate a full set of scripts, you can begin by running through the EM Streams setup wizard, save the script at the review page, and then cancel out the wizard without finishing. You can edit the saved script, setting the perfrom_actions parameter to "FALSE", and run the script from SQLPLUS. The procedure will create the SQL script to set up the Streams to directory specified by the script_directory_object parameter with the filename by the script_name parameter. This script can then be opened and viewed with a standard text editor. This file can then be run as a standalone script. Keeping a copy of these scripts also provides a way to quickly and accurately recreate the Streamed configuration between the two databases from the command line, should the need arise.

In this section, we are going to look at the PL/SQL commands needed to configure single-streams replication between our two databases. The scripts created by the EM and DBMS_STREAMS_ADM.MAINTAIN_* procedure do pretty much the same thing though there may be some minor differences in order of operations and the secondary version checks which we chose to skip here. The PL/SQL presented here is formatted to be run, stand-alone for each step. This will allow you to "play" with each step, individually. You can download these scripts from the Packt website.

The first step is the creation of the Streams Administrator user and granting of privileges at both the source master and destination target databases. This must be done by a SYSDBA user. The code is in the create_strmadmin.sql script and does the following:

For more detailed information on the privileges for the Streams Administrator, reference the "Configure an Oracle Streams Administrator" of the Oracle Streams Concepts and Administration manual for 11gR1, or the Oracle Streams Replication Administrator's Guide for 11gR2.

--create_strmadmin.sql…
set echo on;
ACCEPT src PROMPT 'Enter tnsalias for the source database:'
ACCEPT dba_pwd_src PROMPT 'Enter Password of user "sys" to create Streams Admin at Source : ' HIDE
ACCEPT strm_pwd_src PROMPT 'Enter Password of Streams Admin "strm_admin" to be created at Source : ' HIDE
ACCEPT dest PROMPT 'Enter tnsalias for the target database:'
ACCEPT dba_pwd_dest PROMPT 'Enter Password of user "sys" to create Streams Admin at Destination : ' HIDE
ACCEPT strm_pwd_dest PROMPT 'Enter Password of Streams Admin "strm_admin" to be created at Destination : ' HIDE
PROMPT connecting as sys at &src
connect sys/&dba_pwd_src@&src as SYSDBA;
PROMPT
PROMPT creating strm_admin user
create user strm_admin identified by &strm_pwd_src;
PROMPT granting privs
grant DBA, IMP_FULL_DATABASE, EXP_FULL_DATABASE to strm_admin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strm_admin',
grant_privileges => true);
END;
/
COMMIT;
/
PROMPT connecting as sys at &dest
connect sys/&dba_pwd_dest@&dest as SYSDBA;
PROMPT
PROMPT creating strm_admin user
create user strm_admin identified by &strm_pwd_dest;
PROMPT granting privs
grant DBA, IMP_FULL_DATABASE, EXP_FULL_DATABASE to strm_admin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strm_admin',
grant_privileges => true);
END;
/
PROMPT strm_admin create and privs complete
/
COMMIT;
/

Next, we connect as strm_admin and create a database link from our source database to our target database.

connect strm_admin/&strm_pwd_src;
--if the STRM2 dblink already exists you can drop it.
--DROP DATABASE LINK STRM2;
CREATE DATABASE LINK STRM2 connect to strm_admin identified by &strm_pwd_dest using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS= (PROTOCOL=TCP)(HOST=strm_target)
(PORT=1521)))(CONNECT_DATA=(SID=strm2)(server=DEDICATED)))';
COMMIT;
/
--end code

Notice that we used the full TNS connect description rather than the alias for the using parameter? Using the full connect description instead of a TNS alias avoids issues with tnsnames.ora file configurations. This removes any dependency of the database link on tnsnames.ora files that can be changed, moved, or deleted.

Next, we need to add supplemental logging to the tables in the HR schema at the source master database. This needs to be done for each table.

The command to do this is:

ALTER TABLE "HR"."<table_name>" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, -FOREIGN KEY, UNIQUE INDEX) COLUMNS';

In the setup script generated by the DBMS_STREAMS_ADM.MAINTAIN_* procedure, you will see the command repeated and hard-coded for each table in the schema. The following code block below accomplishes the same actions, but dynamically, so it does not need to be edited if table names change, or if tables are added or dropped. This code is found in the add_supplog_schema.sql script and does the following:

Next we set up the capture queue on our source master (STRM1). This code is found in the create_capture_queue.sql script. The script should be run as the STRM_ADMIN user and does the following:

Next, create the propagation rules for the schema. Note that the propagation job is created in an enabled state, so we want to disable it until we are finished setting up the apply queue on the destination site. This code is found in the add_schema_propagation.sql script. The script should be run as the STRM_ADMIN user and does the following:

Next we add the schema capture rule. This code is found in the add_schema_capture.sql script. The script should be run as the STRM_ADMIN user and does the following:

Next, we will want to instantiate the HR schema. We do this using Data Pump to export the HR schema to a dump file that will then be imported into the destination target database. In our example, the HR schema does not exist at our destination site. If it did exist, we would want to drop the HR schema at the destination site so that the Data Pump import is able to identically reproduce it at the destination site.

First, we will do the Data Pump export. This code is found in the instantiate_exp_schema.sql script. The script will connect as the STRM_ADMIN user and do the following:

Now, we move to the destination site.

First, we need to instantiate the HR schema, and then create the Apply process. We use Data Pump import to instantiate the HR schema and data. It is important to remember that Data Pump will create the objects in the same tablespace in which they reside at the source database. So, our import is going to need the EXAMPLE tablespace. Make sure you create the EXAMPLE tablespace in the destination database if you have not already done so. You can do this through the EM DBConsole. Or, you can issue the create tablespace command.

For example:

CREATE SMALLFILE TABLESPACE "EXAMPLE" DATAFILE 'C:ORACLE11GR2ORADATASTRM1EXAMPLE01.DBF' SIZE 100M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Now that we have our tablespace in place, we can begin the import. This code is found in the instantiate_imp_schema.sql script. This will connect as the STRM_ADMIN user and do the following:

Check to see if it needs to transport the databfile based on the source and destination directory locations you specify. If these are different, dbms_file_transfer.put_file is called.

Note some caveats here:

Next, create the apply queue at the destination target. This code is found in the add_apply.sql script, it should run as the STRM_ADMIN user and do the following:

By using the dbms_streams_adm.add_schema_rules procedure to add our Capture process at the source-master database, we automatically prepared our schema object SCN instantiation value. This means that the dbms_streams_adm.add_schema_rules already made a call to the dbms_capture_adm.prepare_schema_instantiation procedure for us.

If we had used dbms_capture_adm, we would need to call the dbms_capture_adm.prepare_schema_instantiation procedure explicitly.

As we used Data Pump export/import to instantiate our schema at the destination target database, the instantiation SCN has also been set for us.

If we had used other instantiation methods, we would need to call DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN manually.

With our instantiation SCNs synchronized, we are now ready to start our Apply process at our destination database, our Propagation process at our source database, and finally our Capture process at our source database; in that order. We do it in this order to allow the apply and Propagation processes time to "ramp up" before getting work from the Capture process. This code is found in the start_it_all_up.sql script. The script runs as the STRM_ADMIN user and does the following:

First, we start at our destination database and start the Apply process.

--start_it_all_up.sql…
ACCEPT destdb PROMPT 'Enter Destination DB tnsalias: (ex: STRM2): '
ACCEPT stmadm PROMPT 'Enter Stream Admin username: '
ACCEPT stmadmpwd PROMPT 'Enter Stream Admin Password: '
ACCEPT srcsdb PROMPT 'Enter Source tnsalias: (ex: STRM1): '
ACCEPT qname PROMPT 'Enter Queue Name prefix: (ex: SCHEMA_HR) '
--First connect to the destination database and start the Apply process.
PROMPT 'connecting to &destdb as &stmadm and starting &qname._APPLY'
connect &stmadm/&stmadmpwd@&destdb
BEGIN
dbms_apply_adm.start_apply(
apply_name => '&qname._APPLY'),
dbms_lock.sleep(10); --give it time to start
EXCEPTION WHEN OTHERS THEN
IF sqlcode = -26666 THEN NULL; -- APPLY process already running
ELSE RAISE;
END IF;
END;
/

Next, connect to the source database and start the Propagation process.

--NOTE: this code assumes the db link name is the same as the tnsalias
-- if this is not the case, you will need to accommodate for the
-- destination parameter in the enable_propagation_schedule call
PROMPT 'connecting to &srcsdb as &stmadm
connect &stmadm/&stmadmpwd@&srcsdb
PROMPT 'starting &qname._PROPAGATION'
BEGIN
dbms_aqadm.enable_propagation_schedule(
queue_name => '&stmadm..&qname._CAPTURE_Q',
destination => '&destdb',
destination_queue => '&stmadm..&qname._APPLY_Q'),
dbms_lock.sleep(10); --give it time to start
EXCEPTION WHEN OTHERS THEN
IF sqlcode = -24064 THEN NULL; -- propagation already enabled
ELSE RAISE;
END IF;
END;
/
--Finally, start the Capture process
PROMPT starting &qname._CAPTURE'
BEGIN
dbms_capture_adm.start_capture(
capture_name => '&qname._CAPTURE'),
dbms_lock.sleep(10); --give it time to start
EXCEPTION WHEN OTHERS THEN
IF sqlcode = -26666 THEN NULL; -- CAPTURE process already running
ELSE RAISE;
END IF;
END;
/
select apply_name || ' is ' ||status apply_status from dba_apply@&destdb
where apply_name like upper('%&qname.%')
/
select propagation_name || ' is ' ||status apply_status from dba_propagation
where propagation_name like upper('%&qname.%')
/
select capture_name || ' is ' ||status apply_status from dba_capture
where capture_name like upper('%&qname.%')
/
--end code

The process for setting up single-source replication from STRM1 to STRM2 is now complete.

Now that we have created our single source environment, let us see if it works!

We will log into SQLPlus as the HR user at our source master (STRM1) and create a record in the employee table.

We will then log into SQLPlus as the HR user at our destination target (STRM2) and see if the new record appears.

In this screenshot for our STRM1 SQLPlus session, we will do the following:

In this screenshot for our STRM2 SQLPlus session, we first show the current user and the global name of our instance to verify where we are.

We then show that there are no records in the employee table that has "McKinnell" for the last_name.

After we insert the "McKinnell" record into the employee table at STRM1 and commit, we rerun the select at STRM2 to see if the record is now there.

Voila! Our record has been replicated.

As mentioned earlier, the EM setup wizard generates a PL/SQL block with a call to the appropriate DBMS_STREAMS_ADM.MAINTAIN_* subprogram. This PL/SQL block can be run manually from SQLPLUS if you wish. These scripts can save you a lot of time and headaches.

What happens if the DBMS_STREAMS_ADM.MAINTAIN_* scripts fail? How do you recover? The beauty of the DBMS_STREAMS_ADM.MAINTAIN_* scripts is that they record their progress in the database and "know" where it left off. If the script fails, it will raise an error and add an entry in the DBA_RECOVERABLE_SCRIPT view (and other DBA_RECOVERABLE_SCRIPT views). You can then use the DBMS_STREAMS_ADM.RECOVER_OPERATION procedure to do the following:

If you want to use the forward recover, do not rollback or purge.

The caveat here is that DBMS_STREAMS_ADM.RECOVER_OPERATION will only recover a Streams build done through a DBMS_STREAMS_ADM.MAINTAIN_* procedure.

The DBMS_STREAMS_ADM.MAINTAIN_* scripts also provide an excellent baseline with which to begin scripting a custom Streams topology.