Cleaning Journal table

Journal Table cleanup process

As we have seen in previous posts journal table behaviour, if the rebuild gets cancelled in between and oracle does not gets a latch, it does not clear the journal table automatically.

Also, oracle won’t allow dropping or rebuilding of index.

session1>alter index mytab_idx rebuild online;
alter index mytab_idx rebuild online
*
ERROR at line 1:
ORA-08104: this index object 98701 is being online built or rebuilt

sesion1>drop index mytab_idx;
drop index mytab_idx
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Its the responsibility of the smon to clear the indexes stuck in journal.

However, in many cases dba intervention is required.

There are 2 ways to clear the journal table.

1. By waking up smon

2. By using oracle supplied dbms_repair.online_index_clean package

waking up smon

We can post smon to clear the temporary segments and journal by issuing the following command.

select pid from v$process p, v$bgprocess b where b.paddr = p.addr and name='SMON';

oradebug wakeup <pid>

Using dbms_repair.online_index_clean package

dbms_repair is a oracle supplied package which has many procedures to detect and repair block corruption or rebuild freelists etc.

we can use online_index_clean to clean up the journal table.

Be informed that this procedure also requires initial latch. I have developed a small script ndx-cleanup.sql to clear the journal table. It takes owner and index name as input and must be executed from SYS.                                                                                                                                                                      

Session1>@ndx-cleanup
Enter value for owner: subhatia_dba
Enter value for index_name: mytab_idx
OBJECT ID OF INDEX IS -----------> 98701
JOURNAL TABLE IS -----------> SYS_JOURNAL_98701
CURRENT INDEX STATUS -----------> VALID
INDEX CLEANUP COMPLETED
JOURNAL TABLE HAS BEEN CLEANED

PL/SQL procedure successfully completed.

ndx-cleanup.sql

This script must be executed as SYS and is used to cleanup the journal table.

declare

/* Name :- ndx-cleanup.sql */
/* DB Version :- 11.1.0.7 */
/* Author :- Sumit Bhatia */
/* Version :- 1.1
/* Purpose :- To cleanup the journal entries */
/* Input :- owner and index name */

/*declare variables*/
index_name dba_objects.object_name%type;
index_owner dba_objects.owner%type;
index_id dba_objects.object_id%type;
index_status dba_objects.status%type;
journal_name dba_objects.object_name%type;
retval boolean;
wait_for_lock binary_integer;

begin
/* INITIALIZE */
journal_name:='NONE_NONE';

/*check if index exists*/
 begin
select object_name,owner,object_id,status into index_name,index_owner,index_id,index_status from dba_objects where owner=upper('&owner') and object_name=upper('&index_name') and object_type='INDEX';
 exception
 WHEN NO_DATA_FOUND THEN
 dbms_output.put_line('INDEX NOT FOUND');
 raise;
 end;

/*check if index is stuck in journal*/
 begin
 select object_name into journal_name from dba_objects where object_name=concat('SYS_JOURNAL_',index_id);
 exception
 WHEN NO_DATA_FOUND THEN
 journal_name:='NONE_NONE';
 end;

dbms_output.put_line('OBJECT ID OF INDEX IS -----------> '||index_id);
if journal_name ='NONE_NONE'
then
 dbms_output.put_line('No journal table found. Index is clean');
else
 dbms_output.put_line('JOURNAL TABLE IS -----------> '||journal_name);
 dbms_output.put_line('CURRENT INDEX STATUS -----------> '||index_status);

 wait_for_lock := NULL;
 retval:=sys.dbms_repair.online_index_clean(index_id);
 case
 when retval then dbms_output.put_line('INDEX CLEANUP COMPLETED');
 when not retval then dbms_output.put_line('INDEX COULD NOT BE CLEANED');
 end case;

 commit;
end if;

/*confirm that journal entry has been cleared */
begin
 select object_name into journal_name from dba_objects where object_name=concat('SYS_JOURNAL_',index_id);
 exception
 WHEN NO_DATA_FOUND THEN
 dbms_output.put_line('NO JOURNAL TABLE FOUND');
 end;

end;
/