执行闪回时发生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...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
