漫活部落

Oracle 登入相關

-- 強制登入instance (無法執行任何sql，但可以執行 shutdown instance & 執行 oradebug)

sqlplus -prelim / as sysdba

 

Oracle 運行相關

-- 檢查 cursor 數量

set line 200
col sql_text format a80
col user_name format a30
select sql_text, count(*) as "OPEN CURSORS", user_name 
from v$open_cursor 
group by sql_text, user_name 
order by count(*) asc;

-- 查看目前正在執行中的SQL內容

set line 200
col username format a13
col osuser format a13
select s.username,s.sid,s.osuser,t.sql_id,sql_text
from v$sqltext_with_newlines t,v$session s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.sid != sys_context('userenv','sid')
order by s.sid,t.piece;

-- 顯示SQL執行計劃

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('sql_id',0));

-- 對 table objects 新增 DML 監控

dbms_fga.add_policy(object_schema=>'Schema', object_name=>'TableName', policy_name=>'Table_Policy',enable=>true,statement_types=>'INSERT,UPDATE,DELETE');

select timestamp,sql_text,userhost,instance_number 
from dba_fga_audit_trail 
where object_name='TableName' and timestamp > to_date('20210110 13:00:00','yyyymmdd hh24:mi:ss');

 

Oracle Redolog相關

-- 檢查每小時 redolog switch 幾次
-- https://blog.zeddba.com/2019/05/31/online-redo-log-switch-frequency-map/

set pages 999 lines 400
col h0 format 999
col h1 format 999
col h2 format 999
col h3 format 999
col h4 format 999
col h5 format 999
col h6 format 999
col h7 format 999
col h8 format 999
col h9 format 999
col h10 format 999
col h11 format 999
col h12 format 999
col h13 format 999
col h14 format 999
col h15 format 999
col h16 format 999
col h17 format 999
col h18 format 999
col h19 format 999
col h20 format 999
col h21 format 999
col h22 format 999
col h23 format 999
SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day",
 COUNT (1) "Total",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
 SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",
 ROUND (COUNT (1) / 24, 2) "Avg"
FROM gv$log_history
WHERE thread# = inst_id
AND first_time > sysdate -7
GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
ORDER BY 1,2;

 

Oracle Lock 相關

-- RAC 架構查詢 lock

set linesize 180
col user_status format a15
col sid_serial format a15
col program format a30 wrapped
col machine format a15 wrapped
col osuser format a15 wrapped
col conn_instance format a15
col object_name format a25 wrapped
 SELECT DECODE (l.block, 0, 'Waiting', 'Blocking ->') user_status,
     CHR (39) || s.sid || ',' || s.serial# || CHR (39) sid_serial,
     (SELECT instance_name
      FROM gv$instance
      WHERE inst_id = l.inst_id)
      conn_instance,
     s.sid,
     s.program,
     s.osuser,
     s.machine,
     DECODE (l.TYPE,
         'RT', 'Redo Log Buffer',
         'TD', 'Dictionary',
         'TM', 'DML',
         'TS', 'Temp Segments',
         'TX', 'Transaction',
         'UL', 'User',
         'RW', 'Row Wait',
         l.TYPE)
      lock_type--,id1
           --,id2
     ,
     DECODE (l.lmode,
         0, 'None',
         1, 'Null',
         2, 'Row Share',
         3, 'Row Excl.',
         4, 'Share',
         5, 'S/Row Excl.',
         6, 'Exclusive',
         LTRIM (TO_CHAR (lmode, '990')))
      lock_mode,
     ctime--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status
     ,
     object_name
  FROM gv$lock l
     JOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid)
     JOIN gv$locked_object o
      ON (o.inst_id = s.inst_id AND s.sid = o.session_id)
     JOIN dba_objects d ON (d.object_id = o.object_id)
  WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
                   FROM gv$lock
                   WHERE request > 0)
ORDER BY id1, id2, ctime DESC; 

-- RAC 架構查詢 lock

SELECT DISTINCT
     s1.username
    || '@'
    || s1.machine
    || ' ( INST='
    || s1.inst_id
    || ' SID='
    || s1.sid
    || ' Serail#='
    || s1.serial#
    || ' ) IS BLOCKING '
    || s2.username
    || '@'
    || s2.machine
    || ' ( INST='
    || s2.inst_id
    || ' SID='
    || s2.sid
    || ' Serial#='
    || s2.serial#
    || ' ) '
     AS blocking_status
 FROM gv$lock l1,
    gv$session s1,
    gv$lock l2,
    gv$session s2
 WHERE   s1.sid = l1.sid
    AND s2.sid = l2.sid
    AND s1.inst_id = l1.inst_id
    AND s2.inst_id = l2.inst_id
    AND l1.block > 0
    AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;

