오라클 Alert Log에 사용자 정의 로그 남기기

-- Alert Log에 기록 남기기

SYS.DBMS_SYSTEM.KSDWRT(2,l_message);

1-write to trace file (Trace 파일에만 Message 기록)

2-write to alert log (Alert Log 파일에만 Message 기록)

3-write to both (Trace / Alert Log 파일 모두에 Message 기록)

 

-- 활용예(로그인 실패 로그)

create or replace trigger logon_fail_write_alertlog

AFTER SERVERERROR on database

declare

l_message varchar2(2000); -- 메시지 내용 저장

BEGIN

-- ORA-1017 : invalid username/password; logon denied

IF(IS_SERVERERROR(1017)) THEN

SELECT 'ORA-01017 Failed login attempt to the "'|| sys_context('USERENV','AUTHENTICATED_IDENTITY') ||'" schema'||' using ' ||

sys_context('USERENV','AUTHENTICATION_TYPE') ||' Authentication'||' at '||to_char(logon_time,'YYYY/MM/DD HH24:MI:SS') ||

' from ' ||osuser||'@'||machine||' ['||nvl(sys_context('USERENV','IP_ADDRESS'),'Unknown IP')||']' ||' via the "' ||program||'" program.'

INTO l_message

FROM SYS.V_$SESSION

WHERE SID = TO_NUMBER(SUBSTR(DBMS_SESSION.UNIQUE_SESSION_ID,1,4),'XXXX') AND SERIAL# = TO_NUMBER(SUBSTR(DBMS_SESSION.UNIQUE_SESSION_ID,5,4),'XXXX');

--write to alert log

SYS.DBMS_SYSTEM.KSDWRT(2,l_message);

-- ORA-28000 : the account is locked

elsif (IS_SERVERERROR(28000)) THEN

select 'ORA-28000 the account is locked "'|| sys_context('USERENV','AUTHENTICATED_IDENTITY') ||'" Schema '||' using ' ||

sys_context('USERENV','AUTHENTICATION_TYPE') ||' Authentication'||' at '||to_char(logon_time,'YYYY/MM/DD HH24:MI:SS') ||

' from ' ||osuser||'@'||machine||' ['||nvl(sys_context('USERENV','IP_ADDRESS'),'Unknown IP')||']' ||' via the "' ||program||'" program.'

INTO l_message

FROM SYS.V_$SESSION

WHERE SID = TO_NUMBER(SUBSTR(DBMS_SESSION.UNIQUE_SESSION_ID,1,4),'XXXX') AND SERIAL# = TO_NUMBER(SUBSTR(DBMS_SESSION.UNIQUE_SESSION_ID,5,4),'XXXX');

--write to alert log

SYS.DBMS_SYSTEM.KSDWRT(2,l_message);

END IF;

END;

/