DASH

 Option Explicit

Sub APPS_DBA_Dashboard_Quick_Refresh_All()

    

    ' ------------------------------------------------------------

    ' Consolidated macro for APPS DBA Team Lead dashboard

    ' Refresh + Color + Age calculation + Weekly summary

    ' Mohammed - Feb 2025 / 2026 version

    ' ------------------------------------------------------------

    

    Dim ws As Worksheet

    Dim rng As Range, cell As Range

    Dim lastRow As Long, i As Long, outRow As Long

    Dim dueDate As Date

    

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    Application.EnableEvents = False

    

    On Error GoTo Finish

    

    ' 1. Refresh all data connections, pivots, formulas

    ThisWorkbook.RefreshAll

    ThisWorkbook.Worksheets("Dashboard").Calculate

    

    ' 2. Color status cells in main tracking sheets

    Dim statusSheets As Variant

    statusSheets = Array("ServiceNow", "RFC", "Release Tasks", "OEM", "Escalations", "Performance", "FlexDeploy")

    

    Dim s As Variant

    For Each s In statusSheets

        On Error Resume Next

        Set ws = ThisWorkbook.Worksheets(s)

        On Error GoTo Finish

        

        If Not ws Is Nothing Then

            ' Try to find status column dynamically (looks for words like Status, State, Result)

            Dim statusCol As Long

            statusCol = 0

            For i = 1 To 26

                If InStr(1, LCase(ws.Cells(1, i).Value), "status") > 0 Or _

                   InStr(1, LCase(ws.Cells(1, i).Value), "state") > 0 Or _

                   InStr(1, LCase(ws.Cells(1, i).Value), "result") > 0 Then

                    statusCol = i

                    Exit For

                End If

            Next i

            

            If statusCol > 0 Then

                lastRow = ws.Cells(ws.Rows.Count, statusCol).End(xlUp).Row

                If lastRow >= 2 Then

                    Set rng = ws.Range(ws.Cells(2, statusCol), ws.Cells(lastRow, statusCol))

                    

                    For Each cell In rng

                        Select Case LCase(Trim(cell.Value))

                            Case "open", "in progress", "wip", "not started", "pending"

                                cell.Interior.Color = RGB(255, 192, 0)      ' Orange

                            Case "critical", "high", "urgent", "blocked"

                                cell.Interior.Color = RGB(192, 0, 0)        ' Red

                            Case "closed", "completed", "done", "success", "passed", "resolved"

                                cell.Interior.Color = RGB(0, 176, 80)       ' Green

                            Case "failed", "error", "rolled back", "rejected"

                                cell.Interior.Color = RGB(255, 0, 0)        ' Bright red

                            Case Else

                                cell.Interior.ColorIndex = xlNone

                        End Select

                    Next cell

                End If

            End If

        End If

    Next s

    

    ' 3. Calculate Age (days open) - ServiceNow & Escalations

    Dim ageSheets As Variant

    ageSheets = Array("ServiceNow", "Escalations")

    

    For Each s In ageSheets

        On Error Resume Next

        Set ws = ThisWorkbook.Worksheets(s)

        On Error GoTo Finish

        

        If Not ws Is Nothing Then

            Dim dateCol As Long, ageCol As Long

            dateCol = 0: ageCol = 0

            

            For i = 1 To 30

                Dim hdr As String: hdr = LCase(ws.Cells(1, i).Value)

                If InStr(hdr, "open") > 0 Or InStr(hdr, "created") > 0 Or InStr(hdr, "raised") > 0 Then

                    dateCol = i

                End If

                If InStr(hdr, "age") > 0 Or InStr(hdr, "days") > 0 Then

                    ageCol = i

                End If

            Next i

            

            If dateCol > 0 And ageCol = 0 Then

                ' If no Age column → create one at the end

                ageCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1

                ws.Cells(1, ageCol).Value = "Age (days)"

                ws.Cells(1, ageCol).Font.Bold = True

            End If

            

            If dateCol > 0 And ageCol > 0 Then

                lastRow = ws.Cells(ws.Rows.Count, dateCol).End(xlUp).Row

                For i = 2 To lastRow

                    If IsDate(ws.Cells(i, dateCol).Value) Then

                        ws.Cells(i, ageCol).Value = DateDiff("d", ws.Cells(i, dateCol).Value, Date)

                        

                        Select Case ws.Cells(i, ageCol).Value

                            Case Is >= 30:  ws.Cells(i, ageCol).Interior.Color = RGB(192, 0, 0)

                            Case Is >= 14:  ws.Cells(i, ageCol).Interior.Color = RGB(255, 192, 0)

                            Case Else:      ws.Cells(i, ageCol).Interior.ColorIndex = xlNone

                        End Select

                    End If

                Next i

            End If

        End If

    Next s

    

    ' 4. Quick weekly urgent summary → Plan sheet

    On Error Resume Next

    Set ws = ThisWorkbook.Worksheets("ToDo")

    Dim wsPlan As Worksheet

    Set wsPlan = ThisWorkbook.Worksheets("Plan")

    On Error GoTo Finish

    

    If Not ws Is Nothing And Not wsPlan Is Nothing Then

        wsPlan.Range("A8:F" & wsPlan.Rows.Count).ClearContents

        

        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

        outRow = 10

        

        wsPlan.Cells(8, 1).Value = "Weekly Urgent Items (" & Format(Date, "dd-mmm-yyyy") & ")"

        wsPlan.Cells(8, 1).Font.Bold = True

        wsPlan.Cells(9, 1).Value = "Task":         wsPlan.Cells(9, 2).Value = "Prio": _

        wsPlan.Cells(9, 3).Value = "Owner":        wsPlan.Cells(9, 4).Value = "Due": _

        wsPlan.Cells(9, 5).Value = "Status":       wsPlan.Cells(9, 6).Value = "Age"

        

        For i = 2 To lastRow

            If ws.Cells(i, "D").Value <> "" Then   ' assuming Due Date in column D

                dueDate = ws.Cells(i, "D").Value

                If dueDate >= Date And dueDate <= Date + 7 Then

                    wsPlan.Cells(outRow, 1).Value = ws.Cells(i, "A").Value

                    wsPlan.Cells(outRow, 2).Value = ws.Cells(i, "B").Value

                    wsPlan.Cells(outRow, 3).Value = ws.Cells(i, "C").Value

                    wsPlan.Cells(outRow, 4).Value = dueDate

                    wsPlan.Cells(outRow, 5).Value = ws.Cells(i, "E").Value

                    

                    If wsPlan.Cells(outRow, 4).Value <= Date Then

                        wsPlan.Range("A" & outRow & ":F" & outRow).Interior.Color = RGB(255, 230, 230) ' light red = overdue

                    ElseIf wsPlan.Cells(outRow, 4).Value <= Date + 3 Then

                        wsPlan.Range("A" & outRow & ":F" & outRow).Interior.Color = RGB(255, 242, 204) ' light orange

                    End If

                    

                    outRow = outRow + 1

                End If

            End If

        Next i

        

        wsPlan.Range("A8:F" & outRow - 1).Borders.LineStyle = xlContinuous

        wsPlan.Columns("A:F").AutoFit

    End If

    

    ' 5. Final cleanup

    Dim finalSheets As Variant

    finalSheets = Array("Dashboard", "ServiceNow", "RFC", "Release Tasks", "Plan")

    For Each s In finalSheets

        On Error Resume Next

        ThisWorkbook.Worksheets(s).Columns("A:Z").AutoFit

        On Error GoTo Finish

    Next s

