From zzat
Revision as of 06:34, 18 August 2017 by Zztat (talk | contribs)
Jump to: navigation, search

Installation prerequisites

The following requirements must be met in order for zztat to install and run successfully:

  • Oracle version,,, or higher
  • Access to basic SYS packages for the zztat users (normally these are executable by PUBLIC):
    • dbms_aq
    • dbms_aqadm
    • dbms_crypto
    • dbms_db_version
    • dbms_utility
    • dbms_lob
    • dbms_lock
    • dbms_scheduler
    • dbms_session
    • dbms_sql
    • dbms_stats
    • utl_call_stack (Oracle 12.1+)
    • utl_mail (see details below)
    • utl_raw
  • job_queue_processes must be set to a value greater than 0. 10 is a good starting point if you don't run many jobs apart from zztat.
  • Your environment and policies should allow granting the following system privileges to the zztat users:
    • Note that these privileges are mandatory for zztat to function. Granting them to zztat via roles is currently not supported - they must be direct grants.

Requirements for optional features

The following components can be configured at your discretion, based on how you set up the setup.sql configuration file (see below for details). Additional requirements are applicable if you intend to enable the following features.


  • zztat can send emails as part of its reaction framework to alert you
  • Emails can be sent to pager email aliases to trigger immediate alerts
  • Alternate emails can be configured for warning and critical type alerts.

In order for zztat to be able to send emails when an alert occurs, the following requirements apply:

  • The XML Database component must be installed in your database. Starting with Oracle 12.1 this has become a mandatory component. If you are still running Oracle 11.2, you need to have XDB installed to be able to get emails from zztat.
  • smtp_out_server must be configured
  • ACLs must be configured for the servers specified in smtp_out_server. The installer does this automatically for you. Please note that it uses dbms_network_acl_admin to do so.
  • The utl_mail package must be installed. See the Oracle documentation here for details.

Tracing and Dumps

There are two distinct features that can be enabled. First and foremost, zztat's ability to trace itself. It has automatic tracing built-in that can be enabled if there are performance problems with zztat's own procedures.

Secondly, zztat offers several reactions that make use of the various Oracle events and dumps available. Some examples include hanganalyze, library cache dumps, event 10046, etc. It's also important to note that the more intrusive dumps, such as a systemstate dump or a hanganalyze are disabled by default - even after enabling the basic tracing functionality in zztat.

  • To be able to use traces and diagnostics dumps, the zztat users will require the ALTER SESSION system privilege. The installer will grant this automatically if you enable this option.

Session Management

zztat has several metrics, gauges and reactions defined by default that make session management easier for the DBA:

  • Idle sessions with uncommitted transactions can be terminated
  • Sessions which are causing a cascading effect blocking many other sessions can be terminated

In order to make use of those tools, the zztat user will require the ALTER SYSTEM system privilege. The installer will grant this automatically if you enable this option.

Alert Log Messages

To integrate with existing monitoring solutions, zztat has the ability to write messages to the alert log. If you enable this functionality, the zztat user will require access to the dbms_system package.

Setting up your Installation

zztat is delivered as a tar-ball. To begin, you may want to distribute this to at least two servers hosting Oracle databases: One which will become the zztat repository and one which will become a target database monitored by zztat.

After you extract the tar-ball you will find a number of scripts. The first file to work with is setup.sql, which is grouped into several sections:

  1. Tablespace where we store our data
  2. The schema name of the application user on TARGETS 
  3. The schema name of the repository owner on the REPOSITORY
  4. The database link user account
  5. The database link name to use
  6. Email configuration (optional but highly recommended)
  7. dbms_system configuration (optional)
  8. alter session configuration (optional but highly recommended)
  9. alter system configuration (optional)
  10. zztat sys-helper configuration (optional but highly recommended)

The first section in points 1 through 5 are the mandatory configuration. These determine how and where zztat will be setup by the installer.

The second section in points 6 through 10 are the optional configuration. These determine which advanced zztat features will be available in your installation.

Go through the setup.sql file in detail - it contains a lot of information. Failing to edit the file properly may result in a failing or broken installation.

Installing the Repository

To begin the zztat repository installation, run the init_r.sql script. The script requires no parameters, everything is configured within setup.sql

Installing Targets

To add a new target database to the zztat environment, run the init_r.sql script on the target database. If you have updated setup.sql before installing the repository, remember to copy the file across to the target database host.

Installation Notes & Tips

Please be aware of the following when running the installation script:

  • The script automatically creates the necessary users and grants the necessary privileges.
  • The installer will drop the users before creating them. Do not use this script to correct a problem - you will loose any and all historic zztat data in your repository and / or targets.
    • Do not specify an existing user to install zztat in if it contains any application or user data already. zztat must be installed in a dedicated database schema / user account.
  • The script is designed to be fully re-executable. If you specified the wrong tablespace, or anything else went wrong, you can safely run the script again.
  • The installation script should raise no errors, it may however do so when the script is re-executed, which is expected.
  • Close all database connections you may have to any of the zztat users to avoid problems with the install script. Do not connect to the repository owner schema or the application user schema while running the installer.
  • Run the zzdiag.sql script after the installation completes.