Como pré requisito antes de executar upgrade para o Oracle Database 19c, o relatório gerado com “autoupgrade.jar” pode sugerir encerrar as transações distribuídas que ficaram pendentes no banco de dados:
2. Resolve all outstanding distributed transactions prior to database
upgrade.
The database contains one or more unresolved distributed transactions
found in sys.dba_2pc_pending.
There can be no unresolved distributed transactions prior to database
upgrade because the data they contain would be lost during the upgrade.
You can view the unresolved distributed transactions with
SELECT * FROM sys.dba_2pc_pending;
A seguinte query monta os comandos para realizar essa limpeza, usando a procedure PURGE_LOST_DB_ENTRY da package DBMS_TRANSACTION.
SQL> SELECT 'exec dbms_transaction.purge_lost_db_entry(''' || local_tran_id || ''');' FROM SYS.DBA_2PC_PENDING;
'EXECDBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('''||LOCAL_TRAN_ID||''');'
--------------------------------------------------------------------
exec dbms_transaction.purge_lost_db_entry('16301.12.16898');
exec dbms_transaction.purge_lost_db_entry('21665.12.22787');
Então podemos executar os comandos no SQLPLUS:
SQL> exec dbms_transaction.purge_lost_db_entry('16301.12.16898');
PL/SQL procedure successfully completed.
Mas o segundo comando pode apresentar erro:
SQL> exec dbms_transaction.purge_lost_db_entry('21665.12.22787');
BEGIN dbms_transaction.purge_lost_db_entry('21665.12.22787'); END;
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 74
ORA-06512: at "SYS.DBMS_TRANSACTION", line 96
ORA-06512: at line 1
Workaround:
Uma “solução” simples pode ser simplesmente executar o segundo comando em uma nova sessão.
Saindo do SQLPLUS (fechando esta sessão):
SQL> EXIT
Disconnected from Oracle Database 11g EE Extreme Perf Release 11.2.0.4.0 - 64bit Production
Entrando no SQLPLUS novamente (abrindo uma nova sessão):
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 31 21:56:02 2021
Executando o mesmo comando que havia apresentado erro anteriormente:
SQL> exec dbms_transaction.purge_lost_db_entry('21665.12.22787');
PL/SQL procedure successfully completed.
Nenhuma transasão pendente:
SQL> SELECT 'exec dbms_transaction.purge_lost_db_entry(''' || local_tran_id || ''');' FROM SYS.DBA_2PC_PENDING;
no rows selected