Oracle Information Site (OIS) written by Sascha 'SieGeL' Pfalz
                      Last Updated: 22-Sep-2009
---------------------------------------------------------------------------
  This program is free software; you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation; either version 2 of the License, or
  (at your option) any later version.
---------------------------------------------------------------------------

0. E-O-L (End of life) Information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This is the last version of OIS that will be released.
All further development is stopped, no bugs or new features will be added.
I've decided to stop development as the whole code base is not up to date,
the design is completly table based and the concept of storing user
credentials in plain text files is also not a good idea at all.

I'm currently working on a completly new version of OIS called OIS 2,
which is written from scratch, uses database authentication, supports
a plugin system to easily extend the functionality and other nice features.
Normally this new tool should be released in the year 2009, but time will
tell if this can be done...so check my homepage for updates!


1. INTRODUCTION
~~~~~~~~~~~~~~~
When I started with Oracle back in 2001 I had really no idea how Oracle
works and how to extract Run-Time informations to optimize the database.
After some teaching by Oracle University I've got the DBA cert and
started to actually use Oracle in productive environments.
During that time my work machine was, well, "underpowered" (800Mhz P3) and
was not able to allow to work with the OEM without waiting times...I
really do not like to wait, so I've started to code my own info site in PHP
which allows me to access all Oracle-related informations with any browser
and, much more important, with any machine capable of running a decent
browser.
OIS was born.
It was started as a bunch of static HTML scripts which just dump out the
results of my SQL scripts, later I added CSS and formatted the output so
that it doesn't look that ugly anymore.

Now, 4 years later I've 6 Oracle Databases to administer. All of them are
running either in productive or test environments and are running on
three different OSes (Windows, Linux, Solaris), which of course require also
updates and security fixes from time to time, same with Oracle. It started
to get very confusing when you MUST have an overview of all your
installations, I have plenty of papers flying around with all installation
informations etc. But these infos are neither centralized nor sorted in any
fashion, leaving me with a great amount of chaos. During lack of time I've
finally decided to enhance OIS again and include some kind of Oracle
management system, which allows me to store a) all required informations
about the machine and b) stores update reports together with database
informations, so I can see with one click what happened with the server
during the running time.
The management part is still not finished but can be used NOW and allows
DBAs to have a centralized point for all Oracle related informations.


2. REQUIREMENTS
~~~~~~~~~~~~~~~
I'm no fan of old and unsupported products, so I've decided that OIS will
run ONLY (!) with the OCI8 API that PHP 4.x provides, as this is much more
powerful than the old API and also allows usage of Bindvars, CLOBs etc.
So to actually use OIS, you MUST have:

- PHP 4.4.x with OCI8 extension ENABLED and FULLY WORKING.
  Recommended PHP version is 5.2.0

- Any Oracle DB starting from 8i up to 10g, all Databases should be covered
  by OIS with correct diagnostic statements.

- A Webserver which is able to CONNECT (!) to the Databases you wish to check
  via OIS. OIS always connects to Databases via TNS Names and requires a
  working SQL*Net installation.

- Optionally if you wish to use the management part of OIS you need access to
  an Oracle 8i+ Database where the informations can be stored. This is however
  not required if you already have a working manager solution.

- Most of the Queries OIS executes against Oracle require SYSDBA privileges,
  else you will get "table not found" errors.

- The webserver must be able to execute $ORACLE_HOME/bin/tnsping as this is
  used to check for existence of your configured databases.

- Finally, as OIS runs with SYSDBA privileges, I HIGHLY recommend that you
  protect the pages with some kind of .htaccess protection, else everyone
  may be able to view all database objects, and this is really not what you
  want to allow, don't you? And of course such a site shouldn't be available
  via Internet, only in your own LAN or Intranet access should be allowed!


3. INSTALLATION
~~~~~~~~~~~~~~~
If you met all requirements listed above, you can install OIS, which should
be straight forward and not too complicated:

- Extract the archive to your document root directory of your Webserver or
  put the contents of the archive anywhere where the Webserver can
  reach them. A new directory called "OIS" will be created.

