Have you at any point simply sat there observing the greater part of the procedures running against the database, simply sitting tight to something to happen? Obviously not! We are database developers, administrators, architects and we don’t have time for that! We likewise may get bored wondering why there is by all accounts a large number of a similar inquiry running against the database – an exchange for some other time. In any case, there would be reasons that we are watching procedures or explanations running in against the database and it is typically in light of the fact that something was alarmed or the telephone rang asking, “Why is the database running so slow”.

The unavoidable issue is the thing that would it be a good idea for us to be cautioned on to endeavour to beat the telephone call to an answer, or what are a portion of the basic issues regardless we need to manage in more mechanized situations? Indeed, even with checking devices, what are the alarms we require and the edges to set them at? There are a few things that I can consider to monitor, however there have been a chosen few that have made the database be down or not accessible regardless of what option or version.

The observing should be possible with instruments, for example, Oracle Enterprise Manager or other checking apparatuses, or contents, yet the rundown is short and there are particular things to search for to keep the blackout or issue. Here is my short rundown to have checked on any database:

1.  Listener

The listener being accessible is obvious for new associations coming into the database. Possibly the listener is presently in the matrix home or the database home however in the event that it isn’t up and running there is no entrance to the database. The listener is anything but difficult to check in the event that it is accessible at the OS level with checking for TNS process. Additionally utilizing lsnrctl order line will give status of the listener and the databases that it is tuning in for. The log will be valuable for investigating availability issues and refusals. Here utilizing OEM to adjust on if the audience is accessible or utilizing contents to monitor the procedure to ensure they are running. Signing into the database could be a piece of another test and error messages would be like ORA-12560: TNS:protocol connector mistake or other ORA – : TNS mistakes. Be that as it may, not all TNS errors are listener issues which prompt the following thing.

2. Procedures

Hitting max procedures can either cause a login to hang which makes it in some cases hard to monitor, particularly in the event that you are signing into the database to monitor it. It can likewise give a TNS error, which can delude you for a little time until looking through the ready log and finding the ORA-00020: most extreme number of procedures blunder in the log. Here an edge is helpful to monitor achieving a percent of max forms, so when it hits 80% there is a great opportunity to explore what is happening to forestall bolt out of the database. When hitting the edge, executing forms is a choice to avert achieving the maximum procedures, since when expecting to change the parameter will require a restart of the case. Changing the procedures parameter is not generally the answer for this issue, but rather it may give a Band-Aid to take a gander at alternatives and make sense of why and where the procedures are originating from.

3. Recovery area and archive log

Archive log space will make the framework hang and give the feared ORA-00257: archiver error, connect internal only, until cleared. Thus observing the space accessible on the mount point is critical. Setting limits to checking when 80 or 85% of space utilized is come to ought to enable time to go down the archive logs and evacuate them to free up space. When utilizing the recovery area, the alert log will have warnings in view of the edges that Oracle has set to monitor the recovery area. These are set at 85%, with a critical alert at 97%. Utilizing the ready log could monitor these edges, or utilizing v$flash_recovery_area_usage to monitor.

Space can be effectively added to the recovery area by the db_recovery_dest_size parameter progressively. This is a simple issue to forestall with appropriately estimating the archive log space and dealing with the backup of the archive logs, which makes this a frustrating eror to keep running into and makes checking this region significantly more critical.

4. Space, tablespace and filesystem

More space issues that can cause issues, however these space issues may not cause a system wide blackout like the issues recorded as of now; this relies upon how tablespaces are distributed. The framework tablespaces, for example, SYSTEM, TEMP, UNDO, SYSAUX, can cause more issues yet are genuinely simple to monitor and set limits in the monitoring by percent full. The trap here with the greater part of the tablespace is representing auto expand, and in the event that you have larger tablespaces, the percent limits won’t be appropriate, and simply provide alert noise if 10% free still has 100 to 200 GB free.

Having the capacity to monitor both size and percent is useful, and having ordinarily just setup some shell scripts to do this, which at that point can be utilized as a part of the observing devices. Likewise with the auto extending of tablespace, the secondary issue is the file system space, and that is sufficiently simple to monitor with OS or monitoring tools. Another good thing to monitor to be proactive about space is monitor in light of the auto extending and whether that could become past the size of file system.

5. Database accessibility

Regardless of whether the database is up or down is most likely extremely clear thing to monitor for and may appear to be basic as well. You can check for smon, pmon and different procedures to ensure that they are running, and this will work on the off chance that you don’t keep running into different issues, similar to archive space, forms or tablespace issues. Regardless of the possibility that the procedures are accessible, the login to the database will either hang or demonstrate another error, so really signing into the database and a simple select will appear if access to the database is available.

Conclusion

This is by far not the entire rundown of things to monitor for, yet they appear like issues that fly up each time they are not being observed. A large portion of these do cause issues that will cause the database example to hang or not permit any new associations. They are straightforward things to monitor for in accessible tools or basic contents and they have their little traps to give the correct level of proactive monitoring.