Solved

oracle streams

Posted on 2013-06-24
4
597 Views
Last Modified: 2013-07-08
All there were created

what is next to created oracle streams

BEGIN
  DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee          => 'strmadmin',    
    grant_privileges => FALSE,
    file_name        => 'grant_strms_privs.sql',
    directory_name   => 'admin_dir');
END;
/
-- rule privileges section
grant execute on dbms_rule_adm to "STRMADMIN"
/
BEGIN
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.CREATE_EVALUATION_CONTEXT_OBJ,
    grantee => '"STRMADMIN"',
    grant_option => true);
END;
/
BEGIN
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.CREATE_RULE_SET_OBJ,
    grantee => '"STRMADMIN"',
    grant_option => true);
END;
/
BEGIN
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.CREATE_RULE_OBJ,
    grantee => '"STRMADMIN"',
    grant_option => true);
END;
/
BEGIN
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.CREATE_ANY_RULE_SET,
    grantee => '"STRMADMIN"',
    grant_option => true);
END;
/
BEGIN
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.ALTER_ANY_RULE_SET,
    grantee => '"STRMADMIN"',
    grant_option => true);
END;
/
BEGIN
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.EXECUTE_ANY_RULE_SET,
    grantee => '"STRMADMIN"',
    grant_option => true);
END;
/
BEGIN
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.CREATE_ANY_RULE,
    grantee => '"STRMADMIN"',
    grant_option => true);
END;
/
BEGIN
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.ALTER_ANY_RULE,
    grantee => '"STRMADMIN"',
    grant_option => true);
END;
/
BEGIN
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.EXECUTE_ANY_RULE,
    grantee => '"STRMADMIN"',
    grant_option => true);
END;
/
-- queue privileges section
grant execute on dbms_aq to "STRMADMIN"
/
grant execute on dbms_aqadm to "STRMADMIN"
/
BEGIN
  dbms_aqadm.grant_system_privilege(
    'ENQUEUE_ANY',
    '"STRMADMIN"',
    admin_option => TRUE);
END;
/
BEGIN
  dbms_aqadm.grant_system_privilege(
    'DEQUEUE_ANY',
    '"STRMADMIN"',
    admin_option => TRUE);
END;
/
BEGIN
  dbms_aqadm.grant_system_privilege(
    'MANAGE_ANY',
    '"STRMADMIN"',
    TRUE);
END;
/
grant select on dba_queue_tables to "STRMADMIN"
/
grant select on dba_queues to "STRMADMIN"
/
grant select on dba_queue_schedules to "STRMADMIN"
/
grant select on sys.v_$aq to "STRMADMIN"
/
grant select on sys.gv_$aq to "STRMADMIN"
/
grant select on sys.aq$_propagation_status to "STRMADMIN"
/
grant execute on sys.dbms_aqin to "STRMADMIN"
/
grant select on sys.aq$internet_users to "STRMADMIN"
/
grant execute on sys.dbms_transform to "STRMADMIN"
/
grant execute on sys.dbms_aqelm to "STRMADMIN"
/
grant select on dba_aq_agents to "STRMADMIN"
/
grant select on dba_aq_agent_privs to "STRMADMIN"
 
 
 
/
grant select on dba_queue_subscribers to "STRMADMIN"
/
-- streams packages section
grant execute on dbms_capture_adm to "STRMADMIN"
/
grant execute on dbms_propagation_adm to "STRMADMIN"
/
grant execute on dbms_apply_adm to "STRMADMIN"
/
grant execute on dbms_streams_adm to "STRMADMIN"
/
grant execute on dbms_streams_messaging to "STRMADMIN"
 
 
 
