1 / 38

Streams

Streams. Julian Dyke Independent Consultant. Web Version. juliandyke.com. Streams. Online Redo Log. Streams Multi-Database Configuration. Source Database. Target Database. Propagate LCRs. Streams Queue. Database Objects. Streams Queue. Database Objects. Record Changes. Enqueue LCRs.

nguyet
Download Presentation

Streams

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Streams Julian Dyke Independent Consultant Web Version juliandyke.com

  2. Streams

  3. OnlineRedoLog StreamsMulti-Database Configuration Source Database Target Database PropagateLCRs Streams Queue Database Objects Streams Queue Database Objects Record Changes EnqueueLCRs DequeueLCRs ApplyChanges LGWR CaptureProcess ApplyProcess LogChanges CaptureChanges

  4. OnlineRedoLog ArchivedRedoLog ArchivedRedoLog StreamsArchived Log Downstream Capture Source Database Downstream Database Streams Queue Database Objects Record Changes EnqueueLCRs LGWR ARCn CaptureProcess LogChanges Write Redo Data CaptureChanges ReadRedo Data Copy Redo Log Files

  5. OnlineRedoLog StandbyRedoLog ArchivedRedoLog StreamsReal-Time Downstream Capture Source Database Downstream Database Database Objects Streams Queue Record Changes EnqueueLCRs LGWR RFS CaptureProcess LGWR LogChanges LogChanges Write Redo Data Send Redo Data ReadRedo Data

  6. StreamsPreparation • Used two separate servers • server1 and server2 • Used DBCA to create one database on each server • SOURCE and TARGET • Archiving must be enabled • Used default value for GLOBAL_NAMES (FALSE) • Oracle recommends setting this parameter to TRUE • Installed SCOTT/TIGER schema • $ORACLE_HOME/rdbms/admin/utlsampl.sql

  7. StreamsPreparation • Modified TNSNAMES.ORA on both nodes SOURCE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SOURCE) ) ) TARGET = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TARGET) ) )

  8. StreamsConfiguration • On both source and target as SYSDBA • Create STREAMS tablespace CREATE TABLESPACE streamsDATAFILE '/u01/oradata/SOURCE/streams01.dbf'SIZE 100M; • Create STRMADMIN user CREATE USER strmadmin IDENTIFIED BY strmadminDEFAULT TABLESPACE STREAMSQUOTA UNLIMITED ON STREAMS;

  9. StreamsConfiguration • On both servers as SYSDBA grant privileges to STRMADMIN GRANT CONNECT, RESOURCE, DBA TO STRMADMIN; EXECUTE DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE( - privilege => 'ENQUEUE_ANY', - grantee => 'STRMADMIN', - admin_option => FALSE); - EXECUTE DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE( - privilege => 'DEQUEUE_ANY', - grantee => 'STRMADMIN', - admin_option => FALSE); EXECUTE DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE( - privilege => 'MANAGE_ANY', - grantee => 'STRMADMIN', - admin_option => TRUE); EXECUTE DBMS_AQADM.GRANT_TYPE_ACCESS( - user_name => 'STRMADMIN');

  10. StreamsConfiguration • Grant more privileges... EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( - privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, - grantee => 'STRMADMIN', - grant_option => TRUE); EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( - privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, - grantee => 'STRMADMIN', - grant_option => TRUE); EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( - privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, - grantee => 'STRMADMIN', - grant_option => TRUE); EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( - privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET, - grantee => 'STRMADMIN', - grant_option => TRUE);

  11. StreamsConfiguration • Grant even more privileges... EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( - privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET, - grantee => 'STRMADMIN', - grant_option => TRUE); EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( - privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET, - grantee => 'STRMADMIN', - grant_option => TRUE); EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( - privilege => DBMS_RULE_ADM.CREATE_ANY_RULE, - grantee => 'STRMADMIN', - grant_option => TRUE); EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( - privilege => DBMS_RULE_ADM.ALTER_ANY_RULE, - grantee => 'STRMADMIN', - grant_option => TRUE);

  12. StreamsConfiguration • And finally... EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( - privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE, - grantee => 'STRMADMIN', - grant_option => TRUE); EXECUTE DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( - privilege => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT, - object_name => 'SYS.STREAMS$_EVALUATION_CONTEXT', - grantee => 'STRMADMIN', - grant_option => FALSE ); EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( - privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT, - grantee => 'STRMADMIN', - grant_option => TRUE); EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

  13. StreamsConfiguration • On both servers as STRMADMIN create AQ queue table and queue EXECUTE DBMS_STREAMS_ADM.SET_UP_QUEUE( - queue_table => 'STREAMS_QUEUE', - queue_name => 'STREAMS_QUEUE', - queue_user => 'STRMADMIN'); • On source as STRMADMIN create database link to target CREATE DATABASE LINK TARGETCONNECT TO strmadmin IDENTIFIED BY strmadminUSING 'TARGET'; • On target as STRMADMIN create database link to source CREATE DATABASE LINK SOURCECONNECT TO strmadmin IDENTIFIED BY strmadminUSING 'SOURCE';

  14. StreamsConfiguration • On source as STRMADMIN build Logminer dictionary DECLARE l_scn number;BEGIN -- Build Logminer dictionary for the capture process DBMS_CAPTURE_ADM.BUILD (l_scn); -- Creates the capture based on the previous build DBMS_CAPTURE_ADM.CREATE_CAPTURE ( queue_name=>'STRMADMIN.STREAMS_QUEUE', capture_name=>'CAPTURE1', checkpoint_retention_time=>7, first_scn=>l_scn ); END;/

  15. StreamsConfiguration • On source as STRMADMIN create capture rules for SCOTT.EMP DECLARE l_global_name varchar2(255);BEGIN -- Get the global name of the database SELECT global_name INTO l_global_name FROM global_name; -- Adds the SCOTT.EMP to the streams capture rules DBMS_STREAMS_ADM.ADD_TABLE_RULES ( table_name => 'SCOTT.EMP', streams_type => 'CAPTURE', streams_name => 'CAPTURE1', queue_name => 'STRMADMIN.STREAMS_QUEUE', include_dml => TRUE, include_ddl => TRUE, source_database => l_global_name );END;/

  16. StreamsConfiguration • On source as STRMADMIN prepare table for instantiation -- Prepare tables for instantiation -- Supplemental logging must be enabled on the tables BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION ( table_name => 'SCOTT.EMP', supplemental_logging=>'keys' ); END;/ • On source as STRMADMIN archive the current online redo log ALTER SYSTEM ARCHIVE LOG CURRENT;

  17. StreamsConfiguration • On target as STRMADMIN use Data Pump to copy table SET SERVEROUTPUT ON DECLARE l_job_handle NUMBER; -- Data Pump job handle l_job_state VARCHAR2(30); -- Keeps track of job state l_scn NUMBER; -- SCNBEGIN -- Get scn from source l_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@SOURCE; --Create a user-named Data Pump job to do a "table-level" import --using a network link l_job_handle := DBMS_DATAPUMP.OPEN ( operation => 'IMPORT', job_mode => 'TABLE', remote_link => 'SOURCE', job_name => 'IMPORT_SCOTT_'||TO_CHAR (SYSDATE,'SSSSS') );

  18. StreamsConfiguration • Data Pump continued... -- Schema filter DBMS_DATAPUMP.METADATA_FILTER ( handle => l_job_handle, name => 'SCHEMA_LIST', value => '''SCOTT'' ); -- Table filter DBMS_DATAPUMP.METADATA_FILTER ( handle => l_job_handle, name => 'NAME_LIST', value => '''EMP''' );

  19. StreamsConfiguration • Data Pump continued... -- Set parameters DBMS_DATAPUMP.SET_PARAMETER ( handle => l_job_handle, name => 'FLASHBACK_SCN', value => l_scn ); DBMS_DATAPUMP.SET_PARAMETER ( handle => l_job_handle, name => 'TABLE_EXISTS_ACTION', value => 'REPLACE' );

  20. StreamsConfiguration • Data Pump continued... -- Start the Datapump job DBMS_DATAPUMP.START_JOB ( handle => l_job_handle, skip_current => 0 ); DBMS_DATAPUMP.WAIT_FOR_JOB ( handle => l_job_handle, job_state => l_job_state ); DBMS_OUTPUT.PUT_LINE ('Job completed - job state = '||l_job_state); DBMS_DATAPUMP.DETACH (handle => l_job_handle);END;/

  21. StreamsConfiguration • On target as STRMADMIN add table rules for SCOTT.EMP DECLARE l_scn number;BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES ( table_name => 'SCOTT.EMP', streams_type => 'APPLY', streams_name => 'APPLY1', queue_name => 'STRMADMIN.STREAMS_QUEUE', include_dml => TRUE, include_ddl => TRUE, source_database => 'SOURCE' ); END;/

  22. StreamsConfiguration • On target as STRMADMIN set table instantiation SCN DECLARE l_scn number;BEGIN l_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER (); -- Set the table instantiation SCN DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN ( source_object_name => 'SCOTT.EMP', source_database_name => 'SOURCE', instantiation_scn => l_scn );END;/

  23. StreamsConfiguration • On source as STRMADMIN add the SCOTT schema to the propagation rules BEGIN -- add the schema to the propagation rules DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES ( schema_name => 'SCOTT', streams_name => 'PROPAGATE1', source_queue_name => 'STRMADMIN.STREAMS_QUEUE', destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@TARGET', include_dml => TRUE, include_ddl => TRUE, queue_to_queue => TRUE, source_database => 'SOURCE' ); END;/

  24. StreamsConfiguration • On target as STRMADMIN start apply process EXECUTE DBMS_APPLY_ADM.START_APPLY ('APPLY1'); • On source as STRMADMIN start capture process EXECUTE DBMS_CAPTURE_ADM.START_CAPTURE ('CAPTURE1'); • Note - propagation is started automatically by the ADD_SCHEMA_PROPAGATION_RULES procedure

  25. LogicalChangeRecords

  26. Logical Change Records Introduction • Two types of LCR • row LCR • SYS.LCR$_ROW_RECORD • DDL LCR • SYS.LCR$_DDL_RECORD • Enqueued into an ANYDATA queue • Can be • Captured by capture process • Constructed and enqueued by user or application • Can be modified by • Rule-based transformation • Apply process

  27. Logical Change RecordsExample • This is an example of manual creation of an LCR • Example 1 - Create LCR on target server • As SYSDBA grant privileges to STRMADMIN user GRANT EXECUTE ON DBMS_STREAMS_MESSAGING TO strmadmin; • As STRMADMIN create a queue table and a queue BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE ( queue_table => 'QUEUE_TABLE2', storage_clause => NULL, queue_name => 'QUEUE2' );END;/

  28. Logical Change RecordsExample • Create apply process BEGIN DBMS_APPLY_ADM.CREATE_APPLY ( queue_name => 'QUEUE2', apply_name => 'APPLY2', apply_captured => FALSE, apply_user => 'SCOTT' );END;/

  29. Logical Change RecordsExample • Create add table rules for SCOTT.EMP BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES ( table_name => 'SCOTT.DEPT', streams_type => 'APPLY', streams_name => 'APPLY2', queue_name => 'QUEUE2', include_dml => TRUE, include_ddl => FALSE, include_tagged_lcr => FALSE, source_database => 'SOURCE', inclusion_rule => TRUE );END;/

  30. Logical Change RecordsExample • Do not disable apply process when an error is encountered BEGIN DBMS_APPLY_ADM.SET_PARAMETER ( apply_name => 'APPLY2', parameter => 'DISABLE_ON_ERROR', value => 'N' );END;/ • Start apply process EXECUTE DBMS_APPLY_ADM.START_APPLY ('APPLY2');

  31. Logical Change RecordsExample • In source database create propagation process BEGIN -- add the schema to the propagation rules DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => 'SCOTT', streams_name => 'PROPAGATE2', source_queue_name => 'STRMADMIN.QUEUE2', destination_queue_name => 'STRMADMIN.QUEUE2@TARGET', include_dml => TRUE, include_ddl => FALSE, queue_to_queue => TRUE, source_database => 'SOURCE' );END;/

  32. Logical Change RecordsInsert Example • Inserting a row (1 of 3) INSERT INTO dept (deptno,dname,loc) VALUES (50,'IT','LONDON'); DECLARE l_deptno SYS.LCR$_ROW_UNIT; l_dname SYS.LCR$_ROW_UNIT; l_loc SYS.LCR$_ROW_UNIT; l_newvals SYS.LCR$_ROW_LIST; l_row SYS.LCR$_ROW_RECORD; BEGIN l_deptno := SYS.LCR$_ROW_UNIT ( 'DEPTNO', ANYDATA.ConvertNumber (50), DBMS_LCR.NOT_A_LOB, NULL, NULL );

  33. Logical Change RecordsInsert Example • Inserting a row (2 of 3) l_dname := SYS.LCR$_ROW_UNIT ( 'DNAME', ANYDATA.ConvertVarchar2 ('IT'), DBMS_LCR.NOT_A_LOB, NULL, NULL ); l_loc := SYS.LCR$_ROW_UNIT ( 'LOC', ANYDATA.ConvertVarchar2 ('LONDON'), DBMS_LCR.NOT_A_LOB, NULL, NULL ); l_newvals := SYS.LCR$_ROW_LIST (l_deptno,l_dname,l_loc);

  34. Logical Change RecordsInsert Example • Inserting a row (3 of 3) -- Construct the LCR l_row := SYS.LCR$_ROW_RECORD.CONSTRUCT ( source_database_name => 'SOURCE', command_type => 'INSERT', object_owner => 'SCOTT', object_name => 'DEPT', old_values => NULL, new_values => l_new_vals ); DBMS_STREAMS_MESSAGING.ENQUEUE ( queue_name => 'QUEUE2', payload => ANYDATA.ConvertObject (l_row) );

  35. Logical Change RecordsUpdate Example • Updating a row (1 of 3) UPDATE dept SET loc = 'READING' WHERE deptno = 50; DECLARE l_deptno SYS.LCR$_ROW_UNIT; l_old_loc SYS.LCR$_ROW_UNIT; l_new_loc SYS.LCR$_ROW_UNIT; l_oldvals SYS.LCR$_ROW_LIST; l_newvals SYS.LCR$_ROW_LIST; l_row SYS.LCR$_ROW_RECORD; BEGIN l_deptno := SYS.LCR$_ROW_UNIT ( 'DEPTNO', ANYDATA.ConvertNumber (50), DBMS_LCR.NOT_A_LOB, NULL, NULL );

  36. Logical Change RecordsUpdate Example • Updating a row (2 of 3) l_old_loc := SYS.LCR$_ROW_UNIT ( 'LOC', ANYDATA.ConvertVarchar2 ('LONDON'), DBMS_LCR.NOT_A_LOB, NULL, NULL ); l_oldvals := SYS.LCR$_ROW_LIST (l_deptno,l_old_loc); l_new_loc := SYS.LCR$_ROW_UNIT ( 'LOC', ANYDATA.ConvertVarchar2 ('READING'), DBMS_LCR.NOT_A_LOB, NULL, NULL ); l_newvals := SYS.LCR$_ROW_LIST (l_new_loc);

  37. Logical Change RecordsUpdate Example • Updating a row (3 of 3) -- Construct the LCR l_row := SYS.LCR$_ROW_RECORD.CONSTRUCT ( source_database_name => 'SOURCE', command_type => 'UPDATE', object_owner => 'SCOTT', object_name => 'DEPT', old_values => l_old_vals, new_values => l_new_vals ); DBMS_STREAMS_MESSAGING.ENQUEUE ( queue_name => 'QUEUE2', payload => ANYDATA.ConvertObject (l_row) );

  38. Thank you for your interest info@juliandyke.com

More Related