Audit Users` Manual - ALTIBASE Customer Support
Contents
1. Optional multiple fields are allowed The comma must be in front of every repetition DESC fox NU References For more detailed information see the following document Altibase Administrator s Manual Altibase Replication User s Manual Altibase iSQL User s Manual Altibase Utilities User s Manual Altibase Error Message Reference Online Manual Korean and English versions of on line manuals PDF or HTML are available from Altibase Download Center http atc altibase com Altibase Welcomes Your Opinions Please send us your comments and suggestions regarding this manual Your comments and sugges tions are important and they may be used to improve future versions of the manual When you send your feedback please make sure to include the following information The name and version of the manual in use Your comments or suggestions regarding the manual Your name address and phone number Audit Users Manual iv About This Manual Please send your e mail to the following address support altibase com This address is intended to report any errors or omissions discovered in the manual When you need an immediate assistance regarding technical issues please contact Altibase Customer Support Cen ter We always appreciate your comments and suggestions v Preface About This Manual Audit Users Manual vi l O2. pere nettere denten 15 Setting SYNG Option ott ete eee edi pa eese deed 15 Execution Command sceau I MM E M MEME Neel 15 Exec tion Results ese eee e RERO tni ON eS RUE 15 LANA CIIM ab 17 Example of DIFF Records Log Sisene ideeen edere derde hellende 18 DIFF Exi Mpe Tic eR dateerde e RE RR ele esce Er Aena 18 DIFF Example 2 18 DIFF Example 3 19 Example of Synchronization SYNC Example 1 SYNC Example2 SYNC Example 3 7 Example rnaar enen neerde 21 Preface About This Manual About This Manual This manual describes how to monitor and administrator Altibase replication using Audit utility Target Users This manual could be useful for the following Altibase users Database administrators Application designers Programmers Before reading this manual understanding of following background knowledge is recommended Basic knowledge required for computers operating systems and operating system command Experience in using the relational database or understanding of the database concepts Computer programming experience Software Environment This manual has been prepared assuming Altibase 5 3 1 will be used as the database server Organization This manual has been organized as follows Chapter 1 Overview This chapter discribes what is the Autit and Inconsistency controlling methods for Altibase Replica tion Servers Chapter 2 C
3. LOG FILE sample log EMP TABLE EMPLOYEE SCHEMA SYS DEPARTMENT TABLE DEPARTMENT SCHEMA SYS As shown in the above example the target tables in the master server host1 and the slave server host2 may have different names 3 1 2 DIFF Example 2 The following is when selection is made based on ENO column of EMP table and JOIN DATE and SEX column are excluded from comparison Depending on Condition property only EMP record of which ENO is between 1 and 20 will be com pared Depending on EXCLUDE property JOIN DATE and SEX will be excluded from the comparison In other words the records are considered as the same as long as other columns are the same except for JOIN DATE and SEX EMP TABLE EMPLOYEE WHERE ENO gt 1 and ENO lt 20 EXCLUDE JOIN DATE SEX DEPARTMENT Audit Users Manual 18 Example of DIFF Records Logs 3 1 3 DIFF Example 3 The following is when selection is made based on ENO column of EMP table and JOIN DATE and SEX column are excluded from comparison EMP TABLE EMPLOYEE WHERE ENO gt 1 and ENO lt 20 or JOIN DATE gt 20001010 j EXCLUDE SEX DEPARTMENT Depending on Where property only EMP record of which ENO is between 1 and 20 or of which JOIN_DATE is after October 10 2000 will be compared Also depending on Exclude property SEX will be excluded from the comparison subject 19 Examples Example of Synchronization 3 2 Ex
4. Tables group The contents of the configuration file are displayed as follows INFO MNG Tread O init is OK INFO MNG Tread 0 start is OK TAB 2 TAB 2 Fetch Rec In Master 3 Fetch Rec In Slave 2 MOSX SI MXSO MOSO SU MXSX Operation Type MASTER SLAVE INSERT Try 0 1 Fail 0 0 UPDATE Try X 1 Fail X 0 DELETE Try X 0 15 Controlling Data Inconsistency Syncronizing Automatically Fail X 0 UPDATE Try 0 2 Fail 0 0 OOP TPS 13698 63 SCAN TPS 20547 95 Time 0 00 sec If there is any record fails the cause of the error and the record contents are displayed Audit Users Manual 16 3 Examples This chapter describes examples of using comparison DIFF and synchronization SYNC features 17 Examples Example of DIFF Records Logs 3 1 Example of DIFF Records Logs The following example compares EMP table of host 1 with Employee table of host 2 and Depart ment table of host 1 with Department table of host 2 3 1 1 DIFF Example 1 When DB MASTER is host1 and DB_SLAVE is host2 and when all records of each table are compared cfg file is as follows DB MASTER altibase sys man ager DSN host1 PORT NO 10111 NLS USE US7ASCII DB SLAVE altibase sys manager DSN host2 PORT NO 20111 NLS USE US7ASCII OPERATION DIFF MAX THREAD 1 DELETE IN SLAVE ON INSERT TO SLAVE ON INSERT TO MASTER ON UPDATE TO SLAVE ON AUTODETECT UNIO INX ON LOG DIR mjn
5. ALTIBASE Tools Audit Users Manual release 5 3 3 Contents AE IAEE EE IAE EENE OSEE S E A E A AAEE E EE E ETE E A E A i About This Manual ssrin en aE e ert ER e e ee c EER Ni ii Farget USET Siea ii SoftWare Environme Nts nsr a e NAE N O AOAN tee da ii Organization eret PM Emma RD E M EAM ME ii Documentation Rule4 rete rt voe cp d co e te ron ec t eO Pe ER e cur e ea ee ii Referentes marna RAA t iv Online Man al z b de dete ete bed Cot teta A ped SR EEA iv Altibase Welcomes Your Opinions 1 Overview 1 IntroductiOn Lu eescssescssssscccsseccesstecssneceecnsecessees 2 2 Terminology of the Replication od Inconsistency Record 252 Synchronization Policy 43 Running Audit Utility 5 2 Controlling Data Inconsistency as ssessrsonesveonenvennennvennenonenneeonenneeorseneennennveonenneeonennvennennsensennvennennsenneenveoneensenneen 7 Configuring Audit Utility ea Fas emi e ire RE e ve eerte ales 8 Audit Property File nnn ended entente vennen etten EE 8 Execution Options emir servants deedeetee edet addeert dea 9 FAB OG ssd TUI 10 Extracting DIFF Records Eos rrr ertt dei esrb reser iri ERU E AEE 13 Setting DIFF Option p 13 Execution Commands tnt onontdekt E E A enden 13 Ex cution Results eterne trepidi getto dissel renstal diene eerde 13 Syricronizing Automatically
6. B and the slave DB have different table names related information must be described and applied to comparison DIFF and synchro nization SYNC features When omitted it is set as the same name with the table name of the master DB However you should specify table name with alphabetic letter number and special characters blank 96 amp _ except korean letter Describes table schema of slave db If a user s schema name connected to slave is different a object table s schema must describe table schema are different if omitted use schema of contact user Audit Users Manual 12 Extracting DIFF Records Logs 2 2 Extracting DIFF Records Logs It creates a execution result file by identifying the inconsistent records that may occur during repli cation between the master DB and the slave DB 2 2 1 Setting DIFF Option Sets Operation property of the auditing environment file as DIFF All property files of the execution option must be described and such properties as Where Exclude Table and Schema must be selectively described 2 2 2 Execution Commands Comparison DIFF function is executed as follows shell audit f script file name Script file name File name including the path of the environment file 2 2 3 Execution Results As the execution result it compares the master database with the slave database for each execution log file and table including the summary of the executi
7. Data Type Support ALTIBASE doesn t support CLOB and BLOB if database is Oracle You should use EXCLUDE property as follows to exclude certain column for Audit Example 4 You should exclude certain column for AUDIT when CLOB column exists on EMP table Audit Users Manual 8 Configuring Audit Utility TABLE EMP EXCLUDE CCC 2 1 2 Execution Options Specifies the synchronization policy of connection information between local server and remote server and comparison DIFF syncronisation SYNC and inconsistent records Properties that can be set in this group are as follows DB MASTER Defines the sever that runs the auditing program Sets the user name the password the IP address of the server the server name and NLS_USE The property value must match with information in the property file under Altibase home directory DB SLAVE Sets the counter server Sets the user name the password the IP address of the server the server name and NLS USE The property value must match with information in the property file under Altibase home directory However if you want to save them as text file not in the opposite server but in the path where you can execute Audit you can make it by doing as follows They are saved in log DB SLAVE text user ID PW log OPERATION Sets DIFF for comparison or SYNC for synchronization INSERT TO SLAVE Sets SI policy for the inconsistency of the MOSX Decides whether to in
8. OYEE SCHEMA SYS DEPARTMENT TABLE DEPARTMENT SCHEMA SYS Shell audit f sample cfg shell cat sample log INFO MNG Tread O0 init is OK INFO MNG Tread 1 init is OK INFO MNG Tread 0 start is OK INFO MNG Tread 1 start is OK DEPARTMENT DEPARTMENT Fetch Rec In Master 5 Fetch Rec In Slave 5 MOSX NO MXSO NO MOSO SU Operation Type MASTER SLAVE INSERT Try 0 0 Fail 0 0 UPDATE Try X 0 Fail X 0 DELETE Try X 0 Audit Users Manual 22 UPDATE Try OOP TPS 0 00 SCAN TPS 60240 96 Time 0 00 sec EMPLOYEE gt EMPLOYEE Fetch Rec In Master 20 Fetch Rec In Slave 15 MOSX NO MXSO NO MOSO SU Operation Type INSERT Try Fail UPDATE Try Fail DELETE Try Fail UPDATE Try Fail OOP TPS 576 04 SCAN TPS 2304 15 Time 0 01 sec 23 Example of Synchronization Examples Example of Synchronization Audit Users Manual 24 Index A audit Environment File 4 audit execution option 9 audit property file 8 AUTODETECT UNIQ INX 10 Automatic synchronization 15 C CHECK INTERVAL 10 configuring audit utility 8 D DB MASTER 9 DB SLAVE 9 DELTE IN SLAVE 9 DIFF 4 13 18 different record DIFF 2 E EXCLUDE 11 Extraction DIFF Records logs 13 I INSERT_TO_ MASTER 9 INSERT_TO_SLAVE 9 L local server 2 M master DB 2 master server 2 MAX_THREAD 10 MI Policy 3 MOSO Unsynchronization 2 MOSX Unsynchronization 2 MXSO Unsynchronization 2 o OPERATION 9 P pro
9. ample of Synchronization It presents the example that specifies the operation and the table related to the synchronization pol icy to resolve inconsistent records 3 2 1 SYNC Example 1 Assume that the inconsistent MOSX record that is in the master server but not in the slave server will be inserted into the slave server and that the inconsistent MXSO record that is in the slave sever but not in the master server will be ignored Master Server altibase sys man ager DSN host1 PORT NO 10111 NLS USE US7ASCII Slave Server altibase sys man ager DSN host2 PORT NO 20111 NLS USE US7ASCII OPERATION SYNC MAX THREAD 1 DELETE IN SLAVE OFF INSERT TO SLAVE ON INSERT TO MASTER OFF UPDATE TO SLAVE ON AUTODETECT UNIO INX ON LOG DIR LOG FILE sample log EMP TABLE EMPLOYEE SCHEMA SYS DEPARTMENT TABLE DEPARTMENT SCHEMA SYS As SI policy is a synchronization policy to remove inconsistent MOSX records INSERT TO SLAVE property has been set to ON Also inconsistent MXSO record is ignored so INSERT TO MASTER and DELETE IN SLAVE property related to essential MI SD policy will be set to OFF 3 2 2 SYNC Example2 Assume that the inconsistent MOSX record that is in the master server but not in the slave server will be inserted into the slave server and that the inconsistent MXSO record that is in the slave server but not in the master server will be inserted into the master se
10. ave server the synchronization policy such as SI policy and SD policy is necessary Therefore INSERT TO SLAVE and DELETE IN SLAVE properties are set to On 3 2 4 Example A simple example that compares Employee table of the local server host 1 with Employee table with ENOs from 16 to 20 deleted of the remote server host 2 and synchronizes Department table of host 1 server with Department table of host 2 Creates a replicated connection between the local server and the remote server 21 Examples Example of Synchronization For local server IP 192 168 1 11 iSQL CREATE REPLICATION repi WITH 127 0 0 1 sys employee FROM sys department TO sys department Create Success isQL For remote server IP 127 0 0 1 iSQL CREATE REPLICATION repl WITH 192 168 1 11 sys employee FROM sys department TO sys department Create Success isQL In case current directory is user charlie altibase home audit shell vi sample cfg Master Server altibase sys man ager DSN 127 0 0 1 PORT NO 20582 NLS USE US7ASCII Slave Server altibase sys man ager DSN 192 168 1 11 PORT NO 20582 NLS USE US7ASCII 56342 FROM sys employee TO 65432 FROM sys employee TO OPERATION SYNC MAX THREAD 1 DELETE IN SLAVE ON INSERT TO SLAVE ON INSERT TO MASTER OFF UPDATE TO SLAVE ON AUTODETECT UNIO INX ON LOG DIR LOG FILE sample log EMPLOYEE WHERE ENO gt 1 and ENO lt 20 TABLE EMPL
11. cludes unique properties The property contains information necessary for running the program See sam ple cfg under audit directory 2 1 1 Audit Property File The property format is property name property value and is case insensitive The environment file can include symbols that have special meaning i represents comments and ignores the character strings after the symbol symbol is to describe the property value in multiple lines i mark as an identifier to separate multiple values markis used on character string when including reserved or special character on user name password table name column name Altibase porvides special characters 96 amp x Property Name It consists of characters without blank and identifies the property in the group Property Value It can have a single value multi values or expressions The expression may include blank and most properties have this format E g 1 TABLE EMPLOYEE The multiple value has several single values separated by identifier and the property value in multiple line must be within Example 2 Exclude group allows multiple values E g 2 EXCLUDE ENO DNO ENAME Or EXCLUDE ENO DNO ENAME The expression is to express character strings including blank and must be within D Where property has expression E g 3 WHERE ENO gt 1000 and ENO 2000
12. diate time when tables are synchronized The unit is MS MiliSecond MAX_THREAD Specifies the number of threads for operation If you want to use unlimited number of threads you want to perform specify this as 1 2 1 3 Table Group You must describe Groups as many as the number of targeting tables and the group name must be the table name of the master DB You can set properties as follows WHERE Defines the conditions to select the table records The property value can be described in the same way as Where clause of SQL statement There are possible Multiple values But the user cannot define multiple conditions by using identifier It is impossible to write notes on this property Applied to comparison DIFF and comparison SYNC features Audit Users Manual 10 Configuring Audit Utility Exclusive Column DB1 DB2 gt DB1 DB2 A LA Defines information about replication target tables EXCLUDE Defines the condition to project the table record The property must have multiple values The described columns are excluded from comparison and synchronization operations Applied to comparison DIFF and comparison SYNC features If Where and Exclude are combined properly the user can audit the result of selection and projec tion Exclusive Column DB1 DB2 11 Controlling Data Inconsistency Configuring Audit Utility E Designates the table name of the slave DB In case the master D
13. mn value when a record have a same type with LOB column type Audit Users Manual 14 Syncronizing Automatically 2 3 Syncronizing Automatically It identifies the consistent records between the master DB and the slave DB removes the inconsis tency according to the synchronization policy in the audit configuration file and creates an execu tion result file including execution summary data and the error data 2 3 1 Setting SYNC Option Defines Operation property in the auditing environment file as SYNC All property fields of the execution option must be described and such properties as Where Exclude Table and Schema of the table group must be selectively described 2 3 2 Execution Command Synchronization function is executed as follows shell audit f script file name Script file name File name including the path of the configuration file 2 3 3 Execution Results As an execution result it compares the master database with the slave database for each execution log file and table including the summary of the execution result It consists of an execution result file that includes the inconsistent column contents of the record that is not synchronized between two databases and an error log file that includes the errors occurring during operation Execution Log File Named as script file name log and displays the contents of the executed environment file as well as the summary of the synchronization for the tables in
14. n execution result file including execution summary data and the error data Audit Property File An environment file to set the option to execute auditing This file includes connection information auditing feature and synchronization policy Audit Users Manual 4 Running Audit Utility 1 2 Running Audit Utility To use auditing feature the user must create an auditing environment file that includes information about the table where DIFF or SYNC feature is performed Audit environment file will be explained at Audit Environment File Section of Operation Method Auditing commands are as follows shell audit f script file name script file name File name including the path of the environment file or If current directory is as follows user charlie altibase home audit user charlie altibase home audit audit script file name user charlie altibase home audit audit script file name The separate feature for auditing between Altibase server and Oracle server can be included in the package 5 Overview Running Audit Utility Audit Users Manual 2 Controlling Data Incon sistency This chapter describes the auditing environment file that explains information necessary for audit ing and then describes DIFF and SYNC features 7 Controlling Data Inconsistency Configuring Audit Utility 2 1 Configuring Audit Utility The environment file describes the comparison and synchronization operations and each in
15. on result Then an execution result file will be created including the inconsistent column contents of the record that is not synchronized between two databases For example sample log and master table user name slave table log files for each table are cre ated under audit directory if audit command is run successfully when you run this of user charlie altibase home audit audit sample cfg Execution log file Named as script file name log and displays the contents of the executed environment file and the summary of comparison DIFF for the table of each Tables group The contents of the environment file are displayed as follows INFO MNG Tread O init is OK INFO MNG Tread 0 start is OK TAB 2 TAB 2 Fetch Rec In Master 3 Fetch Rec In Slave 2 MOSX DF Count 1 MXSO DF Count O MOSO DF Count 1 SCAN TPS 20547 9 Time 0 00 sec Execution Result File This is named as master table user name slave table log and displays the execution result in the fol 13 Controlling Data Inconsistency Extracting DIFF Records Logs lowing format DF m n gt COL N Vn M Vn S PK PCOL V DF Inconsistency MOSX MOSO MXSO m Record sequence of Master Server n Record sequence of Slave Server COL N First column name have a different value of comparing result Vn M Corresponding column value of Master Server Vn S Corresponding column value of Slave Server It is not described a colu
16. ontrolling Data Inconsistency This chapter describes the auditing environment file that explains information necessary for audit ing and then describes DIFF and SYNC features Chapter 3 Example This chapter describes examples of using comparison DIFF and synchronization SYNC features Documentation Rule This chapter describes the rules of this manual With understanding of this rule it is easy to search information in this manual and other manuals Audit Users Manual ii About This Manual Rules are as follows Syntax diagram Sample code rule This manual describes the command syntax using the diagram composed of the following elements Elements Meaning The command starts The syntax element which is not a complete command starts with an arrow Reserved word The command continues to the next line The syntax ele ment which is not a complete command terminates with this symbol The command continues from the previous line The syn tax element which is a complete command starts with this symbol End of a statement Mandatory SELECT Optional NOT Mandatory field with optional items Only one field must be provided ADD DROP lii Preface About This Manual Elements Meaning ASC DESC Optional field with optional item
17. perty name 8 property value 8 R remote server 2 S SD Policy 4 SI Policy 3 slave DB 2 slave server 2 SU Policy 3 SYNC 4 15 synchronization 20 synchronization policy 3 25 T TABLE 12 TABLES group 10 U UPDATE_TO_SLAVE 10 W WHERE 10
18. rver Master Server altibase sys man ager DSN host1 PORT NO 10111 NLS USE US7ASCII Slave Server altibase sys man ager DSN host2 PORT NO 20111 NLS USE US7ASCII OPERATION SYNC MAX THREAD 1 DELETE IN SLAVE OFF INSERT TO SLAVE ON INSERT TO MASTER ON Audit Users Manual 20 Example of Synchronization UPDATE TO SLAVE ON AUTODETECT UNIQ INX ON LOG DIR LOG FILE sample log EMP TABLE EMPLOYEE SCHEMA SYS DEPARTMENT TABLE DEPARTMENT SCHEMA SYS As SI policy is a synchronization policy to remove inconsistent MOSX records INSERT TO SLAVE property has been set to ON Also inconsistent MXSO record must insert to Master Server However essential consistent policy is MI policy So corresponding INSERT TO MASTER property is set to ON and DELETE IN SLAVE property is set to OFF respectively 3 2 3 SYNC Example 3 Assume that the master server is synchronized with the slave server Master Server altibase sys man ager DSN host1 PORT NO 10111 NLS USE US7ASCII Slave Server altibase sys man ager DSN host2 PORT NO 20111 NLS USE US7ASCII OPERATION SYNC MAX THREAD 1 DELETE IN SLAVE ON INSERT TO SLAVE ON INSERT TO MASTER OFF UPDATE TO SLAVE ON AUTODETECT UNIO INX ON LOG DIR n fon LOG FILE sample log EMP TABLE EMPLOYEE SCHEMA SYS DEPARTMENT TABLE DEPARTMENT SCHEMA SYS To synchronize the master server with the sl
19. sert the corresponding record into the slave DB The property value may be ON or OFF for insertion or non insertion respectively INSERT TO MASTER Sets MI policy for the inconsistency of the MXSO Decides whether to insert the corresponding record into the Master DB The property value may be ON or OFF for insertion or non insertion respectively On and DELETE IN SLAVE property cannot exist together at the same time DELTE IN SLAVE Sets SD policy for the inconsistency of the MXSO Decides whether to delete the corresponding record from the slave DB The property value may be ON or OFF for deletion or non deletion 9 Controlling Data Inconsistency Configuring Audit Utility On and INSERT TO MASTER property cannot exist together at the same time UPDATE TO SLAVE Sets SU policy for the inconsistency of the MOSO Decides whether to update the corresponding record in the slave DB If the property value may be ON or OFF for modification or non modifica tion AUTODETECT UNIQ INX Decides whether to delete the corresponding record from the slave DB and to execute it again if Duplicated Key Values error occurs in the slave DB when inserting or updating the data to the slave DB from the master DB If property value may be ON or OFF for deletion or non deletion It can be On only when both INSERT TO SLAVE and DELETE IN SLAVE properties are On CHECK INTERVAL Sets interval as specified time during an interme
20. verview This Chapter discribes what is the Autit and Inconsistency controlling methods for Altibase Replica tion Servers 1 Overview Introduction 1 1 Introduction It compares the database of the Altibase server being replicated with the database of the Altibase server to be replicated or with the database of the Oracle server to be replicated by table If there is any inconsistency it displays inconsistent information and corrects the inconsistency 1 1 1 Terminology of the Replication Local Server The server to execute auditing Remote Server The remote server subject to replication Master Server There are the local or remote server as standard server for auditing Master DB The database of the master server Slave Server There are the local or remote server as standard server for auditing Slave DB The database of the slave server 1 1 2 Inconsistency Record Inconsistency is checked by comparing records of the table in the mster DB with the one in the slave DB using primary key There are three types of inconsistency MOSX Unsynchronization When a record in the master DB but is not in the slave DB MOSO Unsynchronization When both master and slave table has the same value for primary key columns but the value in the non primary key column is defferent MXSO Unsynchronization When a record is not in the master DB but is in the slave DB Audit Users Manual 2 Introduction 1 1 3 S
21. ynchronization Policy A policy that specifies how to synchronize inconsistent records The auditing program fixes the mas ter DB as the reference DB and synchronizes it with the slave DB There are four synchronization policies SU Policy It is the policy to resolve the MOSO inconsistency by updating the slave DB to the contents of the master DB SI Policy It is the policy to resolve the MOSX inconsistency by inserting the record of the mas ter DB into the slave DB Master DB Slave DB MI Policy It is the policy to resolve the MXSO inconsistency by inserting the records of the slave DB into the master DB Master DB Slave DB Master DB Slave DB 3 Overview Introduction SD Policy It is the policy to resolve the MXSO inconsistency by deleting the records from the slave DB Master DB Slave DB 15 MN The user can define SU policy SI policy MI policy and SD policy in the auditing environment file Note that MI policy and SD policy are exclusive and they cannot be defined at the same time Extracting Different Records Log Method DIFF Create a execution result file by identifying the inconsistent records that may occur during replica tion between the master DB and the slave DB Automatic Synchronization Method SYNC Identify the inconsistent records between the master DB and the slave DB resolve the inconsistency according to the synchronization policy in the audit environment file and create a
Download Pdf Manuals
Related Search