Readers in a DBA or database development role will most likely be familiar with SQL Loader, Oracle database external tables, Oracle GoldenGate, and Oracle Warehouse Builder. Application developers and architects will mostly likely be familiar with Oracle BPEL and the Oracle Service Bus.
Database migration products and tools
Data migration is the first step when moving your mission critical data to an Oracle database. The initial data loading is traditionally done using Oracle SQL Loader. As data volumes have increased and data quality has become an issue, Oracle Data Warehouse and Oracle Data Integrator have become more important, because of their capabilities to connect directly to source data stores, provide data cleansing and profiling support, and graphical drag and drop development. Now, the base addition of Oracle Data Warehouse Builder is a free, built-in feature of the Oracle 11g database, and price is no longer an issue.
Oracle Warehouse Builder and Oracle Data Integrator have gained adoption as they are repository based, have built-in transformation functions, are multi-user, and avoid a proliferation of scripts throughout the enterprise that do the same or simpler data movement activity. These platforms provide a more repeatable, scalable, reusable, and model-based enterprise data migration architecture.
SQL Loader
SQL Loader is the primary method for quickly populating Oracle tables with data from external files. It has a powerful data parsing engine that puts little limitation on the format of the data in the data file. The tool is invoked, when you specify the sqlldr command or use the Oracle Enterprise Manager interface.
SQL Loader has been around as long as the Oracle Database logon “scott/tiger” and is an integral feature of the Oracle database. It works the same on any hardware or software platform that Oracle supports. Therefore, it has become the de facto data migration and information integration tool for most Oracle partners and customers. This also makes it an Oracle legacy data migration and integration solution with all the issues associated with legacy tools, such as:
- difficult to move away from as the solution is embedded in the enterprise.
- The current solution has a lot of duplicated code, because it was written by many different developers before the use of structured programming and shared modules.
- The current solution is not built to support object-orientated development, Service Orientated Architecture products, or other new technologies such as web services and XML.
- The current solution is difficult and costly to maintain because the code is not structured, the application is not well documented, the original developers are no longer with the company, and any changes to the code cause other pieces of the application to either stop working or fail.
SQL Loader is typically used in ‘fat file’ mode. This means the data is exported into a command-delimited fat file from the source database or arrives in an ASCII fat file. With the growth of data volumes, using SQL Loader with named pipes has become common practice. Named pipes eliminate the need to have temporary data storage mechanisms—instead data is moved in memory.
It is interesting that Oracle does not have an SQL unload facility, as Sybase and SQL Server have the Bulk Copy Program (BCP). There are C, Perl, PL/SQL, and other SQL-based scripts to do this, but nothing official from Oracle. The SQL Loader source and target data sources along with development languages and tools supported are as follows:
- Data source – Any data source that can produce flat files. XML files can also be loaded using the Oracle XMLtype data type
- Data target – Oracle
- Development languages and tools – Proprietary SQL Loader control files and SQL Loader Command Line Interface (CLI)
The most likely instances or use cases when Oracle SQL Loader would be the Oracle product or tool selected are:
- Bulk loading data into Oracle from any data source from mainframe to distributed systems.
- Quick, easy, one-time data migration using a free tool.
Oracle external tables
The external tables feature is a complement to the existing SQL Loader functionality. It enables you to access data in external sources as if it were in a table in the database. Therefore, standard SQL or Oracle PL/SQL can be used to load the external file (defined as an external table) into an Oracle database table.
Customer benchmarks and performance tests have determined that in some cases the external tables are faster than the SQL Loader direct path load. In addition, if you know SQL well, then it is easier to code the external table load SQL than SQL Loader control files and load scripts. The external table source and target data sources along with development languages and tools supported are:
- Data source – Any data source that can produce flat files
- Data target – Oracle
- Development languages and tools -SQL, PL/SQL, Command Line Interface (CLI)
The most likely instances or use cases when Oracle external tables would be the Oracle product or tool selected are:
- Migration of data from non-Oracle databases to the Oracle database.
- Fast loading of data into Oracle using SQL.
Oracle Warehouse Builder
Oracle Warehouse Builder (OWB) allows users to extract data from both Oracle and non-Oracle data sources and transform/load into a Data Warehouse, Operational Data Store (ODS) or simply to be used to migrate data to an Oracle database. It is part of the Oracle Business Intelligence suite and is the embedded Oracle Extract- Load-Transform (ELT) tool in this BI suite. With the usage of platform/product specific adapters it can extract data from mainframe/legacy data sources as well.
Starting with Oracle Database 11g, the core OWB product is a free feature of the database. In a way, this is an attempt to address the free Microsoft entry level ELT tools like Microsoft Data Transformation Services (DTS) and SQL Server Integration Services (SSIS) from becoming de facto ELT standards, because they are easy to use and are cheap (free). The Oracle Warehouse Builder source and target data sources along with development languages and tools supported are:
- Data source – Can be used with the Oracle Gateways, so any data source that the Gateway supports
- Data target – Oracle, ODBC compliant data stores, and any data source accessible through Oracle Gateways, flat files, XML
- Development languages and tools -OWB GUI development tool, PL/SQL, SQL, CLI
The most likely instances or use cases when OWB would be the Oracle product or tool selected are:
- Bulk loading data on a continuous, daily, monthly or yearly basis.
- Direct connection to ODBC compliant databases for data migration, consolidation and physical federation, including data warehouses and operational data stores.
- Low cost (free) data migration that offers a graphical interface, scheduled data movement, data quality, and cleansing.
SQL Developer Migration Workbench
Oracle SQL Developer Migration Workbench is a tool that enables you to migrate a database, including the schema objects, data, triggers, and stored procedures, to an Oracle Database 11g using a simple point-and-click process. It also generates scripts necessary to perform the migration in batch mode. Its tight integration into SQL Developer (an Oracle database development tool) provides the user with a single- stop tool to explore third-party databases, carry out migrations, and to manipulate the generated schema objects and migrated data. Oracle SQL Developer is provided free of charge and is the first tool used by Oracle employees to migrate Sybase, DB2, MySQL and SQL Server databases to Oracle.
SQL Developer Migration Workbench 3.0 was released 2011 and includes support for C application code migration from Sybase and SQL Server DB-Library and CT- Library, a Command Line Interface (CLI), a host of reports that can be used for fixing items that did not migrate, estimating and scoping, and database analysis, and a pluggable framework to support identification and changes to SQL in Java, Powerbuilder, Visual Basic, Perl, or any programming language.
SQL Developer Migration Workbench actually started off as a set of Unix scripts and a crude database procedural language parser based on SED and AWK. This solution was first made an official Oracle product in 1996. Since then, the parser has been totally rewritten in Java and the user interface integrated with SQL Developer. SQL Developer Migration Workbench source and target data sources along with development languages and tools supported are:
- Data source – DB2 LUW, MySQL, Informix, SQL Server, Sybase
- Data target – Oracle
- Development languages and tools – SQL Developer GUI development tool, Command Line Interface (CLI)
The most likely instances or use cases when SQL Developer Migration Workbench would be the Oracle product or tool selected are:
- Data migration from popular LUW RDBMS systems to Oracle using fat files or JDBC connectivity.
- RDBMS object (stored procedures, triggers, views) translation from popular LUW RDBMS to Oracle.