Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 27

In Chapter 2, you installed Apache and PHP; now it’s time to add the Oracle database component. In this chapter, we’ll give you the basic installation instructions for Oracle Database XE on both
Windows and supported Linux platforms. First, we’ll give you the general requirements for each platform, and then you can dive into the sections for your particular platform.
After the installation tasks for each platform, we’ll show you how to hook PHP up to your data- base and give you a couple of examples to try out to make sure that everything is working as expected.
Finally, we’ll show you how to perform a number of configuration tasks using the XE Web inter- face, as well as give you a high-level overview of Oracle administration tasks such as creating new user accounts.

Ensuring Installation Prerequisites

Regardless of whether you are installing Oracle Database XE on Windows or Linux, there are several minimum requirements for the hardware and software on the workstation or server where you install Oracle Database XE:

System architecture: Oracle Database XE only runs on Intel or AMD x86–compatible processors.

Memory (RAM): 256MB is the minimum memory requirement; 512MB is recommended. Oracle Database XE can utilize as much as 1GB of RAM.

Network protocol: Oracle Database XE only supports TCP/IP.

Disk space: A minimum of 1.6GB of disk space is required for the Oracle executables and the starter database. Your disk space requirements may be higher, depending on the amount of user data. Oracle Database XE supports up to 4GB of user data.

Because you have Apache and PHP installed on your workstation already, your memory requirements may exceed 512MB, depending on the database applications you will run and any other applications running simultaneously on your workstation.
Your Web browser is the primary administrative interface for Oracle Database XE. Make sure that you enable cookies in your browser and that it is included in the following list:

• Microsoft Internet Explorer 6.0 or later

• Netscape Navigator 7.2 or later

• Firefox 1.0 or later

• Mozilla 1.7 or later

469

If you have another recently released browser, such as Opera or Konqueror, chances are it will most likely work. However, if you have any problems with these officially unsupported browsers, Oracle support may require that you reproduce your problem with one of the supported browsers.

Windows Installation Tasks

This section covers the Windows installation tasks: determining operating system prerequisites, obtaining the installation files, performing the installation, and testing out your installation. We cover the equivalent Linux installation instructions later in this chapter.

Windows Prerequisites

In addition to the general Oracle Database XE requirements listed earlier in this chapter, there are some Windows-specific requirements. First, you must be using a recent release of Windows included in this list:

• Windows 2000 SP4 or later

• Windows Server 2003

• Windows XP SP1 or later

In addition, you must install Oracle Database XE logged in as a user in the Administrators group.

Downloading the Installation Files
Navigate to the Oracle Web site download area for Oracle Database XE, http://www.oracle.com/ technology/software/products/database/xe/index.html, and follow the links to download the Windows version: OracleXE.exe. Registration for Oracle Technology Network (OTN) is required, but free, and gives you access to many other resources on http://otn.oracle.com.

■Note If you will be administering Oracle Database XE and developing applications from other workstations, you
need to perform a client installation after the server installation is complete. You install the client installation file, OracleXEClient.exe, on each remote workstation that needs to access Oracle Database XE outside of a Web browser.

Performing the Installation

Double-click the file OracleXE.exe, and the Oracle Install Wizard for Oracle Database XE starts. You will see the window shown in Figure 27-1.
Click the Next button, and you will see the License Agreement page for Oracle Database XE shown in Figure 27-2. Review the license agreement and click the I Accept… radio button, then click the Next button. Oracle Database XE is free, if you weren’t aware of this yet.

Figure 27-1. The Oracle Database XE Install Wizard

Figure 27-2. The Oracle Database XE License Agreement window

In Figure 27-3, select the folder where you want to install Oracle Database XE. Be sure to select a folder on a disk drive with at least 1.6GB of available disk space. In this example, you put the instal- lation files and the starter database in the folder D:\OracleXE. By default, the system executable and configuration files reside in the app subdirectory, and user data files are stored in the oradata subdirectory under D:\OracleXE; however, you can change the location of the data files later if you have a faster disk drive, for example.

Figure 27-3. Specifying the Oracle Database XE destination location

After you click the Next button, you enter and confirm the password for the SYS and SYSTEM accounts shown in Figure 27-4. You can create different passwords for these accounts later using the administrative Web page we introduce later in the “Creating User Accounts” section. Click the Next button after you confirm the password.

Figure 27-4. The Oracle Database XE Specify Database Passwords window

