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;

July 11, 2009

Setup Oracle Replication (Part 2: MV Site)

Filed under: Oracle — Tags: , — vicker313 @ 10:42 am

Now continue to setup the Materialized View site (MV site). Please refer here to setup master site.

BEWARE AGAIN: Master Site Server needs to be stay static (no data changes) during the whole setup process, from Master Site Setup to MV Site Setup.

  1. Create materialized view administrator (Connect as SYSTEM).
    CREATE USER mviewadmin IDENTIFIED BY mviewadmin;
    EXECUTE DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ('mviewadmin');
    GRANT COMMENT ANY TABLE TO mviewadmin;
    GRANT LOCK ANY TABLE TO mviewadmin;
    GRANT SELECT ANY DICTIONARY TO mviewadmin;
  2. Create the propagator (SYSTEM).
    CREATE USER propagator IDENTIFIED BY propagator;
    EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR ('propagator');
  3. Create the refresher, responsible for “pulling” changes made to the replicated tables at the target master site to the materialized view site (SYSTEM).
    CREATE USER refresher IDENTIFIED BY refresher;
    GRANT CREATE SESSION TO refresher;
    GRANT ALTER ANY MATERIALIZED VIEW TO refresher;
  4. Create database links to the master site (SYSTEM).
    CREATE [PUBLIC] DATABASE LINK [master site db name] USING '[net_service_name]';
  5. Create the materialized view administrator database link (MVIEWADMIN).
    CREATE DATABASE LINK [master site db name]
    CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;
  6. Create the propagator/receiver database link (PROPAGATOR).
    CREATE DATABASE LINK [master site db name]
    CONNECT TO repadmin IDENTIFIED BY repadmin;
  7. Create necessary schema at the materialized view site (SYSTEM).
    CREATE USER [schema] IDENTIFIED BY [password];
    GRANT
    CREATE SESSION,
    CREATE TABLE,
    CREATE PROCEDURE,
    CREATE SEQUENCE,
    CREATE TRIGGER,
    CREATE VIEW,
    CREATE SYNONYM,
    ALTER SESSION,
    CREATE MATERIALIZED VIEW,
    ALTER ANY MATERIALIZED VIEW,
    CREATE DATABASE LINK
    TO [schema];
  8. Create database link at the materialized view site (SCHEMA).
    CREATE DATABASE LINK [master site db name] CONNECT TO [master site schema] IDENTIFIED by [password];
  9. Create necessary materialized view (SCHEMA).
    CREATE MATERIALIZED VIEW [schema].[mv name]
    REFRESH FAST WITH PRIMARY KEY AS SELECT *
    FROM [master site schema].[table name]@[master site db link];
  10. Create an empty materialized view group (MVIEWADMIN).
    BEGIN
    DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
    gname => 'mview_repg',
    master => '[master site db name]',
    propagation_mode => 'ASYNCHRONOUS');
    END;
    /
  11. Create an empty refresh group. We can refresh (or replicate) the data for a single table, but if it is more than 1 table, we can create a refresh group so that we can schedule and refresh the data in 1 go (MVIEWADMIN).
    BEGIN
    DBMS_REFRESH.MAKE (
    name => 'mview_refg',
    list => '',
    next_date => SYSDATE,
    interval => 'SYSDATE + 1/24',
    implicit_destroy => FALSE,
    rollback_seg => '',
    push_deferred_rpc => FALSE,
    refresh_after_errors => FALSE);
    END;
    /
  12. Add 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;
    /
  13. Add the materialized views to the refresh group (MVIEWADMIN).
    BEGIN
    DBMS_REFRESH.ADD(
    name => '"MVIEWADMIN"."MVIEW_REFG"',
    list => '"[SCHEMA]"."[TABLE NAME]"',
    lax => TRUE);
    END;
    /
  14. 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');

Setup Oracle Replication (Part 1: Master Site)

Filed under: Oracle — Tags: , — vicker313 @ 10:39 am

Setup Oracle Replication involves two parts: setup at master site, where the actual data is store, and the materialized view site (MV site), where the data replicated to. Materialized view is combination of view and table, which it selects data from another table, and store the selected data as its owned. This is also the major part of Oracle Replication.

BEWARE: Master Site Server needs to be stay static (no data changes) during the whole setup process, from Master Site Setup to MV Site Setup.

  1. Create replication administrator at master site and grant necessary privileges. Replication administrator is needed to be created in each server that participate the replication (Connect as SYSTEM).
    CREATE USER repadmin IDENTIFIED BY repadmin;
    EXECUTE DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ('repadmin');
    GRANT COMMENT ANY TABLE TO repadmin;
    GRANT LOCK ANY TABLE TO repadmin;
    GRANT SELECT ANY DICTIONARY TO repadmin;
  2. Register the propagator at master site, which responsible for propagating the deferred transaction queue to other master sites (SYSTEM).
    EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR('repadmin');
  3. Schedule purge at master site, in order to keep the size of the deferred transaction queue in check (Connect as REPADMIN).
    BEGIN
    DBMS_DEFER_SYS.SCHEDULE_PURGE (
    next_date => SYSDATE,
    interval => 'SYSDATE + 7',
    delay_seconds => 0);
    END;
    /
  4. Create proxy master site users at master site (SYSTEM).
    CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;
    EXECUTE DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP ('proxy_mviewadmin', 'proxy_snapadmin', NULL);
    GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;
  5. Create the proxy refresher. The proxy refresher performs tasks at the master site on behalf of the refresher at the materialized view site (SYSTEM).
    CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;
    GRANT CREATE SESSION TO proxy_refresher;
    GRANT SELECT ANY TABLE TO proxy_refresher;
  6. Create the master group (REPADMIN).
    EXECUTE DBMS_REPCAT.CREATE_MASTER_REPGROUP('master_repg');
  7. Add objects (that wanted to be replicated to MV site) into master group (REPADMIN).
    BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
    gname => '"MASTER_REPG"',
    type => '[TABLE|INDEX|VIEW|FUNCTION]',
    oname => '"[OBJECT NAME]"',
    sname => '"[SCHEMA]"',
    copy_rows => TRUE,
    use_existing_object => TRUE);
    END;
    /
  8. Generate replication support to tables that needed to be replicated to MV site (REPADMIN).
    BEGIN
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
    sname => '"[SCHEMA]"',
    oname => '"[OBJECT NAME]"',
    type => '[TABLE]',
    min_communication => TRUE,
    generate_80_compatible => FALSE);
    END;
    /
  9. Start the replication (REPADMIN).
    BEGIN
    DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname => 'master_repg');
    END;
    /
  10. Create the necessary materialized view logs, if they do not exist (REPADMIN).
    create materialized view log on [table name];

Continue to Setup MV Site.

July 1, 2009

Add header to printout in HPUX using not JetDirect

Filed under: HPUX — Tags: , — vicker313 @ 8:00 am

This post is continue from Add header to printout in HPUX using JetDirect, if you’re not using JetDirect but other print server such as DLINK, you might use method below to set your printout header:

  1. Add your queues using System Administrator Manager (SAM): sam
  2. Go to our destination: cd /usr/bin.
  3. Create a file called enbanner. This is used to set which queue to print out header and which don’t, simply 1 stand for yes and 0 stand for no, and it is space sensitive (10 character space for the queue name). Here is the sample:
    prt1      1
    prt2      0
  4. You can found an execution file called lp, rename it to lporig: mv lp lporig.
  5. create a text file called lp and put in the following code:
    #!/sbin/sh
    TEMP=/tmp/printmp
    printer=`echo $1 | /usr/bin/cut -b 3-100`
    enbanner=`cat /usr/bin/enbanner | grep $printer | cut -c11`
    if [ `expr $enbanner` != 0 ]
    then
    /usr/bin/lp2 $1 $2 $TEMP
    /usr/bin/lporig $1 $TEMP $2
    rm $TEMP
    else
    /usr/bin/lporig $1 $2
    fi
  6. create a text file called lp2 and put in the following code:
    #!/bin/sh
    fname=$2
    TEMP=$3
    printer=`echo $1 | /usr/bin/cut -b 3-100`


    do_banner()
    {
    # Print the standard header
    banner TESTING
    account=`whoami`
    banner $account
    banner `basename $fname`
    echo "\n"
    reqque=`cat /var/spool/lp/seqfile`
    reqqueadd=`expr $reqque + 1`
    reqid=$printer-$reqqueadd
    echo "Request id: $reqid Printer: `basename $printer`\n"
    date
    echo "\n"

    echo "\f\r\c"
    }

    echo "" > $TEMP
    do_banner >> $TEMP
  7. Then make lp and lp2 executable:
    chmod 777 lp
    chmod 777 lp2
  8. Now you can try to print out the header, depend on the setting at enbanner.

Create a free website or blog at WordPress.com.