Installing PHP and the Oracle TimesTen In-Memory Database on Linux


by Christopher Jones

Published April 2012

This article shows how to install and use the PHP scripting language with the Oracle TimesTen In-Memory Database on Oracle Linux 6. It describes the PHP OCI8 extension, which can be used with TimesTen Database as well as with Oracle Database.

The marketing description gives the best overview of TimesTen: 

Oracle TimesTen In-Memory Database (TimesTen) is a full-featured, memory-optimized, relational database with persistence and recoverability. It provides applications with the instant responsiveness and very high throughput required by database-intensive applications. Deployed in the application tier, TimesTen operates on databases that fit entirely in physical memory (RAM). Applications access the TimesTen database using standard SQL interfaces. For customers with existing application data residing on the Oracle Database, TimesTen is deployed as an in-memory cache database with automatic data synchronization between TimesTen and the Oracle Database.
 

Most standard PHP OCI8 features work with TimesTen, however it is a different database than Oracle Database, so some PHP OCI8 features cannot be expected to work the same way, or don't work at all. In particular LOB support from PHP OCI8 is not available, even though TimesTen 11g Release 2 now allows LOBS to be stored. Collection support isn't available and you might experience some edge cases with binding, particularly if your code is not well formed. Error messages might differ. If you are migrating existing applications to TimesTen is also important to test thoroughly because TimesTen does not support all SQL, PL/SQL and database features of Oracle Database.

Installing TimesTen

Install TimesTen following Oracle TimesTen In-Memory Database Installation Guide 11g Release 2 (11.2.2). These steps are summarized below using Oracle Linux 6.2, 64 bit:

  1. As root, create a new software owner and set a password: 
      
useradd -m -c "TimesTen Owner" -d /home/ttadmin -s /bin/bash ttadmin
chmod 755 /home/ttadmin
passwd ttadmin
  2. Create a new timesten group and add the TimesTen administrator and the Apache users to it: 
      
groupadd timesten
usermod -a -G timesten ttadmin
usermod -a -G timesten apache
  3. Create the instance registry: 
      
mkdir /etc/TimesTen
chgrp ttadmin /etc/TimesTen
chmod 770 /etc/TimesTen/
  4. Login as the ttadmin user.
  5. Download "TimesTen 11.2.2.2.0 for Linux x86 (64-bit)" from TimesTen Downloads page.
  6. In a terminal, extract the file 
      
tar -xf timesten112220.linux8664.tar.gz
  7. Install TimesTen: 
      
cd linux8664
./setup.sh


    Accept the default values except for the Group and the QuickStart questions. The prompts are answered:

    • Instance name: tt1122 
    • Install the "Client/Sever and Data manager"
    • Install into /home/ttadmin 
    • Create the daemon directory /home/ttadmin/TimesTen/tt1122/info 
    • Daemon logs are in /home/ttadmin/TimesTen/tt1122/info 
    • Default port for the TimesTen daemon: 53396 
    • Do not restrict access to the ttadmin group. (This is not the default option). Restrict it to the timesten group.
    • Enable PL/SQL
    • TNS_ADMIN is skipped and left unset
    • The TimesTen server listens on 53397 
    • Install the QuickStart and Documentation: yes (This is not installed by default.)
    • Create the DemoDataStore in /home/ttadmin/TimesTen/tt1122/info 
    • Do not use Replication with Oracle Clusterware

       

  8. As root, follow the steps given on the screen to install the startup scripts in /etc/init.d: 
    
cd /home/ttadmin/TimesTen/tt1122/bin
./setuproot -install

TimesTen will now be running.

Managing TimesTen

The /home/ttadmin/TimesTen/tt1122/quickstart/ttquickstartenv.sh is always a convenient way to set Oracle environment variables before accessing the sample database.

Check the status of the instance by setting the environment and running the ttstatus command:

source /home/ttadmin/TimesTen/tt1122/quickstart/ttquickstartenv.sh
ttstatus


This should give:

[ttadmin@localhost linux8664]$ ttstatus
TimesTen status report as of Tue Apr 17 14:52:03 2012

Daemon pid 23629 port 53396 instance tt1122
TimesTen server pid 23638 started on port 53397
------------------------------------------------------------------------
Accessible by group timesten
End of report


There is a command-line tool you can access the instance with to run ad-hoc queries:

ttisql "dsn=sampledb_1122;uid=appuser"


The password is oracle.

If you need to manually stop or start this instance of TimesTen, login as root and run:

