4 Basic Techniques to Troubleshoot Your Oracle 12c System Methodology

Your Oracle 12c database requires multiple systems on your computer to run in order to support the databases you create. Here are some systems to check if you are having issues with your database:
CPU Usage: Has the CPU Exceeded Maximum?
Memory: How much memory is available?
Available Disk Space: Is there disk space available?
System event logs: Is anything reported to the system?
You do not need to be a system administrator to perform these checks.
 
Advertising CPU Usage in Oracle 12c
Servers may have single or multiple CPUs. Regardless, processing must be available for the server to process application requests.
If the device has very high or full CPU usage, performance issues will occur.
In Windows, the previously mentioned Windows Task Manager has a Performance tab. This tab displays CPU usage as a percentage and as recent highs
For Linux/UNIX systems, the top command described earlier displays the CPU usage at the top of the screen.

Oracle 12c and memory
If the server is lacking memory, system performance suffers or even stands still. It isn’t desirable to run a server with little or no memory available. If you find the server is consistently memory starved, either add more memory or reduce the amount of memory allocated for programs.
To check memory on Windows, the Windows Task Manager Performance tab provides the total memory on the machine and amount available.
Linux/UNIX systems have multiple tools to check memory, but the top utility provides this information rapidly.
Available disk space and Oracle 12c
Disk space is different than disk utilization. The frequency of reads and writes on a disk is utilization and can be a major performance factor. Running out of available disk space can bring your system to a halt and is the focus here because you, as the DBA, can do something about it.
What can happen when a disk fills up? It depends on what’s writing to that disk. At minimum, log files can’t be written to and tablespaces can’t be expanded. At worst, archive log files can’t be successfully written, and the database hangs.
With other software, processes can spin high amounts of CPU, and Java Virtual Machines can crash. Any one of these problems is likely to generate a panicked call to your desk.
The quickest way to check for disk space is to see if any file systems are 100 percent full.

• For Windows systems, the fastest way is to go to my Computer and look at free space for each disk drive. Be sure to have the Details option selected on the View tab to get the full information.

• The df -m command helps Linux and Oracle Solaris users:

$ df -m
Filesystem 1M-blocks Used Available Use% Mounted on
/dev/sda3 8064 3587 4068 47% /
tmpfs 3957 1885 2072 48% /dev/shm
/dev/sda5 8064 208 7447 3% /home
/dev/sda6 8064 147 7508 2% /tmp
/dev/sda1 64310 17016 44028 28% /u01
/dev/sr0 2367 2367 0 100% /media/Oracle12cBeta
 
This code shows the file systems, their percentage used, percentage free (available), and actual amounts in megabytes. The df -k command can show the same information but listed in kilobytes. The -k flag is useful because some versions of UNIX do not support the -m flag.
On HP-UX UNIX systems, use bdf.
Oracle 12c and system event logs
If all else fails, listen to what the computer is telling you. As a DBA, you must have at least read access to the system event logs on your server and hopefully all servers that are part of the application.
System event logs record routine events on the server, but they may also list special error events that may be the cause of your system problems. Sometimes event logs clearly list hardware issues, file systems are full, or the device just rebooted or just crashed. These are all good things to know when you are trying to track down an issue.
The location of the event logs can vary, and often the additional logs are outside the operating system logs for you to review as you get to know your system.
On Windows, go to Control Panel -> Administrative Tools -> Event Viewer to see the system and application logs.
On Linux/UNIX systems, /var/adm/messages and /var/log/syslog are very valuable. The dmesg command can see the end of the most recent system log file.
Much of this information may not make sense to a database administrator who is not an operating system expert; However, seeing the errors can be enough to get the opinion of the sysadmin.