Repgen Features
and Benefits
Repgen
Demo Available for download...
-- script name: repsremovsetup1_prod3
-- Features: Removes the initial replication setup tasks by
-- dropping the replication tablespace,
-- running catrepr.sql - to remove replication support from the database,
-- dropping the repadmin user,
-- dropping the private and public database links,
-- removing 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 using repadmin user account
connect repadmin/rp_12!#
-- unschedule the daily purge job to purge completed deferred transactions
execute dbms_defer_sys.unschedule_purge ();
-- unschedule push job(s) to send data to the other master sites - using
1 minute interval
execute dbms_defer_sys.unschedule_push (dblink =>'prod5');
-- drop private database links to each of the other master sites
DROP DATABASE LINK prod5;
-- connect as sys user
connect / as sysdba
-- drop public database links to each of the other master sites
DROP PUBLIC DATABASE LINK prod5;
-- drop repadmin account
execute dbms_defer_sys.unregister_propagator('repadmin');
execute dbms_repcat_admin.unregister_user_repgroup(username=>'repadmin',privilege_type=>'receiver',list_of_gnames=>NULL);
execute dbms_repcat_admin.revoke_admin_any_schema (username =>'repadmin');
DROP USER repadmin CASCADE;
-- set sys and system to use replication tablespace as default tablespace
to
-- insure that replication objects get removed properly when
-- catrepr.sql is run
alter user sys default tablespace replication;
alter user system default tablespace replication;
-- run catrepr.sql to remove replication
@c:\v901\rdbms\admin\catrepr.sql
-- drop replication tablespace
DROP TABLESPACE REPLICATION INCLUDING CONTENTS CASCADE CONSTRAINTS;
-- remove replication tablespace datafile
host del c:\u01\prod3\replication01.dbf
-- 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
-- removal of invalid objects
-- create sql code to drop invalid objects because some versions of
-- catrepr.sql leave invalid objects after running, which prevents
-- catrep.sql from running correctly the next time replication is setup
-- Warning: examine and edit the output file as needed before running
drop_invalid.sql.
-- run as SYS user
set echo off
set feedback off
set verify off
set pagesize 0
spool drop_invalid.sql
select 'drop '||object_type||' ' ||owner||'.'|| chr(34) ||object_name
|| chr(34)||';' from dba_objects where status = 'INVALID';
spool off
set echo off
set feedback on
set verify on
--@drop_invalid.sql
-- check for invalid objects after recompiling
select object_name, object_type, status, owner from dba_objects where
status='INVALID';
-- 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;