In the installation settings window shown in Figure 27-5, you can review the options you specify, including the default values for the Oracle database listener, Oracle Services for Microsoft Transaction Server, and the Oracle administrative HTTP listener port.

Figure 27-5. The Oracle Database XE installation settings window

■Note You may wonder why the default HTTP listener port is 8080 and not the default HTTP port 80. This is
because the default installation of Apache HTTP Server uses port 80; any content you serve for your users will most likely be managed by Apache and your PHP applications using port 80. Since the Oracle Database XE administrative interface is also an HTTP service, it must use a different port number. As a result, you have two Web servers running on your workstation at the same time. This is not a problem since they are listening on different port numbers.

After you click the Install button, the installer copies the files to the destination directory and creates the database. When the installation process is complete, you see the window shown in Figure 27-6.

Figure 27-6. Oracle Database XE installation is complete.

By default, the Launch the Database Homepage checkbox is selected. After you click the Finish button, the installer automatically starts the Oracle Database XE administrative interface you saw in Chapter 25.

Configuring Oracle and PHP

Now that we have all of the required components for our Apache/PHP/Oracle on Windows environ- ment installed, you must perform the final hookup: enabling PHP to connect to the Oracle database.
Find the PHP configuration file php.ini you created in Chapter 2 and locate this line in the file:

;extension=php_oci8.dll

Remove the semicolon at the beginning of the line and save the file in its original location. Be sure to check the value of the parameter extension_dir in php.ini; its value is the directory where PHP finds its extensions. For this particular PHP installation, here is the value of extension_dir:

extension_dir = "c:\php5.2.0\ext"

If the file php_oci8.dll is not in the directory referenced by the parameter extension_dir (along with about 45 other extensions), locate it and change the value of this parameter to the directory containing it. For these changes to take effect, you must restart the Apache HTTP server. The easiest way to do this in Windows is to use the Start menu. Click All Programs ➤ Apache HTTP Server ➤ Control Apache Server ➤ Restart. Unless an error occurs, you will briefly see a command window. To
see if PHP can connect to Oracle successfully, create the file test_ora_conn.php using the code in Listing 27-1 and place it in your Apache document root. For a default Apache 2.2 installation on Windows, the document root directory is C:\Program Files\Apache Software Foundation\Apache2.2\ htdocs. If you followed the Apache installation instructions in Chapter 2, the document root direc- tory is C:\Apache2\htdocs.

Listing 27-1. PHP Code to Test Oracle Connectivity (test_ora_conn.php)

<?php
if ($conn = oci_connect('system', 'yourpassword', '//localhost/xe')) { print 'Successfully connected to Oracle Database XE!'; oci_close($conn);
} else {
$errmsg = oci_error();
print 'Oracle connect error: ' . $errmsg['message'];
}
?>

Be sure to substitute the password you entered in the step shown in Figure 27-4 in the second
line of the script; otherwise you will exercise the else clause of the if statement in the PHP code. Navigate to this URL http://localhost/test_ora_conn.php, and you should see the success message in the first print statement.
You’ve probably seen the Oracle-specific function calls such as oci_connect and oci_error in examples earlier in this book. We’ll talk about them more formally in Chapter 28 and throughout the examples in the rest of the book.

Linux Installation Tasks

This section covers the Linux installation tasks: determining operating system prerequisites, obtaining the installation files, performing the installation, and testing out your installation. There are a few more setup tasks in the Linux environment than in a Windows environment.

Linux Prerequisites

In addition to the general Oracle Database XE requirements listed earlier in this chapter, there are some Linux-specific requirements explained in the following sections. Be sure to follow these instruc- tions carefully. Your installation may succeed if you ignore some of these requirements, but you will most likely run into performance problems later if your environment is not configured correctly.

Linux Distribution

You must be using one of the Linux distributions from the following list:

• Red Hat Enterprise Linux, version 3 or 4

• SUSE Linux SLES-9 or higher

• Fedora Core 4

• Red Flag DC Server 5.0 / Miracle Linux 4.0 / Haansoft Linux 2006 Server (Asianux 2.0 Inside)

• Debian 3.1

While your installation may work fine for testing and development on other Linux distributions, Oracle will not support them and strongly recommends that you do not deploy any production applications on a nonsupported distribution.

Required Packages

Regardless of the Linux distribution, you must also ensure you have these packages installed:

