Oracle Streams SET_KEY_COLUMNS
When an apply process applies changes to a table, substitute key columns can either replace the primary key columns for a table that has a primary key or act as the primary key columns for a table that does not have a primary key. Set the substitute key columns for a table using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. This setting applies to all of the apply processes that apply local changes to the database.
For Example,
Source EMP table have only one primary column.but i need target table two primay column(emp_id,worker_id).
BEGIN
DBMS_APPLY_ADM.SET_KEY_COLUMNS(
object_name => 'SCOTT.EMP',
column_list => 'EMP_ID,WORKER_ID');
END;
/
Note: You must specify an unconditional supplemental log group at the source database for all of the columns specified as substitute key columns in the column_list or column_table parameter at the destination database
Showing posts with label Oracle Streams. Show all posts
Showing posts with label Oracle Streams. Show all posts
Friday, October 9, 2009
Oracle Streams Delete Column
Oracle Streams Delete Column.
Source Schame Name : SCOTT
Source Table Name : EMP
Target Schame Name : SCOTT
Target Table Name : EMP
Delete Column Names : SAL,JOB
Two Steps.
1.Rule-based transformation.
2.Rename columns in the LCR using a DML Apply Handler.
1.Rule-based transformation.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.EMP',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/
SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='EMP';
begin
DBMS_STREAMS_ADM.DELETE_COLUMN(
rule_name => 'STRMADMIN.EMP34',
table_name => 'SCOTT.EMP',
column_name => 'SAL',
operation => 'ADD');
END;
/
begin
DBMS_STREAMS_ADM.DELETE_COLUMN(
rule_name => 'STRMADMIN.EMP34',
table_name => 'SCOTT.EMP',
column_name => 'JOB',
operation => 'ADD');
END;
/
2.Rename columns in the LCR using a DML Apply Handler.
rem Create DML handler procedure
CREATE OR REPLACE PROCEDURE emp_dml_handler(in_any IN SYS.ANYDATA)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
dmlcommand VARCHAR2(10);
row_empno SYS.ANYDATA;
v_empno NUMBER;
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
dmlcommand := lcr.GET_COMMAND_TYPE();
-- Filter out required row and and columns
IF object_owner = 'SCOTT' and
object_name = 'EMP' and
dmlcommand IN ('INSERT','UPDATE','DELETE') THEN
-- Remove Columns
lcr.delete_column('SAL','*');
lcr.delete_column('JOB','*');
LCR.EXECUTE(TRUE);
END IF;
END;
/
rem Set the DML Handler for the INSERT operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the UPDATE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the DELETE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/
Source Schame Name : SCOTT
Source Table Name : EMP
Target Schame Name : SCOTT
Target Table Name : EMP
Delete Column Names : SAL,JOB
Two Steps.
1.Rule-based transformation.
2.Rename columns in the LCR using a DML Apply Handler.
1.Rule-based transformation.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.EMP',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/
SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='EMP';
begin
DBMS_STREAMS_ADM.DELETE_COLUMN(
rule_name => 'STRMADMIN.EMP34',
table_name => 'SCOTT.EMP',
column_name => 'SAL',
operation => 'ADD');
END;
/
begin
DBMS_STREAMS_ADM.DELETE_COLUMN(
rule_name => 'STRMADMIN.EMP34',
table_name => 'SCOTT.EMP',
column_name => 'JOB',
operation => 'ADD');
END;
/
2.Rename columns in the LCR using a DML Apply Handler.
rem Create DML handler procedure
CREATE OR REPLACE PROCEDURE emp_dml_handler(in_any IN SYS.ANYDATA)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
dmlcommand VARCHAR2(10);
row_empno SYS.ANYDATA;
v_empno NUMBER;
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
dmlcommand := lcr.GET_COMMAND_TYPE();
-- Filter out required row and and columns
IF object_owner = 'SCOTT' and
object_name = 'EMP' and
dmlcommand IN ('INSERT','UPDATE','DELETE') THEN
-- Remove Columns
lcr.delete_column('SAL','*');
lcr.delete_column('JOB','*');
LCR.EXECUTE(TRUE);
END IF;
END;
/
rem Set the DML Handler for the INSERT operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the UPDATE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the DELETE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/
Oracle Streams Rename Column
Oracle Streams Rename Column.
Source Schame Name : EMP
Source Table Name : MANAGER
Column Names : MANAGER_ID,MANAGER_NAME,EMPLOYEE
Target Schame Name : EMP
Target Table Name : MANAGER
Column Names : MANAGER_ID,MANAGER_NAME,WORKER
Two Steps.
1.Rule-based transformation.
2.Rename columns in the LCR using a DML Apply Handler.
1.Rule-based transformation.
Create Capture rule.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'EMP.MANAGER',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/
SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='MANAGER';
BEGIN
DBMS_STREAMS_ADM.RENAME_COLUMN(
rule_name => 'MANAGER33',
table_name => 'EMP.MANAGER',
from_column_name => 'EMPLOYEE',
to_column_name => 'WORKER',
value_type => 'NEW',
step_number => 0,
operation => 'ADD');
END;
/
2.Rename columns in the LCR using a DML Apply Handler.
CREATE OR REPLACE PROCEDURE rename_column_EMPLOYEE (in_any in sys.anydata)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(30);
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
-- Filter out required owner and table name
IF lcr.get_object_owner() = 'EMP' AND lcr.get_object_name() = 'MANAGER' THEN
IF (lcr.get_value('NEW','EMPLOYEE') is not null) THEN
lcr.rename_column('EMPLOYEE','WORKER','NEW');
END IF;
IF (lcr.get_value('OLD','EMPLOYEE') is not null) THEN
lcr.rename_column('EMPLOYEE','WORKER','OLD');
END IF;
LCR.EXECUTE(TRUE);
END IF;
END;
/
rem Set the DML Handler for the INSERT operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the UPDATE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the DELETE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/
Source Schame Name : EMP
Source Table Name : MANAGER
Column Names : MANAGER_ID,MANAGER_NAME,EMPLOYEE
Target Schame Name : EMP
Target Table Name : MANAGER
Column Names : MANAGER_ID,MANAGER_NAME,WORKER
Two Steps.
1.Rule-based transformation.
2.Rename columns in the LCR using a DML Apply Handler.
1.Rule-based transformation.
Create Capture rule.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'EMP.MANAGER',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/
SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='MANAGER';
BEGIN
DBMS_STREAMS_ADM.RENAME_COLUMN(
rule_name => 'MANAGER33',
table_name => 'EMP.MANAGER',
from_column_name => 'EMPLOYEE',
to_column_name => 'WORKER',
value_type => 'NEW',
step_number => 0,
operation => 'ADD');
END;
/
2.Rename columns in the LCR using a DML Apply Handler.
CREATE OR REPLACE PROCEDURE rename_column_EMPLOYEE (in_any in sys.anydata)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(30);
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
-- Filter out required owner and table name
IF lcr.get_object_owner() = 'EMP' AND lcr.get_object_name() = 'MANAGER' THEN
IF (lcr.get_value('NEW','EMPLOYEE') is not null) THEN
lcr.rename_column('EMPLOYEE','WORKER','NEW');
END IF;
IF (lcr.get_value('OLD','EMPLOYEE') is not null) THEN
lcr.rename_column('EMPLOYEE','WORKER','OLD');
END IF;
LCR.EXECUTE(TRUE);
END IF;
END;
/
rem Set the DML Handler for the INSERT operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the UPDATE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the DELETE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/
Oracle Streams Rename Schema
Oracle Streams Rename Schema.
Source Schame Name : EMP
Target Schame Name : WORKER
Create Capture rule.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'EMP.MANAGER',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/
SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='MANAGER';
BEGIN
DBMS_STREAMS_ADM.RENAME_SCHEMA(
rule_name => 'STRMADMIN.MANAGER35',
from_schema_name => 'EMP',
to_schema_name => 'WORKER',
step_number => 0,
operation => 'ADD');
END;
/
Source Schame Name : EMP
Target Schame Name : WORKER
Create Capture rule.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'EMP.MANAGER',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/
SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='MANAGER';
BEGIN
DBMS_STREAMS_ADM.RENAME_SCHEMA(
rule_name => 'STRMADMIN.MANAGER35',
from_schema_name => 'EMP',
to_schema_name => 'WORKER',
step_number => 0,
operation => 'ADD');
END;
/
Thursday, October 8, 2009
Oracle Streams Add New Column in Target Table
Oracle Streams Add New Column in target Table
CREATE OR REPLACE PROCEDURE test_im_amc(evt IN SYS.ANYDATA)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
BEGIN
-- Access the LCR. RC holds the return code.
rc := evt.GETOBJECT(lcr);
--add a new column called commit_scn
lcr.ADD_COLUMN('new','COMMIT_SCN',SYS.AnyData.ConvertNumber(lcr.GET_SCN()));
-- Apply row LCR
lcr.EXECUTE(true);
END;
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the UPDATE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the DELETE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/
CREATE OR REPLACE PROCEDURE test_im_amc(evt IN SYS.ANYDATA)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
BEGIN
-- Access the LCR. RC holds the return code.
rc := evt.GETOBJECT(lcr);
--add a new column called commit_scn
lcr.ADD_COLUMN('new','COMMIT_SCN',SYS.AnyData.ConvertNumber(lcr.GET_SCN()));
-- Apply row LCR
lcr.EXECUTE(true);
END;
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the UPDATE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the DELETE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/
Thursday, May 14, 2009
Oracle Streams - Strart and Stop
Oracle Streams - Strart and Stop
Start Streams.
1. Start Capture.
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STREAM1_CAPTURE');
END;
/
2. Start Progapation.
BEGIN
dbms_propagation_adm.start_propagation(
PROPAGATION_NAME=>'STREAM1_PROPOGATION');
END;
/
3. Start Apply.
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'APPLY1_STREAM');
END;
/
Stop Streams.
1. Stop Apply.
BEGIN
DBMS_APPLY_ADM.STOP_APPLY(
apply_name => 'APPLY1_STREAM');
END;
/
2. Stop Progagation
BEGIN
dbms_propagation_adm.stop_propagation(
PROPAGATION_NAME=>'STREAM1_PROPOGATION');
END;
/
3. Stop Capture.
BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'STREAM1_CAPTURE');
END;
/
Start Streams.
1. Start Capture.
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STREAM1_CAPTURE');
END;
/
2. Start Progapation.
BEGIN
dbms_propagation_adm.start_propagation(
PROPAGATION_NAME=>'STREAM1_PROPOGATION');
END;
/
3. Start Apply.
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'APPLY1_STREAM');
END;
/
Stop Streams.
1. Stop Apply.
BEGIN
DBMS_APPLY_ADM.STOP_APPLY(
apply_name => 'APPLY1_STREAM');
END;
/
2. Stop Progagation
BEGIN
dbms_propagation_adm.stop_propagation(
PROPAGATION_NAME=>'STREAM1_PROPOGATION');
END;
/
3. Stop Capture.
BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'STREAM1_CAPTURE');
END;
/
Oracle Streams Configure and Sample - Heart Beat
Oracle Streams Configure
Oracle Live(Prod) Database Name : ORCL
Oracle Downstreams Database Name : Power
Oracle Target Database Name : Blue
1.Configure Oracle Net so that the ALL database can communicate.
2.Preparing to Copy Redo Log Files for Downstream Capture
Source(Live db ORCL) initORCL.ora
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,power)'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ABLE
ALTER SYSTEM SET LOG_ARCHIVE_DEST_5='SERVICE=power ARCH OPTIONAL NOREGISTER REOPEN=60 TEMPLATE=D:\TEST\arc%s%t%r.arc DB_UNIQUE_NAME=power'
Downstream(Power) initpower.ora
alter system set log_Archive_config='DG_CONFIG=(ORCL,POWER)'
Follwing Steps in Live DB(Prod)
3. Create Sample table and upadte every 1 minute sample data.
create user ms identified by ms;
ALTER USER MS Default tablespace users Quota unlimited on users;
create table ms.stream_heartbeat (GLOBAL_NAME VARCHAR2(4000),LAST_ACTIVITY_DATE DATE) TABLESPACE USERS;
alter table ms.stream_heartbeat add primary key (global_name,last_Activity_Date);
INSERT INTO STREAM_HEARTBEAT ((SELECT GLOBAL_NAME FROM GLOBAL_NAME),SYSDATE)
4. Enable Supplemental log.
ALTER TABLE ms.stream_heartbeat ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY ) COLUMNS;
5. Create sample procedure (Streams Heart Beat)
create or replace procedure proc_stream_heartbeat_ms IS
begin
update ms.stream_heartbeat
set last_Activity_Date = sysdate;
end proc_stream_heartbeat_ms;
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'Streams_ms_Heartbeat',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN ms.proc_stream_heartbeat_ms; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
end_date => NULL,
enabled => TRUE,
comments => 'Heartbeat job for ms Schema');
End;
BEGIN
DBMS_SCHEDULER.DISABLE('Streams_ms_Heartbeat');
-- and then
DBMS_SCHEDULER.ENABLE('Streams_ms_Heartbeat');
END;
SELECT owner, job_name, enabled FROM dba_scheduler_jobs;
6. Create Streams User.
Create user strmadmin Identified by st$1$1admin ;
Create database link blue Connect to strmadmin identified by st$1$1admin Using 'blue';
7. Connet Streams User and run instantiation.
DECLARE
source_scn NUMBER;
BEGIN
source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@blue(
source_object_name => 'ms.stream_heartbeat ',
source_database_name => 'orcl',
instantiation_scn => source_scn);
END;
/
Following Step are Downstream db;
1. Create New Tablespace for Streams.
create tablespace strm datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\strm01.dbf' size 1G;
2. Create New User and grant Streams Privileges.
Create user strmadmin Identified by st$1$1admin Default tablespace strm Quota unlimited on strm;
Grant connect, resource, dba,aq_Administrator_role to strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;
3. Connect strmadmin/strm$1admin
4.Create database link
Create database link orcl Connect to strmadmin identified by st$1$1admin Using 'orcl';
Create database link blue Connect to strmadmin identified by st$1$1admin Using 'blue';
5. Now Configure Streams Stepup in Downstrams (Capture and Propagation)
6.Create Streams Queue Stepup
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
7. Create Capture Stepup
BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'streams_queue',
capture_name => 'stream1_capture',
rule_set_name => NULL,
start_scn => NULL,
source_database => 'orcl',
use_database_link => true,
first_scn => NULL,
logfile_assignment => 'implicit');
END;
/
8. Add table to Capture.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'ms.stream_heartbeat',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/
9. Create Propagation Setup.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'ms.stream_heartbeat',
streams_name => 'stream1_propogation',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@blue',
include_dml => true,
include_ddl => false,
source_database => 'orcl',
inclusion_rule => true);
END;
/
Following Steps are Target DB.
1. Create New Tablespace for Streams.
create tablespace strm datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\strm01.dbf' size 1G;
2. Create New User and grant Streams Privileges.
Create user strmadmin Identified by st$1$1admin Default tablespace strm Quota unlimited on strm;
GRANT CONNECT, RESOURCE, DBA TO strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;
3.Connect strmadmin/strm$1admin
4.Create Streams Queue Stepup (Apply)
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
5.Create database link
Create database link orcl Connect to strmadmin identified by st$1$1admin Using 'orcl';
6. Create sample table.
create table ms.stream_heartbeat as (select * from ms.stream_heartbeat@orcl);
alter table ms.stream_heartbeat add primary key (global_name,last_Activity_Date);
7. Create Apply Processing.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'msr.stream_heartbeat',
streams_type => 'apply',
streams_name => 'apply1_stream',
queue_name => 'strmadmin.streams_queue',
include_dml => TRUE,
include_ddl => FALSE,
source_database => 'orcl',
inclusion_rule => TRUE );
END;
/
Now Streams Configuraction Compelete.
1. Start Capture.
2. Start Propagation.
3. Start Apply.
Oracle Live(Prod) Database Name : ORCL
Oracle Downstreams Database Name : Power
Oracle Target Database Name : Blue
1.Configure Oracle Net so that the ALL database can communicate.
2.Preparing to Copy Redo Log Files for Downstream Capture
Source(Live db ORCL) initORCL.ora
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,power)'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ABLE
ALTER SYSTEM SET LOG_ARCHIVE_DEST_5='SERVICE=power ARCH OPTIONAL NOREGISTER REOPEN=60 TEMPLATE=D:\TEST\arc%s%t%r.arc DB_UNIQUE_NAME=power'
Downstream(Power) initpower.ora
alter system set log_Archive_config='DG_CONFIG=(ORCL,POWER)'
Follwing Steps in Live DB(Prod)
3. Create Sample table and upadte every 1 minute sample data.
create user ms identified by ms;
ALTER USER MS Default tablespace users Quota unlimited on users;
create table ms.stream_heartbeat (GLOBAL_NAME VARCHAR2(4000),LAST_ACTIVITY_DATE DATE) TABLESPACE USERS;
alter table ms.stream_heartbeat add primary key (global_name,last_Activity_Date);
INSERT INTO STREAM_HEARTBEAT ((SELECT GLOBAL_NAME FROM GLOBAL_NAME),SYSDATE)
4. Enable Supplemental log.
ALTER TABLE ms.stream_heartbeat ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY ) COLUMNS;
5. Create sample procedure (Streams Heart Beat)
create or replace procedure proc_stream_heartbeat_ms IS
begin
update ms.stream_heartbeat
set last_Activity_Date = sysdate;
end proc_stream_heartbeat_ms;
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'Streams_ms_Heartbeat',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN ms.proc_stream_heartbeat_ms; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
end_date => NULL,
enabled => TRUE,
comments => 'Heartbeat job for ms Schema');
End;
BEGIN
DBMS_SCHEDULER.DISABLE('Streams_ms_Heartbeat');
-- and then
DBMS_SCHEDULER.ENABLE('Streams_ms_Heartbeat');
END;
SELECT owner, job_name, enabled FROM dba_scheduler_jobs;
6. Create Streams User.
Create user strmadmin Identified by st$1$1admin ;
Create database link blue Connect to strmadmin identified by st$1$1admin Using 'blue';
7. Connet Streams User and run instantiation.
DECLARE
source_scn NUMBER;
BEGIN
source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@blue(
source_object_name => 'ms.stream_heartbeat ',
source_database_name => 'orcl',
instantiation_scn => source_scn);
END;
/
Following Step are Downstream db;
1. Create New Tablespace for Streams.
create tablespace strm datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\strm01.dbf' size 1G;
2. Create New User and grant Streams Privileges.
Create user strmadmin Identified by st$1$1admin Default tablespace strm Quota unlimited on strm;
Grant connect, resource, dba,aq_Administrator_role to strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;
3. Connect strmadmin/strm$1admin
4.Create database link
Create database link orcl Connect to strmadmin identified by st$1$1admin Using 'orcl';
Create database link blue Connect to strmadmin identified by st$1$1admin Using 'blue';
5. Now Configure Streams Stepup in Downstrams (Capture and Propagation)
6.Create Streams Queue Stepup
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
7. Create Capture Stepup
BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'streams_queue',
capture_name => 'stream1_capture',
rule_set_name => NULL,
start_scn => NULL,
source_database => 'orcl',
use_database_link => true,
first_scn => NULL,
logfile_assignment => 'implicit');
END;
/
8. Add table to Capture.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'ms.stream_heartbeat',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/
9. Create Propagation Setup.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'ms.stream_heartbeat',
streams_name => 'stream1_propogation',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@blue',
include_dml => true,
include_ddl => false,
source_database => 'orcl',
inclusion_rule => true);
END;
/
Following Steps are Target DB.
1. Create New Tablespace for Streams.
create tablespace strm datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\strm01.dbf' size 1G;
2. Create New User and grant Streams Privileges.
Create user strmadmin Identified by st$1$1admin Default tablespace strm Quota unlimited on strm;
GRANT CONNECT, RESOURCE, DBA TO strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;
3.Connect strmadmin/strm$1admin
4.Create Streams Queue Stepup (Apply)
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
5.Create database link
Create database link orcl Connect to strmadmin identified by st$1$1admin Using 'orcl';
6. Create sample table.
create table ms.stream_heartbeat as (select * from ms.stream_heartbeat@orcl);
alter table ms.stream_heartbeat add primary key (global_name,last_Activity_Date);
7. Create Apply Processing.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'msr.stream_heartbeat',
streams_type => 'apply',
streams_name => 'apply1_stream',
queue_name => 'strmadmin.streams_queue',
include_dml => TRUE,
include_ddl => FALSE,
source_database => 'orcl',
inclusion_rule => TRUE );
END;
/
Now Streams Configuraction Compelete.
1. Start Capture.
2. Start Propagation.
3. Start Apply.
Wednesday, October 15, 2008
Oracle Streams Overview
Oracle Streams Overview
Oracle Streams, a built-in feature of the Oracle database, is a critical data replication and integration feature. It provides a flexible infrastructure that meets a wide variety of information sharing needs. Oracle Streams enables the propagation of data, transactions and events in a data stream either within a database, or from one database to another. The flexibility of Streams over traditional solutions for data replication and message queuing, allows customers to select a single information sharing solution, and to deploy information solutions in less time and for less cost. As business requirements change, simply implement a new capability of Oracle Streams, without sacrificing existing capabilities.
