PPAs Command Line Migration Tool

1

The following table between database objects that can be migrated when migrating from another database to PPAs

2

Installation of PPAs will have the option to install Migreation Toolkit, you can choose to install, you can also download the installation to www.enterprisedb.com

3

Edit/opt/postgresplus/9.2as/etc/toolkit.properties

Linux-np3p:/opt/postgresplus/9.2as # Cat-netc/toolkit.properties

Src_db_url=jdbc:oracle:thin:@192.168.1.127:1521:orcl

Src_db_user=testuser

Src_db_password=testuserpw

Target_db_url=jdbc:edb://localhost:5444/testdb1

Target_db_user=testuser

target_db_password=123456

Linux-np3p:/opt/postgresplus/9.2as #

4

Copy Oracle's JDBC interface drive into/opt/portgresplus/9.2as/jre/lib/ext

Note the driver to copy and JRE version

5

Migration

Linux-np3p:/opt/postgresplus/9.2as/bin #./runmtk.sh TestUser

Source Database connection Information ...

Connect =JDBC:ORACLE:THIN:@192.168.1.127:1521:ORCL

User =testuser

Password =******

Target Database connection Information ...

Connect =JDBC:EDB://LOCALHOST:5444/TESTDB2

User =testuser

Password =******

Connecting to source Oracle database server ...

Connecting to target Enterprisedb database server ...

Importing Redwood Schema TestUser ...

Creating schema ... TestUser

Creating table ...

Creating table: TEST1

1 tables have been created.

Loading table data in 8 MB batch size ...

Loading table: TEST1 ...

[TEST1] has migrated 2 rows.

[TEST1] Table Data Load summary: Total time (seconds): 0.375 lines total: 2

Data Load Summary: Total time (seconds): 1.111 lines total: 2 size Total (MB): 0.0

The schema testuser was successfully imported.

Creating User: TESTUSER

An error occurred while creating user TESTUSER

There are one or more schema objects that cannot be imported in the migration process. For more detailed information, see Migrating output information.

Migration log has been saved to/root/.enterprisedb/migration-toolkit/logs

Migration Summary ********************

Tables:1 from 1

users:0 from 1

All objects: 2

Success Count: 1

Failure Count: 1

List of failed objects

======================

Users

--------------------

1. TESTUSER

*************************************************************

6

Query Migration Results

Linux-np3p:/opt/postgresplus/9.2as/bin #./psql-u testuser TESTDB1

Password for user testuser:

Psql (9.2.0.1)

Enter "help" to get assistance information.

testdb2=# select * from Test1;

id| XName

----+--------------

1 |test/gaga

2 |test/gaga

(2 Line Records)

testdb2=#

7

The following is the contents of the runmtk.sh, you can see the call to the tool Edb-migrationtoolkit.jar

# ----------------------------------------------------------------------------

# --

#-Copyright (c) 2004-2012-enterprisedbcorporation. All Rights Reserved.

# --

#----------------------------------------------------------------------------

/opt/postgresplus/9.2as/jre/bin/java-dprop=.. /etc/toolkit.properties-jar Edb-migrationtoolkit.jar "$@"

8

The following are Migreationtoolkit's help information, listing optional parameters

Linux-np3p:/opt/postgresplus/9.2as #jre/bin/java-jar Bin/edb-migrationtoolkit.jar-help

Enterprisedb Migration Toolkit (build 46)

Usage: RUNMTK [-Options] SCHEMA

If no options are specified, the complete schema is imported.

Where the options include:

-HELP Displays the command line usage of the application.

-version Displays the application version information.

-verbose [On|off] Displays application log messages in standard output (default: On).

-schemaonly only imports schema object definitions.

-dataonly only import table data. If-tables is specified, only the data for the selected table is imported. Note: If any foreign key constraints are defined on the destination table, this option should be used with the-truncload option.

-sourcedbtype Db_type the-sourcedbtypeoption Specifies the source database type. Db_type May is one of the Followingvalues:mysql, Oracle, SQL Server, Sybase, PostgreSQL, Enterprisedb. Db_type iscase-insensitive. By default, Db_type is Oracle.

-targetdbtype Db_type the-targetdbtypeoption Specifies the target database type. Db_type May is one of the followingvalues:oracle, SQL Server, PostgreSQL, Enterprisedb. Db_type iscase-insensitive. By default, the Db_type is enterprisedb.