• glibc 2.3.2 or later

• libaio 0.3.96 or later

See the installation instructions for your Linux distribution to install or update these packages. All of the Linux distributions listed previously include these packages in their distribution media.

Kernel Parameters

For Oracle Database XE to install and run successfully, you must ensure that the kernel parameters in Table 27-1 have the corresponding minimum values. The Oracle installer checks these values before installation will proceed.

Table 27-1. Minimum Linux Kernel Parameter Values

Kernel Parameter Minimum Parameter Value
semmsl 250 semmns 32000 semopm 100 semmni 128
shmmax 536870912 shmmni 4096 shmall 2097152 file-max 65536
ip_local_port_range 1024–65000

You can confirm these parameters from a shell prompt using the sysctl and egrep commands as follows:

[root@phpxe ~]# /sbin/sysctl -a | egrep 'sem|shm|file-max|ip_local'
net.ipv4.ip_local_port_range = 1024 65000 vm.hugetlb_shm_group = 0
kernel.sem = 250 32000 100 128 kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 2147483648 fs.file-max = 65536 [root@phpxe ~]#
The kernel parameters semmsl, semmns, semopm, and semmni correspond to the four values in the sysctl output for parameter kernel.sem. Many of these parameters will already be at least set at their minimum values. For those parameters that need to be changed, edit the file /etc/sysctl.conf and change the parameters to these minimum values. You can change these values in the running kernel immediately by running the command /sbin/sysctl -p. These values will be set automatically in the future when your workstation starts.

Swap File Space

Depending on the amount of RAM in your workstation, you must have additional swap file space allocated for both the installation process and for Oracle Database XE while it runs. Table 27-2 shows
the memory range and the corresponding minimum swap file space requirements.

Table 27-2. Minimum Linux Swap Space Requirements

Memory Range Minimum Swap Space Required

128MB–256MB 3X RAM size

256MB–512MB 2X RAM size

More than 512MB 1GB

Use the free command to see the current amount of allocated swap file space. To allocate a new swap file using either a disk partition or a regular file, see the Linux Code Inline pages for the mkswap and swapon commands. Be sure to allocate enough swap space to accommodate the needs of the other applications you run on your workstation.

System Privileges

Finally, you must install Oracle Database XE logged in as root. Access to the root account (or equiv- alent) is required to adjust system parameters and swap file space as well.

Downloading the Installation Files

Navigate to the Oracle Web site download area for Oracle Database XE, http://www.oracle.com/ technology/software/products/database/xe/index.html, and follow the links to download the Linux version of Oracle Database XE. Registration for OTN is required and gives you access to many other resources on http://otn.oracle.com. The Linux version will run fine on the distributions mentioned

earlier in this chapter. The installation may work on other Linux distributions, but proceed at your own risk.
There are two installation files available, oracle-xe-10.2.0.1-1.0.i386.rpm and oracle-xe_
10.2.0.1-1.0_i386.deb. As you might expect, the first one works with all Linux distributions except for Debian; and the second one is specifically for the Debian package manager. (The Debian package manager is similar in many ways to the Red Hat package manager.) If you are going to access Oracle Database XE on the same computer where you installed it, you do not need to install the client files.

Performing the Installation

To begin the installation process, locate the rpm file you downloaded in the previous section and run the rpm command as follows:

[root@phpxe tmp]# rpm -ivh oracle-xe-10.2.0.1-1.0.i386.rpm

Preparing... ########################################### [100%]
1:oracle-xe ########################################### [100%] Executing Post-install steps...

You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.

[root@phpxe tmp]#

As the output of this installation step implies, you need to run /etc/init.d/oracle-xe configure next. The dialog in Listing 27-2 prompts you for the administrative interface port number, the Oracle listener port number, the password for the SYS and SYSTEM accounts, and whether you want to start Oracle automatically when your workstation boots.

Listing 27-2.Oracle Database XE Installation Dialog on Linux

[root@phpxe tmp]# /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express Edition. The following questions will determine whether the database should be starting upon system boot, the ports it will use, and the passwords that will be used for database accounts. Press <Enter> to accept the defaults. Ctrl-C will abort.

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: *********
Confirm the password: *********

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:

Starting Oracle Net Listener... Done
Configuring Database... Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex" [root@phpxe tmp]#

