Post RMAN Refresh tasks -- Remove Replication

Post RMAN Refresh tasks -- Remove Replication
==================================================================

Issues:
-------

When use RAMN duplicate command to create a test database from a
production database, which is a replication master definition site
database, we want to drop all replication stuff in the test database.

Post-refresh task
--------------------

1. drop replicaiton packages
login as system
------ gen_drop_rep_packages.sql ---
set feedback off;
set echo off;
set pagesize 1000;
set linesize 200;
set heading off;
set echo off;
spool drop_replication_packages.sql;
select 'spool drop_replication_packages.lst;' from dual;

select 'DROP PACKAGE ' owner '.' object_name ';' from
dba_objects
where object_type ='PACKAGE' and (object_name like '%$RP' or
object_name like '%$RL') ;

select 'spool off;' from dual;
select 'exit;' from dual;
spool off;
-- exit;
---------------

select 'DROP PUBLIC SYNONYM ' object_name ';' from dba_objects
where object_type ='SYNONYM' and (object_name like '%$RP' or
object_name like '%$RL') ;

2. login as sys, run catrepr.sql ( remove replciation-replciated catalog
views)
@?/rdbms/admin/catrepr.sql

3. remove replciation related jobs

login as repadmin
select 'exec dbms_job.remove(' job ');' from user_jobs;

'EXECDBMS_JOB.REMOVE('JOB');'
---------------------------------------------------------------
exec dbms_job.remove(98);
exec dbms_job.remove(78);
exec dbms_job.remove(44);
exec dbms_job.remove(79);
exec dbms_job.remove(80);
exec dbms_job.remove(81);
exec dbms_job.remove(82);
exec dbms_job.remove(83);
exec dbms_job.remove(84);
exec dbms_job.remove(85);
exec dbms_job.remove(86);
exec dbms_job.remove(43);
exec dbms_job.remove(87);
exec dbms_job.remove(88);
exec dbms_job.remove(89);
exec dbms_job.remove(90);
exec dbms_job.remove(141);
exec dbms_job.remove(142);
exec dbms_job.remove(144);
exec dbms_job.remove(145);

4. drop database links
repadmin@GENQB> drop database link GENB.WORLD
2 /
Database link dropped.

repadmin@GENQB> @conn system/xxx

system@GENQB> drop public database link GENB.WORLD;
Database link dropped.

drop GENARCHP.WORLD public link
drop public database link GENARCHP.WORLD;

5. Remove the REPADMIN user
DROP USER repadmin CASCADE;

Note: Before re-create replication, maybe need to execulte following:
EXECUTE Dbms_Defer_Sys.Unregister_Propagator(username=>'REPADMIN');
EXECUTE Dbms_Repcat_Admin.Revoke_Admin_Any_Schema(username=>'REPADMIN');

6. change password for all users

7. fix global_name
alter database rename global_name to genqb.world

8. remove statspack job too
conn perfstat/xxx
execute dbms_job.remove(119);

9. Add tempfile if not done so

10. Drop gen_rep_data tablespace:

rm the following file after drop the tablespace
system@GENQB> select file_name from dba_data_files where tablespace_name
='GEN_REP_DATA';

FILE_NAME
------------------------------------------------------------------------
----------------------------
/ora02/oradata/GENXQB/gen_rep_data_05.dbf
/ora01/oradata/GENQB/gen_rep_data_01.dbf
/ora02/oradata/GENQB/gen_rep_data_02.dbf
/ora01/oradata/GENQB/gen_rep_data_03.dbf
/ora02/oradata/GENQB/gen_rep_data_04.dbf