I have a legacy application with new modules and some changes to Moodle's core, I have to migrate it from postgres to Oracle 11g (customer's request, so cannot stay using postgres).
What's the best way of doing the migration process?
1) migrate the database from postgres to oracle and change Moodle's config?
2) install a new Moodle on oracle and migrate modules and changes made to the core?
3) anything you would like to propose
Thanks.
I'm using Moodle 2.3 (Build: 20120625) and cannot find the Database Transfer experimental tool. Watching this post seems that it's available only for 2.3.2 and 2.4.
https://tracker.moodle.org/browse/MDL-34441
http://docs.moodle.org/24/en/Database_transfer
If you truly want to help your client, you talk them out of it, in the same way that if you see someone about to jump off a bridge, you don't help them.
that decission does not depend on me, if so i would have convinced them to use postgres...but there's no way back
Well, probably the first thing to do is to make a fresh install of Moodle + Oracle on a test server, and test:
just to back up what Tim says about testing ... we ran our Moodle 1.9 on Oracle for 2 years (now moving to M2 on MySQL) and have had a catalogue of issues with third party code not being written robustly enough for Oracle - which unless you have your own developers is going to be a right pain. Perhaps there is a good business case for Oracle (in-house expertise) etc but we managed to convince our DB team to adopt MySQL as well and it's been a breeze so far.
Michael "[we] have had a catalogue of issues with third party code not being written robustly enough for Oracle..."
Obviously you can't expect 3rd-party developers developing add-ons for Moodle to have at their disposal all of the database systems currently available, especially paying ones. I would expect MySQL to be the database of choice with small-time add-on developers (like me).
Joseph
Developers can download Oracle for free using a developer license: http://www.oracle.com/technetwork/indexes/downloads/index.html
I would expect developers who are contributing their code, to write according to Moodle coding standards. A lot of our problems were caused by developers using inline SQL instead of built in cross database functions.
And we are not just talking about 'small-time add on developers'. One particular large commercial organisation, includes the AS keyword (for table alias) in their integration with every new release. We have to request a fix every time.
Regardless of the reason for the problem, the point Mike is making is valid. A lot of developers do not test or even consider Oracle users. This makes Oracle an expensive choice in terms of support. You will also find it rare to see a solution or discussion for many of the bugs you encounter, in the forums or the Tracker.
To be fair, there are a lot of situations which call for gathering data from a large number of tables at once, via JOINS, etc. that would be very slow and inefficient if written using multiple calls to the DB functions instead of hand-coded SQL. What I am trying to say is that many times inline SQL is entirely justified.
That does, of course, make it more difficult to maintain code that works across all database engines (especially when considering some of the rarely-used ones such as Oracle). Maybe it would be worth adding a setting somewhere in Moodle that would automatically scan all executed SQL queries for common cross-DB compatibility issues (but then, apart from 'AS', I'm not really aware of what the other Oracle 'quirks' are)?
No one is saying don't hand-code SQL. They are saying follow the coding guidelines so that it works on all DBs. The do do that all the time and never make a mistake is hard.
And installing Oracle (for Moodle development) is too much of a pain. I have never done it and I don't intend to. Call it an irrational prejudice agains overly expensive closed-source software if you like. You would be almost right. Just that it is rational
You might find http://sqlfiddle.com/ quite useful for checking compatibility against Oracle without installing it (although its a small pain to create the schema in that, and i've never used it in anger).
Excellent link Dan. For anyone who has not come accross the fiddle style sites, you get a chance to experiment with various language constructs from within a browser without having to do much/any configuration setup. There are also jsfiddle and phpfiddle sites.
Oracle on windows is quite easy to install (just a click next installation), you can also download VirtualBox images with Oracle bundled in them, so for testing / developing purposes you won't be in trouble.
A different matter is installing Oracle on Linux, quite a tedious and problematic proccess.
I agree entirely Davo. The intention here is not to criticise other coders (I would not dare!). Just to speak from our experience of using Oracle with Moodle. As it may be of use to anyone who does decide to use Oracle, the main issues we had were:
1. Use of the the AS keyword in table alias's.
2. Use of clob type fields in SQL containing DISTINCT in the SELECT statement, or LIKE in the WHERE statement.
3. Case sensitivity not matching the behaviour in MySQL (specific to our version of Oracle).
4. Null fields.
We only used Oracle with 1.9 so some of the above problems may be fixed now. I'm also happy to provide more detail and the solutions we deployed if required.
3. is a bug in MySQL. Oracle and Postgres get that one right. (It is still something that developers need to be aware of.)
I'm a small time (tiny time?) developer of moodle code. I've done Oracle/PLSQL for a living but I won't be testing my code against Oracle any time soon*. This is because MySQL is the de-facto standard for Moodle and the type of low cost hosting that I use. I try to stick to the Moodle standards so in theory the code should work on any of the supported databases.
In theory there is no difference between theory and practice. However I have noticed that in practice there is. Rumour has it that Oracle people may feel more comfortable with Postgres than MySQL.
(*Obviously I would do any testing/developing you wanted on Oracle for money )
Agree with Amanda,
After installing/migrating some moodles on Oracle I'd like to point out a couple of things:
- When exporting / importing a moodle db in Oracle, CLOB fields can be a pain in the ass if using a SQL script, I've had to convert them to another type before importing the SQL file, and once imported converting them again to CLOB.
- When importing SQL files SqlPlus can give problems with large sql sentences (inserts with long strings), so stick to SqlDeveloper. And whenever possible use Oracle's dump tools: impdp and expdp (faster and safer)
- SCORMS packages with a big number of files on Oracle can drawback performance at least on Moodle 2.2 version. We've improved performance in 10x faster just using a single postgres virtual server vs oracle racc. So whenever possible use postgres over oracle