Finish:

    Application.ScreenUpdating = True

    Application.Calculation = xlCalculationAutomatic

    Application.EnableEvents = True

    

    If Err.Number <> 0 Then

        MsgBox "Finished with warning(s)." & vbNewLine & Err.Description, vbExclamation, "APPS DBA Refresh"

    Else

        MsgBox "Dashboard refreshed & colored." & vbNewLine & _

               "• Status colors updated" & vbNewLine & _

               "• Ages calculated" & vbNewLine & _

               "• Weekly urgent summary created", vbInformation, "APPS DBA Dashboard"

    End If

    

End Sub

SSO Validation

#!/bin/bash
# ==============================================================================
# Script Name : sso_validation_post_clone.sh
# Description : Validates SSO Registration after EBS Environment Clone
# Author      : Minh
# Date        : Feb 05, 2026
# ==============================================================================

# 1. Environment Initialization
cd $HOME
if [ -f "./EBSapps.env" ]; then
    . ./EBSapps.env run
    echo "[$(date)] Environment sourced successfully."
else
    echo "[ERROR] EBSapps.env not found in $HOME. Exiting."
    exit 1
fi

# 2. Define Variables
LOG_DIR="$INST_TOP/logs/sso_post_clone"
mkdir -p $LOG_DIR
CHECK_LOG="$LOG_DIR/SSORegCheck_$(date +%Y%m%d_%H%M%S).log"

echo "----------------------------------------------------------"
echo "Starting SSO Registration Check..."
echo "Log File: $CHECK_LOG"
echo "----------------------------------------------------------"

# 3. Execute SSORegCheck
# Using -silent=yes to avoid interactive prompts during automation
perl $FND_TOP/bin/txkrun.pl \
-script=SSORegCheck \
-outdir=$LOG_DIR \
-silent=yes > $CHECK_LOG 2>&1

# 4. Success/Failure Logic
if [ $? -eq 0 ]; then
    echo "[SUCCESS] SSO Configuration is valid for $TWO_TASK."
    # Optional: Integration with your tracking tools
    # Example: Send success status to Jira/Datadog
else
    echo "[CRITICAL] SSO Validation Failed for $TWO_TASK!"
    echo "Please review the log: $CHECK_LOG"
    # You can trigger a PagerDuty alert here if this is a QCT/CORP env
    exit 1
fi

echo "----------------------------------------------------------"
echo "Validation Complete."

Note: Ensure you run this script as the applmgr user after the post-clone configuration is complete.