-alltables Import All tables.

The-tables list imports a comma-delimited list of tables.

-constraints Import table Constraints.

-indexes Import Table Index.

-triggers Import table triggers.

-allviews Import all views.

The-views list imports a comma-separated list of views.

-allprocs import all stored procedures.

The-procs list imports comma-delimited lists of stored procedures.

-allfuncs Import all functions.

The-funcs list imports a comma-delimited list of functions.

-allpackages Import all packages.

The-packages list imports a comma-delimited list of packages.

-allsequences import all the sequences.

The-sequences list imports a comma-delimited list of sequences.

The name of the-targetschema name target schema (default: The target schema is named after the source schema).

-alldblinks Import all database links.

-allsynonyms It enables the migration of all public and private synonyms from anoracle database to a Advanced Server dat Abase. If a synonym with the same name already exists in the target database,the existing synonym would be replaced with the Migra Ted version.

-allpublicsynonyms It enables the migration of all public synonyms from a oracledatabase to a Advanced Server data Base. If a synonym with the same name already exists in the target database,the existing synonym would be replaced with the Migra Ted version.

-allprivatesynonyms It enables the migration of all private synonyms from a oracledatabase to a Advanced Server dat Abase. If a synonym with the same name already exists in the target database,the existing synonym would be replaced with the Migra Ted version.

-dropschema [True|false] If the schema already exists in the target database, delete the schema (default: false).

-truncload This option disables any constraints on the target table, and truncates the data in the table before importing new data. This option can only be used with-dataonly.

-safemode uses pure SQL statements to transmit data in safe mode.

-copydelimiter when loading table data, specify a character as the separator in the Copy command. The default value is \ t

-BATCHSIZE Specifies the batch size to use for bulk INSERT. Valid values are 1-1000, the default batch size is 1000, and if there is an "out-of-memory" exception, you can reduce this value

-CPBATCHSIZE Specifies the batch size, in megabytes, to be used by the replication command. Valid value is greater than 0 and the default batch size is 8 MB

-FETCHSIZE Specifies the extraction size (the number of rows to be fetched each time from the result set). This option is available when the datasheet contains millions of rows and you want to avoid out-of-memory errors.

-filterprop The property file that contains the table WHERE clause.

-skipfkconst skips migration of foreign key constraints.

-skipckconst skips the migration of check constraints.

-ignorecheckconstfilter By default, MTK does not migrate CHECK constraints and default clauses from Sybase, this option allows you to turn off this filtering function.

-fastcopy Skips WAL logging, performs the COPY operation optimally, and is disabled by default.

The-customcoltypemapping list uses custom type mappings, represented by a semicolon-delimited list, where each entry is specified with a Col_name_reg_expr=type pair, for example. *id=integer

-customcoltypemappingfile prop_file The custom type mappings represented by the property file, where each entry is specified using a Col_name_reg_expr=type pair, for example. *id=integer

-offlinemigration [Ddl_path] This will perform an offline migration and save the DDL script in a file for later execution. By default, if the requirement followed by the-offlinemigration option and the custom path, the script file is saved under the user's home folder.

-logdir Log_path Specifies the custom path used to save the log file. By default, the log file is saved in the Logs folder in the working directory.

-copyviadblinkora This option can be used to copy data by using the Dblink_ora Copy command. This option is only available from Oracle to enterprisedb migration mode.

-singledatafile Use single SQL file for offline data storage to all tables. This option cannot is used in COPY format.

-allusers Import all users and roles from the source database.

-users LIST Imports selected users/roles from the source database. A comma-delimited list of user/role names, such as-users mtk,sample

-allrules Import all rules from the source database.

-rules LIST Imports the selected rule from the source database. List is a comma-delimited listing of names, such as-rules high_sal_emp,low_sal_emp

-allgroups Import all groups from the source database.

-groups LIST Imports the selected group from the source database. List is a comma-separated set of group names, such as-groups acct_emp,mkt_emp

-alldomains Imports all domains, enumerations, and composite types from the source database.

-domains LIST Imports the selected fields, enumerations, and composite types from the source database. A comma-delimited list of domain names, such as-domainsd_email,d_dob, mood

-objecttypes Import user-defined object types.

-replacenullchar <CHAR> If the null character is part of the column, migrating the data through the JDBC protocol will fail. This option replaces an empty string with a user-specified character.

