ORA-55507: Encountered mining error during Flashback Transaction Backout

kingsql發表於2015-05-22
Error

11.2.0.1下執行DBMS_FLASHBACK.TRANSACTION_BACKOUT

會遭遇bug：

[@more@]

PROBLEM:
--------
In a certain condition, if we conduct DBMS_FLASHBACK.TRANSACTION_BACKOUT
then ORA-55507/ORA-1291 raises, as below.

SQL> SELECT versions_xid,versions_operation,COL1,COL2
2 FROM kka.FLASHBACK_TBL_TEST
3 VERSIONS BETWEEN scn minvalue and maxvalue
4 /

VERSIONS_XID V COL1 COL2
---------------- - ---------- ----------
04001400BE000000 U 3 TEST
03001600C0000000 U 2 TEST
0A000D00BD000000 I 3 C
0A000D00BD000000 I 2 B
0A000D00BD000000 I 1 A

SQL> set serveroutput on
SQL> DECLARE
2 f_xid sys.XID_ARRAY := sys.XID_ARRAY();
3 BEGIN
4 f_xid.extend;
5 f_xid(1) := HEXTORAW('&1');
6 DBMS_FLASHBACK.TRANSACTION_BACKOUT(1,f_xid);
7 END;
8 /
Enter value for 1: 0A000D00BD000000
old 5: f_xid(1) := HEXTORAW('&1');
new 5: f_xid(1) := HEXTORAW('0A000D00BD000000');
DECLARE
*
ERROR at line 1:
ORA-55507: Encountered mining error during Flashback Transaction Backout.
function:krvxpsr
ORA-1291: missing logfile
ORA-6512: at "SYS.DBMS_FLASHBACK", line 37
ORA-6512: at "SYS.DBMS_FLASHBACK", line 70
ORA-6512: at line 6

DIAGNOSTIC ANALYSIS:
--------------------
This error seems to have some relationships with a result of executing
"recover database" from RMAN and populated online redo log records into
v$archived_log.

As the logs below, recover database operation uses online redo logs
automatically.

----------
RMAN> recover database;

Starting recover at 25-NOV-10
Starting implicit crosscheck backup at 25-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Finished implicit crosscheck backup at 25-NOV-10

Starting implicit crosscheck copy at 25-NOV-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-NOV-10

searching for all files in the recovery area
no files cataloged

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file
/opt/app/oracle/archive/1_2_735997822.arc
archived log for thread 1 with sequence 3 is already on disk as file
/opt/app/oracle/oradata/orcl/redo03.log
archived log for thread 1 with sequence 4 is already on disk as file
/opt/app/oracle/oradata/orcl/redo01.log
archived log for thread 1 with sequence 5 is already on disk as file
/opt/app/oracle/oradata/orcl/redo02.log
archived log file name=/opt/app/oracle/archive/1_2_735997822.arc
thread=1 sequence=2
archived log file name=/opt/app/oracle/oradata/orcl/redo03.log
thread=1 sequence=3
archived log file name=/opt/app/oracle/oradata/orcl/redo01.log
thread=1 sequence=4
archived log file name=/opt/app/oracle/oradata/orcl/redo02.log
thread=1 sequence=5
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-NOV-10
----------

Then online redo log records can be seen from v$archived_log.

----------
SQL> select name from v$archived_log;

NAME
----------------------------------------------------
/opt/app/oracle/archive/1_1_735997822.arc
/opt/app/oracle/archive/1_2_735997822.arc
/opt/app/oracle/archive/1_3_735997822.arc
/opt/app/oracle/archive/1_4_735997822.arc
/opt/app/oracle/oradata/orcl/redo01.log
/opt/app/oracle/oradata/orcl/redo02.log
/opt/app/oracle/oradata/orcl/redo03.log
----------

I thought record of online redo logs usually cannot be populated into
v$archived_log, but due to this, it seems that mining operation starts
from online redo log and raises ORA-1291. Here is the trace log when
ORA-1291/ORA-55507 occurred.


*** 14:17:18.412
Called backout_xids with option: 1
xid:02.04.200
Scn Hint:0x0000.00000000
Initializing Mining from startScn:0x0000.0003528e endScn:0x0000.000352ce
redo02.log(0x0000.000350f8->0xffff.ffffffff)
1_2_736006522.arc(0x0000.0003523f->0x0000.0003529c)
Oldest Log Scn: 0x0000.000350f8
Cleaning up: callno: 1 error: 55507
Ending Compensating Txn: 0.0.0 status: rollback

