ORACLE19c执行事务回退报:ORA-55510

郑全

1.前提条件设置
conn / as sysdba

SQL> alter database add supplemental log data ;
SQL> alter database add supplemental log data (primary key) columns;

SQL> SELECT supplemental_log_data_min,a.SUPPLEMENTAL_LOG_DATA_PK fROM V$DATABASE a;

SUPPLEME SUP
-------- ---
YES      YES



2.准备数据

SQL> conn hr/hr
Connected.

SQL> CREATE TABLE T_FLASH_TRANS (ID NUMBER, NAME VARCHAR2(30));

Table created.

SQL> INSERT INTO T_FLASH_TRANS VALUES (1, 'A');

1 row created.

SQL> INSERT INTO T_FLASH_TRANS VALUES (2, 'B');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from T_FLASH_TRANS;

        ID NAME
---------- ------------------------------
         1 A
         2 B

SQL> INSERT INTO T_FLASH_TRANS SELECT 3 + ROWNUM, 'C' FROM T_FLASH_TRANS;

2 rows created.

SQL> select * from T_FLASH_TRANS;

        ID NAME
---------- ------------------------------
         1 A
         2 B
         4 C
         5 C

SQL> commit;

3.找出事务ID
SQL> r
  1  SELECT XID, TABLE_NAME, OPERATION, UNDO_SQL,start_timestamp FROM FLASHBACK_TRANSACTION_QUERY
  2*      WHERE TABLE_NAME = 'T_FLASH_TRANS'

XID                        TABLE_NAME           OPERATION        UNDO_SQL                                                                                                  START_TIMESTAMP
------------------------  --------------------      -------------------   ------------------------------------------------------------------------------------------------   -------------------
05002100F5030000  T_FLASH_TRANS     INSERT               delete from "HR"."T_FLASH_TRANS" where ROWID = 'AAASrkAAHAAAAI0AAC'；25-05-2024:12:05:44
05002100F5030000  T_FLASH_TRANS     INSERT               delete from "HR"."T_FLASH_TRANS" where ROWID = 'AAASrkAAHAAAAI0AAD'   25-05-2024:12:05:44
0600190002040000  T_FLASH_TRANS     DELETE               insert into "HR"."T_FLASH_TRANS"("ID","NAME") values ('1','A');                      25-05-2024:12:09:23
0600190002040000  T_FLASH_TRANS     DELETE               insert into "HR"."T_FLASH_TRANS"("ID","NAME") values ('2','B');                      25-05-2024:12:09:23

4 rows selected.

4.回退

SQL>   DECLARE
  2    V_XID XID_ARRAY;
  3    BEGIN
  4    V_XID := SYS.XID_ARRAY('0600190002040000');
  5    DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
  6    END;
  7  /
  DECLARE
*

ERROR at line 1:
ORA-55510: Mining could not start
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at "SYS.TXN_BACKOUT", line 21
ORA-06512: at line 1

经过检查，只有归档没有开启，开启归档后，问题解决

SQL>]SQL>]Database log mode              No Archive Mode

Automatic]Automatic]Archive destination            /u01/app/oracle/product/19.22.0/dbhome_1/dbs/arch

Oldest]Oldest]Current log sequence           13

SQL>]SQL>]Database closed.

Database]Database]ORACLE instance shut down.

SQL>]SQL>]ORACLE instance started.

<span]

<span]Total System Global Area 2348806832 bytes

Fixed]Fixed]Variable Size             553648128 bytes

Database]Database]Redo Buffers                7831552 bytes

Database]Database]SQL> alter database archivelog ;

<span]

<span]Database altered.

<span]

<span]SQL> alter database open;

<span]

<span]Database altered.

<span]

<span]SQL> archive log list;

Database]Database]Automatic archival             Enabled

Archive]Archive]Oldest online log sequence     11

Next]Next]Current log sequence           13

SQL>  DECLARE
  2   V_XID XID_ARRAY;
  3   BEGIN
  4   V_XID := SYS.XID_ARRAY('0600190002040000');
  5   DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
  6   END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-55507: Encountered mining error during Flashback Transaction Backout. function:krvxglsr
ORA-01291: missing log file
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 5

这个是由于还没有一个归档日志导致，执行一次日志切换后，问题解决。

SQL> alter system archive log current;

System altered.

SQL>  DECLARE
  2   V_XID XID_ARRAY;
  3   BEGIN
  4   V_XID := SYS.XID_ARRAY('0600190002040000');
  5   DBMS_FLASHBACK.TRANSACTION_BACKOUT(1, V_XID);
  6   END;
  7  /

PL/SQL procedure successfully completed.