Activating a Passive Replicate HA As Primary (One-Way Replication)

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

Rev 2, 08/17/2016

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.

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)

Build dynamic SQL to get sequences from source to build the SQL to run on target database

. Build the create sequences on the target.

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

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;

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;

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;

exit

Check table rowcounts 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

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.

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.

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

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

REM ------

REM Run on SOURCE DATABASE

REM

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

REM

REM SPOOL file name : disable_fk_constraints.sql

REM

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

exit

REM ------

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_DIRlogfile= METADATA.log JOB_NAME=DP_META_0022 (optionally exclude=CONTRAINTS,REF_CONSTRAINTS)