Oracle Database monitoring

Track the availability and performance of your Oracle Database instances and databases by monitoring the key performance indicators—including the pluggable database (PDB), response time, hit ratios, System Global Area (SGA), Program Global Area (PGA), Tablespaces, Sessions, Redo log files, and Automatic Storage Management (ASM) disk group—and get instant alerts whenever there is a breach.

Once the agent is successfully installed, the Oracle Database running on your server will be auto-discovered and added for monitoring.

Prerequisites

1. Supported versions:

Oracle versions: 12c and above
Linux Site24x7 Server Monitoring agent version 19.8.0

Info: You can also use Oracle Instant Client setup to start monitoring. Skip this step if you are not using Instant Client.

Follow the steps in the Oracle Instant Client Installation guide to install the Instant Client according to your OS and architecture.
Ensure that the unzipped directory is accessible to the Site24x7 Linux Agent on your server. Note that this Instant Client path will be required for Site24x7 Oracle monitoring.
- Example:
```
mkdir -p /opt/oracle/ && cd /opt/oracle/
unzip /opt/oracle/instantclient-basiclite-linux.x64-21.13.0.0.0dbru.zip
```

2. You need to create a user in the Oracle database for the Site24x7 server monitoring agent. With the following instructions, you can create a user and grant the Oracle Database user permission.

CREATE USER C##<username> IDENTIFIED BY "<password>" CONTAINER=ALL;

Example:

CREATE USER C##site24x7 IDENTIFIED BY "Monitoring@123" CONTAINER=ALL;

3. The Site24x7 agent needs a few privileges to collect the metrics. You need to provide the Oracle user with the following limited privileges by connecting to the CDB container (CDB$ROOT) with the SYS user:

GRANT CREATE SESSION TO <username> CONTAINER=ALL;
GRANT CONNECT TO <username> CONTAINER=ALL;
GRANT SELECT_CATALOG_ROLE TO <username> CONTAINER=ALL;

ALTER USER <username> SET container_data=all container=current;

Add an Oracle Database monitor

If there are multiple Oracle Database instances, repeat the above steps for each Oracle Database instance. After providing the required permissions, you can proceed to add an Oracle Database monitor using the following steps.

Log in to Site24x7 and install the Site24x7 Linux server monitoring agent on your Linux server.
Site24x7 server monitoring agent automatically discovers Oracle Database instances in your servers and adds a sample view of the Oracle monitor on the Home tab. To start monitoring, follow the instructions below to authenticate Site24x7 to collect metrics.

Optional: If you are using Instant Client, enter the Instant Client path procured from the prerequisite steps. This entry is a one-time step, and it need not be repeated for all the instances. This Instant Client path will be used by the Site24x7 agent for all the Oracle instances.

To update the Instant Client path if it is changed, use the below command:
```
/opt/site24x7/monagent/scripts/AgentManager.sh oracledb --update_library_path
```
Execute the following command in your terminal and enter your Oracle Database instance's username and password from Step 1, as well as the Host Name, Port, and Service Name for the Site24x7 agent to authenticate and configure Oracle monitoring:
```
 /opt/site24x7/monagent/scripts/AgentManager.sh oracledb --add_instance
```
Note
This step has to be repeated for every Oracle instance in the server to be monitored.
After logging in to Site24x7, click Database > Oracle on the navigation pane.
You will be directed to the monitor page to view the list of Oracle Database instances you have added to monitor.
Under the Oracle Database instance, click PDB to view the list of databases in the selected instance.
Note
- The username and password you provide will be securely encrypted in the agent and will not be stored in any of the Site24x7 databases.
- If you don't want Oracle to be auto-discovered and added for monitoring, toggle the Auto discover applications on the server option to No in the Settings page (Admin > Server Monitor > Settings).
- By default, ten pluggable databases and ten Tablespaces will be auto-discovered and added in each Oracle Database instance.

Performance metrics

