Oracle 8i Advance Replication

1. Oracle 8i Advance Replication

2. Discussion point • Multi master architecture • Replication Jobs and parallel propagation • Setting up Master to Master Replication • Tips and Tricks (multi-master and snapshot)

3. Multi-master architecture Database A Database B Error Queue Error Queue Internal Procedure Internal Procedure DML Delete Scott.EMP Where (2 rows deleted); Scott.EMP Scott.EMP Internal Trigger Internal Trigger Deferred Queue Deferred Queue Background process Background process

4. New in Oracle 8. Changed and Old values are stored in raw argument. REP_INSERT • new column values ( _n) • site_name (varchar2) • propagation_flag (char) REP_DELETE • column_changed$ (raw) • old column values ( _o) • site_name (varchar2) • propagation_flag (char) The _o arguments are backward compatible with v7 version. All old values are send to destination for conflict detection. REP_UPDATE • column_changed$ (raw) • old column values ( _o) • new column values ( _n) • site_name (varchar2) • propagation_flag (char) V8 has the option to sent only changed values on new arg. V7 will sent all column values. Replication Package (internalized) Scott.EMP$$RP Arguments for $RP are stored in defcall by replication trigger (repcatlogtrig). Propagator (repadmin) execute RPC on remote as receiver. Initiated automatically (SNP), manually, and or in parallel (as slave proc).

5. Transaction Propagation • Rep_insert: • new values for all columns. • Use PK for conflict detection. • new value for conflict resolution (Column Group) • Site name • Propagation flag Houston London • Rep_delete: • a raw argument for v8 • all old values. • Use PK for conflict detection • No conflict resolution method • Site name • Propagation flag • Rep_update: • a raw argument for v8 • all old values for conflict detection • all new values (v7), changed values (v8) using min_communication • new values for conflict resolution (CG) • v8, some old values (send_old_values)

6. 3 Possible outcome of Deferred Transaction • Execution @ destination success and outcome success.(deftran @ source is cleared) • Execution @ destination success but outcome fail. (deftran @ source is cleared, error is logged @ destination). • Execution @ destination fail. (deftran @ source is kept to be retried again later).

7. DBA_REPCATLOG DBA_REPCATLOG Deftran, Deftrandest Defcall Deftran, Deftrandest Defcall Deftran, Deftrandest Defcall Deftran, Deftrandest Defcall • Perform local deferred administrative task queued in the DBA_REPCATLOG. • Created when create Rep Group or Add master site to a Rep Group • One job per Rep Group. • DBA_REPCATLOG also serves as error log. • Single Job on Oracle 7, that push and purge entries on deftran. • One execute job on per destination site. • Parallel Features on Oracle 8. Push job does not clean deftran, only deftrandest. • One push job per destination (db_link) and one purge job per DB. • Perform data propagation queued on deftran, and log error on deferror. • Can use Parallel slave process for parallel propagation. Replication Jobs DB 1 DB 2 • Do_deferred_repcat_admin • Schedule_execution • Schedule_push • Schedule_purge DB LINKS

8. Parallel propagation Serial propagation Parallel propagation SNP/User process is coordinator Run by Parallel query processes. Run by SNP / User process Deftran queue Deftran queue Long transaction Deftran ID 1 Deftran ID 1 Deftran ID 2 Deftran ID 2 Deftran ID 3 Deftran ID 3 • Sequence is maintained, but not in order. • Trans. dependency tracking by def$_aqcall (dscn,cscn) Sequence is maintained, and in order

9. dbms_repcat.create_master_repgroup • Will create do_deffered_repcat_admin job. • dbms_repcat.create_master_repobject • dbms_repcat.make_column_group (optional) • dbms_repcat.add_update_resolution (optional) • dbms_repcat.genereate_replication_support • The new RG is in quiesced mode. • Houston will be Master Def for the RG Create Public with only ‘using’ Clause and private link with only ‘connect to -- identified’ clause on BOTH site. • Create user REPADMIN • dbms_repcat.grant_admin_any_schema • dbms_defer_sys.register_propagator • dbms_defer_sys.schedule_push • (destination => dblink) • dbms_defer_sys.schedule_purge • dbms_repcat.add_master_database ( gname, master => dblink) • Will create do_deferred_repcat_admin on remote master site. • Master sites will be in dba_repsites Setting up Multi Master Replication London Houston 1. Repadmin 1. Repadmin 3.Push and Purge Job 3.Push and Purge Job 2. Database Link 4.Create Master RG 5.Add Master DB

10. Tips & Tricks • To simulate synchronous replication use ‘delay_seconds’ parameter. Max up to 5500 seconds. • Improve performance for replicating LOB objects, use dbms_repcat.send_old_values and dbms_repcat.compare_old_values.

11. Tips on managing Snapshots • Create index on MLOG$ on primary key columns. • MLOG$ must be created before snapshot creation. • Register snapshots to Master site. • Using refresh group and RBS size on master site. • Primary key and Rowid snapshots. (no reorg or not modifying PK)