Home

Duplica User Manual - Mark Gurry & Associates

1. 46 48 23 47 4 8 2 4 Replication Shutdown Date Time for Local DB Backup 47 4 8 2 5 Error Sounds P 47 49 CONFIGURE THE TABLE VALIDATION SETTINGS 47 4 9 1 How Table 47 4 9 2 EEN 48 4 9 3 50 4 9 4 TDI PIT TE A 51 5 REPLICATION CONTROL MECHANISM csscssssccesssssssscsteoscssessactuscivesssivedessarsevesssicesenscasessdesdecsensts 53 MGA Duplica Version 3 01 CONTENTS 4 5 1 STARTING AND STOPPING THE REPLICATION CONTROL MECHANISM 53 6 USING REPLICATION MANAGER 54 1 HOW TO DEBINE YOUR SUES 54 6 2 HOW TO OPEN REPLICATION MANAGE R cccscccssssccsscscsscscucsenecscscescscussececscncscscussecacsenecscsenscsuacs 55 03 THE REPLICATION MANAGER A
2. 30 4 5 1 TLOW tonlari he Duplica sbi 30 4 5 2 E TERCER 31 4 5 3 8 32 4 5 4 The Re create Overwrite Objects Option essen nnns nnns 33 4 5 5 The Rebuild Oracle Packages 33 4 5 6 The Just Database Monitor Views 33 4 5 7 LO OM E A A E TA A 33 4 6 CREATING EDITING AND DELETING DATABASE LINKS 35 4 6 1 Howto Create a Database Link 0 SOUTCE deesset 35 4 6 2 Howto Edita Source Database Link aes 36 4 6 3 How to Delete a Source Database 1 aaa nn 37 w ccc cO 38 4 7 1 How tostart Update 38 4 7 2 Generate DDL Scripts 39 4 7 3 OES 42 4 7 4 TIT EDIT RERO Rcs 42 4 7 5 Using the Column t0 Listas ODIO 44 d CONFIGURE THE DOPEICA SETTINGS EN 44 4 8 1 How OS DUD OS T 44 4 8 2 Changing the Duplica 5 45 4824 Starup ODLIOHS 45 0 2
3. 105 STOPPED LNE OSPR COMPANY REPOWMER 1M STOPPED TO OASP EQUIPMENT REPOWNER 1M STOPPED LINK DASP USERS REPOWNER 1M STOPPED LINK TO 5 Figure 56 Replication Manager window showing that all the menu options are available once a suite name has been highlighted Obviously the next stage in preparation for replication is to know how to create a new suite 6 3 1 Replication Manager Main Menu The Replication Manager main menu is summarised below Table 2 Suite Management Icons m 5 MANAGEMENT _ Add suites to suite window Opens a window containing a list of removed suites Choose from this list Select multiple suites if necessary Modify suite settings Can handle Opens a window where you multiple selections and if settings are to can change the settings of MODIFY be changed then new settings will be Replication interval Next applied to all selected suites run max query time row expectation max rows fetched Database links source target schemas MGA Duplica Version 3 01 USING REPLICATION MANAGER 57 i Remove suite s permanently from A confirmation box will Duplica appear DELETE Change tables of a suite Opens a window where you can add and delete tables TABLES view performance and logs of the suite Suite Schedulin m SUITE SCHEDULING 4 SCHEDULE UNSCHEDULE Schedule suite s for r
4. 82 6 6 3 AUR TIER c 83 6 6 4 EGS DUI MEC 65 sm NI TA RR UU UU ms 85 COA Suo ME ES o ae 86 IIRC ATON tirana 87 6 7 1 Why nse Manual 67 6 7 2 Start the Data Refetch 87 6 7 3 Choose a Ro EET 88 6 8 GET ROW COUNTS SUITE LEY EL ccacccsssssncssncsseansdoncwanswondsooumensearsusseousstoniaauvaendsannGenseassuercaeensacoencens 90 RING PORT 91 6 9 1 205100017 92 6 9 2 92 MGA Duplica Version 3 01 CONTENTS 5 6 9 3 P acta ie 93 E 94 7 USING REPLICATION MONITOR swsssiasessssceiscasssesiusecusssavaccuseasiereresassentetvsssaceucesauuaterseasieuacintevectaves 96 7 1 he UIA S ee ee er 96 ie TO OPEN REPLICATION MONITOR tges ccorereessevsiciccopendaccannsoecanseneesdentiie a RAAE ARA Eai 96 L3 JSBPEICATIONGJNIONEEOB tita t aa 98 7 3 1 D ITI IS T T
5. e e VOW 44 eor dn qq y 80132135 po re3eq e Kaogisodo I r S seqezedqd 023007 7 013007 e3eq UE 0 doy dO JHL PRE INSTALLATION CHECKS 21 3 Pre Installation Checks 3 1 Hardware Requirements Before installing Duplica make sure that your system meets the minimum hardware requirements below Client PC 300 MHz CPU minimum 500 MB hard disk space e 128 MB RAM Server e Any hardware platform that Oracle Supports e g HP UX Solaris Redhat Linux Windows2000 3 2 Software Requirements The following software must be installed on your PC or Server before you install Duplica 3 2 1 Operating System Duplica is designed to run on a minimum of either e Windows NT 4 0 2000 XP Pro or Windows 95 98 XP Home 3 2 2 Software Client PC e Oracle SQL Net version 2 1 minimum SQL Plus 3 2 0 0 or later Oracle TCP IP Protocol Adaptor 2 2 0 0 Server MGA Duplica Version 3 01 PRE INSTALLATION CHECKS 22 e Oracle 7 3 4 or later MGA prefer 81 or 91 3 3 Source and Target Database Requirements e The target and source objects should be identical Both database are ready and operational All tar
6. x daa 55 6 3 1 Replication Manager Moin E EE AAT 56 6 3 2 a loins 58 MEE i08 0 E r E E E 99 6 4 1 How HINT RR 59 6 4 2 Howto Add OF TaD ON P 61 OZ Add Tables 10 CGU Spb Ud 62 6 4 22 How to Edit the Table Settings and Validation 67 Delete Tables ICS 70 6 4 2 4 Howto Validate the Added 5 71 6 4 3 Howto OU NENE 72 6 4 4 to Validate the 5 eene eee nnn nnn nnne nennen ener nnns 74 6 5 SCHEDULING SUITES FOR AUTOMATED REPLICATION eene 76 6 5 1 DOHEONICUSHITOS IRD ICOM ON temas niu edens 76 6 5 2 Un schedule Suites for Replication sss eene eene nennen nennen 76 6 5 3 TG IOS 76 6 5 4 HOWO TANTI 77 VEWNG TIE SUME DETAILS dul 19 6 6 1 E IENE A E E I A E 79 6 6 2 UES PU E E EEE E M 61 MEME I 61 IC
7. ANCILLARY Job Status STOPPED Logs Performance Settings Tables d s v amp ANCILLARY REQUEST ANCILLARY REQUEST ANCILLARY _REGUIREMENTS ANCILLARY REQUIREMENTS ANCILLARY SERVICE ANCILLAR Y SERVICE SERVICE TYPE AMCILLAR Y SERVICE Figure 67 Table Settings window The following window opens Figure 68 which allows you to edit the Table Settings and the Validation Settings Table Settings MGA Duplica Version 3 01 USING REPLICATION MANAGER 69 Table Settmgs Table Settings Validation Settings Suite AN Server Table Mame ANCILLARY REQUEST Replicate This T abl Truncate Before Insertin Allow Manual Full Refresh Yes Yes Replication Settings Scan Back Interval Scan Back Unit Last Changed Query Field TIME OF LAST UPDATE Action Insert Only Insert Update Download Order Deleted Record Details ves Table BNCILLARY REQUEST DEL Generate DDL Record Selection Criteria Cancel Figure 68 Table Settings Tab If you have made changes Press OK to save your changes or press CANCEL Refer to Table 5 for further information Validation Settings Edit the Validation settings then press OK Figure 69 The Reset button will change the settings back to your original settings as opposed to the default setti
8. 6 5 2 Un schedule Suites for Replication Highlight the suite s that you want to de schedule from the replication process e Click the Unschedule button or e Right mouse click gt click Un Schedule Suite The button sets the state of the suite s to stopped If the process of replication is turned on suites in the stopped state will not replicate 6 5 3 How to Add Suites There are two ways to add suites to the Replication Manager Suites List window e Click the Add button in the Suite Management Group or Right mouse click on a suite gt Add Suite Figure 78 Suite Target Schema Interval Status Source Source Backu Next Hun Last Hun F m Emm een EO ee COMPANY REPOWNER 1M STOFFE EQUIPMENT REPOWNER 1M sT PPE Modify Suite EXTERNAL USE LEIGHG 1M NEW Delete Suite USERS REPOWNER 1M STOPPE Tables Schedule Suite Un Schedule Suite Refetch Data Update Row Count Statistics Figure 78 Alternative way to add a suite Click the Add button The Select Suite s window will appear Figure 79 MGA Duplica e Version 3 01 USING REPLICATION MANAGER 77 x Select Suite s Select Suites to add to Schedule ANCILLARY USERS Edit COMPANY EQUIPMENT Create Cancel Figure 79 Add Suites window All available suites that are not currently displayed in Manager window are listed From this window you can also create a new suite or
9. ccssseccssssccscscccsscscsscecscsececsceescsceecscucsenecsenecscseussetcsenesenee 99 TABLE 22 LIVE REPLICATION ERRORS FIELD DESCRIPTIONS 99 TABLE 23 LIVE DATABASE AND GENERAL REPLICATION ERRORS FIELD 8 100 TABLE 24 SUMMARY OF THE SUITES MONITOR TAB OPTIONS eese eene enne nnne nennen nns 100 TABLE 25 AVAILABLE ICONS AND FILED DESCRIPTIONS FOR THE SCHEDULED TO RUN NEXT VIEW 101 TABLE 26 CURRENTLY RUNNING VIEW FIELD DESCRIPTIONS AND 102 TABLE 27 AVAILABLE VIEWS AND ICONS FOR THE LATEST RUN PERFORMANCE VIEW 102 TABLE 28 LATEST RUN LOG OPTIONS icsseeeeeeeeeeeen enne e e nennen nnne nn een n sse nsi s se s seen sese nene 104 TABLE 29 AUDIT VIEW DESCRIPTION Lua Pads pt a bg a 105 TABLE 30 DESCRIPTION OF THE ERRORS VIEW ccceeeeeeeeetee een eee nennen hene nnne n nen n sse sens esee innen nnns 106 TABLE 31 SUMMARY OF THE TABLESPACE CHECKS FUNCTIONALITY 107 TABLE 32 DESCRIPTION OF THE INFORMATION DISPLAYED IN THE TABLESPACE VIEW 107 TABLE 33 DESCRIPTION OF THE INFORMATION DISPLAYED IN THE OBJECTS ABOUT TO CRASH VIEW 109 TABLE 34 DESCRIPTION OF THE INFORMATION DISPLAYED IN THE TABLES TO REBUILD VIEW 110 TABLE 35 REPLICATION AUDIT LOG DES
10. 4 1 2 Installation Files The Duplica software is normally supplied by e mail from MGA The mail message should include one zip file Extract the file from the mail message and store it in a folder that is visible to all the PCs where you want to install Duplica OR Place a copy of the file on the PC where you are installing Duplica The zip file should contain a SETUP EXE file Click on the SETUP EXE file to start the InstallShield Wizard Once the setup has been run the following files should have been installed to the default directory or the directory of your choice Connects dat dbman ini e ODBLIST txt repconfig ini e duplica exe Version 3 01 MGA Duplica INSTALLATION 24 repman ini e SQL Folder containing All the SQL files required to run Duplica NOTE 1 If you already installed previous versions of Duplica then you may want to keep the Connects dat file The Connects dat file contains the username and database SID data of the recently used schema The Connects dat file is updated via the Duplica front end When upgrading to different versions of Duplica it 1s important that you do not overwrite this file otherwise information previously saved will be lost Save it to a different location Then overwrite the new file with the original Connects dat file after the upgrade NOTE 2 If you want to retain previously generated scripts when upgrading make sure you Save a copy of the S
11. AEE EENE EAE 116 12 REFERENCES 119 MGA Duplica Version 3 01 INTRODUCTION 6 List of Figures FIGURE SUMMARY DIAGRAM THAT ILLUSTRATES THE CONCEPT OF REPLICATION 15 FIGURE 2 DIAGRAM SHOWING THE RECOMMENDED DUPLICA SCHEMA SET UP ON THE TARGET DATABASE 16 FIGURE 3 DETAILED DIAGRAM ILLUSTRATING THE DUPLICA CONCEPT esee ne ener nnn 20 FIGURE 4 DUPLICA WILL PROMPT YOU TO UPGRADE THE DUPLICA REPOSITORY IF IT FINDS THAT YOU HAVE AN VERSION INS WA 24 FIGURE 5 DUPLICA LOGON WINDOW cubes sbabebsdysedusetsmdbbtqcVsedub essemus uod wed Pa ase pb Pid wsvas 25 FIGURE 6 ROLES AND PRIVILEGES OF A NEW USER CREATED IN DUPLICA CALLED REPOWNER NOTE THAT THERE ARE NO OBJECT eene nennen nene nsn rn eer ss en ene essere ses serene nens 26 FIGURE 7 DUPLICA LOGON WINDOW HIGHLIGHTING THE NEW BUTTON 21 FIGURE 8 DBA LOGON PROMPT TO CREATE NEW USER ACCOUNT FOR A DATABASE 27 FIGURE 9 HOW TO CREATE NEW USER FROM WITHIN 28 FIGURE 10 ADD NEW USER WINDOW ccccceecccesccceeecccecccecccunccceueccueceeeucsceucesuueceueceueeceseucseueessecseneeeenes 29 FIGURE 11 POP UP TO CONFIRM NEW USER HAS BEEN 29 FIGURE 12 EX
12. Source Database option To create a database link to the source click the add option Duplica requires a database link name username password and host Figure 23 The database link name can be anything you like but it is recommended that you use an intuitive name that infers a link from the target to the source such as db link to HISP The next three fields are the User Name Password and Host Name database SID of the source database MGA Duplica Version 3 01 INSTALLATION 36 Addmg Source Database Lmk Link db link to HISP User Mame SCOTT Password EE Host HISH Created men Figure 23 Fill in the Source Database Link details in the above window If the details are correct the following window will appear Figure 24 Information x d Database link has been created and tested Figure 24 Window to confirm that the database link has been created 4 6 2 How to Edit a Source Database Link If you need to edit a database link select the edit option from the menu options as shown in Figure 21 or Figure 22 Select a database link from the DB Link drop down list After you have chosen a link the current value of the User Name Password Host Name and date Created will be displayed Figure 25 If the changes are OK then a popup window will confirm this Figure 26 Editing Source Database Link DB Link Hers User Name Password Host Mame Created 19
13. Interval Last Run Error Count Expectation Rows Expected Minimum Expected Start Time End Time Limit amount of table rows to fetch per run rows for each table DBMS Job ID Mext Run Time Difference Figure 83 Suite Settings Window The information displayed about the run time parameters Replication Interval Max Query Time Row Expectation etc is the same as what 1s described in Table 6 on page 74 The suite settings view also displays statistics These are described below 1n Table 8 The number of rows in the local database in tables 1n this suite The number of errors in the last run This only includes Replication errors Start Time The time the last run number started The time the last run number ended DBMS Job ID The job id of the scheduled run to replicate this suite in your local database The time the job 18 scheduled to run D Refresh the screen Table 8 Suite Settings Statistics MGA Duplica Version 3 01 USING REPLICATION MANAGER 8 1 6 6 2 Suite Performance Details The Suite Performance details has two tabs e Suite Graph Tables 6 6 2 1 Suite Graph The suite graph illustrates a suites performance for each run It shows how many rows of data were processed replicated with respect to the total query time The Suite Graph tab is shown below Figure 84 Suite Information 45 Suite Name ANCILLARY Job Status PAUSE Suite Graph Tables E So
14. SUITE SETTINGS OPTIONS eda 74 TABLE 7 SUMMARY OF THE SUITE DETAILS OPTIONS cccccesccccseccesscccecccensceccscceecseeeeceeusceeeceeecsseuseeenscss 79 TABLE 8 SUITE SETTINGS STATISTICS ccccccsccccsscccsecccescccesecceuecseecseesccceueceeueceeecssucceeueceeeceeeecsseeceeseceueeseees 80 TABLE 9 PERFORMANCE SUITE GRAPH ICON OPTIONS 82 TABLE 10 DESCRIPTION OF THE TABLES 83 TABLE 11 RUN TIME CABLE STATISTICS 1 5 84 TABLE 12 TABLES VIEW ICON DESCRIPTIONS ccccsccccesccceseceeseccceecccescsceueceueeceueececeucseeneceeuecseeceseceeeneseueceees 84 TABLE 13 TABLES TAB PERFORMANCE GRAPH PREVIEW OPTIONS ecce eene nennen nnne nns 85 TABLE 14 SUMMARY OF THE AUDIT TAB CONTENTS 86 TABLE 15 DESCRIPTION OF THE LOGS ERROR 87 TABLE 16 REFETCH OPTIONS L OCO 88 TABLE 17 POPUP MESSAGES AFTER YOU HAVE CHOSEN REFETCH OPTION eere eene 89 TABLE 15 PRINT OPTIONS an ee ee ne re eee eu eee nn eee en eee ee 92 TABLE 19 PRINT PREVIEW CP TIONS 92 TABLE 20 DESCRIPTION FILTER OPTIONS AND SUBSEQUENT POPUP WINDOW FOR SOME OF THE OPTIONS 95 TABLE 21 ERRORS VIEW ICON DESCRIPTIONS
15. W MGA Duplica Version 3 01 GLOSSARY OF TERMS 118 Definition MGA Duplica Version 3 01 REFERENCES 119 12 References Joy D 2001 Duplica 2 3 User Manual 1 19 09 2001 Perera S 2001 Duplica 2 4 2 User Manual 1 29 11 2001 Mark G 2002 O Reilly Oracle SQL Tuning January 2002 Version 3 01 MGA Duplica
16. 09 2001 14 17 10 Started Replication for Table ANCILLAR Y_SERYVICE at 20 03 2001 14 17 10 388 ANCILLARY ANCILLARY REQUIREMENTS 20 09 2001 14 17 10 Finished Replication for T able ANCILLARY REQUIREMENTS at 20 09 2001 14 17 10 388 ANCILLARY ANCILLARY REQUIREMENTS 20709 2001 14 77 70 Started Replication for Table ANCILLARY REQUIREMENTS at 20 03 2001 14 17 10 388 ANCILLARY ANCILLARY REGLIEST 20 09 2001 14 17 10 Finished Replication for T able ANCILLARY REQUEST at 20 09 2001 14 17 10 388 ANCILLARY ANCILLARY REQUEST 20 09 2007 14 77 70 Started Replication for Table ANCILLARY REQUEST at 20 09 2001 14 77 10 388 ANCILLARY ANCILLARY REQUEST 20 09 2001 14 17 10 Finished Deletions for Table ANCILLARY REQUEST at 20 09 2001 14 17 10 388 ANCILLARY ANCILLARY REGLIEST 20 09 2001 14 17 10 Started Deletions for Table ANCILLARY REQUEST at 20 09 2001 14 17 10 388 ANCILLARY SUITE 20 09 2001 14 17 09 Starting Replication for Suite ANCILLARY Run ID 368 362 ANCILLARY SUITE 20 09 2001 14 16 15 Finished Replication for Suite ANCILLARY errors 0 warnings 0 39 ANCILLARY SERVICE TYPE 20 03 2001 14 16 15 Finished Replication For T able ANCILLARY_SERVICE_TY PE at 20 03 2001 14 16 15 397 ANCILLARY SERVICE TYPE 20 03 2001 14 16 15 Inserted 25 rows and updated D rows oof ANCILLARY ANCILLARY SERVICE TYPE 20 03 2001 14 16 15 Started Replication For Table SERVICE at 20 03 2001 14 16 15 36 ANCILLARY ANCIL
17. 107 FIGURE TIO FREE SPACE OP 5 55 3 3 108 FIGURE 111 OBJECTS ABOUT TO CRASH VIEW 108 FIGURE 112 TABLES TO REBUILD VIEW 109 FIGURE 113 REPLICATION AUDIT LOG VIEW cc cccccceeccceseccceecccenccceuecceecseenceceueccuecseuecscenesseeceueceseneeuneees 110 FIGURE 114 REPLICATION ERROR LOG 111 FIGURE 115 HELP FUNCTION OPTIONS 113 FIGURE 116 INFORMATION DISPLAYED WHEN THE ABOUT OPTION IS 2 113 FIGURE 117 USING THE MAIN MENU TO EXIT 114 MGA Duplica Version 3 01 INTRODUCTION 9 List of Tables TABLE 1 DESCRIPTION OF THE SCHEMAS USED BY DUPLICA cccccccccecccsecceccesccesececsceuscsessceusceusceeseceeceusess 17 TABLE 2 REPLICATION MANAGER FUNCTIONALITY 58 TABLE 3 SUITES LIST ICON AND MISCELLANEOUS MESSAGE DESCRIPTIONS eee eene 58 TABLE 4 SUITES LIST COLUMN DESCRIPTIONS ecce 59 TABLE 5 ADD TABLE REPLICATION SETTINGS OPTIONS cccssccceseccesecccecccescccesecceeecscencceeneceueceeeceeeneseuneees 65 TABLE
18. REPOWNER Cancel Figure 74 Suite Settings Window for options 1 amp 2 You can change any of the details in table below Table 6 Replication Interval The interval between Replications of the table in this suite Replication Unit The Unit field tells you what the interval is measured in The units of measurement are Day Hour Minutes Seconds MGA Duplica Version 3 01 USING REPLICATION MANAGER 74 The date and time of the next run Max Query Time Interval This parameter is provided for evaluating performance You Minutes can use this in conjunction with filter in the Latest Run Performance Suite Graph monitor view to filter all suites where the time took greater than the maximum query time Row Expectation Max The maximum number of rows that can be expected 1n any one Rows Expected Replication Row Expectation The minimum number of rows that can be expected 1 any one Minimum Expected Replication Max rows for each table The limit of the number of rows to fetch in a run Database Link to the source Source Backup Database Link to the source backup Target Schema Schema at the target database which will receive the replicated data Table 6 Suite Settings Options If you make a change to the suite settings click OK The following popup will appear Figure 75 Ci x 2 Save changes to ANCILLARY Figure 75 Popup to save changes made to the Suite Settings Click Yes to sav
19. SHOWING THAT ALL THE MENU OPTIONS ARE AVAILABLE ONCE A SUITE NAME HAS BEEN HIGHLIGHTED 000cceccescessecceccesseeccececcseccecenseascecacseasseceaseassatsaceasseteaveasesaseusass 56 FIGURE 57 SUITES LIST COLUMNS 58 FIGURE 58 CLICK ADD FROM THE TOOL TO CREATE NEW SUITE 60 FIGURE 59 CREATE A NEW SUITE BY RIGHT MOUSE CLICKING ON A SUITE THEN CLICK ADD SUITE 60 FIGURE O0 ADD SUITE 4 3 5 N a cu Usi tacet uua 60 FIGURE 61 REPLICATION MANAGER WINDOW HIGHLIGHTING THE NEWLY CREATED SUITES cce 61 FIGURE 62 OPENING THE SUITE INFORMATION TABLE TAB WHERE YOU CAN ADD TABLES TO A SUITE 61 FIGURE 63 SUITE INFORMATION WINDOW DEFAULTED TO THE TABLES TAB eee 62 FIGURE 64 ADD TABLES TABLE DETAILS WINDOW ccccssecccsecccscccesecceuecseuecseeuceseueceeecseeecsseneseueseeeeceees 63 FIGURE 65 SERVER TABLE VALIDATION wiisicssssasssavnscadccwasanasaendeadesedenbdsuanscatssonanedacendasissioduneddadesaenstaaeoosebisaenouns 66 FIGURE 06 LOCAL TABLE VALIDATION 67 FIGURE 67 TABLE SETTINGS WINDOW cccccceecccecccesecccsscceeeccceucccuueceuecsescseeecsseueceueceeecssneseecesecsueeeeenes 68 FIGURE O5 TABLE SETIINGS MR 69 FIGURE 69 VALI
20. Sort By Query The number of seconds it took to complete the Replication of the suite Time By default the chart is sorted by query time The suite at the top of the chart 1s the one that took the least time Sort By Rows The number of rows processed for the suite Processed Table 27 Available views and Icons for the Latest Run Performance view 7 3 3 3 1 Suite Graph tab MGA Duplica Version 3 01 USING REPLICATION MONITOR 103 The Suite Graph view sorted by Query Time is shown below Figure 104 Suite Graph Table Graph 12 Sot Query Time C Rows Processed Query Time JJ Rows Processed Rows Processed 0 1 000 2 000 3 000 4 000 5 000 5 000 7 000 8 000 9 000 10 000 11 000 12 000 13 000 14 000 15 000 16 000 i ANCILLARY 6 ANCILLARY 14 193 Query Time Seconds Figure 104 Suite Graph view sorted by Query Time To sort the suites in order of the number of rows processed click Rows Processed then click Refresh To filter the suites displayed click the Filter button and choose an option Max Query Time is the parameter specified in the suite settings for this suite You can change this by editing the suite To view the suite details of any suite click the row on the graph that represents the suite 7 3 3 3 2 Table Graph tab The Table Graph views are shown below Figure 105 and Figure 106 To sort tables in order of the number of rows processed click Rows Processed
21. User Name REPOWNER Password EERE Verity Password ERRE Tablespace Default USERS Quota 20 M Temporary TEMP Database Administrator User Name SYSTEM Password Cancel Figure 10 Add New User Window Once the information has been entered correctly the following pop up window will confirm that the new user has been created Figure 11 When you press OK the Duplica Repository Set up Wizard will start automatically refer to the next section Information D Mew user has been created Figure 11 Pop up to confirm new user has been created If the information was not entered correctly then you will get some of the following error messages Figure 12 and Figure 13 Warning ees AN You must enter a user name Figure 12 Example Error Message No 1 MGA Duplica e Version 3 01 INSTALLATION 30 AN You must enter a password Figure 13 Example Error Message No 2 You must be logged on as the DBA otherwise the following error message will appear if you try to proceed with creating a new user Figure 14 x d OR A 1017 invalid usermame passward logon denied L1 Figure 14 DBA Logon Error Message 4 5 Setting up the Duplica Repository The Duplica Repository is set up at the Target Database only You must be currently logged in as the Duplica Repository user for the target database before you proceed with se
22. add the table with the options you have selected then you need to create a new field using the Update Tables function refer to section 4 7 After you have done this then edit the table settings to add the last changed query field The Download Order is very important The user must have a clear idea of which tables are the parent tables and which are the child tables The parent tables should be downloaded before the child tables Thus the parent table Download Order number should be 1 Select the other options if they are applicable If you are replicating data for the first time make sure that the Last Changed Time is not ticked Server Table Validation Edit the Server Table Validation settings then press OK The Reset button will change the settings back to your original settings as opposed to the default settings Refer to section 4 9 for further information regarding the validation settings MGA Duplica Version 3 01 USING REPLICATION MANAGER 66 Add Tables Table Details Server T able Validation Local Table Validation Validation for Insert Update Delete Operations Last Changes Query Field settings and properties f Error C Warning v Primary Key existence and properties Error C Warning Index on Last Changed Query Field Eror Warning v Insert 7 Update trigger properties Error Warning Validation For Delete Control T able v Delete Control T able settings f
23. clicking the Start button RUNNING if the suite is currently being replicated 1 e the job has been submitted STOPPED if replication was stopped by clicking the Stop button NEW if the suite 15 new PAUSED if the replication control mechanism has been turned off all currently running jobs will become paused A suites main source of data Backup Source An entry will appear if the backup source 1s being used for replication Next Run The date and time the next run 1s scheduled This will only be updated when Job Status 1s running Last run finish The date and time of the last run This will only be updated when the Job Status 1s running Local CNT The total number of rows in all the tables belonging to a suite for the local database server CNT The total number of rows 1 all the tables belonging to a suite for the source database Table 4 Suites List column descriptions 6 4 Setup Suites 6 4 1 How to Create a New Suite The following procedure shows you how to create a new suite using the main menu Figure 58 Click MGA Duplica e Version 3 01 USING REPLICATION MANAGER 60 Replication Manager m SUITE MANAGEMENT W _ SUITE SCHEDULING MANUAL OPERATIONS _ DELETE TABLES SCHEDULE REFETCH COUNT ROWS FILTER PRINT V Active Filter All Suites Figure 58 Click add from the tool bar to create a new suite You can also create a new suite by selecting the opti
24. control the rate at which data is replicated by setting a time interval set the maximum number of rows that get downloaded per run set the maximum query time set the order in which tables within a suite are replicated to allow for parent child relationships and change the time that that a table was last updated You can start and stop replication at any time on a suite level or you can turn the entire replication control mechanism on or off The Replication Manager suites list can be filtered to show the suites you are interested in or printed for reporting purposes Replication Monitor allows the user to monitor data whilst it 1s being replicated Every time data is replicated an entry is saved to an audit log file Errors go to the error log file The audit log and error log can be viewed from the Replication Monitor These logs can be refreshed manually or automatically at intervals specified by the user The logs can be printed for reporting purposes The latest run logs can be viewed as a graph and printed if necessary You can also view the target database system resources such as tablespace size 2 4 2 The Duplica Owner Duplica requires a database user to store the data it requires to run the software This user must reside on the target database It is recommended that a new user is created on the target database to house the Duplica related data i e thus becoming the Duplica Repository Thus the Duplica repository 1s kept separate fro
25. edit a suite Refer to section 6 4 1 if you want to know more about creating a new suite or 6 4 3 to know more about editing suites Highlight the suites you want to add Click OK The selected suites will be added to the list in the Replication Manager window You can also create new suites or edit the suite settings from this window 6 5 4 How to Remove Suites Highlight the suite s in the Replication Manager Suites List window Click the Delete button in Suite Management buttons group Right mouse click on a suite Delete Suite See Figure 80 MGA Duplica Version 3 01 USING REPLICATION MANAGER 78 Suite Target Schema Interval Status SOLUCE a COMPANY REPOWNE EQUIPMENT REPOWNE Modify Suite EXTERNAL USE LEIGHG Delete Suite USERS HEFUW MHE Tables _TO_OASP Schedule Suite Un Schedule Suite Refetch Data Update Row Count Statistics Figure 80 How to remove a suite using the right mouse click shortcut After you have pressed remove you will be prompted to confirm the removal of the suite from the suites list Figure 81 x 2 Remove Ehe selected suites From Ehe scheduler Figure 81 Popup window to confirm the removal of a suite The suites will stay in the main window However the status of the suites changes to NOT SCHEDULED MGA Duplica Version 3 01 USING REPLICATION M
26. etc Stockbrokers with a 15 minute license only have access to the database that is updated every 15 minutes Therefore the system could handle the multitude of stockbrokers logging into the stock exchange databases and bidding and selling as well as large amount of data being generated from the brokers at the stock exchange This is an example of where Duplica functionality and efficiency could be applicable because data only needs to be replicated from the main stock exchange server to the other databases if it has changed ie whether the stock price has changed since the last download and at different intervals of time 2 3 3 Large Supermarket Retail Store Model The head offices that control large supermarket and retail stores rely on inventory data that is imported on a daily basis from their regional stores The employees at head office then use this data to order the supply of goods from food producers farmers fishing companies food manufacturers or clothing companies The orders are then shipped to the large warehouses and then distributed to the various retail outlets accordingly The application to Duplica is that the inventory data could be replicated from the remote stores to the head office on a regular basis instead of being imported overnight for example This would allow trends in the sales to be seen a lot earlier and therefore orders could be made much earlier 2 3 4 Maintain Back up Databases Replication may be us
27. if needed cena that use server table Select to open the dialog box below like E E Server Table Name Like Filter X Enter T able name like string include if needed 1 Cancel All with tables in Select to open the dialog box below tablespace i Tablespace Name Like Filter Enter Tablespace name like sting include if needed hn Cancel Suite Name Like Select to open the dialog box below Suite Name Like Filter Enter suite name string include if needed Cancel Schema Name Like Target Schema Name Like Filter Enter Target Schema name like string include X if nee cena Table 20 Description of Filter options and subsequent popup window for some of the options MGA Duplica Version 3 01 USING REPLICATION MONITOR 96 Using Replication Monitor 7 1 Summary Replication Monitor is used to monitor run time replication and target database resources It is basically a series of views that accurately portray the state of the database and the performance of data replication The Errors view shows errors that are related to Live Database and General Replication Errors relate to the health of the database There are four Suites Monitor views The Scheduled to run next view lists the suites that are currently scheduled to run The currently running view shows some runtime statistics on suites that are currently running The Latest Run Perfor
28. insert only option Download Y N Default Y If Y then data will replicate If N then data will not replicate Download Order Indicates to Duplica the order in which tables should be downloaded Parent tables are downloaded before child tables Last Change Time Do not tick if replicating data for the first time Therefore Duplica will grab all the data in the source table If you have replicated once this field defaults to ticked which means that only the updated or new source data will be replicated Replicate this Tick Yes or No Default Yes Table Truncate Table If you want to truncate the entire local table before Truncate Before data is replicated Thus this option effectively means that the entire table is replicated every time the table is set to replicate Default No Inserting MGA Duplica Version 3 01 USING REPLICATION MANAGER 65 Copy Allow This option uses the copy command to replicate Manual Full data to the local table Refresh Default No Deleted Record Last Delete Time This option is ticked if you want Duplica to update Details the target local database using the last delete time Table Name Delete Control Table Name at the Source Record Selection This allows further filtration of data being Criteria replicated Table 5 Add Table Replication Settings Options Choose the Last Changed Query Field from the drop down list box If there are no fields to choose from
29. interval of 1 USERS REPOWNER iM STOPPED LINK TO DAsp Hiinute Figure 61 Replication Manager window highlighting the newly created suites 6 4 2 How to Add or Delete Tables To add or delete tables from a suite you must highlight a suite from the Replication Manager window then right mouse click This opens a shortcut to some of the main menu options Click on Table as shown in Figure 62 This will open the Suite Information window shown in Figure 63 Replication Manager SUITE MANAGEMENT SUITE SCHEDULING PS DELETE COMPANY REPO WNER EQUIFMENT REPDwNER Modify Suite EXTERNAL USE LEIGHG Delete Suite USERS REPOWNER DASP Schedule Suite Un Schedule Suite Refetch Data Update Row Count Statistics Figure 62 Opening the Suite Information Table Tab where you can add tables to a suite MGA Duplica Version 3 01 USING REPLICATION MANAGER 62 5urte Information X Suite Name USERS Job Status d rz IE Ss EN v Table Name server Table Weekly Count Weekly Server Count Download Order Download Indicator server Count l PE Sort B Query Time Rows Processed Query Time Rows Processed TABLE Rows Processed Run Z 0 Query Time Seconds Figure 63 Suite Information window defaulted to the Tables Tab The suite information window displays the tables that currently makeup th
30. then click Refresh To filter the tables displayed click the Filter button v then choose an option MGA Duplica Version 3 01 USING REPLICATION MONITOR 104 Suite Graph Table Graph Sot By Query Time C Rows Processed J Query Time JJ Rows Processed Rows Processed 0 1 000 2 000 3 000 4 000 5 000 5 000 7 000 8 000 9 000 ANCILLARY SERVICE TYPE 25 ANCILLARY SERVICE 0 ANCILLARY 187 7 7 ANCILLARY SERVICE TYPE 0 ANCILLARY REQUIREMENTS 1 i ANCILLARY REQUIREMENTS 7 219 ANCILLARY _REQUEST 2 ANCILLARY _REQUEST 6 762 Query Time Seconds Figure 105 Table Graph view sorted by query time Suite Graph Graph a ij C Query Time Rows Processed Time J Rows Processed Rows Processed 0 500 1 000 1 500 2 000 2 500 3 000 3 500 4 000 4 500 5 000 5 500 5 000 5 500 7 000 7 500 8 000 8 500 9 000 ANCILLARY SERVICE TYPE 0 ANCILLARY SERVICE TYPE 25 er ANCILLARY SERVICE 0 i ANCILLARY _SER VICE 187 ANCILLARY REQUEST 4 ANCILLARY REQUEST 6 762 ANCILLARY REQUIREMENTS 1 i i ANCILLARY REQUIREMENTS 7 219 Query Time Seconds Figure 106 Table Graph view sorted by rows processed 7 3 3 4 Latest Run Logs The Latest Run Logs view has two options Table 28 wow This view displays all replication events that have taken place since last time th
31. to filter the full list of tables See Figure 37 and Figure 38 for an example of how to use the filter effectively The list of tables will be filtered much more effectively if you use both the Table Name and the Columns fields in the filter query MGA Duplica Version 3 01 INSTALLATION 43 Filter DATE UPDATED TIME LAST Figure 37 Using both the Table Name and the Columns to filter the table list See next figure for result f Update Tables ANCILLARY ANCILLARY REQUEST ANCILLARY ANCILLARY_REQGUIREMENTS TIME LAST UPDATE Figure 38 Filtered Update Tables List after above query parameters were used MGA Duplica Version 3 01 INSTALLATION 44 4 7 5 Using the Add Column to List Option When you click on the Add Column to List button the following window will display Figure 39 which allows you to enter the name of the column that you wish to append to the table Note The name of the creation modified date columns should be as generic as possible for the entire database Add Hew Column E xj Column Las T CHANGED Cancel Figure 39 Add New Column Window with example column name entered 4 8 Configure the Duplica Settings The Duplica Settings window 1s used to set e Startup options e Replication Source options e Validation options e Database shutdown times e Error sounds that you want to play whenever r
32. v Last Changed Query Field settings and properties f C Warming v Friman Key existence and properties f Error C Warming Iv Indes on Last Changed Query Field Warning Defaults Figure 47 Local Table Validation Tab 4 9 4 Tables to Apply The Tables to Apply window show a list of tables that are available to have the validation settings applied to them Figure 48 The user can select all the tables or just select at random MGA Duplica Version 3 01 INSTALLATION 52 Y ahdation ANCILLARY ANCILLARY_REQUEST ANCILLARY AMCILL amp RY REGLIREMENTS ANCILLARY SERVICE ANCILLARY ANCILLARY SERVICE 0 Figure 48 Tables to Apply Tab The Filter button allows the user to filter the list of tables in the Tables to Apply tab Figure 49 Filter Figure 49 Filter By option MGA Duplica Version 3 01 REPLICATION CONTROL MECHANISM 5 5 Replication Control Mechanism 5 1 Starting and Stopping the Replication Control Mechanism Duplica REPOWNER amp rtest File Setup Tools Help REPLICATION ME r G7 EB x g SETUP MONITOR Start Replication ees Figure 50 Start the Replication Control Mechanism The START button will start the Replication control mechanism Figure 50 If you start the control mechanism all scheduled jobs will begin replicating A
33. you wish to delete the table Figure 70 Confirm the removal of the table from the suite 6 4 2 4 How to Validate the Added Table s To validate the tables that have been added to the Suite click on the Validate icon located just above the list of tables Once a validation check 15 performed and tables are valid the following popup window will display Figure 71 Information 34 d All selected Tables valid Figure 71 Confirmation that the Table s are valid If the added tables were invalid a popup will display like the one below Figure 72 Error Validation errars Target Table Validation encountered 1 error s and 2 warnirng s Server T able Validation encountered 2 errors and 3 warning s Figure 72 Errors found in the table validation A more detailed log of the error can be viewed in the Replication Monitor Error Log refer to sections 7 3 2 and 7 3 6 for further information See below Figure 73 MGA Duplica Version 3 01 USING REPLICATION MANAGER 72 210 x Auto Refreshing every Seconds Errors Suites Monitor Tablespace Checks Replication amp udit Replication Error Lag 52 121 ka ANCILLARY SERVICE 20 09 2001 09 18 26 ERROR INSERT trigger does not exist or is not enabled ANCILLARY amp NCILLA amp HY SERVICE 20 09 2001 09 18 26 Indes on Last Changed Query Field does nat exist 0 ANCILLARY AN
34. 0 REQUIREMENTS 690 AMCILLAR Y SERVICE 690 AMCILLAR YY SERVICE TYPE REHLIEST ESE REHLUIREMENTS ANCILLAR Y_SERYICE ANCILLAR Y_SERVICE_TY PE 682 ANCILLARY REHLIEST ESZ ANCILLARY _REGUIREMENTS 682 AMCILLAR Y SERVICE Coo co oc oc co co co co oc ANCILLARY REQUEST 1 ANCILLAR Y_REGUIREMENTS 0 ANCILLARY SERVICE ANCILLARY SERVICE TYPE 0 ANCILLARY REQUEST 0 ANCILLARY_REGUIREMENTS 0 ANCILLARY _SERYVICE ANCILLARY SERVICE TYPE 0 ANCILLARY REQUEST 0 ANCILLARY_REGUIREMENTS ANCILLARY _SERVICE 0 ANCILLARY SERVICE TYPE 0 ANCILLARY REQUEST 0 ANCILLARY_REGUIREMENTS ANCILLARY SERVICE ANCILLARY SERVICE TYPE 0 ANCILLARY REQUEST 0 ANCILLARY _REGUIREMENTS ANCILLARY SERVICE 20 08 2001 15 54 46 2009 2001 15 54 47 2009 2001 15 54 47 2070972001 15 54 47 20 03 2001 15 53 45 20709 2001 15 53 45 20709 2001 15 53 45 20709 2001 15 53 45 20 09 2007 15 52 43 20 09 2007 15 52 43 20 09 2007 15 52 43 20 09 2007 15 52 43 2009 2001 15 51 41 2070972001 15 51 41 20092001 15 51 41 202092001 15 51 41 20 03 2001 15 50 40 20 03 2001 15 50 40 20 09 2007 15 50 40 Figure 85 Performance Details Tables view The following table describes the information displayed for each table in the Tables tab Table 10 The time it took to complete the query in seconds even though the heading says Minutes The name of the table on th
35. 09 2001 01 54 29 PM Carosi Figure 25 Edit database link window MGA Duplica Version 3 01 INSTALLATION 37 I x d Database link has been updated and tested Figure 26 Confirmation that the changes to the database link were OK 4 6 3 How to Delete a Source Database Link If you need to delete a database link select the delete option from the menu options as shown in Figure 21 or Figure 22 The following window will appear Figure 27 When you press delete Duplica will make you confirm that you want to delete the database link Figure 28 if so the database link to the source 1s erased Deleting Source Database Link X DB Link User Name Password Host Mame Created 19709 2001 01 54 23 men Figure 27 Window to delete a database link Confirm x 9 You wish to delete the database link Figure 28 Confirm that you want to delete the database link MGA Duplica Version 3 01 INSTALLATION 38 4 7 Updating Tables This functionality is required because you will often find that you need to add an extra field to the source and target tables being replicated This will generally occur when you are in the process of adding tables a suite and subsequently find that there is no last changed query field for the table that you wish to replicate There are two stages to updating tables e Create the target and source scripts for each table to be updated Review edit Gf nece
36. 15 54 47 Finished Replication T able AMCILL amp SRE Y SERVICE at 20 03 2001 15 54 47 Started Replication for Table amp N ILLAR Y SERVICE at 20 09 2001 15 54 47 Finished Replication for Suite ANCILLARY errors warnings 0 Finished Replication for Table ANCILLARY SERVICE at 20 09 2001 15 54 47 Starting Replication for Suite ANCILLARY Run ID 698 Started Deletions for Table ANCILLARY REQUEST at 20 03 2001 15 54 46 Finished Deletions Far Table ANCILLARY REQUEST at 20 08 2001 15 54 45 Started Replication for Table ANCILLARY REQUEST at 20 08 2001 15 54 46 Finished Replication Table ANCILLAR Y_REQUIREMENTS at 20 03 2001 15 54 46 Started Replication Far Table ANCILLARY REQUIREMENTS at 20 08 2001 15 54 46 Finished Replication for Table ANCILLAR Y_REQUEST at 20 09 2001 15 54 46 Finished Deletions for Table ANCILLARY REQUEST at 20 09 2007 15 53 45 Started Replication for T able ANCILLARY REQUEST at 20 09 2001 15 53 45 Finished Replication Far T able ANCILLARY REQUEST at 20 09 2001 15 53 45 Started Replication for T able ANCILLARY SERVICE at 20 08 2001 15 53 45 Finished Replication for Table ANCILLARY SERVICE at 20 09 2007 15 53 45 Finished Replication for T able ANCILLARY 5 at 20 09 2001 15 53 45 Started Replication For Table AMCILLAFT REBUIREMENTS at 20 09 2001 15 53 45 Started Replication for T able ANCILLARY SERVICE TYPE at 20 09 2001 15 53 45 Figure 87 Audit Tab The Audi
37. 3 2 Errors The Errors Tab contains errors that are related to Live Database and General Replication Errors relate to the health of the database The Errors view is shown below Figure 101 The Errors view is divided into two sections live Replication Errors live Database amp General Duplica Errors No data is displayed when you first open the Error tab You must refresh the lists using the refresh icon The errors are stored in the local database and remain there for seven days unless you remove them earlier ipi xj Auto Refreshing every 10 m Seconds Suites Monitor Tablespace Checks Repication Audit Log Replication Log d 4 s Live Replication Errors Auto Refresh Play Alarm CM HNIC 37 7 S N 0 ANCILLARY ANCILLA amp RY SERVICE TYPE 1082 20 09 2001 11 58 24 not exists or is disabled 0 ANCILLARY SERVICE TYPE LIVE 1083 20 03 2001 11 58 24 ERROR Last Changed Query Field does not exist 0 ANCILLARY ANCILLARY_SERVICE_TYPE LIVE 1084 20 09 2001 11 58 24 ERROR Primary Key does not exists or is disabled 0 ANCILLARY ANCILLARY SERVICE TYPE LIVE 1085 20 03 2001 11 58 24 ERROR Index on Last Changed Query Field does not exist 0 ANCILLARY ANCILLARY_SERVICE_TYPE LIVE 1086 20 09 2001 11 58 24 ERROR INSERT trigger does not exist or is not enabled 0 ANCILLARY ANCILLARY SERVICE LIVE 1097 20 09 2001 11 58 32 ERROR Last Cha
38. 91 is tested press OK The refetch will then take place MGA Duplica Version 3 01 USING REPLICATION MANAGER 90 Schema Passwords a 1 Test Passwords Figure 90 Schema Passwords Information d All passwords valid Figure 91 Confirmation that passwords are valid You will see the popup message Refreshing Suite Data as the task completes You should also notice that an MS DOS window opens establishes a connection using the login details and then runs a script that will refetch the data If the refetch was successful the MSDOS window will close and you will be returned to the Replication Manager window 6 8 Get Row Counts Suite Level The Row Counts button on the main menu Figure 92 basically returns a count of the number of rows of data at the server source database and the target local database for the selected suite s or for all scheduled suites MANUAL OPERATIONS COUNT ROWS Figure 92 Row Counts Button and options The row count results are inserted into the Replication Manager Suites List window under the column headings Local CNT and Server CNT MGA Duplica e Version 3 01 USING REPLICATION MANAGER 91 6 9 Printing Reports The following data can be printed from their respective windows e Suites e Performance Tables e Performance Suite Graphs e Audit Logs Error Logs e Tables From the Replicati
39. AMPLE ERROR MESSAGE 1 se unes ete enses sene nnns 29 FIGURE 13 EXAMPLE ERROR MESSAGE NO 2 30 FIGURE 14 DBA LOGON ERROR 30 FIGURE 15 HOW TO START THE DUPLICA REPOSITORY 31 FIGURE 16 ALTERNATIVE WAY TO START THE DUPLICA REPOSITORY 31 FIGURE 17 DUPLICA REPOSITORY SET UP WINDOW ccccccsecscsssscscsscccscsecscseneescscnscecacsenecscscnscscussencseneesenes 32 FIGURE 18 ORACLE LOGON PROMPT cccccsecccsscccescccesecceeeccescsceucccuseccueceeecesenceseueceueeceeecsseceseecseecsseeceeanes 33 FIGURE 19 POP UP WINDOW TO INDICATE SQL FILES BEING 34 FIGURE 20 CONFIRMATION THAT THE DUPLICA REPOSITORY SET UP IS COMPLETE ccce 34 FIGURE 21 LOCATION OF ADD SOURCE DATABASE 35 FIGURE 22 ALTERNATIVE LOCATION OF ADD SOURCE DATABASE OPTION eee 35 FIGURE 23 FILL IN THE SOURCE DATABASE LINK DETAILS IN THE ABOVE WINDOW 36 FIGURE 24 WINDOW CONFIRM THAT THE DATABASE LINK HAS BEEN CREATED ecce 36 FIGURE 25 EDIT DATABASE LINK WINDOW Ca mE yia DEDI d d 36 FIGURE 26 CONFIRMATION THAT THE CHANGES TO THE DATABASE LINK WERE OK een 37 FIGURE 27 WINDOW TO
40. ANAGER 79 6 6 Viewing the Suite Details The suite details options are summarised below Table 7 Suite Details Description Option Log Displays audit and error logs related to the selected suite audit and error logs related to the selected suite Performance Displays a graph and table that shows either the number of rows processed or the amount of time it took to complete the query for each run number Settings Displays a view of the run time settings and statistics Tables Shows details for individual tables in a suite including comparisons of the server and local row counts This option allows you to edit tables Table 7 Summary of the Suite Details Options To view the suite details see Figure 82 Highlight the suite to be changed gt Right mouse click Table Target Schema aT DAGE COMPANY REPOMWNER Add Suite D DASP EQUIPMENT REPDWMER Modify Suite DASP EXTERNAL USE LEIGHG Delete Suite K TU DB LINK TD USERS REPOWNER 5 Schedule Suite Un Schedule Suite Refetch Data Update Raw Count Statistics Figure 82 How to view the Suite Details using a right mouse click button 6 6 1 Suite Settings The Suite Details window is shown in Figure 83 MGA Duplica Version 3 01 USING REPLICATION MANAGER 8 0 Suite Information Ez Suite Name ANCILLARY Job Status STOPPED Replication Interva Shats Int Unit Query Tim
41. CILLARY SERVICE TYPE 20 09 2001 08 18 26 Primary does not exists or is disabled D ANCILLARY ANCILLARY _SERYVICE_TYPE 20 09 2001 09 18 26 Last Changed Query Field does not exist ANCILLARY ANCILLARY SERVICE 20 09 2001 09 18 26 Primary does not exists or is disabled QO ANCILLARY ANCILLARY SERVICE 20 09 2001 08 18 26 INSERT trigger does nat esist or i enabled 0 ANCILLARY ANCILLARY _ SERVICE 20 09 2001 03 18 26 Indes on Last Changed Query Field does nat exist ANCILLARY ANCILLARY SERVICE 20 09 2001 09 18 26 Primary does not exists or is disabled QO ANCILLARY ANCILLARY SERVICE 20 09 2001 08 18 26 Last Changed Query Field does not exist ANCILLARY ANCILLARY _ REQUIREMENTS 20 09 2001 09 18 26 INSERT trigger does nat exist ar is nat enabled 0 ANCILLARY ANCILLARY REQUIREMENTS 20 09 2001 09 18 26 Index on Last Changed Query Field does not exist 0 ANCILLARY ANCILLARY REQUIREMENTS 20 09 2001 09 18 26 Primary does not exists or is disabled 0 ANCILLARY ANCILLARY REQUIREMENTS 20 09 2001 09 18 26 Last Changed Query Field does not exist ANCILLARY ANCILLARY REQUIREMENTS 20 09 2001 09 18 26 Index Last Changed Query Field does not exist 0 ANCILLARY ANCILLARY REQUEST 20 03 2001 09 18 26 INSERT trigger does not exist or i nat enabled Figure 73 Replication Monitor Error Log displaying validation errors The tables can be invalid for a number of reasons The main reasons are e N
42. CRIPTIONS sae 110 TABLE 36 REPLICATION ERROR LOG DESCRIPTION een eser nennen nnns 112 MGA Duplica Version 3 01 INTRODUCTION 10 Revision History Date Author User Manual Software Revision Notes Version Version 19 09 2001 David Joy 1 2 4 Save 2 3 as 2 4 make adjustments 23 09 2001 Sebastian 1 1 2 4 2 9 Make necessary changes for Perera version 2 4 2 9 5 Sept 2002 Mark Rance 1 2 8 0 Make necessary changes for version 8 0 19 October Mark Rance 1 8 3 01 Make necessary changes for 2002 version 8 01 1 July 2003 Update documentation Document ID Duplica_3 01_User_Manual doc Location C Documents and Settings Robert My Documents MGA Work Operations Products Duplica 3 01 Duplica_3 01_User _Manual doc MGA Duplica Version 3 01 INTRODUCTION 11 1 Introduction 1 1 Purpose and Scope of this Manual MGA Duplica Duplica 1s a software tool developed by Mark Gurry and Associates MGA This product manages the replication of one database to another database This user manual shows how to set up and use Duplica productively and efficiently It includes meaningful and intuitive descriptions and screen illustrations to describe the functionality of Duplica and how to perform various tasks Duplica has two main functionalities e The Replication Manager allows you to schedule the replication process e The Replication Monitor allows
43. DATION SETTINGS c cccccccccsscccsecccseccecsccccuccecuscececsueecseeuscseeceeuecseecseeucseeusceucseeeseeenes 70 FIGURE 70 CONFIRM THE REMOVAL OF THE TABLE FROM THE SUITE ecce eene hne nnne 71 FIGURE 71 CONFIRMATION THAT THE TABLE S ARE 71 FIGURE 72 ERRORS FOUND IN THE TABLE VALIDATION 71 FIGURE 73 REPLICATION MONITOR ERROR LOG DISPLAYING VALIDATION ERRORS 72 FIGURE 74 SUITE SETTINGS WINDOW FOR OPTIONS 1 amp 2 73 FIGURE 75 POPUP TO SAVE CHANGES MADE TO THE SUITE SETTINGS cccccceeccceeccceseccesecceeceeencceeeeceeeeeees 74 FIGURE 76 CONFIRMATION THAT THE SUITE S ARE VALID cccsssseseccccecceessseeccceeeseaseseeecceeeeeaseeeeeceesseaaaeeeeees 75 FIGURE 77 ERRORS FOUND IN THE SUITE VALIDATION cccccccccceeseccsceeeseecseeeeseecseceueecsseueeeeseseeueeessesueeeeseees 75 FIGURE 78 ALTERNATIVE WAY TO ADD A SUITE 76 FIGURE 79 ADD SUITES WINDOW ccccsccccsecccsscccessccesececscceecscencceeueceeecsescseeusceeeceeecseeecseseceeessseecseeeseeenes T4 FIGURE 80 HOW TO REMOVE A SUITE USING THE RIGHT MOUSE CLICK SHORTCUT 78 FIGURE 81 POPUP WINDOW TO CONFIRM THE REMOVAL OF A SUITE ccceeeeenn 78 FIGURE 82 HOW TO VIEW THE SUITE DETAILS USING A RIGHT MOUSE CLICK BUTTON 79 FIGURE 83 SUITE
44. DELETE A DATABASE LINK e eee emen e nene rhse hse rese hse ese rie s sese e rsen 37 FIGURE 28 CONFIRM THAT YOU WANT TO DELETE THE DATABASE LINK eene 37 FIGURE 29 HOW START UPDATE TABLES 38 FIGURE 30 HOW START UPDATE TABLES ALTERNATIVE sssssecccccccesssseecccceceaeeeseecccceesaaeseeeeceeeseuaneeeeees 39 FIGURE 31 UPDATE TABLES 3 4 45 39 FIGURE 32 UPDATE TABLES GUI WITH ALL OPTIONS SELECTED 40 FIGURE 33 SAVE SQL FILE WINDOW NOTE THE DEFAULT FILE NAME CONTAINS THE WORD SOURCE 40 FIGURE 34 POPUP TO ACKNOWLEDGE THAT THE DDL WAS CREATED SUCCESSFULLY IN THE DIRECTORY AS woe 41 FIGURE 35 SAVE SQL FILE WINDOW NOTE THE DEFAULT FILE NAME CONTAINS THE WORD 4 FIGURE eM Ch 42 FIGURE 37 USING BOTH THE TABLE NAME AND THE COLUMNS FILTER THE TABLE LIST SEE NEXT FIGURE 43 FIGURE 38 FILTERED UPDATE TABLES LIST AFTER THE ABOVE QUERY PARAMETERS WERE 43 FIGURE 39 ADD NEW COLUMN WINDOW WITH EXAMPLE COLUMN NAME ENTERED 44 FIGURE 40 HOW TO START DUPLICA S
45. ETTINGS 44 FIGURE 41 HOW TO START DUPLICA SETTINGS ALTERNATIVE 45 FIGURE 42 DUPLICA SETTINGS 46 FIGURE 43 EXAMPLE OF THE POPUP WINDOW TO CONFIRM A DATABASE SOURCE CHANGE 47 FIGURE 44 HOW TO OPEN TABLE VALIDATION VIA THE SETUP BUTTON 48 MGA Duplica Version 3 01 INTRODUCTION 7 FIGURE 45 HOW TO OPEN TABLE VALIDATION VIA THE MAIN MENU SETUP 48 FIGURE 46 SERVER TABLE VALIDATION enhn nnn en esee ese sn seen en esee esee sese enses 50 FIGURE 4 LOCAL TABLE VALIDATION 51 FIGURE 48 TABLES TO APPLY 52 FIGURE 49 OPTION 52 FIGURE 50 START THE REPLICATION CONTROL MECHANISM 53 FIGURE 51 TURN OFF THE REPLICATION CONTROL 53 FIGURE 52 EXAMPLE SUITE GROUPINGS FOR COMPANY RELATED TABLES AND USER RELATED TABLES 54 FIGURE 53 HOW TO OPEN REPLICATION MANAGER USING THE MANAGE BUTTON 55 FIGURE 54 HOW TO OPEN REPLICATION MANAGER FROM THE TOOLS OPTION 55 FIGURE 55 REPLICATION MANAGER WINDOW SHOWING CURRENT SUITES 56 FIGURE 56 REPLICATION MANAGER WINDOW
46. Error C Warning v Delete Trigger properties Error warming i Column Settings Error C warning v Primary Key existence and properties Error Warning LN BL 6 Waring SE Waring Warring nem Cancel Figure 65 Server Table Validation Local Table Validation Edit the Local Table Validation settings then press OK The Reset button will change the settings back to your original settings as opposed to the default settings Refer to section 4 9 for further information regarding the validation settings MGA Duplica Version 3 01 USING REPLICATION MANAGER 67 Add Tables Table Details Server Table Validation Local Table Validation Validation Far Insert Update Operations Iv Last Changed Query Field settings and properties Error C warning i Friman Key existence and properties f C Warning Iv Index on Last Changed Query Field Error f Warning Cancel Figure 66 Local Table Validation Press OK to add the table to a suite Press CANCEL if you do not wish to proceed Either option will return you to the Suite Information window defaulted to the Tables Tab 6 4 2 2 How to Edit the Table Settings and Validation Settings From the Suite Information Tables Tab window double click on Table of concern use the Table Settings Icon Figure 67 MGA Duplica Version 3 01 USING REPLICATION MANAGER 68 Suite Information Suite
47. Figure 112 Eephcation Monitor E loj x Auto Refreshing every 0 zi Seconds Auto Refresh Tables that possibly need to be rebuilt Table Name Figure 112 Tables to Rebuild view The following table describes tables that require re indexing Table 34 s Manual Refresh W Auto Refresh Allow the view to be refreshed at the time interval specified in the Auto Refresh central control MGA Duplica Version 3 01 USING REPLICATION MONITOR 110 Table 34 Description of the information displayed in the Tables to Rebuild view 7 3 5 Replication Audit Log The Replication Audit Log view is shown below Figure 118 Eephcation Monitor loj x Auto Refreshing every 0 Seconds Ema Suites iManitar Checks Replication Audit Log RpheshonEnar Suite Table Table Name 388 ANCILLARY SUITE 20 09 2001 14 77 10 Finished Replication for Suite ANCILLARY errors 0 warnings 388 ANCILLARY ANCILLARY SERVICE TYPE 20 09 2001 14 17 10 Finished Replication for Table ANCILLAR Y_SERYVICE_TYy PE at 20 08 2001 14 17 10 388 ANCILLARY ANCILLARY SERVICE TYPE 20 08 2001 74 77 10 Started Replication for Table ANCILLARY SERVICE TYPE at 20 09 2001 14 17 10 388 ANCILLARY ANCILLARY SERVICE 20 09 2001 14 77 10 Finished Replication for Table ANCILLARY _SERVICE at 20 09 2001 14 17 10 388 ANCILLARY ANCILLARY_SERVICE 20
48. G REPLICATION MANAGER 85 Print Report Refresh Table List Filter Data Table 13 Tables tab Performance Graph Preview Options 6 6 4 Suite Log Details The Logs tab has the following tabs e Audit tab e Error tab 6 6 4 1 Audit Tab The Audit tab is shown below Figure 87 Suite Information X E Suite Name ANCILLARY Job Status PAUSED Logs Performance Settings Tables Audit Error Hun Table Name Time Text Bunt Table Name Time O O 698 ANCILLARY SERVICE 698 ANCILLARY_SERYVICE SERVICE 698 SUITE 698 ANCILLARY SERVICE 698 SUITE 698 ANCILLARY REQUEST 698 ANCILLARY REQUEST 698 ANCILLARY REQUEST Bde ANCILLARY REBHUIREMENTS B38 ANCILLARY REBHUIREMENTS 698 ANCILLARY REQUEST 694 ANCILLARY REQUEST 594 ANCILLARY REQUEST 694 ANCILLARY REQUEST 694 ANCILLARY_SERVICE 594 4NCILLARY_SERYVICE 594 ANCILLARY_REQUIFEMENTS 594 ANCILLARY _REQUIFEMENTS 694 ANCILLARY SERVICE TYPE 2009 2001 15 54 47 20 08 2001 15 54 47 20 03 2001 15 54 47 20 09 2001 15 54 47 20 09 2001 15 54 47 20 09 2001 15 54 46 20 09 2001 15 54 46 20 09 2001 15 54 46 20 09 2001 15 54 46 20 09 2001 15 54 46 20 09 2001 15 54 46 20 09 2001 15 54 46 20 08 2001 15 53 45 20 08 2001 15 53 45 20 08 2001 15 53 45 20 08 2001 15 53 45 20 08 2001 15 53 45 20 08 2001 15 53 45 20 09 2001 15 53 45 20 09 2001 15 53 45 Started Replication For Table AMCILLAR Y SERVICE at 20 09 2001
49. LAE SERVICE 20 05 2001 14 16 15 Finished Replication for T able SERVICE at 20 03 2001 14 16 15 Figure 113 Replication Audit Log view This view displays all replication events that have taken place since the last time the table was purged deleted The data can be printed refreshed auto refreshed or purged at any time The following table describes the information displayed Table 35 Run Number The number of the run during which the error was generated Suite Table The source table being replicated at the time the message was generated Local Table Name The table on the local database being updated The date and time the message was generated The details of the message Manual Refresh In the current version of Duplica a refresh 1s required after you use the Delete button Delete all Log entries When you press this button you are prompted with a choice to delete all log entries or those older than 5 days Auto Refresh Allow the view to be refreshed at the time interval specified in Auto Refresh central control Table 35 Replication Audit Log descriptions MGA Duplica Version 3 01 USING REPLICATION MONITOR 111 7 3 6 Replication Error Log The Replication Error Log view is shown below Figure 114 This view lists all the errors that have occurred during the replication process This view can be printed refreshed auto refreshed or purged at any time
50. MGA Duplica User Manual Version 3 01 CONTENTS 2 Table of Contents L INTRODUCTION rcc 11 1 1 PURPOSE AND SCOPE OF THIS MANUAL a 11 1 2 WHERE TO STORE THIS MANUAL Seba genou Coi Ans 11 97 OW OS HIS IP LEE Pc 11 2 THE CONCEPT OF REPLICA 13 2 1 PO 13 Ze WHATI DATA REPLICATION 5 s 13 REPCE ATON 131 eer m 13 2 3 1 A a 13 2 3 2 E EE TAE L RO 13 pe Large Supermarket Retail Store 1 14 2 3 4 Maintain Back up eee ee ee eee EEE ELLE EEE EEE EEE EEE EEE n aaa 14 2 4 DUPLICA DESIGN AND HOW TP 15 2 4 1 The Graphical User Interface 1 15 2 4 2 The 16 2 4 3 The 17 2 4 4 The Replication Control Scheduling 18 204 Montor BCDHICSDO Cede 18 244 2 M
51. N TET 98 98 JepHcaltom ee 99 7 3 2 2 Live Database and General Replication 99 7 3 3 H 100 7 3 3 1 E Hir cibi T E 100 pM nii RR 101 425 59 ates Bun PettottldllCe icdt auno 102 ESEMPIO 102 gs s P Table Graph TAD T 103 NU ce 104 Diti ce 104 106 7 3 4 Tablespace Cheeks 106 E 5 4 107 i on ner en DT OT DESERT 108 pA MES Crash opr 108 Tables to Re DUE adt 109 Jr TED OM o 110 7 3 6 KOPU on Metu CI MU 111 3 WSUNG E m 113 9 CEOSING DUPEICA EEE 114 9 VATIENAIN MEN M HH 114 9a USWGTHE SHORTCUT KEY 5 45 a 114 93 USING 114 10 TROUBLE SHOO DING Cm 115 11 GLOSSARY OF TERMS
52. NS ccccceecccesecccsecccescccenccceueccesecsuecseeuccseueceueecseecsencsssneceueeseeseseanes 94 MGA Duplica Version 3 01 INTRODUCTION 8 FIGURE 98 HOW TO OPEN REPLICATION 97 FIGURE 99 HOW TO OPEN REPLICATION MONITOR USING THE TOOLS 97 FIGURE 100 REPLICATION MONITOR WINDOW DEFAULT VIEW CURRENTLY RUNNING JOBS 97 FIGURE 101 REPLICATION MONITOR ERRORS VIEW AFTER REFRESH ssssscccceeceeseeeccceeeceeseeeecceeeseaaeeeeeees 98 FIGURE 102 SUITES MONITOR VIEW DEFAULTED TO THE SCHEDULED TO RUN NEXT TAB 100 FIGURE 103 CURRENTLY RUNNING VIEW SHOWS THE SUITES THAT ARE CURRENTLY RUNNING 101 FIGURE 104 SUITE GRAPH VIEW SORTED BY QUERY TIME ccccsscccecssececcssececaeecceeuecceeusececuueccesaeceeeueeceeaes 103 FIGURE 105 TABLE GRAPH VIEW SORTED BY QUERY TIME cccccsccssccssccsccsccsscesccusceuccesccuscesscenccuscesseesceuss 104 FIGURE 106 TABLE GRAPH VIEW SORTED BY ROWS PROCESSED sccccsececesecceescccescscencceeuecseusceeecseeneseueeees 104 BG OR DOT AUDI E 105 FIGURE 108 LATEST RUN LOGS ERRORS VIEW wssicsisssdsricnsssecwsanssannennsdineaenssoiatuesardsaensoesitussaeasiensavsnseetaraveensuaus 106 FIGURE 109 TABLESPACE STATISTICS FOR THE TARGET
53. OR INSERT trigger does nat exist ar is nat enabled 20 03 2001 11 59 11 ANCILLARY ERROR Index Last Changed Query Field does not exist 20 09 2001 11 53 11 AMCILLARY REGLIEST ERROR Primary Kep does not exists or iz disabled 20 09 2001 11 59 11 ANCILLARY REQUEST ERROR Last Changed Query Field does not exist 20 09 2001 11 59 01 ANCILLARY REBHLIEST ERROR INSERT trigger does not exist or is not enabled 20 09 2001 11 59 01 ANCILLARY REHLIEST ERROR Index an Last Changed Query Field does not exist 20 09 2001 11 59 01 ANCILLARY REQUEST ERROR Primary does not exists or iz disabled 20 09 2001 11 59 01 ANCILLARY REQUEST ERROR Last Changed Query Field does not exist 20 08 2001 11 58 39 ANCILLARY REQUEST ERROR INSERT trigger does nat exist ar is not enabled 20 08 2001 11 58 39 ANCILLARY REQUEST ERROR Index on Last Changed Query Field does not exist 20 08 2001 11 58 33 ANCILLARY REGLIEST ERROR Primary Kep does not exists or iz disabled 20 03 2001 11 58 33 ANCILLARY REBLIEST ERROR Last Changed Query Field does nat exist 20 09 2001 11 58 37 ANCILLARY REGLUIREMEMTS ERROR INSERT trigger does nat exist ar is not enabled cc Figure 88 Error Log The Error tab displays errors that occurred during the Replication process Error messages are stored in the REPLICATION ERRORS table on the local database The following table explains the information
54. RY OF TERMS 116 11 Glossary of Terms Term Definition A B C Client Connection Control Data Control Mechanism See Replication Control Scheduling Mechanism CPU Central Processing Unit Cue Card D Data Database Link DDL Scripts G GUI Graphical User Interface Last Changed Query Field Last Changed Last Updated M N O Oracle Database P MGA Duplica Version 3 01 GLOSSARY OF TERMS 117 Term Definition Query Time R Refetch Refresh Duplica Owner Duplica Repository Replication Replication Manager Replication Manager Control Mechanism Replication Monitor Replication Source See Source Database Repository 5 Schedule Schema Scripts SQL code Server Database See Source Database Source Database Source See Source Database SQL Standard Query Language Replication is performed on a suite level A suite may Suite contain many tables A suite is a group of tables with similar replication requirements T Target Table Equivalent to Local Table Target Database Equivalent to Local Database Target Schema Tnsnames ora file Tables Tables makeup suites A table can only belong to one suite U Users V Validation
55. Replication Monitor loj xi Auto Refreshing every 0 zi Seconds 1 FERE ESSERE suites Monitor ablespace Lhecks Ab Auto Refresh O ANCILLARY AMNCILLARYT SERVICE 20 09 2001 14 14 10 ERROR Primary Key does not exists ar is disabled AMCILLARY amp MCILLARY SERVICE 1112 20 03 2001 12 52 50 Primary Key does not exists or is disabled ANCILLARY ANCILLARY SERVICE 1111 20 03 2001 11 53 14 INSERT trigger does not exist ar iz nat enabled O ANCILLARY amp MCILLARY SERVICE 1110 20 03 2001 11 53 14 Index on Last Changed Query Field does nat exist 0 ANCILLARY AMCILLARY SERVICE 1109 20 09 2001 11 53 14 Primary does not exists or iz disabled 0 ANCILLARY ANCILLARY SERVICE 1108 20 09 2007 11 53 14 Last Changed Query Field does not exist ANCILLARY amp HMCILLARY SERVICE 1107 20 03 2001 11 53 14 Primary Key does not exists or is disabled ANCILLARY ANCILLARY REQUEST 1106 20 03 2001 11 53 11 INSERT trigger does not exist ar iz enabled ANCILLARY ANCILLARY REQUEST 1105 20 03 2001 11 53 11 Index en Last Changed Query Field does not exist ANCILLARY ANCILLARY REBUEST 1104 20 09 2007 11 53 11 Primary Key does not exists or is disabled ANCILLARY ANCILLARY REQUEST 1103 20 09 2001 11 53 11 Last Changed Query Field does nat exist QO ANCILLARY ANCILLARY REGLIEST 1102 20 09 2007 11 53 01 INSERT trigger does not exist a
56. S B nues o Load Report Close Close the Print Preview and return back to the Print Report window Table 19 Print Preview Options 6 9 2 Fonts The fonts option allows you to choose the font font size style effect and script Figure 95 MGA Duplica Version 3 01 USING REPLICATION MANAGER 93 Font Font style SIZE Cancel Italic Bald Bold Italic Palatino Linotype Roman Script Small Fonts md O Symbol Effects Strikeout Underline Color mm Black Script Westem Figure 95 Font Options Choose a small font size if you have selected several columns to display Once you have made your changes press OK Use the Print Preview option to view the changes 6 9 3 Printer Setup The Printer Setup allows the user to change the printer settings Figure 96 In the present version of Duplica you should change the Printer Settings from the main Printing Reports window rather than from the Print Preview window The default printer is displayed in the Printer Name window 21 x Status Paper out 0 documents waiting Type EPSON EPL N4000 Advanced Wher IP 182 158 3 215 Comment Print range AJ Pages from 0 to 0 Copies Number of copies Selection pi Collate MGA Duplica Version 3 01 USING REPLICATION MANAGER 94 Figure 96 Printer Settings The properties button gives
57. SETTINGS WINDOW 80 FIGURE 84 PERFORMANCE SUITE GRAPH VIEW cccccesecccsecccescscenccceuecceseccescseeueceeneceeuecseecssecseeneseeeceeeceenes 81 FIGURE 85 PERFORMANCE DETAILS TABLES VIEW cccccescccesecceescccscccenecceecceecseencessueceuecseeecsseneseueeesueeeeees 92 FIGURE 83 FIGURE 67 AUDIT E 85 FIGURE 65 ERROR LOG TAD 86 FIGURE 89 DATA REFRESH WIZARD ccsecscesecccsscevacsenecsceceacscescacacsenecscsecacscuececucsenecscneacseuassussenecacsesacazacs 88 FIGURE DU SCHEMA PASSWORD 5 1 90 FIGURE 91 CONFIRMATION THAT PASSWORDS ARE VALID cccccseccceecccescccesececescceeuceceueceuueceeeecesencseueseuecenes 90 FIGURE 92 ROW COUNTS BUTTON AND OPTIONS 90 FIGURE 93 EXAMPLE PRINTING REPORT WINDOW cccccccccesecceescccencccenecceecceeceeenceceneceueceseeceseeceeueeseueesenes 9 FIGURE 94 PRINT PRE VIEW OP TIONS wicaecensiucizeactnceacanaieazennsapaqanausante 92 FIGURE 95 FONT OPTIONS icsosisccssnsavsaccasonntovnieusancasdenssedabessacnswendosaiwaeaccnsdenouaanbuaincnsmecdetadouaeotnvdenennanensennavandeeentbe 93 FIGURE 96 FPRINTER SETTINGS 94 FIGURE 97 FILTER BUTTON AND OPTIO
58. Started Replication for Table ANCILLARY SERVICE at 20 09 2007 15 23 55 aro ANCILLARY ofa ANCILLARY 578 ANCILLARY 578 ANCILLARY aro ANCILLARY aro ANCILLARY ANCILLARY REQUIREMENTS 20 09 2007 15 23 55 Finished Replication For T able ANCILLARY REQUIREMENTS at 20 09 2001 15 23 55 ANCILLARY REQUIREMENTS 20 09 2007 15 22 56 Started Replication tor Table ANCILLARY 5 at 20 09 2001 15 23 56 ANCILLARY REQUEST 20 09 2001 15 23 56 Finished Replication For Table SNCILLARY_REQUEST at 20 08 2001 15 23 56 ANCILLARY REQUEST 20 09 2001 15 23 56 Started Replication for Table amp REGUEST at 20 03 2001 15 23 56 ANCILLARY REQUEST 20 03 2001 15 23 56 Finished Deletions for Table ANCILLARY _REQUEST at 20 03 2001 15 23 56 ANCILLARY REQUEST 20 09 2001 15 23 56 Started Deletions for Table ANCILLARY_REQUEST at 20 03 2001 15 23 55 El Figure 107 Audit view The following table describes the information displayed in the above figure Table 29 Run Number Suite Table The number of the run during which the error was generated The Source table being replicated at the time the message was generated The table on the local database being updated The date and time the message was generated The details of the message Manual Refresh In the current version of Duplica a refresh is required after you use the Delete button Local Table Name Time stamp Text Delete all Log entries When you press thi
59. T 20 03 2001 11 59 11 INSERT trigger does not exist or is not enabled QO ANCILLARY ANCILLARY REQUEST 20 09 2001 11 58 11 Index on Last Changed Query Field does not exist ANCILLARY ANCILLARY REQUEST 2070972001 11 59 11 Primary Key does not exists or iz disabled ANCILLARY ANCILLARY REQUEST 20 03 2001 11 59 11 Last Changed Query Field does not exist AMCILLARY ANCILLARY REQUEST 20 09 2001 11 59 01 INSERT trigger does not exist ar is not enabled of Figure 108 Latest Run Logs Errors view This view shows error messages that were generated for all suites from the most recent run The following table describes the information displayed Table 80 eem Status This is LIVE if the message will be deleted within seven days of it being generated or LOGGED if it will be deleted within two days of it being generated Table 30 Description of the Errors view 7 3 4 Tablespace Checks The Tablespace Checks view has four tabs Table 31 m o i ee Table Spaces The free space in each of the tablespaces in the database Free Space Graph A graph of the data displayed on the Table Spaces tab Version 3 01 MGA Duplica USING REPLICATION MONITOR 107 Objects About to Crash Objects having trouble creating a Next Extent Tables To Rebuild Tables that require indexing Table 31 Summary of the Tablespace Checks functionality 7 3 4 1 Tablespaces The Table Spaces view is shown below Figu
60. a database Once the DBA has logged in a window will appear where the DBA can enter the details for the new user go to section 4 4 4 MGA Duplica Version 3 01 INSTALLATION 28 4 4 3 How to Create a New User from within Duplica To create a new user from within Duplica do the following Setup gt New Repository Owner refer to Figure 9 Add New User E x Mew User User Hame Password Verity Password T ablespace Default Quota 20 Temporary T Database Administrator User Name Password Cancel Figure 9 How to create a new user from within Duplica Note Once you have entered details for New User you must enter the Database Administrator username and password This ensures that only the DBA is allowed to create a new user ca 4 4 4 Enter the New User Details The Add New User window is where the DBA enters the new user information such as user name password default tablespaces and size and the temporary tablespace Figure 10 If you were creating a new user from the Duplica login prompt then you will notice that the database administrator login and password are shown again at the bottom of the window However if you are creating a new user from within Duplica then the DBA username and password will be blank This ensures that only the DBA 15 allowed to create a new user MGA Duplica Version 3 01 INSTALLATION 29 New User
61. anager suites list highlight the suites you want to refetch Note Refetch does not use the Maximum Number of Rows It will get all the records that match the criteria selected To start the Data Refetch Wizard Click the Refetch button on the main menu or e Right mouse click gt Refetch Data If you click on the arrow next to Refetch you can choose All Scheduled Suites MGA do not recommend use of this option The Data Refresh Wizard is shown below Figure 89 MGA Duplica Version 3 01 USING REPLICATION MANAGER 88 Data Wizard Clean amp Full Refetch Truncate table and copy Last Changed Get All new rows Date Range Refetch Get new rows within a date range Clean amp Date Range Refetch Truncate and get rows within a date range count check after refetch From Date To Date 01 011990 01 01 01 20 08 2001 12 42 46 Figure 89 Data Refresh Wizard Select the option you want to use 6 7 3 Choose a Refetch Option Table 16 summarises the Refetch Options that you can choose Clean amp Full Refetch Removes what is currently in the table and copies all rows from the Source Last Changed Only copies rows with a LastChanged date and time more recent that most recent entry in your database Date Range Refetch Gets all rows with a date within the date range you specify Clean amp Date Range Refetch Removes everything tha
62. arison with having multiple copies of Duplica one per schema for a given database e Improved performance if Duplica has its own tablespace That is it ensures that there 15 no discontention from other schemas e Greater Security The Duplica Repository user should be given greater privileges than the other schemas that are used for data replication e Flexibility You can still have a separate repository for each replicated schema if you wish You must always login to Duplica as the user that owns the Duplica Repository e g repowner Otherwise the GUI interface will not function The Duplica Repository owner must have certain roles and privileges If you have created a user by means other than Duplica then ensure that the roles and privileges are the same refer to section 4 4 for further information MGA Duplica e Version 3 01 THE CONCEPT OF REPLICATION 18 Figure 3 illustrates the Duplica concept in more detail 2 4 4 The Replication Control Scheduling Mechanism There are three major processes that run in Duplica These are e Monitor Replication e Monitor Database e Housekeeping Each of these major processes has several minor processes described below 2 4 4 1 Monitor Replication The monitor replication process handles all scheduling and the submitting of jobs for individual suites The various sub processes occur when the replication control mechanism is turned on They are e Submit a HOUSEKEEPING
63. at suite For further information about the icons and field descriptions in the Table tab refer to section 6 6 3 6 4 2 1 Add Tables to Suites If you want to add tables to a suite click the Add table s to a suite icon This will open up the Add Tables window Figure 64 The Add Tables window contains three tabs e Table Details e Server Table Validation e Local Table Validation Table Details MGA Duplica Version 3 01 USING REPLICATION MANAGER Add Tables Table Details Server Table Validation Local Table Validation T ables Select table to add ANCILLARY AEQUEST ANCILLARY REQUIREMENTS ANCILLARY SERVICE ANCILLARY SERVICE TYPE Search By Search Fre PRE Press EPrez Preorder Example S uite M ame AN Server T able Mame ANCILLARY REQUEST Record Selection Criteri 6 3 x Replication Settings Scan Back Interval Scan Back 1 Last Changed Query Field TIME DF LAST UPDATE v Acton f Insert Only Insert 7 Update Download es Download Order 1 Truncate T able Lop Deleted Record Details T able BNCILL amp R Y REQUEST DEL Last Delete Time P 3 04 2001 Cancel Figure 64 Add Tables Table Details window Choose highlight the tables that you want to add to the suite The tables are listed in alphabetical order Use
64. atus LIVE which means the error will be deleted in seven days from when it was generated The error number in the local database table The time the error occurred The Oracle error message Table 22 Live Replication Errors Field Descriptions 7 3 2 2 Live Database and General Replication Errors Live Database and General Replication Errors relate to the health of the database e g lack of space problems creating the next extent or fragmentation of the indexes Database errors are stored in the DATABASE ERROR LOG table on the local database The following table describes the information displayed for Live Database and General Replication Errors Table 23 Version 3 01 Duplica USING REPLICATION MONITOR 100 Status LIVE which means the error will be deleted in seven days from the time it was generated Text The text in the error message Recommendation The recommendation concerning what action you should take Table 23 Live Database and General Replication Errors Field Descriptions 7 3 3 Suites Monitor The following table summarises the tabs in the Suites Monitor view Table 24 They are described in detail in the next couple of sections Table 24 Summary of the Suites Monitor Tab Options 7 3 3 1 Scheduled to Run Next The Scheduled to run next view is shown below Figure 102 This view only lists the suites that are currently scheduled to run c f Replicati
65. cripts folder a subdirectory of the SQL folder After you have copied the new files remember to copy the scripts folder back into the SQL Folder You are now ready to use Duplica Double click on the Duplica icon that has been placed on your desktop or start Duplica via Start gt Program Files 4 2 Starting Duplica Click on the Duplica icon that has been placed on your desktop or via Start gt Program Files gt Duplica If you are installing Duplica for the first time the logon prompt will display refer to section 4 3 However if you have previously installed Duplica then you may be prompted with a message to upgrade your Duplica Repository Figure 4 x i Old version of Duplica Repository Found You need to perform an Upgrade Figure 4 Duplica will prompt you to upgrade the Duplica Repository if it finds that you have an old version installed MGA Duplica Version 3 01 INSTALLATION 25 4 3 Logon to Duplica Once you have started Duplica the following window will normally display Figure 5 If you are installing Duplica for the first time this window will not display any information If you have previously logged in to Duplica this window will display the most recently used connection and user logon information The Duplica logon window prompts you for user information Previous logon information user name and database SID is stored in the connects dat file Thus if you copied the connects dat file from a pr
66. data tables are those tables that contain data that is changing regularly That is data is regularly inserted updated or deleted from these types of table Duplica performs four tests to validate the common data tables Last Changed Query Field settings and properties The last Changed query field must be populated with a valid column name The specified column must exist 1 the source table and must be of type DATE e Primary Key existence and properties The primary key constraint or unique constraint exists and is enabled e Index on Last Changed Query Field Check that the index exists e Insert Update Trigger properties Check that the trigger is valid and enabled The Delete Control tables are validated with respect to the e Delete Control Table Settings Check that the delete control table is specified Check that the table exists in the source database e Delete Trigger properties Check that the trigger is enabled and valid Check that the trigger writes to the delete control table e Column Settings Check that the table contains all primary key columns for the corresponding table plus the last changed query field e Primary Key existence and properties The primary key should exist and should consist of all the columns in the table MGA Duplica Version 3 01 INSTALLATION 5 0 Validation for Insert Update or Delete Operation Validation for Delete Control T able x Server Table Local Table Tables to Appl
67. dd Column List Figure 31 Update Tables GUI 4 7 2 Generate DDL Scripts Tick the table s which require updating select the new column from the drop down list box or create a new column if necessary then click Generate DDL see Figure 32 MGA Duplica e Version 3 01 INSTALLATION 40 Update Tables Total 4 C ANCILLARY ANCILLARY REBLIEST ANCILLARY ANCILLARY _REGUIREMENTS ANCILLARY ANCILLARY SERVICE Generate DDL ANCILLARY ANCILLARY SERVICE Cancel Filter Click Select All Mew Creation Modified Date TIME DP LAST UPDATE gt Add Column To List Figure 32 Update Tables GUI with all options selected When you press the Generate DDL button you will be prompted with a Save Update Table DDL Script File window that shows the default file name Figure 33 Save Update Table DDL Script File x Save in E Duplica pt 2 REPLO DOL 2003080 124331 5 File name REPLOG DOL 20030807 124424 save as type sat Script Files Cancel 27 Figure 33 Save SQL file Window Note the default file name contains the word SOURCE The sql file is saved into a folder called scripts which is a subdirectory of the default SQL directory that was created when Duplica was installed MGA Duplica e Version 3 01 MGA Duplica INSTALLATION 41 Duplica automatically creates an sql scri
68. displayed for all Replication errors generated for this suite Table 15 Status The error s status If it is LIVE it will remain in the local database for seven days after it was generated If it is LOGGED it will remain for two days after 1t was generated Table Name The name of the table being replicated when the error occurred Version 3 01 MGA Duplica USING REPLICATION MANAGER 87 The Oracle error message Refresh Table List Table 15 Description of the Logs Error window 6 7 Manual Replication 6 7 1 Why use Manual Replication Manual Replication is used when e You want to download a large amount of data Table corruption occurs and data needs to be restored quickly e Replication for one or more tables has been stopped for sometime and you need to quickly replicate those particular tables Duplica has an option called Refetch to perform manual replication Refetch downloads all the data from the source one suite at a time Thus a refetch may involve one or many tables depending on how many tables are defined within the suite You can download one or more suites at any time using the Refetch option You may want to do this if for example you have had Replication turned off for some time and want to copy all of the updated data in one transfer or if you need to re create the replicated database 6 7 2 Start the Data Refetch Wizard Turn off the replication control mechanism In the Replication M
69. e body corporate responsible for the administration and operation of the wholesale national electricity market Electricity is traded between generators and electricity retailers This means that all the electricity output from generators is pooled and then scheduled to meet electricity demand The NEM uses data replication to allow participants to download NEM data to their own systems for analysis and storage 2 3 2 Stock Exchange Model The stock exchange creates a large amount of data on a daily basis With such a large amount of data being generated the database s responsible for storing the data must be organized and managed for optimum performance Stockbrokers all over the country rely on having access to the latest stock prices Remote stockbrokers purchase a license which gives them access to the latest prices There are different types of licenses available If you want to have access to the stock prices as they change at the exchange then you purchase an expensive license You can purchase a cheaper license if you only want to be updated every hour or once a day MGA Duplica Version 3 01 THE CONCEPT OF REPLICATION 14 An example of their database setup could be as follows The stock exchange may have multiple servers each with multiple databases that are updated by the main stock exchange server The databases could be divided depending on the timeframe that data is replicated to them e g 1 minute 5 minutes 15 minutes 1 hour
70. e server database Version 3 01 Query Time Seconds Server Table MGA Duplica USING REPLICATION MANAGER 8 Time Stamp The time when the run occurred Print Opens the Print Report Window Select the columns to display 1 the report Refreshes the screen Table 10 Description of the Tables view 6 6 3 Suite Tables Details The Tables tab is shown below Figure 86 Suite Information xj z Suite Name ANCILLARY Job Status PAUSED Lags Performance Settings Tables SET Dood sera Tics m LLAR Y REGLIEST m LLAR Y REGLIEST 9 07 2001 02 ANCILLARY _REGUIRE ANCILLARY REBLIREMENTS 13 05 2000 08 ANCILLARY SERVICE AMCILLAR Y SERVICE 5 07 2001 11 4 ANCILLARY SERVICE ANCILLARY SERVICE TYPE 4v 20 03 2001 OF 4 E sort B l Query Time C Rows Processed aues Time Rows Processed ANCILLARY REQUEST Rows Processed 0 500 1 000 1 500 2 000 2 500 3 000 3 500 4 000 4 500 5 000 5 500 6 000 6 500 7 000 186 za 181 172 Bea 168 Guery Time Seconds Figure 86 Tables view The main window contains tables and run time statistics These are described in the following table Table 11 Download Indicator If Y the rows will be downloaded for this table N otherwise MGA Duplica Version 3 01 USING REPLICATION MANAGER 84 Increase The increase 1n extent size of t
71. e table was purged deleted Error displays a complete list of the replication errors Table 28 Latest Run Log Options 7 3 3 4 1 Audit view MGA Duplica Version 3 01 USING REPLICATION MONITOR 105 The Audit view is a table that shows all the audit messages generated during the most recent replication run Figure 107 Replication Monitor Auto Refreshing every 10 zi Seconds Etats Suites Monitor Tablespace Scheduled to run next Currently Running Latest Run Performance Latest Run Logs Audit Error Auto Refresh Latest Hun Audit Log Suite Table Local Table Name 0 581 EQUIPMENT 578 ANCILLARY 578 ANCILLARY 578 ANCILLARY 580 COMPANY 572 ANCILLARY 578 ANCILLARY SUITE SERVICE TYPE ANCILLARY SERVICE TYPE SUITE AMCILLAR T SERVICE ANCILLARY SERVICE 20 09 2001 15 23 56 Finished Replication for Suite EQUIPMENT errors D warnings 20 09 2001 15 23 55 20 09 2001 15 23 55 20 09 2001 15 23 55 20 09 2001 15 23 55 20 03 2001 15 23 56 20 03 2001 15 23 55 Finished Replication for Suite ANCILLARY errors 0 warnings Finished Replication for T able ANCILLARY_SERVICE_TYPE at 20 08 2001 15 23 55 Started Replication for T able ANCILLARY SERVICE TYPE at 20 08 2001 15 23 56 Finished Replication for Suite COMPANY errors warnings Finished Replication for Table SERVICE at 20 03 2001 15 23 55
72. e the changes 6 4 4 How to Validate the Suite s v To validate the suites click on the Validate icon located just above the list of suites Once a validation check 1 performed and the tables are valid the following popup window will display Figure 76 Information d All selected Suites are valid MGA Duplica Version 3 01 USING REPLICATION MANAGER 75 Figure 76 Confirmation that the Suite s are valid If the suite s tables are invalid a popup will display like the one below Figure 77 Error x Validation errors Validation for Suite ANCILLARY encountered 4 errors and 5 warnina s Figure 77 Errors found in the suite validation A more detailed log of the error can be viewed in the Replication Monitor Error Log refer to sections 7 3 2 and 7 3 6 for further information A suite s is invalid if the tables that makeup that suite are invalid The main reasons have been outlined 1 section 6 4 2 4 MGA Duplica Version 3 01 USING REPLICATION MANAGER 76 6 5 Scheduling Suites for Automated Replication 6 5 1 Schedule Suites for Replication Highlight the suites that you want to schedule for replication e Click the Schedule button or e Right mouse click gt click Schedule Suite The button sets the suite s to a scheduled or ready state If the process of replication is turned on suites the scheduled state will begin replicating Their state will then change to running
73. e user can specify whether they want the validation tests to be reported as errors or warnings validation error and warning messages are sent to the Error Log Table and can be view via the Replication Monitor 4 9 1 How to Open Table Validation The Table Validation can be opened via the Setup button Figure 44 or drop down menu Figure 45 MGA Duplica Version 3 01 INSTALLATION 48 Fil Setup Tools Help New Repository Owner Source Database Updating Tables Duplica Settings Duplica Repository Table Validation Figure 44 How to open Table Validation via the Setup button Is Duplica REPOWNER rtest File Setup Tools Help New Repository Owner Source Database Updating Tables Duplica Settings Duplica Repository Table Validation Figure 45 How to open Table Validation via the main menu Setup option Note Once the Master Table Validation Settings have been configured they can also be edited in Replication Manager refer to section 6 4 2 2 The Validation window has three tab options e Server Table Local Table e Tables to Apply The information 1n each of these tab options is described below 4 9 2 Server Table Validation Server table validation can be performed on two distinct categories of tables Common Data Tables MGA Duplica Version 3 01 INSTALLATION 49 Delete Control Tables Refer to Figure 46 The common
74. ect Privileges Figure 6 Roles and Privileges of a new user created in Duplica called REPOWNER Note that there are no Object Privileges If you have already created a user before instalhng Duplica make sure that the roles and privileges are the same as shown above MGA Duplica Version 3 01 INSTALLATION 27 4 4 2 How to Create a New User from the Duplica Logon Prompt 4 4 2 1 Duplica Logon MGA Duplica Logon x MGA DUPLIGA Recent Connections User Server User Password Database Create New User cont Mark Gurry amp Associates Figure 7 Duplica logon window highlighting the New button To create a new user click on the Create New User button from the logon prompt A database administrator DBA logon window will open You do not need to login to Duplica as a system or sys user to create new users In the process of creating a new user you will be prompted for DBA system or sys logon information 4 4 2 2 DBA Logon Only the database administrator can create a new user As shown in Figure 8 the DBA will be prompted to enter a system user name password and the database that you want to create a new user for DEA Logon X Paszwnrd um Cancel d Database rest must log inta the database as a database administrator Figure 8 DBA logon prompt to create a new user account for
75. ect an option belo f Install First time installation Upgrade Upgrade current installation Re Create Overwrite Drop and recreate objects Rebuild Packages Recreate just the Oracle Code C Just Database Monitor Views Create views needed for Monitor Tablespace for Duplica Objects USERS 38 658 MB free IND 51 192 free D S LG TAB 292 08 MB free O45 LG ID 383 352 MB free 213 212 MB LAS 1428 784 MB free 5 SM TAB 409 592 MB free Figure 17 Duplica Repository Set up Window Click on the install option Remember to select the tablespace and estimate the database size The Duplica repository is commonly set up in the users tablespace The database size options you may choose are 4 10 and 21 Gigabytes The next release of Duplica will offer more flexibility MGA recommend that the Duplica Repository should not be too big Once you press next you will be prompted to login as an Oracle SYS user go to section 4 5 7 4 5 3 The Upgrade Option The upgrade option allows the Repository structure and backend code to be upgraded whilst maintaining your existing set up i e the suites that you have set up Note When you start a new version or new release of Duplica it will find whether you need to upgrade the Duplica Repository and will display an information message before opening the Duplica Repository window When new releases of Duplica are se
76. ed for maintaining a standby database Backups generally occur on a daily weekly or monthly basis Large backups that may take several hours can be avoided because Duplica is constantly replicating any changes that are made to the source database MGA Duplica Version 3 01 THE CONCEPT OF REPLICATION 15 2 4 Duplica Design and how it works Duplica is installed on the client and connects over SQL Net or Net 8 to manage replication from one schema to another Data replication can occur on the one or multiple Oracle databases which can be loaded on any hardware platform refer to Figure 1 To replicate data Duplica creates a small number of database objects in its own schema the Duplica repository Data Data Data Control Control Data Data Control Data Client PC with sd DUPLICA front end wa _ Repository Control ee Data RA Doo DUPLICA back end Figure 1 Summary diagram that illustrates the concept of Replication 2 4 1 The Graphical User Interface front end The Duplica GUI makes the job of replicating data more user friendly and is built using Delphi It allows the user to control the replication of data Duplica consists of two main tools Replication Manager and Replication Monitor MGA Duplica Version 3 01 THE CONCEPT OF REPLICATION 16 Replication Manager allows you to create you own suites and add tables to these suites depending on the nature of the source database You can
77. eplication Selected Suites All Scheduled Suites SCHEDULE Un schedule suite s for replication Selected Suites All Scheduled Suites UNSCHEDULE Manual Operation m MANUAL OPERATIO 5 31 Download selected suites or all Selected Suites scheduled suites All Scheduled Suites MGA recommend that you do not use the All Scheduled Suites option unless you are running it overnight Update the values in Local CNT and Selected Suites Server CNT for the selected suite s All Scheduled Suites MGA recommend that you do not use the All Scheduled Suites option COUNT ROWS Miscellaneous MGA Duplica Version 3 01 USING REPLICATION MANAGER 5 8 Print a report This report will include Opens a window where the all the suites that are currently in the user can select columns to suite list The user can choose which include in the report options they want to include in the report The report must be previewed before you can print Filter the number of suites displayed in All Suites the Replication Manager suite list with All Scheduled Suites FILTER respect to the available options All Mot Scheduled Suites All Stopped Items with Interval that contain local table like that use server table like with tables in tablespace Suite Mame Like Schema Like Table 2 Replication Manager functionality 6 3 2 Suites Window The Replication Manager screen lists all sui
78. eplication errors occur 4 8 1 How to start Duplica Settings The Duplica Settings GUI is found by using the Setup button or main menu Setup option Figure 40 and Figure 41 me Duplica REPOWNER amp rtest File Setup Tools Help New Repository Owner Source Database Updating Tables Duplica Settings Duplica Repository Table Validation Figure 40 How to start Duplica Settings MGA Duplica e Version 3 01 INSTALLATION 45 Duplica REPOWNER rtes Fil Setup Tools Help Mew Repository Owner Source Database Updating Tables Duplica Settings Duplica Repository Table Validation Figure 41 How to start Duplica Settings alternative 4 8 2 Changing the Duplica Settings The Duplica Settings options are described below refer to Figure 42 4 8 2 1 Startup Options e Start Replication Monitor not available in version 2 4 e Start Replication Manager not available in version 2 4 e Start Database Monitor Audit Records The database monitor 15 responsible for checking the health of the database and see if any objects need to be attended to Note Turning off this option does not disable all the functionality in the Replication Monitor As mentioned in section 2 4 4 2 the processes check Ifa table is badly fragmented and has chained rows therefore check the pctused and pctfree definitions for the table If initial and next extent
79. evious installation of Duplica then the Connection drop down list box will contain logon information You can use the connections drop down list box to quickly logon If you select a connection then all you will need to do is enter the password to logon MGA Duplica Logon x MGA DUPLIGA da Recent Connections Leer Server Uzer m UserName REPIWNER 00 Password Database Create New User amp Associates Figure 5 Duplica Logon Window If you have previously created users on the target database and have chosen one of those users to hold the Duplica Repository then logon to Duplica as that user Otherwise create new user by clicking on the Create New User button MGA Duplica Version 3 01 INSTALLATION 26 4 4 Create New Users 4 4 1 Duplica User Roles and Privileges As discussed in section 3 1 is recommended that there is a user especially for the Duplica Repository and a user s for the replicated data When new users are created in Duplica they have the following roles and privileges See Figure 6 v 5 REPOWMER 5 9 Granted Roles 2 Sh CONNECT RESOURCE Bef System Privileges 10 000 c Efl CREATE DATABASE LINK gil CREATE PROCEDURE gif CREATE SEQUENCE DELETE ANY TABLE E DROP ANY TABLE EXECUTE ANY PROCEDURE Ej INSERT ANY TABLE SELECT ANY TABLE UNLIMITED TABLESPACE UPDATE ANY TABLE m Obj
80. fully developed Figure 115 At this stage it points you to the About option displays some information about the version of the software currently installed on your PC Figure 116 MGA Duplica REPOWNER rtest File Setup Tools Help Figure 115 Help Function Options About MGA Duplica MGA Duplica Replication For vvindows MT plattarm 3 0 1 13 Build Mark Gurry amp Associates Copyright 2001 Mark Gurry amp Associates All nights reserved Figure 116 Information displayed when the About option is chosen MGA Duplica Version 3 01 CLOSING DUPLICA 114 9 Closing Duplica 9 1 Via the Main Menu Duplica can be closed using the main menu Figure 117 File gt Exit Duplica REPOWNER amp rEesE File Setup Tools Help Figure 117 Using the main menu to exit Duplica 9 2 Using the Shortcut Key Duplica can be closed using a built in shortcut key Ctrl X 9 3 Using Icon Duplica can be closed using the standard X icon in the top right corner of the main Duplica window MGA Duplica Version 3 01 TROUBLE SHOOTING 115 10 Trouble shooting Problem Definition Refetch Refetch errors can occur if the location of the Duplica folder and sub folders on your PC is too low level For example The following Duplica home directory is too long C Projects Duplica MGA Duplica Test Production Output MGA Duplica Version 3 01 GLOSSA
81. get users created All target users have tables created e Tables to be replicated should have an identical structure to the Source table The source databases are commonly so large that they would normally reside on a server of some kind In most cases the data is replicated to a target database that is located on a different server Only the database administrator should be responsible for creating a database on your local server s 3 3 1 Rules and Restrictions Table name must be unique within the first 24 characters All tables nominated for replication must have a timestamp column and an index on this column If timestamp information is not available triggers must be created to add time stamp information Alternatively you can use the flagging mechanism to track the replicated records All tables nominated for Insert Update replication must have a primary key Tables nominated for Delete must have a trigger MGA Duplica Version 3 01 INSTALLATION 23 4 Installation 4 1 Setup the Client PC 4 1 1 Update the tnsnames ora file The tnsnames ora file on the PC where you will be installing Duplica must have database alias entries for e The primary source database The target local database The alias entries in the tnsnames ora on your PC running as the Client need to be identical to the alias entries in the tnsnames ora for the Server s where the target and source databases are located
82. he target table Suite The suite that the table belongs too DB Link The database link that is being used to transfer data Last Changed The name of the field that 1s being used to determine Table 11 Run time Table statistics The following table describes the options that are available from the icons above the Tables window Table 12 Update table row counts for the table that is currently highlighted A popup window will ask you to confirm that you want to update the row count af Add table to a suite There are various table settings that you need to specify F Remove table from a suite Table settings You can edit the table settings that you specified when you first added the table to the suite Validate the table s that are highlighted Table 12 Tables view icon descriptions The bottom section of the Tables tab is a preview of the Performance Suite Graph This chart displays the performance details for the selected table You can change the display as follows e To sort runs by number of rows processed click the Rows Processed radio button and click the Refresh Graph icon see only the last twenty runs click the Filter Data icon select Last 20 Runs from the drop down menu and click the Refresh Graph icon The following table describes the functionality of the icons that are located above the Performance Graph Preview window Table 13 MGA Duplica Version 3 01 USIN
83. how Duplica works The pre installation requirements that need to be checked such as minimum hardware and software requirements at the client and server end and source and target database requirements are discussed in section 3 Section 4 details the installation of Duplica It shows you how to set up the client PC logon to Duplica then use Duplica to create new users and set up install upgrade etc the Duplica Repository It also discusses using Duplica to create edit and delete database links and how to update the source and database tables Section 5 is a brief section that explains the Replication Control Mechanism The overall data replication process can be controlled via an on and off button on the Duplica main menu In Duplica data replication 1s largely controlled by e Replication Manager e Replication Monitor These are discussed in sections 6 and 7 respectively Replication Manager is where you can create suites groups of tables with similar replication requirements and control the replication process To do this you must how to define your suites set up the suites edit the settings and schedule the suites for automated replication or manual replication Replication Monitor is used to monitor run time replication and the resources of the target database It is basically a series of views that portray the state of the database and the performance of data replication The Duplica Help section is not developed in this versio
84. job Submit a job for each SCHEDULED suite Reschedule job if they re in the backup period e Change the status to RUNNING for each scheduled suite 2 4 4 2 Monitor Database The monitor database process checks the health of the database and checks to see if any objects need to be attended to It informs 1f tables need to be rebuilt or if objects may crash The Monitor Database process can be turned on or off when you setup Duplica The Monitor Database process specifically checks Ifa table is badly fragmented and has chained rows therefore check pctused and pctfree definitions for the table e If initial and next extents need to be increased e Ifa tablespace has less than 10 free space therefore add a data file If an object cannot create next extent and therefore it is necessary to increase the tablespace size or decrease next extent 2 4 4 3 Housekeeping Housekeeping is run once a day at 4 00 AM The housekeeping process removes old log records from the database MGA Duplica Version 3 01 THE CONCEPT OF REPLICATION 19 e Replication errors are deleted if they are seven days old and have not been logged If they were logged then they are deleted if they are two days old e An audit log is deleted if it is three days old A database error is deleted if it is two days old Both suite performance and table performance data is deleted after 10 days MGA Duplica Version 3 01 L0
85. ll paused and scheduled jobs will start automatically All stopped jobs need to be restarted Starting the replication control mechanism starts all the scheduled and paused replication jobs All stopped jobs will need to be restarted if you want them to replicate The replication process 18 programmed to start every 2 minutes When the control mechanism is running you will see the Duplica icon and a yellow arrow in the top right hand corner of your screen moving continuously There is no need to stop Replication control mechanism if you are making changes to the run time settings for a suite Ies MGA Duplica REPOWNER rtest Setup Tools Help REPLICATION SETUP MANAGE MONITOR START ror EE Figure 51 Turn off the Replication Control Mechanism The stop button will stop the Replication control mechanism Figure 51 If you stop the control mechanism the status of all running and scheduled jobs will automatically change to paused stopped jobs stay stopped MGA Duplica Version 3 01 USING REPLICATION MANAGER 54 6 Using Replication Manager 6 1 How to Define your Suites Replication Manager is where you can create suites groups of tables with similar replication requirements and control the replication process The replication settings for each suite can be unique You can also choose to start or stop replication process on a suite level o
86. lso be opened from the Tools drop down list Figure 54 MGA Duplica REPOWNER rtest Setup Tools Help Replication Manager Replication Monitor MANAGE MONITOR START Figure 54 How to open Replication Manager from the Tools option 6 3 The Replication Manager GUI When the Replication Manager window is first opened you will notice that not all the buttons are accessible Figure 55 These buttons become accessible when a suite name is highlighted from the suites listed in the window Figure 56 A suite is a group of tables that will be replicated The tables are grouped together because they are intimately related i e parent child table relationships through primary keys and foreign keys or the tables are required as a group for further processing by the employees at the target database MGA Duplica e Version 3 01 USING REPLICATION MANAGER 5 6 Replication Manager r SUITE MANAGEMENT 4 L SUITE SCHEDULING MANUAL OPERATIONS 4 GE fg w UNSCHEDULE ANCILLARY REPO NER 105 STOPPED COMPANY REPU NER TM STOPPED LINK_TO_OASP EQUIPMENT REFO WNER 1M STOPPED LINKB_TO_OASP USERS REPO NER 1M STOPPED LINK_TO_OASP r SUITE MANAGEMENT 4 SUITE SCHEDULING _ MANUAL OPERATIONS Active Filter All Suites ANCILLARY
87. m the replicated data which could be associated with one or more users refer to Figure 2 and Table 1 System user Target data user e g USERS e g OAS TABLESPACE Sys user TABLESPACE Figure 2 Diagram showing the recommended Duplica schema set up on the target database If necessary the users can be created from the Duplica logon prompt or internally see section 4 3 MGA Duplica Version 3 01 THE CONCEPT OF REPLICATION 17 Target Owner of Owner of Login Required Duplica Replicated Objects Data Schema Duplica Owner Co ordinates e g repowner Duplica User B e g No unless truncate and copy Data Repository oas md tab No only within Duplica if prompted E g When creating a new user No only within Duplica if prompted E g When installing the Duplica Repository Table 1 Description of the schemas used by Duplica User B system and sys users should not be used to login to Duplica from the prompt that 1s when you first start Duplica 2 4 3 The Duplica Repository back end The Duplica Repository owns all the replication tables packages triggers etc associated with running the software The Duplica Repository i e Duplica s schema is only created at the target database and can be named whatever you like e g Repowner The advantages of a Duplica central Repository are central copy of Duplica can control multiple schemas e Simpler maintenance in comp
88. mance views show the performance statistics for the most recent run for each suite that has been replicated Shows performance statistics for the most recent run for each table The Latest Run Logs views are an Audit log which displays all replication events that have taken place since the last time the table was purged deleted and an Error log which displays a complete list of the replication errors The Tablespace Checks view contains four sub views The Tablespaces view shows the amount of free space in each of the tablespaces in the database The Free Space Graph illustrates the data displayed in the Tablespaces view The Objects About to Crash view shows the objects which are having trouble creating a next extent The Tables To Rebuild shows the tables which require indexing The Replication Audit Log view shows displays all replication events that have taken place The Replication Error Log view shows all the errors that have occurred during the replication process 7 2 How to Open Replication Monitor There are three ways to open the Replication Monitor e Using the Monitor button e Using Tools gt Replication Monitor e CTRLO The location of the Monitor button 18 shown in Figure 98 MGA Duplica Version 3 01 USING REPLICATION MONITOR 97 MGA Duplica REPOWNER amp rtest Setup Tools Help REPLICATION Click 8 SETUP MANAGE START STOP Start Replication Monitor Figure 98 How to open Replication M
89. n section 8 It 1s possible to find out the version of Duplica you have installed on your PC using the Help function Section 9 shows you how to exit Duplica Troubleshooting section 10 is a log of problems that you may encounter whilst using Duplica Possible solutions to these problems are listed and expanded upon A Glossary of Terms can be located at the back of this manual MGA Duplica Version 3 01 THE CONCEPT OF REPLICATION 13 2 The Concept of Replication 2 1 History The concept of replication was born through the need for some companies to have a copy of another database that is up to date and fully functional This is a common occurrence for companies that perform 24 x 7 business activities and rely on having up to the minute data so that informed decisions can be made and ultimately so that the business can run smoothly These amount of data generated within these types of businesses is enormous thus their database s are constantly updating deleting or inserting data 2 2 What is Data Replication If changes are made to data in a source database such as adding a new record deleting a record or updating a record then Duplica will mirror these changes in a target database within a specified timeframe by copying the changes from the source to the target 1n effect creating a replica of the source database 2 3 Replication Scenarios 2 3 1 Electricity Market Model The National Electricity Market NEM is th
90. nged Query Field does not exist 0 ANCILLARY ANCILLARY SERVICE LIVE 1088 20 09 2001 11 58 32 ERROR Primary Key does not exists or is disabled 0 ANCILLARY ANCILLARY SERVICE LIVE 1083 20 03 2001 11 58 32 ERROR Index on Last Changed Query Field does not exist EB d Live Database amp General Replica Errors JV Auto Refresh Play Alarm Type Recommendation Figure 101 Replication Monitor Errors view after refresh The following table describes the function of the icons displayed above both the Live Replication Errors and Live Database and General Replication Errors views Table 21 MGA Duplica e Version 3 01 USING REPLICATION MONITOR 99 Log Error Changes the LIVE status to LOGGED Manual Refresh In the current version of Duplica a refresh is required after you use the Delete button Delete all log entries When you press this button you are prompted with a choice to delete all log entries or those older than 5 days W Auto Refresh Allow the view to be refreshed at the time interval specified in the Auto Refresh central control jw Play Alarm You can play a sound if Table 21 Errors View Icon Descriptions 7 3 2 1 Replication Errors Live Replication errors are related to the replication process and are stored in REPLICATION ERRORS table on the local database The following table describes the information displayed for Replication errors Table 22 ee St
91. ngs Refer to section 4 9 for further information regarding the validation settings MGA Duplica Version 3 01 USING REPLICATION MANAGER 70 Table Settings X Table Settings Validation Settings Server Table Settings Validation for Inger Update Delete Operation Last Changes Query Field settings and properties Error C Warning Friman Key existence and properties f Warning Index on Last Changed Query Field Error f Warning Insert Update trigger properties Eror Waring Validation for Delete Control abl vw Delete Control T able settings Eror Warming Delete Trigger properties f Warning v Column Settings Ermo Warning Friman Key existence and properties f Warning Target Table Settings Validation for Insert 7 Update Operations Last Changed Query Field settings and properties C Warning v Primary Key existence and properties Eror C Warming Iv Indes on Last Changed Query Field Eror warning Figure 69 Validation Settings Tab 6 4 2 3 Delete Tables from Suites To delete tables from a suite highlight the table name from the Suite Information Table Tab window and click the Delete table s from a suite icon You will be prompted to confirm the deletion Figure 70 MGA Duplica Version 3 01 USING REPLICATION MANAGER 71 x 2 Do
92. nt to the Client the email will commonly consist of e Updated SQL Files e Updated Duplica exe e New system files if necessary An upgrade creates the objects that makeup the new release and then applies the scripts that were saved Choose the upgrade option then select the tablespace name and size Click next MGA Duplica e Version 3 01 INSTALLATION 33 Go to section 4 5 7 4 5 4 The Re create Overwrite Objects Option This process drops all the existing Duplica objects and recreates new ones Go to section 4 5 7 4 5 5 The Rebuild Oracle Packages Option This process rebuilds the Duplica back end packages only Go to section 4 5 7 4 5 6 The Just Database Monitor Views Option This process recreates the Duplica views only which are used by the front end This process usually goes hand in hand with the upgrade 4 5 7 Oracle Logon x Username Password a Database test Figure 18 Oracle Logon prompt You must logon as a SYS user to finish the Duplica Repository set up Once you press OK Duplica automatically loads the objects required to run Duplica This completes the creation of the objects for the Duplica Repository or Duplica Owner schema The following window should pop up when the sql scripts are being run Figure 19 and successful completion will be acknowledged by a popup wind
93. o Database Link to the source schema No Primary Key e No Index on the Last Changed Query Field No Last Changed Query Field No delete table specified if Table settings delete field is ticked 6 4 3 How to Edit the Suite Settings For each suite there are several parameters that need to be set before replication will take place The Replication Interval is the most important of these The Suite settings define e How frequently the suite will be replicated e The maximum number of rows to fetch in any run e Values you can use to monitor performance such as maximum query time The suite settings window can be opened by e Ensure you are in the Replication Manager window e Double click on the suite to be changed MGA Duplica Version 3 01 USING REPLICATION MANAGER 73 e Highlight the suite to be changed gt Click the Settings Button from the Replication Manager window Option 1 and 2 both open the Suite Settings window shown in Figure 74 5 chedule Item Sute ANCILLARY Status HEW Replication Interva Interval TimeOut Factor Nest Ru Date and Time hh mm ss PM 2 ee Clear Query Tim Interval Minutes Haw Expectation Rows Expected Minimum Expected Limit amount of table rows to fetch per ru rows for each table Database Links Source LINK T _ 5 Source Backup Target Schema
94. o on This process will continue depending on how many new tables you have selected to update Version 3 01 INSTALLATION 42 4 7 3 Run SQL Scripts Duplica does not execute the generated scripts automatically Users are advised to review them make changes as they see fit then execute them Before you run the scripts you must login as the source schema to run the source scripts that Duplica generated Thus you must login as the target schema to run the target scripts that Duplica generated 4 7 4 Using the Filter Option The filter option can be used to quicken the process of adding an extra column to the required table s by allowing you to filter the list of replicated tables by table name like table name or by the name of the last updated column within each table Figure 86 Filter By x Table Hame Example Fre PRE Fre Prez Preorder DATE_UPDATED Add Delete TIME LAST LIPDATE Select All eed Figure 36 Filter By GUI If you know the table name that you want to update enter the name into the caption If you want the filter to return several tables with similar names then enter the first few letters of the table name then followed by the character The Columns option contains a list of the field names that are used by the tables in the database for holding date type data The date field 1s used to indicate when a record was last changed This column can be used
95. on Manager It shows the suite name replication interval last run number and the date and time of the next scheduled run les Replication Monitor Auto Refreshing every 10 Seconds Last Run Mumber Next Run Date Time COMPANY EQUIPMENT USERS Figure 102 Suites Monitor View Defaulted to the Scheduled to run next tab The following table describes the information displayed in the window Table 25 The run number when this suite was last replicated The name of the suite Version 3 01 MGA Duplica USING REPLICATION MONITOR 101 Interval Unit The frequency with which this suite is replicated Unit The Unit field tells you what the interval is measured in The units of measurement are D Day H Hour M Minutes 5 Seconds Manual Refresh In the current version of Duplica a refresh is required after you use the Delete button Auto Refresh Allow the view to be refreshed at the time interval specified in the Auto Refresh central control Table 25 Available icons and filed descriptions for the Scheduled to Run Next view The date and time the run 15 scheduled for 7 3 3 2 Currently Running The Currently Running view is shown below Figure 103 This view shows some runtime statistics on suites that are currently running Eephcation Monitor Auto Refreshing every 10 zi Seconds Eos Suites Monitor Tablespace Checks Repicaon Aut Log Replication Enor Log Scheduled ta run next C
96. on Manager main window click the Print button This will open the Printing Report window Figure 93 Select the columns to display then click the Preview button to see the Report x Report Title jal Selected Scheduled Suites Select columns to display C NEXT RUM CI INTERVAL Printer Setup C INTERVAL UNIT C JOB STATUIS Fonts O START_TIME END TIME Select All TIME INTERVAL ROWS EXPECTED ROWS MIN EXPELTED TABLE REFRESH TIME RELDORD x LIE IE TETE 5 L Figure 93 Example Printing Report Window The functionality of each buttons and tick box option is summarised in the following table Table 18 and described in next few sections MGA Duplica Version 3 01 USING REPLICATION MANAGER 92 omm Preview Preview of the Report You must preview a report before you can print it Cancel Cancel and return back to the previous window Printer Set up Change the default printer settings Cannot print from this window Fonts Change the font font size etc Select All Select all the columns to be in the report Table 18 Print Options 6 9 1 Print Preview Options The Print Preview options are shown below Figure 94 and their meaning 1s summarised in Table 19 2 All Selected Scheduled Suites Figure 94 Print Preview Options option Desens SS
97. onitor The Replication Monitor can also be opened from the Tools drop down list Figure 99 Ins MGA Duplica REPOWNER rtest Setup Tools Help Figure 99 How to open Replication Monitor using the Tools option The Replication Monitor default window will display the suites that are currently being run Thus if replication is turned off or there are no suites scheduled for replication then this window will be empty Figure 100 Eephcation Monitor Auto Refreshing every 10 Seconds Suites Monitor Tablespace Checks Replication Audit og Replication Log Scheduled to run next Currently Running Latest Run Performance Latest Run Logs oe E Auto Refresh Current Run Number 5 ANCILLARY 14 35 18 Figure 100 Replication Monitor Window Default view Currently running jobs MGA Duplica Version 3 01 USING REPLICATION MONITOR 98 7 3 Replication Monitor Views 7 3 1 Auto Refresh Central Control The auto refresh central control tick box is located in the top left corner of the Replication Monitor window Choose a time interval You must have this box ticked to start the overall Replication Monitor auto refresh process Note In all the Replication Monitor views there is a tick box options for auto refresh So when the main auto refresh box is ticked the auto refresh will only occur for the various views have been ticked 7
98. onitor Database 18 prm ee Mis uS 18 3 PREJNSTALDLDATIONS ee 21 3 1 HARDWARE REQUIREMENTS 21 32d SOFTWARE REOUIREMENTS CORE PA 3 2 1 Bc IE e 21 322 21 3 3 SOURCE AND TARGET DATABASE REQUIREMENTS ccccssccssccssccsccescesccesccescenscesccessenssesceusseseeceues 22 3 3 1 CNG AT III 22 23 4 1 25 4 1 1 RR Rm 23 4 1 2 23 4 2 assist T 24 4 3 TONE PA 25 CREATE NEVU ER iine 26 4 4 1 Duplica User Roles and PIV Ut aos auus 26 MGA Duplica e Version 3 01 CONTENTS 3 4 4 2 How to Create a New User from the Duplica Logon Prompt eese 27 4421 Dp e 27 DBA TG ODE T UU MM 27 4 4 3 How to Create a New User from within Duplica csse 26 4 4 4 Enere NCW DI CLAUS 28 4 5 SETTING UP THE DUPLICA REPOSITORY oseyen ers
99. ons that are available when using the right mouse button Figure 59 Replication Manager MODIFY Target Schema ANCILLARY COMPANY Add Suite STOI EQUIPMEN Modify Suite STOI USERS Delete Suite sTul Tables Schedule Suite Un Schedule Suite Refetch Data Update Row Count Statistics Figure 59 Create a new suite by right mouse clicking on a suite then click Add Suite The following window will appear Figure 60 RS Add Suite Suite Source Database Link Cancel LINE 45 Backup Database Link Target Schema REPOWNER Figure 60 Add Suite Window Enter a suite name then choose the source database link backup database link if applicable and target schema from the drop down lists for each option You must choose enter a main data source when you set up the suites MGA Duplica e Version 3 01 USING REPLICATION MANAGER 6 1 The target schema is a user that resides on the target database that is used to replicate data to tables Press OK The new suite will appear in the Replication Manager Suite window with a Status equal to NEW Figure 61 Replication Manager Source ANCILLARY REPD WNER SCHEDLL D amp SP New Suites have COMPANY REPD WNER 1M STOPPED TO DASP NEW status and EQUIPMENT REPOWNER 1M STOPP LINK DASP EXTERNAL USE LEIGHG 1M NEW DB LINK ro
100. ow Figure 20 MGA Duplica Version 3 01 INSTALLATION 34 Repository Setup Duplica Repository Setup Running APPLY VIEWS SUL Hill Total Progress rr Figure 19 Pop up window to indicate sql files being run Information d Repository Setup Complete Figure 20 Confirmation that the Duplica Repository set up is complete MGA Duplica Version 3 01 INSTALLATION 35 4 6 Creating Editing and Deleting Database Links 4 6 1 How to Create a Database Link to Source Duplica requires a database link to read from the source database A database link should be created between the Duplica owner at the target database and the user schema at the source database who owns the data to be replicated If these were not already created you can create them using Duplica Figure 21 and Figure 22 IS MGA Duplica REPOWNER rtest File Setup Tools Help New Repository Source Database Edit Delete Updating Tables Duplica Settings Duplica Repository Table validation Figure 21 Location of Add Source Database option Duplica REPOWNERG rtest Setup Tools Help REPLICATION MANAGE MONITOR STAR Mew Repository Owner Source Database Updating Tables Edit Delete Duplica Settings Duplica Repository Table Validation Figure 22 Alternative location of Add
101. pt for both the source and target databases The source sql is generated before the target sql The sql script created for the source is different to sql script created for the target because the source may contain triggers The target database does not need triggers as each table is populated directly from its corresponding primary source table After you press save a popup window will appear which lets you know if the DDL was created successfully Figure 34 x i Replication Log DDL successfully created Output to File CHyClients DuplicatREPLOG DDL 20030607 124424 sq Figure 34 Popup to acknowledge that the DDL was created successfully in the directory as shown Once you press OK Duplica will then create the sql script for the creation of a new column at the target database Save Update Table DDL Script File _ Save Scripts E SOURCE Alter Table 20010625 127150 sql SOURCE Alter Table 20010625 122759 sg Ex TARGET Alter Table 20010825 122214 54 File name STE SHEL Save as soL Script Files Cancel 7 Figure 35 Save SQL Window Note the default file name contains the word TARGET Press Save If the DDL was created successfully you will see an acknowledgement popup window similar to Figure 35 Once the DDL has been created for the first table you wanted to update it will then move to the next table that you requested and s
102. r is not enabled ANCILLARY ANCILLARY REQUEST 1101 20 09 2007 11 53 01 Index en Last Changed Query Field does not exist ANCILLARY ANCILLARY REQUEST 1100 20 03 2001 11 53 01 Primary Key does not exists or is disabled ANCILLARY ANCILLARY REBLUEST 1099 20 09 2007 11 53 01 Last Changed Query Field does nat exist 0 ANCILLARY ANCILLARY 1098 20 09 2001 11 58 39 INSERT tigger does not exist or is not enabled Replication Audit Log Replication Error Log Figure 114 Replication Error Log The following table describes the information displayed Table 36 eatin Status This is LIVE if the message will be deleted within seven days of it being generated or LOGGED 1f it will be deleted within two days of it being generated The message contents Manual Refresh In the current version of Duplica a refresh 1s required after you use the Delete button Delete all Log entries When you press this button you are prompted with a choice to delete all log entries or those older than 5 days MGA Duplica Version 3 01 USING REPLICATION MONITOR 112 Filter All Errors validation Errors All Replication Errors Errors Text Like J Auto Refresh Allow the view to be refreshed at the time interval specified in the Auto Refresh central control Table 36 Replication Error Log description MGA Duplica Version 3 01 USING HELP 113 8 Using Help The help function is not
103. r you can turn the replication process on or off for all the suites The tables that makeup the suites are generally determined by the Client The replication interval for each suite is determined by the data There are a number of criteria for deciding which tables make up a particular suite The relationships between tables parent child relationships Figure 52 The rate at which data is being added deleted or updated to tables Tables that are constantly being updated require much smaller replication intervals than tables that only get updated every now and then Company Suite Users Suite Company 4 Users Company_ Equipment Company Notify_URL AN ZIN Company_ Company_ User Filter User Role Stat MSG_ Notification c User_ Distro_ List Company_ Role Figure 52 Example Suite Groupings for Company related tables and User related tables MGA Duplica Version 3 01 USING REPLICATION MANAGER 55 6 2 How to Open Replication Manager There are three ways to open the Replication Manager e Using the Manage button e Using Tools gt Replication Manager CTRLM The location of the Manage button 1s shown in Figure 53 m Duplica REPOWSER rtest Setup Tools Help p Click START of OP Start Replication Manager Figure 53 How to open Replication Manager using the Manage button The Replication Manager can a
104. re 109 This view shows the amount of space available and the largest free extent for each tablespace on the Target database Replication Monitor a loj x Auto Refreshing every 10 zi Seconds Auto Refresh Largest Free Extent IM Dies 51132 51132 DAS Li ID 333352 42152 DAS Li TAB 338335 221584 HAS 1428080 911288 DAS MD TAB 269912 165904 DAS SM 409592 369512 SM 409592 141040 PSSINDO1 09504 465744 PSSINDO2 117416 04704 PSsINDOS 135720 47450 PSSTABOT 649176 453144 PSSTABU2 109288 pozde PSs TABUS 166016 97432 FBS 337912 10240 SYSTEM 423624 416160 TEMP 511992 409592 TOOLS Je332 99192 USERS 44888 44536 Figure 109 Tablespace Statistics for the Target database The following table describes the information displayed Table 32 So M Manual Refresh Auto Refresh Allow the view to be refreshed at the time interval specified in the Auto Refresh central control Table 32 Description of the information displayed in the Tablespace view Version 3 01 MGA Duplica USING REPLICATION MONITOR 108 7 3 4 2 Free Space Graph The Free Space Graph view is shown below Figure 110 The Free Space graph illustrates the amount of free space in each tablespace on the Target database Replication Monitor 1 SI Auto Refreshing every 10 zi Seconds Errors Suites Monitor Tablespace Checks Replication Audit Log Replication Error Log A
105. s button you are prompted with a choice to delete all log entries or those older than 5 days Auto Refresh Allow the view to be refreshed at the time interval specified in the Auto Refresh central control Table 29 Audit view description MGA Duplica Version 3 01 USING REPLICATION MONITOR 106 7 3 3 4 2 Error view The Error view is shown below Figure 108 Replication Monitor l ial xl Auto Refreshing every 0 Seconds Replication Error Log 4444244 424444 Scheduled ta run next Currently Running Latest Run Performance Latest Run Logs Audi Eror dl Auto Refresh Latest Run Replication Error Log amp NLILLARY SERVICE 1113 20 08 2001 14 74 10 ERROR Primary Kep does not exists or iz disabled QO ANCILLARY ANCILLARY SERVICE 20 09 2001 12 52 50 Primary Key does not exists or iz disabled AMCILLARY ANCILLARY SERVICE 20 09 2001 11 58 14 INSERT trigger does nat exist ar is not enabled AMCILLARY ANCILLARY SERVICE 2070972001 11 59 14 Index on Last Changed Query Field does not exist ANCILLARY ANCILLARY SERVICE 20 03 2001 11 59 14 Primary Key does not exists or is disabled AMCILLARY ANCILLARY SERVICE 20 09 2001 11 58 14 Last Changed Query Field does not exist AMCILLARY ANCILLARY SERVICE 2070972001 11 59 14 Primary Key does not exists or iz disabled ANCILLARY ANCILLARY REQUES
106. s need to be increased Ifa tablespace has less than 10 free space therefore add a data file Ifan object cannot create next extent and therefore it is necessary to increase the tablespace size or decrease next extent MGA Duplica Version 3 01 INSTALLATION 46 Duplica Settings E X Detail Startup Options Start Replication Manager Start Replication Monita Start Database Monitor M Log Audit Records Replication Sourc Activate Yalidatia M Backup Iv when Housekeeping runs with every Suite replication Replication Shutdown Date Time For Local DB Backup Shutdown on Shutdown Time HH MM Sunda ES z Tuesday wednesday Thursday Friday Saturday Error Sound Replication Error C Program Files Windows NT sPinbal 5S DEINDH Way Database Error Figure 42 Duplica Settings Window 4 8 2 2 Replication Source These options allow you to switch between the main database and the backup database if the main database is shutdown Duplica will use the backup database if 1t exists and has been configured as a replication source for a suite s However if a backup database has not been configured for a suite then when you switch to the backup database that particular suite will not receive replicated data You can only change the replication source when the Replication control mechanism has been turned off If you change the da
107. ssary then run the scripts The first stage is done in Duplica The second stage can be completed using SQLPLUS logged on as either the source or target schema You must login as the source schema to run the source scripts that Duplica generated You must login as the target schema to run the target scripts that Duplica generated 4 7 1 How to start Update Tables The Update Tables GUI is found by using the Set up button or main menu Set up option Figure 29 and Figure 30 MGA Duplica REPOWNER rtest Setup Tools Help Mew Repository Owner Source Database Updating Tables Duplica Settings Duplica Repository Table Validation Figure 29 How to start Update Tables MGA Duplica Version 3 01 INSTALLATION 39 ms Duplica REPOWNER rtest Setup Tools Help New Repository Owner Source Database Updating Tables Duplica Settings Duplica Repository Table Validation Figure 30 How to start Update Tables alternative Once the Update Tables window opens Figure 31 select the tables that you wish to update You can select all or use the Filter option to shorten the list of tables Update Tables Total 4 E g Filter ANCILLARY ANCILLARY REQUIREMENTS ANCILLARY ANCILLARY_SERVICE Generate DDL ANCILLARY ANCILLARY SERVICE TYPE Cancel Select All Mew Calumn CreationModitied Date A
108. t 1s currently the table and gets all rows with a date within the date range you specify Run row count check after Tick this option if you want the Duplica to compare the refetch number of rows on the local database with the number of rows on the source database From Date amp To Date If you choose Date Range Refetch or Clean amp Data Range Refetch enter the From and To Dates Table 16 Refetch Options Table 17 shows the respective popup message that appears once you have chosen a refresh type MGA Duplica Version 3 01 USING REPLICATION MANAGER 89 Refetch Option Confirm Popup Message Clean amp Full Refetch x 1 Truncating all suite tables 2 Copying suite data Fram the server 2 This option will affect all selected suiteis by Do vou wank Ea continue Last Changed x e This option will affect all scheduled suites by 1 Fetching all new data From the server Do vou wank continues Date Range Refetch x This option will affect all scheduled suites by 1 Fetching all new data From the server Do vou wank Ea continues Clean amp Date Range Refetch x 2 This option will affect all selected suites bv 1 Truncating all suite tables 2 Fetching all new data From the server Do vou wank bo continue Table 17 Popup Messages after you have chosen a Refetch Option If you continue with the refetch Duplica will prompt you for a password Figure 90 After the password Figure
109. t B ud Query Time Rows Processed Time Rows Processed Rows Processed 0 2 000 4 000 6 000 000 10 000 12 000 14 000 Query Time Seconds Figure 84 Performance Suite Graph view The icons and options displayed in the above window are described in the following table Table 9 Print Opens the Printing Report Window Refresh Graph Filter Data MGA Duplica Version 3 01 USING REPLICATION MANAGER 8 2 meme SSCS Query Time The number of seconds it took to complete the replication of the suite Rows Processed By default the chart 1s sorted by query time The run number at the top of the chart 1s the one that took the least time Table 9 Performance Suite Graph icon options To sort the runs in order of the number of rows processed click Rows Processed then click Refresh To see only the last 20 runs click the Filter button and choose Last 20 Runs then click Refresh 6 6 2 2 Tables The Tables tab is shown below Figure 85 Suite Information x 45 Suite ANCILLARY Job Status PALISED Logs Performance Settings Tables Suite Graph Tables Table Name Query Time Seconds Server Table 698 ANCILLARY REQUEST 698 ANCILLARY _ REQUIREMENTS 596 ANCILLARY SERVICE 698 ANCILLAR Y_SERVICE_TY PE 694 AMCILLAR Y REHLIEST 694 REHUIREMENTS 694 AMCILLAR Y SERVICE 694 SERVICE TYPE 690 AMCILLAR Y REHLIEST 69
110. t tab displays events logged during the Replication process Audit messages are stored in the REPLICATION AUDIT table on the local database The following table explains the information displayed Table 14 ooo MGA Duplica Version 3 01 USING REPLICATION MANAGER 86 Run The run number during which the message was generated Table Name The table being replicated when the message was generated Time The time that the message was generated Text The message contents Table 14 Summary of the Audit Tab Contents 6 6 4 2 Error Tab The Error tab is shown below Figure 88 Suite Information x Suite Name ANCILLARY Job Status PAUSE Logs Performance Settings Tables Audit Enor 1113 20 08 2001 14 14 10 M AMCILLARY SERVICE TYPE ERROR Primary Kev does not exists or is disabled 20 09 2001 12 52 50 ANCILLARY SERVICE TYPE ERROR Primary does not exists or iz disabled 20 09 2001 11 53 14 SERVICE TYPE ERROR INSERT trigger does not exist or is not enabled 20 09 2001 11 53 14 SERVICE TYPE ERROR Index an Last Changed Query Field does not exist 20 09 2001 11 53 14 ANCILLARY SERVICE TYPE ERROR Primary does not exists or iz disabled 20 08 2001 11 53 14 ANCILLARY SERVICE TYPE ERROR Last Changed Query Field does nat exist 20 08 2001 11 53 14 ANCILLARY SERVICE TYPE ERROR Primary Kep does not exists or iz disabled 20 08 2001 11 59 11 ANCILLARY ERR
111. ta source Duplica will prompt you to confirm that you want to proceed with the switch Figure 43 MGA Duplica Version 3 01 INSTALLATION 47 x Switch your replication source 22 FROM PRIMARY TO BACELIP Figure 43 Example of the popup window to confirm a database source change 4 8 2 3 Activate Validation Validation can be set to run when housekeeping runs 4 00 am daily with every suite replication on both occasions or not run at all MGA recommended that validation 1s performed with every suite run It is known that on some Oracle configuration querying the Oracle dictionary can result in performance degradation thus it is recommended that validation 1s performed manually to confirm that the setup 1s correct and then configure Duplica to validate whenever housekeeping runs Refer to section 2 4 4 3 for further information 4 8 2 4 Replication Shutdown Date Time for Local DB Backup The replication of data can be turned off for user specified periods of time This flexibility 1s included so that the local database can be backed up on a regular basis 4 8 2 5 Error Sounds P Click on the magnifying glass 1con to locate the appropriate WAV file The WAV file will sound whenever an error occurs 4 9 Configure the Table Validation Settings Table validation 1s required to ensure that both the source server database and the local target database are configured properly before the replication of data can occur Th
112. tes whether they are scheduled or not scheduled If you have just installed Duplica the suites window will be empty For each suite it displays the settings and some run time data as shown in Figure 57 V Active Filter All Suites PM EET Last Run Finish Local ENT Server CNT TO DL TM Figure 57 Suites List Columns The purpose and meaning of the icons and miscellaneous screen messages are Summarised in Table 3 Table 3 Suites List icon and miscellaneous message descriptions A description of the column headings and run time statistics 1s provided in Table 4 Suite Name The name of the suite about which the settings are displayed Only suites that have been selected for scheduling are displayed here Target Schema A user that resides on the target database that is used to replicate data to tables MGA Duplica Version 3 01 USING REPLICATION MANAGER 59 Interval The interval between Replications of the table in this suite Day Hour Minutes Seconds You can change the interval if the defaults are not suitable by editing the suite s settings Status The suite status will be SCHEDULED if this suite is scheduled for Replication 1 e the job that Replication Manager the suite has not been submitted to the Oracle Scheduling System This is the case if it has just been added to the list using the Add command or if after being previously stopped it been started again by
113. the Search button to find a specific table The following table summarises the Replication Settings options for a particular table Table 5 MGA Duplica Version 3 01 USING REPLICATION MANAGER 64 Scan Back The Scan Back interval allows for the situation Interval where changes may be made to a record in a database but are not committed for a period of time in which Duplica runs When a record is changed but not committed the changes are not visible to the Oracle session used by Duplica Thus the uncommitted change will not be replicated When the record 1s finally committed then when replication process runs again the record will not be replicated because its last updated timestamp for this record 1s before the time that has been recorded in suite table last changed field The scan back interval should be set to the appropriate time and not left blank Scan Back Unit Choose 5 seconds minutes hours Last Changed Query Field Every table at the source needs a last changed query field if it is going to be replicated The target database must be identical to the source The last changed query field is used by Duplica to determine whether at the source needs to be replicated to the target database Action Insert Only Set this option if the source data is not or cannot be updated Insert Update Set this option if the data in tables 1s updated and inserted Note This option is less efficient than the
114. tting up the Duplica Repository 4 5 1 How to Start the Duplica Repository The Duplica Repository set up wizard starts automatically if you are installing Duplica for the first time and have just created a new user When you set up the Duplica Repository you must choose the type of installation that you require If you are installing Duplica for the first time you should choose the install option In time once you have already loaded Duplica you will commonly be using the Upgrade Re create Overwrite or rebuild options see later sections The Duplica Repository can also be accessed from the Set up button on the screen or in the Duplica main menu Figure 15 or Figure 16 MGA Duplica Version 3 01 INSTALLATION Duplica REPOWNER rtest File Setup Tools Help New Repository Source Database b Updating Tables Duplica Settings Duplica Repository Table Validation Figure 15 How to start the Duplica Repository MGA Duplica REPOWNER amp rEesE Setup Tools Help Mew Repository Owner Source Database Updating Tables Duplica Settings Duplica Repository Table Validation Figure 16 Alternative way to start the Duplica Repository 4 5 2 The Install Option The Install option is for first time installation Figure 17 MGA Duplica 3 1 Version 3 01 INSTALLATION 32 Repository Setup Duplica Repository Setup Sel
115. urrently Running Latest Run Performance Latest Run Logs ow v Auto Refresh Current Run Number 405 ANCILLARY Figure 103 Currently Running view shows the suites that are currently running The following table describes the information displayed in the Currently Running view Table 26 T MGA Duplica Version 3 01 USING REPLICATION MONITOR 102 mem omem Manual Refresh In the current version of Duplica a refresh 1s required after you use the Delete button Auto Refresh Allow the view to be refreshed at the time interval specified in the Auto Refresh central control Table 26 Currently Running view Field Descriptions and icons Suites move from the Scheduled to run next view into the Currently Running view once the suite run begins i e the replication process begins 7 3 3 3 Latest Run Performance The table below describes the tab and icon options for the Latest Run Performance tab Table 27 Both the view default a view sorted by query time Suite Graph Shows the performance statistics for the most recent run for each suite that has been replicated Table Graph Shows performance statistics for the most recent run for each table Manual Refresh In the current version of Duplica a refresh is required after you use the Delete button Filter Al gt than Max Query Time than rows expected 10 Query Times Top 10 Rows Processed
116. uto Refresh Tablespace Free Space 49 990 m 209 9900485 LG IDX zd 262610045 TAB 1394 510055 IDX m 114 660PSSINDO2 1272530 PS SINDUS 105 3DIPSSTABU2 i 0 S Megabytes Figure 110 Free Space Graph 7 3 4 3 Objects About to Crash The Objects About to Crash view is shown below Figure 111 This view shows tables and indexes that are either running out of space in tablespace or reaching the extent limits Replication Monitor _ Auto Refreshing every 10 m Cem Errors Suites Monitor Tablespace Checks Replication Audit Log Replication Error Log ow Auto Refresh Objects that will have Trouble Throwing Extents Figure 111 Objects about to Crash View The following table describes the information displayed Table 88 e Object Name The name of the table or index which is having a problem MGA Duplica Version 3 01 USING REPLICATION MONITOR 109 RN This will be Table or Index The tablespace where the object is located Next Extent The size of the next extent of the object Manual Refresh W Auto Refresh Allow the view to be refreshed at the time interval specified in the Auto Refresh central control Table 33 Description of the information displayed in the Objects about to Crash view 7 3 4 4 Tables to Rebuild The Tables to Rebuild view is shown below
117. y v Last Changes Query Field settings and properties f Error C Warning Prima Key existence and properties Error fe warming Iv Index on Last Changed Query Field C f Warning Insert 7 Update trigger properties f Warning Delete Control Table settings f Error C Warning Delete Trigger properties Error Warning v Column Settings Emor Warning v Primary Key existence and properties C Emor Warning Defaults Figure 46 Server Table Validation Tab 4 9 3 Local Table Validation Local target table validation 1s performed on the common data tables Refer to Figure 47 The common data tables are only validated for MGA Duplica Last Changed Query Field settings and properties Check that the last changed field 1s populated with a valid column name Check that the specified column exists in the target table and 1s of type DATE Primary Key existence and properties Check that the primary key constraint exists and is enabled Check that if the primary key is on the REPID column then it must have a valid insert update trigger associated to the table to populate it The checking is done if the source table exists Index on Last Changed Query Field Version 3 01 INSTALLATION 5 1 Check that there is an index on the last changed query field x Server Table Local T able Tables to Apply Validation for Insert Update Operation
118. you access to all the standard Printer Setup options such as page size page orientation paper source paper size layout overlay etc Press OK to apply the changes Press Cancel to discard the changes 6 10 Filter Options The Filter button on the Replication Manager main menu can be used to reduce the number of suites displayed in the Replication Manager suites list window with respect to the type of filter used Figure 98 e All Suites All Scheduled Suites All Not Scheduled Suites All Stopped Items All With Interval All that contain local table All that use server table like All with tables in tablespace Suite Like Schema Like Figure 97 Filter Button and Options The filter options are described in detail in Table 20 This table also shows the popup window and an example of the input data required All Not Scheduled Suites All With Interval Select to open the dialog box below Enter Interval and Unit X Enter Interval Unit Cancel Type the interval and unit separated by e g 15 m The unit can be typed in upper or lower case All suites with the interval and unit specified are listed MGA Duplica Version 3 01 USING REPLICATION MANAGER 95 Filter Menu Option Explanation All that contain local table Select to open the dialog box below like E Local Table Like Filter Enter T able name like string include
119. you to gauge both the status of the replication process and the health of your local database This document includes e A discussion of the concept of replication How to install configure and upgrade Duplica e How to use the Replication Manager e How to use the Replication Monitor 1 2 How and Where to Store this Manual A digital copy of this manual has been supplied by MGA Multiple copies should be kept in different physical locations to protect against damage or loss caused by fire or theft The digital copy should be saved in multiple locations This is necessary because computer hardware may breakdown or may be required to go off site for repair This ensures that there will always be a copy of the User Manual for reference 1 3 How to Use this Manual This document has been written for anyone who may use Duplica at your site It assumes that the reader has had some experience in using Oracle databases and is familiar with the database concept and terminology This manual 1s task oriented that 1 it has been designed so that users can visually associate and identify where 1 the manual they need to refer to 1n order to complete their task MGA Duplica Version 3 01 INTRODUCTION 12 Section 2 is a summary of the concept of replication It discusses the history behind why replication of data is necessary gives some examples scenarios of how data replication 1s used by industry and briefly discusses

Duplica User Manual - Mark Gurry & Associates

Contents

Download Pdf Manuals

Related Search

Related Contents

Duplica User Manual - Mark Gurry &amp; Associates

Contents

Download Pdf Manuals

Related Search

Related Contents

Duplica User Manual - Mark Gurry & Associates