-importpartitionastable [LIST] uses this option to import partitioned tables from Oracle into the ENTERPRISEDB as a regular table. In order to apply a rule on a collection of selected tables, you should follow the list of comma-delimited table names after the options.

-enableconstbeforedataload Use this option to re-enable constraints (and triggers) before the data is imported. This option is useful when the table you want to migrate corresponds to a partitioned table in Enterprisedb.

When-checkfunctionbodies [True|false] is set to False, the function experience certificate in the creation Function procedure is disabled, thereby avoiding an error when the function contains a forward reference. The target database is Postgres/enterprisedb, and the default value is true.

-retrycount VALUE Specifies the number of retries of the MTK migration for objects that failed due to cross schema dependencies. The value parameter should be greater than 0 and the default value is 2.

-analyze it will invoke the analyze operation on the target Postgres or Postgres Plus Advanced Server database. ANALYZE collects statistics for migrated tables that are used to efficiently query the plan.

-vacuumanalyze it will invoke VACUUM and ANALYZE operations on the target postgres or postgresplus Advanced Server database. VACUUM reclaims inactive tuple stores, ANALYZE collects statistics for migrated tables that are used for valid query plans.

-loadercount VALUE Specifies the number of jobs (threads) that perform data loading in parallel. The value parameter should be greater than 0 and the default value is 1.

Database connection Information:

The application will read the connection information for the source and destination database servers from the file toolkit.properties.

For more information, see the Readme documentation for MTK.

Linux-np3p:/opt/postgresplus/9.2as #

9

If you see the noon Help information is ambiguous, look at the following English help information

ot@host19.2as]# Jre/bin/java-jar Bin/edb-migrationtoolkit.jar-help

Enterprisedbmigration Toolkit (Build 46)

Usage:runmtk[-options] SCHEMA

If no option isspecified, the complete schema would be imported.

where Optionsinclude:

-help Display the application command-lineusage.

-version Display the application versioninformation.

-verbose[on|off] Display Application log messages on standard output (Default:on).

-schemaonly Import the schema object definitions only.

-dataonly Import the table data only. When-tables is inplace, it imports data to the selected tables. Note:if There are any fkconstraints defined in target tables, use-truncload option along with Thisoption.

-sourcedbtypedb_type the-sourcedbtype option specifies the source database type. Db_typemay be one of the following values:mysql, Oracle, SQL Server, Sybase,postgresql, Enterprisedb. Db_type is case-insensitive. By default, Db_type isoracle.

-targetdbtypedb_type the-targetdbtype option specifies the target database type. Db_typemay be one of the following values:oracle, SQL Server, Postgresql,enterprisedb. Db_type is case-insensitive. By default, the Db_type is enterprisedb.

-alltables Import All tables.

-tables list Import comma-separated List of tables.

-constraints Import the table constraints.

-indexes Import the table indexes.

-triggers Import the table triggers.

-allviews Import all views.

-views list Import comma-separated List of views.

-allprocs Import all stored procedures.

-procs list Import comma-separated list of storedprocedures.

-allfuncs Import all functions.

-funcs list Import comma-separated List of functions.

-allpackages Import All packages.

-packages listimport comma-separated List of packages.

-allsequences Import all sequences.

-sequences listimport comma-separated List of sequences.

-targetschemaname Name of the target schema (Default:target schema is named after Sourceschema).

-alldblinks Import all Database Links.

-allsynonyms It enables the migration of all public andprivate synonyms-Oracle database to a Advanced Server D  Atabase. If a synonym with the same name alreadyexists in the target database, the existing synonym'll is replaced with Themigrat Ed version.

-allpublicsynonyms It enables the migration of all publicsynonyms from Oracle database to a Advanced Server database. If a synonym with the same name alreadyexists in the target database, the existing synonym'll is replaced with THEMIGR Ated version.

-allprivatesynonyms It enables the migration of all privatesynonyms from Oracle database to a Advanced Server database  . If a synonym with the same name alreadyexists in the target database, the existing synonym'll is replaced with Themigrat Ed version.

-dropschema[true|false] Drop The schema if it already exists in the target database (Default:false).

-truncload It disables any constraints on target table andtruncates the ' data from the ' table before importing new data. This option canonly be used with-dataonly.

-safemode Transfer data in Safe mode using plain sqlstatements.

-copydelimiter specify a single character to is used asdelimiter in copy command when loading table data. Default is \ t

