Enabling XA recovery operations

Oracle requires the granting of special permissions to enable XA recovery operations.

Ensure that we have database administrator authority.

Failure to enable XA recovery can result in the following error:

WTRN0037: The transaction service encountered an error on an xa_recover operation.

  1. As the database administrator, connect to the database by issuing this command: sqlplus /AS SYSDBA.
  2. Run these commands: 
    grant select on pending_trans$ to public;
grant select on dba_2pc_pending to public;
grant select on dba_pending_transactions to public;
grant execute on dbms_system to itim_db_user;
    where itim_db_user is the user that owns the ISIM database, such as itimuser.
  3. Stop and restart the database instance for these changes to take effect.
    • Start the database instance with the following commands:
      # ./sqlplus "/ as sysdba"
SQL> startup nomount pfile= ORACLE_HOME/dbs/initdb_name.ora
    • Stop the database instance with this command:
      SQL> SHUTDOWN [mode]
      where mode is normal, immediate, or abort.

Tune additional settings.

Parent topic: Oracle database performance tuning


Related tasks