|Oracle® Database Migration Guide
12c Release 1 (12.1)
|PDF · Mobi · ePub|
This chapter discusses the following topics:
You can use the DBA Navigator in Oracle SQL Developer 3.2 to install and manage the translator and translation profile.
This section contains the following topics:
The SQL Translation framework is installed as part of Oracle Database installation. However, it must be configured to recognize the non-Oracle SQL dialect of the application and you must install at least one translator to fully utilize the framework.
Before using the SQL Translation feature, you must migrate your data, schema, stored procedures, triggers, and views. Oracle implements database schema migration and data migration through Oracle SQL Developer functionality. Oracle SQL Developer simplifies the process of migrating a non-Oracle database to an Oracle Database through the use of Migration Wizard. The Migration wizard provides convenient and comprehensive guidance through the phases involved in migrating a database.
Oracle SQL Developer captures information from the source non-Oracle database and displays it in a captured model, which is a representation of the structure of the source database. This representation is stored in a migration repository, which is a collection of schema objects that Oracle SQL Developer uses to store migration information.
The information in the repository is used to generate the converted model, which is a representation of the structure of the destination database as it will be implemented in the Oracle database. You can then use the information in the captured model and the converted model to compare database objects, identify conflicts with Oracle reserved words, and manage the migration progress. When you are ready to migrate, you generate the Oracle schema objects, and then migrate the data.
See Also:Oracle SQL Developer User's Guide
This chapter describes how to perform the subsequent tasks that enable automatic run-time migration. The steps described in this chapter are for using SQL Translator with a JDBC application that is implemented to run against a Sybase database, but they can be easily adapted for other client/database configurations.
Note:Oracle SQL Developer is shipped with the Sybase translator installed.
Oracle SQL Developer 3.2 is shipped with Oracle Database 11g JDBC drivers and there is no client for Windows in the current release. If you are using a Windows system, then you must enable Oracle SQL Developer 3.2 to use Oracle Database 12c JDBC driver, so that all the features of the current release are enabled. Perform the following steps to achieve this:
sqldeveloper\jdbc\lib folder to
Create a new empty folder as
Unzip the Oracle Database 12c Release 1 JDBC JAR files into the new
See Also:Oracle Database JDBC Developer's Guide for more information about Oracle Database 12c Release 1 JDBC files
By default, Oracle SQL Developer automatically uses the JDBC drivers found in any
ORACLE_HOME\client directory. You must use the following commands to create a new
sqldeveloper.bat file in the
sqldeveloper directory to override this behavior and make Oracle SQL Developer use the JDBC drivers in the
set ORACLE_HOME=%CD% start sqldeveloper.exe
sqldeveloper.bat file to run Oracle SQL Developer. You must check the JDBC driver configuration in the following way:
Select About from Help menu.
Select Properties. It must display the configuration as shown in Figure 3-1:
Create a connection to the Database with the credentials as shown in Figure 3-2:
You can use the following command to check the database you are connected to and the JDBC driver being used:
You must set up the migration preferences in the following way:
Select Preferences from the Tools menu.
Select Generation Options from Migration option on the left panel, as shown in Figure 3-3.
Perform the following steps to determine whether Sybase SQL Translator is properly installed or not:
Open Oracle SQL Developer.
From the Tools menu, select Migration, and then select Translation Scratch Editor.
In the Scratch Editor toolbar, select Sybase T_SQL To PL/SQL option, which is the Sybase translator.
In the left panel of the Scratch Editor, enter the following query in Sybase SQL dialect:
select top 10 * from dual
Click the Translate icon.
The translated query text is displayed in the right panel of the editor.
Oracle SQL Developer is installed along with Oracle Database Release 12c and it loads the Java classes of the Sybase Translator, approximately 15 MB, into Oracle Database. Due to the size and the number of Java classes loaded, Oracle recommends you to install the translator locally, if possible, and not over a WAN.
If the translator is installed under a user profile that has a migration repository already, then the translator picks up the context of the database, such as name changes. Therefore, you must create a new user with the following specifications:
CREATE VIEW privileges
Access to storage in the
Perform the following steps to install the SQL Translator and create a translation profile:
Log into the database using
At the command line, enter the following commands.
GRANT CONNECT, RESOURCE, CREATE VIEW TO TranslUser identified by TranslUser; ALTER USER TranslUser QUOTA UNLIMITED ON SYSTEM;
From the View menu, select DBA.
In the DBA Navigator, right-click Connections and select Add Connection.
In the Select Connection box, select the connection if you want to use an existing connection. If you want to create a new connection, then add the information for
transluser discussed in step 2.
In the DBA navigator, right-click the connection created in the preceding steps, and select Install SQL Translator.
The Install SQL Translator dialog box opens.
Note:You must have special permissions to install the SQL Translator and create a SQL Translation Profile. You will be prompted to provide the
SYSpassword, so that these privileges can be granted. Refer to "Permissions Required for Installing the SQL Translator" for more information about these privileges.
Along with installing the SQL Translator, the Install SQL Translator dialog box also enables you to create a translation profile by performing the following steps:
From the SQL Translator drop-down box, select Sybase or SQL Translator.
Check Create New Profile.
SYBASE_PROFILE in Profile Name field.
In Profile Schema, select the name of the user created in section "Creating a Translation Profile and Installing a SQL Translator".
Verify that the user has sufficient privileges to run the translation profile.
You may need to login as
SYS user to grant additional privileges.
Install SQL Translator.
To ensure that both the Profile and Translator are properly installed, verify whether the appropriate package and Java class files are present or not in the Connections pane.
You can test the SQL Translation Profile by using SQL worksheet in the following way:
select Open SQL Worksheet with Profile.
Enter a T-SQL statement that you want to translate. For example,
SELECT TOP 4 * FROM ALL_TABLES.
SYBASE_PROFILE and select the SQL Translation tab to inspect the profile to see how the statement was translated.
You can view the profile SQL in a better way when you double-click on it, the fingerprint and template open in a Translation Scratch Editor as shown in the following images:
The following sections describe how to install and configure the SQL Translation Framework from the command line:
To install Oracle Sybase Translator, Use Oracle SQL Developer as described in "Installing and Configuring SQL Translation Framework with Oracle SQL Developer".
Perform the following steps to set up a SQL Translation Profile through a command-line interface:
Login as a
> sqlplus system/<password>
Grant create privileges to the standard user.
This allows the standard user to create a SQL Translation Profile.
SQL> grant create sql translation profile to <user>;
Login as a standard user.
Invoke the methods of
DBMS_SQL_TRANSLATOR PL/SQL package to create and configure the translation profile.
SQL> exec dbms_sql_translator.create_profile('sybase_profile') SQL> exec dbms_sql_translator.set_attribute('sybase_profile', dbms_sql_translator.attr_translator, 'migration_repo.sybase_tsql_translator')
Grant all privileges for the SQL Translation Profile to Oracle Sybase translation schema.
SQL> grant all on sql translation profile sybase_profile to migration_repo;
This section describes how to add a database service in a standard environment and in an Oracle Real Application Clusters environment.
Login as a DBA and give the following commands to use the
DBMS_SERVICE PL/SQL package to create and invoke the database service:
SQL> declare params dbms_service.svc_parameter_array; begin params('SQL_TRANSLATION_PROFILE') := 'user.sybase_profile'; dbms_service.create_service('sybase_service', 'network_name', params); dbms_service.start_service('sybase_service'); end; /
Add the database service:
srvctl add service -db db_name -service sybase_service -sql_translation_profile user.sybase_profile
Start the database service:
srvctl start service -db db_name -service sybase_service
Perform the following steps to test the translation:
Login as a standard user:
Specify the SQL Translation Profile at the SQL prompt:
SQL> alter session set sql_translation_profile = sybase_profile;
Force the database to treat SQL*Plus as a foreign SQL application:
SQL> alter session set events = '10601 trace name context forever, level 32';
Run a SQL query that uses Sybase SQL dialect. For example:
select top 3 * from emp;
The query returns the following results:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------------------------------------------------------------------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
This section discusses the privileges that you must have to install the SQL Translator. The profile created here is called the
SYBASE_PROFILE and you have the following two users:
MIGREP, where the translator will be installed
TARGET_USER, where the profile will be installed
First, you must connect as
SYS to grant the required privileges:
connect sys/oracle as sysdba
Now, you must grant the following privileges to:
MIGREP to create a view and have access to unlimited quota:
GRANT connect, resource, create view to MIGREP; ALTER USER MIGREP QUOTA UNLIMITED ON USERS;
TARGET_USER to create a view and have access to unlimited quota:
GRANT connect, resource, create view to TARGET_USER; ALTER USER MIGREP QUOTA UNLIMITED ON TARGET_USER;
MIGREP to load a SQL Translator:
BEGIN DBMS_JAVA.GRANT_PERMISSION(UPPER('MIGREP'), 'SYS:java.lang.RuntimePermission', 'getClassLoader', ''); END; /
TARGET_USER to create profiles:
GRANT CREATE SQL TRANSLATION PROFILE TO TARGET_USER;
TARGET_USER to explicitly alter the session to use a profile:
GRANT ALTER SESSION TO TARGET_USER;
Note:The preceding privilege is not granted in SQL Developer by default.
Allow the translator to make reference to the profile:
CONNECT TARGET_USER/TARGET_USER; GRANT ALL ON SQL TRANSLATION PROFILE SYBASE_PROFILE TO MIGREP;
Allow the profile to make reference to the translator:
connect MIGREP/MIGREP GRANT EXECUTE ON SYBASE_TSQL_TRANSLATOR TO TARGET_USER;