/
grant execute on dbms_streams_advisor_adm to "STRMADMIN"
/
grant execute on dbms_capture_switch_adm to "STRMADMIN"
/
grant execute on dbms_xstream_adm to "STRMADMIN"
/
grant execute on dbms_streams_handler_adm to "STRMADMIN"
/
-- streams views section
grant select on dba_streams_global_rules to "STRMADMIN"
/
grant select on dba_streams_schema_rules to "STRMADMIN"
/
grant select on dba_streams_table_rules to "STRMADMIN"
/
grant select on dba_streams_transform_function to "STRMADMIN"
/
grant select on dba_streams_administrator to "STRMADMIN"
/
grant select on dba_streams_message_rules to "STRMADMIN"
/
grant select on dba_streams_message_consumers to "STRMADMIN"
/
grant select on dba_apply to "STRMADMIN"
/
grant select on dba_apply_parameters to "STRMADMIN"
/
grant select on dba_apply_instantiated_objects to "STRMADMIN"
/
grant select on dba_apply_instantiated_schemas to "STRMADMIN"
/
grant select on dba_apply_instantiated_global to "STRMADMIN"
/
grant select on dba_apply_key_columns to "STRMADMIN"
/
grant select on dba_apply_conflict_columns to "STRMADMIN"
/
grant select on dba_apply_dml_handlers to "STRMADMIN"
/
grant select on dba_apply_progress to "STRMADMIN"
/
grant select on dba_apply_error to "STRMADMIN"
/
grant select on dba_apply_enqueue to "STRMADMIN"
/
grant select on dba_apply_execute to "STRMADMIN"
/
grant select on sys.gv_$streams_apply_coordinator to "STRMADMIN"
/
grant select on sys.v_$streams_apply_coordinator to "STRMADMIN"
/
grant select on sys.gv_$streams_apply_server to "STRMADMIN"
/
grant select on sys.v_$streams_apply_server to "STRMADMIN"
/
grant select on sys.gv_$streams_apply_reader to "STRMADMIN"
/
grant select on sys.v_$streams_apply_reader to "STRMADMIN"
/
grant select on dba_capture to "STRMADMIN"
/
grant select on dba_capture_parameters to "STRMADMIN"
/
grant select on dba_capture_prepared_database to "STRMADMIN"
/
grant select on dba_capture_prepared_schemas to "STRMADMIN"
/
grant select on dba_capture_prepared_tables to "STRMADMIN"
/
grant select on dba_capture_extra_attributes to "STRMADMIN"
/
grant select on dba_registered_archived_log to "STRMADMIN"
/
grant select on sys.gv_$streams_capture to "STRMADMIN"
/
grant select on sys.v_$streams_capture to "STRMADMIN"
/
grant select on dba_rule_sets to "STRMADMIN"
/
grant select on dba_rulesets to "STRMADMIN"
/
grant select on dba_rules to "STRMADMIN"
/
grant select on  dba_rule_set_rules to "STRMADMIN"
/
grant select on dba_evaluation_contexts to "STRMADMIN"
/
grant select on dba_evaluation_context_tables to "STRMADMIN"
/
grant select on dba_evaluation_context_vars to "STRMADMIN"
/
grant select on dba_queue_publishers to "STRMADMIN"
/
grant select on sys.gv_$buffered_queues to "STRMADMIN"
/
grant select on sys.v_$buffered_queues to "STRMADMIN"
/
grant select on sys.gv_$buffered_subscribers to "STRMADMIN"
/
grant select on sys.v_$buffered_subscribers to "STRMADMIN"
/
grant select on sys.gv_$buffered_publishers to "STRMADMIN"
/
grant select on sys.v_$buffered_publishers to "STRMADMIN"
/
grant select on dba_propagation to "STRMADMIN"
 
 
 
