1 / 45

Painless Master Table Alter In Replication Paper # 524

Painless Master Table Alter In Replication Paper # 524. Arup Nanda. pr. ligence. Empowering Intelligence. Question. Two Most Important Objectives of Living Organisms Surviving Reproducing!. Replication Options Revisited. MASTER. COPY. Primary. Secondary. Multi-Master. MASTER. COPY.

jarah
Download Presentation

Painless Master Table Alter In Replication Paper # 524

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Painless Master Table Alter In ReplicationPaper # 524 Arup Nanda pr ligence Empowering Intelligence

  2. Question Two Most Important Objectives of Living Organisms Surviving Reproducing!

  3. Replication Options Revisited MASTER COPY

  4. Primary Secondary Multi-Master MASTER COPY ROW1 ROW1 ROW2 push ROW2 Trigger Trigger Snapshot Logs Snapshot Logs

  5. Multi-Master contd. Changes are PUSHED to secondary Advantages • Can be Used in Disaster Recovery • Can be SYNCHRONOUS • Secondary Copy Useful for Transactions • Bidirectional Flow of Data _

  6. Multi-Master contd. Disadvantages • Continuosly Available Link Required • Conflict Resolution Logic Needed • Can Strain the Database _

  7. Updateable Snapshot push MASTER SNAPSHOT ROW1 ROW1 ROW2 ROW2 CREATE SNAPSHOT MYSNAP FOR UPDATE AS SELECT * FROM MASTER@MAINDB Trigger Trigger Snapshot Logs Snapshot Logs pull

  8. Updateble Snapshots contd. • Changes are PULLED rather than pushed Advantages • Controlled from Secondary Side • Continuous Link Not Needed • Can be Used in Disaster Recovery _

  9. Updateable Snapshots contd. Disadvantages • Conflict Management • Difficult Setup and Administration • Strain on Resources • Cannot be SYNCHRONOUS • Reporting Requirements – Overkill _

  10. Primary Secondary Read Only Snapshot MASTER SNAPSHOT ROW1 ROW1 pull Trigger Snapshot Logs

  11. Read Only Snapshots contd. PULLED Controlled by Secondary Disadvantages Cannot be Used in Disaster Recovery Advantages • No Conflict Management • Simple Setup • Less Strain on Resources • Continuous Link Not Needed _

  12. Altering The Master Table Not captured in Refresh Process PROD REPL

  13. Documented PROD REPL X Drop the Snasphot

  14. Documented PROD REPL Recreate the Snapshot

  15. Documented Process • Drop Snapshot (Snapsite) • Add Column to Master Table (Master) • Create Snapshot (Snapsite) • Refresh Snapshot (Snapsite)

  16. Problem • Time Consuming • Rollback Segment Space Needed • ORA-1555 • Temporary Segment Space Needed • Space Needed in Target Tablespace

  17. Database Example • Schema Owner : ANANDA • Table TEST1 • COL1 CHAR(1) • COL2 CHAR(1)

  18. Prepping the Master Site Creating the Snapshot Log As user ANANDA CREATE SNAPSHOT LOG ON TEST1 TABLESPACE USER_DATA WITH PRIMARY KEY INCLUDING NEW VALUES;

  19. Create Repl Group As REPADMIN User DBMS_REPCAT. CREATE_MASTER_REPGROUP ( GNAME=>'TEST1', QUALIFIER=>'', GROUP_COMMENT=>’TEST SNAPSHOT’ ); Group Name

  20. Generate Master RepObject DBMS_REPCAT. CREATE_MASTER_REPOBJECT( GNAME=>'TEST1', TYPE=>'TABLE', ONAME=>'TEST1', SNAME=>'ANANDA' ); Group Name Table Name Schema Name

  21. Generate Repl Support DBMS_REPCAT. GENERATE_REPLICATION_SUPPORT( SNAME=>'ANANDA', ONAME=>'TEST1', TYPE=>'TABLE', MIN_COMMUNICATION=>TRUE ); Schema Name Object Name

  22. Resume Master Activity DBMS_REPCAT. RESUME_MASTER_ACTIVITY( GNAME=>'TEST1'); Group Name

  23. Make the Refresh Group At Database REPL as user MVADMIN DBMS_REFRESH.MAKE( NAME=>'MVADMIN.TEST1', NEXT_DATE=>SYSDATE+5/(24*60), INTERVAL=>'SYSDATE+5/(24*60)' ); Group Name

  24. Make the Snapshot Group At Database REPL as user MVADMIN DBMS_REPCAT. CREATE_SNAPSHOT_REPGROUP( GNAME=>'TEST1', MASTER=>'PROD', PROPAGATION_MODE=> ‘ASYNCHRONOUS’ ); Group Name Master Database

  25. Create the Snapshot At Database REPL as user ANANDA CREATE SNAPSHOT TEST1 REFRESH FAST AS SELECT * FROM TEST1@PROD

  26. Add Snapshot to Group At Database REPL as user ANANDA DBMS_REFRESH.ADD ( NAME=>'MVADMIN.TEST1', LIST=>'ANANDA.TEST1' ); Group Name Object Name

  27. Generate Repl Support At Database REPL as user MVADMIN DBMS_REPCAT. CREATE_SNAPSHOT_REPOBJECT( GNAME =>'TEST1', SNAME =>'ANANDA', ONAME =>'TEST1', TYPE =>'SNAPSHOT' ) Group Name Schema Name Object Name

  28. Most Expensive CREATE SNAPSHOT TEST1 REFRESH FAST AS SELECT * FROM TEST1@PROD

  29. Prebuilt Table PROD REPL EXPORT/IMPORT SQL*LOADER DIRECT PATH SNAPSHOT TEST1 TEST1 DIRECT PATH INSERT SNAPSHOT LOGS CREATE TABLE AS SELECT SNAPSHOT LOGS FAST REFRESH

  30. Usual Method CREATE SNAPSHOT TEST1 REFRESH FAST AS SELECT * FROM TEST1@PROD

  31. Prebuilt Table CREATE SNAPSHOT TEST1 ON PREBUILT TABLE REFRESH FAST AS SELECT * FROM TEST1@PROD

  32. Needed Changes • Add a column COL3 CHAR(1) • Modify column COL2 CHAR(1000) As user ANANDA in PROD Database ALTER TABLE TEST1 ADD (COL3 CHAR(1); ALTER TABLE TEST1 MODIFY (COL2 CHAR(1000));

  33. Snapshot on Prebuilt Table TEST1 is a TABLE TEST1 is a TABLE again! CREATE SNASPSHOT TEST1 ON PREBUILT TABLE AS …. DROP SNASPHOT TEST1 SNAPSHOT TEST1 TABLE

  34. Segment State SNAPSHOT SNAPSHOT TEST1 COL1 CHAR(1) COL2 CHAR(1) SNAPSHOT IS CREATED ON PREBUILT TABLE SNAPSHOT IS DROPPED COL3 IS ADDED TO THE TABLE SNAPSHOT IS RECREATED ON THE TABLE ROW1 ROW2 ROW3 COL3 CHAR(1)

  35. The state of the segment, i.e. the data is the same at these two points in time; only the additional column is different. Segment State Analysis • Snapshot is Dropped • Segment Reverts to Table • Column Is Added • Snapshot is Recreated • Segment Becomes Snapshot TIME Therefore, a FULL Refresh Is NOT Needed!

  36. Stop Any Refresh SELECT JOB FROM USER_REFRESH WHERE RNAME = 'TEST1'; EXEC DBMS_JOB.BROKEN (<JOBNUMBER>,TRUE); COMMIT; SELECT SID FROM DBA_JOBS_RUNNING WHERE JOB = <JOBNUMBER>;

  37. Add the Columns DROP SNAPSHOT TEST1; Snapshot dropped; Table remains ALTER TABLE TEST1 ADD (COL4 CHAR(1); ALTER TABLE TEST1 MODIFY (COL3 CHAR(1000));

  38. Potential Problem When a SNAPSHOT is Created on a Master Table, the Snashot Logs Entries are Erased Need to Capture Snapshot Log Entries MLOG$_<first 20 Chars of Table Name> Table Name:THIS_IS_A_LONG_TABLE_NAME Snap Log Name:MLOG$_THIS_IS_A_LONG_TABLE

  39. Preserve the Log As User ANANDA COL PART_TAB_NAME NOPRINT NEW_VALUE PART_TAB_VAL SELECT SUBSTR(‘&TABNAME',1,20) PART_TAB_NAME FROM DUAL / DROP TABLE MLOG_BAK / CREATE TABLE MLOG_BAK AS SELECT * FROM MLOG$_&&PART_TAB_VAL.@PROD / Table to Preserve Snapshot Log

  40. Recreate the Snapshot • Build the Snapshot • Add the Snapshot to the Refresh Group • Build Replication Support for the Group

  41. Reinstate the Log Entries INSERT INTO MLOG$_&&PART_TAB_VAL.@PROD SELECT * FROM MLOG_BAK / COMMIT / Snapshot Log Table Table We Preserved the Snapshot Logs In

  42. Test the Refresh • Do a Fast Refresh execute dbms_snapshot.refresh('TEST1','F') • Re-run the Job execute dbms_job.run(<jobnumber>)

  43. Dare to Compare? 16 minutes 10 hours 6 minutes

  44. Conclusion • Elapsed Time Reduced As Much As 99% • Resource Utilization Low • All Processes Supported by Oracle • No Data Dictionary Manipluation, No Underscore Parameters

  45. Thank You!Painless Master Table Alter In ReplicationPaper # 524by Arup Nanda www.proligence.com

More Related