sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-42008 ORA-12015原因和MOS官方解决办法

时间:2016-11-07 09:31   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库出现问题,分析原因是FGA导致在线重定义报ORA-42008,ORA-12015错误。
下面是在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都没有报任何的错误。

oracleplus.net>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 
                           http://www.oracleplus.net                        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/2015/05/11/fga%e5%af%bc%e8%87%b4%e5%9c%a8%e7%ba%bf%e9%87%8d%e5%ae%9a%e4%b9%89%e6%8a%a5ora-42008%ef%bc%8cora-12015%e9%94%99%e8%af%af.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-42008 ORA-12015原因和MOS官方解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1194.html

Oracle研究中心

关键词:

ORA-42008解决笔记

ORA-12015解决笔记

FGA导致在线重定义报ORA-42008 ORA-12015错误