analysse

WITH stats_summary AS (

    SELECT 

        owner,

        COUNT(*) AS analyzed_tables,                  -- tables with any stats

        COUNT(CASE WHEN last_analyzed > SYSDATE - 2 

                   THEN 1 END) AS recent_tables,      -- tables analyzed in last 2 days

        MAX(last_analyzed) AS last_analyzed_max

    FROM all_tab_statistics

    WHERE last_analyzed IS NOT NULL

      AND owner NOT LIKE 'SYS%'

      AND owner NOT LIKE 'APEX%'

      AND owner NOT IN ('SYSTEM','OUTLN','DBSNMP','CTXSYS','MDSYS','XDB')  -- typical exclusions

    GROUP BY owner

),

total_tables_per_schema AS (

    SELECT 

        owner,

        COUNT(*) AS total_tables

    FROM all_tables

    WHERE owner NOT LIKE 'SYS%'

      AND owner NOT LIKE 'APEX%'

      AND owner NOT IN ('SYSTEM','OUTLN','DBSNMP','CTXSYS','MDSYS','XDB')

    GROUP BY owner

)

SELECT 

    s.owner                          AS schema_name,

    t.total_tables,

    s.analyzed_tables,

    s.recent_tables,

    ROUND(s.analyzed_tables * 100 / NULLIF(t.total_tables, 0), 1) AS pct_analyzed_overall,

    ROUND(s.recent_tables  * 100 / NULLIF(t.total_tables, 0), 1) AS pct_analyzed_recent,

    TO_CHAR(s.last_analyzed_max, 'DD-MON-YYYY HH24:MI:SS') AS last_analyzed_max

FROM stats_summary s

JOIN total_tables_per_schema t ON s.owner = t.owner

WHERE s.last_analyzed_max > SYSDATE - 2          -- only schemas touched in last 2 days

ORDER BY s.last_analyzed_max DESC;

prmp

 You are an expert Oracle database performance analyst with deep knowledge of Automatic Workload Repository (AWR) reports. I will provide you with the content of an AWR report (extracted from a PDF or text file). Your task is to thoroughly analyze the report, identify any performance issues or bottlenecks, and provide step-by-step debugging recommendations to resolve them.

First, parse the key sections of the AWR report, including but not limited to:

• Report Header (DB Name, Instance, Snapshot Interval, Elapsed Time, DB Time)
• Load Profile (e.g., Parses, Executes, Transactions per second)
• Instance Efficiency Percentages (e.g., Buffer Hit %, Library Hit %, Soft Parse %)
• Top Timed Foreground Events (e.g., CPU time, db file sequential read, log file sync)
• Wait Class Breakdown
• SQL Statistics (Top SQL by Elapsed Time, CPU Time, Buffer Gets, Executions)
• Instance Activity Stats (e.g., user commits, redo size)
• Tablespace I/O Stats
• Advisory Statistics (e.g., Buffer Cache, PGA, SGA advice)
• Any RAC-specific sections if applicable (e.g., Global Cache stats)

For each relevant section:

1. Summarize the key metrics and highlight any abnormalities (e.g., high wait times >10% of DB time, low hit ratios <90%, excessive parses).
2. Identify potential issues, such as:
   • CPU bottlenecks (high CPU usage without corresponding waits).
   • I/O issues (slow reads/writes, high physical I/O).
   • Locking/contention problems (enq: waits, latch misses).
   • SQL inefficiencies (poorly optimized queries, missing indexes).
   • Memory shortages (frequent swapping, undersized SGA/PGA).
   • Network or log-related delays.
   • Overall workload spikes during the snapshot period.
3. Provide root cause analysis based on correlations across sections (e.g., link high waits to specific SQL IDs).
4. Suggest actionable debugging steps and fixes, prioritized by impact:
   • Query tuning (e.g., add indexes, rewrite SQL, use hints).
   • Configuration changes (e.g., increase buffer cache, adjust parameters like cursor_sharing).
   • Monitoring tools (e.g., run ADDM, ASH reports, or trace specific sessions).
   • Hardware/resource upgrades if indicated.
   • Best practices for prevention.

Output your analysis in a structured format:

• Summary: High-level overview of the report's health (e.g., good/fair/poor performance).
• Key Issues: Bullet list of top 5-10 problems with severity (low/medium/high).
• Detailed Analysis: Section-by-section breakdown.
• Recommendations: Numbered list of steps to debug and resolve, with expected outcomes.
• Follow-up Questions: Any clarifying questions for more context (e.g., DB version, workload type).

Be objective, data-driven, and use evidence from the report. If the report is incomplete or unclear, note that and request more details.

USERS

SELECT sid, serial#, username, osuser, program, sql_id, event, wait_time, seconds_in_wait

FROM v$session

WHERE username IN ('APPS', 'APPLSYSPUB')  -- or the user logging in

  AND status = 'ACTIVE'

  AND program LIKE '%JDBC%'  -- AccessGate uses JDBC

ORDER BY seconds_in_wait DESC;