|
Hi, Can anyone recommend me any free tool to perform the migration from Oracle 8i to ASA9? |
|
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). 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.
(28 May '13, 09:51)
Walmir Taques
Replies hidden
Comment Text Removed
1
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...
(28 May '13, 13:51)
Breck Carter
Ah, memories... "check your tnsnames.ora" file http://www.dba-oracle.com/t_ora_12154_tns_resolve_service_name.htm
(28 May '13, 14:11)
Breck Carter
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.
(28 May '13, 14:44)
Walmir Taques
Replies hidden
Comment Text Removed
as shown in figure 2 ... error presented in Sybase Central.
(28 May '13, 14:58)
Walmir Taques
2
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
(28 May '13, 15:48)
Jeff Albion
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 :)
(28 May '13, 16:09)
Breck Carter
... 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.
(29 May '13, 10:47)
Jeff Albion
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.
(29 May '13, 11:48)
Walmir Taques
|
|
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) I'm more concerned with the data. Functions, triggers, with procedures already got all ready.
(28 May '13, 09:45)
Walmir Taques
@Breck Carter, Domain *How am I going to have to change one by one? ... I'm on the farm?
(28 May '13, 16:53)
Walmir Taques
Replies hidden
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.
(28 May '13, 17:00)
Breck Carter
"I'm on the farm" mean? is an expression used here. Mean -> I have much work to do :)!
(29 May '13, 09:52)
Walmir Taques
Replies hidden
Excellent... I'm going to start saying it myself.
(29 May '13, 14:15)
Breck Carter
|
|
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. What is the exact symptom? (error message, etc)
(28 May '13, 14:13)
Breck Carter
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?
(28 May '13, 16:39)
Walmir Taques
Replies hidden
The migration wizard should do everything for you. Are you having a problem?
(28 May '13, 17:02)
Breck Carter
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.
(05 Jun '13, 09:26)
Walmir Taques
Replies hidden
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)
(05 Jun '13, 15:14)
Breck Carter
I do not have the help file. :(
(05 Jun '13, 17:50)
Walmir Taques
Replies hidden
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.
(06 Jun '13, 03:06)
Volker Barth
Send me an email: breck dot carter at gmail dot com
(06 Jun '13, 07:13)
Breck Carter
|
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.