转载

执行闪回时发生ORA-55510错误

今天在做闪回的时候发生了ORA-55510 错误,记录一下,具体如下
SQL> CREATE TABLE TEST_EMP  TABLESPACE USERS AS SELECT * FROM HR.EMPLOYEES;

Table created.

SQL> DELETE FROM TEST_EMP WHERE  EMPLOYEE_ID=100;   

1 row deleted.

SQL> commit;

Commit complete.

SQL> DELETE FROM TEST_EMP WHERE  EMPLOYEE_ID=101;

1 row deleted.

SQL> commit;

Commit complete.

SQL> UPDATE TEST_EMP SET salary=10000 WHERE employee_id=163;

1 row updated.

SQL> commit;

Commit complete.

SQL> select versions_xid, versions_startscn, versions_endscn,versions_operation, employee_id, first_name from test_emp versions between scn minvalue and maxvalue where versions_operation='D' order by 2;

VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN V EMPLOYEE_ID
---------------- ----------------- --------------- - -----------
FIRST_NAME
--------------------
06001400E0030000           1441528                 D         100
Steven

070010008F030000           1441652                 D         101
Neena


SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> alter table test_emp enable row movement;

Table altered.

在执行闪回时,发生错误
SQL> exec dbms_flashback.transaction_backout(2, xid_array('06001400E0030000','070010008F030000'),dbms_flashback.nocascade);
BEGIN dbms_flashback.transaction_backout(2, xid_array('06001400E0030000','070010008F030000'),dbms_flashback.nocascade); END;

*
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 line 1

之所以出现这个错误原因是:

Minimum supplemental logging was not enabled

所以添加supplemental log data即可

SQL> alter database add supplemental log data;

继续执行,发现也报错了
SQL>  exec dbms_flashback.transaction_backout(2, xid_array('06001400E0030000','070010008F030000'),dbms_flashback.nocascade);
BEGIN dbms_flashback.transaction_backout(2, xid_array('06001400E0030000','070010008F030000'),dbms_flashback.nocascade); END;

*
ERROR at line 1:
ORA-55519: Supplemental logging not available for mining SCN range
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 1
原因是之前的操作是在我们添加supplemental log data之前,没有相应记录,所以只能从头来过啦。
SQL> DROP TABLE TEST_EMP;

SQL> CREATE TABLE TEST_EMP  TABLESPACE USERS AS SELECT * FROM HR.EMPLOYEES;

Table created.

SQL> DELETE FROM TEST_EMP WHERE  EMPLOYEE_ID=100;

1 row deleted.

SQL> commit;

Commit complete.

SQL> DELETE FROM TEST_EMP WHERE  EMPLOYEE_ID=101;

1 row deleted.

SQL> commit;

Commit complete.

SQL> UPDATE TEST_EMP SET salary=10000 WHERE employee_id=163;

1 row updated.

SQL> commit;

Commit complete.

SQL> select versions_xid, versions_startscn, versions_endscn,versions_operation, employee_id, first_name from test_emp versions between scn minvalue and maxvalue where versions_operation='D' order by 2;

VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN V EMPLOYEE_ID
---------------- ----------------- --------------- - -----------
FIRST_NAME
--------------------
02000100BB030000           1442123                 D         100
Steven

01000700DC020000           1442146                 D         101
Neena

执行后发现成功了
SQL> exec dbms_flashback.transaction_backout(2, xid_array('02000100BB030000','01000700DC020000'),dbms_flashback.nocascade);


PL/SQL procedure successfully completed.
SQL> SQL> commit;

Commit complete.
查看下,发现数据已经回来啦
SQL> select count(*) from test_emp where employee_id=100 or employee_id=101;

  COUNT(*)
----------
         2
正文到此结束
Loading...