重庆思庄Oracle、Redhat认证学习论坛

标题: ORACLE19c执行事务回退报:ORA-55510 [打印本页]

作者: 郑全 时间: 2024-5-25 12:43
标题: 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.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)