Panorama for Oracle databases
„Panorama” is my swiss army knife for performance analysis and troubleshooting on Oracle databases.
Completed over many years it contains solutions for analysis workflows based on practical experience.
As a GUI-application it ensures quick reaction with predefined workflows instead off dealing by hand with lots of SQL scripts.
Content
What does Panorama offer?
- Dialogs and workflows for:
- Performance-optimization of applications using Oracle databases
- Analysis of runtimes and wait events, also back in history
- Preparation of several Oracle database's internal information for easy evaluation
- Systematic scan over your whole database for serveral performance pitfalls (more than 100 considered aspects)
- it focuses on usage by DBAs and also by software developers with less Oracle knowledge
- aims to many issues that are inadequately analyzed and presented by other existing tools such as Enterprise Manager
- Panorama only reads via SELECT-SQLs from database. No write access, own database objects, compile etc. is needed at your database.
- Supports:
- Oracle databases on standard hardware as well as RAC-systems, engineered systems (EXADATA) and Autonomous Database in OCI
- Tested against Oracle database versions beginning with Oracle 11.2
- Here you can find a function overview by available top level menu entries
Which preconditions are required to use Panorama?
- Container runtime environment like Docker, Kubernetes, Ranger etc.
- Web browser with ES6-support to access Panorama-GUI (Firefox, Chrome, Safari, Edge)
- Your Oracle-User used for login with Panorama requires at least the grant SELECT ANY DICTIONARY
Additional grants/privileges are required for some particular functions:
Grant | Reason for the grant |
SELECT ANY DICTIONARY | Default grant for access on DBA_xxx-views. This is the minimum requirement to run Panorama |
OEM_MONITOR | Starting with Oracle 11.2.0.4 you need this grant to generate Oracle's builtin AWR-reports and ASH-reports |
SELECT_CATALOG_ROLE | For getting results from DBMS_METADATA.GET_DDL and for Autonomous Database in Oracle cloud you need this role |
EM_EXPRESS_BASIC | For getting results from DBMS_PERF (Performance Hub report) you need this grant |
ANALYZE ANY | For getting results from DBMS_SPACE.SPACE_USAGE you need the ANALYZE privilege on the particular object or the ANALYZE ANY privilege |
SELECT ANY TRANSACTION | This grant is needed for selection on Flashback_Transaction_Query |
- For non-admin users in the autonomous DB in the OCI cloud additional grants are required for some minor functions:
Grant | Resaon for the grant |
SELECT ON V$DIAG_ALERT_EXT | Allow read on particular table |
READ ON SYS.DBMS_LOCK_ALLOCATED | Allow read access |
READ ON gv$BH | Allow read access |
AUDIT_VIEWER | Allow read access on unified audit view |
- Several functions of Panorama are functional only if either:
- Oracle's "Diagnostic Pack" is licensed for the connected Oracle-DB or
- You use the Panorama-Sampler instead to gather historic data (see also)
- Panorama's function "SQL monitor" is available only if Oracle's "Tuning Pack" is licensed for the connected Oracle-DB
License
You can use Panorama free of charge under the terms and conditions of
GNU General Public License.
How to run it?
Start running quickly
Panorama supports two ways to run it: as Java application or as Docker container.
Run as Java application
- Download the latest release of Panorama.jar from here
- Start Panorama.jar by typing
java -jar Panorama.jar
in a terminal
Run as Docker container
To download the image and start the Panorama application as Docker container type:
docker run -p8080:8080 -d rammpeter/panorama
This starts the application within one or two minutes. Now you can connect to Panorama's login screen in your browser via
http://<your server>:8080
Customize a Panorama instance running as Docker container
You can customize your Docker container:
Example:
docker run --name panorama -p8080:8080 \
-v $TNS_ADMIN/tnsnames.ora:/etc/tnsnames.ora \
-v /var/opt/my_panorama_config:/var/opt/panorama \
-e TNS_ADMIN=/etc -e TZ="Europe/Berlin" \
-e MAX_JAVA_HEAP_SPACE_MB=1024 \
-e SECRET_KEY_BASE=<my secret server side key>
-d rammpeter/panorama
Startup of application in docker container may require one or two minutes.
Your docker container is up and running if this two lines appear in Docker log:
> INFO:oejs.ServerConnector:main: Started ServerConnector@6a83bd10{HTTP/1.1}{0.0.0.0:8080}
> INFO:oejs.Server:main: Started @137606ms
Running docker-container with SSL encryption for https access
This
blog post describes how to configure Panorama and Nginx with docker-compose for SSL-encryption.
Options for customizing the Panorama application (Java + Docker)
Environment variables for customization:
MAX_CONNECTION_POOL_SIZE |
This setting allows you to define the maximum number of connections in Panorama's connection pool
If you don't set this the default is max. 100 connections.
That means:
- Up to 100 different database connections are cached for reuse in Panorama even if they are inactive.
- Maximum 100 concurrent client requests can be served at the same time limited by the connection number.
|
MAX_JAVA_HEAP_SPACE_MB |
Set maximum Java heap space memory usable for Panorama in megabytes (Default=1024).
The suggested value for multiuser production use is 4096.
If running as pure Java program (Panorama.jar) then use the Java parameter -Xmx4096m instead.
|
PANORAMA_MASTER_PASSWORD |
Setting a master password in environment adds an additional menu item "Spec. additions"/"Admin login" in Panorama GUI.
Applying this password at "Admin logon" adds additional control functions to the GUI menu, e.g. Panorama-Sampler configuration.
|
PANORAMA_USAGE_INFO_MAX_AGE |
Maximum age in days for logging records in Usage.log to comply with european GDPR rules. |
PANORAMA_VAR_HOME |
Panorama stores encrypted session connect data and usage log in local filesystem.
This setting controls the used directory inside the Docker container (Default: /var/opt/panorama).
It is suggested to mount this directory to the local filesystem outside the Docker container.
|
PANORAMA_LOG_LEVEL=debug|info|warn|error |
Set the level for output in Docker log.
Default log level is "info".
|
PANORAMA_LOG_SQL=true |
Force the output of executed SQL statements in Docker log.
This helps to reproduce how Panorama gets information from database (without setting PANORAMA_LOG_LEVEL=debug which logs the same + much more).
|
SECRET_KEY_BASE |
Server side key used to encrypt stored connection info. See also What about security? |
SECRET_KEY_BASE_FILE |
Location of file with server side key used to encrypt stored connection info. See also What about security? |
Run demo installation
-
Run preinstalled Panorama at http://158.101.168.240:8080 with access to a demo database instance at Oracle cloud.
Use one of the TNS-aliases PANORAMATEST_xxx with user = 'panorama_test' and password = 'TryItOut2019'
What about security?
- For production purposes you should run Panorama with SSL-encrypted web traffic (https). Easiest way for SSL connections is to run Panorama in a configured Tomcat etc. or use Nginx/Apache as reverse proxy.
- Your Oracle DB connection's password is stored encrypted in server memory and will be decrypted shortly only for the process of establishing connection to your target database.
The key used for this encryption consists of the server side key (SECRET_KEY_BASE
) which is salted with a unique client key specific to your browser instance.
This way only requests from your browser are able to reuse the stored connect info for reestablishing the database connection.
- If you retain your login info for further fast logins (checkbox 'Save logins' in statup dialog), this info is stored in local server file system at
PANORAMA_VAR_HOME
completely encrypted the same way like passwords.
- You can customize the server side key used for encryption by one of the following:
- create a environment variable
SECRET_KEY_BASE
with the key value.
- create a file with the key value and set the environment variable
SECRET_KEY_BASE_FILE
to the location of this file.
The key can contain all printable characters and should be at least 128 characters long.
Every change of the given key at server side leads to invalidation of all stored connection info.
If no fixed key is given by SECRET_KEY_BASE
or SECRET_KEY_BASE_FILE
then a system generated key is used for encryption.
This system generated key is stored in the folder declared by PANORAMA_VAR_HOME
.
-
Caution: If you don't set a value for
PANORAMA_VAR_HOME
then your systems temporary folder is used to store the encyryption key base as well as the saved encrypted login credentials.
That means this information may be lost at OS or container restart!
Behind the scenes: Implementation details
- Panorama is an Ruby on Rails application that runs with JRuby. Web resources you can find here:
- If you use
tnsnames.ora
for TNS name resolution it should be located at server site in $ORACLE_HOME/network/admin
or in a directory targeted by environment variable TNS_ADMIN
.
- Panorama serves an arbitrary number of multiple web-client sessions at one time, each client may choose his own target database connection
- Panorama internally uses a connection pool, so client request may reuse existing free connections from pool if same credentials are used (TNS-data, user/password)
- Inactive database connections in connection pool are terminated 10 minutes after last usage
- Panorama renders one single web page via Ajax-calls. This way the back button of your browser is not supported to return to former page content.
- Panorama writes short usage information to local plain text file 'Usage.log' in the directory targeted by environment variable
PANORAMA_VAR_HOME
This log records cointain the client IP address, DB name, time, called function and used TNS alias/URL
Setting PANORAMA_USAGE_INFO_MAX_AGE = 0 disables this logging completely.
You can view this log info via http://<your server ip>:8080/usage/info
- Panorama pools (reuses) DB-connections. You can view pooled connections at Panorama server via
http://<your server ip>:8080/usage/connection_pool