migrating databases from Oracle to PPAs

PPAs has two migration tools, a graphical interface, a command line, and the following is an example of a graphical interface.

1

First of all, we need to establish the corresponding user and peer permissions on the target database system PPAs, and then set up the target database.

Create user "usernamexxx" withsuperuser password ' xxxxxx ';

Create DATABASE Databasenameowner= "tytutor" encoding= ' UTF8 ';

2

Copy the Oracle database driver to the following directory according to the JRE version (the current environment is version 1.4)

/opt/postgresplus/9.2as/jre/lib/ext/ojdbc14.jar

3

Running the PPAs Migration Tool

4

5

Right-click on the servers to increase the migration source and target database

6

7

Right-click the schema to migrate on the left Oracle source database and migrate online now

8

Select target database, schema, click Run

OK, start migrating, you can see the process of information prompts or migration log files, Oracle migrated to PG may have many Pl/sql database objects will fail, to move to PPAs is much better, because PPAs has the Oracle compatible engine.

When you're done, you can migrate the apps, and that's a bunch of things.

PPAs also has a command-line migration tool that can have more control over the migration process than the graphical interface, as shown in the following parameters that can be taken:

Ot@host1 9.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 version information.

-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-tablesis in place, it imports data is for the selected tables. Note:if there areany FK constraints defined on target tables, use-truncload option along option.

-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 is Oracle.

-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 from a Oracle database to a Advanced Server dat  Abase. 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 Datab  Ase. If a synonym with the same name alreadyexists in the target database, the existing synonym'll is replaced with Themigrat Ed version.

-allprivatesynonyms It enables the migration of all privatesynonyms from Oracle database to a Advanced Server Datab  Ase. 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 to target Tableand truncates the data from the table before importing new data. This optioncan is 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 Thebulk inserts. 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 used inthe Copy Command. Valid value is > 0, default Batch the size is 8 MB

-fetchsize specify fetch size in terms of number ofrows should is fetched in result set at a time. This option can is used whentables contain millions of rows and you want to avoid out of memory.

-filterprop The properties file that contains Tablewhere clause.

-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 opt Ion 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 Specifi Ed 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.

-logdir log_pathspecify a custom path to save the LOG file. By default, on Linux the logs willbe saved under folder $HOME/.enterprisedb/migration-toolkit/logs. In case ofwindows 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 datastorage to all tables. This option cannot is used in COPY format.

-allusers Import allUsers 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

-allrules Importall rules from the source database.

-rules LIST importthe selected rules from the source database. The list is a comma-separated list ofrule names e.g.-rules high_sal_emp,low_sal_emp

-allgroups Importall groups from the source database.

-groups Listimport The selected groups from the source database. The LIST is a comma-separatedlist of 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.

-retrycount VALUE Specify the number of re-attempts Performedby MTK to migrate objects this failed to due Dependencies. Thevalue parameter should be greater than 0, 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 Database . 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 was greater 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.