WORKAROUND:
-----------

RELATED BUGS:
-------------
none.

REPRODUCIBILITY:
----------------
The reproducibility in-house with a testcase is as follows;

Version OS Reproducibility
=============== =============== ===============
11.2.0.1 AIX 6.1 YES(Ct's site)
11.2.0.1 Linux x86 YES(inhouse)
11.2.0.2 Linux x86 YES(inhouse)
=============== =============== ===============

TEST CASE:
----------
The test case is as below.

1. Take online backup and some archived log files.
2. Take backup controlfile.
3. Shutdown database instance.
4. Delete control file and all data files, but leave online log files
untouched.
5. Restore all datafiles and controlfile taken at 1. and 2. manually.
6. Startup database with mount mode, and execute "recover database"
from RMAN(Right after this, online redo log records will be
populated into v$archived_log)
7. Execute "alter database open resetlogs"
8. Do DBMS_FLASHBACK.TRANSACTION_BACKOUT operation.

STACK TRACE:
------------

SUPPORTING INFORMATION:
-----------------------

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------

DIAL-IN INFORMATION:
--------------------

IMPACT DATE:
------------

*** 11/24/10 10:01 pm *** (CHG: Sta->16)
*** 11/24/10 10:01 pm ***
*** 11/24/10 10:05 pm ***
*** 12/01/10 11:15 pm ***
*** 12/01/10 11:21 pm ***
*** 12/01/10 11:24 pm ***
If we don't use RMAN and specify online log file on recovery,
v$archived_log does not show online log record.
------------------------------------------------------
SQL> recover database using backup controlfile;
ORA-279: change 432307 generated at 12/02/2010 06:31:57 needed for thread 1
ORA-289: suggestion : /ade/b/650688947/oracle/dbs/arch1_3_736669829.dbf
ORA-280: change 432307 for thread 1 is in sequence #3
Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-279: change 432317 generated at 12/02/2010 06:31:57 needed for thread 1
ORA-289: suggestion : /ade/b/650688947/oracle/dbs/arch1_4_736669829.dbf
ORA-280: change 432317 for thread 1 is in sequence #4
ORA-278: log file '/ade/b/650688947/oracle/dbs/arch1_3_736669829.dbf' no
longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/ade/b/650688947/oracle/dbs/t_log2.f
Log applied.
Media recovery complete.
SQL> select name from v$archived_log;
NAME
---------------------------------------------------------------------------
/ade/b/650688947/oracle/dbs/arch1_1_736669829.dbf
/ade/b/650688947/oracle/dbs/arch1_2_736669829.dbf
/ade/b/650688947/oracle/dbs/arch1_3_736669829.dbf
*** 12/01/10 11:31 pm ***
*** 12/01/10 11:33 pm ***
*** 12/01/10 11:37 pm ***
*** 12/01/10 11:38 pm ***
*** 12/01/10 11:39 pm *** (CHG: Database->NULL)
*** 12/01/10 11:39 pm ***
*** 12/01/10 11:39 pm *** (ADD: Impact/Symptom->NON-INTERNAL ERROR )
*** 12/01/10 11:39 pm *** (ADD: Impact/Symptom->FEATURE UNUSABLE )
RELEASE NOTES:
]]DBMS_FLASHBACK.TRANSACTION_BACKOUT could fail after certain recovery operatio
]]ns.
REDISCOVERY INFORMATION:
If DBMS_FLASHBACK.TRANSACTION_BACKOUT reports the error ORA-1291: missing
logfile, its likely this problem.
WORKAROUND:
None
--=================================

下面是我試驗的大致過程：

SQL> show user
USER is "B"
SQL>

SQL> create table tt(id int,name varchar2(10));

Table created.

SQL> insert into tt values(1,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into tt values(2,'b');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into tt values(3,'c');

1 row created.

SQL> commit;

Commit complete.

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


PL/SQL procedure successfully completed.

SQL> select * from tt;

ID NAME
---------- --------------------
1 a
3 c

SQL>

普通使用者執行DBMS_FLASHBACK.TRANSACTION_BACKOUT會報許可權不足，即使有dba許可權也不行，需要單獨授予create any table的許可權。
其它需要注意的事項參考一下老楊總結的吧：

http://yangtingkun.itpub.net/post/468/419695

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1667871/，如需轉載，請註明出處，否則將追究法律責任。

相關文章