[Oracle EBS] ORA-01591 In-Doubt Transactions


Problem :
ORA-01591: lock held by in-doubt distributed transaction.
Saat sedang melakukan query ke database oracle untuk mengambil data tertentu dengan filter/kondisi where, tau2 terjadi error. Padahal itu adalah query dengan kondisi where biasa dan tidak terlalu rumit. Saat dicoba query tanpa kondisi where, tidak terjadi error. Errornya adalah :
ORA-01591: lock held by in-doubt distributed transaction 9.4.660456
Menurut oracle, penjelasan mengenai ORA-01591, adalah sebagai berikut:
ORA-01591 lock held by in-doubt distributed transaction string
Cause: An attempt was made to access resource that is locked by a dead two-phase commit transaction that is in prepared state.
Action: The database administrator should query the PENDING_TRANS$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, the database administrator should contact the database administrator at the commit point if known or the end user for correct outcome, or use heuristic default if given to issue a heuristic COMMIT or ABORT command to finalize the local portion of the distributed transaction.
Cause :
Kondisi error tersebut disebut dengan In-Doubt Transactions.  Error terjadi saat kita mempunyai 2 buah proses transaction yang bersamaan dan saling menunggu untuk melakukan commit atau rollback sedangkan transaction yang ditunggu sudah crash/dead sehingga kondisi commit tidak pernah terjadi sehingga terjadilah kondisi lock. Hal itu terjadi karena :
– Mesin server yang menjalankan Oracle Database crash
– Koneksi Jaringan yang terputus saat terjadi proses transaction
– Kesalahan Aplikasi.
Penjelasan lebih lengkap bisa dilihat di Oracle® Database Administrator’s Guide

Solution :
1. Masuk ke sqlplus atau toad atau tools lainnya, connect sebagai sysdba.

2. Jalankan querty untuk melakukan pengecekan transaction id
SQL> select local_tran_id from dba_2pc_pending;
LOCAL_TRAN_ID
———————
1.9.4.660456


3. Lakukan rollback.
SQL> ROLLBACK FORCE '9.4.660456';
Rollback complete.
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('9.4.660456');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.


4. Cek lagi
SQL> select local_tran_id from dba_2pc_pending;
no rows selected


Oke done. Problem solved.

[Oracle EBS] Query to list Reports assigned to a Responsibility

On Oracle E Business Suites, if we want to view any report that attaches to a responsibilities, we can use this following Query.

SELECT fcpl.user_concurrent_program_name "REPORT NAME",
                fnrtl.responsibility_name, frg.request_group_name,
                fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME"
           FROM apps.fnd_request_groups frg,
                apps.fnd_request_group_units frgu,
                apps.fnd_concurrent_programs fcp,
                apps.fnd_concurrent_programs_tl fcpl,
                apps.fnd_executables fe,
                apps.fnd_responsibility fnr,
                apps.fnd_responsibility_tl fnrtl
          WHERE frg.application_id = frgu.application_id
            AND frg.request_group_id = frgu.request_group_id
            AND frg.request_group_id = fnr.request_group_id
            AND frg.application_id = fnr.application_id
            AND fnr.responsibility_id = fnrtl.responsibility_id
            AND frgu.request_unit_id = fcp.concurrent_program_id
            AND frgu.unit_application_id = fcp.application_id
            AND fcp.concurrent_program_id = fcpl.concurrent_program_id
            AND fe.execution_method_code LIKE 'P' --'Oracle Reports'
            AND fcp.executable_id = fe.executable_id
            AND fe.application_id = fcp.executable_application_id
            AND fnrtl.responsibility_name LIKE '&Responsibility_Name'
            -- Example Resp. Name : Inventory, Vision Operations (USA)
            AND fnrtl.LANGUAGE = 'US'
            AND fcpl.LANGUAGE = 'US';

For example, we want to view the report that attaches to Global HRMS Manager Responsibilities. Just fill the value from responsibility_name variables into Global HRMS Manager :


Than the result is :



Untuk melihat Report apa saja yang menempel pada suatu responsibilities bisa menggunakan Query diatas.

[Oracle EBS] Query Period Status

Period status didalam Oracle Application (EBS / E-Business Suite) sangat penting. Karena saat mau melakukan posting maupun memproses transaksi dibutuhkan period yang open/future tergantung pada Aplikasi (modul) yang dipergunakan. Suatu perusahaan atau organisasi bisa menggunakan banyak modul seperti General Ledger, Inventory, Payables, Receivables, dan lain-lain. Jika kita ingin memeriksa status period nya open/closed di masing-masing modul tanpa harus berpindah-pindah modul, sehingga bisa menghemat waktu dan tenaga maka bisa di pergunakan query berikut ini. Yang dibutuhkan adalah application_name pada table fnd_application_tl, serta set_of_books_id, period_name pada table gl_period_statuses

SELECT gs.period_name, fa.application_name, gs.closing_status, gs.set_of_books_id,
DECODE (gs.closing_status,
'C','Closed',
'O','Open',
'F','Future',
'W','Closed Pending',
'N','Never Opened',
'P','Permanently Closed'
) "PERIOD_CLOSING_STATUS"
FROM gl.gl_period_statuses gs, apps.fnd_application_tl fa
WHERE fa.application_id = gs.application_id
AND fa.application_name IN
('Payables', 'Receivables', 'General Ledger') --fill with oracle module
AND gs.set_of_books_id = :set_of_books_d --fill with set of books id
AND gs.period_name in ('OCT-14','SEP-14') --fill with Period Nam
and closing_status in ('O','W','C')
group by gs.Period_name, fa.application_name, gs.closing_status, gs.set_of_books_id order by Period_name desc