Oracle DB

From DreamFactory
Jump to: navigation, search
(Bitnami Windows Install)
(Oracle Instant Client Instructions for Linux)
 
(27 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 
==Drivers==
 
==Drivers==
===Bitnami Windows Install===
+
=== Requirements ===
'''Note:''' These instructions are applicable for DreamFactory 2.1.0-4 and older. Starting in DreamFactory 2.1.1 you will need to use Oracle Instant Client 12
+
* You will need the following items from your package manager (linux)
*Download the Oracle Instant Client [http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html here]
+
** PHP Pear and PHP Dev (for PECL)
**You will need to download the appropriate version for your version of Oracle (i.e Instant Client 11 goes with Oracle 11g server.)
+
** Development tools / (e.g. Build Essential tools)
**Since the Windows Bitnami stack is only 32-bit you need to download the 32-bit instant client
+
** libaio1
**You will also need an Oracle login for this (free registration.)
+
* For Windows you will just need an OCI8 dll file from PHP PECL (or from Bitnami)
*Extract the zip file to your preferred location, such as C:\instantclient_11_2
+
* Oracle Instant Client Files
*Edit the php.ini file (''C:\Bitnami\dreamfactory-x.x.x-x\php\php.ini'')
+
** Download the basic and sdk instant client files: [http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html Oracle Instant Client Downloads]
**Uncomment (remove the semicolon) from the line that says <source lang=bash>;extension=php_oci8_11g.dll</source>
+
** Example Filenames:
**For Oracle Instant Client 12, the line will be denoted with a comment indicating it requires uncommenting <source lang=bash>;extension=php_oci8_12c.dll  ; Use with Oracle Database 12c Instant Client </source>
+
*** instantclient-basic-linux.x64-12.1.0.2.0.zip
*Edit your PATH variable.
+
*** instantclient-sdk-linux.x64-12.1.0.2.0.zip
**Go to Control Panel->System->Advanced System Settings
+
**Click on Environment Variables
+
**Under System variables, double click on Path.
+
**At the end of the Variable value, add the path to the instant client directory (see above.) Use a semicolon to separate this new entry from the current last entry. <source lang=bash>;C:\instantclient_11_2</source>
+
*Restart the system
+
  
==Linux==
+
=== Driver Install ===
===Bitnami Linux Install===
+
* In Linux you will need to use PECL to build the OCI8 module, pointing to the unzipped instant client files as the library. Then enable the module in PHP.
'''Note:''' These instructions are applicable for DreamFactory 2.1.0-4 and older. Starting in DreamFactory 2.1.1 you will need to use Oracle Instant Client 12 found [http://www.oracle.com/technetwork/topics/winsoft-085727.html here]
+
* In Windows you will need to add the unzipped instant client files to your PATH variable and restart the machine. Then enable the module in PHP.
*Download the Oracle Instant Client [http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html here]
+
 
**You will need to download the appropriate version for your version of Oracle (i.e Instant Client 11 goes with Oracle 11g server.)
+
For concrete examples, see the [[../../../APT/Ubuntu_16.04/Modules/Oracle_DB| Ubuntu 16.04]] instructions or the [[../../../Bitnami_Windows/Modules/Oracle_DB| Bitnami Windows]] instructions.
**You will also need an Oracle login for this (free registration.)
+
 
*Extract the zip file to your preferred location, such as ''~/instantclient_11_2''
+
==== Oracle Instant Client Instructions for Linux ====
*Edit the php.ini file (''~/dreamfactory-x.x.x-x/php/php.ini)''
+
 
**Uncomment (remove the semicolon) from the line that says <source lang=bash>;extension=oci8.so</source>
+
Begin by navigating to http://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html and download an appropriate Oracle Instant Client and SDK. You can't download these files directly from your Linux server because Oracle requires you to accept a license agreement and additionally sign into your Oracle account (free registration) before completing the download.
*Edit the setenv.sh file ( ''~/dreamfactory-2.0.2-0/scripts/build-setenv.sh'' )
+
 
**Add the following to the end of the file, just before the line that says  ''. /home/user/dreamfactory-2.0.2-0/scripts/build-setenv.sh''
+
Download the desired packages. For instance on Ubuntu, Debian, and other Linux variants we've downloaded the following packages:
<source lang=bash>LD_LIBRARY_PATH=/home/bitnami/instantclient_11_2:$LD_LIBRARY_PATH
+
 
export LD_LIBRARY_PATH</source>
+
* instantclient-sdk-linux.x64-18.3.0.0.0dbru.zip
===Manual Install from Github on Linux===
+
* instantclient-basic-linux.x64-18.3.0.0.0dbru.zip
'''These instructions are Ubuntu/Debian based'''
+
 
<br>
+
SSH into your Linux machine and create the following directory:
'''Note:''' These instructions are applicable for DreamFactory 2.1.0-4 and older. Starting in DreamFactory 2.1.1 you will need to use Oracle Instant Client 12 found [http://www.oracle.com/technetwork/topics/winsoft-085727.html here]
+
 
*Download the Oracle Instant Client [http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html  here]
+
<source lang=javascript>
**Get both the basic client and the sdk (two files)
+
$ mkdir /opt/oracle
**You will need to download the appropriate version for your version of Oracle (i.e Instant Client 11 goes with Oracle 11g server.)
+
</source>
**You will also need an Oracle login for this (free registration.)
+
 
*Extract the instant client basic zip file to your preferred location, such as ''/opt/instantclient_11_2''
+
Transfer the downloaded packages to this directory and unzip both, beginning with the basic (non SDK) package:
*Extract the sdk zip to the same location. There should be a sdk folder inside the main instantclient_11_2 folder
+
 
*Make a symlink from libclntsh.so.11.1 to libclntsh.so
+
<source lang=javascript>
**<source lang=bash>$ sudo ln -s /opt/instantclient_11_2/libclntsh.so.11.1 /opt/instantclient_11_2/libclntsh.so</source>
+
$ unzip instantclient-basic-linux.x64-18.3.0.0.0dbru.zip
*Ensure that you have the php-pear and php5-dev packages installed on your system
+
$ unzip instantclient-sdk-linux.x64-18.3.0.0.0dbru.zip
**<source lang=bash>$ sudo apt-get install php-pear php5-dev</source>
+
</source>
*Use PECL to install the oci8 so file.
+
 
**<source lang=bash>$ sudo pecl install oci8-2.0.10</source>
+
It is important to follow this sequence because the SDK package will unzip into the directory created by the basic package. If you're using a fresh Linux instance you may need to install the unzip package.
**You will be asked for the type of install and location of library. Use instantclient and the location where you extracted the files above. ex: "instantclient,/opt/instantclient_11_2"
+
 
**If the the install generates any errors it will fail you. You will need to troubleshoot based on the error reported.
+
Next you'll add the instant client directory to ldconfig:
*Create a module file for your oci8 installation.
+
 
**<source lang=bash>$ sudo touch /etc/php5/mods-available/oci8.ini</source>
+
<source lang=javascript>
*Edit the file you just created in your preferred text editor.
+
$ touch /etc/ld.so.conf.d/oracle-instantclient
**There is a single line in the file it looks like this:
+
</source>
**<source lang=bash>extension=oci8.so</source>
+
 
*Enable the php extension
+
Open this newly created file and add the following line:
**<source lang=bash>$ sudo php5enmod oci8</source>
+
 
*Restart your web service service
+
<source lang=javascript>
**<source lang=bash>$ sudo service apache2 restart</source> or
+
/opt/oracle/instantclient_18_3
**<source lang=bash>$ sudo service php5-fmp restart && sudo service nginx restart</source>
+
</source>
 +
 
 +
Next you'll need to install a few PHP and system packages. Use your package manager to do so, on Debian/Ubuntu you'd use apt install for instance:
 +
 
 +
<source lang=javascript>
 +
$ apt install php-dev php-pear build-essential libaio1
 +
</source>
 +
 
 +
If you're running an earlier version of PHP (for example 7.1) then you will need to modify the PHP package names to look like php7.1-dev, php7.1-pear, etc.
 +
 
 +
Next, install the oci8 PHP extension:
 +
 
 +
<source lang=javascript>
 +
$ pecl install oci8
 +
downloading oci8-2.1.8.tgz ...
 +
Starting to download oci8-2.1.8.tgz (194,154 bytes)
 +
.................................done: 194,154 bytes
 +
11 source files, building
 +
running: phpize
 +
Configuring for:
 +
PHP Api Version:        20170718
 +
Zend Module Api No:      20170718
 +
Zend Extension Api No:  320170718
 +
Please provide the path to the ORACLE_HOME directory. Use 'instantclient,/path/to/instant/client/lib' if you're compiling with Oracle Instant Client [autodetect] : instantclient,/opt/oracle/instantclient_18_3
 +
</source>
 +
 
 +
IMPORTANT: Note you are prompted to provide the path to the ORACLE_HOME directory. The required format is rather odd, and it must be followed precisely. You must specify the string instantclient followed by a comma, followed by the path to the instantclient directory. For example:
 +
 
 +
<source lang=javascript>
 +
instantclient,/opt/oracle/instantclient_18_3
 +
</source>
 +
 
 +
Finally, you'll just need to add the extension reference to your php.ini file. You likely have *two* php.ini files on your server, one for the CLI and one for the web server. You can learn more about their locations by running:
 +
 
 +
<source lang=javascript>
 +
$ php --ini
 +
</source>
 +
 
 +
Note however this will only identify the CLI php.ini file path because we're running PHP via the CLI. However the php.ini file used in conjunction with the web server is also typically found in this path. For instance on Debian 8 the respective paths are:
 +
 
 +
<source lang=javascript>
 +
/etc/php/7.2/apache2/php.ini
 +
/etc/php/7.2/cli/php.ini
 +
</source>
 +
 
 +
Open each file and append the following line to the bottom of each:
 +
 
 +
<source lang=javascript>
 +
extension = oci8.so
 +
</source>
 +
 
 +
After saving the files, run the following command to confirm the PHP CLI sees the OCI8 extension:
 +
 
 +
<source lang=javascript>
 +
$ php -m | grep oci
 +
oci8
 +
</source>
 +
 
 +
Finally, add the following two variables to your system environment and to Apache envvars file:
 +
 
 +
<source lang=javascript>
 +
export LD_LIBRARY_PATH=/opt/oracle/instantclient_18_3
 +
export ORACLE_HOME=/opt/oracle/instantclient_18_3
 +
</source>
 +
 
 +
If you're running NGINX, you can define these variables in the NGINX configuration file:
 +
 
 +
<source lang=javascript>
 +
location ~ \.php$ {
 +
    ...
 +
    fastcgi_param LD_LIBRARY_PATH /opt/oracle/instantclient_18_3;
 +
    fastcgi_param ORACLE_HOME /opt/oracle/instantclient_18_3;
 +
}
 +
</source>
 +
 
 +
Remember you'll need to restart your web server to ensure any changes to the web-related php.ini file take effect. Once complete, you should be able to generate an Oracle API via your DreamFactory web administration console.
 +
 
 +
For testing purposes you might additionally consider downloading the SQL Plus client (instantclient-sqlplus-linux.x64-18.3.0.0.0dbru.zip) to ensure your DreamFactory server can connect to the Oracle database. You'll download this zip file from the aforementioned Oracle website. Be sure to move the file to /opt/oracle, and then unzip it into /opt/oracle/instantclient_18_3. After unzipping it, make sure the following environment variables are set:
 +
 
 +
<source lang=javascript>
 +
$ export ORACLE_HOME=/opt/oracle/instantclient_18_3
 +
$ export LD_LIBRARY_PATH="$ORACLE_HOME"
 +
$ export PATH="$ORACLE_HOME:$PATH"
 +
</source>
 +
 
 +
After setting these variables, you can start SQL Plus like so:
 +
 
 +
<source lang=javascript>
 +
$ sqlplus
 +
</source>
 +
 
 +
=== Don't Need Oracle? ===
 +
 
 +
DreamFactory's Silver and Gold licenses include the Oracle connector by default, however if you don't require Oracle support and therefore don't install the OCI8 extension then you'll encounter the following error:
 +
 
 +
Problem 2
 +
  - Installation request for yajra/laravel-oci8 v5.5.8 -> satisfiable by yajra/laravel-oci8[v5.5.8].
 +
  - yajra/laravel-oci8 v5.5.8 requires ext-oci8 >=2.0.0 -> the requested PHP extension oci8 is missing from your system.
 +
 
 +
You can forgo the Oracle extension requirement by following these instructions:
 +
 
 +
Open '''composer.json''' and delete this block:
 +
 
 +
{
 +
  "type": "vcs",
 +
  "url":  "https://github.com/dreamfactorysoftware/df-oracledb"
 +
},
 +
 
 +
Scroll further down '''composer.json''', and remove the line beginning with '''dreamfactory/df-oracledb":'''
 +
 
 +
When removing these lines, be sure to remove the concluding comma, and leave the preceding comma intact.
 +
 
 +
Save your changes, and run the following command from your project's root directory:
 +
 
 +
$ composer update dreamfactory/df-oracledb --ignore-platform-reqs
 +
 
 +
If you instead run '''composer install --ignore-platform-reqs''' after making this change, you'll see the following warning:
 +
 
 +
Warning: The lock file is not up to date with the latest changes in composer.json. You may be getting outdated
 +
dependencies. Run update to update them.
 +
 
 +
This isn't a particularly big deal, however in the interests of ensuring your '''composer.json''' and '''composer.lock''' files are synchronized, run the aforementioned '''composer update''' command after the '''composer install''' command completes, at which point you are done.

Latest revision as of 01:55, 27 February 2019

Drivers

Requirements

  • You will need the following items from your package manager (linux)
    • PHP Pear and PHP Dev (for PECL)
    • Development tools / (e.g. Build Essential tools)
    • libaio1
  • For Windows you will just need an OCI8 dll file from PHP PECL (or from Bitnami)
  • Oracle Instant Client Files
    • Download the basic and sdk instant client files: Oracle Instant Client Downloads
    • Example Filenames:
      • instantclient-basic-linux.x64-12.1.0.2.0.zip
      • instantclient-sdk-linux.x64-12.1.0.2.0.zip

Driver Install

  • In Linux you will need to use PECL to build the OCI8 module, pointing to the unzipped instant client files as the library. Then enable the module in PHP.
  • In Windows you will need to add the unzipped instant client files to your PATH variable and restart the machine. Then enable the module in PHP.

For concrete examples, see the Ubuntu 16.04 instructions or the Bitnami Windows instructions.

Oracle Instant Client Instructions for Linux

Begin by navigating to http://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html and download an appropriate Oracle Instant Client and SDK. You can't download these files directly from your Linux server because Oracle requires you to accept a license agreement and additionally sign into your Oracle account (free registration) before completing the download.

Download the desired packages. For instance on Ubuntu, Debian, and other Linux variants we've downloaded the following packages:

  • instantclient-sdk-linux.x64-18.3.0.0.0dbru.zip
  • instantclient-basic-linux.x64-18.3.0.0.0dbru.zip

SSH into your Linux machine and create the following directory:

$ mkdir /opt/oracle

Transfer the downloaded packages to this directory and unzip both, beginning with the basic (non SDK) package:

$ unzip instantclient-basic-linux.x64-18.3.0.0.0dbru.zip
$ unzip instantclient-sdk-linux.x64-18.3.0.0.0dbru.zip

It is important to follow this sequence because the SDK package will unzip into the directory created by the basic package. If you're using a fresh Linux instance you may need to install the unzip package.

Next you'll add the instant client directory to ldconfig:

$ touch /etc/ld.so.conf.d/oracle-instantclient

Open this newly created file and add the following line:

/opt/oracle/instantclient_18_3

Next you'll need to install a few PHP and system packages. Use your package manager to do so, on Debian/Ubuntu you'd use apt install for instance:

$ apt install php-dev php-pear build-essential libaio1

If you're running an earlier version of PHP (for example 7.1) then you will need to modify the PHP package names to look like php7.1-dev, php7.1-pear, etc.

Next, install the oci8 PHP extension:

$ pecl install oci8
downloading oci8-2.1.8.tgz ...
Starting to download oci8-2.1.8.tgz (194,154 bytes)
.................................done: 194,154 bytes
11 source files, building
running: phpize
Configuring for:
PHP Api Version:         20170718
Zend Module Api No:      20170718
Zend Extension Api No:   320170718
Please provide the path to the ORACLE_HOME directory. Use 'instantclient,/path/to/instant/client/lib' if you're compiling with Oracle Instant Client [autodetect] : instantclient,/opt/oracle/instantclient_18_3

IMPORTANT: Note you are prompted to provide the path to the ORACLE_HOME directory. The required format is rather odd, and it must be followed precisely. You must specify the string instantclient followed by a comma, followed by the path to the instantclient directory. For example:

instantclient,/opt/oracle/instantclient_18_3

Finally, you'll just need to add the extension reference to your php.ini file. You likely have *two* php.ini files on your server, one for the CLI and one for the web server. You can learn more about their locations by running:

$ php --ini

Note however this will only identify the CLI php.ini file path because we're running PHP via the CLI. However the php.ini file used in conjunction with the web server is also typically found in this path. For instance on Debian 8 the respective paths are:

/etc/php/7.2/apache2/php.ini
/etc/php/7.2/cli/php.ini

Open each file and append the following line to the bottom of each:

extension = oci8.so

After saving the files, run the following command to confirm the PHP CLI sees the OCI8 extension:

$ php -m | grep oci
oci8

Finally, add the following two variables to your system environment and to Apache envvars file:

export LD_LIBRARY_PATH=/opt/oracle/instantclient_18_3
export ORACLE_HOME=/opt/oracle/instantclient_18_3

If you're running NGINX, you can define these variables in the NGINX configuration file:

location ~ \.php$ {
     ...
     fastcgi_param LD_LIBRARY_PATH /opt/oracle/instantclient_18_3;
     fastcgi_param ORACLE_HOME /opt/oracle/instantclient_18_3;
 }

Remember you'll need to restart your web server to ensure any changes to the web-related php.ini file take effect. Once complete, you should be able to generate an Oracle API via your DreamFactory web administration console.

For testing purposes you might additionally consider downloading the SQL Plus client (instantclient-sqlplus-linux.x64-18.3.0.0.0dbru.zip) to ensure your DreamFactory server can connect to the Oracle database. You'll download this zip file from the aforementioned Oracle website. Be sure to move the file to /opt/oracle, and then unzip it into /opt/oracle/instantclient_18_3. After unzipping it, make sure the following environment variables are set:

$ export ORACLE_HOME=/opt/oracle/instantclient_18_3
$ export LD_LIBRARY_PATH="$ORACLE_HOME"
$ export PATH="$ORACLE_HOME:$PATH"

After setting these variables, you can start SQL Plus like so:

$ sqlplus

Don't Need Oracle?

DreamFactory's Silver and Gold licenses include the Oracle connector by default, however if you don't require Oracle support and therefore don't install the OCI8 extension then you'll encounter the following error:

Problem 2
 - Installation request for yajra/laravel-oci8 v5.5.8 -> satisfiable by yajra/laravel-oci8[v5.5.8].
 - yajra/laravel-oci8 v5.5.8 requires ext-oci8 >=2.0.0 -> the requested PHP extension oci8 is missing from your system.

You can forgo the Oracle extension requirement by following these instructions:

Open composer.json and delete this block:

{ 
  "type": "vcs",
  "url":  "https://github.com/dreamfactorysoftware/df-oracledb"
},

Scroll further down composer.json, and remove the line beginning with dreamfactory/df-oracledb":

When removing these lines, be sure to remove the concluding comma, and leave the preceding comma intact.

Save your changes, and run the following command from your project's root directory:

$ composer update dreamfactory/df-oracledb --ignore-platform-reqs

If you instead run composer install --ignore-platform-reqs after making this change, you'll see the following warning:

Warning: The lock file is not up to date with the latest changes in composer.json. You may be getting outdated 
dependencies. Run update to update them.

This isn't a particularly big deal, however in the interests of ensuring your composer.json and composer.lock files are synchronized, run the aforementioned composer update command after the composer install command completes, at which point you are done.