FGA导致在线重定义报ORA-42008，ORA-12015错误
May112015
下面是在11.2.0.4遇到在线重定义报错，信息如下：
|
1，报错代码 |
exec DBMS_REDEFINITION.START_REDEF_TABLE('HTZ','HTZ','HTZ_RED');
ERROR
-----------------------
ORA-42008: error occurred while instantiating the redefinition
ORA-12015: cannot create a fast refresh materialized view from a complex query
ORA-6512: at "SYS.DBMS_REDEFINITION", line 52
ORA-6512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-6512: at line 2
这里看到START_REDEF_TABLE报错，但是在CAN_REDEF_TABLE都没有报任何的错误。
www.htz.pw > EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('HTZ','HTZ',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
|
2，配置EVENT 10046,ERRORSTACK |
oradebug setmypid oradebug event 10046 trace name context forever,level 12; oradebug event 12015 trace name errorstack level 12;
|
3，分析trace文件 |
*** 2015-05-05 22:58:52.183
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=12, mask=0x0)
----- Error Stack Dump -----
ORA-12015: cannot create a fast refresh materialized view from a complex query
----- Current SQL Statement for this session (sql_id=7fq15yav7sx45) -----
create snapshot "HPAY"."USER_KEY_MID" on prebuilt table with reduced precision refresh fast with primary key as select * from "HPAY"."USER_KEY" "USER_KEY"
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x3a3433408 56 package body SYS.DBMS_REDEFINITION
0x3a3433408 1498 package body SYS.DBMS_REDEFINITION
0x3d05a9f88 1 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+41 call kgdsdst() 000000000 ? 000000000 ?
7FFF77A069C0 ? 7FFF77A06A98 ?
7FFF77A0B540 ? 000000002 ?
ksedst1()+103 call skdstdst() 000000000 ? 000000000 ?
7FFF77A069C0 ? 7FFF77A06A98 ?
7FFF77A0B540 ? 000000002 ?
ksedst()+39 call ksedst1() 000000000 ? 000000001 ?
7FFF77A069C0 ? 7FFF77A06A98 ?
7FFF77A0B540 ? 000000002 ?
dbkedDefDump()+2746 call ksedst() 000000000 ? 000000001 ?
7FFF77A069C0 ? 7FFF77A06A98 ?
7FFF77A0B540 ? 000000002 ?
ksedmp()+41 call dbkedDefDump() 00000000C ? 000000000 ?
7FFF77A069C0 ? 7FFF77A06A98 ?
7FFF77A0B540 ? 000000002 ?
dbkdaKsdActDriver() call ksedmp() 00000000C ? 000000000 ?
+1960 7FFF77A069C0 ? 7FFF77A06A98 ?
7FFF77A0B540 ? 000000002 ?
dbgdaExecuteAction( call dbkdaKsdActDriver() 7F993BD28730 ? 7FFF77A0DC40 ?
)+1065 7FFF77A069C0 ? 7FFF77A06A98 ?
7FFF77A0B540 ? 000000002 ?
dbgdaRunAction()+81 call dbgdaExecuteAction( 7F993BD28730 ? 00A600760 ?
5 ) 0020C0003 ? 7FFF77A0DC40 ?
000000001 ? 000000002 ?
dbgdRunActions()+64 call dbgdaRunAction() 7F993BD28730 ? 000000005 ?
0020C0003 ? 7FFF77A0DC40 ?
000000001 ? 000000002 ?
dbgdProcessEventAct call dbgdRunActions() 7F993BD28730 ? 000000005 ?
ions()+656 0020C0003 ? 7FFF77A0DC40 ?
000000001 ? 000000002 ?
dbgdChkEventKgErr() call dbgdProcessEventAct 7F993BD28730 ? 00C0CC9E0 ?
+1658 ions() 7F993B399AC0 ? 7FFF77A0DC40 ?
000000001 ? 000000002 ?
dbkdChkEventRdbmsEr call dbgdChkEventKgErr() 7F993BD28730 ? 00C0CC9E0 ?
r()+61 7F993B399AC0 ? 7FFF77A0DC40 ?
000000001 ? 000000002 ?
ksfpec()+66 call dbkdChkEventRdbmsEr 7F993BD28730 ? 00C0CC9E0 ?
r() 7F993B399AC0 ? 7FFF77A0DC40 ?
000000001 ? 000000002 ?
dbgePostErrorKGE()+ call ksfpec() 7F993BD28730 ? 00C0CC9E0 ?
1137 7F993B399AC0 ? 7FFF77A0DC40 ?
000000001 ? 000000002 ?
dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 00C0CC9E0 ? 7F993BD35BD0 ?
71 000002EEF ? 7FFF77A0DC40 ?
000000001 ? 000000002 ?
kgeselv()+276 call dbkePostKGE_kgsf() 00C0CC9E0 ? 7F993BD35BD0 ?
000002EEF ? 7FFF77A0DC40 ?
000000001 ? 000000002 ?
ksesecl0()+162 call kgeselv() 00C0CC9E0 ? 7F993BD35BD0 ?
000002EEF ? 7FFF77A0DC40 ?
000000001 ? 000000000 ?
kkzsval()+745 call ksesecl0() 00C0CC9E0 ? 7F993BD35BD0 ?
000002EEF ? 000000000 ?
000000000 ?
1000000000000000 ?
kkzcsn()+1415 call kkzsval() 3A16ADE38 ? 7FFF77A0F050 ?
7FFF77A0F1A8 ? 7FFF77A0F030 ?
7FFF77A0F1A4 ? 7FFF77A0F19C ?
opiexe()+21292 call kkzcsn() 3A16ADE38 ? 7FFF77A0F050 ?
7FFF77A0F1A8 ? 7FFF77A0F030 ?
7FFF77A0F1A4 ? 7FFF77A0F19C ?
opiosq0()+3932 call opiexe() 000000004 ? 000000000 ?
7FFF77A0F1A8 ? 7FFF77A0F030 ?
7FFF77A0F1A4 ? 7FFF77A0F19C ?
opiall0()+4834 call opiosq0() 000000003 ? 00000000E ?
7FFF77A10AF0 ? 000000020 ?
7FFF77A0F1A4 ? 7FFF77A0F19C ?
opikpr()+524 call opiall0() 000000065 ? 000000022 ?
7FFF77A11278 ? 000000000 ?
7FFF77A0F1A4 ? 7FFF77A0F19C ?
opiodr()+917 call opikpr() 000000065 ? 00000001C ?
7FFF77A129A8 ? 000000000 ?
7FFF77A0F1A4 ? 7FFF77A0F19C ?
rpidrus()+211 call opiodr() 000000065 ? 00000001C ?
7FFF77A129A8 ? 000000000 ?
7FFF77A0F1A4 ? 7FFF77A0F19C ?
skgmstack()+148 call rpidrus() 7FFF77A12588 ? 00000001C ?
7FFF77A129A8 ? 000000000 ?
7FFF77A0F1A4 ? 7FFF77A0F19C ?
rpiswu2()+690 call skgmstack() 7FFF77A12560 ? 00C0CC600 ?
00000F618 ? 009698B1C ?
7FFF77A12588 ? 7FFF77A0F19C ?
kprball()+1232 call rpiswu2() 3CED42700 ? 000000000 ?
7FFF77A128B0 ? 000000002 ?
7FFF77A12588 ? 7FFF77A0F19C ?
kkzucpmv()+1747 call kprball() 7FFF77A129A8 ? 000000100 ?
7FFF77A128B0 ? 000000002 ?
7FFF77A12588 ? 7FFF77A0F19C ?
kkzusord()+8098 call kkzucpmv() 7F993B4D8B54 ? 000000004 ?
7F993B4D89DC ? 000000002 ?
7FFF77A12588 ? 7FFF77A0F19C ?
kkzisord()+862 call kkzusord() 000000001 ? 7F993B7FFCA8 ?
7F993B7FFCB0 ? 7F993B7FFCB8 ?
7FFF77A12588 ? 7FFF77A0F19C ?
spefcmpa()+874 call kkzisord() 000000001 ? 7F993B7FFCA8 ?
7F993B7FFCB0 ? 7F993B7FFCB8 ?
7FFF77A12588 ? 7F993B4D8298 ?
spefmccallstd()+235 call spefcmpa() 7FFF77A17C40 ? 00000000E ?
7F993BB17FD8 ? 7F993B7FFCB8 ?
7FFF77A12588 ? 7F993B4D8298 ?
pextproc()+41 call spefmccallstd() 7FFF77A18040 ? 7FFF77A17CE8 ?
7FFF77A17A00 ? 7FFF77A17C40 ?
000000000 ? 7F993B4D8298 ?
peftrusted()+150 call pextproc() 7FFF77A18040 ? 7FFF77A17CE8 ?
7FFF77A17A00 ? 7FFF77A17C40 ?
000000000 ? 7F993B4D8298 ?
psdexsp()+255 call peftrusted() 7FFF77A18040 ? 7FFF77A17CE8 ?
7FFF77A17A00 ? 7FFF77A17C40 ?
000000000 ? 7F993B4D8298 ?
rpiswu2()+1776 call psdexsp() 7FFF77A17820 ? 7FFF77A17CE8 ?
7FFF77A17A00 ? 7FFF77A17C40 ?
000000000 ? 7F993B4D8298 ?
psdextp()+700 call rpiswu2() 3CED0E820 ? 000000000 ?
3CED0E8A4 ? 000000002 ?
000000000 ? 7F993B4D8298 ?
pefccal()+726 call psdextp() 7FFF77A19080 ? 7FFF77A17CE8 ?
7FFF77A17A00 ? 000000000 ?
000020003 ? 7FFF77A18040 ?
pefcal()+224 call pefccal() 7FFF77A19080 ? 7FFF77A17CE8 ?
7FFF77A17A00 ? 000000000 ?
000020003 ? 7FFF77A18040 ?
pevm_FCAL()+169 call pefcal() 7FFF77A18040 ? 7FFF77A17CE8 ?
7FFF77A17A00 ? 000000000 ?
000020003 ? 7FFF77A18040 ?
pfrinstr_FCAL()+75 call pevm_FCAL() 7F993B849720 ? 39E917000 ?
7FFF77A17A00 ? 39E916E00 ?
000020003 ? 7FFF77A18040 ?
pfrrun_no_tool()+63 call pfrinstr_FCAL() 7F993B849720 ? 39E91BD12 ?
7F993B849790 ? 39E916E00 ?
000020003 ? 7FFF77A18040 ?
pfrrun()+627 call pfrrun_no_tool() 7F993B849720 ? 39E91BD12 ?
7F993B849790 ? 39E916E00 ?
000020003 ? 7FFF77A18040 ?
plsql_run()+649 call pfrrun() 7F993B849720 ? 39E91BD12 ?
7F993B849790 ? 7FFF77A19080 ?
000020003 ? 7FFF77A18040 ?
peicnt()+302 call plsql_run() 7F993B849720 ? 39E91BD12 ?
7F993B849790 ? 7FFF77A19080 ?
000020003 ? 7FFF77A18040 ?
kkxexe()+525 call peicnt() 7FFF77A19080 ? 7F993B849720 ?
7F993B849790 ? 7FFF77A19080 ?
000020003 ? 000000000 ?
opiexe()+18001 call kkxexe() 7F993B8280F0 ? 7F993B849720 ?
7F993B849790 ? 7FFF77A19080 ?
000020003 ? 000000000 ?
kpoal8()+2118 call opiexe() 000000049 ? 000000003 ?
7F993B849790 ? 7FFF77A19080 ?
000020003 ? 000000000 ?
opiodr()+917 call kpoal8() 00000005E ? 000000003 ?
7F993B849790 ? 7FFF77A19080 ?
000020003 ? 000000000 ?
*** 2015-05-05 22:58:52.787
ttcpip()+2183 call opiodr() 00000005E ? 00000001C ?
7FFF77A1D8B0 ? 7FFF77A19080 ?
000020003 ? 000000000 ?
opitsk()+1710 call ttcpip() 00C0EA630 ? 00999BB10 ?
7FFF77A1D8B0 ? 000000000 ?
7FFF77A1D308 ? 7FFF77A1D8AC ?
opiino()+969 call opitsk() 00C0EA638 ? 000000000 ?
7FFF77A1D8B0 ? 000000000 ?
7FFF77A1D308 ? 7FFF77A1D8AC ?
opiodr()+917 call opiino() 00000003C ? 000000004 ?
7FFF77A1F0A8 ? 000000000 ?
7FFF77A1D308 ? 7FFF77A1D8AC ?
opidrv()+570 call opiodr() 00000003C ? 000000004 ?
7FFF77A1F0A8 ? 000000000 ?
7FFF77A1D308 ? 7FFF77A1D8AC ?
sou2o()+103 call opidrv() 00000003C ? 000000004 ?
7FFF77A1F0A8 ? 000000000 ?
7FFF77A1D308 ? 7FFF77A1D8AC ?
opimai_real()+133 call sou2o() 7FFF77A1F080 ? 00000003C ?
000000004 ? 7FFF77A1F0A8 ?
7FFF77A1D308 ? 7FFF77A1D8AC ?
ssthrdmain()+265 call opimai_real() 000000002 ? 7FFF77A1F270 ?
000000004 ? 7FFF77A1F0A8 ?
7FFF77A1D308 ? 7FFF77A1D8AC ?
main()+201 call ssthrdmain() 000000002 ? 7FFF77A1F270 ?
000000001 ? 000000000 ?
7FFF77A1D308 ? 7FFF77A1D8AC ?
__libc_start_main() call main() 000000002 ? 7FFF77A1F418 ?
+253 000000001 ? 000000000 ?
7FFF77A1D308 ? 7FFF77A1D8AC ?
_start()+41 call __libc_start_main() 000A29134 ? 000000002 ?
7FFF77A1F408 ? 000000000 ?
7FFF77A1D308 ? 7FFF77A1D8AC ?
|
4 故障处理 |
exec dbms_fga.disable_policy(object_schema=>'HTZ', object_name=> 'HTZ', policy_name=> 'HTZ');
取消表上面的FGA后，再次执行START正常。
下面来看一下FGA的内容
OBJECT_SCHEMA OBJECT_NAME POLICY_OWNER POLICY_NAME POLICY_TEXT POLICY_COLUMN PF_SCHEMA PF_PACKAGE PF_FUNCTION ENABLED SEL INS UPD DEL AUDIT_TRAIL POLICY_COLUMN_OPTIONS
1 HTZ HTZ HTZ HTZ "SYS_CONTEXT('USERENV','CURRENT_USER') not in ('RA','RCONTROL','SMS','HPAY','LOG','APM','JERRY','BITEL')" YES YES NO NO NO DB+EXTENDED ANY_COLUMNS
本文固定链接: http://www.htz.pw/?p=973 | 认真就输
最新评论
K.I.S.S.LANG:
来过，总要留下点什么brokensword:
非常感谢博主的分享，不知博主可以冰冻心灵:
主库使用ALTER DATABAcsdw:
是 1031
哈哈,,黄大师,总结得好huangtingzhong:
Loading Hinted E
下面是ASM磁盘头的部分ASM
RMAN backup to F
Database Crashed
How to Resolve O
最活跃的读者