- Open the file inc/config.inc.php and configure OIS for your own needs.
  You have to enter all databases you wish to monitor with OIS.
  The format is always the same:

  [SNIP]

  $ora_user[0] = "<DBA_USERNAME>";
  $ora_pass[0] = "<DBA_PASSWORD>";
  $ora_host[0] = "<ORACLE_TNS_NAME>";
  $ora_desc[0] = "<DESCRIPTION_MAX_60 CHARS>";
  $ora_ping[0] = TRUE|FALSE;
  [SNAP]

  These 5 variables form ONE description for a database. Enter as
  DBA_USERNAME/DBA_PASSWORD the logindata for an account with SYSDBA
  privileges, either use SYSTEM or create your own DBA account for that
  purpose.
  Enter as <ORACLE_TNS_NAME> the name you have defined in your
  $ORACLE_HOME/network/admin/tnsnames.ora file. Check first on your
  commandline that you have the right name by entering:

  $> tnsping <YOUR_TNS_NAME>

  If all goes well you should see something like "OK (10msec)", else
  please check that the name you are using is really defined!

  Enter as DESCRIPTION a short info (max. 60 chars) about the database
  being used.

  If you do not want to have OIS auto-ping your Databases when you load
  the index file, set "$ora_ping[] = FALSE;" in this case no TNSPing
  is called. If you want to have a specific database "pinged" on index
  loading, set the according "$ora_ping[] = TRUE;" .

  To add another database, simply increment the counter inside the
  square brackets [] by one, so the next dbs would look like:

  $ora_user[1]="<ANOTHER_DBA_USERNAME>";
  ...
  $ora_user[2]="<AND_AGAIN_ANOTHER_DBA_USERNAME>";
  ...

  Repeat to duplicate the 5 variables for as many databases as you
  wish to have been included.

  If you want to use the management system from OIS, you have to
  edit the following three defines:

  [SNIP]

  define('MANAGEDB_USER'  , '<DB_USER>');
  define('MANAGEDB_PASS'  , '<DB_PASS>');
  define('MANAGEDB_HOST'  , '<DB_TNSNAME>');

  [SNAP]

  Again change <DB_USER>, <DB_PASS> and <DB_TNSNAME> to an account
  on your management database. This account won't need any special
  privileges except the usual ones, no sysdba privilege is required.

  If you do not want to use the management part of OIS either
  uncomment or completly remove the three defines, OIS will then run
  without any management functionality enabled.

- If you have configured management functionality, please start
  sql*plus with the user you have entered for management and copy &
  paste the file "sql/management.sql" into your SQL*Plus session.
  This will create all required database objects required for OIS'
  management system.
  You can of course execute the file directly in SQL*Plus by entering:

  SQL> @/path/to/your/management.sql

  NOTE: This step IS ONLY REQUIRED WHEN YOU WANT TO USE MANAGEMENT!!!
        Else you do not have to create any database objects!

  Since V1.34 OIS supports exporting of simple tables (no blobs/raw)
  as ASCII files containing SQL INSERT statements. This is a simple
  solution to quickly made a ready-to-insert backup of a given table.
  To support this feature you have to define the OIS_DATA_DIR define
  in config.inc.php and set it to a valid directory where the webserver
  is able to write files. Make sure that the permissions are correctly
  set on that directory! OIS is then able to export all files into this
  directory and you are able to download or remove these files later.
  OIS automatically checks for any export files inside OIS_DATA_DIR and
  presents an additional menu entry called "Manage Export files" whenever
  there are any files available.
  The export can be executed on the schemabrowser, just choose the schema
  to be used and select TABLE as object type, you can then mark all tables
  you want to export and click on the button "Export marked tables". OIS
  then creates SQL files (one for every table) and exports these files
  into your given data directory.
  To view, download or remove these export files click on the menu entry
  "Manage export files", you will be prompted with a list of available files
  and you can either download them by clicking on the "Transfer" button
  or mark files to be removed.

That's it, OIS is now configured and should be working as expected.


4. USAGE
~~~~~~~~
Point your favourite browser to the location where you have installed
the package, I assume http://localhost/OIS/ in all examples.

The main index page shows a list of all configured databases together
with a small information how fast the TNSPing command could be executed.
Just click on either the name or the description of a database and you
will be jumping to mainmenu which provides the information views
from the previous selected database.

The following menuentries are available as of V1.35:

- Oracle Database Informations
  Provides general informations and a complete list of init.ora parameters
  sorted by default/non-default settings.

- Processes & Sessions
  Lists all currently active sessions on the target database. Clicking on
  the process id will give you detailed informations what happen there.
  In the future the details will be enhanced with more detailed infos.
  NOTE: If you choose to view the session of OIS, it may happen that the
  details are empty, thats because PHP must connect to Oracle everytime
  you load a new page, and therefor it may happen that the process you
  clicked on disappeared.
  Since V1.35 OIS now detects long-running operations and displays them
  in Bold on the overview page. In the process-detail page you can see
  what Oracle is currently doing, and how long it would take to complete
  that operation.

- Redo Logs
  Lists redolog details and usage informations including some hints how
  the redologs perform.

- All Tablespaces
  Lists all tablespaces together with space management, sizes and a small
  LED indicator which is red if the tablespace is >=95% full.
  Clicking on a tablespace name lists all datafiles details attached to
  the choosen tablespace.
  Below the list there is a second table which shows datafile usage, this
  is a good view to see where the actual I/O is performed.

- Rollback Segments
  Lists rollback segments. If you are using 9i+ the undo tablespace is
  listed, else you will see a list of configured rollback segments.
  Also any rbs in usage will be shown in the second table. Since V1.35
  also some rollback statistics are shown to help tuning.

- Database Users
  This provides a list of all registered database users. You can view the
  objects of a given user by clicking on the mag icon, which links to the
  schema browser you can also reach via main menu.

- Installed Jobs
  Lists all installed database jobs created with DBMS_JOB.* functions.

