FileMaker-to-Oracle Migration with FmPro Migrator on Mac OS X , Part 2by David Simpson
Editor's note: FileMaker Pro 6 is a terrific database, as long as you stay within the confines of its design. In part one of this series, David Simpson provided practical migration information using FmPro Migrator on Mac OS X to automate the move from FileMaker to Oracle. Here in part two, he goes through the steps of completing a FileMaker-to-Oracle transition.
The FileMaker Example Database
The example database used for this article is the Contact_Management.fp5 database that is supplied as a template with FileMaker Pro. (Notice that I have replaced the space with an underline character.) This database includes a wide selection of fields so that we can see how the various data types get transferred to Oracle. The following table shows the FileMaker data types and the compatible column types for the Oracle database.
FileMaker Field Type
|Oracle Column Type|
|Text||CLOB (if > 4000 bytes)|
Table 1. FileMaker and Oracle Data Types
The first steps in the migration process are to launch FileMaker, open the Contact_Management.fp5 database file, and add a few records of sample data to the database. Then launch FmPro Migrator on Mac OS X and click on the FileMaker folder tab. Since I usually do a migration project for a customer, I change the Customer field to reflect their name. The name within this field is used to create the Copyright information within each generated script, thus keeping my customer's legal department happy. Click the Browse button to select an output directory, since there will be over a dozen files created for the migration process.
If the name of your FileMaker application is not "FileMaker Pro", then click on the Select button beside the FileMaker App Name field to select the actual name of your FileMaker application. Some common variations of the FileMaker application name include "FileMaker," "FileMaker Pro," and "FileMaker Developer." This information is used to construct the AppleScript commands that will be sent to the FileMaker database application.
Select Oracle 8i-9i from the Destination Database menu. Selecting the Oracle 8i-9i menu option instead of the Oracle 8 option will change the table/tablespace creation code to enable the use of locally managed tablespaces. This is generally a good feature to implement with newer versions of Oracle in order to improve performance.
The Path to Perl field will not require modification in most cases.
Oracle Database Parameters
The Oracle-specific parameters are entered on the Oracle tab. These parameters include the Service Name as it is listed within the tnsnames.ora file. You don't need to specify the an IP address or TCP/IP port number when connecting to an Oracle database, because these parameters are specified as part of the tnsnames.ora service name entry. All you have to do is simply provide the service name, and Oracle networking takes care of looking up the specifics within the tnsnames.ora. For this example, I am using a database service name of dev1, which corresponds to my database instance that uses the same name.
The Username and Password are used to connect to the Oracle database.
The Data and Index Tablespace names and Filenames specify where the new database table and index will be located. Unchecking the Use Existing check boxes will cause FmPro Migrator to generate SQL code to create any of these items; otherwise, existing accounts and tablespaces will be used. Specifying the exact tablespace and data path for the tablespace enables the Oracle DBA to perform load balancing with the database server hardware.
If you plan to migrate a FileMaker database that is expected to be used frequently, you may choose to locate it on a different part of a disk array from other heavily utilized database tables. The need to perform this type of hardware load balancing is minimized with fast disk array hardware such as the Xserve Raid system from Apple. Modern disk arrays can do a lot of this load balancing automatically when set up in a RAID 0+1 configuration.
But what if you have a project that is so heavily utilized that you have two Xserve arrays? In this case, you can put one set of tables on the first array and the second group of tables on the second array. Load balancing in this manner can prevent you from fully saturating the communications channels going to a single array. Based upon the high performance specs of an Xserve Raid array, it would be difficult to saturate a single channel, but this type of planning is still important.