Setup Two databases
Use DBCA to create two 11g databases on the same server or on two different servers
In this sample let us assume that we are using databases RK01 and RK02
Setup the streams related initialization parameters in both databases
Since it is simpler to set parameters up in a pfile as opposed to a spfile, use the create pfile from spfile command to create pfiles on both databases.
On RK01 add the following lines to the pfile
# Added for streams
*.global_names=TRUE
*.job_queue_processes=10
*.log_archive_dest_1=’LOCATION=/u01/app/orarch/RK01 MANDATORY’
*.log_archive_dest_state_1=ENABLE
*.log_archive_format=’RK01_%t_%s_%r.arc’
*.parallel_max_servers=12
*.processes=100
*.service_names=’RK01′
*.streams_pool_size=20
Add the following lines to the pfile for RK02
#Added for streams
*.global_names=TRUE
*.job_queue_processes=10
*.log_archive_dest_1=’LOCATION=/u01/app/orarch/RK02 MANDATORY’
*.log_archive_dest_state_1=ENABLE
*.log_archive_format=’RK02_%t_%s_%r.arc’
*.parallel_max_servers=12
*.processes=100
*.service_names=’RK02′
*.streams_pool_size=20
use the hr_main script ($ORACLE_HOME/demo/schema/human_resources) to create the hr schema in your first database (RK01). Do not create this schema in RK02, since it will get created when you run the streams schema maintenance script
Login as sys into RK01 and create a directory audit_dir pointing to /tmp/
Create the streams administrator in both databases
GRANT DBA TO strmadmin IDENTIFIED BY manager;
ALTER USER strmadmin DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
Create database links in both databases (As strmadmin)
On RK01
CREATE DATABASE LINK rk02 CONNECT TO strmadmin
IDENTIFIED BY manager USING ‘rk02’;
On RK02
CREATE DATABASE LINK RK01 CONNECT TO strmadmin
IDENTIFIED BY manager USING ‘RK01’;
Create tnsnames.ora entries to provide connectivity between the databases
Start the database with the new pfile (That has the streams related parameters set)
Set the databases to be in Archivelog mode
Setup Streams
Use DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS to create a script to setup schema replication
BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => ‘hr’,
source_directory_object => ‘audit_dir’,
destination_directory_object => ‘audit_dir’,
source_database => ‘RK01’,
destination_database => ‘RK02’,
capture_name => ‘capture_rk01’,
capture_queue_table => ‘rep_capture_queue_table’,
capture_queue_name => ‘rep_capture_queue’,
capture_queue_user => null,
apply_name => ‘apply_rk01’,
apply_queue_table => ‘rep_dest_queue_table’,
apply_queue_name => ‘rep_dest_queue’,
apply_queue_user => null,
propagation_name => ‘prop_rk01’,
log_file => ‘exp.log’,
bi_directional => true,
include_ddl => true,
instantiation => dbms_streams_adm.instantiation_schema_network,
perform_actions => false,
script_name => ‘schema_replication_ii.sql’,
script_directory_object => ‘audit_dir’
);
END;
Login as sys on RK01 and run the script
Test replication