service tt_tt1122 stop


or

service tt_tt1122 start


If you want to remove this instance of TimesTen, first stop it and then run:

cd /home/ttadmin
TimesTen/tt1122/bin/setup.sh -uninstall


Creating the TimesTen Sample DB

Create the sample database:

  1. Run the creation script: 
    
cd /home/ttadmin/TimesTen/tt1122/quickstart/sample_scripts/createdb
./build_sampledb.sh
  2. Choose and remember passwords when prompted for the adm, appuser and xlauser users.

     

Installing Apache and PHP

Install Apache and PHP:

  1. As root, use the bundled packages for the webserver and for PHP: 
    
yum install httpd php php-devel
  2. Edit /etc/php.ini and add a timezone setting using an appropriate timezone, for example: 
    
date.timezone = America/Los_Angeles
  3. Install the OCI8 extension:

    Download and extract the latest OCI8 extension from PECL. Then:

    tar -xf oci8-1.4.7.tgz
cd oci8-1.4.7
phpize
./configure --with-oci8=instantclient,/home/ttadmin/TimesTen/tt1122/ttoracle_home/instantclient_11_2
make
make install
    4. Edit /etc/php.ini and add:
    
extension=oci8.so
  4. Configure Apache. In /etc/sysconfig/httpd set: 
    
export TNS_ADMIN=/home/ttadmin/TimesTen/tt1122/network/admin/samples
export LD_LIBRARY_PATH=/home/ttadmin/TimesTen/tt1122/lib:/home/ttadmin/TimesTen/tt1122/ttoracle_home/instantclient_11_2

Managing Apache

Use the httpd service if you manually need to stop and restart Apache. Since the configuration just changed when OCI8 was installed, restart it now:

service httpd restart


Test PHP by creating a file /var/www/html/pi.php:

<?php
phpinfo();
?>


Load this in a browser using http://localhost/pi.php.

You should get a page of configuration information about PHP. Check there is a section for OCI8. The configuration options can be changed by editing /etc/php.ini and restarting Apache.

Connecting to TimesTen with PHP OCI8

The ttisql example given earlier shows the sample credentials and connect string. These can be used in a PHP script to connect to TimesTen. Create a script /var/www/html/oci8.php:

  <?php

  $c = oci_connect("appuser", "oracle", "sampledb_1122");
  if (!$c) {
	  $m = oci_error();
	  trigger_error('Could not connect to database: '. $m['message'], E_USER_ERROR);
  }

  $s = oci_parse($c, "select * from emp");
  if (!$s) {
	  $m = oci_error($c);
	  trigger_error('Could not parse statement: '. $m['message'], E_USER_ERROR);
  }
  $r = oci_execute($s);
  if (!$r) {
	  $m = oci_error($s);
	  trigger_error('Could not execute statement: '. $m['message'], E_USER_ERROR);
  }

  echo "<table border='1'>\n";
  while (($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
	  echo "<tr>\n";
	  foreach ($row as $item) {
		  echo "  <td>".($item!==null?htmlentities($item, ENT_QUOTES):"&nbsp;")."</td>\n";
	  }
	  echo "</tr>\n";
  }
  echo "</table>\n";

  ?>


Run this with http://localhost/oci8.php. The query results will be displayed in a table.

If you get a blank screen or have problems, check the Apache log in /var/logs/httpd/error_log. Make sure SELinux is in Permissive mode:

setenforce permissive
service httpd restart


Configuring TimesTen

If you are using command line scripts or using non-persistent connections, disconnection from TimesTen might be slow in some cases due to the database unloading from RAM when the last connection to it is closed. Resolve this by setting a RAM policy with ttRamPolicySet. This setting is persistent for the TimesTen instance. The following code sets the time the database is kept in RAM after the last application has disconnected to five seconds, deferring the shutdown and allowing the PHP application to close the connection without delay:

$s = oci_parse($cc, "call ttRamPolicySet('inUse', 5)");
oci_execute($s);


Summary

The PHP OCI8 extension works with both TimesTen Database and Oracle Database. Many basic scripts will work unchanged against either database. However, not all PHP OCI8 or Oracle Database features are supported in Oracle TimesTen, so careful application testing is recommended.

Generic information on the OCI8 extension can be found in the Underground PHP and Oracle Manual.

Christopher Jones works in the Linux Engineering team at Oracle, espousing the Oracle-PHP-Apache-Linux ("OPAL") stack.