This is a short cookbook to create schema replication between two 10g databases. For this example the two databases are called prod and dest. The prod database contains the two schemas that will be replicated (APP2 and APP3).
The start point is having two 10g databases, one of which holds the schema that will be replicated.
The cookbook will create bi-directional replication however, the changes needed to create one direction replication are noted. It will use the "maintain_schema" procedure to create the replication. The method uses Data Pump to export, transfer the dump file, and import the data.
Database Parameter Changes
*.job_queue_processes=10 #must be greater than 1. Default=10
*.processes=xxx #must add 4-6 Streams processes.
*.streams_pool_size=50M #memory allocated for Streams
The streams pool is where oracle does the replication work, log miner memory, queue memory, etc. If you are using sga_target, the streams_pool_size is the minimum size. The automatic memory management will grow the streams pool as needed. If you are not using sga_target, the parameter is the actual size.
A streams_pool_size of 50M is fine for testing, training but too small for most real systems.
alter system set global_names = true scope = both;
Global Names is not required for Streams Replication but it is used by Streams Replicaton so setting it to true is a good idea.
The Streams Administrator is the user that manages Streams Replication. Each database must have one. It can be any user (with the required grants) but normally a specific user is created to manage the replication.
The Streams Administrator should have its own tablespace and must be granted DBA.
CREATE USER strmsadmin
IDENTIFIED BY xxxxxxxxx
DEFAULT TABLESPACE strm_admin
QUOTA UNLIMITED ON strm_admin;
GRANT DBA TO strmsadmin;
In Oracle 10g and 11g a procedure is provided to grant all the necessary grants to the Streams Administrator.
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmsadmin',
grant_privileges => true);
END;
/
The Streams Administrator is created the same way on both servers. The Strmsadmin users are not required to have the same name or password on both servers but it is normal for them to have the same name.
This example uses the source database orcl and the remote database dest. Notice global names are used.
CREATE DATABASE LINK DEST.ORCL.LOCAL
CONNECT TO strmsadmin IDENTIFIED BY oracle123
USING 'DEST.ORCL.LOCAL';
Must be both ways for bi-directional replication.
CREATE DATABASE LINK PROD.ORCL.LOCAL
CONNECT TO strmsadmin IDENTIFIED BY oracle123
USING 'PROD.ORCL.LOCAL';
These links may be private or public links. The Streams Administrator must have access to them.
Test Your Links as the Streams Administrator!
SQL> select * from dual@DEST.ORCL.LOCAL;
This is used by datapump to instantiate the replicaiton. Any database directory can be used, here we create a specific directory for setting up streams. These directories are not ever used again by streams once the replication is created. The directories must be accessable by the oracle user and must be able to hold an export dmp and log file for whatever you are replicating. In this example the first directory is on the prod database and the second is on the dest database.
SQL> create or replace directory PROD_DR as '/u01/app/oracle/export';
SQL> create or replace directory DEST_DR as '/u01/app/oracle/export';
Normally you will pre-create the tablespaces on the destination database. You do not have to but if you do not pre-create them, Data Pump will try to create them to match the source database.
You also want to create users/tables that are not in the replication schema but are referenced by replicated objects.
The creation of the replication is accomplished with one command; however, it can take hours to complete the command. For this reason you may want to place the command in the background and allow it to run to completion even if the person that initiates the command logs off or the connection is dropped.
The actual command to instantiate the replication is below. This command will use Data Pump to move and create the two replicated schemas (APP2,APP3), build the replication components (Capture, Apply, Propagator) on each server, instaniate and set supplemental logging for all replicated tables. This command will create rule sets to replicate both DML and DDL.
This command is run by the Streams Administator.
set serveroutput on;
begin
dbms_streams_adm.maintain_schemas(
schema_names => 'APP2,APP3',
source_directory_object => 'PROD_DR',
destination_directory_object => 'DEST_DR',
source_database => 'PROD.ORCL.LOCAL',
destination_database => 'DEST.ORCL.LOCAL',
perform_actions => true,
dump_file_name => 'exp_app23.dmp',
capture_queue_table => 'rep_capt_table',
capture_queue_name => 'rep_capt_queue',
capture_queue_user => NULL,
apply_queue_table => 'rep_dest_table',
apply_queue_name => 'rep_dest_queue',
apply_queue_user => NULL,
capture_name => 'capture_pubs',
propagation_name => 'prop_pubs',
apply_name => 'apply_pubs',
log_file => 'exp_app23.log',
bi_directional => true,
include_ddl => true,
instantiation => dbms_streams_adm.instantiation_schema);
end;
/
When this command completes, the stream replication is built and is running.
Below is an explination of each variable.
schema_names => 'APP2,APP3'
This is a comma delinated list of schemas to be replicates. This procedure is actually overloaded to accept a varchar or a PL/SQL table of schema names.
source_directory_object => 'PROD_DR'
The directory on the source database where the export dump and log file are created.
destination_directory_object => 'DEST_DR'
The directory on the destination database where the export dump is transfered to and the import log file is created.
source_database => 'PROD.ORCL.LOCAL'
destination_database => 'DEST.ORCL.LOCAL'
This is where the global names is used, even if not set to true. These variables identify both the source (prod) and destination databases (dest) and the database links used to communicate with them.
perform_actions => true
This parameter tells the procedure to actually instantiate the replication. As an alternative you can have the procedure create a script of all the commands used in the replication instantiation.
perform_actions => fales,
script_name => 'schema_rep.sql'
script_directory_object => 'PROD_DR'
This will create a script in the PROD_DR directory location called schema_rep.sql that can be used to create the replication.
dump_file_name => 'exp_app23.dmp'
log_file => 'exp_app23.log'
The Data Pump dump and log file name.
capture_queue_table => 'rep_capt_table'
capture_queue_name => 'rep_capt_queue'
capture_queue_user => NULL
This is the queue name and the queue table name for the Capture Process. This name will be used on both the prod and dest databases. The queue user is the Streams Administrator.
apply_queue_table => 'rep_dest_table'
apply_queue_name => 'rep_dest_queue'
apply_queue_user => NULL
Same as the Capture above except for the Apply Process.
capture_name => 'capture_rep'
propagation_name => 'prop_rep'
apply_name => 'apply_rep'
These are the names for the Capture Process, the Apply Process and the Propagator. The names will be used on both the source and destination databases. These name should not be very long because the database name is appended to them and they can exceed the maximum length and cause the creation to thow an exception.
bi_directional => true
The tells the procedure to create bi-directional replication. If one way replication is desired, set the false.
include_ddl => true
This tells the procedure to create rule sets that replication DDL and DML. If you only want DML, set to false.
instantiation => dbms_streams_adm.instantiation_schema);
This tells the procedure to use this procedure to instantiate the replication.