执行闪回时发生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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31386161/viewspace-2134086/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31386161/viewspace-2134086/