Helpful Moodlers,
I'm the technical lead for a Moodle evaluation project at Cornell University and we are trying to use Moodle with an Oracle backend.
I've found that Moodle/Oracle support is a bit sparse. I'm trying to find out:
1) Are there groups withing the Moodle community actively working on integrating Moodle with Oracle?
2) Could someone point me at a site (preferably a large site) that is using Moodle with Oracle so I could ask about the pros and cons?
3) Any advice for me? Can this work?
Thanks very much,
Ken Ridley
Cornell Information Technologies
kdridley@gmail.com
607.351.6209
I'm doing the same thing to evaluate the performance of Moodle with Oracle and I've already come up to a bit of an issue -
Tried to modify database, but ".../mod/book/db/oci8po.sql" doesn't exist!
None of the modules have php/sql files for oci8po, but I think we can mostly get away with renaming the Postgres files to oci8po. I'll be working on this tomorrow so I'll share more information when I've got it.
Tried to modify database, but ".../mod/book/db/oci8po.sql" doesn't exist!
None of the modules have php/sql files for oci8po, but I think we can mostly get away with renaming the Postgres files to oci8po. I'll be working on this tomorrow so I'll share more information when I've got it.
those .sql files have been replaced by the install.xml file in Moodle 1.7 and higher - they are still supported for 3rd party modules when the install.xml file doesn't exist, but all core modules use the install.xml
see: http://docs.moodle.org/en/Development:XMLDB_Documentation
see: http://docs.moodle.org/en/Development:XMLDB_Documentation
"Tried to modify database, but ".../mod/book/db/oci8po.sql" doesn't exist!"
That was during a 1.8.5+ install and copying over the Postgres php/sql files is not a valid solution. I retried with 1.9.4+ and the install went through without a problem.
The only issue is "ADODB Error: ORA-12541: TNS:no listener" but that is probably an issue with our setup and not Moodle/AdoDB support of Oracle.
That was during a 1.8.5+ install and copying over the Postgres php/sql files is not a valid solution. I retried with 1.9.4+ and the install went through without a problem.
The only issue is "ADODB Error: ORA-12541: TNS:no listener" but that is probably an issue with our setup and not Moodle/AdoDB support of Oracle.
we have a couple of clients using Oracle as their backend DB and have found it to be slow and cumbersome.
Also there aren't a lot of people running it yet, so they are constantly running into small bugs with table names/column names/invalid Sql that other people aren't affected by. - Especially in 3rd party modules. (this is improving and we file bug reports and push upstream where we can)
Ideally if you have the option to use PostGres (or MySql) you should do that! - If you don't have the option, you should try and make it an option. If support of something other than Oracle is an issue internally you could contract a local Moodle Partner to help you support the architecture
hope that helps!
Also there aren't a lot of people running it yet, so they are constantly running into small bugs with table names/column names/invalid Sql that other people aren't affected by. - Especially in 3rd party modules. (this is improving and we file bug reports and push upstream where we can)
Ideally if you have the option to use PostGres (or MySql) you should do that! - If you don't have the option, you should try and make it an option. If support of something other than Oracle is an issue internally you could contract a local Moodle Partner to help you support the architecture
hope that helps!
To extend on what Dan said:
I think you can tell that Oracle has had a monopoly at the high end of database market for too long, and as a result, they have a rather arrogant view that they can do whatever they like, and who cares about the ANSI SQL standard.* As a result, Oracle is the hardest of the common databases to have to support in a product like Moodle that has to work cross-database.
Add to that the fact that Oracle is expensive, so few people use it, and so it gets less testing that other database we support, and that explains why Moodle on Oracle is the flakiest combination. However, we would like to find and squish all the Oracle bugs, and normally the hard part of that is finding. Once you realise that a certain query does not work on Oracle, it is easy to use our guidelines about the safe subset of SQL, and our compatibility methods, to rewrite the query.
So, as more people with developers on hand start using Oracle, it will get better.
On the other hand, unless your institution is seriously committed to a single database strategy, and really only has Oracle expertise** and nothing else, it is worth considering Postgres or MySQL.
* I suppose a more charitable alternative view is to say that Oracle pioneered a lot of database features, then ANSI came along later and standardised them in a different way, at which point Oracle could only choose between breaking backwards compatibility and annoying it existing customers, or changing to support the standard.
** The other Oracle conspiracy theory is that the reason Oracle is so difficult to administer is that Oracle DBAs are paid huge salaries for their expertise. As a result, they always recommend Oracle and nothing else. Therefore, Oracle the company keeps the database administration arcane, so DBAs can have job security and will continue to recommend Oracle to their emplyers
I think you can tell that Oracle has had a monopoly at the high end of database market for too long, and as a result, they have a rather arrogant view that they can do whatever they like, and who cares about the ANSI SQL standard.* As a result, Oracle is the hardest of the common databases to have to support in a product like Moodle that has to work cross-database.
Add to that the fact that Oracle is expensive, so few people use it, and so it gets less testing that other database we support, and that explains why Moodle on Oracle is the flakiest combination. However, we would like to find and squish all the Oracle bugs, and normally the hard part of that is finding. Once you realise that a certain query does not work on Oracle, it is easy to use our guidelines about the safe subset of SQL, and our compatibility methods, to rewrite the query.
So, as more people with developers on hand start using Oracle, it will get better.
On the other hand, unless your institution is seriously committed to a single database strategy, and really only has Oracle expertise** and nothing else, it is worth considering Postgres or MySQL.
* I suppose a more charitable alternative view is to say that Oracle pioneered a lot of database features, then ANSI came along later and standardised them in a different way, at which point Oracle could only choose between breaking backwards compatibility and annoying it existing customers, or changing to support the standard.
** The other Oracle conspiracy theory is that the reason Oracle is so difficult to administer is that Oracle DBAs are paid huge salaries for their expertise. As a result, they always recommend Oracle and nothing else. Therefore, Oracle the company keeps the database administration arcane, so DBAs can have job security and will continue to recommend Oracle to their emplyers
hi there, please be kind to a non native english speaker 
1) I would also like to know, if there is any more progress towards making moodle perform (better) with oracle in moodle 2.0 as Mr. Langhoff said in:
http://moodle.org/mod/forum/discuss.php?d=87509
by the way, we met Mr. Dougiamas at the german moodlemoot
unfortunately (for us) he mentioned, that he personally only knows about three moodle-oracle installations (well, including ours ) and that because of the refactoring of the moodle 2.0 database access layer he doesn't know, if moodle 2.0 and oracle will work together at all...
It seemed, that there is no one left (at least in the moodle core team?) he knows of, who has control over an oracle database for development purposes...
@Mr. Marsden and Mr. Hunt: would you agree with Mr. Langhoff or Mr. Dougiamas?
2) we had to use oracle as our database since 1.7...in 1.9 we now have 21.000users, 50-100concurrent, 600courses...
3) well, it can...but don't use third party plugins, expect some oracle-sql-bugs (only a few in 1.9!) and change the clob in _config-table to varchar (see link above)
1) I would also like to know, if there is any more progress towards making moodle perform (better) with oracle in moodle 2.0 as Mr. Langhoff said in:
http://moodle.org/mod/forum/discuss.php?d=87509
by the way, we met Mr. Dougiamas at the german moodlemoot
unfortunately (for us) he mentioned, that he personally only knows about three moodle-oracle installations (well, including ours
It seemed, that there is no one left (at least in the moodle core team?) he knows of, who has control over an oracle database for development purposes...
@Mr. Marsden and Mr. Hunt: would you agree with Mr. Langhoff or Mr. Dougiamas?
2) we had to use oracle as our database since 1.7...in 1.9 we now have 21.000users, 50-100concurrent, 600courses...
3) well, it can...but don't use third party plugins, expect some oracle-sql-bugs (only a few in 1.9!) and change the clob in _config-table to varchar (see link above)
Making Moodle 2.0 work on Oracle is not terribly difficult. It is just that someone needs to finish writing (and test) the oci_native_moodle_database and oci_native_moodle_recordset classes in lib/dml/oci_native_moodle_database.php.
(If would, of course, be helpful if someone volunteered either the developer time, or money, to get this done.)
One good thing about the new DB layer in Moodle 2.0 is that it means that all the Oracle-specific code will then be in those two classes, which should make it easier to see what is going on, and, with luck, find ways to make it better.
But fundamental problems remain, which the Oracle code in Moodle has to work around, the two worst are probably:
(If would, of course, be helpful if someone volunteered either the developer time, or money, to get this done.)
One good thing about the new DB layer in Moodle 2.0 is that it means that all the Oracle-specific code will then be in those two classes, which should make it easier to see what is going on, and, with luck, find ways to make it better.
But fundamental problems remain, which the Oracle code in Moodle has to work around, the two worst are probably:
- Oracle does not seem to be able to tell the difference between an empty string and NULL.
- CLOB handling in the PHP drivers is bad.
Moodle's install.php -
if ($INSTALL['dbtype'] == 'oci8po' && strlen($INSTALL['prefix']) > 2) { // Oracle max prefix = 2cc (30cc limit)
$errormsg = get_string('dbwrongprefix', 'install');
$nextstage = DATABASE;
}
Doesn't the max character limit only apply to older versions of Oracle (9i)? Limiting the prefix to 2 characters is not an ideal solution to this issue. Can it become a warning instead?
if ($INSTALL['dbtype'] == 'oci8po' && strlen($INSTALL['prefix']) > 2) { // Oracle max prefix = 2cc (30cc limit)
$errormsg = get_string('dbwrongprefix', 'install');
$nextstage = DATABASE;
}
Doesn't the max character limit only apply to older versions of Oracle (9i)? Limiting the prefix to 2 characters is not an ideal solution to this issue. Can it become a warning instead?
You probably know more about it than me. Perhaps you could file an issue in the Moodle tracker with the relevant information. Thanks.
> CLOB handling in the PHP drivers is bad.
I thought that as well. And then I spent a few weeks looking at the CLOB problem in depth. The problem is not in the driver, it's in the TCP/IP protocol.
No amount of fiddling with the PHP/Oracle driver helped. The protocol specifies that every individual CLOB field sent over the wire wants to go in a separate TCP packet, and the next one ain't sent until the ACK is received.
That's why Oracle is missing in the modern web CMS market. They are all textfield based (mysql, pg excel at this), and Oracle belongs to an earlier generation of DB, with a more number-crunching focus.
I thought that as well. And then I spent a few weeks looking at the CLOB problem in depth. The problem is not in the driver, it's in the TCP/IP protocol.
No amount of fiddling with the PHP/Oracle driver helped. The protocol specifies that every individual CLOB field sent over the wire wants to go in a separate TCP packet, and the next one ain't sent until the ACK is received.
That's why Oracle is missing in the modern web CMS market. They are all textfield based (mysql, pg excel at this), and Oracle belongs to an earlier generation of DB, with a more number-crunching focus.
To clarify: the problem is the Oracle-specific protocol on top of TCP/IP. "SQL*Net" or "Ora*Net" I think they call it.
I'm having a strange issue where all page requests take at least 7 seconds to finish but querying the db directly through sqlplus on the web server doesn't have the problem.
Any ideas?
Any ideas?
@Martín
would you advise us to switch our oracle-moodle to mysql or postgresql (at least for moodle 2.x)?
would you advise us to switch our oracle-moodle to mysql or postgresql (at least for moodle 2.x)?
A quick search of this forum will tell you the answer
About the migration of data from Mysql to Oracle, is there a tool to help migrating the data from mysql to Oracle ?
Oracle's SQL Developer will do it all for you, including moving over MySQL users.
you could probably also use the Database transfer script in Moodle 2.0 (but you would probably be the first to go from mySql to Oracle and might find a few issues)
see:
http://docs.moodle.org/en/Hacking_the_Moodle_2.0_database_transfer_script_to_convert_a_Moodle_1.9_site
see:
http://docs.moodle.org/en/Hacking_the_Moodle_2.0_database_transfer_script_to_convert_a_Moodle_1.9_site