Oracle XE 11.2. and MariaDB 10.1 integration on Ubuntu 14.04 and Debian systems

You are viewing an old version of this article. View the current version here.

1) Sign up for Oracle downloads and download Oracle Express at:

http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

- 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:

  1. !/bin/bash
  2. Oracle 11gR2 XE installer chkconfig hack for Ubuntu file=/etc/init.d/oracle-xe if 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
  3. 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.

  1. 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

  1. 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)

  1. Execute the following to avoid getting a ORA-00845: MEMORY_TARGET
  2. error. Note: replace "size=4096m" with the size of your (virtual)
  3. 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

  1. NOTE: replace "size=4096m"
  2. with the size of your machine RAM in MBS.
  1. !/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

  1. Tell the correct Java path, if sqldeveloper 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

  1. Install mariadb-server:

sudo apt-get install mariadb-server

23) Install ODBC driver

https://downloads.mariadb.org/connector-odbc/

  1. 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=**'; select I from t1;

  1. You should see the previously inserted values 1,3,5 and 8.
  2. Using isql or sqldeveloper, add another rows with values 9 and 11.
  3. Remember to commit, if using sqldeveloper. You should now see
  4. the added values via mariadb connection.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.