-batchsize Specify the Batch Size to is used by the bulkinserts. Valid values are 1-1000,default batch size is 1000, reduce if you run into out of Memory exception

-cpbatchsize Specify the Batch Size in MB, to be Usedin the Copy Command. Valid value is > 0, default Batch the size is 8 MB

-fetchsize specify fetch size in terms of number of rowsshould is fetched in to a time. This option can is used when tablescontain millions of rows and your want to avoid out of memory.

-filterprop The properties file that contains table Whereclause.

-skipfkconst Skip migration of FK constraints.

-skipckconst Skip migration of Check constraints.

-ignorecheckconstfilter By default MTK does not migrate checkconstraints and default clauses to Sybase, use this optio N to turn off Thisfilter.

-fastcopy Bypass WAL Logging to perform the copyoperation in a optimized way, default disabled.

-customcoltypemappinglist Use custom type mapping Representedby a semi-colon separated list, where each entry is specified Usingcol_name_reg_expr=type pair. e.g. *id=integer

-customcoltypemappingfileprop_file the custom type mappingrepresented by a properties FILE, where each entry is SPECIF IED Usingcol_name_reg_expr=type pair. e.g. *id=integer

-offlinemigration[path] This is performs offline migration and saves the DDL/DML scripts in FilesFor a later execution. By default the script files would be saved under Userhome folder, if required follow-offlinemigration option with a custom Path.

-logdirlog_path Specify a custom PATH to save the log file. By default, on Linux thelogs'll be saved under folder $HOME/.enterprisedb/migration-toolkit/logs. Incase of Windows logs would be saved under Folder%homedrive%%homepath%\.enterprisedb\migration-toolkit\logs.

-copyviadblinkorathis option can be used to copy data using Dblink_ora copy Commad. This optioncan is used in Oracle to enterprisedb migration mode.

-singledatafile Use single SQL file for offline data storagefor all tables. This option cannot is used in COPY format.

-allusersimport all users and roles from the source database.

-users Listimport The selected users/roles from the source database. The list is acomma-separated list of user/role names e.g.-users mtk,sample

-allrulesimport all rules from the source database.

-rules Listimport The selected rules from the source database. The LIST is a comma-separatedlist the rule names e.g.-rules high_sal_emp,low_sal_emp

-allgroupsimport all groups from the source database.

-groups Listimport The selected groups from the source database. The LIST is a comma-separated listof group names e.g.-groups acct_emp,mkt_emp

-alldomainsimport all domain, enumeration and composite types from the source database.

-domains Listimport The selected domain, enumeration and composite types from the sourcedatabase. The list is a comma-separated list of domain names e.g.-domainsd_email,d_dob, mood

-objecttypes Import the User-defined object types.

-replacenullchar<char> If null character is part of a column value, the data migrationfails over JDBC protocol. This option can is used to replace null Characterwith a user-specified character.

-importpartitionastable[list] Use that option to import Oracle partitioned table as a normal table inenterprisedb. To apply the rule on a selected set of tables, follow the Optionby a comma-separated list of table names.

-enableconstbeforedataloaduse this option to re-enable constraints (and triggers) before data load. Thisis useful in the scenario when the migrated table was mapped to a partitiontable in Enterprisedb.

-checkfunctionbodies[true|false] When set to false, it disables validation of the function bodyduring function creation, t The is to avoid errors if function contains forwardreferences. Applicable when the target database is Postgres/enterprisedb, Defaultis true.

-retrycountvalue Specify the number of re-attemptsperformed by MTK to migrate objects-failed to due Ndencies. The VALUE parameter should is greater than 0, and the default is 2.

-analyze It invokes analyze operation against a targetpostgres or Postgres Plus Advanced Server database. The ANALYZE collectsstatistics for the migrated tables this are utilized for efficient query plans.

-vacuumanalyze It invokes VACUUM and ANALYZE operationsagainst a target Postgres or Postgres Plus Advanced Server dat Abase. The Vacuumreclaims dead tuple storage whereas ANALYZE collects statistics for themigrated tables so are utilized for EFF Icient query plans.

-loadercountvalue Specify the number of jobs (threads) to perform data load in parallel. The VALUE parameter should begreater than 0, default is 1.

DatabaseConnection Information:

The Applicationwill read the connectivity information for the source and target databaseservers from Toolkit.properties fi Le.

Refer to Mtkreadme the document for more information.