Migration Oracle to ASA9

[former_SQLA_member1694872]

Hi, Can anyone recommend me any free tool to perform the migration from Oracle 8i to ASA9?

[VolkerBarth]

Have you tried to use the "Migrate Database Wizard" (available in v10 and above, not sure w.r.t. v9) or the according sa_migrate() system procedure?

Here's the according v12 doc page link.

[Breck_Carter]

Regardless of the tool, free or otherwise, pay CLOSE ATTENTION to the data types after the migration. There are many fundamental differences at the physical level, and which SQL Anywhere data type you choose (in SOME cases) depends on how the data is being used by your application.

Data types are often chosed because they are "plenty good enough" for a particular column. That's fine, but after migration, the generated data type might be "not quite good enough".

If you care about your data (and it's OK to not care, if the data's not important), there is no substitute manually ... checking ... each ... and ... every column data type.

Plus, if you don't completely understand all the Oracle and SQL Anywhere data types involved, either learn (they're all documented) or find someone who does understand.

(and good luck with the triggers and procedures, but that's a whole different conversation)

[former_SQLA_member1694872]

I'm more concerned with the data. Functions, triggers, with procedures already got all ready.

[former_SQLA_member1694872]

@Breck Carter, Domain

*How am I going to have to change one by one? ... I'm on the farm?

[Breck_Carter]

I don't understand what you are asking. What does "I'm on the farm" mean?

I didn't say you have to change them one by one, I said you have to CHECK THEM after migration.

...or, don't bother, your call.

[former_SQLA_member1694872]

"I'm on the farm" mean? is an expression used here. Mean -> I have much work to do :)!

[Breck_Carter]

Excellent... I'm going to start saying it myself.

[MarkCulp]

First, I would suggest that you use a newer version of SQL Anywhere. ASA 9 has been EOL'ed for many years. The current version is SA 16.

Have you looked at the Database Migration Wizard? The SQL Anywhere migration tools can help you move your data from Oracle into a SQL Anywhere database, but cannot help you migrate any stored procedures or triggers, etc. (I doubt you will find any tool to do the latter since the languages used by the two DBMSes are different).

[former_SQLA_member1694872]

In the 3951 version of ASA9. Try the option of migrating but could not connect to oracle via odbc, is showing below error message:

ORA-12154: Unable to resolve the name of the service

Is that because I'm using a virtual machine where the oracle database is started?

*** Already connected by another station on the network and connected in oracle normally.

[Breck_Carter]

Do you have an ODBC DSN that works with Oracle, on the computer that you are using to run the migration? If not, get one 🙂

That ORA error is a common "can't connect" message and really has nothing to do with the migration wizard or ASA... that's not finger-pointing, it's just a clue as to where the problem lies. FWIW the migration wizard uses the proxy tables / remote server feature, and that means "ODBC" (which never works well with Oracle).

Yes, it certainly could have something to do with the VM... I've never understood why VMs are used for databases, but I don't understand a lot of things these days 🙂

This screenshot from 2007 may not help because it's using the SQL Anywhere 10 version of the ODBC driver, but here it is anyway...

[Breck_Carter]

Ah, memories... "check your tnsnames.ora" file

http://www.dba-oracle.com/t_ora_12154_tns_resolve_service_name.htm

[former_SQLA_member1694872]

I checked the TNSNames.ora everything is ok. But it does not connect. When testing the connection in odbc works: Connection sucessfull.

but when trying to use via Sybase Central displays the error Ora-12154.

[former_SQLA_member1694872]

as shown in figure 2 ... error presented in Sybase Central.

[jeff_albion]

This is Oracle's supplied ODBC driver, which we traditionally have had issues using as a server proxy. We have had many reports of server crashes when trying to use Oracle's ODBC driver for migration purposes (due to the instability/bugs in the Oracle ODBC driver).

To avoid this, you will need to be on build 9.0.2.3508 or higher in order to obtain the corect "iAnywhere driver for Oracle" compatible ODBC driver. See CR #472239. You will then need a compatible Oracle client library installed, with a correct ORACLE_HOME (and optional TNS_ADMIN) environment variable set pointing to the the Oracle client libraries and TNS configuration (and optional location for the tnsadmin.ora configuration).

You should then use this new ODBC source as the remote Oracle server definition.

See: http://dcx.sybase.com/index.html#1001/en/dbmlen10/ml-ml-drivers-s-3857187.html

[Breck_Carter]

He's using 9.0.2.3951, so it should just be a matter of creating a DSN with the right driver, correct? (plus all that other stuff you mention 🙂

[jeff_albion]

... ummm, yes - I may have missed the version number in the back and forth. Thanks Breck, and sorry about that Walmir! You should have the ODBC driver already, you just need to use the other ODBC driver when creating the DSN.

My other recommendation is to ensure that you match the bitness (x86 or x64) of the database server, ODBC driver, and Oracle client driver.

ORA-12154 basically means that the Oracle driver you're referencing can't find the tnsnames.ora entry for your SID.

[former_SQLA_member1694872]

Ok @ Jeff,

Already I created another dsn. Directly on the server (the VM) managed to connect Sybase Central in oracle. But from a Windows 7 can not (already reinstalled oracle client 8i) ... I will continue to work directly on VM.

[former_SQLA_member1694872]

Young, So, tested here and Migration Database option does not work.

At the time I took a break to resume this work so I'll post here again.

[Breck_Carter]

What is the exact symptom? (error message, etc)

[former_SQLA_member1694872]

I managed to connect to oracle database :) And we created tables and proxy. Proxy that contains the data. Is to pass the data directly to the table?

[Breck_Carter]

The migration wizard should do everything for you.

Are you having a problem?

[former_SQLA_member1694872]

I had never used the migration wizard. It should load all the data from the proxy to the tables?

At the time I took a break to resume this work so I'll post here again.

[Breck_Carter]

Yes. The migration wizard was added in SQL Anywhere 7.0.2 so by version 9 it was pretty solid. You can run it in one step to do everything: create tables, copy data, then even drop the proxy tables. Or you can call the sa_migrate* stored procedures yourself and modify stuff in between the steps if you want to make changes to the schema (get rid of some foreign keys, for example). It is all described in the Help... do you have the Help?

My earlier comment about checking the data types is something you can do after migration. If you see something funky, you may be able to ALTER the tables. At worst, you have to re-migrate a table or two, but you can probably do that by hand. At best, you don't have to do anything (which can happen if the Oracle database is straightforward; i.e., not guru-created)

[former_SQLA_member1694872]

I do not have the help file. 😞

[VolkerBarth]

See my old comment right beneath your original question. It's the link to the DCX site that contains the online docs for SQL Anywhere 10.0.1 and above.

For 10.0.1, you may start here.

Note: I don't know whether the Wizard works exactly that way in v9, but you might gite it a try.

[Breck_Carter]

Send me an email: breck dot carter at gmail dot com