Monitoring the key performance metrics of your Oracle Database instance is highly crucial. By closely tracking these parameters, you can proactively identify potential bottlenecks, troubleshoot issues, and optimize the overall efficiency of your Oracle environment.

Individual pluggable database (PDB) monitoring

Site24x7 offers individual PDB monitoring as well. Track the critical performance metrics of your pluggable databases, set thresholds, and get alerts.

Threshold configuration

After adding the Oracle monitor to your Site24x7 account, you can add a threshold and availability profile to set thresholds and receive alerts when there is a breach and take corrective action.

To set a threshold and availability profile:

Select the Oracle monitor, click the hamburger icon > Edit.
On the Edit Oracle page, you will find the Threshold and Availability tab under Configuration Profiles. Click the plus + icon to add a new threshold profile. Or click the pencil icon to edit a threshold profile.
Click Save.

You will receive alerts when a particular threshold is breached, and the specific resource will be declared as Trouble or Critical.

Dashboards

After successfully adding an Oracle instance monitor, you can view your instances and databases by logging into your Site24x7 account and then navigating to Database > Oracle Database > Health Dashboard. You can analyze the performance of your Oracle instance environment and your individual PDBs and detect performance outages. From here, you can:

See the total number of all the Oracle instances and PDBs in one view.
View the current status of all the Oracle servers as separate NOC dashboards. Click on an NOC box to go to that particular resource's Summary page.
Identify issues faster by seeing the status of your servers as Down, Critical, or Trouble.
Analyze the top CPU and memory utilizing instances to troubleshoot performance issues instantly and avoid future performance degradation.

Security

The Site24x7 server monitoring agent never stores your data in the Site24x7 app servers. The username and password are encrypted in the agent for security reasons.

If you have more questions or concerns, check out some FAQs on the security aspects of our Oracle Database monitoring.

FAQs

Is it mandatory to download the Instant Client for Oracle monitoring?
No. By default, we connect to your Oracle database using Thin Client for monitoring purposes. We can also make Oracle database connections using Thick Client, for which we would require Oracle Instant Client to be downloaded and extracted. You should also provide the extracted folder path of the Instant Client to our agent using the below command:
```
 /opt/site24x7/monagent/scripts/AgentManager.sh oracledb --update_library_path
```
Should I update the Instant Client path for every Oracle database instance on the same machine?
No, you would not be required to provide multiple Instant Client paths. Instant Client is a set of .so files (library files) provided by Oracle, and is the only way by which we can make a connection to the Oracle database. So, a single Instant Client path is sufficient.
I have given the wrong library path by mistake. How do I update it?
You can use the below command to update the Instant Client:
```
/opt/site24x7/monagent/scripts/AgentManager.sh oracledb --update_library_path
```
Example:
Does Site24x7 need write permission to access databases?
No.
What performance data is collected by Site24x7 for monitoring Oracle Databases?
Site24x7 collects data for the performance metrics listed in the Oracle performance metrics document for monitoring Oracle Databases.
How does Site24x7 collect performance metrics for Oracle monitoring?
Performance metrics are collected using SQL queries by connecting to the Oracle Database with the user credentials given by the user, which has read-only access.
How does Site24x7 connect with Oracle Database?
Site24x7 connects with the Oracle Database using the user credentials given in the terminal console and collects data.
Does Site24x7 keep the Oracle connection open?

No. Site24x7 creates a connection to the Oracle Database for collecting performance metrics. Once the data collection is complete, the connection will be closed.
Does Site24x7 store the Oracle user password directly?

No. Site24x7 encrypts the password given. The username and password are encrypted in the agent for security reasons. They will not be saved or stored in the Site24x7 app servers.

Licensing

Each Oracle Database instance consumes one Advanced monitor license and includes monitoring for 25 PDBs. For each additional PDB, one Basic monitor license will be consumed. For now, each Oracle instance and each PDB can include monitoring of up to 10 Tablespaces. For more information, contact [email protected].