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;

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

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