Oracle 11.2.0.3.0 on Oracle Data Appliance.
Hi,
We have 12 dev/test databases running on an ODA. Each database gets a daily RMAN hot backup and a full database export.
One of the databases started reporting that the daily export was failing with
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.SYSTEM_INFO_EXP(0,dynconnect,'11.02.00.03.00',newblock)
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 101
ORA-06512: at "WMSYS.LT_CTX_PKG", line 2116
ORA-06512: at "WMSYS.LT_EXPORT_PKG", line 1515
I spent some time searching on Google and Oracle Support and found a note which suggested as part of investigation
to
Select *
from dba_registry;
When I tried this I got
SQL> select *
2 from dba_registry;
from dba_registry
*
ERROR at line 2:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 101
ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 1885
ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 1893
ORA-06512: at "SYS.DBMS_REGISTRY", line 1810
Interestingly if I execute the statement again it works. If I log out of SQL*PLus and back in again
the select statement fails for the first execution again.
I then found :
https://community.oracle.com/thread/3732253?start=0&tstart=0
Frustratingly there was no resolution but I followed the suggestions in the thread anyway.
Particularly I ran
SQL>@?/rdbms/admin/prvtcr.plb;
SQL>@?/rdbms/admin/utlrp.sql
After which the select from dba_registry works. Then when I log out and back in again I get the same error.
I have bounced the database and also tried a sql trace on the session which I will include below.
I have logged a service request but it's slow progress (it's at P3 at the moment) and I was wondering if anyone on here had any
ideas as to what to try. Which would be very much appreciated.
Regards,
Stuart
SQL Trace:
*** 2016-08-01 10:52:46.385
*** SESSION ID:(10.2839) 2016-08-01 10:52:46.385
*** CLIENT ID:() 2016-08-01 10:52:46.385
*** SERVICE NAME:(SYS$USERS) 2016-08-01 10:52:46.385
*** MODULE NAME:(sqlplus@kfoda2-tst-nd1 (TNS V1-V3)) 2016-08-01 10:52:46.385
*** ACTION NAME:() 2016-08-01 10:52:46.385
WAIT #140011546571496: nam='library cache lock' ela= 519 handle address=2839337184 lock address=2846075688 100*mode+namespace=26298084818946 obj#=-1 tim=1470045166385555
WAIT #140011546571496: nam='library cache pin' ela= 249 handle address=2839337184 pin address=2846075432 100*mode+namespace=26298084818946 obj#=-1 tim=1470045166386118
=====================
PARSING IN CURSOR #140011546570616 len=37 dep=1 uid=0 oct=3 lid=0 tim=1470045166386298 hv=1398610540 ad='bc23dec0' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #140011546570616:c=1000,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1470045166386295
BINDS #140011546570616:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=7f56fa7f6b38 bln=16 avl=16 flg=05
value=0000269F.0008.0001
EXEC #140011546570616:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1470045166386491
WAIT #140011546570616: nam='Disk file operations I/O' ela= 213 FileOperation=2 fileno=1 filetype=2 obj#=-1 tim=1470045166386771
WAIT #140011546570616: nam='Disk file operations I/O' ela= 2955 FileOperation=2 fileno=0 filetype=15 obj#=69 tim=1470045166389898
WAIT #140011546570616: nam='db file sequential read' ela= 3513 file#=1 block#=9887 blocks=1 obj#=69 tim=1470045166393647
FETCH #140011546570616:c=1000,e=7478,p=1,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1470045166393987
STAT #140011546570616 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=1 pw=0 time=7465 us cost=1 size=15 card=1)'
CLOSE #140011546570616:c=0,e=11065,dep=1,type=0,tim=1470045166405081
WAIT #140011546571496: nam='row cache lock' ela= 157 cache id=16 mode=0 request=3 obj#=-1 tim=1470045166409268
=====================
PARSING IN CURSOR #140011546567904 len=210 dep=1 uid=0 oct=3 lid=0 tim=1470045166409676 hv=864012087 ad='bc132460' sqlid='96g93hntrzjtr'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #140011546567904:c=0,e=357,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=0,tim=1470045166409675
BINDS #140011546567904:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f56fa7f4810 bln=22 avl=03 flg=05
value=1281
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f56fa7f4828 bln=22 avl=02 flg=01
value=2
EXEC #140011546567904:c=1000,e=850,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=2239883476,tim=1470045166410593
WAIT #140011546567904: nam='gc cr grant 2-way' ela= 516 p1=1 p2=32164 p3=1 obj#=427 tim=1470045166411252
WAIT #140011546567904: nam='Disk file operations I/O' ela= 17 FileOperation=2 fileno=0 filetype=15 obj#=427 tim=1470045166411327
WAIT #140011546567904: nam='db file sequential read' ela= 6871 file#=1 block#=32164 blocks=1 obj#=427 tim=1470045166418216
WAIT #140011546567904: nam='Disk file operations I/O' ela= 12 FileOperation=2 fileno=0 filetype=15 obj#=425 tim=1470045166418312
WAIT #140011546567904: nam='db file sequential read' ela= 2320 file#=1 block#=2629 blocks=1 obj#=425 tim=1470045166420668
FETCH #140011546567904:c=0,e=10101,p=2,cr=3,cu=0,mis=0,r=1,dep=1,og=3,plh=2239883476,tim=1470045166420706
STAT #140011546567904 id=1 cnt=1 pid=0 pos=1 obj=425 op='TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=2 pw=0 time=10112 us)'
STAT #140011546567904 id=2 cnt=1 pid=1 pos=1 obj=427 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=1 pw=0 time=7667 us)'
CLOSE #140011546567904:c=0,e=34,dep=1,type=3,tim=1470045166420772
WAIT #140011546571496: nam='row cache lock' ela= 49 cache id=16 mode=0 request=3 obj#=-1 tim=1470045166420860
BINDS #140011546567904:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f56fa7f4810 bln=22 avl=03 flg=05
value=1281
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f56fa7f4828 bln=22 avl=02 flg=01
value=5
EXEC #140011546567904:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1470045166420986
FETCH #140011546567904:c=0,e=11,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=3,plh=2239883476,tim=1470045166421018
CLOSE #140011546567904:c=0,e=5,dep=1,type=3,tim=1470045166421047
WAIT #140011546571496: nam='row cache lock' ela= 399 cache id=16 mode=0 request=3 obj#=-1 tim=1470045166421479
BINDS #140011546567904:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f56fa7f4810 bln=22 avl=03 flg=05
value=1281
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f56fa7f4828 bln=22 avl=02 flg=01
value=7
EXEC #140011546567904:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1470045166421575
FETCH #140011546567904:c=0,e=12,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=3,plh=2239883476,tim=1470045166421598
CLOSE #140011546567904:c=0,e=6,dep=1,type=3,tim=1470045166421625
WAIT #140011546571496: nam='row cache lock' ela= 52 cache id=16 mode=0 request=3 obj#=-1 tim=1470045166421703
BINDS #140011546567904:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f56fa7f4810 bln=22 avl=03 flg=05
value=1281
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f56fa7f4828 bln=22 avl=02 flg=01
value=8
EXEC #140011546567904:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1470045166421807
FETCH #140011546567904:c=0,e=9,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=3,plh=2239883476,tim=1470045166421826
CLOSE #140011546567904:c=0,e=6,dep=1,type=3,tim=1470045166421846
WAIT #140011546571496: nam='row cache lock' ela= 395 cache id=16 mode=0 request=3 obj#=-1 tim=1470045166422266
BINDS #140011546567904:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f56fa7f4810 bln=22 avl=03 flg=05
value=1281
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f56fa7f4828 bln=22 avl=02 flg=01
value=9
EXEC #140011546567904:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1470045166422349
FETCH #140011546567904:c=0,e=10,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=3,plh=2239883476,tim=1470045166422369
CLOSE #140011546567904:c=0,e=6,dep=1,type=3,tim=1470045166422388
WAIT #140011546571496: nam='row cache lock' ela= 74 cache id=16 mode=0 request=3 obj#=-1 tim=1470045166422488
BINDS #140011546567904:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f56fa7f4810 bln=22 avl=03 flg=05
value=1281
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f56fa7f4828 bln=22 avl=02 flg=01
value=11
EXEC #140011546567904:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1470045166422588
WAIT #140011546567904: nam='db file sequential read' ela= 122 file#=1 block#=2630 blocks=1 obj#=425 tim=1470045166422757
FETCH #140011546567904:c=0,e=182,p=1,cr=3,cu=0,mis=0,r=1,dep=1,og=3,plh=2239883476,tim=1470045166422789
CLOSE #140011546567904:c=0,e=5,dep=1,type=3,tim=1470045166422812
=====================
PARSING IN CURSOR #140011546571496 len=26 dep=0 uid=0 oct=3 lid=0 tim=1470045166423857 hv=993150485 ad='a93cf330' sqlid='a5ak6vwxm4jhp'
select *
from dba_registry
END OF STMT
PARSE #140011546571496:c=11000,e=39282,p=4,cr=20,cu=0,mis=1,r=0,dep=0,og=1,plh=4040086236,tim=1470045166423857
EXEC #140011546571496:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4040086236,tim=1470045166423946
WAIT #140011546571496: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1470045166424018
FETCH #140011546571496:c=1999,e=1323,p=0,cr=152,cu=0,mis=0,r=0,dep=0,og=1,plh=4040086236,tim=1470045166425374
STAT #140011546571496 id=1 cnt=1 pid=0 pos=1 obj=0 op='HASH JOIN (cr=152 pr=0 pw=0 time=713 us cost=12 size=2622 card=19)'
STAT #140011546571496 id=2 cnt=19 pid=1 pos=1 obj=0 op='MERGE JOIN (cr=149 pr=0 pw=0 time=148 us cost=6 size=2280 card=19)'
STAT #140011546571496 id=3 cnt=126 pid=2 pos=1 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=147 pr=0 pw=0 time=519 us cost=3 size=3132 card=174)'
STAT #140011546571496 id=4 cnt=146 pid=3 pos=1 obj=11 op='INDEX FULL SCAN I_USER# (cr=1 pr=0 pw=0 time=156 us cost=1 size=0 card=1)'
STAT #140011546571496 id=5 cnt=19 pid=2 pos=2 obj=0 op='SORT JOIN (cr=2 pr=0 pw=0 time=174 us cost=3 size=1938 card=19)'
STAT #140011546571496 id=6 cnt=19 pid=5 pos=1 obj=1281 op='TABLE ACCESS FULL REGISTRY$ (cr=2 pr=0 pw=0 time=59 us cost=2 size=1938 card=19)'
STAT #140011546571496 id=7 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS FULL USER$ (cr=3 pr=0 pw=0 time=18 us cost=5 size=3132 card=174)'
WAIT #140011546571496: nam='SQL*Net break/reset to client' ela= 12 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1470045166425564
WAIT #140011546571496: nam='SQL*Net break/reset to client' ela= 63 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1470045166425639
*** 2016-08-01 10:52:56.344
WAIT #140011546571496: nam='SQL*Net message from client' ela= 9918795 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1470045176344476