Database link check

Job details

Name:	Database link check
Platform:	Oracle
Category:	Availability
Description:	Checks database links.
Long description:	In order to check private database links you have to create a following view ‘create view dbw_link_check as select username from user_users@DB_LINK‘ in the database link owner schema, and then grant select privilege to dbwatch user on that view (‘grant select on dbw_link_check to dbwatch’).
Version:	2.5
Default schedule:	15,45 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & _priv_read_dba_db_links = ‘1′ ]

Name	Default value	Description
ignore database links		Database links names excluded from being checked (comma separated).
return status when database link is down	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when database link is down.
check private db links	NO

Purpose: The purpose of this job is to monitor and check the status of database links in an Oracle database environment. This includes identifying whether the links are operational and logging any errors associated with non-functioning links.
Why: This job is critical to ensure database links are functional, which is fundamental for database operations relying on these links for data access and integration across different databases. Timely detection of failed links prevents potential disruptions in operations and data processes.
Manual checking: You can check this manually in the database by issuing SQL commands to query the dba_db_links view:

SELECT * FROM dba_db_links;

The procedure begins by clearing out an existing custom table “dbw_db_links_tab” which is used to store database link status.
A cursor is declared to fetch database link details from the dba_db_links view.
For each link, depending on whether it’s a public link or private (and if private checks are enabled), appropriate SQL commands are constructed and executed to test the link and capture the outcome.
Errors encountered during link checks are captured and stored in the “dbw_db_links_tab”.
If a database link is found down and not listed in the “ignore database links” parameter, it increments a counter for defective links.
Finally, the job updates the monitoring record with a status depending on the number of defective links found and logs detailed information about down links or the total links checked.

Parameter	Description
“ignore database links”	Database link names excluded from being checked (comma-separated).
“return status when database link is down”	Specifies the return status for different scenarios (ALARM – 2, WARNING – 1, OK – 0) when a database link is down.
“check private db links”	Enables the check for private database links when set to “YES“. Requires a specific helper view to be created and select privilege granted to the Dbwatch user.

The main procedure requiring db_link_check relies on these dependencies:
- Main check logic (object-type 2) – Contains the primary logic to assess the links.
- Helper table (object-type 0) – Used for logging and operational purposes during the check sequence. If the job fails, cleanup is initiated.

An automated report template is set to generate results on database links, showing statuses in tabular form.
Key data presented in the report:
- Database link ownership, link name, associated username, host, creation date, and possible error messages if any.
- This structured format assists in quick identification and resolution of issues with database links.

The procedure includes safety measures such as capturing exceptions, rolling back transactions if needed, and logging errors adequately for troubleshooting.
The job is scheduled to run twice every hour at the 15th and 45th minute by default, ensuring regular and timely checks.