Index online redefinition repair

--
-- 1) alter index owner.index_name rebuild online;
-- 2)kill session
-- 3) ...
--

-- Check Locks On online_index:
select *
from v$session
where sid in
(select session_id from dba_dml_locks where name = 'EVENTCOMMIT')


-- Cleanup index:
declare
  retval boolean;
  object_id binary_integer;
  wait_for_lock binary_integer;
begin
  object_id := 71593;
  wait_for_lock := 0;
  
  retval := dbms_repair.online_index_clean (object_id, wait_for_lock);
  commit;
  
  dbms_output.put_line(retval);
end;

Work with NFS on Linux

--
-- Work with NFS on Linux
--

-- On NFS Server:
chkconfig --list
chkconfig --level 345 nfs on
service nfs start

vi /etc/exports
/u05/oracle/backups replica(rw)

service nfs reload


-- On NFS Client:
mount -t nfs aqua:/u05/oracle/backups /mnt/aqua_backups
umount /mnt/aqua_backups

vi /etc/fstab
aqua:/u05/oracle/backups /mnt/aqua_backups      nfs     soft,bg         1 2

mount /mnt/aqua_backups

Установка и конфигурирование шлюза Oracle к SQL Server

Установка шлюза

Соблюдение требований к оборудованию и операционной системе (Certification Matrix for Open System Gateways).

Настройка переменных среды окружения
export DISPLAY=xxx.xxx.xxx.xxx:0.0
export ORACLE_BASE=/app/oracle

Шаги установщика Oracle Universal Installer:

Oracle Universal Installer: Welcome   -> Next
Oracle Universal Installer: Select a Product to Install   -> Oracle Database  Gateways 11.1.0.6
Oracle Universal Installer: Specify Home Details  -> 
   Name: OraGtw11g_home1
   Path: /app/oracle/product/11.1.0/tg_1
Oracle Universal Installer: Available Product Components   -> 
    a. Select Oracle Database Gateway for SQL Server 11.1.0.6.0.
    b. Click Next.
Oracle Universal Installer: Oracle Database Gateway for SQL Server   ->
  SQL Server Database Server Host Name: testhost
  SQL Server Database Server Port number: 1344
  SQL Server Instance Name:  
  SQL Server Database Name: testdb
Oracle Universal Installer: Summary   -> Click Install.
Oracle Net Configuration Assistant: Welcome   -> Click Cancel.
Oracle Net Configuration Assistant   -> Click Yes.
Error   ->  Click Ok.
Oracle Universal Installer: Configuration Assistants   ->  Click Next.
Execute Configuration Scripts   -> (as root)# /app/oracle/product/11.1.0/tg_1/root.sh
  -> Then Click OK.
Oracle Net Configuration Assistant: End of Installation -> Click Exit

Блокировки Oracle (Dead locks)

Возвращает строки, когда блокировки присутствуют:

SELECT
  ' Blocking_user    : ' || bs.username || chr(13)||chr(10)||
  ' DB_user          : ' || bs.username || chr(13)||chr(10)||
  ' Waiting_user     : ' || ws.username || chr(13)||chr(10)||
  ' Blocking_SID     : ' || bs.SID      || chr(13)||chr(10)||
  ' Blocking_Serial# : ' || bs.serial#  || chr(13)||chr(10)||
  ' Waiting_SID      : ' || ws.SID      || chr(13)||chr(10)||
  ' Blocking_App     : ' || bs.program  || chr(13)||chr(10)||
  ' Waiting_App      : ' || ws.program  || chr(13)||chr(10)||
  ' Blocking_machine : ' || bs.machine  || chr(13)||chr(10)||
  ' Waiting_machine  : ' || ws.machine  || chr(13)||chr(10)||
  ' Blocking_OS_user : ' || bs.osuser   || chr(13)||chr(10)||
  ' Waiting_OS_user  : ' || ws.osuser   || chr(13)||chr(10)||
  ' lock_type        : ' ||
    DECODE (
      wk.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',
      wk.TYPE) || chr(13)||chr(10)||
  ' Mode_held        : ' ||
    DECODE (
      hk.lmode,
      0, 'None',
      1, 'NULL',
      2, 'ROW-S (SS)',
      3, 'ROW-X (SX)',
      4, 'SHARE',
      5, 'S/ROW-X (SSX)',
      6, 'EXCLUSIVE',
      TO_CHAR (hk.lmode)) ||chr(13)||chr(10)||
  ' Mode_req         : ' ||
    DECODE (
      wk.request,
      0, 'None',
      1, 'NULL',
      2, 'ROW-S (SS)',
      3, 'ROW-X (SX)',
      4, 'SHARE',
      5, 'S/ROW-X (SSX)',
      6, 'EXCLUSIVE',
      TO_CHAR (wk.request)) ||chr(13)||chr(10)||
  ' Id1              : ' || TO_CHAR (hk.id1) || chr(13)||chr(10)||
  ' Id2              : ' || TO_CHAR (hk.id2)