- Tuning Views
  Provides some tuning informations for DB Block Buffer Hit Ratio, library
  and dictionary cache and sorting stats. These stats should give you a
  general overview how the database performs by comparing the percent
  values with the recommended values mentioned in the table header.
  Also the sort parameter is helpful, if you see many disk based sorts
  here you have to increase the SORT_AREA_SIZE init.ora parameter i.e. !
  Since V1.35 a list of fragmented objects is shown on bottom of page,
  so you can re-organize these objects if fragmentation is too high.

- Memory Usage
  Shows usage of SGA memory and shared pool details. Also good to see how
  the memory is used, where memory is wasted etc.
  NOTE: The views differ between 8i and 9i databases, I have currently
  no 10g to test installed but I would think that there are again changes.
  Since V1.35 the SGA allocation overview is shown on bottom of page.

- Top 20 SQL Queries
  Lists the Top 20 Queries from the SQL Cache sorted by Disk reads. The
  SQL queries are taken from an DOAG News article (see Doag News Q4/04
  Page 50 ff.) written by Harry Flora, I just put some HTML code around
  it and added a detailed view of a choosen query.
  Thanks for the good article Harry!
  Since V1.35 there is a second table printed on bottom of page which shows
  SQL with most invalidations, again a good hint if your Instance require
  some further tuning.

- NLS Setup
  Lists the Database, Instance and Session setup for NLS.

- Lock Overview (V1.35+)
  Lists all locks that are currently held in your instance. This helps to
  detect dead locks when using Database links for example. At least this
  was the reason why I added such a view ;)

- Instance Statistics (V1.35+)
  This view shows all instance statistics with a count > 0, the top wait
  statistics and cursor statistics. Again very useful to tune your instance.

- Schema Browser
  This provides a small schema browser which allows to view all stored
  database objects of a given schema. You can view the DDL commands for
  all stored objects by clicking on the icon to the right. For tables
  an additional icon is shown (a mag) which lists details about the
  selected table. On this detail page you may then export this table
  only or view the the contents of the selected table. Also additional
  informations about the selected table is shown.

[SPACE FOR RENT/MORE FEATURES]


5. USAGE OF THE OIS MANAGER
~~~~~~~~~~~~~~~~~~~~~~~~~~~
The OIS manager is a simple database manager which allows to store a list
of machines that are running Oracle instances. You can store machine
specific details including Oracle Release details and you can also save
comments for every machine.
To have this working you must configure the MANAGEDB_USER, MANAGEDB_PASS
and MANAGEDB_HOST defines in config.inc.php and also have installed the
required SQL objects (see sql/management.sql).

If the defines are set an additional menu entry is shown called
[Manage Oracle Databases]. Just click on it to see an overview of all
added machines. You can add new machines with the "Add machine" button,
edit the entries by clicking on any entry, add notes to a specific
machine by using the "newspaper" icon or drop a machine including all
associated comments by clicking on the "trashcan" icon.

Please note that currently no output options exist, I'm currently in
progress of reviewing the possible output formats (i.e. PDF) and these
things will be added in future versions.

I do not think that more explanation is required, the OIS Manager
should be pretty self-explaining.


6. HOW EXPORTING OF TABLES WORKS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If you want the possibility to make quick exports of tables you have to
configure the OIS_DATA_DIR directive (see above) so that OIS is able to
write the exported files inside this directory.

Please keep in mind that the table export does not work for CLOB/BLOB/RAW
fieldtypes! Only the VARCHAR* / NUMBER fields are supported.

The exported files contains INSERT statements for every row, but there is
NO COMMIT at the end of these files, you have to commit them on your own!



7. TO-DO, MISSING PARTS
~~~~~~~~~~~~~~~~~~~~~~~
Software like OIS is never fully completed, new features are introduced
with every new release of Oracle which must be added to OIS. Also the
existing functions maybe extended with addtional infos, so if you have
some nice info views which you want to be added to OIS, feel free to
send them to me together with a small description what the queries done.

Known missing parts as of V1.34:

- No output/export functions for the OIS manager

- Simple SQL interface to allow to execute statements directly from OIS

- Some graphical stats for tablespace usage etc.

- A consistent design...I'm completly lost when it comes to design/graphics,
  so if anyone out there has some ideas how to improve the GUI...please
  report to me (best would be to add screenshots).

- Some kind of update/install script for easier installation of OIS

- All your ideas/improvements etc.

- Adding new features to schemabrowser

- Possibility to start/stop/edit/add new DBMS_JOB calls via "Jobs" menu.



8. CONTACT
~~~~~~~~~~
If you have any suggestions, bug-reports or improvements for OIS, feel free
to contact me on one of the methods listed below:

E-MAIL: php@saschapfalz.de
   WWW: http://www.saschapfalz.de
   ICQ: 9691810

Normally I answer to emails in less than 8 hours, if this is not the case I'm
either very busy or your mail was killed by a spam filter, in this case resend
your email please.

-----------------------------------------------------------------------------
$Id: README,v 1.9 2009/09/22 20:11:37 siegel Exp $
-----------------------------------------------------------------------[EOF]-