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

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;