이슈를 공유하자

Table 단위 DML 현황 조회

SELECT M.TABLE_OWNER,
   M.TABLE_NAME,T.NUM_ROWS,ROUND(NUM_ROWS/10,0) "NUM_ROWS/10", 
   INSERTS+UPDATES+DELETES "tot_changed",
   INSERTS, UPDATES, DELETES , 
   m.TIMESTAMP,
   LAST_ANALYZED,ROUND(sysdate - m.TIMESTAMP,0) GAP
FROM DBA_TAB_MODIFICATIONS M,DBA_TABLES T
WHERE TABLE_OWNER NOT IN ('SYS','SYSTEM')
AND T.TABLE_NAME = M.TABLE_NAME
AND T.OWNER = M.TABLE_OWNER
AND M.TABLE_OWNER = 'SCOTT'
AND TO_CHAR(TIMESTAMP,'YYYY') = '2023'
ORDER BY LAST_ANALYZED - TIMESTAMP

% number of rows DML since last analyze time (마지막 통계정보 생성 이 후 DML Count(10%변경분))

개별 Table로 조회 시

select o.object_name,m.* 
from SYS.MON_MODS_ALL$ m, dba_objects o
where m.obj# = o.object_id
and object_name = 'TB_TEST01'

shared_pool에 모았다가 3시간주기로 Dictionary Veiw에 반영, 즉시 반영하려면
exec dbms_stats.flush_database_monitoring_info 실행

https://support.oracle.com/knowledge/Oracle%20Database%20Products/2668605_1.html

• In customer environment, SQLNET.ALLOWED_LOGON_VERSION was set to 8 in Oracle Home of DB server.

sqlnet.ora:
SQLNET.ALLOWED_LOGON_VERSION=8

• DB user password version was "10G 11G 12C".
• After changed password By Alter User Identified by <Password> command, the password version of target db user was changed to "11G 12C".
• This problem can be reproduce as following:

/<ORACLE_HOME>/network/admin/
sqlnet.ora:
SQLNET.ALLOWED_LOGON_VERSION=8

/<PATH1>/
sqlnet.ora:
SQLNET.ALLOWED_LOGON_VERSION=12

export TNS_ADMIN=<PATH1>

$ sqlplus <DBA USER>/<PASSWORD>

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 21 03:37:23 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Apr 21 2020 03:35:27 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select username,password_versions from dba_users where username='<USER_NAME>';

USERNAME   PASSWORD_VERSIONS
---------- -----------------
<USER_NAME>       10G 11G 12C

SQL> alter user <USER_NAME> identified by <PASSWORD>;

User altered.

SQL> select username,password_versions from dba_users where username='<USER_NAME>';

USERNAME   PASSWORD_VERSIONS
---------- -----------------
<USER_NAME>       11G 12C

SQL> quit

Which sqlnet.ora or password version is fetched can be found by strace as following:

strace -ftttT -o /tmp/strace1.log sqlplus <USER_NAME>/<PASSWORD>
The abstract of strace1.log:
7005  1587440243.885264 access("/<PATH>/network/admin/sqlnet.ora", F_OK) = 0 <0.000008>
7005  1587440243.885296 open("/<PATH>/network/admin/sqlnet.ora", O_RDONLY) = 8 <0.000007>
7005  1587440243.885320 fcntl(8, F_SETFD, FD_CLOEXEC) = 0 <0.000004>
7005  1587440243.885338 fstat(8, {st_mode=S_IFREG|0644, st_size=32, ...}) = 0 <0.000004>
7005  1587440243.885361 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fb08d2c9000 <0.000005>
7005  1587440243.885377 read(8, "SQLNET.ALLOWED_LOGON_VERSION=12\n", 4096) = 32 <0.000006> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

● sqlplus 툴로 DB 접속 할 때 특수문자가 들어가 있는 패스워드로 접속이 되지 않을 때 

]$ sqlplus etawas/\"xxx@#\"

※ 역슬러시,더블쿼테이션(\")를 앞과 뒤에 삽입

• ASM 파일을 Local filesystem으로 복사하기 (archive log file backup)

for j in `cat dlist`
do
for i in $(asmcmd ls +RECO/TESTDB/ARCHIVELOG/$j)
do
asmcmd cp +RECO/TESTDB/ARCHIVELOG/$j/$i /oradump/arch_bak/$j
done
done

※ dlist 에는 복사할 파일 목록이 들어 있음.

• ASM 파일을 삭제하기 (archive log file backup)

for j in `cat dlist`
do
for i in $(asmcmd ls +RECO/TESTDB/ARCHIVELOG/$j)
do
asmcmd rm +RECO/TESTDB/ARCHIVELOG/$j/$i
done
done

# 시퀀스 네임을 받아 초기 값으로 설정하는 프로시저

CREATE OR REPLACE PROCEDURE RESET_SEQ(P_SEQ IN VARCHAR2)
IS 
L_VAL NUMBER;

BEGIN
    EXECUTE IMMEDIATE
    'SELECT '||P_SEQ||'.NEXTVAL FROM DUAL' INTO L_VAL;
    EXECUTE IMMEDIATE
    'ALTER SEQUENCE '||P_SEQ||' INCREMENT BY -'||L_VAL||' MINVALUE 0';
    
    EXECUTE IMMEDIATE
    'SELECT '||P_SEQ||'.NEXTVAL FROM DUAL' INTO L_VAL;
    EXECUTE IMMEDIATE
    'ALTER SEQUENCE '||P_SEQ||' INCREMENT BY 1 MINVALUE 0;
 END;
 /

※ 초기화 시 반드시 "MINVALUE 0" 로 설정해야 함.

-- 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;

/