vicker313 tech blog

July 19, 2009

Setup Oracle Replication (Part 3: Offline Export and Import)

Filed under: Oracle — Tags: , — vicker313 @ 11:44 am

When we do replication setup, the master site server might contain too much data which MV (Materialized View) site server cannot afford to create materialized view directly from master site through network. In this case we can use an offline export and import method to solve the problem.

At Master Site

  1. Refer here to setup Master Site.
  2. Create a temporary schema at the master site for the materialized views (connect as SYSTEM).
    CREATE TABLESPACE offline_mview
    DATAFILE 'data file location' SIZE 10M AUTOEXTEND ON
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

    CREATE TEMPORARY TABLESPACE offline_temp_mview
    TEMPFILE 'temp data file location' SIZE 5M AUTOEXTEND ON;

    CREATE USER temp_schema IDENTIFIED BY temp_schema;

    ALTER USER temp_schema DEFAULT TABLESPACE offline_mview
    QUOTA UNLIMITED ON offline_mview;

    ALTER USER temp_schema TEMPORARY TABLESPACE offline_temp_mview;

    GRANT
    ALTER SESSION,
    CREATE CLUSTER,
    CREATE DATABASE LINK,
    CREATE SEQUENCE,
    CREATE SESSION,
    CREATE SYNONYM,
    CREATE TABLE,
    CREATE VIEW,
    CREATE INDEXTYPE,
    CREATE OPERATOR,
    CREATE PROCEDURE,
    CREATE TRIGGER,
    CREATE TYPE,
    CREATE MATERIALIZED VIEW,
    SELECT ANY TABLE
    TO temp_schema;

  3. Create temporary materialized views at the master site in the temporary schema, and make sure database link to the master site database is included in the SELECT statements (connect as TEMP_SCHEMA).
    CREATE MATERIALIZED VIEW temp_schema.[mv name]
    REFRESH FAST WITH PRIMARY KEY AS SELECT *
    FROM [master site schema].[table name]@[master site db link];
  4. Create a directory object (SYSTEM).
    CREATE DIRECTORY DPUMP_DIR AS 'DPUMP_DIR location';
  5. Perform export using EXPDP utility (in Command Prompt or Terminal).
    expdp system/[system password]@[master site db] SCHEMAS=temp_schema DIRECTORY=DPUMP_DIR DUMPFILE=temp_schema.dmp

At MV Site:

  1. Refer here to setup MV Site.
  2. Create a directory object(SYSTEM).
    CREATE DIRECTORY DPUMP_DIR AS 'DPUMP_DIR location';
  3. Transfer the dump file exported at master site to MV site server
  4. Import the dump file, and we need to use the REMAP parameter to change the schema and tablespace name (Terminal or Command Prompt).
    impdp system/[system password]@[mv site db] DIRECTORY=DPUMP_DIR DUMPFILE=temp_schema.dmp REMAP_SCHEMA=temp_schema:[mv schema] REMAP_TABLESPACE=offline_mview:[mv data tablespace]
  5. Add the imported materialized views to the materialized view group (MVIEWADMIN).
    BEGIN
    DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
    gname => 'mview_repg',
    sname => '[schema]',
    oname => '[object name]',
    type => '[INDEX|TABLE|VIEW|FUNCTION]',
    min_communication => TRUE);
    END;
    /
  6. Add the imported materialized views to the refresh group (MVIEWADMIN).
    BEGIN
    DBMS_REFRESH.ADD(
    name => '"MVIEWADMIN"."MVIEW_REFG"',
    list => '"[SCHEMA]"."[TABLE NAME]"',
    lax => TRUE);
    END;
    /
  7. Refresh materialized views to register them at master site. This is also method to manually refresh the refresh group (MVIEWADMIN).
    EXECUTE DBMS_REFRESH.REFRESH ('mview_refg');

At Master Site:

  1. Drop the temporary schema to delete the temporary materialized views you created at the master site (SYSTEM).
    DROP USER temp_schema CASCADE;
    DROP TABLESPACE offline_mview INCLUDING CONTENTS AND DATAFILES;
    DROP TABLESPACE offline_temp_mview INCLUDING CONTENTS AND DATAFILES;
    DROP DIRECTORY DPUMP_DIR;
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: