Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Unanswered: replication help please

    I need to replicate approximately 20 tables from one database to another. The problem is that I have read only access to the master (national) database. I would like to copy/replicate a number of tables to a local database so my end users have faster access and I can customize queries. The tables range in size from 10Gb and millions (only a few tables of this size) of records to small tables of a couple hundred records each. The master database is Oracle 9i, the local database could be either Oracle 10g, or MySQL (doesn’t matter to me as the local copy only needs to service some reports/web based queries).

    I have tried a couple different approaches but none solve my problem. I wrote a perl script (run from a cron job) to insert (on duplicate update) a local copy of the tables. This works fine for the smaller tables but it takes six days to work on the 10Gb table and over 4 hours to work on a 1.7 million record table. I can tolerate some lag (a couple hours perhaps) but not six days. Oh ya, I am authorized only one session so I cannot run multiple scripts simultaneously.

    I have tried to use Oracle Advanced Replication but I do not have adequate privilege on the master database to replicate.

    There are several utilities that will migrate an entire database but I've found nothing that will keep two synchronized.

    So, does anyone have any idea as to how this can be accomplished either through a script or utility or magic?

    Thanks for your time.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your task is fairly simple. setup a materilized view on your local machine and have it refreshed with any changes from the master table on a regular schedule. The initial load will take a while, but the sync should be very fast. By using an MVIEW_LOG on the master database, the refresh will automatically know what changes to send.

    (From the manual)
    Materialized views are schema objects that can be used to summarize, compute, replicate, and distribute data. They are suitable in various computing environments such as data warehousing, decision support, and distributed or mobile computing:

    * In data warehouses, materialized views are used to compute and store aggregated data such as sums and averages. Materialized views in these environments are typically referred to as summaries because they store summarized data. They can also be used to compute joins with or without aggregations. If compatibility is set to Oracle9i or higher, then materialized views can be used for queries that include filter selections.

    Cost-based optimization can use materialized views to improve query performance by automatically recognizing when a materialized view can and should be used to satisfy a request. The optimizer transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables or views.
    * In distributed environments, materialized views are used to replicate data at distributed sites and synchronize updates done at several sites with conflict resolution methods. The materialized views as replicas provide local access to data that otherwise has to be accessed from remote sites.
    * In mobile computing environments, materialized views are used to download a subset of data from central servers to mobile clients, with periodic refreshes from the central servers and propagation of updates by clients back to the central servers.

    Materialized views are similar to indexes in several ways:

    * They consume storage space.
    * They must be refreshed when the data in their master tables changes.
    * They improve the performance of SQL execution when they are used for query rewrites.
    * Their existence is transparent to SQL applications and users.

    Unlike indexes, materialized views can be accessed directly using a SELECT statement. Depending on the types of refresh that are required, they can also be accessed directly in an INSERT, UPDATE, or DELETE statement.

    A materialized view can be partitioned. You can define a materialized view on a partitioned table and one or more indexes on the materialized view.
    Last edited by beilstwh; 02-10-05 at 14:46.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Feb 2005
    Posts
    2
    Thank you for the prompt response. I have been working with materialized views as you suggested, but it still fails due to my limited privileges. I have user read only rights and when I try to add a database in OEM Console the error message says "This application requires more database privileges than you have currently been granted."

    Any thoughts?

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by siberjunk
    Thank you for the prompt response. I have been working with materialized views as you suggested, but it still fails due to my limited privileges. I have user read only rights and when I try to add a database in OEM Console the error message says "This application requires more database privileges than you have currently been granted."

    Any thoughts?
    Sorry, the materilized view must be setup by the dba on the master database through the db link. You don't have the rights.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •