Repgen Features
and Benefits
Repgen
Demo Available for download...
-- script name: repsetup1_prod3
-- Features: Performs the initial replication setup tasks of
-- creating the replication tablespace,
-- running catrep.sql - to add replication support to the database,
-- creating the repadmin user,
-- assigning the correct roles to the repadmin user,
-- creating the private and public database links,
-- creating the purge and push jobs.
-- This file must only be run at the master site prod3.
-- Copyright 2002 by .com Solutions Inc.
-- This output file was created by Repgen version 2.2.4 on Fri May 31
13:29:24 2002. By .com Solutions Inc. www.dotcomsolutionsinc.net
-- connect as sys user
connect / as sysdba
-- make sure base Oracle packages are OK before installing replication
@c:\v901\rdbms\admin\catalog.sql
@c:\v901\rdbms\admin\catproc.sql
-- check for invalid packages
select object_name, object_type, status, owner from dba_objects where
status='INVALID';
--set up replication tablespace as locally managed tablespace
CREATE TABLESPACE "REPLICATION" LOGGING DATAFILE 'c:\u01\prod3\replication01.dbf'
SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
-- set sys and system to use replication tablespace as default tablespace
to
-- insure that replication objects get put into the replication tablespace
when
-- catrep.sql is run
alter user sys default tablespace replication;
alter user system default tablespace replication;
-- run catrep.sql to add replication objects
@c:\v901\rdbms\admin\catrep.sql
-- It is best to run these ALTER TABLE MOVE and INDEX REBUILD commands
as soon
-- as replication is setup but before replication is actually configured
for tables
-- or replication is actually running. That is why this task is done
here during setup.
-- If these tasks need to be run later, replication should be quiesced
in order to move
-- these tables. For more info, please see Oracle Note: 1037317.6
-- These ALTER TABLE MOVE commands are compatible with Oracle 8.1.5
and higher.
connect system/s2_#!39
alter table SYSTEM.DEF$_DESTINATION move tablespace replication;
alter index SYSTEM.DEF$_DESTINATION_PRIMARY rebuild;
alter table SYSTEM.DEF$_CALLDEST move tablespace replication;
alter index SYSTEM.DEF$_CALLDEST_PRIMARY rebuild tablespace index;
alter index SYSTEM.DEF$_CALLDEST_N2 rebuild tablespace index;
alter table SYSTEM.DEF$_DEFAULTDEST move tablespace replication;
alter index DEF$_DEFAULTDEST_PRIMARY rebuild tablespace index;
alter table SYSTEM.DEF$_ERROR move tablespace replication;
alter index SYSTEM.DEF$_ERROR_PRIMARY rebuild tablespace index;
alter table SYSTEM.DEF$_ORIGIN move tablespace replication;
alter table SYSTEM.REPCAT$_REPSCHEMA move tablespace replication;
alter index SYSTEM.REPCAT$_REPSCHEMA_PRIMARY rebuild tablespace index;
connect / as sysdba
-- return the sys and system user's default tablespace back to the system
tablespace
alter user sys default tablespace system;
alter user system default tablespace system;
-- check for and recompile invalid packages
select object_name, object_type, status, owner from dba_objects where
status='INVALID';
@c:\v901\rdbms\admin\utlrp.sql
-- check for invalid objects after recompiling
select object_name, object_type, status, owner from dba_objects where
status='INVALID';
-- create repadmin account
CREATE USER repadmin IDENTIFIED BY "rp_12!#"
DEFAULT TABLESPACE REPLICATION
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT CONNECT TO repadmin WITH ADMIN OPTION;
GRANT EXP_FULL_DATABASE TO repadmin WITH ADMIN OPTION;
GRANT IMP_FULL_DATABASE TO repadmin WITH ADMIN OPTION;
GRANT RESOURCE TO repadmin WITH ADMIN OPTION;
GRANT SELECT_CATALOG_ROLE TO repadmin WITH ADMIN OPTION;
GRANT OEM_MONITOR TO repadmin WITH ADMIN OPTION;
GRANT ALL ON DEFERROR TO repadmin;
GRANT ALL ON DEFTRAN TO repadmin;
GRANT ALL ON DEFTRANDEST TO repadmin;
GRANT ALL ON DBA_2PC_PENDING TO repadmin;
GRANT ALL ON DBA_2PC_NEIGHBORS TO repadmin;
ALTER USER repadmin QUOTA UNLIMITED ON REPLICATION;
ALTER USER repadmin DEFAULT ROLE CONNECT,
EXP_FULL_DATABASE,
IMP_FULL_DATABASE,
RESOURCE,
SELECT_CATALOG_ROLE,
OEM_MONITOR;
execute dbms_defer_sys.register_propagator('repadmin');
execute dbms_repcat_admin.register_user_repgroup(username=>'repadmin',privilege_type=>'receiver',list_of_gnames=>NULL);
grant execute any procedure to repadmin;
execute dbms_repcat_admin.grant_admin_any_schema ('repadmin');
grant comment any table to repadmin;
grant lock any table to repadmin;
-- create public database links to each of the other master sites
CREATE PUBLIC DATABASE LINK prod5 USING 'prod5';
-- all tasks from here onward are performed by the repadmin user account
connect repadmin/rp_12!#
-- create private database links to each of the other master sites
CREATE DATABASE LINK prod5 CONNECT TO repadmin IDENTIFIED BY rp_12!#
USING '';
-- create the daily purge job to purge completed deferred transactions
execute dbms_defer_sys.schedule_purge (interval =>'sysdate+1/24',next_date
=> sysdate+1/24);
-- create push job(s) to send data to the other master sites - using
1 minute interval
execute dbms_defer_sys.schedule_push (destination =>'prod5', interval
=>'sysdate+1/86400', next_date =>sysdate, stop_on_error =>FALSE, parallelism
=>2);