/
-- streams views section
grant select on dba_streams_unsupported to "STRMADMIN"
/
-- streams views section
grant select on dba_streams_newly_supported to "STRMADMIN"
/
grant select on dba_streams_rules to "STRMADMIN"
/
grant select on dba_apply_table_columns to "STRMADMIN"
/
grant select on dba_streams_add_column to "STRMADMIN"
/
grant select on dba_streams_delete_column to "STRMADMIN"
/
grant select on dba_streams_rename_column to "STRMADMIN"
/
grant select on dba_streams_rename_schema to "STRMADMIN"
/
grant select on dba_streams_rename_table to "STRMADMIN"
/
grant select on dba_streams_transformations to "STRMADMIN"
/
grant select on dba_apply_spill_txn to "STRMADMIN"
/
grant select on sys.gv_$streams_transaction to "STRMADMIN"
/
grant select on sys.v_$streams_transaction to "STRMADMIN"
/
grant select on dba_sync_capture to "STRMADMIN"
/
grant select on dba_sync_capture_prepared_tabs to "STRMADMIN"
/
grant select on sys.gv_$streams_message_tracking to "STRMADMIN"
/
grant select on sys.v_$streams_message_tracking to "STRMADMIN"
/
grant select on DBA_STREAMS_TP_COMPONENT to "STRMADMIN"
/
grant select on DBA_STREAMS_TP_COMPONENT_LINK to "STRMADMIN"
/
grant select on DBA_STREAMS_TP_COMPONENT_STAT to "STRMADMIN"
/
grant select on DBA_STREAMS_TP_DATABASE to "STRMADMIN"
/
grant select on DBA_STREAMS_TP_PATH_BOTTLENECK to "STRMADMIN"
/
grant select on DBA_STREAMS_TP_PATH_STAT to "STRMADMIN"
/
grant select on DBA_STREAMS_COLUMNS to "STRMADMIN"
/
grant select on "_DBA_STREAMS_COMPONENT" to "STRMADMIN"
/
grant select on "_DBA_STREAMS_COMPONENT_LINK" to "STRMADMIN"
/
grant select on "_DBA_STREAMS_COMPONENT_PROP" to "STRMADMIN"
/
grant select on "_DBA_STREAMS_COMPONENT_STAT" to "STRMADMIN"
/
grant select on "_DBA_STREAMS_COMPONENT_EVENT" to "STRMADMIN"
/
grant select on "_DBA_STREAMS_TP_COMPONENT_PROP" to "STRMADMIN"
/
grant select on dba_streams_keep_columns to "STRMADMIN"
/
grant select on dba_streams_split_merge to "STRMADMIN"
/
grant select on dba_streams_split_merge_hist to "STRMADMIN"
/
grant select on dba_comparison to "STRMADMIN"
/
grant select on dba_comparison_columns to "STRMADMIN"
/
grant select on dba_comparison_row_dif to "STRMADMIN"
/
grant select on dba_comparison_scan to "STRMADMIN"
/
grant select on dba_comparison_scan_values to "STRMADMIN"
/
grant select on dba_recoverable_script to "STRMADMIN"
/
grant select on dba_recoverable_script_blocks to "STRMADMIN"
/
grant select on dba_recoverable_script_errors to "STRMADMIN"
/
grant select on dba_recoverable_script_hist to "STRMADMIN"
/
grant select on dba_recoverable_script_params to "STRMADMIN"
/
grant select on dba_comparison_scan_summary to "STRMADMIN"
/
grant select on dba_xstream_inbound to "STRMADMIN"
/
grant select on dba_xstream_outbound to "STRMADMIN"
/
grant select on dba_xstream_inbound_progress to "STRMADMIN"
/
grant select on dba_xstream_outbound_progress to "STRMADMIN"
/
grant select on dba_xstream_rules to "STRMADMIN"
/
grant select on dba_streams_stmt_handlers to "STRMADMIN"
/
grant select on dba_streams_stmts to "STRMADMIN"
/
grant select on dba_apply_change_handlers to "STRMADMIN"
/
grant select on sys.gv_$streams_pool_statistics to "STRMADMIN"
/
grant select on sys.v_$streams_pool_statistics to "STRMADMIN"
/
-- miscellaneous privileges section
grant restricted session to "STRMADMIN"
/
grant execute on dbms_flashback to "STRMADMIN"
/
BEGIN
  dbms_streams_auth.grant_remote_admin_access('"STRMADMIN"');
END;
/
grant select_catalog_role to "STRMADMIN"
/
grant select on DBA_DB_LINKS to "STRMADMIN"
/
grant select on DBA_SCHEDULER_JOBS to "STRMADMIN"
/
0
Comment
Question by:walkerdba
  • 2
4 Comments
 
LVL 2

Expert Comment

by:guruworld
ID: 39271317
hope you set global names to true in both databases, coz that is very important. Also hope u have DB links already in place between the 2 databases.

Now what u need is creating rules, and then instantiation.. Are you propagating at table or schema level?
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 39274848
Is there any particular error you are getting or thing which is stopping for the streams configuration. You can put that. That will help in resolving your problem more easliy.
0
 
LVL 5

Accepted Solution

by:
Sanjeev Labh earned 500 total points
ID: 39275634
Ideally for streams configuration there few major activities to be undertaken depending upon the type of streams configuration you are doing. Depending upon the capture process it could be a local capture or a downstream capture.

The set of activities for both type of setups are different. Broadly the activities for streams setup would include the following.

1. Enable log shipping (in case of downstream)
2. Instantiate destination database for streams capture
3. Create streams admin in both database
4. Create various streams related objects
5. Create capture queue
6. Apply queue
7. Create capture process
8. Apply process
9. Propagation process
10. Set Apply / Capture Schema rules
11. Set Apply / Capture Negative rules
12. Start streams

These are guidelines for the streams setup which you should be taking care of. Individually each activity might have a number of dependent activity technically which you can easily do by following Oracle streams installation guide.

There are some minor differences for setup in Oracle 10g and 11g so you would also need to consider on which version you are setting up.

I have provided a link to the streams setup guide from Oracle documentation for 11g. This should help you further.

http://docs.oracle.com/cd/B28359_01/server.111/b28321/strms_mprep.htm

If you are facing any specific errors after setup then let us know we will be able to further comment on it.
0
 

Author Closing Comment

by:walkerdba
ID: 39308509
yes
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now