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?
Which preconditions are required to use Panorama?
License
How to run it?
How to get help?
What about security?
Behind the scenes: Implementation details

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:

Mount your host's tnsnames.ora at $TNS_ADMIN/tnsnames.ora into the container in a folder and tell it Panorama by setting $TNS_ADMIN in container so Panorama can refer to your config:
```
-v $TNS_ADMIN/tnsnames.ora:/etc/tnsnames.ora -e TNS_ADMIN=/etc
```
Caution: with Docker in Windows sometimes you have to mount the file tnsnames.ora with
```
-v $TNS_ADMIN/tnsnames.ora:/etc
```
Mount directory for Panorama's config files (/var/opt/panorama) to a directory of your choice outside the container so the config persists even if container is rebuilded
Set your timezone for Docker container (default is UTC):
```
-e TZ="Europe/Berlin"
```
Set additional customizing properties for Panorama application as Docker container environment variables

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. Set to 0 to completely disable logging into Usage.log.
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 enclose Panorama behind a reverse proxy ( Nginx/Apache/Traefik).
Your Oracle DB connection's password is stored encrypted at server side and will be decrypted shortly in server memory 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 (stored as browser cookie).
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:
- Source code, last commit is for release from
- Version history
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 find a menu item "Usage history" to view this log info if logged in as admin (see PANORAMA_MASTER_PASSWORD)
Panorama pools (reuses) DB-connections. You can view pooled connections at Panorama server via
```
http://<your server ip>:8080/usage/connection_pool
```