Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Friday, 13 November 2009

Resize Online Redo Logs - Rough Notes

To resize redo log files, we have to drop and recreate them. The status of these files is critical.

Files with a status of:

  • CURRENT - cannot/must not be dropped. To change the status of a current file, we have to initiate a log switch (ALTER SYSTEM SWITCH LOGFILE;).
  • ACTIVE - Is likely to return an error; ORA-01624: log 1 needed for crash recovery of instance. This can be overcome by generating a checkpoint (ALTER SYSTEM CHECKPOINT GLOBAL;).
  • INACTIVE - May be dropped.
  • UNUSED - May be dropped.

    Once again, the status of these files can be changed by initiating a log switch (ALTER SYSTEM SWITCH LOGFILE;).

    The following SQLs assist with the task of:

    Checking pertinent redo log file details
    SELECT a.group#
         , a.member
         , b.bytes / 1024 / 1024 MB
         , b.status
      FROM v$logfile a
         , v$log b 
     WHERE a.group# = b.group#
    order by a.group#;
    Dropping redo log file groups

    ALTER DATABASE DROP LOGFILE GROUP n;
    Resizing redo log file

    ALTER DATABASE ADD LOGFILE GROUP n 
    ('/member_path/redo_logfile_name1',  
     '/member_path/redo_logfile_name2') SIZE nM REUSE;

    For a standby database the following additional steps are needed before dropping a online redo log group ...

    Stop Standby Recovery
    ALTER DATABASE RECOVER MANAGED STANDBY CANCEL;
    Set Standby File Management to Manual
    ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
    Clear Logfile Group
    If the STATUS of the logfile group is CLEARING or CLEARING_CURRENT, you can set it to unused by running command ALTER DATABASE CLEAR LOGFILE GROUP n;
  • Tuesday, 3 November 2009

    Logon Audit Trigger

    This is my version of a logon audit trigger. It picks up every iteration of a logon, even to the point of when a session switches from ACTIVE/INACTIVE mode.

    The more conventional way of creating a logon audit trigger would be to simply capture environment variables using the SYS_CONTEXT function, utilising USERENV, environment variables, for example ..

    sys_context('USERENV','CURRENT_USER')
    sys_context('USERENV','CURRENT_USERID')
    sys_context('USERENV','HOST')
    sys_context('USERENV','OS_USER')

    Create a table to store the information provided by the trigger

    create table nazim_logon_audit 
    (  sid number
     , pid number
     , spid varchar2(12)
     , process varchar2(12)
     , status varchar2(8)
     , username varchar2(30)
     , osuser varchar2(30)
     , program varchar2(48)
     , client_info varchar2(64)
     , module varchar2(48)
     , logon_time varchar2(30)
     , last_call_et varchar2(30)
    );
    

    Create the trigger

    create or replace trigger
       nazim_logon_audit_trigger
    AFTER LOGON ON DATABASE
    BEGIN
    insert into nazim_logon_audit
    (select s.sid 
         , p.pid 
         , p.spid
         , s.process
         , s.status
         , s.username
         , s.osuser
         , s.program
         , s.client_info
         , s.module
         , to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss')
         , to_char(sysdate - (s.last_call_et /86400),'yyyy-mm-dd hh24:mi:ss') 
      from v$session s
         , v$process p
     where s.paddr = p.addr
       and s.username=user 
       and s.logon_time = sysdate
     );
    commit;
    END;
    /

    Monday, 2 November 2009

    Oracle Process Details with SQL Text

    A useful query to check database processes alongwith the executed SQL statement:

    select s.sid
         , p.pid
         , p.spid
         , s.process
         , s.status
         , s.username
         , s.osuser
         , s.program
         , a.name
         , s.client_info
         , s.module
         , to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss')
         , to_char(sysdate - (s.last_call_et /86400),'yyyy-mm-dd hh24:mi:ss')
         , sq.sql_text
      from v$session s
         , v$process p
         , audit_actions a
         , v$sql sq
     where s.paddr = p.addr
       and s.command = a.action
       and sq.address (+) = decode(s.sql_address,'00', s.prev_sql_addr, s.sql_address)
     order by s.status;
    Field Key
  • Oracle Session ID
  • Oracle Process ID
  • OS Process ID
  • OS Client Process ID
  • Status of Session
  • Oracle Username
  • OS Username
  • OS Program Name
  • Command in Progress
  • Client Info from DBMS_APPLICATION_INFO.SET_CLIENT_INFO proc
  • Module Info from DBMS_APPLICATION_INFO.SET_MODULE proc
  • Time of Logon
  • Last Call
  • SQL Text