Thursday, July 26, 2012

Configure one way schema replication with Oracle Streams

Oracle Streams can be used to perform replication of databases , schemas , tablespaces and tables between heterogeneous O/S and different DB versions.


In this post i will present the procedure to replicate a schema from a 10gR2 Db on Windows to a 11gR2 DB on RHEL. The instantiation of the schema will take place using Data Pump import (impdp) using the network (via the DB Links that will be created)


A. Configuration Parameters for both Source and Target 



1. COMPATIBLE must be >=10.2.0.0
2. GLOBAL_NAMES = TRUE
3. JOB_QUEUE_PROCESSES >=2
4. OPEN_LINKS >=4
5. UNDO_RETENTION >=3600
6. AQ_TM_PROCESSES
From 10gR2 and up is it advisable to be unset and let system autotune. If it was set to a specific value then the recomended is to set to 1

B.  Create Oracle Streams administrator user on both DBs with the same credentials. Its advisable to create a specific tablespace for this user and give him the necessary privileges.

As SYSDBA user:

create user strmadmin identified by strmadmin  
  default tablespace streams_tbs quota  unlimited on streams_tbs;
 
grant connect, resource, aq_administrator_role,dba to strmadmin;  

begin  
  dbms_streams_auth.grant_admin_privilege(  
grantee => 'strmadmin',  
grant_privileges => true);  
end;  
/  
commit;

C. Create the directory object which will be used to store the script that will enable the Streams on both source and target DB Server

create directory streams_files as '[DIRECTORY_PATH]';

grant read,write on directory streams_files to strmadmin;

The directory path can be different on source and target DB server.

D. Create the appropriate TNS entries and both source and target

Ensure that you will use the global_name of each database to the TNS connect string and that DB Link is working properly from both DBs.

As The Streams Administrator User

create database link [DB_GLOBAL_NAME] connect to strmadmin identified by strmadmin using '[DB_TNS_ENTRY]';

E. Execute on target the following script to replicate schema AGIS which has a table named T for the test purposes

As Streams Admin User on Source DB

begin
  dbms_streams_adm.maintain_schemas(
  schema_names=> 'agis',
  source_directory_object=> null,
  destination_directory_object=> null,
  source_database=> 'ODIN.NET1.CEC.EU.INT',
  destination_database => 'EROSD.NET1.CEC.EU.INT',
  perform_actions => true,
  script_name =>'Schema_maintain_streams.sql',
  script_directory_object=>'streams_files',
  bi_directional=> false,
  include_ddl => true ,
  instantiation=>dbms_streams_adm.instantiation_schema_network);
  commit;
end;
/

If The PL/SQL block terminates without errors then check Streams Functionality.

REMARKS 
"instantiation=>dbms_streams_adm.instantiation_schema_network" on the procedure will instantiate the desired schema using impdp via DBLINK.

F. Test that Streams Replication is working

For example : 

- Insert some records on T@SOURCE and check if the changes are propagated on T@TARGET
- Add a new column on T@SOURCE and check if the changes are propagated on T@TARGET
etc

REFERENCES
Oracle Support Note ID: 878638.1









No comments:

Post a Comment