Project for data migration from old CRM system of the company (Clipper-DBF) to new (Oracle, HTML, JavaScript).

  • Volume data: 30Gb
  • Volume of the resulting database (with indexes): 65Gb
  • Amount of destination tables: 60
  • Maximum number of records in the table: 90 million
  • Tables with number of records more than million: 25
  • Duration of the migration process: more 24 hours
  • The ability to restart process from same point in case of an error: provided
  • Stop the transformation process from interface: provided
  • Number of steps of the process: 9
  • Skipping steps transformation in the interface settings: provided
  • Oracle server: 11G 64bit
  • The mechanism of data transfer: Oracle heterogeneous services (MS Access DBF driver)

    Previously, I had to migrate data from DBF files to 32 bit instances of Oracle databases and driver problems have been not encountered. For the 64 bit version everything was difficult. Microsoft has closed support for DBF formats. After searching the forums was that it is still possible to use the DBF driver for MS Access.
    The download process was managed through a simple web interface. The web server used Oracle Apache Server that worked with the database directly via mod_plsql module. Business ESCC for the four countries of the former USSR unified enough, but between the branches still have differences, so setting some constants of the transfer data, I realized through the interface of the registry.
    My experience has shown that directly work with large tables in DBF format using heterogeneous services is almost impossible due to performance loss, therefore in the first step, I transferred the data into a temporary Oracle schema. In addition, for DBF files there is a limit in size (2 GB), so some large tables were broken to pieces. When loading into a temporary schema I did a merge of the tables, building indexes, and small improve conversion. Download process tables managed to parallelize, at the same time could run four processes, and their number is constantly checked by the backend and supervisor program, and at reduction was made to start the process of downloading the next table.
    The transfer process is started from the interface. When it launched a program- supervisor, as the process Oracle. It every 10 seconds checks the status of tasks and the error log. All tasks were run this program, to determine their belonging to the project used field [MODULE] and [CLIENT_INFO] in sessions Oracle table. If necessary, the transfer process could be ended, it was done with [KILL SESSION]. Not yet been completed the old process, the new start was impossible.
    The migration process was divided into 9 steps, in accordance with the whole logical blocks. Sometimes there are cases when some steps don’t make sense to repeat, so in the interface to specify a script to run by disabling some steps.
    The loading process of the data recorded in detail with the help of the log. Each step had a few items, data on which completing was recorded. From the interface you can see all the steps, indicating their start time and end time.
    Information about previous processes are also stored and available for viewing.
    In case of an error, after correction, the process could start from the beginning of the current step.
    In the first step of the process, the whole database is reset. To speed up the process I turned off all CONSTRAINTS in the database, using the script and make the necessary tables TRUNCATE, then re-included CONSTRAINTS.
    After migration, SEQUENCES for the primary keys of the loaded tables re-calculated and the whole scheme was about statistics.

    The main difficulty faced in this project: debugging queries. The problem was that at the beginning of the loading tables were empty, then they were filled and could be great, but Oracle CBO continued to consider them small and always had plans of a query on the basis of the FULL SCAN, so in the project there is not a single query without HINTS.

  • Add Comment