-- Kill Session in RAC
alter system kill session 'sid,serial#,@inst_id';

-- Kill Session in Standalone
alter system kill session 'sid,serial#;

-- 整批 kill session by username

declare
  cursor connList is
    select serial#, sid, inst_id from gv$session where username = upper('USERNAME');
begin
  for rec in connList
  loop
    execute immediate 'alter system kill session ''' || rec.sid || ',' || rec.serial# || ',@' || rec.inst_id || ''' immediate' ;
  end loop;
end;
/

-- 列出作業系統待 kill session process (db session 砍不掉時，從作業系統上砍) (需逐台執行)

select 'kill -9 '|| spid from v$process where addr in (select paddr from v$session where username = upper('USERNAME'));
select 'kill -9 '|| spid from v$process where addr in (select paddr from v$session where sid = 1234);

-- kill 所有 Oracle 連接的 Process (逐台執行)

ps aux | grep -v grep | grep LOCAL=NO | awk '{print $2}' | xargs kill -9

 

Dataguard 相關

-- 檢查 dataguard 服務狀態 (MRP)

select process, status, sequence# 
from v$managed_standby;

-- 檢查目前Database的dataguard角色狀態

select open_mode, database_role, switchover_status 
from v$database;

-- 檢查Dataguard同步狀況 (Primary & Standby)