FROM
  v$lock hk,
  v$session bs,
  v$lock wk,
  v$session ws
WHERE hk.BLOCK = 1
  AND hk.lmode != 0
  AND hk.lmode != 1
  AND wk.request != 0
  AND wk.TYPE(+) = hk.TYPE
  AND wk.id1(+) = hk.id1
  AND wk.id2(+) = hk.id2
  AND hk.SID = bs.SID(+)
  AND wk.SID = ws.SID(+)
ORDER BY 1;

Индексы требующие перестроения

declare
  v_Owner    dba_indexes.owner%type;        -- Setup Index Owner
  v_IdxName  dba_indexes.index_name%type;   -- Setup Index Name
  
  vOwner     dba_indexes.owner%type;        -- Index Owner
  vIdxName   dba_indexes.index_name%type;   -- Index Name
  vAnalyze   varchar2(100);                 -- String of Analyze Stmt
  vCursor    number;                        -- DBMS_SQL cursor
  vNumRows   integer;                       -- DBMS_SQL return rows
  vHeight    index_stats.height%type;       -- Height of index tree
  vLfRows    index_stats.lf_rows%type;      -- Index Leaf Rows
  vDLfRows   index_stats.del_lf_rows%type;  -- Deleted Leaf Rows
  vDLfPerc   number;                        -- Del lf Percentage
  vMaxHeight number;                        -- Max tree height
  vMaxDel    number;                        -- Max del lf percentage
  
  cursor cGetIdx(
    pc_owner dba_indexes.owner%type,
    pc_index dba_indexes.owner%type
  )
  is select owner,index_name
  from dba_indexes
  where owner = nvl(pc_owner, owner)
    and index_name = nvl(pc_index, index_name)
    and index_type='NORMAL'
    order by owner, index_name;
begin
  -- Customize maximums:
  vMaxHeight := 3;
  vMaxDel    := 20;
  
  -- Customize indexes:
  v_Owner   := 'OWNER_NAME';
  v_IdxName := 'INDEX_NAME';
  
  -- Open Indexes Cursor:
  open cGetIdx(v_Owner, v_IdxName);
  
  -- For every index, validate structure:
  loop
    fetch cGetIdx
    into vOwner, vIdxName;
    
    exit when cGetIdx% notfound;
    
    -- Try Analyze Index:
    begin
      -- Open DBMS_SQL cursor:
      vCursor := dbms_sql.open_cursor;
      
      -- Set up dynamic string to validate structure:
      vAnalyze := 'analyze index ' || vOwner || '.' || vIdxName || ' validate structure';
      dbms_sql.parse(vCursor, vAnalyze, dbms_sql.v7);
      vNumRows := dbms_sql.execute(vCursor);
      
      -- Close DBMS_SQL cursor:
      dbms_sql.close_cursor(vCursor);
      
      -- Does index need rebuilding? If so, then generate command
      select height, lf_rows, del_lf_rows
      into vHeight, vLfRows, vDLfRows
      from index_stats;
      
      -- Handle case where div by zero:
      if vDLfRows = 0 then 
        vDLfPerc := 0;
      else
        vDLfPerc := trim((vDLfRows / vLfRows) * 100);
      end if;
      
      if (vDLfPerc > vMaxDel) then
        dbms_output.put_line(
          'alter index ' || vOwner || '.' || vIdxName ||
          ' rebuild online compute statistics;' || ' -- ' || to_char(vDLfPerc, '09') || '%'
        );
      end if;
    exception
      when others then
        dbms_output.put_line( '-- ' || vOwner || '.' || vIdxName || ': ' || sqlerrm());
    end;
  end loop;
  
  -- Close Indexes Cursor:
  close cGetIdx;
end;
/

Мониторинг отката транзакций

Иногда после принудительного прекращения массового обновления ощущается замедление работы СУБД.
Можно проверить наличие отката транзакций запросом:

select *
from V$FAST_START_SERVERS t;

который вернет информацию по процесам, выполняющим откат незавершенной транзакции.

Ход выполнения отката транзакции можно уточнить запросом:

select t.UNDOBLOCKSDONE/t.UNDOBLOCKSTOTAL*100
from v$fast_start_transactions t;

Более подробно можно почитать здесь:
week2_10gdba.html