Database Of Migration And Replication With Oracle Golden Gate

The main goal of this research is to analyze and design a database configuration of migration and replication in PT Metro Batavia. Research methodologies used in this research are data collecting, analysis and design model. Data collecting method is conducted with library research and direct survey in the company. Analysis method is conducted by analyzing hangar system, migration and reflection process and the available problems. Design method is conducted by designing a prototype for migration process with the implementation of Oracle SQL Developer and replication process with implementation of Oracle Golden Gate. The result of this research is a prototype for configuration of migration and replication process by using Oracle Golden Gate, which can produce two sets of identical data for the purpose of backup and recovery, and also design a simple tool that is expected to help active-active or active-passive replication process. The conclusion of this research is migration process of MySQL database to Oracle database by using Oracle Golden Gate hasn't been conducted, because Oracle Golden Gate still has bug related to binary log, so database of migration is conducted by using Oracle Golden Gate. However, replication of bi-directional in between database of Oracle by using Oracle SQL Developer can guarantee data availability and reduce work burden from primary database.


INTRODUCTION
In this globalization era, business is developing very rapidly and more competitive. As with this business development, information technology is also expected to develop and innovate so it can balance development in business. Company and organization ZLOO PRUH GHSHQG RQ WKH PRVW HIIHFWLYH DQG HI¿FLHQW information technology in running their business process including process of storage and processing business information.
Integration between information technology and business process will produce very important information for a company or organization in process of decision-making concerned interests of business of that company. Then, from that guarantee about safety and reliability of an information technology, it becomes an important factor for a company to use offered information technology. However, there isn't any information technology provider companies that give one hundred percent guarantee that by using their information technology, system will run without any REVWDFOHV HLWKHU SODQQHG RU XQSODQQHG OLNH GLI¿FXOW\ in integrating data that is using different database, possibility of downtime when integrating the data, high performance from server because of continuous access so there is possibility of damage occurrence to the server, or there is possibility of unexpected natural disasters. Those disturbances may cause bad impact to process of storage and processing business information resulting loss in a company. Therefore, company wants a system that can keep the continuity of business process, integrity and safety of data storage.
PT. Metro Batavia is a company moving in DLU WUDQVSRUWDWLRQ ¿HOG RU XVXDOO\ FDOOHG DV DLUOLQH by using Batavia Air as brand name. In its business process, not all of systems from PT. Metro Batavia are integrated in the same database. Hangar system in this company still uses MySQL database. However, because of the big amount of data and information that must be provided by this system, then company decides to migrate the database from MySQL to Oracle to run this system.
In the development of business in PT. Metro Batavia, data availability and business information produced by hangar system in the company get a big portion in success of PT. Metro Batavia as one of the airlines that is the most interested by society. Then, from that, it needs a hangar system that runs real time so needed data and information can be distributed on time. In addition, PT Metro Batavia also wants a system that provides continuous and availability, where that system is free from potential disturbance for causing data loss and delay in business process. Because of this awareness, PT Metro Batavia wants precise design of data replication in hangar system, so it can keep the continuity of their business process, integrity and safety of data storage.
The goals of this research are to analyze, to do database migration from MySQL to Oracle by using Oracle SQL Developer and to do active-active FRQ¿JXUDWLRQ RI GDWD UHSOLFDWLRQ LQ 2UDFOH GDWDEDVH of the result of replication to provide data continually (continuous data availability) in hangar system of PT Metro Batavia by using Oracle Golden Date. Furthermore, this research aims to do analysis of migration business by using Oracle Golden Gate and JLYH VROXWLRQ RI FRQ¿JXUDWLRQ RI UHSOLFDWLRQ WKDW FDQ keep data availability.
:KLOH WKH H[SHFWHG EHQH¿WV LQ WKH HQG RI this research are: (1) database of hangar system is not MySQL database anymore, but Oracle database, (2) to prevent data loss by doing active-active replication between two databases (primary and backup database). (3) The result of research can be used to develop other systems for application of high availability system.

Literature Review
Oracle Golden Gate allows change and manipulates data in transaction level in middle platform that is more than one and different. Oracle  Extract process runs in source system and is extraction mechanism from Oracle Golden Gate.

&RQ¿JXUDWLRQ RI H[WUDFW FDQ EH GRQH E\
,QLWLDO load: for initial data loads, extracts a set of data directly from source object. (2) Changing synchronization: to keep synchronized data source with other sets of data, Extract catches changes done in data ( especially insert, update and delete transactional) after initial synchronization has been done, change in DDL and sorting is also extracted if it is according to type of used database.
Extract process catches all changes that is done LQ FRQ¿JXUHG REMHFWV WR V\QFKURQL]H ([WUDFW SURFHVV saves all changes till stage has accepted commit records or rollbacks. When rollback is accepted, Extract throws away data for that transaction. When commit is received, Extract sends data for that transaction to trail process in target system. All log notes for a transaction is written on trail as a transaction unit arranged sequentially. This design holds responsible of data speed and integrity.
'DWD SXPS LV DGGLWLRQDO H[WUDFW FRQ¿JXUHG in source system. If a data pump isn't used, Extract process must send data into remote trail in target V\VWHP ,I GDWD SXPS LV FRQ¿JXUHG JURXS RI SULPDU\ Extract will write in local trail which is available in WDUJHW V\VWHP 'DWD SXPS DGG ÀH[LELOLW\ RI VWRUDJH and also serve isolation process of primary Extract from TCP/IP activity.
Replicat process runs in target system. Replicat reads change in data extracted and change LQ ''/ LI WKHUH LV FKDQJH VSHFL¿HG LQ 5HSOLFDW FRQ¿JXUDWLRQ DQG WKHQ UHSOLFDWHV EDFN WR WDUJHW GDWDEDVH &RQ¿JXUDWLRQ RI UHSOLFDW LV GRQH E\ Initial load: for initial data loads, Replicat can apply data to target object or send it to high-speed bulkload utility. (2) Changing synchronization: to keep synchronization, Replicat applies change in extracted data in target object using native database interface or ODBC, depends on the types of database. DDL and orders replicated are also applied, if it is according to used database. Replicat applies changes that are replicated by the same order when those changes are committed in source database.
To support extraction and replication process continually from change in database, Oracle Golden *DWH VDYHV FKDQJHV FDXJKW WR GLVF LQ VHULHV ¿OH WKDW LV called as trail. A trail can be in source or even target system, or also in intermediate system, spends on KRZ WKH FRQ¿JXUDWLRQ RI 2UDFOH *ROGHQ *DWH ,Q WKH local system, trail is known as extract trail or local trail. In remote system trail is known as remote trail.
In using trail for storage, Oracle Golden Gate support accuracy of a data and error tolerance. Usage of trail also allows extraction and replication activity appearing freely with other trails. By this separated process, there is an opportunity to arrange how data is sent. For example, rather than extracting and replicating change continually, we can extract changes continually but save it in trail to replicate in target later, whenever target application needs it.
Checkpoint saves position that is recently read and copied from a process to a disc for recovery. This checkpoint ensures that any changes in marked data for synchronization is extracted through Extract process and replicated by Replicat process, and also prevent repetitive or redundant process. Checkpoint provides error tolerance by preventing data loss, and requiring system, network, or a process of Oracle *ROGHQ *DWH WR VWDUW DJDLQ )RU FRPSOH[ FRQ¿JXUDWLRQ of synchronization, checkpoint allows Extract and Replicat process to be more than one to read set of same trail.
Checkpoint works with inter-process acknowledgement to prevent data loss in network. Oracle Golden Gate has technology of incision of guaranteed message. Extract creates Checkpoint in its position in source data and in trail.
A checkpoint system is used by Extract and Replicat process operating continually, but it doesn't need Extract and Replicat process running in batch mode. A batch process can be run back from its start point, where the process continually needs support to interrupt planned or unplanned that is provided by checkpoint.
Manager is a process controller from Oracle *ROGHQ *DWH 0DQDJHU PXVW UXQ LQ ERWK FRQ¿JXUHG system by Oracle Golden Gate before Extract or Replicat can be started, and Manager must still run while those processes running till management function of resource is done. Manager does functions like these: (1) Monitor and restart process of Oracle Golden Gate. (2) Publishing beginning reports, like when throughput runs slowly or when hidden synchronization increases. (3) Keeping trail and log ¿OHV $OORFDWLQJ GDWD VWRUDJH URRP 5HSRUWLQJ mistake and incident. (6) Accepting and sending demand from user of interface Collector is a process running in background in target system. Collector accepts change in extracted database sent through TCP/IP network, and writes LW LQWR WUDLO ¿OH RU H[WUDFW 6SHFLDOO\ 0DQDJHU VWDUWV Collector automatically when a network connection is needed. When Manager starts Collector, process is known as dynamic Collector and users of Oracle Golden Gate also can be run manually. This is known DV VWDWLF &ROOHFWRU 1RW DOO FRQ¿JXUDWLRQV RI 2UDFOH Golden Gate use Collector process.
When a dynamic Collector is used, Collector can received information only from an Extract process, then it must be a dynamic Collector in each Extract process that is used. However, the comparison of one DQG RQH LV RSWLPDO &ROOHFWRU SURFHVV LV ¿QLVKHG ZKHQ FRPELQDWLRQ RI ([WUDFW SURFHVV LV ¿QLVKHG For default, the Extract process starts TCP/ IP connection from source system to Collector in WDUJHW EXW 2UDFOH *ROGHQ *DWH FDQ EH FRQ¿JXUHG VR Collector starts connection from target. Initiation of connection from target may be needed if target is in network area that can be trusted, but the source is in the area of network that can't be trusted.
According to Ref. [1], replication is a creation RI D RU PRUH FRS\ ³¿OH V\VWHP´ 5HSOLFDWLRQ LV XVHG to duplicate all changes that happen in a server called as master server or master, to other server or called as slave server or slave. Two important things from replication is creating backup from main server to avoid data loss if master is damaging and having copy from main server to run reporting and work analysis without disturbing the course of business. Replication like migration or data synchronization is done in database, between source and target.
According to Ref. [1] migration is movement RI D ³¿OH V\VWHP´ IURP D VHUYHU WR WKH RWKHU VHUYHU According to Ref. [2], database of migration refers to collection of processes and procedures to convert data from a server database to other server database.
The method for data replication between a VRXUFH DQG D WDUJHW FRQVLVWV RI 'H¿QLQJ SK\VLFDO data model saved from source and target, each physical model represents plural data structure. (2) 'H¿QLQJ ORJLFDO GDWD PRGHO IURP VRXUFH DQG WDUJHW each logical model consists of plural nodes and based on data structure from physical related model

METHOD
In this research, writer collects data based on theories supporting research comes from several literatures and interview with company party to know condition and need of company. Based on data that has been collected, writer analyzes company condition and faced problems, decides precise solution to overcome those problems and does design system prototypes that are suggested.
Research that is done by reviewing theories DQG RWKHU VFLHQWL¿F GDWD IURP VHYHUDO OLWHUDWXUHV WKDW will be made as reference of this thesis writing and others source references like text book and internet website related to research topic.
Research directly conducted to obtain main direct data from company. Survey analysis is conducted in form of direct interview with related parties to get information needed for this thesis writing. Writer interviews directly the Database Administrator of PT Metro Batavia about business process running especially hangar system and company needs.
Design method that is used in design of this migration and active-passive replication is: (1) Design and implementation of migration using Oracle SQLDeveloper to change MySQL database to Oracle. (2) Design and implementation of activepassive by using Oracle Golden Gate to provide data continually (continuous data availability). (3) While, to ease replication process, then designed simple by using program language Microsoft Visual Basic .NET 2008 Express Edition.

RESULTS AND DISCUSSION
In hangar system, there are three important parts that related to each other, which is Land Maintenance (LM), Base Maintenance (BM), and PPC & MMC Login. LM and BM are in charge to repair plane, check plane condition, repair plane and maintenance regularly for each plane. Procedure in hangar system starts by making Taskcard by PPC/MMC. Then, from available taskcard, engineer/mechanic will choose and print several taskcard to be done. That taskcard consists RI ZRUNV WKDW PXVW EH ¿QLVKHG E\ WHFKQLFLDQ %HIRUH starting doing taskcard, technician must earlier check stock of part and material that will be used. Part and material that will be used listed on printed taskcard. If part and material that will be used is not provided, then technician must request from logistic division. If goods that are needed provided in logistic division then that goods will be directly sent to hangar system. If they aren't, logistic division will do request purchasing good in purchasing division. Purchasing division will process that request and part and material that are requested wull be sent to logistic division that will be sent to hangar division later.
After all parts and materials are available, technician will immediately start to work. Each task WKDW KDV EHHQ ¿QLVKHG ZLOO EH PDUNHG 7KHQ DIWHU DOO is done, technician will do scan and save task card WKDW KDYH EHHQ ¿QLVKHG ,QVSHFWRU ZLOO FKHFN ZKHWKHU DOO ZRUNV WKDW KDYH EHHQ ¿QLVKHG DUH FRUUHFW 6HW RI task card that has been done will be made in a work order that consists of works that have been done, start WLPH ¿QLVK WLPH DV WKH SDUW RI VDODU\ FDOFXODWLRQ RI D technician.
)RU KDQJDU V\VWHP WKUHH VHUYHUV IXO¿OOLQJ need of logistic data, login function and hangar data are used. In addition, each of available server has its own database. Database that is used is MySQL. The faced problems are because MySQL doesn't provide dblink facility, then when database is in 2 different servers, it needs to build 2 connections earlier, other than that, MySQL doesn't support inter join and left join facility.
Database of migration process is a process to convert data from a database to other database either in the same environment or the different environment. Environment that is meant is hardware and software that is used altogether with database. In this process, not only data that is moved, but also available objects in that database like table, view, trigger, stored procedure, function and other objects. There are reasons why a company wants to do migration process in its database: (1) A company wants to upgrade database that they are using with the newer version. (2) A company wants to change their database with other database that is more reliable and can do better process of processing data.
(3) Hardware and software that are being used will be changed, so company is required to change their database to higher version or other vendor.
Database of replication process is process of doubling all changes that happen in a server or called as master server or master to other servers called as slave server or slave. In this process, a company can make replica from database that is being used by them as a backup if failure happens on system either intentionally or unintentionally so that company still has copy from main server to run reporting and work analysis without disturbing the course of business.
Several reasons of a company want to do FRQ¿JXUDWLRQ RI UHSOLFDWLRQ LQ WKH PDLQ GDWDEDVH DUH %\ GRLQJ FRQ¿JXUDWLRQ RI UHSOLFDWLRQ GDWD availability so business process is more guaranteed.
&RQ¿JXUDWLRQ RI UHSOLFDWLRQ DOVR DOORZV WR reduce burden of a database because target database of replication can be used to handle reporting and read-only queries that are in basic are not heavy. (3) Company will have backup database that can be used if failure happens planned or unplanned outage.
Problems that are being faced by PT Metro Batavia related with migration and replication process especially for hangar system are: (1) The size of data storage in MySQL database isn't enough to contain the amount of data that must be saved. (2) Processing data process becomes slow. (3) It doesn't support DB-Link feature. (4) It needs tough, cost-effective and can do migration in many platforms. (5) Accessing database excessively (overload transaction) causes processing data to be slow. (6) Hangar system hasn't had backup database +HUH DUH FRQ¿JXUDWLRQV FRQGXFWHG LQ VRXUFH database adjusted with requirement of environment adjustment before using Oracle Golden Gate: (1) Do FRQ¿JXUDWLRQ RI 7&3 ,3 DQG '16 RQ HWF KRVWV 7KLV is done so secondary system can know its hostname.
(2) Make sure that 7809 port hasn't been used by command of netstat-na. (3) Create user oraclegg as system user that will be given right to access and XVHG 2UDFOH *ROGHQ *DWH (

Binary log problem
Until the previous stage, we have done environment adjustment with Oracle Golden Gate, initialization of extract and replication, and its parameter. The next thing that we should do is START EXTRACT ext_1. However, when we run this, then we will get that extraction isn't running and the status is ABENDED, by giving error as shown in Fig. 3.

CONCLUSION
Analysis and design of data migration and replication In hangar system is meant to increase performance of hangar system database as the effect of many available transactions to build a useful DFWLYH DFWLYH FRQ¿JXUDWLRQ ZKHQ RXWDJH KDSSHQV LQ that database.
After doing analysis and design of this data migration and replication, then it can be concluded that: (1) After doing research of usage of Oracle Golden Gate to migrate database from MySQL WR 2UDFOH ZULWHU ¿QGV RXW WKDW WKHUH LV SUREOHP happening during migration, which is problem on binary log in MySQL database. Error happens from Oracle Golden Gate, when accessing status from binary log that has been in enabled condition as disabled. Evaluation of the result of migration research is by using Oracle Golden Gate. (2) Activeactive replication (bi-diractional replication) is done WR EXLOG D FRQ¿JXUDWLRQ EHWZHHQ WZR GDWDEDVHV VR those two databases are synchronized to each other and produces two databases that have identical data set. This replication can guarantee data availability in hangar system and reduce work burden to replace primary database. In addition, data set of replication result (secondary database) can be used to replace primary database if outage happens in that database.
Several suggestions that can be given for further development is that the company is recommended to document tables in each available database and business process lane in that company LWVHOI VR LW FDQ HDVH GHYHORSHU WR GHVLJQ FRQ¿JXUDWLRQ of migration and replication in the future. This thing LV EDVHG RQ WKH ZULWHU ¶V GLI¿FXOW\ WR JHW VWUXFWXUH RI hangar system database that will be migrated and replicated.