Oracle XE 11.2. and MariaDB 10.1 integration on Ubuntu 14.04 and Debian systems
1) Sign up for Oracle downloads and download Oracle Express at:
- Sign up (unless already) and log in. - Accept the license agreement. - Download Oracle Database Express Edition 11g Release 2 for Linux x64 (oracle-xe-11.2.0-1.0.x86_64.rpm.zip, version numbers may change over time)
2) Prepare apt-get on your system
sudo add-apt-repository ppa:webupd8team/java <press Enter to accept> sudo apt-get update sudo apt-get install oracle-java8-installer
3) After Java installation, verify the version
java -version java version "1.8.0_121"
4) Edit /etc/bash.bashrc
Scroll to the bottom of the file and add the following lines.
export JAVA_HOME=/usr/lib/jvm/java-8-oracle export PATH=$JAVA_HOME/bin:$PATH
Save and check:
source /etc/bash.bashrc echo $JAVA_HOME /usr/lib/jvm/java-8-oracle
5) Additional packages are required, unless installed already. Run the command:
sudo apt-get install alien libaio1 unixodbc
6)
unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip cd Disk1/ sudo alien --scripts -d oracle-xe-11.2.0-1.0.x86_64.rpm
This step might take some time. You may proceed steps 7)-11) in the meanwhile in another terminal window.
7)
Create a new file /sbin/chkconfig and add the following contents:
#!/bin/bash # Oracle 11gR2 XE installer chkconfig for Ubuntu file=/etc/init.d/oracle-xe if [[ ! `tail -n1 $file | grep INIT` ]]; then echo >> $file echo '### BEGIN INIT INFO' >> $file echo '# Provides: OracleXE' >> $file echo '# Required-Start: $remote_fs $syslog' >> $file echo '# Required-Stop: $remote_fs $syslog' >> $file echo '# Default-Start: 2 3 4 5' >> $file echo '# Default-Stop: 0 1 6' >> $file echo '# Short-Description: Oracle 11g Express Edition' >> $file echo '### END INIT INFO' >> $file fi update-rc.d oracle-xe defaults 80 01 #EOF
8)
sudo chmod 755 /sbin/chkconfig
9)
Create a new file /etc/sysctl.d/60-oracle.conf
Copy and paste the following into the file. Kernel.shmmax is the maximum possible value of physical RAM in bytes. 536870912 / 1024 /1024 = 512 MB.
#Oracle 11g XE kernel parameters fs.file-max=6815744 net.ipv4.ip_local_port_range=9000 65000 kernel.sem=250 32000 100 128 kernel.shmmax=536870912
10)
sudo service procps start sudo sysctl -q fs.file-max
- This method should return the following: fs.file-max = 6815744
11) Some additional steps required
sudo ln -s /usr/bin/awk /bin/awk mkdir /var/lock/subsys touch /var/lock/subsys/listener
12) Install Oracle XE (should have been converted from .rpm to .deb by now)
sudo dpkg --install oracle-xe_11.2.0-2_amd64.deb
13)
Execute the following to avoid getting a ORA-00845: MEMORY_TARGET error. Note: replace "size=4096m" with the size of your (virtual) machine RAM in MBs.
sudo rm -rf /dev/shm sudo mkdir /dev/shm sudo mount -t tmpfs shmfs -o size=4096m /dev/shm
14) Create the file /etc/rc2.d/S01shm_load
NOTE: replace "size=4096m" with the size of your machine RAM in MBS.
#!/bin/sh case "$1" in start) mkdir /var/lock/subsys 2>/dev/null touch /var/lock/subsys/listener rm /dev/shm 2>/dev/null mkdir /dev/shm 2>/dev/null mount -t tmpfs shmfs -o size=4096m /dev/shm ;; *) echo error exit 1 ;; esac
sudo chmod 755 /etc/rc2.d/S01shm_load
15) Configure Oracle 11g R2 Express Edition. Default answers are probably OK.
sudo /etc/init.d/oracle-xe configure
Specify the HTTP port that will be used for Oracle Application Express [8080]:
Specify a port that will be used for the database listener [1521]:
Specify a password to be used for database accounts. Note that the same password will be used for SYS and SYSTEM. Oracle recommends the use of different passwords for each database account. This can be done after initial configuration:
Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:
16) Edit /etc/bash.bashrc. Add to the end of the file:
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe export ORACLE_SID=XE export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh` export ORACLE_BASE=/u01/app/oracle export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export PATH=$ORACLE_HOME/bin:$PATH
Save.
17) Run source command and check that the output makes sense
source /etc/bash.bashrc echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/xe
18) Apply desktop icon changes and start Oracle:
sudo chmod a+x ~/Desktop/oraclexe-gettingstarted.desktop sudo service oracle-xe start
19) Download SQL Developer package
Download Oracle SQL Developer from the Oracle site. Select the Linux RPM package: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Choose the Linux RPM package.
20) Install SQL Developer
sudo alien --scripts -d sqldeveloper-4.1.5.21.78-1.noarch.rpm sudo dpkg --install sqldeveloper_4.1.5.21.78-2_all.deb mkdir ~/.sqldeveloper
21) Run sqldeveloper:
sudo /opt/sqldeveloper/sqldeveloper.sh
- Tell sqldeveloper the correct Java path, if it asks for it:
/usr/lib/jvm/java-8-oracle
- Click connections - Add new connection - Connection name: XE - username: SYSTEM - password: <your-password> - Connection type: Basic Role: Default - Hostname: localhost - Port: 1521 - SID: xe
#
MariaDB ##
22) Install MariaDB
https://downloads.mariadb.org/mariadb/repositories/ Instructions are for Ubuntu, but choose the one that is appropriate: - Ubuntu - 14.04 LTS "trusty" - 10.1 [Stable] - choose a mirror
Run the commands given for you. For example (DO NOT COPY PASTE BELOW, CHECK WHAT THE MariaDB PAGE TELLS YOU TO DO):
sudo apt-get install software-properties-common sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://mirror.netinch.com/pub/mariadb/repo/10.1/ubuntu trusty main' sudo apt-get update
- Install mariadb-server:
sudo apt-get install mariadb-server
23) Install ODBC driver
https://downloads.mariadb.org/connector-odbc/
- MariaDB Connector/ODBC 2.0.13 Stable for Linux
Download: mariadb-connector-ODBC-2.0.13-ga-linux-x86_64.tar.gz
tar xfz mariadb-connector-odbc-2.0.13-ga-linux-x86_64.tar.gz sudo cp -p mariadb-connector-odbc-2.0.13-ga-linux-x86_64/lib/libmaodbc.so /lib sudo ldconfig
24) Install unixodbc and mariadb-connect engine
apt-get install unixodbc-dev apt-get install unixodbc-bin apt-get install unixodbc apt-get install libodbc1 apt-get install mariadb-connect-engine-10.1
25) Edit /etc/odbcinst.ini
Add:
[Oracle ODBC driver for Oracle 11.2] Description = Oracle 11.2 ODBC driver Driver = /u01/app/oracle/product/11.2.0/xe/lib/libsqora.so.11.1
26) Edit /etc/odbc.ini
Add (check your password):
[XE] Driver = Oracle ODBC driver for Oracle 11.2 ServerName = //localhost:1521/xe DSN = XE UserName = SYSTEM Password = <your-password>
27) Test ODBC connection and add a table
isql -v XE SYSTEM <your-password> create table t1 (i int); insert into t1 (i) values (1); insert into t1 (i) values (3); insert into t1 (i) values (5); insert into t1 (i) values (8); select i from t1;
And you should see the rows. You can test the same with sqldeveloper, open XE connection and run select i from t1; in Worksheet.
28) Edit /etc/init.d/mysql
Add:
export JAVA_HOME=/usr/lib/jvm/java-8-oracle export PATH=$JAVA_HOME/bin:$PATH export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe export CLIENT_HOME=$ORACLE_HOME export ORACLE_SID=XE export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh` export ORACLE_BASE=/u01/app/oracle export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export PATH=$ORACLE_HOME/bin:$PATH
Right after END INIT INFO. Otherwise mysqld will not find the Oracle ODBC driver.
28) Restart MariaDB
sudo /etc/init.d/mysql restart
29)
mysql -uroot -p CREATE DATABASE mdb; USE mdb; INSTALL SONAME 'ha_connect'; CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC tabname='T1' CONNECTION='DSN=XE;UID=SYSTEM;PWD=<your-password>'; select I from t1;
You should see the previously inserted values 1,3,5 and 8. Using isql or sqldeveloper, add another rows with values 9 and 11. Remember to commit, if you are using Oracle sqldeveloper. You should now see the added values via MariaDB client (mysql-client) connection.
To be examined: inserting values to the t1 table from mariadb connection does not work. It gives a precision error from Oracle side.
Connect to MariaDB via JDBC
Download MySQL Connector from http://dev.mysql.com/downl0.8.oads/connector/j/5.0.html
Select Version (for example 5.0.8)
Platform independent. Download mysql-connector-java-5.0.8.tar.gz
tar xvfz mysql-connector-java-5.0.8.tar.gz cd mysql-connector-java-5.0.8/ sudo cp -p mysql-connector-java-5.0.8-bin.jar /usr/lib/jvm/java-8-oracle/lib/mariadb/ sudo /opt/sqldeveloper/sqldeveloper.sh
In SQL Developer choose Tools -> Preferences
Expand the "Database" option in the left hand tree
Click on "Third Party JDBC Drivers"
Click on "Add Entry..."
Navigate to your third-party driver jar file and choose OK
/usr/lib/jvm/java-8-oracle/lib/mariadb/mysql-connector-java-5.0.8-bin.jar
Click Connections -> New connection.
Add values. The following are examples:
Connection Name: MariaDB via MySQL Conn Username: root Password: ******** Save Password: [x] Choose MySQL tab Hostname: localhost Port: 3306 Click "Test Connection". It should says Status: Success. Click Save. Click Connect.
You are connected. You may run commands in the Worksheet. For example:
use mdb;
show tables;
You should see the tables.