I know it may look strange to start at the horses tail - but this way we make sure we don't have to "hop around". The work on the web server needs to be done so the remote OS can transfer the data here. And on that OS, the directories need to exist so the database can store them. So trust me, and let's start here.

This task is quite easy: Just copy the files and directories from the web/ directory of the archive somewhere into the web tree, i.e. to the place where you want to have them in. Basic installation is done then: Call up the location with your web browser, and it will show you the demo data. Admitted: not yet the final result, so some configuration is needed:

  • remove the demo data by deleting all directories below the csv/ directory. Instead, create new directories for each database to be monitored. The directory name should match the lower-case ORACLE_SID
  • Open the index.php with your favorite editor. You certainly want to replace all the demo entries by your own databases. For this, you only need to change the lines in the Databases section of the script - i.e. all $dbdata->addDB('sid','group'); lines. The first parameter again is the lower-case ORACLE_SID, the second is used to organize the databases into groups.

Task 1 of 3 is hereby completed - now we can switch to the database machines.

Operating system

On the OS level, we need to:

  • create a directory where the PL/SQL package can store its data into. This directory will be used later for the Oracle Directory to be created inside the database.
  • Place the transfer script. On Windows systems, we also need some SSH client: PuTTY is a good candidate for this. You can locate it via Google, and you will for sure need the pscp.exe plus puttygen.exe. While the former is used to do the transfer, the latter is used to generate the SSH keys (or to convert them from OpenSSH).
  • Adjust the transfer script to reflect your settings. Important changes include the variables RDIR (remote directory, i.e. on the web server), LDIR (local directory, i.e. on the database machine), and SERVER (host name of the web server). On a Windows machine, some additional settings must be made: SID (the ORACLE_SID of the database), SSHDIR (path where pcsp.exe resides, and SSHKEYFILE (file name of the SSH key file to use, including the path to it).

When this is done, run the script once manually to verify it works. The manual run is further necessary since the first run probably requires some interaction: If you never connected to the web server from here, SSH will ask your confirmation to connect to that host.


Finally we get to the installation of the PL/SQL package into the database to be monitored. As we need to gather information from the system views, the database user must have certain privileges. The script provided in oracle/create_fra_moni.sql grants those privileges - but you may want to adjust the users name and review the script before actually executing it. So your steps will be the following:

  1. Open oracle/create_fra_moni.sql with your favorite editor:
    • Replace all occurences of the user framon by the name of the user in whose schema the objects shall be installed, also replace framonpwd with that users password.
    • Replace the path of the directory in the second SQL command to the place the output files shall be saved into (make sure this directory exists and is writable by the user running the Oracle database).
    • At the end of the script, make sure the settings for the job creation match the OS the server is running - and that the paths are correct there.
  2. If everything looks correct, run the script from SQL*Plus as SYS user, e.g.: sqlplus "/as sysdba" @create_fra_moni.sql
  3. Check the output to verify everything went OK. As an Oracle DBA, you for sure will be able to fix any errors possibly displayed - if there are any.
  4. Make sure the scheduler is working. For details on this, you can refer to the corresponding article at the IzzySoft website.