In this example, you accept the defaults for port numbers and startup options. The installer starts the listener, configures the database, and starts the administrative Web interface service. To access the Oracle Database XE administrative interface, navigate to the URL you see at the end of the installation dialog:

http://127.0.0.1:8080/apex

You should see the Oracle Database XE administrative interface you saw in Chapter 25. If you are using Debian Linux, the previous steps are the same except that you initiate the installation process using the Debian package manager as follows:

dpkg -i oracle-xe-universal_10.2.0.1-1.0_i386.deb

Configuring Oracle and PHP

Now that you have all of the required components for your OPAL installed, you must perform the final hookup: enabling PHP to connect to the Oracle database.
Locate the PHP configuration file php.ini you created in Chapter 2. In a default installation on Red Hat Enterprise Linux 4, it is located in /etc/php.ini. Open the file in your favorite text editor and locate this line in the file:

;extension=oci8.so

Remove the semicolon at the beginning of the line and save the file in its original location. If you are not using Zend Core for Oracle on your Linux server, you automatically have OCI8 configured. If you do not even have oci8.so in your extensions directory, you can get it as a PEAR module using the following command:

pear download oci8

Next, you must reconfigure PHP with the --with-oci8 option.
For these changes to take effect, you must restart the Apache HTTP server. The easiest way to do this in Linux is to run this command:

/etc/init.d/httpd restart

To see if PHP can connect to Oracle successfully, create the file test_ora_conn.php using the code in Listing 27-1 and place it in your Apache document root. For a default Apache 2.0 or 2.2 instal- lation on Red Hat Linux, the document root directory is /var/www/html.
Be sure to substitute the password you entered in the step shown in Figure 27-4 in the second line of the script; otherwise you will exercise the else clause of the if statement in the PHP code. Navigate to the URL http://localhost/test_ora_conn.php, and you should see the success message in the first print statement.

Performing Post-Installation Tasks

By default, Apache and Oracle Database XE start automatically when Windows or Linux starts. If you did not launch the administrative interface when you finished the installation steps, open your Web browser and navigate to this URL: http://localhost:8080/apex/.
To access the administrative interface from another workstation (even if you did not install the client software on that workstation), use the same URL with the name of the Oracle Database XE server hostname instead of the default internal Linux hostname localhost, as in this example: http://example.com:8080/apex/.
If Oracle Database XE’s Web service has started successfully, you will see the login screen shown
in Figure 27-7.

Figure 27-7. Oracle Database XE login page

Creating User Accounts

In a default installation of Oracle Database XE, you specify the password for both the SYS and SYSTEM accounts. Following Oracle best practices, you should rarely use the SYS account, since it is the owner of all internal database tables and you want to minimize the risk of deleting or changing these tables. While you can use the SYSTEM account for most, if not all, of your administrative tasks, you should create another privileged account. This has a number of advantages. First, in case you lose, forget, or misplace the password for one of the accounts, you can still easily get into the database with another account. Second, in an environment with more than one administrator, having a sepa- rate account for each administrator provides more accountability and the option to fine-tune the privileges granted to each administrator.
To create an additional administrator account, log in using the Oracle Database XE administra-
tion home page shown in Figure 27-7, and click Administration. On the next page, click Database Users. Alternatively, you can click the arrow next to the Administration button itself and follow the drop-down menus to the user account management page.

Next, click the Create button and you will see the Web page shown in Figure 27-8.

Figure 27-8. Oracle Database XE Create Database User page

Specify a username and the password. By default, the CONNECT and RESOURCE roles are selected. To give this user administrative privileges, select the DBA checkbox as well. We will explain how privileges and roles work in Chapter 30. Click the Create button, and you are ready to use the new administrative account the next time you log in.

Summary

You should now have a complete application and database environment installed: Oracle, PHP, and Apache on either Windows or Linux. Oracle Database XE retains many of the robust features found in the other (i.e., not free) versions of Oracle. It integrates nicely with other open source products such as PHP and Apache.
Throughout the rest of this book, we’ll use examples in the Windows environment except where the difference between environments is significant. However, you will soon find that you won’t even be able to tell that you’re on either Windows or Linux. This provides you with the flexibility to move your applications between platforms when the need arises with minimal, if any, rework.
The next chapter introduces many more administrative functions you will perform on a regular basis. We’ll also show you the basics of Oracle Database XE’s memory and disk architecture to help you optimize your database environment, whether it’s just for development or for a mission-critical department application.