A customer is running ZFS version15 on Solaris SPARC 10/08 supporting Oracle 10.2.0.3 databases in a dev and production test environment. We have come across some cache inconsistencies with one of the Oracle databases where fetching a record displays a 'historical value' (that has been changed and committed many times). This is an isolated occurance and is not always consistent. I can't replicate it to other tables. I'll also be posting a note to the ZFS discussion list.
Is it possible for a read to bybpass the write cache and fetch from disk before the flush of the cache to disk occurs? This is a large system that is infrequently busy. The Oracle SGA size is minimized to 1GB per instance and we rely more on the ZFS cache, allowing us to fit 'more instances' (many of which are cloned snapshots). We've been running this setup for 2 years. The filesystems are set with compression on, blocksize 8k for oracle datafiles, 128k for redologs. Here are the details of the scenerio: 1. Update statement re-setting existing value. At this point the previous value was actually set to -643 prior to the update. It was originally set to 3 before today's session: SQL> update [name deleted] set status_cd = 1 where id = 65; 1 row updated. SQL> commit; Commit complete. SQL> select rowid, id, status_cd from [table name deleted] SQL> where id = 65; ROWID ID STATUS_CD ------------------ ------------------ ---------- AAAq/DAAAAAAERlAAM 65 3 Note that when retrieved the status_cd reverts to the old original value of 3, not the previous value of -643. 2. Oracle trace file proves that the update was issued and committed: ===================== PARSING IN CURSOR #1 len=70 dep=0 uid=110 oct=6 lid=110 tim=17554807027344 hv=3512595279 ad='fd211878' update [table deleted] set status_cd = 1 where id = 65 END OF STMT PARSE #1:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554807027340 BINDS #1: EXEC #1:c=0,e=257,p=0,cr=3,cu=3,mis=0,r=1,dep=0,og=2,tim=17554807027737 WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554807027803 WAIT #1: nam='SQL*Net message from client' ela= 2999139 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554810026992 STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE [TABLE DELETED] (cr=3 pr=0 pw=0 time=144 us)' STAT #1 id=2 cnt=1 pid=1 pos=1 obj=177738 op='INDEX UNIQUE SCAN [TABLE_DELETED]_XPK (cr=3 pr=0 pw=0 time=19 us)' PARSE #2:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=17554810027367 XCTEND rlbk=0, rd_only=0 EXEC #2:c=0,e=226,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,tim=17554810027630 WAIT #2: nam='log file sync' ela= 833 buffer#=9408 p2=0 p3=0 obj#=-1 tim=17554810028507 WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554810028578 WAIT #2: nam='SQL*Net message from client' ela= 1825185 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811853812 ===================== PARSING IN CURSOR #1 len=67 dep=0 uid=110 oct=3 lid=110 tim=17554811854015 hv=1593702413 ad='fd713640' select status_cd from [table_deleted] where id = 65 END OF STMT PARSE #1:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554811854010 BINDS #1: EXEC #1:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554811854273 WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811854327 FETCH #1:c=0,e=64,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=2,tim=17554811854436 WAIT #1: nam='SQL*Net message from client' ela= 780 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811855291 FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=17554811855331 WAIT #1: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811855366 There are no Oracle or Solaris error messages indicating any issue with this update. Haas anyone seen this behavoir? The features of ZFS (snapshots/clones/compression) save us a ton of time on this platform and we have certainly benefited from it. Just want to understand how something like this could occur and determine how we can prevent it in the future. ========== Gerry Bragg Sr. Developer Altarum Institute (734) 516-0825 gerry.br...@altarum.org<mailto:gerry.br...@altarum.org> www.altarum.org<http://www.altarum.org/> "Systems Research For Better Health"
_______________________________________________ zfs-discuss mailing list zfs-discuss@opensolaris.org http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
