In essence, allows Oracle Streams to replicate this scheme on a stream in a db db destination, in addition, any DDL or DML performed under schema to be replicated on the source will also check the db schema replicated in the destination db.
on OLTP databases, the process of extracting data can not be run directly as it interferes with normal DDL / DML, because the architecture uses Oracle Streams ARCHIVELOG, decidedly intrusive chin. Before you can use Oracle a stream, you must ensure that they meet certain prerequisites:
1) configure properly the two db server so that you can run tnsping db server from source to destination and vice versa 2) put both in ARCHIVELOG MODE databases
3)
create a user for the management of the stream to both Oracle database 
4) create a db link to the initial creation, only to run the first time, the db replica on the target schema using the Data Pump utilities 
5) create a db link from the source to the destination db, useful in cases where the initial creation of the schema replication occurs at the network without using an intermediate dmp file (I also created this db link anyway) 6) configuration utilities and expdp impdp both the source database is on target qyello 7)
create a directory and a corresponding Oracle db file system directory on the source server to host the script generated by the DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS that deals with the schema replication via Oracle streams.
Assuming for convenience that the points 1) and 2) are already satisfied, I start directly from step 3). Henceforth, the term "PROD" identifies the source database and the letter "TEST" identifies the target. The scheme will be to replicate the ubiquitous "SCOTT!"
1) OK 2) OK 3) create a user on the management of the stream, and on both PROD to TEST, connect as user SYS as SYSDBA:
streams_tbs CREATE TABLESPACE DATAFILE '/ u01/app/oracle/oradata / streams_tbs.dbf ' SIZE 25M;
strmadmin CREATE USER IDENTIFIED BY DEFAULT TABLESPACE strmadminpw streams_tbs
QUOTA UNLIMITED ON streams_tbs; GRANT DBA TO strmadmin;
BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE (
grantee => 'strmadmin', grant_privileges => true ) END;
4) Set a db link from PROD to TEST for the initial import of the schema to be replicated:
- to connect strmadmin
PROD / PROD strmadminpw @
CREATE DATABASE LINK TEST CONNECT TO
strmadmin
STRMADMINPW
IDENTIFIED BY USING 'TEST';
5) Now create a db link from TEST in PROD:
- connect to TEST
strmadmin / strmadminpw TEST @ PROD
CREATE DATABASE LINK TO CONNECT
strmadmin
STRMADMINPW
IDENTIFIED BY USING 'PROD';
6) configure the data pump utilities of the two db server by creating an Oracle directory called "source" on the PROD db server hosting and a directory of Oracle named "dest" on the db server hosting TEST. The two-level operating system directories are created with the appropriate operating system commands (mkdir, for GNU / Linux):
- up conn sys @ PROD PROD
as sysdba CREATE OR REPLACE DIRECTORY source AS '/ u01/app/oracle/oradata/source ';
GRANT READ, WRITE ON DIRECTORY TO strmadmin source;
- to TEST
conn sys @ TEST AS SYSDBA CREATE OR REPLACE DIRECTORY
dest AS '/ u01/app/oracle/oradata/dest';
GRANT READ, WRITE ON DIRECTORY dest TO strmadmin;
7) create an Oracle directory and a corresponding file system directory on the db server that hosts the database PROD. This directory will contain the script to run for the SCOTT schema replication from PROD to TEST via Oracle streams:
strmadmin CONNECT / @ PROD strmadminpwsource_database => 'prod',
CREATE OR REPLACE DIRECTORY SCRIPT_DIR AS '/ home / oracle / script_dir';
Once satisfied all the requirements, the db server hosting PROD I have created a strmadmin script to run as user with the following contents:
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS (
schema_names => 'scott',
destination_database => 'test',
capture_name => 'capture_scott',
capture_queue_table => 'rep_capture_queue_table',include_ddl => true, instantiation =
capture_queue_name => 'rep_capture_queue',
capture_queue_user => null,
apply_name => 'apply_scott',
apply_queue_table => 'rep_dest_queue_table',
apply_queue_name => 'Rep_dest_queue',
apply_queue_user => null,
propagation_name => 'prop_scott',
log_file => 'exp_scott.log',
bi_directional => false,
> dbms_streams_adm.instantiation_schema,
perform_actions => false,
script_name => 'schema_replication.sql',END;
script_directory_object => 'script_dir'
)
I would say that is a must for an explanation of the basic parameters Otherwise, it does not understand much: *
schema_names:
the schema name (or patterns, separated by commas) to propagate (in this case Scott) *
source_database:
the source database
destination_database *:
the target database
* capture_name:
the name of the capture process configured to capture changes made on PROD
* capture_queue_table:
append the table name for each queue used by a capture process
* capture_queue_name :
the name of each queue used by a capture process
* capture_queue_user:
be set to null to indicate that the procedure can not give the grant of any privilege
* propagation_name:
spread the name of each configured to propagate changes
* log_file: the name of the log file generated by expdp
* bi_directional: if set to 'false' indicates that the delegate spread is one-way information from the production db to test
* include_dll: set to 'true' indicates that both the DDL and DML ones are replicated from the source db to the destination
* instantiation: if set to DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA indicates that the instance of the target schema will be performed using the utilities expdp / impdp
* perform_action: if set to 'true' allows the script PL / SQL, when set to 'false' stores the result in another script that will be placed in the directory indicated the parameter 'script_directory_object'
* script_name: set with the string 'schema_replication.sql'. This script will be created in Oracle directory 'script_dir' (/ home / oracle / script_dir) and will contain all the steps to instantiate the schema replication
* script_directory_object: the Oracle directory to store the script 'schema_replication.sql'
Flipping the script Oracle does not start the stream, so even the replication process on the user SCOTT db TEST, but the script is created schema_replication.sql in / home / oracle / script_dir that will always run in part as a user strmadmin @ PROD. The creation of intermediate script checks to see if there were errors during the execution of the procedure DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA, alternatively, you can always skip the middle and create the script directly launch the procedure DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA with these parameters:
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS ( schema_names => 'scott',
source_database => 'prod', destination_database => 'test', capture_name => 'capture_scott',
capture_queue_table => 'rep_capture_queue_table', capture_queue_name => 'rep_capture_queue', capture_queue_user => null,
apply_name => 'apply_scott', apply_queue_table => 'rep_dest_queue_table', apply_queue_name => 'rep_dest_queue',
apply_queue_user => null, propagation_name => 'prop_scott', log_file => 'exp_scott.log',
bi_directional => false, include_ddl => true, instantiation = > dbms_streams_adm.instantiation_schema,
perform_actions => true ) END;
be noted that the "perform_actions" is now set to true and not to guidelines "script_name" and "script_directory_object.
If everything works correctly, the TEST db will be an exact replica of the db schema SCOTT PROD, now you just have to test if any DDL / DML performed on the schema SCOTT @ PROD is also replicated on SCOTT @ TEST.
Good replica!
Ah, I leave some useful links to deepen the subject: *
http://www.dba-oracle.com/t_streams_schema_replication.htm
http://ca.geocities.com/mosicr * @ rogers.com / * OracleStreams101.htm
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_strm_a.htm # ARPLS305
0 comments:
Post a Comment