ORA-55507: Encountered mining error during Flashback Transaction Backout
来源：互联网 发布：sql语句改列名 编辑：程序博客网 时间：2024/05/14 10:40
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
文章来源：http://blog.itpub.net/19602/viewspace-1060128/
- ORA-55507: Encountered mining error during Flashback Transaction Backout
- Oracle Backout Transaction
- OutputStream encountered error during write
- Error encountered during installing R in Ubuntu
- Fatal error encountered during command execution
- Fatal error encountered during command execution
- Fatal error encountered during data read.
- Weblogic安装出错Fatal error encountered during file installation
- Redeploy' has encountered a problem.An internal error occurred during
- mysql报Fatal error encountered during command execution的解决办法
- 查看流表时出现 Dpctl : Error During Transaction错误的解决方法
- flashback transaction query
- Flashback Transaction Query
- Oracle Flashback Transaction Query
- flashback transaction query
- Flashback Query Flashback Version Query Flashback Table Flashback Transaction
- Oracle10g的Flashback之Flashback Transaction Query
- Flashback Version Query和Flashback Transaction Query！
- notes goldendict github wiki
- 百度地图demo，根据ip展示地图显示地区，并在地图上高亮显示
- 188. Best Time to Buy and Sell Stock IV
- 关于ie兼容性的问题
- 特征的选择_03：ChiSqSelector卡方选择器
- ORA-55507: Encountered mining error during Flashback Transaction Backout
- 2017任务
- 节点门面和自定义事件研究
- 转发——谷歌云官方：一小时掌握深度学习和 TensorFlow
- 线程间操作无效: 从不是创建控件“Control Name'”的线程访问它问题的解决方案及原理分析
- WIN7不能被远程桌面问题
- JavaSE_13th_对象转型(casting)
- 一款基于ssd1306驱动的12864屏简介
- Buff系统
