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

Revision: 106952
User: Jani
Date: 2017-06-26 17:34

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

<<code>>
sudo add-apt-repository ppa:webupd8team/java
<press Enter to accept>
sudo apt-get update
sudo apt-get install oracle-java8-installer
<</code>>

3) After Java installation, verify the version

<<code>>
java -version
java version "1.8.0_121"
<</code>>

4) Edit /etc/bash.bashrc

Scroll to the bottom of the file and add the following lines.

<<code>>
export JAVA_HOME=/usr/lib/jvm/java-8-oracle
export PATH=$JAVA_HOME/bin:$PATH
<</code>>

Save and check:

<<code>>
source /etc/bash.bashrc
echo $JAVA_HOME
/usr/lib/jvm/java-8-oracle
<</code>>

5) Additional packages are required, unless installed already. Run the command:

<<code>>
sudo apt-get install alien libaio1 unixodbc
<</code>>

<<code>>
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
<</code>>

This step might take some time. You may proceed steps 7)-11)
in the meanwhile in another terminal window.

Create a new file /sbin/chkconfig and add the following contents

<<code>>
#!/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
<</code>>

<<code>>
sudo chmod 755 /sbin/chkconfig
<</code>>

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.

<<code>>
#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
<</code>>

10)

<<code>>
sudo service procps start
sudo sysctl -q fs.file-max
<</code>>

# This method should return the following:
fs.file-max = 6815744

11) Some additional steps required

<<code>>
sudo ln -s /usr/bin/awk /bin/awk
mkdir /var/lock/subsys
touch /var/lock/subsys/listener
<</code>>

12) Install Oracle XE (should have been converted from .rpm to .deb by now)

<<code>>
sudo dpkg --install oracle-xe_11.2.0-2_amd64.deb
<</code>>

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.

<<code>>
sudo rm -rf /dev/shm
sudo mkdir /dev/shm
sudo mount -t tmpfs shmfs -o size=4096m /dev/shm
<</code>>

14) Create the file /etc/rc2.d/S01shm_load

NOTE: replace "size=4096m"
with the size of your machine RAM in MBS.

<<code>>
#!/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
<</code>>

<<code>>
sudo chmod 755 /etc/rc2.d/S01shm_load
<</code>>

15) Configure Oracle 11g R2 Express Edition. Default answers are probably OK.

<<code>>
sudo /etc/init.d/oracle-xe configure
<</code>>

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:

<<code>>
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
<</code>>

Save.

17) Run source command and check that the output makes sense

<<code>>
source /etc/bash.bashrc
echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/xe
<</code>>

18) Apply desktop icon changes and start Oracle:

<<code>>
sudo chmod a+x ~/Desktop/oraclexe-gettingstarted.desktop
sudo service oracle-xe start
<</code>>

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

<<code>>
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
<</code>>

21) Run sqldeveloper:

<<code>>
sudo /opt/sqldeveloper/sqldeveloper.sh
<</code>>

# Tell sqldeveloper the correct Java path, if it asks for it:

/usr/lib/jvm/java-8-oracle

<<code>>
- Click connections
- Add new connection
- Connection name: XE
- username: SYSTEM
- password: <your-password>

- Connection type: Basic Role: Default
- Hostname: localhost
- Port: 1521
- SID: xe
<</code>>

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

<<code>>
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
<</code>>

# Install mariadb-server:

<<code>>
sudo apt-get install mariadb-server
<</code>>

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

<<code>>
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
<</code>>

24) Install unixodbc and mariadb-connect engine

<<code>>
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
<</code>>

25) Edit /etc/odbcinst.ini

Add:

<<code>>
[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
<</code>>

26) Edit /etc/odbc.ini

Add (check your password):

<<code>>
[XE]
Driver          = Oracle ODBC driver for Oracle 11.2
ServerName      = //localhost:1521/xe
DSN             = XE
UserName        = SYSTEM
Password        = <your-password>
<</code>>

27) Test ODBC connection and add a table

<<code>>
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;
<</code>>

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:

<<code>>
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 
<</code>>

Right after END INIT INFO. Otherwise mysqld will not find the Oracle ODBC driver.

28) Restart MariaDB

<<code>>
sudo /etc/init.d/mysql restart
<</code>>

29)

<<code>>
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;
<</code>>

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
https://dev.mysql.com/downloads/connector/j/5.0.html

Select Version (for example 5.0.8)

Platform independent. Download mysql-connector-java-5.0.8.tar.gz

<<code>>
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
<</code>>

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:

<<code>>
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.
<</code>>

You are connected. You may run commands in the Worksheet.
For example:

use mdb;

show tables;

You should see the tables.

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.

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

Products

Services

Pricing

Resources

About Us

Download MariaDB