Activating a Passive Replicate HA as Primary (one-way replication)

Owned by Clive Otten (Unlicensed)
Jan 20, 2020
4 min read

Description

The Article explains how to activate a passive replicate High Availability(HA) as a Primary environment.The document scope is only for ONE-WAY replication.

Steps

  1. Once data is in sync with OLTP shutdown application accessing source database. Perform a few log switches then shutdown & startup source database in restricted mode and get ready for migrating your application.

  1. After verifying SOURCE & TARGET are in sync after APPLICATION is SHUTDOWN then shutdown replication (first MINE then APPLY) once PLOG/SCN are in sync (see console)

  • Shutdown MINE & APPLY with $ ./start-console.sh shutdown all .

  • Migrate manually table with unsupported types . Drop tables that had unsupported types if they were created or partially created on target during initial setup on target. This may require dropping an recreating foreign key constraints on target & recreating (verifying) they are enabled after import (drop constraints & reimport metadata is a possibility)

  • Export and import of these tables once application is shutdown on source (VERIFY)

  1. Build dynamic SQL to get sequences from source to build the SQL to run on target database. See link https://dbvisit.atlassian.net/wiki/display/ugd8/Sequences . Build the create sequences on the target.

  1. Run post import scripts to enable/disable the triggers as they are in production that were created in step 12 on TARGET. If you reimport application schema’s metadata after application shutdown this step then should not be required.

SQL>@migrate_triggers.sql

  1. Check count of objects & compare source & target (sample SQL provided below, yours script may vary based on your needs)

Spool object_counts.txt set pagesize 40000 set linesize 140 compute sum of COUNT on report break on owner skip 1 break on report select owner,object_type,status,count(*) "COUNT" from dba_objects  where owner not in ('XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','DBVREP') group by owner,object_type,status order by owner, object_type,status; spool off;

  1. Check count of constraints, status for each type and compare source & target (sample SQL provided below, yours script may vary based on your needs)

SET PAGESIZE 5000 LINESIZE 1000 column owner format A40 select owner,constraint_type,status,count(*) from dba_constraints  where owner not in ('XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','DBVREP')  group by owner,constraint_type,status  order by owner, constraint_type,status;

 

 

  1. Check counts of triggers, status and compare source & target (sample SQL provided below, yours script may vary based on your needs). Remember all triggers were disabled originally on target so this is very important as some may need to be ENABLED on target now.

SET PAGESIZE 5000 LINESIZE 1000 column owner format A30 column trigger_name format A30 select owner,trigger_name, trigger_type, status,count(*) from dba_triggers where owner not in ('XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','DBVREP') group by owner,trigger_name,trigger_type,status order by owner, trigger_name, trigger_type,status;

  1. Check table row counts on source and then on target for each schema logged in as the schema owner using a script of your choosing. Here is one to use as a guide.

  1. Remember if you have any materialized jobs, dbms_jobs or Advance queues on your former source to recreate them on your target before going live. 

  1. When all confirmed & ready to commit to TARGET becoming your new SOURCE, modify point your application to the tnsnames.ora that points to TARGET server/database.

  1. Run some queries & try read only actions from your application, ie reports

  1. Perform whatever other activities testing application you need to make a go/no-go decision.

 

ADDENDUM 1

COPY/PASTE to all.sql on SOURCE server to run on SOURCE server

This script will create 5 dynamic SQLs to run 3 pre import and 2 to run post import

Run this script on source then scp all the *.sql files it creates to run on target those scripts when instructed in the steps of this document. If you are migrating multiple schemas you will have to run this script connected as the user you are migrating for each user..

Scripts created by this all_script.sql are

  • disable_constraints.sql

  • post_imp_constraints.sql

  • disable_triggers.sql

  • migrate_triggers.sql

  • drop_sequences.sql

 

Copy code below to all_script.sql on SOURCE

 ADDEDUM 2 – JUST FYI

Example METADATA export/import

On source

expdp system/<passwd> directory=DATA_PUMP_DIR dumpfile=METADATA.dmp logfile=METADATA.log SCHEMAS=<SchemaName,SchemaName,etc) CONTENT=METADATA_ONLY 

On target

impdp SYSTEM/<passwd> dumpfile= METADATA.dmp directory=DATA_PUMP_DIR logfile= METADATA.log JOB_NAME=DP_META_0022 (optionally exclude=CONTRAINTS,REF_CONSTRAINTS)