PowerShell functions for monitoring Oracle database systems in LIMS environments, including connection testing, performance monitoring, and health checks.
Required Oracle Components:
PowerShell Setup:
# Load Oracle .NET Data Provider
Add-Type -Path "C:\Oracle\ODTwithODAC\odp.net\managed\common\Oracle.ManagedDataAccess.dll"
# Or for unmanaged version:
# Add-Type -Path "C:\Oracle\product\client\ODP.NET\bin\4\Oracle.DataAccess.dll"
---
#region Oracle Connection and Health
function Test-OracleForLIMS {
param(
[string]$ServerName = "localhost",
[string]$Port = "1521",
[string]$ServiceName = "ORCL",
[string]$DatabaseName = "LIMS",
[string]$Username = "lims_user",
[string]$Password,
[int]$TimeoutSeconds = 30
)
# Build Oracle connection string
$connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ServerName)(PORT=$Port))(CONNECT_DATA=(SERVICE_NAME=$ServiceName)));User Id=$Username;Password=$Password;Connection Timeout=$TimeoutSeconds;"
Write-Host "Testing Oracle connection for LIMS..." -ForegroundColor Yellow
Write-Host "Server: $ServerName`:$Port" -ForegroundColor Gray
Write-Host "Service: $ServiceName" -ForegroundColor Gray
Write-Host "Schema: $Username" -ForegroundColor Gray
try {
# Test basic connectivity
$stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
$connection.Open()
# Get server information
$command = New-Object Oracle.ManagedDataAccess.Client.OracleCommand(@"
SELECT
(SELECT BANNER FROM V`$VERSION WHERE ROWNUM = 1) AS VERSION,
SYS_CONTEXT('USERENV','SERVER_HOST') AS SERVER_HOST,
SYS_CONTEXT('USERENV','DB_NAME') AS DATABASE_NAME,
USER AS CURRENT_USER,
SYSDATE AS SERVER_TIME,
SYS_CONTEXT('USERENV','INSTANCE_NAME') AS INSTANCE_NAME
FROM DUAL
"@, $connection)
$reader = $command.ExecuteReader()
$serverInfo = @{}
if ($reader.Read()) {
$serverInfo = @{
Version = $reader["VERSION"].ToString()
ServerHost = $reader["SERVER_HOST"].ToString()
DatabaseName = $reader["DATABASE_NAME"].ToString()
User = $reader["CURRENT_USER"].ToString()
ServerTime = $reader["SERVER_TIME"]
InstanceName = $reader["INSTANCE_NAME"].ToString()
}
}
$reader.Close()
# Test a simple query performance
$testCommand = New-Object Oracle.ManagedDataAccess.Client.OracleCommand("SELECT COUNT(*) FROM USER_TABLES", $connection)
$testResult = $testCommand.ExecuteScalar()
$stopwatch.Stop()
$connection.Close()
$result = [PSCustomObject]@{
Status = "Success"
Server = "$ServerName`:$Port"
ServiceName = $ServiceName
Schema = $Username
ResponseTimeMs = $stopwatch.ElapsedMilliseconds
OracleVersion = $serverInfo.Version
ServerHost = $serverInfo.ServerHost
DatabaseName = $serverInfo.DatabaseName
ConnectedAs = $serverInfo.User
ServerTime = $serverInfo.ServerTime
InstanceName = $serverInfo.InstanceName
TableCount = $testResult
Timestamp = Get-Date
}
Write-Host "✓ Connection successful" -ForegroundColor Green
Write-Host "Response time: $($stopwatch.ElapsedMilliseconds)ms" -ForegroundColor Gray
Write-Host "Connected as: $($serverInfo.User)" -ForegroundColor Gray
Write-Host "Server time: $($serverInfo.ServerTime)" -ForegroundColor Gray
Write-Host "Instance: $($serverInfo.InstanceName)" -ForegroundColor Gray
return $result
}
catch {
Write-Host "✗ Connection failed" -ForegroundColor Red
Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red
# Provide specific guidance for common Oracle errors
if ($_.Exception.Message -like "*ORA-01017*") {
Write-Host "Hint: Invalid username/password - check credentials" -ForegroundColor Yellow
}
elseif ($_.Exception.Message -like "*ORA-12541*") {
Write-Host "Hint: TNS:no listener - check Oracle listener service and firewall" -ForegroundColor Yellow
}
elseif ($_.Exception.Message -like "*ORA-12154*") {
Write-Host "Hint: TNS:could not resolve service name - check service name or TNS configuration" -ForegroundColor Yellow
}
elseif ($_.Exception.Message -like "*timeout*") {
Write-Host "Hint: Check network connectivity and Oracle database responsiveness" -ForegroundColor Yellow
}
return [PSCustomObject]@{
Status = "Failed"
Server = "$ServerName`:$Port"
ServiceName = $ServiceName
Schema = $Username
Error = $_.Exception.Message
Timestamp = Get-Date
}
}
}
function Get-OracleLIMSPerformance {
param(
[string]$ServerName = "localhost",
[string]$Port = "1521",
[string]$ServiceName = "ORCL",
[string]$Username = "lims_user",
[string]$Password,
[string]$ConnectionString
)
if (-not $ConnectionString) {
$ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ServerName)(PORT=$Port))(CONNECT_DATA=(SERVICE_NAME=$ServiceName)));User Id=$Username;Password=$Password;Connection Timeout=30;"
}
$performanceQueries = @{
"DatabaseInfo" = @"
SELECT
NAME AS DATABASE_NAME,
DBID,
CREATED,
LOG_MODE,
OPEN_MODE,
DATABASE_ROLE,
PLATFORM_NAME
FROM V`$DATABASE
"@
"TablespaceUsage" = @"
SELECT
TS.TABLESPACE_NAME,
ROUND(TS.TOTAL_SIZE_MB, 2) AS TOTAL_SIZE_MB,
ROUND(TS.TOTAL_SIZE_MB - NVL(FS.FREE_SIZE_MB, 0), 2) AS USED_SIZE_MB,
ROUND(NVL(FS.FREE_SIZE_MB, 0), 2) AS FREE_SIZE_MB,
ROUND(((TS.TOTAL_SIZE_MB - NVL(FS.FREE_SIZE_MB, 0)) / TS.TOTAL_SIZE_MB) * 100, 2) AS PERCENT_USED
FROM
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS TOTAL_SIZE_MB
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) TS
LEFT JOIN
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS FREE_SIZE_MB
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) FS
ON TS.TABLESPACE_NAME = FS.TABLESPACE_NAME
ORDER BY PERCENT_USED DESC
"@
"ActiveSessions" = @"
SELECT
PROGRAM,
MACHINE,
USERNAME,
COUNT(*) AS SESSION_COUNT,
COUNT(CASE WHEN STATUS = 'ACTIVE' THEN 1 END) AS ACTIVE_COUNT,
COUNT(CASE WHEN STATUS = 'INACTIVE' THEN 1 END) AS INACTIVE_COUNT
FROM V`$SESSION
WHERE USERNAME IS NOT NULL
GROUP BY PROGRAM, MACHINE, USERNAME
ORDER BY SESSION_COUNT DESC
"@
"WaitEvents" = @"
SELECT
EVENT,
TOTAL_WAITS,
ROUND(TIME_WAITED_MICRO/1000000, 2) AS TIME_WAITED_SECONDS,
ROUND(AVERAGE_WAIT, 2) AS AVERAGE_WAIT_MS,
ROUND((TIME_WAITED_MICRO/1000000) / (SELECT SUM(TIME_WAITED_MICRO/1000000) FROM V`$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL*Net%') * 100, 2) AS PCT_WAIT_TIME
FROM V`$SYSTEM_EVENT
WHERE EVENT NOT LIKE 'SQL*Net%'
AND EVENT NOT LIKE '%idle%'
AND TIME_WAITED_MICRO > 0
ORDER BY TIME_WAITED_MICRO DESC
FETCH FIRST 10 ROWS ONLY
"@
"BlockingSessions" = @"
SELECT
w.SESSION_ID AS WAITING_SESSION,
h.SESSION_ID AS HOLDING_SESSION,
w.ORACLE_USERNAME AS WAITING_USER,
h.ORACLE_USERNAME AS HOLDING_USER,
o.OBJECT_NAME,
w.MODE_REQUESTED,
h.MODE_HELD,
w.LOCK_TYPE,
s1.PROGRAM AS WAITING_PROGRAM,
s2.PROGRAM AS HOLDING_PROGRAM,
s1.MACHINE AS WAITING_MACHINE,
s2.MACHINE AS HOLDING_MACHINE
FROM DBA_WAITERS w
JOIN DBA_BLOCKERS h ON w.HOLDING_SESSION = h.HOLDING_SESSION
LEFT JOIN DBA_OBJECTS o ON w.ROW_WAIT_OBJ# = o.OBJECT_ID
LEFT JOIN V`$SESSION s1 ON w.SESSION_ID = s1.SID
LEFT JOIN V`$SESSION s2 ON h.SESSION_ID = s2.SID
"@
"LongRunningQueries" = @"
SELECT
s.SID,
s.USERNAME,
s.MACHINE,
s.PROGRAM,
s.STATUS,
ROUND((SYSDATE - s.LOGON_TIME) * 24 * 60, 2) AS SESSION_MINUTES,
sql.SQL_TEXT,
s.LAST_CALL_ET AS SECONDS_SINCE_LAST_CALL
FROM V`$SESSION s
LEFT JOIN V`$SQLTEXT sql ON s.SQL_ID = sql.SQL_ID AND sql.PIECE = 0
WHERE s.USERNAME IS NOT NULL
AND s.STATUS = 'ACTIVE'
AND s.LAST_CALL_ET > 300 -- More than 5 minutes
ORDER BY s.LAST_CALL_ET DESC
"@
"TopSQLByElapsedTime" = @"
SELECT
SQL_ID,
EXECUTIONS,
ROUND(ELAPSED_TIME/1000000, 2) AS ELAPSED_SECONDS,
ROUND(ELAPSED_TIME/EXECUTIONS/1000000, 4) AS AVG_ELAPSED_SECONDS,
ROUND(CPU_TIME/1000000, 2) AS CPU_SECONDS,
BUFFER_GETS,
DISK_READS,
FIRST_LOAD_TIME,
LAST_ACTIVE_TIME,
SUBSTR(SQL_TEXT, 1, 100) AS SQL_TEXT_PREVIEW
FROM V`$SQLSTATS
WHERE EXECUTIONS > 0
AND SQL_TEXT NOT LIKE '%V`$%'
ORDER BY ELAPSED_TIME DESC
FETCH FIRST 10 ROWS ONLY
"@
"DatabaseSize" = @"
SELECT
'TOTAL DATABASE SIZE' AS METRIC,
ROUND(SUM(BYTES)/1024/1024/1024, 2) AS SIZE_GB
FROM DBA_DATA_FILES
UNION ALL
SELECT
'TOTAL LOG SIZE' AS METRIC,
ROUND(SUM(BYTES)/1024/1024/1024, 2) AS SIZE_GB
FROM V`$LOG
UNION ALL
SELECT
'TEMP FILES SIZE' AS METRIC,
ROUND(SUM(BYTES)/1024/1024/1024, 2) AS SIZE_GB
FROM DBA_TEMP_FILES
"@
}
$results = @{}
Write-Host "Gathering Oracle performance data for LIMS..." -ForegroundColor Yellow
try {
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($ConnectionString)
$connection.Open()
foreach ($query in $performanceQueries.GetEnumerator()) {
Write-Host "Collecting $($query.Key)..." -ForegroundColor Cyan
try {
$command = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($query.Value, $connection)
$adapter = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($command)
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$results[$query.Key] = $dataset.Tables[0]
}
catch {
Write-Warning "Failed to execute $($query.Key): $($_.Exception.Message)"
$results[$query.Key] = $null
}
}
$connection.Close()
# Display key findings
Write-Host "`nOracle Performance Summary:" -ForegroundColor Green
# Database size
if ($results["DatabaseSize"]) {
$totalSizeGB = ($results["DatabaseSize"] | Where-Object {$_.METRIC -eq "TOTAL DATABASE SIZE"}).SIZE_GB
Write-Host "Database size: $($totalSizeGB)GB" -ForegroundColor Cyan
}
# Tablespace usage
if ($results["TablespaceUsage"]) {
$maxUsage = ($results["TablespaceUsage"] | Measure-Object PERCENT_USED -Maximum).Maximum
if ($maxUsage -gt 90) {
Write-Host "⚠ WARNING: Tablespace usage up to $maxUsage%" -ForegroundColor Red
} else {
Write-Host "✓ Tablespace usage under control (max: $maxUsage%)" -ForegroundColor Green
}
}
# Active sessions
if ($results["ActiveSessions"]) {
$totalSessions = ($results["ActiveSessions"] | Measure-Object SESSION_COUNT -Sum).Sum
$activeSessions = ($results["ActiveSessions"] | Measure-Object ACTIVE_COUNT -Sum).Sum
Write-Host "Sessions: $totalSessions total, $activeSessions active" -ForegroundColor Cyan
}
# Blocking sessions
if ($results["BlockingSessions"] -and $results["BlockingSessions"].Rows.Count -gt 0) {
Write-Host "⚠ WARNING: $($results["BlockingSessions"].Rows.Count) blocking sessions detected!" -ForegroundColor Red
} else {
Write-Host "✓ No blocking sessions detected" -ForegroundColor Green
}
# Long running queries
if ($results["LongRunningQueries"] -and $results["LongRunningQueries"].Rows.Count -gt 0) {
$maxRuntime = ($results["LongRunningQueries"] | Measure-Object SECONDS_SINCE_LAST_CALL -Maximum).Maximum
Write-Host "⚠ WARNING: $($results["LongRunningQueries"].Rows.Count) long-running queries (max: $([math]::Round($maxRuntime/60,1)) minutes)" -ForegroundColor Yellow
} else {
Write-Host "✓ No long-running queries detected" -ForegroundColor Green
}
return $results
}
catch {
Write-Error "Failed to gather Oracle performance data: $($_.Exception.Message)"
return $null
}
}
function New-OracleLIMSHealthScript {
param(
[string]$OutputPath = "C:\temp\oracle_lims_health.sql",
[string]$SchemaName = "LIMS"
)
$sqlScript = @"
-- Oracle LIMS Health Check Script
-- Generated: $(Get-Date)
-- Schema: $SchemaName
SET PAGESIZE 100
SET LINESIZE 200
SET FEEDBACK ON
PROMPT ================================================
PROMPT ORACLE LIMS HEALTH CHECK
PROMPT Generated: $(Get-Date)
PROMPT ================================================
PROMPT
PROMPT 1. DATABASE INFORMATION
SELECT
NAME AS DATABASE_NAME,
DBID,
TO_CHAR(CREATED, 'DD-MON-YYYY HH24:MI:SS') AS CREATED,
LOG_MODE,
OPEN_MODE,
DATABASE_ROLE,
PLATFORM_NAME,
TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') AS CURRENT_TIME
FROM V`$DATABASE;
PROMPT
PROMPT 2. INSTANCE INFORMATION
SELECT
INSTANCE_NAME,
HOST_NAME,
VERSION,
STATUS,
STARTUP_TIME,
DATABASE_STATUS
FROM V`$INSTANCE;
PROMPT
PROMPT 3. TABLESPACE USAGE
SELECT
TS.TABLESPACE_NAME,
ROUND(TS.TOTAL_SIZE_MB, 2) AS TOTAL_MB,
ROUND(TS.TOTAL_SIZE_MB - NVL(FS.FREE_SIZE_MB, 0), 2) AS USED_MB,
ROUND(NVL(FS.FREE_SIZE_MB, 0), 2) AS FREE_MB,
ROUND(((TS.TOTAL_SIZE_MB - NVL(FS.FREE_SIZE_MB, 0)) / TS.TOTAL_SIZE_MB) * 100, 2) AS PCT_USED
FROM
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS TOTAL_SIZE_MB
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) TS
LEFT JOIN
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS FREE_SIZE_MB
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) FS
ON TS.TABLESPACE_NAME = FS.TABLESPACE_NAME
ORDER BY PCT_USED DESC;
PROMPT
PROMPT 4. ACTIVE SESSIONS BY PROGRAM
SELECT
PROGRAM,
USERNAME,
COUNT(*) AS SESSION_COUNT,
COUNT(CASE WHEN STATUS = 'ACTIVE' THEN 1 END) AS ACTIVE_COUNT
FROM V`$SESSION
WHERE USERNAME IS NOT NULL
GROUP BY PROGRAM, USERNAME
HAVING COUNT(*) > 2
ORDER BY SESSION_COUNT DESC;
PROMPT
PROMPT 5. BLOCKING SESSIONS (CRITICAL IF ANY)
SELECT COUNT(*) AS BLOCKING_COUNT FROM DBA_BLOCKERS;
SELECT
w.SESSION_ID AS WAITING_SID,
h.SESSION_ID AS HOLDING_SID,
w.ORACLE_USERNAME AS WAITING_USER,
h.ORACLE_USERNAME AS HOLDING_USER,
o.OBJECT_NAME,
w.MODE_REQUESTED,
h.MODE_HELD
FROM DBA_WAITERS w
JOIN DBA_BLOCKERS h ON w.HOLDING_SESSION = h.HOLDING_SESSION
LEFT JOIN DBA_OBJECTS o ON w.ROW_WAIT_OBJ# = o.OBJECT_ID
WHERE ROWNUM <= 10;
PROMPT
PROMPT 6. LONG RUNNING SESSIONS (>5 MINUTES ACTIVE)
SELECT
SID,
USERNAME,
MACHINE,
PROGRAM,
STATUS,
ROUND(LAST_CALL_ET/60, 2) AS MINUTES_SINCE_LAST_CALL,
LOGON_TIME
FROM V`$SESSION
WHERE USERNAME IS NOT NULL
AND STATUS = 'ACTIVE'
AND LAST_CALL_ET > 300
ORDER BY LAST_CALL_ET DESC;
PROMPT
PROMPT 7. TOP WAIT EVENTS
SELECT
EVENT,
TOTAL_WAITS,
ROUND(TIME_WAITED_MICRO/1000000, 2) AS TIME_WAITED_SEC,
ROUND(AVERAGE_WAIT, 2) AS AVG_WAIT_MS
FROM V`$SYSTEM_EVENT
WHERE EVENT NOT LIKE 'SQL*Net%'
AND EVENT NOT LIKE '%idle%'
AND TIME_WAITED_MICRO > 0
AND ROWNUM <= 10
ORDER BY TIME_WAITED_MICRO DESC;
PROMPT
PROMPT 8. DATABASE ALERTS (V`$ALERT_HISTORY)
SELECT
TO_CHAR(ORIGINATING_TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') AS ALERT_TIME,
MESSAGE_TYPE,
MESSAGE_LEVEL,
HOST_ADDRESS,
SUBSTR(MESSAGE, 1, 100) AS MESSAGE_PREVIEW
FROM V`$ALERT_HISTORY
WHERE ORIGINATING_TIMESTAMP > SYSDATE - 1
ORDER BY ORIGINATING_TIMESTAMP DESC;
PROMPT
PROMPT 9. LIMS SCHEMA SPECIFIC CHECKS
-- Check for tables with no recent activity
SELECT
TABLE_NAME,
NUM_ROWS,
LAST_ANALYZED,
TRUNC(SYSDATE - LAST_ANALYZED) AS DAYS_SINCE_ANALYZED
FROM DBA_TABLES
WHERE OWNER = UPPER('$SchemaName')
AND (TABLE_NAME LIKE '%SAMPLE%' OR TABLE_NAME LIKE '%TEST%' OR TABLE_NAME LIKE '%RESULT%')
AND LAST_ANALYZED IS NOT NULL
ORDER BY DAYS_SINCE_ANALYZED DESC;
PROMPT
PROMPT 10. INVALID OBJECTS IN LIMS SCHEMA
SELECT
OBJECT_TYPE,
OBJECT_NAME,
STATUS,
LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE OWNER = UPPER('$SchemaName')
AND STATUS != 'VALID'
ORDER BY OBJECT_TYPE, OBJECT_NAME;
PROMPT
PROMPT Health check complete. Review any issues above.
PROMPT Check completed at:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') AS COMPLETION_TIME FROM DUAL;
"@
$sqlScript | Out-File -FilePath $OutputPath -Encoding UTF8
Write-Host "Oracle LIMS health check script created: $OutputPath" -ForegroundColor Green
Write-Host "Run this in SQL*Plus: @$OutputPath" -ForegroundColor Cyan
Write-Host "Or from command line: sqlplus username/password@database @$OutputPath" -ForegroundColor Cyan
}
#endregion
# Usage examples and quick commands
Write-Host @"
ORACLE LIMS MONITORING COMMANDS:
=================================
1. Test Oracle connection:
Test-OracleForLIMS -ServerName "ORACLESERVER01" -ServiceName "LIMSPROD" -Username "lims_user" -Password "your_password"
2. Get comprehensive performance data:
`$perf = Get-OracleLIMSPerformance -ServerName "ORACLESERVER01" -ServiceName "LIMSPROD" -Username "lims_user" -Password "your_password"
`$perf["BlockingSessions"] # View blocking sessions
`$perf["LongRunningQueries"] # View slow queries
`$perf["TablespaceUsage"] # View tablespace usage
3. Generate health check script:
New-OracleLIMSHealthScript -SchemaName "LIMS_PROD"
4. Connection with TNS alias:
Test-OracleForLIMS -ServerName "ORACLESERVER01" -ServiceName "LIMSPROD.domain.com" -Username "lims_user" -Password "your_password"
5. Quick tablespace check:
`$perf = Get-OracleLIMSPerformance -ServerName "SERVER" -ServiceName "SID" -Username "user" -Password "pass"
`$perf["TablespaceUsage"] | Where-Object {`$_.PERCENT_USED -gt 80}
"@ -ForegroundColor Green
Oracle.ManagedDataAccess.Client instead of System.Data.SqlClientV$ views instead of SQL Server DMVsDUAL, ROWNUM, SYSDATE)DBA_WAITERS/DBA_BLOCKERS instead of sys.dm_exec_requests1. Install Oracle Client or Oracle Data Access Components (ODAC)
2. Load .NET Provider in PowerShell session
3. Configure TNS (optional) for easier connection management
4. Set Environment Variables for Oracle Home if needed
5. Test connectivity before running performance monitoring
This Oracle version provides equivalent functionality to the SQL Server script while using Oracle-specific commands, views, and best practices for LIMS monitoring.