select al.thread#, max(al.sequence#) "Last Seq Recieved",
max(lh.sequence#) "Last Seq Applied"
from v$archived_log al, v$log_history lh
where al.thread#=lh.thread#
group by al.thread#;

 

oradebug hanganalyze

-- 執行 oradebug 產出 hanganalyze dump 檔

oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3

 

AWR 相關指令

-- Default retention 8 days
-- Modify interval 60min & retention 35 days

exec dbms_workload_repository.modify_snapshot_settings(interval => 60, retention => 50400);
exec dbms_workload_repository.modify_snapshot_settings(interval => 60, retention => 50400, did => 1234567890);

-- Create snapshot manually

exec dbms_workload_repository.create_snaphost;

 

RMAN 相關指令 & SQL

-- 查詢歷史備份記錄

COL STATUS FORMAT a9
COL hrs FORMAT 999.99
select INPUT_TYPE,
STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs,
INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,
OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,
OUTPUT_DEVICE_TYPE
FROM V$RMAN_BACKUP_JOB_DETAILS
order by SESSION_KEY;

-- Monitor restore

TTITLE LEFT '% Completed. Aggregate is the overall progress:'
SET LINE 132
SELECT opname, round(sofar/totalwork*100) "% Complete"
  FROM gv$session_longops
 WHERE opname LIKE 'RMAN%'
   AND totalwork != 0
   AND sofar <> totalwork
 ORDER BY 1;

TTITLE LEFT 'Channels waiting:'
COL client_info FORMAT A15 TRUNC
COL event FORMAT A20 TRUNC
COL state FORMAT A7
COL wait FORMAT 999.90 HEAD "Min waiting"
SELECT s.sid, p.spid, s.client_info, status, event, state, seconds_in_wait/60 wait
  FROM gv$process p, gv$session s
 WHERE p.addr = s.paddr
   AND client_info LIKE 'rman%';

TTITLE LEFT 'Files currently being written to:'
COL filename FORMAT a50
SELECT filename, bytes, io_count
  FROM v$backup_async_io
 WHERE status='IN PROGRESS'
/

TTITLE OFF
SET HEAD OFF
SELECT 'Throughput: '||
       ROUND(SUM(v.value/1024/1024),1) || ' Meg so far @ ' ||
       ROUND(SUM(v.value     /1024/1024)/NVL((SELECT MIN(elapsed_seconds)
            FROM v$session_longops
            WHERE opname          LIKE 'RMAN: aggregate input'
              AND sofar           != TOTALWORK
              AND elapsed_seconds IS NOT NULL
       ),SUM(v.value     /1024/1024)),2) || ' Meg/sec'
 FROM gv$sesstat v, v$statname n, gv$session s
WHERE v.statistic# = n.statistic#
  AND n.name       = 'physical write total bytes'
  AND v.sid        = s.sid
  AND v.inst_id    = s.inst_id
  AND s.program LIKE 'rman@%'
GROUP BY n.name
/
SET HEAD ON

 

datapump 相關

-- 查詢目前datapump job 狀態

set line 200
col owner_name format a20
col job_name format a30
col operation format a20
col job_mode format a20
col state format a20
select * from DBA_DATAPUMP_JOBS;

-- 查詢目前datapump job 開啟的 session

select * from DBA_DATAPUMP_SESSIONS;

-- attach 查看目前的 job 狀態

impdp '"/ as sysdba"' attach=SYS_IMPORT_FULL_01
Import> status

 

TDE 相關

-- 檢查TDE檔案是否加密

set linesize 150
set pagesize 150
column name format a70
select tablespace_name,
       name,
       encrypted 
from v$datafile_header;

 

Cluster & Service 相關指令

-- 停止cluster crs失敗或卡住時，手動kill服務

[root@node1 ~]# ps -fea | grep ohasd.bin | grep -v grep
root 13783 1 0 Oct25 ? 00:01:41 <Grid Infrastructure Home>/bin/ohasd.bin reboot
[root@node1 ~]# ps -fea | grep gipcd.bin | grep -v grep
oracle 13938 1 0 Oct25 ? 00:00:01 <Grid Infrastructure Home>/bin/gipcd.bin
[root@node1 ~]# ps -fea | grep mdnsd.bin | grep -v grep
oracle 13943 1 0 Oct25 ? 00:00:05 <Grid Infrastructure Home>/bin/mdnsd.bin
[root@node1 ~]# ps -fea | grep gpnpd.bin | grep -v grep
oracle 14178 1 0 Oct25 ? 00:00:03 <Grid Infrastructure Home>/bin/gpnpd.bin
[root@node1 ~]# ps -fea | grep evmd.bin | grep -v grep
oracle 14481 1 0 Oct25 ? 00:00:02 <Grid Infrastructure Home>/bin/evmd.bin
[root@node1 ~]# ps -fea | grep crsd.bin | grep -v grep
root 14684 1 0 Oct25 ? 00:05:31 <Grid Infrastructure Home>/bin/crsd.bin reboot
[root@node1 ~]# kill -9 13783 13938 13943 14178 14481 14684

-- 解除 QUARANTINED 狀態

# <Grid Infrastructure Home>/bin/crsctl query cluster site -all
Site 'sitea' identified by GUID 'e735d4b7d4f8ff65ff8a20e52218df7e' in state 'ENABLED' contains nodes 'oraext1,oraext2' and disks 'DATA02_1(DATADG),FRA02_1(FRADG),OCR01_1(OCRDG)'.
Site 'siteb' identified by GUID '4dc847ffea985f92bf0fd5612413b7c8' in state 'QUARANTINED' contains nodes 'oraext3,oraext4' and disks 'DATA01_2(DATADG),FRA01_2(FRADG),OCR01_2(OCRDG)'.
Site 'sitec' identified by GUID 'a870c9114d954f81ff3aa4fe849d712c' in state 'ENABLED' contains no nodes and no disks can be discovered from this site

# <Grid Infrastructure Home>/bin/crsctl modify cluster site siteb -s rejuvenate

# <Grid Infrastructure Home>/bin/crsctl query cluster site -all
Site 'sitea' identified by GUID 'e735d4b7d4f8ff65ff8a20e52218df7e' in state 'ENABLED' contains nodes 'oraext1,oraext2' and disks 'DATA02_1(DATADG),FRA02_1(FRADG),OCR01_1(OCRDG)'.
Site 'siteb' identified by GUID '4dc847ffea985f92bf0fd5612413b7c8' in state 'ENABLED' contains nodes 'oraext3,oraext4' and disks 'DATA01_2(DATADG),DATA02_2(DATADG),FRA01_2(FRADG),OCR01_2(OCRDG)'.
Site 'sitec' identified by GUID 'a870c9114d954f81ff3aa4fe849d712c' in state 'ENABLED' contains no nodes and no disks can be discovered from this site

-- 查詢 votedisk 狀態

# <Grid Infrastructure Home>/bin/crsctl query css votedisk

 

ASM 相關指令

-- 查詢目前DISK狀態

set lines 999
col diskgroup for a15
col diskname for a15
col path for a30
col failgroup for a10
select group_number, 
       name, 
       failgroup, 
       header_status, 
       path, 
       total_mb, 
       free_mb, 
       mode_status 
from v$asm_disk 
order by group_number;

-- 查詢目前Diskgroup

set lines 999
col name format a10
select group_number, 
       name, 
       sector_size, 
       block_size, 
       allocation_unit_size Allocate_size, 
       state, 
       type, 
       total_mb, 
       free_mb, 
       required_mirror_free_mb Req_Mir_Free, 
       usable_file_mb, 
       offline_disks 
from v$asm_diskgroup
;

-- 查詢Diskgroup與Disk關聯

set lines 999
col diskgroup for a15
col diskname for a15
col path for a30
select a.group_number DG_Number, 
       a.name DiskGroup,
       b.name DiskName, 
       b.total_mb, 
       (b.total_mb-b.free_mb) Used_MB, 
       b.free_mb,b.path,
       b.header_status, 
       b.mode_status
from v$asm_diskgroup a, v$asm_disk b
where a.group_number (+) = b.group_number
order by b.group_number,b.name;

已轉向 blogger 記錄

https://slowlife-notes.blogspot.com