We're here to help!

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

Follow
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.

Spool rowcounts.txt
set linesize 150
set pagesize 5000
col owner for a15
clear breaks
clear computes
break on report 

ttitle left '***** Schema table count *****' skip 2

btitle off
compute sum of Table_Count on report
break on report

select OWNER,count(1) Table_Count from dba_tables where owner not in ('XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP') group by owner order by owner;

ttitle off
btitle off
spool off 
  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


REM ---------------------------------------------------------------------------------------

REM Run on SOURCE DATABASE

REM Create script to disable referential constraints on target by gathering them from source db

REM SPOOL file name : disable_fk_constraints.sql

SET HEADING OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

spool disable_fk_constraints.sql

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type = 'R';

spool off

 

REM Get post_import constraint script so they match STATUS as source

REM

REM SPOOL file name :post_imp_fk_constraints.sql

REM

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

SET HEADING OFF

spool post_imp_fk_constraints.sql

select 'alter table '||table_name||' '||replace(status,'ABLED','ABLE')||' constraint '||constraint_name||';' from user_constraints where constraint_type = 'R';

spool off

 REM disable triggers on target

REM

REM SPOOL file name : disable_triggers.sql

REM

SET HEADING OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

spool disable_triggers.sql

select 'alter trigger '||trigger_name||' disable;' from user_triggers;

spool off

 

REM post import, enable triggers on target that are enabled on source

REM

REM SPOOL file name : migrate_triggers.sql

REM

SET HEADING OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

spool migrate_triggers.sql

select 'alter trigger '||trigger_name||' '||replace(status,'ABLED','ABLE') ||' ;'
from user_triggers;

spool off

 
REM drop sequences as they will need to be recreated at cutover.

REM For testing once replication is stopped from source, recreate them starting with last_value.

REM See   https://dbvisit.atlassian.net/wiki/display/ugd7/Sequences for advise

REM

REM SPOOL file name : drop_sequences.sql

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
SET HEADING OFF

spool drop_sequences.sql

select 'drop sequence '||sequence_name||';'
from user_sequences;
spool off
REM ---------------------------------------------------------------------------------------

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)

 

 

 

Have more questions? Submit a request

Comments