Thursday, April 5, 2007

Dynamics Ax 4.0 Database upgrade for Oracle

Before starting the upgrade process do the following activities:

Create an empty Oracle 10g database. Microsoft dynamics will support Oracle 10g database only. While creating the database log in as an Windows Administrator
Use the following SQL statement for creating new user in Oracle 10g

CREATE USER "OPS$\" IDENTIFIED EXTERNALLY;
Grant rights to the user "OPS$\" as given below :
GRANT ALL PRIVILEGES, SELECT ANY DICTIONARY TO “OPS$\";
For upgrading the existing database, create the tablespace in Oracle 10g with the same name as in oracle 9. For New Installation create 3 tablespaces (AXTAB,AXIDX,AXTMP) as given below:

CREATE TABLESPACE AXTAB DATAFILE'D:\ORACLE\ORADATA\AXDB\AXTAB01.DBF' SIZE 3000M BLOCKSIZE 8192;CREATE TABLESPACE AXIDX DATAFILE'D:\ORACLE\ORADATA\AXDB\AXIDX01.DBF' SIZE 2000M BLOCKSIZE 8192;CREATE TEMPORARY TABLESPACE AXTMP TEMPFILE'D:\ORACLE\ORADATA\AXDB\AXTMP01.DBF' SIZE 1000M;COMMIT;
4.1. Create a schema with the name DBO.

There are two xpo files that need to be imported into your 3.0 client and run against the old 9i database as per steps below:

Import PrivateProject_UpgradeColumnList.xpo from the SP1 DatabaseUpgrade folder into your 3.0 system a. Open the form UpgradeColumnList from the imported project b. Click on the button Generate tables UPGRADECOLUMNS and UPGRADETABLES are created in your 3.0 db. They contain fields that need to be updated from int to int64


To trim the right justified fields: Import PrivateProject_LeftJustified.xpo from the SP1 DatabaseUpgrade folder into your 3.0 system c. Run the job MainJob from the imported project opens a form d. Click “Left justify all” and wait until it’s done

If you are running a Oracle version for Windows, It is recommended running the DB Upgrade tool on the same machine where Oracle 10g is installed using the Administrator account.

On the machine where you are running the DB upgrade tool, verify if you can connect to the Oracle 9 by testing it using the ODBC wizard. This is to make sure that there is no problem accessing the Oracle 9 database from that machine.

After completing the above steps, run the DB upgrade tool from the Dynamics 4.0 DVD. The explanation of the fields available in the oracle tab is given below

Source

· Source connect string: Type connection information for the source database (Oracle 9) in the format Server Name/SID.
· Schema owner: Type the name of the schema under which the database objects were created.
· User Name: Type the name of a user who has access to the source database.
· Password: Type the password of a user who has access to the source server.
Target

· Target connect string: Type connection information for the target database, where the new, preliminary Microsoft Dynamics AX database is stored, in the format Server Name/SID
· Use this schema: Type the schema name (DBO by default) for the new, preliminary Microsoft Dynamics AX database

No comments: