Thứ Năm, 27 tháng 2, 2020

[VIP] Các câu lệnh hỗ trợ giám sát cơ sở dữ liệu trong Oracle Database

/****************************************** ACTIVE, LOCK *****************************************/
SELECT distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,
s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE  S.STATUS = 'ACTIVE' AND  S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER' 
--and s.username  NOT in ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE')
--AND username in 'CENTER2'
--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'
--and lower(ss.sql_text) like lower('%parallel%')
--and s.sid=4588 
--and s.machine like '%BINHTV%'
--and s.sql_id ='ccwg0nqr1zbu7'
ORDER BY username,sql_id;

select /* Active theo user*/ USERNAME,count(*) from gv$session where  status='ACTIVE' group by USERNAME order by count(*) desc;

select /* count , status*/ username,status, count(*) from gv$session group by username,status order by count(*) desc;

Select /*blocking_session*/ inst_id,blocking_session, sid, serial#, sql_id, wait_class, seconds_in_wait, username,STATUS,SCHEMANAME,OSUSER,MACHINE,PROGRAM,TYPE,LOGON_TIME  
From gv$session where blocking_session is not NULL and type not like 'BACKGROUND' order by inst_id;

select sql_id,sql_fulltext,loaded_versions,executions,loads,invalidations,parse_calls from gv$sql  where inst_id=4 and sql_id='cn7m7t6y5h77g';

/**************************************** SUM *****************************************/
select /* count , status*/ username,status, count(*) from gv$session group by username,status order by count(*) desc;

select /* Active theo user*/ USERNAME,count(*) from gv$session where  status='ACTIVE' group by USERNAME order by count(*) desc;

select status, count(*) from gv$session  group by status order by status;

select count(*) from gv$session ;

select USERNAME,count(*) from gv$session group by USERNAME order by count(*) desc;

select machine,count(*) from v$session group by machine order by count(*) desc;

select inst_id, count(*) from gv$session group by inst_id;

select /*Thong ke theo status*/  username,status,count(*) from gv$session where username like 'USER1%' group by username,status order by count(*) desc;

select /*Thong ke theo inst_id*/ inst_id,count(*), username from gv$session where username like 'USER1%' group by inst_id, username order by username; 

select /* user theo machine */ machine,count(*), username from gv$session where username like 'USER1%' group by machine, username order by username;

/************************************************** KILL *********************************************************/
-- Xac dinh process tu inst_id, status, username, sql_id, machine, event,
SELECT /*username*/  'kill -9 ' || SPID A ,a.INST_ID,A.SID,A.SQL_ID, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND, A.EVENT
FROM gv$session a, gv$process b  
WHERE b.ADDR = a.paddr 
AND a.inst_id=b.inst_id   
--AND B.inst_id = 4
and a.status='INACTIVE'
--and A.USERNAME LIKE 'BINHTV_OWNER'
--AND A.USERNAME not  in ('SYS','GGATE','GOLDENGATE',''ORA_RECO_070361')
--AND a.program LIKE '%rman%'
--AND sql_id in ('gbmyfjjdcyk75')
--and machine  like '%HCM%'
and a.event in  ('library cache lock','brary cache load lock','cursor: pin S wait on X','library cache pin','gc buffer busy acquire','enq: TS - contention','enq: TX - row lock contention','enq: TM - contention','db file parallel read','row cache lock','enq: DX - contention','enq: US - contention')
--and  round(to_number(sysdate-a.prev_exec_start)*1440) >30   
and type='USER' 
order by a.inst_id;

SELECT /*SID*/  'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND
FROM gv$session a, gv$process b 
WHERE b.addr = a.paddr   
AND a.inst_id=b.inst_id 
--and b.inst_id=2
AND a.sid in (
257
)
and type='USER'
order by inst_id;
  
SELECT /*call package*/  'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND
FROM gv$session a, gv$process b 
WHERE b.addr = a.paddr   
AND a.inst_id=b.inst_id 
--and b.inst_id=4
AND (b.inst_id, a.sid) in (
(select /*+ parallel(8) */  inst_id, sid from gv$access where object like '%proc_test%')
)
and type='USER'
and a.machine not like '%BINHTV%' ;

SELECT /*lock table*/  'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND
FROM gv$session a, gv$process b 
WHERE b.addr = a.paddr   
AND a.inst_id=b.inst_id 
--and b.inst_id=3
AND (b.inst_id, a.sid) in
(SELECT /*+ parallel(8)*/ s.inst_id,s.sid
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
and object_name=upper('TAB1'))
--and type='USER'
--ORDER BY username, session_id;

-- Xac dinh user dang chay cau lenh SQL nao
select  p.INST_ID, 'kill -9 '||P.SPID SPID, s.SID, s.username su, substr(sa.sql_text,1,540) SQL_TEXT 
from gv$process p,gv$session s,gv$sqlarea sa
where p.addr=s.paddr and p.INST_ID=s.INST_ID and s.username is not null and s.sql_address=sa.address(+) and s.sql_hash_value=sa.hash_value(+) 
and s.username=upper('USER1') 
and type='USER'
order by INST_ID, SID;

--alter system kill session '1066,21548,@1' immediate; /*SID, Serial#*/
--alter system kill session '1921,46696' immediate;

select *--'alter system kill session ' || '''' || sid || ',' || SERIAL# || '''' || ' immediate;'
from gv$session 
where event = 'enq: TX - row lock contention'
or event like '%lock contention%';

select 'alter system kill session ' || '''' || sid || ',' || SERIAL# || '''' || ';',TIME_REMAINING+ELAPSED_SECONDS,TIME_REMAINING,TARGET 
from v$session_longops a where TIME_REMAINING>0
order by TIME_REMAINING+ELAPSED_SECONDS;
 
/********************************************** SQL DETAIL *************************************************/
select sql_id,sql_fulltext from gv$sql where  sql_id in ('67bm8d2ah3xhk');

SELECT /* Tim cau lenh sql */ b.inst_id,b.sid, a.SQL_TEXT, b.username, b.machine, b.blocking_session, B.TYPE    
FROM gV$SQLAREA a,gV$SESSION b
WHERE a.ADDRESS = b.SQL_ADDRESS 
AND upper(SQL_TEXT) LIKE '%SHOP%'; 

select machine,username,count(*) from gv$session where sql_id='48hfqhs6n2gak' 
group by machine,username order by count(*) desc;

declare
begin 
    --SQLs with elapsed time more then 1 hour
    SELECT *
    FROM dba_hist_snapshot where end_interval_time>=to_date('03/08/2017 00:00:00','dd/mm/yyyy hh24:mi:ss')
    and end_interval_time <=to_date('04/08/2017 01:00:00','dd/mm/yyyy hh24:mi:ss')
    order by end_interval_time;
    
    SELECT min(snap_id), max(snap_id)
    FROM dba_hist_snapshot where end_interval_time>=to_date('03/08/2017 01:00:00','dd/mm/yyyy hh24:mi:ss')
    and end_interval_time <=to_date('04/08/2017 01:00:00','dd/mm/yyyy hh24:mi:ss')
    order by end_interval_time;

    SELECT sql_id,
    text,
    elapsed_time,
    CPU_TIME,
    EXECUTIONS,
    PX_SERVERS,
    DISK_READ_BYTES,
    DISK_WRITE_BYTES,
    IO_INTERCONNECT_BYTES,
    OFFLOAD_ELIGIBLE_BYTES,
    CELL_SMART_SCAN_ONLY_BYTES,
    FLASH_CACHE_READS,
    ROWS_PROCESSED
    --AVG_PX_SERVER
    FROM (SELECT x.sql_id,
    SUBSTR ( dhst.sql_text, 1, 4000) text,
    ROUND ( x.elapsed_time / 1000000,0)  elapsed_time,
    ROUND ( x.cpu_time / 1000000,0)  CPU_TIME,
    --ROUND ( x.elapsed_time / 1000000, 3) elapsed_time,
    --ROUND ( x.cpu_time / 1000000, 3) cpu_time_sec,
    x.executions_delta       EXECUTIONS,
    ROUND (X.DISK_READ_BYTES/1048576,0)        DISK_READ_BYTES,
    ROUND (X.DISK_WRITE_BYTES/1048576,0)       DISK_WRITE_BYTES,
    ROUND (X.IO_INTERCONNECT_BYTES/1048576,0)  IO_INTERCONNECT_BYTES,
    ROUND (X.OFFLOAD_ELIGIBLE_BYTES/1048576,0) OFFLOAD_ELIGIBLE_BYTES,
    X.FLASH_CACHE_READS                        FLASH_CACHE_READS,
    ROUND (X.cell_smart_scan_only_BYTES/1048576,0)  CELL_SMART_SCAN_ONLY_BYTES,
    (x.ROWS_PROCESSED) ROWS_PROCESSED,
    (X.PX_SERVERS) PX_SERVERS,
    --ROUND(X.PX_SERVERS/X.executions_delta,0) AVG_PX_SERVER,
    row_number () OVER (PARTITION BY x.sql_id ORDER BY 0) rn
    FROM dba_hist_sqltext dhst,
    (SELECT dhss.sql_id                       sql_id,
    SUM (dhss.cpu_time_delta)                 cpu_time,
    SUM (dhss.elapsed_time_delta)             elapsed_time,
    SUM (dhss.executions_delta)               executions_delta,
    SUM (dhss.PHYSICAL_READ_BYTES_DELTA)      DISK_READ_BYTES,
    SUM (dhss.PHYSICAL_WRITE_BYTES_DELTA)     DISK_WRITE_BYTES,
    SUM (dhss.IO_INTERCONNECT_BYTES_DELTA)    IO_INTERCONNECT_BYTES,
    SUM (dhss.IO_OFFLOAD_ELIG_BYTES_DELTA)    OFFLOAD_ELIGIBLE_BYTES,
    SUM (dhss.OPTIMIZED_PHYSICAL_READS_DELTA) FLASH_CACHE_READS,
    SUM (dhss.IO_OFFLOAD_RETURN_BYTES_DELTA)  cell_smart_scan_only_BYTES,
    SUM (dhss.ROWS_PROCESSED_DELTA)      ROWS_PROCESSED,
    SUM (dhss.PX_SERVERS_EXECS_DELTA) PX_SERVERS
    FROM dba_hist_sqlstat dhss
    WHERE dhss.snap_id IN
                        (SELECT distinct snap_id
                        FROM dba_hist_snapshot    
                        WHERE SNAP_ID > 90796 AND SNAP_ID<= 90820)
    --comment BELOW line if want to include current executions.
    --AND dhss.executions_delta > 0    
    and dhss.instance_number=1
    GROUP BY dhss.sql_id) x
    WHERE x.sql_id = dhst.sql_id
    AND ROUND ( x.elapsed_time / 1000000, 3) > 3600    
    )    
    WHERE rn = 1 ORDER BY ELAPSED_TIME DESC;
    
    --WAIT_CLASS AND COUNTS / NOTE " NULL VALUE IS CPU"
    select wait_class, count(*) cnt from dba_hist_active_sess_history
    WHERE SNAP_ID > 90796 AND SNAP_ID<= 90820 and instance_number=1
    group by wait_class_id, wait_class
    order by 2 desc;

    -- Top 40 Objects by Physical Read
    SELECT * FROM (
        SELECT do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']' AS OBJECTS,
        DHSS.INSTANCE_NUMBER AS INST,
        SUM(DHSS.LOGICAL_READS_DELTA) LOGICAL_READ,
        SUM(DHSS.PHYSICAL_READS_DELTA) PHY_READ,
        SUM(DHSS.PHYSICAL_WRITES_DELTA) PHY_WRIT,
        SUM(DHSS.ITL_WAITS_DELTA) ITL_WT,
        SUM(DHSS.ROW_LOCK_WAITS_DELTA) ROW_LCK_WT
        from dba_hist_seg_stat DHSS, DBA_OBJECTS DO    
        WHERE DHSS.SNAP_ID > 90797 AND DHSS.SNAP_ID<= 90820
        AND DHSS.OBJ#=DO.OBJECT_ID
        and DHSS.INSTANCE_NUMBER=1
        group by do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']',DHSS.INSTANCE_NUMBER
        order BY PHY_READ DESC
    ) WHERE ROWNUM <=40;
    
end; 
   
/*************************************** LOCK BANG, PKG ****************************************/
--Table
SELECT c.owner, c.object_name, c.object_type, b.SID,b.SQL_ID, b.serial#, b.status,b.osuser, b.machine 
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.SID = a.session_id 
AND a.object_id = c.object_id 
and lower(object_name) like lower('%tab1%');

SELECT s.inst_id,s.sid, s.serial#,s.sql_id,username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser, s.machine,
DECODE(l.block,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global') STATUS,
  DECODE(v.locked_mode,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', TO_CHAR(lmode)
  ) MODE_HELD,
  decode(l.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',l.type) lock_type
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
and object_name like upper('%TAB1')
--and username like upper('binhtv')
ORDER BY username, session_id;

-- Cac session dang truy cap vao object theo owner --> De kill
select /*+ parallel(8) */ distinct owner from gv$access where lower(object) like lower('%TAB1%');

/************************************** BACKUP ***************************************/
select command_id, start_time, end_time, status,INPUT_TYPE, input_bytes_display, output_bytes_display, time_taken_display, round(compression_ratio,2) RATIO , input_bytes_per_sec_display, output_bytes_per_sec_display
from v$rman_backup_job_details 
where trunc(end_time)>=trunc(sysdate-120)
order by end_time desc; 

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"  
FROM V$SESSION_LONGOPS  
WHERE OPNAME LIKE 'RMAN%'  
  AND OPNAME NOT LIKE '%aggregate%'  
  AND TOTALWORK != 0  
  AND SOFAR  != TOTALWORK ;

/*********************************** STORAGE ************************************/
-- Size theo DF
select  round(sum(bytes)/1024/1024/1024,2) "DB_DF_GB" from dba_data_files
order by "DB_DF_GB" desc;

-- Size theo segments
select  round(sum(bytes)/1024/1024/1024,2) "DB_SEGMENTS_GB" from dba_segments 
order by "DB_SEGMENTS_GB" desc;

--1.75TB
select  round(sum(bytes)/1024/1024/1024,2) "DB_SEGMENTS_GB" from dba_segments 
where owner='USER1'
order by "DB_SEGMENTS_GB" desc;

--Size TBS Read Only
select round(sum(bytes)/1024/1024/1024,2) "TBS_RO_SEGMENTS_GB" from dba_segments where tablespace_name in (select name from v$tablespace 
where ts# in (select ts# from v$datafile where enabled='READ ONLY'));

select * from dba_segments where tablespace_name in (select name from v$tablespace 
where ts# in (select ts# from v$datafile where enabled='READ ONLY'));

-- Tablespace su dung
SELECT  a.tablespace_name,100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Usage",   ROUND 
(a.bytes_alloc / 1024 / 1024) "Size MB",   ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Free MB",
      (ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024)) "Used MB", ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Free", ROUND (maxbytes / 1048576)  "Max MB", 
       ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/  ROUND (maxbytes / 1048576) * 100) "%Used of Max"
  FROM (  SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc,  SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
            FROM dba_data_files f
        GROUP BY tablespace_name) a,
       (  SELECT f.tablespace_name, SUM (f.bytes) bytes_free  FROM dba_free_space f  GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+) --and a.tablespace_name in ('DATA201505','DATA201506','IMPORT_TBS','INDX201505','INDX201506')
 order by "%Used of Max" desc;
 
 select round(sum(bytes)/1024/1024/1024,2) "GB" from dba_data_files;
               
/*********************************** ASM ************************************/
select * from v$asm_diskgroup;

select * from V$ASM_DISKGROUP_STAT;

select name group_number, os_mb, total_mb, free_mb, path,header_status,mount_status,mode_status,state,create_date, mount_date from v$asm_disk order by name, group_number;

select round(sum(bytes)/1024/1024/1024,2) "GB" from dba_data_files;

/*********************************** ARCHIVED LOG ***********************************/
-- Theo doi archived log sinh ra
select trunc(completion_time), round(sum(blocks*block_size)/1024/1024/1024,2) "Archived Log GB" from V$ARCHIVED_LOG
where trunc(completion_time) >= trunc(sysdate-90)
--and trunc(completion_time)>= to_date(trunc(sysdate),'dd/mm/yyyy')
and dest_id=1
group by trunc(completion_time)
order by trunc(completion_time) desc;

-- Archived log sinh ra theo gio
select to_char(next_time,'YYYY-MM-DD hh24') Hour, round(sum(size_in_byte)/1024/1024,2) as size_in_mb, count(*) log_switch from (
select thread# ,sequence#, FIRST_CHANGE#,blocks*BLOCK_SIZE as size_in_byte, next_time 
from v$archived_log where name is not null group by thread# ,sequence#, FIRST_CHANGE#,blocks*BLOCK_SIZE, next_time)
group by to_char(next_time,'YYYY-MM-DD hh24') order by 1 desc;

select
to_char(COMPLETION_TIME,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'00',1,0)),'999') "00h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'01',1,0)),'999') "01h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'02',1,0)),'999') "02h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'03',1,0)),'999') "03h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'04',1,0)),'999') "04h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'05',1,0)),'999') "05h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'06',1,0)),'999') "06h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'07',1,0)),'999') "07h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'08',1,0)),'999') "08h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'09',1,0)),'999') "09h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'10',1,0)),'999') "10h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'11',1,0)),'999') "11h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'12',1,0)),'999') "12h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'13',1,0)),'999') "13h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'14',1,0)),'999') "14h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'15',1,0)),'999') "15h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'16',1,0)),'999') "16h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'17',1,0)),'999') "17h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'18',1,0)),'999') "18h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'19',1,0)),'999') "19h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'20',1,0)),'999') "20h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'21',1,0)),'999') "21h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'22',1,0)),'999') "22h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'23',1,0)),'999') "23h",
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024,3)||' MB' "Total MB in a day",COUNT(*) "Total switch log in a day"
from v$archived_log
where to_date(COMPLETION_TIME) > sysdate-31
group by to_char(COMPLETION_TIME,'YYYY-MM-DD') 
order by day;

/************************************** Analyze, Gather *************************************/
-- Index UNUSABLE
select * from dba_indexes where status='UNUSABLE';

select owner,segment_name, round(sum(bytes)/1024/1024,2) "MB" from dba_segments where (owner,segment_name)
in (select owner, index_name from dba_indexes where status='UNUSABLE')
group by owner, segment_name;

select * from dba_ind_partitions where status='UNUSABLE'; 

-- Partition
select table_owner,table_name,partition_name,last_analyzed from dba_tab_partitions
where table_owner='OAZ12' and last_analyzed<sysdate and last_analyzed>sysdate-7 
order by last_analyzed desc;

select index_owner,index_name,partition_name,last_analyzed from dba_ind_partitions
where  index_owner='OAZ12' and last_analyzed<sysdate and last_analyzed>sysdate-7
order by last_analyzed desc;

-- Non-partition
select owner, table_name,last_analyzed  from dba_tables
where owner='OAZ12' and last_analyzed<sysdate and last_analyzed>sysdate-7   
order by last_analyzed desc;

select owner, index_name,last_analyzed  from dba_indexes
where owner='BINHTV_OWNER' and last_analyzed<sysdate and last_analyzed>sysdate-7   
order by last_analyzed desc;

/************************************* DISTRIBIUTED TRANSACTION **************************************/
---///////ORA-01591: lock held by in-doubt distributed transaction 10.1.10741505, xy ly tren sqlplus
select * from sys.pending_trans$;

select * from DBA_2PC_PENDING;

select * from DBA_2PC_NEIGHBORS;

743.30.1421878
1896.9.233248
2780.32.722288
3127.12.110519

commit force '3127.12.110519'
--rollback force '75.1.3697342'
execute dbms_transaction.purge_lost_db_entry('3127.12.110519');
commit;

SELECT   local_tran_id, state
              FROM   DBA_2PC_PENDING
              where (retry_time-fail_time)*24*60>1.5;
       
/***************************************** OBJECT INVALID *****************************************/
select 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' from dba_objects 
where object_type in ('PROCEDURE','FUNCTION','TRIGGER','PACKAGE') and status like 'INVALID'and OWNER like 'BINHTV_OWNER' 
UNION ALL
select 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;' from dba_objects
where object_type in ('PACKAGE BODY') and status like 'INVALID' and OWNER like 'BINHTV_OWNER';

/**************************************** OTHERS ****************************************/
--DB, Instance
select * from gv$instance;

select * from gv$database;

--Index
select * from dba_ind_partitions where status='UNUSABLE' and index_owner not in ('SYS','SYSTEM') order by index_owner, index_name;

select * from dba_indexes where status!='VALID' and owner not in ('SYS','SYSTEM') and partitioned!='YES' order by owner, index_name;

-- Index parallel
select * from dba_indexes where    degree>1 order by 2;

-- Table parallel
select * from dba_tables  where  degree > '1' ;

-- Check IO
SELECT host_name,
         db_name,
         instance_name,
         ROUND (SUM (last_15_mins) / 1024 / 1024) IO_MB_LAST_15_MINS,
         SYSDATE
    FROM (  SELECT inst.host_name,
                   db.name AS db_name,
                   inst.instance_name,
                   sm.metric_name,
                   ROUND (AVG (sm.VALUE), 0) last_15_mins
              FROM GV$SYSMETRIC_HISTORY sm,
                   gv$instance inst,
                   (SELECT name FROM v$database) db
             WHERE     sm.inst_id = inst.inst_id
                   AND sm.metric_name IN ('Physical Read Total Bytes Per Sec',
                                          'Physical Write Bytes Per Sec',
                                          'Redo Generated Per Sec')
                   AND sm.begin_time >= SYSDATE - 15 / (24 * 60)
          GROUP BY inst.host_name,
                   db.name,
                   inst.instance_name,
                   sm.inst_id,
                   sm.metric_name)
GROUP BY host_name, db_name, instance_name
ORDER BY 1;

Kill -9 tiến trình gây cao tải cơ sở dữ liệu trong Oracle Database

/***************************************** KILL ******************************************/
-- Xac dinh process tu inst_id, status, username, sql_id, machine, event,
SELECT /*username*/  'kill -9 ' || SPID A ,a.INST_ID,A.SID,A.SQL_ID, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND, A.EVENT
FROM gv$session a, gv$process b 
WHERE b.ADDR = a.paddr
AND a.inst_id=b.inst_id 
--AND B.inst_id = 4
--and a.status='INACTIVE'
--and A.USERNAME LIKE 'SYS'
--AND A.USERNAME not  in ('SYS','GGATE','GOLDENGATE',''ORA_RECO_070361')
--and a.username not in ('SYS','SYSTEM','SYSMAN','BINHTV','DBSNMP','GGATE','GOLDENGATE')
--AND a.program LIKE '%rman%'
--AND sql_id in ('gbmyfjjdcyk75')
--and machine  like '%machine%'
and a.event in  ('library cache lock','library cache load lock','library cache: mutex X','cursor: pin S wait on X','library cache pin','gc buffer busy acquire','enq: TS - contention','enq: TX - row lock contention','enq: TM - contention','db file parallel read','row cache lock','enq: DX - contention','enq: US - contention')
--and  round(to_number(sysdate-a.prev_exec_start)*1440) >30 
and type='USER'
order by a.inst_id;

Giám sát tải ACTIVE SESION, LOCK trong Oracle Database

/************************************************ ACTIVE, LOCK ************************************************/
SELECT /*1.ActiveSession*/ distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,
s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE  S.STATUS = 'ACTIVE' AND  S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'
and s.username  NOT in ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE')
--AND username in 'BINHTV'
--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'
--and lower(ss.sql_text) like lower('%parallel%')
--and s.sid=4588
--and s.machine like '%BINHTV%'
--and s.sql_id ='ccwg0nqr1zbu7'
ORDER BY username,sql_id;

Tổng hợp các lỗi thông dụng của WebLogic

1. Server bị warning: Chỉ là cảnh báo, ko nghiêm trọng

  • Nguyên nhân: Do người dùng có request chạy quá thời gian time out (15 phút): thường là báo cáo
  • Xử lý: Vào thư mục logs, kiểm tra file out -> do database server hoac chứcc năng đó kém -> giữ session quá lâu.

2. Server bị overload

  • Thường do số kết nối DB quá tải, kiem tra ngay database.
  • Nêu Db bình thường, cân restart server (server logic) đe giải phóng.

Tổng hợp các lỗi về Datasource WebLogic

Error Message
Cause
Solution
Exceeded simmultaneous SESSION_PER_USER
DB đặt profile giới hạn số lượng session cho user
C1: Restart lại server logic để giải phóng connection tới DB
C2: Kill session từ DB
State mục Monitoring --> Testin, các Server có state Uhelthy
Do kết nối với DB bị lỗi
Nếu 1 DS bị lỗi chuyển sang chế độ fail-over
Tra cứu chiều đến NULL
Chọn JEBC --> Datasource (test_ds1) --> Monitoring --> Testing

Connection test failed with the following exception: weblogic.common.resourcepool.ResourceDisabledException: Pool test_ds1 is Suspended, cannot allocate resources to applications..

Tổng hợp các lỗi liên quan đến Deployment WebLogic

Message lỗi
Nguyên nhân
Giải pháp
Activation hang
Lỗi managed server
Vào deploymnet --> Chọn App --> Target --> Bỏ managed server bị lỗi đi

Tổng hợp các lỗi về Machine WebLogic

Message lỗi
Nguyên nhân
Giải pháp
Trạng thái Node Manager Inactive
<Dec 16, 2012 7:41:40 PM CST> <Warning> <Security> <BEA-090476> <Invalid/unknown SSL header was received from peer Blade04 - 10.50.9.114 during SSL handshake.>
<Dec 16, 2012 7:41:40 PM> <WARNING> <Uncaught exception in server handlerjavax.net.ssl.SSLHandshakeException: [Security:090476]Invalid/unknown SSL header was received from peer Blade04 - 192.168.0.11 during SSL handshake.>
javax.net.ssl.SSLHandshakeException:

Tổng hợp các lỗi thường gặp về Admin Console WebLogic

Message lỗi
Nguyên nhân
Giải pháp
<Nov 14, 2012 10:58:56 AM ICT> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...>
Tiến trình WebLogic vẫn đang chạy
Kill -9 hết các tiến trình WebLogic, java
<Nov 19, 2012 11:37:18 PM ICT> <Error> <EmbeddedLDAP> <BEA-000000> <Error opening the Transaction Log: /u01/weblogic/Oracle/Middleware/user_projects/domains/Test_Domain/servers/AdminServer/data/ldap/ldapfiles/EmbeddedLDAP.tran (Permission denied)>
<Nov 19, 2012 11:37:18 PM ICT> <Error> <EmbeddedLDAP> <BEA-000000> <Error Instantiating 'dc=Test_Domain': null>
<Nov 19, 2012 11:37:18 PM ICT> <Critical> <EmbeddedLDAP> <BEA-171522> <An error occurred while initializing the Embedded LDAP Server. The exception thrown is java.lang.ClassCastException: com.octetstring.vde.backend.BackendRoot cannot be cast to com.octetstring.vde.backend.standard.BackendStandard. This may indicate a problem with the data files for the Embedded LDAP Server. If the problem is with the data files and it can not be corrected, backups of previous versions of the data files exist in /u01/weblogic/Oracle/Middleware/user_projects/domains/Test_Domain/servers/AdminServer/data/ldap/backup.>
<Nov 19, 2012 11:37:18 PM ICT> <Critical> <WebLogicServer> <BEA-000362> <Server failed. Reason:

Tổng hợp các lỗi thường gặp về NodeManager WebLogic

Message lỗi
Nguyên nhân
Giải pháp
Nov 14, 2012 11:09:16 AM weblogic.nodemanager.server.NMServer main
SEVERE: Fatal error in node manager server
java.net.BindException: Address already in use
Chưa kill hết process java, chú ý:

weblogic 22147     1   0 11:09:08 pts/3       0:21 /usr/jdk/instances/jdk1.6.0/bin/sparcv9/java -client -Xms32m -Xmx200m -XX:MaxPe
weblogic 22009     1   0 10:21:28 pts/3       0:42 /usr/jdk/instances/jdk1.6.0/bin/sparcv9/java -client -Xms32m -Xmx200m -XX:MaxPe

Kill process java và start lại NodeManager

Thứ Tư, 26 tháng 2, 2020

Tổng hơp các lỗi thường gặp liên quan đến Managed Server WebLogic

Message lỗi
Nguyên nhân
Giải pháp
ADMIN mode
Một trong những DS bị lỗi (account lock, DB lỗi,…)
Check log tại <managed_server>/logs/<managed_server>.log
<managed_server>.out
1.      Tạm thời: click Resume
2.      Giải quyết triệt để: Check lại kết nối đến DB của DS
Unknown
Do NodeManager bị down nên managed server không kết nối được đến, sau khi node manager bật lại vẫn còn trạng thái này
Start Managed Server lên
Treo ở trạng thái Starting
Treo kết nối giữa managed server và Node Manager
Kill node manager đi và start lại

Các loại log trong WebLogic

Hai loại log:

  • Log *.log: chi tiết trạng thái của managed server
  • Log *.out: chi tiết log, dung lượng lớn


Log
Path to log file
Ghi chú

Log managed Server

/u01/weblogic/bea/user_projects/domains/Test_domain/servers/<tên_server>/logs/
Note: File log cũ được backup ra dạng *.log<number> (file này chưa log dạng rút gọn, ~ 3MB - recommend) hoặc *.out<number> (file này chứa log dạng đầy đủ đến cả câu lệnh SELECT nên rất lớn ~ 1GB)

Chủ Nhật, 23 tháng 2, 2020

DỰNG WEBLOGIC CLUSTER TỪ A-Z - BÀI 10: Deploy domain trên server khác

1.  To user và đưng dn (bưc 1)
2.  Cài đt weblogic vào server mi (bưc 2) vào đưng dn: /u01/weblogic/Oracle/Middleware
3.  Copy thưc mc domain t admin server (server 1) ti server mi. To thư mc user_projects và domains nếu chưa có

DỰNG WEBLOGIC CLUSTER TỪ A-Z - BÀI 9: TẠO DATASOURCE

1.  Truy cp  Services > Data Sources, Click Lock & Edit:

DỰNG WEBLOGIC CLUSTER TỪ A-Z - BÀI 8: TẠO CLUSTER

1.   Truy cp Environment > Clusters.
Click Lock & Edit.
Click New

DỰNG WEBLOGIC CLUSTER TỪ A-Z - BÀI 7: TẠO MANAGED SERVER (SERVER "CON")


1.  Truy cp Environment > Servers.
Click Lock & Edit.
Click New

DỰNG WEBLOGIC CLUSTER TỪ A-Z - BÀI 6: TẠO MACHINE

1.  Truy cp Web console:

2.  Thêm mi machine:
Access Environment > Machines
Click Lock&Edit
Click New

DỰNG WEBLOGIC CLUSTER TỪ A-Z - BÀI 5: BẬT NODE MANAGER


1.  Bt Node manager:

bash-3.2$ cd /u02/weblogic/Oracle/Middleware/wlserver_10.3/server/bin
bash-3.2$ ./startNodeManager.sh
CLASSPATH=/u02/weblogic/Oracle/Middleware/patch_wls1036/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/usr/jdk/instances/jdk1.6.0/lib/tools.jar:/u02/weblogic/Oracle/Middleware/wlserver_10.3/server/lib/weblogic_sp.jar:/u02/weblogic/Oracle/Middleware/wlserver_10.3/server/lib/weblogic.jar:/u02/weblogic/Oracle/Middleware/modules/features/weblogic.server.modules_10.3.6.0.jar:/u02/weblogic/Oracle/Middleware/wlserver_10.3/server/lib/webservices.jar:/u02/weblogic/Oracle/Middleware/modules/org.apache.ant_1.7.1/lib/ant-all.jar:/u02/weblogic/Oracle/Middleware/modules/net.sf.antcontrib_1.1.0.0_1-0b2/lib/ant-contrib.jar::/u02/weblogic/Oracle/Middleware
+ [ ! -z  ]
+ [ ! -z  ]
+ export CLASSPATH
+ export PATH
+ cd /u02/weblogic/Oracle/Middleware/wlserver_10.3/common/nodemanager

DỰNG WEBLOGIC CLUSTER TỪ A-Z - BÀI 4: START DOMAIN VÀ NODE MANAGER

1.  Start domain:

bash-3.2$ cd /u02/weblogic/Oracle/Middleware/user_projects/domains/Test_Domain/bin
bash-3.2$ ./startWebLogic.sh
.
.
JAVA Memory arguments: -Xms256m -Xmx512m  -XX:MaxPermSize=256m

DỰNG WEBLOGIC CLUSTER TỪ A-Z - BÀI 3: TẠO DOMAIN

bash-3.2$ cd /u02/weblogic/Oracle/Middleware/wlserver_10.3/common/bin/
bash-3.2$ ./config.sh -mode=console

<------------------- Fusion Middleware Configuration Wizard ------------------>

Welcome:
--------

Choose between creating and extending a domain. Based on your selection,
the Configuration Wizard guides you through the steps to generate a new or
extend an existing domain.

DỰNG WEBLOGIC CLUSTER TỪ A-Z - BÀI 2: CÀI ĐẶT WEBLOGIC 11G

Chy câu lnh cài đt và chú ý theo hưng dn

bash-3.2$ cd /u02/weblogic/setup
bash-3.2$ java -d64 -jar wls1036_generic_sol10_sparc.jar -mode=console
Extracting 0%....................................................................................................100%


<-------------------- Oracle Installer - WebLogic 10.3.6.0 ------------------->

DỰNG WEBLOGIC CLUSTER TỪ A-Z - BÀI 1: TẠO USER, THƯ MỤC VÀ CHUẨN BỊ BỘ CÀI

1.  To user:
bash-3.2#  useradd -d /u02/weblogic -g other -c "user weblogic" weblogic
bash-3.2# passwd weblogic

Quy tắc đặt tên khi xây dựng hệ thống WebLogic

1. Thư mục cài đặt theo quy tắc:
Trên các máy weblogic 8.205, 8.207
/u01/weblogic: chứa source của ứng dụng

/u01/setup: chứa bộ cài

/u01/backup/weblogic/app1 (app1, app2, app3,…)
/u01/backup/app/

/u01/oracle/app/fmw/10.3.2/user_projects/domains/: chứa các domain

JAVA_HOME=/usr/java1.6/1.6/jdk1.6.0_17

Thiết kế mô hình cho hệ thống WebLogic chuẩn Oracle

Mô hình sau đây được nhiều doanh nghiệp lớn tại Việt Nam và trên thế giới sử dụng ổn định hơn 10 năm rồi, các bạn tham khảo nhé:

Thứ Bảy, 22 tháng 2, 2020

HỌC ORACLE DB TỪ A-Z - BÀI 19: CÁC QUYỀN HỆ THỐNG

19.1.PHÂN LOẠI QUYỀN
Oracle database có khoảng 80 quyền hệ thống và con số này đang tiếp tục tăng lên.
Các quyền hệ thống có thể chia ra như sau:
§  Các quyền cho phép thực hiện các thao tác mức độ rộng trên hệ thống ví dụ như: CREATE SESSION, CREATE TABLESPACE.
§  Các quyền cho phép quản lý các đối tượng thuộc về một user ví dụ: CREATE TABLE
§  Các  quyền cho phép quản lý các đối tượng trong bất cứ một schema nào ví dụ câu lệnh: CREATE ANY TABLE.
Có thể điều khiển các quyền bằng cách câu lệnh GRAND hay REVOKE.

Chú ý:
Các users có quyền ANY đều có thể truy xuất các bảng thuộc data dictionary ngoại trừ các tiền tố USER_ALL và bất cứ views nào trên đó các quyền được gán cho PUBLIC.

HỌC ORACLE DB TỪ A-Z - BÀI 18: QUẢN LÝ THÔNG TIN PROFILES

18.1.GIỚI THIỆU PROFILE

Thông tin profile là tập hợp các thông tin về tài nguyên hệ thống và thông tin liên quan tới mật khẩu của user bao gồm:
§  Thời gian sử dụng CPU (CPU time)
§  Các thao tác vào ra (I/O operations)
§  Thời gian nghỉ (Idle time)
§  Thời gian kết nối (Connect time)
§  Dung lương bộ nhớ (Memory space)
§  Các phiên làm việc đồng thời (Concurrent sessions)
§  Số lần sử dụng và thời gian sử dụng một mật khẩu (Password aging and expiration)
§  Lịch sử thay đổi mật khẩu (Password history)
§  Cơ chế xác nhận mật khẩu (Password complexity verification)
§  Khoá account truy nhập (Account locking)

Quản trị viên database gán các profile cho từng user mà theo đó Oracle server phân bổ tài nguyên cho user theo thông tin có trong profile.
Oracle server tự động tạo default profile (profile mặc định) mỗi khi tạo database. Ban đầu, các thông tin trong default profile được đặt không hạn chế. Quản trị viên database có thể điều chỉnh lại các tham số này đối với từng user.

HỌC ORACLE DB TỪ A-Z - BÀI 17: QUẢN LÝ USER

17.1.USER TRONG DATABASE

17.1.1.  User và những thành phần liên quan

Security Domain
Quản trị viên database định nghĩa các tên User. Qua đó, cho phép người sử dụng có thể truy nhập vào database thông qua các tên user này. Security domain (bảo mật theo miền) định nghĩa các quyền truy nhập nhất định trên các đối tượng trong database và áp dụng các quyền này cho từng user có trong database.
Hình vẽ 1.    Các thành phần bảo mật

HỌC ORACLE DB TỪ A-Z - BÀI 16: NẠP VÀ TỔ CHỨC LƯU TRỮ DỮ LIỆU

16.1.GIỚI THIỆU CHUNG

16.1.1.  Tổng quan việc nạp dữ liệu

Hình vẽ 1.    Tổng quan về việc nạp và lưu trữ dữ liệu

Có một số phương pháp khác nhau để có thể load (nạp) dữ liệu vào trong tables của oracle database, các phương pháp được đề cập trong chương này bao gồm.
§  Công cụ direct load insert: nạp dữ liệu trực tiếp.
§  SQL*loader: nạp dữ liệu từ file text, khuôn dạng tự do
§  Công cụ Import và Export: nạp dữ liệu từ file lưu trữ với khuôn dạng do Oracle quy định.

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master