4 Basic Techniques to Troubleshoot Your Oracle 12c System Methodology
Your Oracle 12c database requires several systems on your computer to function in order to support the databases you create. Here are a few systems to check if you are experiencing problems with your database:
CPU utilization: Is the CPU maxed out?
Memory: How much memory is available?
Available disk space: Is there disk space available?
System event logs: Is anything being reported to the system?
You don’t need to be a system administrator to perform these checks.
Oracle 12c ad CPU utilization
Servers may have single or multiple CPUs. Regardless, processing needs to be available for the server to process application requests.
If a machine has a very high or complete CPU use, performance issues will occur.
On Windows, the previously mentioned Windows Task Manager has a Performance tab. That tab displays CPU use as a percentage and as recent spikes
For Linux/UNIX systems, the previously described top command displays CPU utilization 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 the actual amounts in megabytes. The df -k command can show the same info but listed in kilobytes. The –k flag is useful because some versions of UNIX don’t 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 should 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 also may list special error events that could be the cause of your system problems. Sometimes the event logs clearly list hardware issues, or that the file systems are full, or that the machine just rebooted or crashed. All of these are good things to know when you’re trying to track down a problem.
The location of the event logs can vary, and often additional logs are beyond the OS logs to review when you learn your system.
On Windows systems, 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 quite 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 DBA who isn’t OS savvy; however, seeing errors can be enough to seek the opinion of the system administrator.