-- alert.log 위치 확인
SELECT VALUE
FROM V$DIAG_INFO
WHERE NAME = 'Diag Trace';

-- 현재 활성 백그라운드 프로세스 목록 확인
SELECT PNAME, PID, SPID, BACKGROUND, LATCHWAIT, LATCHSPIN
FROM V$PROCESS
WHERE BACKGROUND = '1'
ORDER BY PNAME;

-- 인스턴스 상태 확인
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS, ACTIVE_STATE
FROM V$INSTANCE;

-- 인스턴스 재기동 (SYSDBA 권한 필요)
-- SQL*Plus 접속 후:
SHUTDOWN ABORT;
STARTUP;

-- 재기동 후 인스턴스 복구 여부 확인
SELECT STATUS FROM V$INSTANCE;

-- 최근 에러 로그 확인 (AWR 또는 V$DIAG_ALERT_EXT 활용)
SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%ORA-00072%'
   OR MESSAGE_TEXT LIKE '%process died%'
ORDER BY ORIGINATING_TIMESTAMP DESC
FETCH FIRST 20 ROWS ONLY;

-- 현재 세션 상태 중 KILLED 상태인 세션 확인
SELECT SID, SERIAL#, USERNAME, STATUS, OSUSER, MACHINE, PROGRAM, LAST_CALL_ET
FROM V$SESSION
WHERE STATUS = 'KILLED'
ORDER BY LAST_CALL_ET DESC;

-- KILLED 상태가 오래 지속되는 세션 강제 종료
-- (PMON이 자동으로 정리하지만, 지연 시 수동 처리)
ALTER SYSTEM KILL SESSION '&SID,&SERIAL#' IMMEDIATE;

-- 운영체제 레벨 프로세스 ID 확인 후 정리
SELECT p.SPID AS OS_PID, s.SID, s.SERIAL#, s.USERNAME, s.STATUS
FROM V$PROCESS p
JOIN V$SESSION s ON p.ADDR = s.PADDR
WHERE s.STATUS = 'KILLED';

-- 위 결과의 SPID를 이용해 OS에서 확인 (DBA가 OS 레벨에서 실행)
-- Linux: kill -9 <SPID>
-- 단, Oracle 프로세스를 OS에서 직접 kill하는 것은 최후의 수단으로만 사용할 것

-- 고아 트랜잭션(Orphan Transaction) 확인
SELECT t.ADDR, t.XIDUSN, t.XIDSLOT, t.XIDSQN, t.STATUS, t.START_TIME
FROM V$TRANSACTION t
LEFT JOIN V$SESSION s ON t.SES_ADDR = s.PADDR
WHERE s.SID IS NULL;

-- RAC 전체 인스턴스 상태 확인
SELECT INST_ID, INSTANCE_NAME, STATUS, DATABASE_STATUS, ACTIVE_STATE
FROM GV$INSTANCE
ORDER BY INST_ID;

-- 각 노드의 백그라운드 프로세스 확인
SELECT INST_ID, PNAME, PID, SPID, BACKGROUND
FROM GV$PROCESS
WHERE BACKGROUND = '1'
  AND PNAME IN ('LMD0','LMS0','LCK0','LMON','DIAG')
ORDER BY INST_ID, PNAME;

-- 노드 간 통신 상태 확인 (GCS: Global Cache Service)
SELECT INST_ID, NAME, VALUE
FROM GV$SYSSTAT
WHERE NAME IN (
    'gc current blocks received',
    'gc cr blocks received',
    'gc current block receive time',
    'gc cr block receive time'
)
ORDER BY INST_ID, NAME;

-- 특정 노드 재시작 후 리소스 재조정 확인
-- (Clusterware 명령어 - OS 레벨, DBA 참고용)
-- srvctl status database -d <DB_NAME>
-- crsctl stat res -t

-- 장애 발생 전후 SCN 흐름 확인
SELECT CURRENT_SCN, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS CHK_TIME
FROM V$DATABASE;

-- 주기적으로 실행할 프로세스 상태 점검 쿼리 (모니터링 스크립트에 활용)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CHECK_TIME,
       COUNT(*) AS TOTAL_PROCESS,
       SUM(CASE WHEN BACKGROUND = '1' THEN 1 ELSE 0 END) AS BG_PROCESS,
       SUM(CASE WHEN BACKGROUND IS NULL THEN 1 ELSE 0 END) AS FG_PROCESS
FROM V$PROCESS;

-- 비정상 세션 현황 모니터링
SELECT STATUS, COUNT(*) AS CNT
FROM V$SESSION
WHERE TYPE = 'USER'
GROUP BY STATUS
ORDER BY CNT DESC;

-- Oracle Health Monitor 실행 (자동 진단)
BEGIN
    DBMS_HM.RUN_CHECK(
        check_name   => 'Process Failure Check',
        run_name     => 'MANUAL_PROCESS_CHECK_' || TO_CHAR(SYSDATE,'YYYYMMDD'),
        input_params => NULL
    );
END;
/

-- 헬스체크 결과 확인
SELECT RUN_NAME, CHECK_NAME, STATUS, START_TIME, END_TIME
FROM V$HM_RUN
ORDER BY START_TIME DESC
FETCH FIRST 10 ROWS ONLY;