Monitoring Oracle Tablespaces

General
,
8

Its’ okay now? I tried to rescan the checkmk monitor services of my host, but there is nothing new.

Configuración de Checkmk

$DBUSER=@(“dbuser”, “password”, “”, “host”, “port”,“SID”)

Secciones síncronas y asíncronas

$SYNC_SECTIONS=@(“instance”, “performance”, “processes”, “sessions”, “longactivesessions”, “logswitches”, “undostat”, “recovery_area”, “recovery_status”, “dataguard_stats”, “resumable”, “locks”, “systemparameter”)
$ASYNC_SECTIONS=@(“tablespace”,“rman”,“locks”)
$CACHE_MAXAGE=600

Parámetros de conexión a Oracle

$oracleUser = $DBUSER[0]
$oraclePassword = $DBUSER[1]
$oracleHost = $DBUSER[3]
$oraclePort = $DBUSER[4]
$oracleSID = $DBUSER[5]

Cadena de conexión sin privilegios de DBA

$connectionString = “User Id=$oracleUser;Password=$oraclePassword;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$oracleHost)(PORT=$oraclePort)))(CONNECT_DATA=(SERVICE_NAME=$oracleSID)))”

Conexión a Oracle

try {
Write-Host “Loading Oracle DLL…”
Add-Type -Path “C:\ProgramData\checkmk\agent\plugins\Oracle.ManagedDataAccess.dll”
Write-Host “DLL loaded successfully”

Write-Host "Creating Oracle connection..."
$oracleConnection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
Write-Host "Opening Oracle connection..."
$oracleConnection.Open()
Write-Host "Connection opened successfully"

# Determinar si la sección es asíncrona o no
if ("tablespace" -in $ASYNC_SECTIONS) {
    Write-Host "<<<oracle_tablespace:cached($CACHE_MAXAGE)>>>"
} else {
    Write-Host "<<<oracle_tablespace>>>"
}

# Consulta SQL mejorada
$query = @"
SELECT t.tablespace_name, 
       ROUND(SUM(df.bytes) / 1024 / 1024, 2) AS allocated_mb, 
       ROUND(SUM(df.bytes - NVL(f.free_bytes, 0)) / 1024 / 1024, 2) AS used_mb,
       CASE 
           WHEN SUM(df.bytes) = 0 THEN 0
           ELSE ROUND((SUM(df.bytes - NVL(f.free_bytes, 0)) / SUM(df.bytes)) * 100, 2) 
       END AS used_percent
FROM dba_tablespaces t
JOIN dba_data_files df ON t.tablespace_name = df.tablespace_name
LEFT JOIN (
    SELECT tablespace_name, SUM(bytes) AS free_bytes 
    FROM dba_free_space 
    GROUP BY tablespace_name
) f ON t.tablespace_name = f.tablespace_name
GROUP BY t.tablespace_name
HAVING SUM(df.bytes - NVL(f.free_bytes, 0)) >= 0

"@

Write-Host "Executing query..."
$command = $oracleConnection.CreateCommand()
$command.CommandText = $query
$reader = $command.ExecuteReader()
Write-Host "Query executed successfully"

# Leer los resultados
while ($reader.Read()) {
    $tablespace = $reader["tablespace_name"]
    $allocatedMb = $reader["allocated_mb"]
    $usedMb = $reader["used_mb"]
    $usedPercent = $reader["used_percent"]
    
    # Mostrar la salida en el formato esperado por Checkmk
    Write-Host "$tablespace|$allocatedMb|$usedMb|$usedPercent"
}

# Cerrar la conexión
$oracleConnection.Close()
Write-Host "Connection closed successfully"

} catch {
Write-Host “Error: $($_.Exception.Message)”
}

1 Like