Forum Stats

  • 3,872,219 Users
  • 2,266,407 Discussions
  • 7,911,107 Comments

Discussions

Cleaning up 2phase commit transactions

Geert Gruwez
Geert Gruwez Member Posts: 1,011
edited Jan 26, 2016 2:53AM in Database Ideas - Ideas

Idea: Be able to provide privileges to a normal db user so they can cleanup pending 2pc transactions.

Sometimes I need to cleanup lost transactions found in dba_2pc_pending.

This can only be done with the sys user.

It would help if privileges can be assigned to a normal user so they can initiate cleanup.

Technically ... cleaning up those transactions here is dead simple.

Run this script, and then it's output in sqlplus

select 'rollback force '''||local_tran_id||'''; '||chr(10)||
'exec dbms_transaction.purge_lost_db_entry('''||local_tran_id||'''); '||chr(10)||
'commit; '||chr(10)||chr(10) t  
from dba_2pc_pending;

Or is this cleanup supposed to work "as designed" ?

Meaning ... you have to give the sys password to anybody wanting to cleanup ?

An automated cleanup job "with the sys user" is a solution.

However, not a lot of people will bother investigating the reasons for failures if it's all automated ...

I know it's against policy ... but even dba's take a holiday !

BPeaslandDBAChizzle Pack
2 votes

Active · Last Updated

Comments

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    I think what you're saying here is that a trusted user doesn't need to be a DBA (ie a sys user).

    For a non-production environment it could be a good idea to allow users to force rollback and cleanup themselves....

    For production, I'd have some concerns.....

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    I'm not a DBA, but there are some severe-looking warnings for DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY - are you sure this should be a routine task?

    Agreed maintenance tasks should generally be associated with grantable system privileges rather than the SYS account though.

  • Geert Gruwez
    Geert Gruwez Member Posts: 1,011

    The idea was to create a cleanup procedure in a non-sys schema.

    The cleanup is for very specific transactions of very specific hosts which always go in error.

    Without the grants it's impossible to setup.

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    The idea was to create a cleanup procedure in a non-sys schema.

    The cleanup is for very specific transactions of very specific hosts which always go in error.

    Without the grants it's impossible to setup.

    What about if you create an account just for maintenance, and assign the sysdba privilege?

    Or does it need to be the sys schema?

  • Geert Gruwez
    Geert Gruwez Member Posts: 1,011

    I attempted that, but i get error 01031 ... insufficient priviliges

    /*

    create user cleanup identified by incorrect;

    grant create session to cleanup;

    grant sysdba to cleanup;

    grant dba to cleanup;

    grant select on dba_2pc_pending to cleanup;

    alter user cleanup default role all;

    -- what other grants can be given ???


    create table cleanup.company_2pc_cleanup as
    select
      local_tran_id,
      global_tran_id,
      state,
      fail_time,
      retry_time,
      os_user,
      os_terminal,
      host,
      cast(null as date) cleanup_time,
      cast(null as varchar2(500)) info
    from dba_2pc_pending
    where 1 = 0;
    */

    create or replace procedure cleanup.cleanup2pc as
      cursor cp is
        select local_tran_id, global_tran_id, state, fail_time, retry_time, os_user, os_terminal, host
        from dba_2pc_pending
        where retry_time is not null;
      temp varchar2(500);
    begin
      for c in cp loop
        begin
          execute immediate 'rollback force '''||c.local_tran_id||'''';
          execute immediate 'exec dbms_transaction.purge_lost_db_entry('''||c.local_tran_id||''') ';
          insert into cleanup.company_2pc_cleanup (local_tran_id, global_tran_id, state, fail_time, retry_time, os_user, os_terminal, host, cleanup_time, info)
            values (c.local_tran_id, c.global_tran_id, c.state, c.fail_time, c.retry_time, c.os_user, c.os_terminal, c.host, sysdate, 'Cleanup done');
        exception
          when others then
          begin
            temp := substr(sqlerrm, 1, 500);
            insert into cleanup.company_2pc_cleanup (local_tran_id, global_tran_id, state, fail_time, retry_time, os_user, os_terminal, host, cleanup_time, info)
            values (c.local_tran_id, c.global_tran_id, c.state, c.fail_time, c.retry_time, c.os_user, c.os_terminal, c.host, sysdate, temp);
          end;
        end;
        commit;
      end loop;
    end; 

    select cleanup_time, info from cleanup.company_2pc_cleanup;

    CLEANUP_TIME    INFO

    ------------------- ------------------------------------

    26/01/2016 13:32:32 ORA-01031: Onvoldoende rechten

    26/01/2016 13:33:18 ORA-01031: Onvoldoende rechten

    any other ideas ?

  • Geert Gruwez
    Geert Gruwez Member Posts: 1,011

    I'm not a DBA, but there are some severe-looking warnings for DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY - are you sure this should be a routine task?

    Agreed maintenance tasks should generally be associated with grantable system privileges rather than the SYS account though.

    no matter how grave a task is

    after a certain amount of repetitions every task becomes routine

    even in a nuclear power plant

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    "It would help if privileges can be assigned to a normal user so they can initiate cleanup."

    If they can only clean up their transactions now someone else's.