Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Treasure Trove of Oracle Security Documents

This morning I stumbled across a veritable treasure trove of Oracle Security documents at the web site of the Defense Information Systems Agency.

I've only spent a few minutes glancing through a small sampling of them, and they appear to be fairly comprehensive. When you consider that these are documents used to audit sensitive database installations, it makes a lot of sense that they would cross all the t's and dot all the i's.

The index of documents can be found here: DISA IT Security Documents

As you can see for yourself, there are documents covering security concerns for a number of other systems.

Tracing VPD Predicates

Even though a number of articles and blog posts have already been written on this topic (e.g. on Pete Finnigan’s site I found references dating back from 2003), from time to time I’m still asked “How to trace predicates generated by VPD?”. Hence, here’s yet another blog post about this topic…
Let’s setup the scene before [...]

Part of the Puzzle: Oracle XMLDB NFS Functionality

This story is long overdue and no its NOT about the Oracle Database 11g Database File System (DBFS). Its about an “undocumented” NFS functionality that, maybe someday, will be serviced by the XMLDB XDB Protocol Adapter. This post is “long overdue” because the actual attempts to try to figure it out were done during the bank holidays between X-mas and new year 2009.

So what is it all about. I once discovered in the Oracle 11gR1 documentation a small entry in the xmlconfig.xsd XML Schema regarding NFS elements that look like that they are or will be used for enabling NFS functionality based on the Oracle XMLDB Protocol Server architecture. In those days, when Oracle 11gR1 was just of the shelve, I made a few attempts, based on the xdbconfig.xsd XML Schema to adjust the corresponding xdbconfig.xml file that controls the XDB Protocol Server functionality, to see what would happen. At that time I only was able to get this far (see the picture) and I promised myself that I should look deeper into it trying to figure out if I could get it working and/or what the concepts were that made it tick in the XMLDB architecture but somewhere down the line I just didn’t come to it and it got “forgotten” by me due to my daily DBA workload.

NFS Protocol Server functionality enabled manually

Click picture to enlarge

The XDB Protocol Server

XML was/is a lot about interfacing. Oracle has from the start with the XMLDB functionality, officially supported since database version 9.2.0.3.0, implemented an enormous amount of W3C and other XML standards. One of this was the need to have a repository. The XDB Protocol Server provides easy access to this repository and on the side opens up database content and functionality you maybe not even aware off. The XDB Protocol Server adapter is build on a very old (Oracle 7) SQL*Net architecture once called “The Multithreaded Server” architecture (MTS) or as it is known nowadays The Shared Server architecture. This architecture was introduced in those Oracle 7 days to minimize the memory resources of dedicated SQL*Net connections of the corresponding server processes in a decade that memory was still incredible expensive.

Click picture to enlarge

You can control the functionality of this XDB Protocol Server adapter architecture on three levels:

  • Via the Listener: the listener.ora and other related configuration files
  • Via the Database: by setting database parameters via the pfile or spfile database configuration file(s)
  • Via the Protocol Server adapter: xdbconfig.xml configuration file

In its behavior and possibilities the xdbconfig.xml file has a lot in its look and feel like the Apache httpd.conf file or a server.xml file of an Application Server. The configuration defines for example MIME types, memory and locking behavior, configuration of “servlets” (naming probably derived from the WebDAV definitions) like HTTP, FTP, Native Database Web Services (NDWS), PL/SQL Gateways like local APEX support, security, etc., etc.

Click picture to enlarge

The xdbconfig.xml file is restricted by one of the by Oracle defined XML Schemata called “xdbconfig.xsd” and you will need the XDBADMIN database role to alter its content. These schemata are located in the “sys” directory of the XDB Repository. Be aware that you should treat this “sys” directory (or so called “resource” in WebDAV terms) as if it were the SYS user schema in the database. Oracle will uphold its right to alter the structures in this directory as it pleases, also regarding the (ACL) security, versioning etc. So never use this “sys” directory/resource or sub-directories as a placeholder for you application files or resources.

Click picture to enlarge

So within the limits of the xdbconfig.xsd, you could add you own specific protocol server needs in terms of extra PL/SQL gateway definitions or adding MIME types, so you would be able to trigger a by you defined method to act on a certain file extension or file extension content. The xdbconfig.xml file which is actually a record in a table called XDB$CONFIG is protected by Access Control Lists (ACL), underneath enforced by Oracle’s VPD methods. Due to the fact that Oracle is using VPD internally to make this work, to enforce ACL security, this part is free of license as is Oracle XMLDB functionality by the way.

Click picture to enlarge

Besides acting as a web server (HTTP/HTTPS), Oracle build in, by default, FTP access (FTP Server functionality), the mentioned Native Database Web Service (the database acts as a web service), and support for its own applications (APEX, a servlet called “ReportFmwkServlet” for DB Console / OEM reporting) and the “DBURI” servlet, which makes it possible to query (via XPath) and report database table content.

These so called Servlets can make use of Java, PL/SQL or C interfacing methods. The C related servlets, like the NDWS, DBURI and “ReportFmwkServlet” hook directly into the Oracle kernel and are, regarding its internal workings, only known to Oracle and not further documented. That is, the only thing that is described, is how they can be enabled and how they should be used.

But there is more.

Another cool feature is that XDB Repository actions/events can be triggering by an Oracle defined action/method (e.g. shredding XML data directly into a corresponding table after being FTP-ed or drag/dropped by WebDAV in the XDB Repository) or, as of Oracle 11gR1, user actions/methods. The latter is called “Repository Events“. Repository Events are events which get triggered via defined methods on the Repository. These XDB Repository Events need defining an “Event Listener“, an “Event Handler” and a “Event Configuration” file, which hooks everything together.

Be aware that a Repository Event is NOT a database trigger. Also a repository operation can be associated with one or more repository events. To name a few: creating, deleting, locking, unlocking, placing under version control, checking in, checking out, unchecking out, opening, and/or updating a resource (aka a file or folder).

If you need more info and/or examples, have a look at the “XFiles” XMLDB Demo Application that demonstrates, among others, lightweight content management functionality, NDWS and more.

Trying to figure out XDB NFS Functionality

Now that you have an overview, where does this XDB NFS kickin in all of this? In the current latest release, Oracle 11gR2, the existence of this XDB NFS functionality can be found via accessing the configuration web pages of the local DB Console. In the DB Console server menu tab, you can find the XDB Configuration URL and after clicking it you will see the following page in Oracle 11gR2.

Click picture to enlarge

Clicking on the “ENABLE” button will bring you in the “exports” and NFS port configuration file.

Click picture to enlarge

After configuring and exporting, for example, the /public and /home of the WebDAV / XDB Repository directories and its resources (aka files) and clicking on the “OK” button, the xdbconfig.xml configuration file will have been updated and the listener will show a status that has an extra entry for the servicing the NFS protocol. IN 2007 I did configure the NFS entries by hand based on the xmlconfig.xsd XML Schema. The DB Console pages will do this for you.

Trying to figure out how all elements hooked in each other I started searching and tracing from the inside out (database, protocol server, listener, OS) or at least described here in “reverse” order.

The Database Part

I am guessing that the NFS XDB functionality probably hooks into a C kernel based “servlet” somehow. At least part of it already can be controlled my the xdbconfig.xml file and the user XDB has a table called XDB$ROOT_INFO that supports NFS entries.

SQL#66cc66;">> sho user
 
USER #993333; font-weight: bold;">IS #ff0000;">"XDB"
 
SQL#66cc66;">> #993333; font-weight: bold;">DESC XDB$ROOT_INFO
 
 Name                                      #993333; font-weight: bold;">NULL?    Type
 #808080; font-style: italic;">----------------------------------------- -------- ----------------------------
 RESOURCE_ROOT                                      ROWID
 RCLIST                                             RAW#66cc66;">(#cc66cc;">2000#66cc66;">)
 FTP_PORT                                           NUMBER#66cc66;">(#cc66cc;">5#66cc66;">)
 FTP_PROTOCOL                                       VARCHAR2#66cc66;">(#cc66cc;">4000#66cc66;">)
 HTTP_PORT                                          NUMBER#66cc66;">(#cc66cc;">5#66cc66;">)
 HTTP_PROTOCOL                                      VARCHAR2#66cc66;">(#cc66cc;">4000#66cc66;">)
 HTTP_HOST                                          VARCHAR2#66cc66;">(#cc66cc;">4000#66cc66;">)
 HTTP2_PORT                                         NUMBER#66cc66;">(#cc66cc;">5#66cc66;">)
 HTTP2_PROTOCOL                                     VARCHAR2#66cc66;">(#cc66cc;">4000#66cc66;">)
 HTTP2_HOST                                         VARCHAR2#66cc66;">(#cc66cc;">4000#66cc66;">)
 NFS_PORT                                           NUMBER#66cc66;">(#cc66cc;">5#66cc66;">)
 NFS_PROTOCOL                                       VARCHAR2#66cc66;">(#cc66cc;">4000#66cc66;">)

Other semi build in table structures that can be found in the XDB user schema or the 11gR2 database that are or that probably are XMLDB NFS related:

SQL#66cc66;">> #993333; font-weight: bold;">DESC XDB$CONFIG
 
 Name                                      #993333; font-weight: bold;">NULL?    Type
 #808080; font-style: italic;">----------------------------------------- -------- ----------------------------
#993333; font-weight: bold;">TABLE of SYS#66cc66;">.XMLTYPE#66cc66;">(XMLSchema #ff0000;">"http://xmlns.oracle.com/xdb/xdbconfig.xsd" Element #ff0000;">"xdbconfig"#66cc66;">) STORAGE #993333; font-weight: bold;">BINARY
 
SQL#66cc66;">> #993333; font-weight: bold;">DESC SERVLET
 
 Name                                      #993333; font-weight: bold;">NULL?    Type
 #808080; font-style: italic;">----------------------------------------- -------- ----------------------------
#993333; font-weight: bold;">TABLE of SYS#66cc66;">.XMLTYPE#66cc66;">(XMLSchema #ff0000;">"http://xmlns.oracle.com/xdb/XDBStandard.xsd" Element #ff0000;">"servlet"#66cc66;">) STORAGE Object#66cc66;">-relational TYPE #ff0000;">"SERVLET_T"
 
SQL#66cc66;">> #993333; font-weight: bold;">DESC #ff0000;">"SERVLET_T"
 
 Name                                      #993333; font-weight: bold;">NULL?    Type
 #808080; font-style: italic;">----------------------------------------- -------- ----------------------------
 SYS_XDBPD$                                         XDB$RAW_LIST_T
 icon                                               VARCHAR2#66cc66;">(#cc66cc;">4000 CHAR#66cc66;">)
 servlet#66cc66;">-name                                       VARCHAR2#66cc66;">(#cc66cc;">4000 CHAR#66cc66;">)
 servlet#66cc66;">-#993333; font-weight: bold;">LANGUAGE                                   XDB$ENUM_T
 display#66cc66;">-name                                       VARCHAR2#66cc66;">(#cc66cc;">4000 CHAR#66cc66;">)
 description                                        VARCHAR2#66cc66;">(#cc66cc;">4000 CHAR#66cc66;">)
 servlet#66cc66;">-class                                      VARCHAR2#66cc66;">(#cc66cc;">4000 CHAR#66cc66;">)
 jsp#66cc66;">-file                                           VARCHAR2#66cc66;">(#cc66cc;">4000 CHAR#66cc66;">)
 servlet#66cc66;">-schema                                     VARCHAR2#66cc66;">(#cc66cc;">4000 CHAR#66cc66;">)
 load#66cc66;">-on#66cc66;">-startup                                    VARCHAR2#66cc66;">(#cc66cc;">4000 CHAR#66cc66;">)
 security#66cc66;">-role#66cc66;">-ref                                  security#66cc66;">-role#66cc66;">-ref6_COLL
 
SQL#66cc66;">> #993333; font-weight: bold;">DESC #ff0000;">"security-role-ref6_COLL"
 
 #ff0000;">"security-role-ref6_COLL" VARRAY#66cc66;">(#cc66cc;">65535#66cc66;">) OF security#66cc66;">-role#66cc66;">-ref5_T
 
 Name                                      #993333; font-weight: bold;">NULL?    Type
 #808080; font-style: italic;">----------------------------------------- -------- ----------------------------
 SYS_XDBPD$                                         XDB$RAW_LIST_T
 description                                        VARCHAR2#66cc66;">(#cc66cc;">4000 CHAR#66cc66;">)
 role#66cc66;">-name                                          VARCHAR2#66cc66;">(#cc66cc;">4000 CHAR#66cc66;">)
 role#66cc66;">-link                                          VARCHAR2#66cc66;">(#cc66cc;">4000 CHAR#66cc66;">)
 
SQL#66cc66;">> #993333; font-weight: bold;">DESC XDB$MOUNTS
 
 Name                                      #993333; font-weight: bold;">NULL?    Type
 #808080; font-style: italic;">----------------------------------------- -------- ----------------------------
 DOBJ#808080; font-style: italic;">#                                              NUMBER
 DPATH                                              VARCHAR2#66cc66;">(#cc66cc;">4000#66cc66;">)
 SOBJ#808080; font-style: italic;">#                                              NUMBER
 SPATH                                              VARCHAR2#66cc66;">(#cc66cc;">4000#66cc66;">)
 FLAGS                                              NUMBER
 
SQL#66cc66;">> #993333; font-weight: bold;">DESC DBMS_XDBNFS
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM #993333; font-weight: bold;">TABLE#66cc66;">( myunwrapper#66cc66;">( #ff0000;">'DBMS_XDBNFS'#66cc66;">,#ff0000;">'XDB'#66cc66;">, #993333; font-weight: bold;">NULL#66cc66;">)#66cc66;">);
 
COLUMN_VALUE
#808080; font-style: italic;">-----------------------------------------------------------------------
PROCEDURE DUMP_NFSSTATS
 
#993333; font-weight: bold;">FUNCTION NFSFH2RESID RETURNS RAW
 Argument Name                  Type                    #993333; font-weight: bold;">IN#66cc66;">/Out #993333; font-weight: bold;">DEFAULT?
 #808080; font-style: italic;">------------------------------ ----------------------- ------ --------
 FH                             RAW                     #993333; font-weight: bold;">IN
 
PROCEDURE RESET_NFSSTATS
 Argument Name                  Type                    #993333; font-weight: bold;">IN#66cc66;">/Out #993333; font-weight: bold;">DEFAULT?
 #808080; font-style: italic;">------------------------------ ----------------------- ------ --------
 SKIP                           BINARY_INTEGER          #993333; font-weight: bold;">IN
 TRACE                          BINARY_INTEGER          #993333; font-weight: bold;">IN
 
PROCEDURE SYNCRESOURCE
 Argument Name                  Type                    #993333; font-weight: bold;">IN#66cc66;">/Out #993333; font-weight: bold;">DEFAULT?
 #808080; font-style: italic;">------------------------------ ----------------------- ------ --------
 RESID                          RAW                     #993333; font-weight: bold;">IN
 
PACKAGE BODY DBMS_XDBNFS #993333; font-weight: bold;">AS
 
#993333; font-weight: bold;">FUNCTION NFSFH2RESID#66cc66;">(FH #993333; font-weight: bold;">IN RAW#66cc66;">) #993333; font-weight: bold;">RETURN RAW #993333; font-weight: bold;">IS
BEGIN
  #993333; font-weight: bold;">RETURN SUBSTR#66cc66;">(FH#66cc66;">, #cc66cc;">1#66cc66;">, #cc66cc;">32#66cc66;">);
END;
 
PROCEDURE SYNCRESOURCE#66cc66;">(RESID #993333; font-weight: bold;">IN RAW#66cc66;">) #993333; font-weight: bold;">IS
  EXTERNAL
  NAME #ff0000;">"DBMSXDB_SYNC_RESOURCE"
  #993333; font-weight: bold;">LANGUAGE C
  LIBRARY DBMS_XDB_LIB
  #993333; font-weight: bold;">WITH CONTEXT
  PARAMETERS #66cc66;">(CONTEXT#66cc66;">,
              RESID   RAW#66cc66;">,
              RESID   INDICATOR SB4#66cc66;">,
              RESID   LENGTH SB4#66cc66;">);
 
PROCEDURE DUMP_NFSSTATS #993333; font-weight: bold;">IS
  EXTERNAL
  NAME #ff0000;">"DBMSXDB_DUMPNFSSTATS"
  #993333; font-weight: bold;">LANGUAGE C
  LIBRARY DBMS_XDB_LIB;
 
PROCEDURE RESET_NFSSTATS#66cc66;">(SKIP #993333; font-weight: bold;">IN PLS_INTEGER#66cc66;">, TRACE #993333; font-weight: bold;">IN PLS_INTEGER#66cc66;">) #993333; font-weight: bold;">IS
  EXTERNAL
  NAME #ff0000;">"DBMSXDB_RESETNFSSTATS"
  #993333; font-weight: bold;">LANGUAGE C
  LIBRARY DBMS_XDB_LIB
  #993333; font-weight: bold;">WITH CONTEXT
  PARAMETERS #66cc66;">(CONTEXT#66cc66;">,
              SKIP           UB4#66cc66;">,
              TRACE          UB4#66cc66;">);
 
END DBMS_XDBNFS;
 
SQL#66cc66;">> #993333; font-weight: bold;">SET head off
SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM DBA_SCHEDULER_JOBS #993333; font-weight: bold;">WHERE JOB_NAME #993333; font-weight: bold;">IN #66cc66;">(#ff0000;">'XMLDB_NFS_CLEANUP_JOB'#66cc66;">);
 
SYS                            XMLDB_NFS_CLEANUP_JOB
                               REGULAR     SYS
STORED_PROCEDURE
xdb#66cc66;">.dbms_xdbutil_int#66cc66;">.cleanup_expired_nfsclients
                  #cc66cc;">0
CALENDAR
Freq#66cc66;">=minutely;interval#66cc66;">=#cc66cc;">5
XMLDB_NFS_JOBCLASS             FALSE TRUE  FALSE DISABLED                   #cc66cc;">3
         #cc66cc;">0                        #cc66cc;">0                        #cc66cc;">0
OFF         FALSE TRUE

Anyway, testing set NFS functionality revealed that it has to be set after database start-up and for now doesn’t automatically load-on-startup, even after entering/updating for example table XDB$ROOT_CONFIG with the proper values.

The Protocol Server Part

Tracing the functionality as described in “ORA-31098: Internal event to turn on XDB tracing”, even combined with tracing on the appropriate shared server process id via the Oracle support facility called oradebug

Methods used where setting the event 31089 XDB protocol trace event on database level, like in the following example or higher and combining it with the 10046 event set via oradebug for the proper shared server process.

#808080; font-style: italic;">-- Used level 2 and higher to trace
SQL#66cc66;">> #993333; font-weight: bold;">ALTER system #993333; font-weight: bold;">SET event#66cc66;">= #ff0000;">'31098 trace name context forever, level 2' scope#66cc66;">=spfile;
 
SQL#66cc66;">> oradebug setorapid #cc66cc;">18
Oracle pid: #cc66cc;">18#66cc66;">, Unix process pid: #cc66cc;">28368#66cc66;">, image: oracle@srv01#66cc66;">-#cc66cc;">18#66cc66;">-102#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL #66cc66;">(S000#66cc66;">)
 
SQL#66cc66;">> oradebug event #cc66cc;">10046 trace name context forever#66cc66;">, level #cc66cc;">12
Statement processed#66cc66;">.
 
#808080; font-style: italic;">-- To disable the event
#808080; font-style: italic;">-- SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF
#808080; font-style: italic;">-- Statement processed.

As you can see/read, I had way to much time, to search the database, but then again working in between bank holidays can be a very quiet and productive time. What helps as well b.t.w. is using a Linux or Unix environment and search, for example, with some “grep” statements through the $ORACLE_HOME/rdbms/admin scripts. Searching through those scripts I found also hints that Oracle started thinking or building this functionality in approximately during/after the year 2005, based on the comments in the header of those scripts.

The Listener / OS part

Initially when I started it took me a while that by default Oracle Enterprise Linux has NFS services running in its “out of the box” state (not OK I think), So after stopping or even killing every NFS related daemon or service I started tracing on the Oracle listener part. Stopping every NFS part on the OS side is important due to the fact that otherwise the enabled port on the listener side (in my case 2049) will be blocked due to the fact that the defined port has already been taken by an OS daemon/service or maybe could trigger other “smart” processes like portmapper and others. In all intervening with my mount / trace attempts.

So the setting was as follows:

#66cc66;">[root@srv01#66cc66;">-#cc66cc;">18#66cc66;">-#cc66cc;">103 oranfs#66cc66;">]#808080; font-style: italic;"># su - oracle -c 'lsnrctl status'
 
LSNRCTL #993333; font-weight: bold;">FOR Linux: Version 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production #993333; font-weight: bold;">ON #cc66cc;">28#66cc66;">-DEC#66cc66;">-#cc66cc;">2009 #cc66cc;">14:#cc66cc;">31:#cc66cc;">38
Copyright #66cc66;">(c#66cc66;">) #cc66cc;">1991#66cc66;">, #cc66cc;">2009#66cc66;">, Oracle#66cc66;">.  #993333; font-weight: bold;">ALL rights reserved#66cc66;">.
Connecting #993333; font-weight: bold;">TO #66cc66;">(ADDRESS#66cc66;">=#66cc66;">(PROTOCOL#66cc66;">=tcp#66cc66;">)#66cc66;">(HOST#66cc66;">=#66cc66;">)#66cc66;">(PORT#66cc66;">=#cc66cc;">1521#66cc66;">)#66cc66;">)
 
#993333; font-weight: bold;">STATUS of the LISTENER
#808080; font-style: italic;">------------------------
Alias                     LISTENER
Version                   TNSLSNR #993333; font-weight: bold;">FOR Linux: Version 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
Start Date                #cc66cc;">15#66cc66;">-DEC#66cc66;">-#cc66cc;">2009 #cc66cc;">18:#cc66cc;">24:#cc66cc;">37
Uptime                    #cc66cc;">12 days #cc66cc;">20 hr#66cc66;">. #cc66cc;">7 min#66cc66;">. #cc66cc;">1 sec
Trace Level               off
Security                  #993333; font-weight: bold;">ON: #993333; font-weight: bold;">LOCAL OS Authentication
SNMP                      OFF
Listener Log File         #66cc66;">/u01#66cc66;">/app#66cc66;">/oracle#66cc66;">/diag#66cc66;">/tnslsnr#66cc66;">/srv01#66cc66;">-#cc66cc;">18#66cc66;">-#cc66cc;">103#66cc66;">/listener#66cc66;">/alert#66cc66;">/log#66cc66;">.xml
Listening Endpoints Summary#66cc66;">...
  #66cc66;">(DESCRIPTION#66cc66;">=#66cc66;">(ADDRESS#66cc66;">=#66cc66;">(PROTOCOL#66cc66;">=tcp#66cc66;">)#66cc66;">(HOST#66cc66;">=srv01#66cc66;">-#cc66cc;">18#66cc66;">-103#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL#66cc66;">)#66cc66;">(PORT#66cc66;">=#cc66cc;">1521#66cc66;">)#66cc66;">)#66cc66;">)
  #66cc66;">(DESCRIPTION#66cc66;">=#66cc66;">(ADDRESS#66cc66;">=#66cc66;">(PROTOCOL#66cc66;">=tcp#66cc66;">)#66cc66;">(HOST#66cc66;">=srv01#66cc66;">-#cc66cc;">18#66cc66;">-103#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL#66cc66;">)#66cc66;">(PORT#66cc66;">=#cc66cc;">8080#66cc66;">)#66cc66;">)#66cc66;">(Presentation#66cc66;">=HTTP#66cc66;">)#66cc66;">(Session#66cc66;">=RAW#66cc66;">)#66cc66;">)
  #66cc66;">(DESCRIPTION#66cc66;">=#66cc66;">(ADDRESS#66cc66;">=#66cc66;">(PROTOCOL#66cc66;">=tcp#66cc66;">)#66cc66;">(HOST#66cc66;">=srv01#66cc66;">-#cc66cc;">18#66cc66;">-103#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL#66cc66;">)#66cc66;">(PORT#66cc66;">=#cc66cc;">2100#66cc66;">)#66cc66;">)#66cc66;">(Presentation#66cc66;">=FTP#66cc66;">)#66cc66;">(Session#66cc66;">=RAW#66cc66;">)#66cc66;">)
  #66cc66;">(DESCRIPTION#66cc66;">=#66cc66;">(ADDRESS#66cc66;">=#66cc66;">(PROTOCOL#66cc66;">=tcp#66cc66;">)#66cc66;">(HOST#66cc66;">=srv01#66cc66;">-#cc66cc;">18#66cc66;">-103#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL#66cc66;">)#66cc66;">(PORT#66cc66;">=#cc66cc;">2049#66cc66;">)#66cc66;">)#66cc66;">(Presentation#66cc66;">=NFS#66cc66;">)#66cc66;">(Session#66cc66;">=RAW#66cc66;">)#66cc66;">)
Services Summary#66cc66;">...
Service #ff0000;">"ORCL" has #cc66cc;">1 instance#66cc66;">(s#66cc66;">)#66cc66;">.
  Instance #ff0000;">"ORCL"#66cc66;">, #993333; font-weight: bold;">STATUS READY#66cc66;">, has #cc66cc;">1 handler#66cc66;">(s#66cc66;">) #993333; font-weight: bold;">FOR this service#66cc66;">...
Service #ff0000;">"ORCLXDB" has #cc66cc;">1 instance#66cc66;">(s#66cc66;">)#66cc66;">.
  Instance #ff0000;">"ORCL"#66cc66;">, #993333; font-weight: bold;">STATUS READY#66cc66;">, has #cc66cc;">1 handler#66cc66;">(s#66cc66;">) #993333; font-weight: bold;">FOR this service#66cc66;">...
 
The command completed successfully

I created a directory on the Linux side /oranfs/public and opened it up for the world regarding its security settings (btw secure linux is not enabled on my test OEL linux system nor any firewall). I attempted connections / mount options via the NFS3 and NFS4 protocols but both weren’t successful (at least that was what I thought) via variations on the following:

#66cc66;">[root@srv01#66cc66;">-#cc66cc;">18#66cc66;">-#cc66cc;">103 oranfs#66cc66;">]#808080; font-style: italic;"># mount -v -t nfs srv01-18-103:2049/public /oranfs/public -o ro,sync,auto,hard,intr
 
mount: Unable #993333; font-weight: bold;">TO connect #993333; font-weight: bold;">TO 10#66cc66;">.252#66cc66;">.252#66cc66;">.103:#cc66cc;">111#66cc66;">, errno #cc66cc;">111 #66cc66;">(Connection refused#66cc66;">)
mount: trying 10#66cc66;">.252#66cc66;">.252#66cc66;">.103 prog #cc66cc;">100003 vers #cc66cc;">3 prot tcp port #cc66cc;">49096
mount: Unable #993333; font-weight: bold;">TO connect #993333; font-weight: bold;">TO 10#66cc66;">.252#66cc66;">.252#66cc66;">.103:#cc66cc;">49096#66cc66;">, errno #cc66cc;">111 #66cc66;">(Connection refused#66cc66;">)
mount: mount #993333; font-weight: bold;">TO NFS server #ff0000;">'srv01-18-103' failed: System Error: Connection refused#66cc66;">.
 
#66cc66;">[root@srv01#66cc66;">-#cc66cc;">18#66cc66;">-#cc66cc;">103 oranfs#66cc66;">]#808080; font-style: italic;"># mount -v -t nfs srv01-18-103:/public /oranfs/public -o ro,sync,auto,hard,intr
 
mount: Unable #993333; font-weight: bold;">TO connect #993333; font-weight: bold;">TO 10#66cc66;">.252#66cc66;">.252#66cc66;">.103:#cc66cc;">111#66cc66;">, errno #cc66cc;">111 #66cc66;">(Connection refused#66cc66;">)
mount: trying 10#66cc66;">.252#66cc66;">.252#66cc66;">.103 prog #cc66cc;">100003 vers #cc66cc;">3 prot tcp port #cc66cc;">49096
mount: Unable #993333; font-weight: bold;">TO connect #993333; font-weight: bold;">TO 10#66cc66;">.252#66cc66;">.252#66cc66;">.103:#cc66cc;">49096#66cc66;">, errno #cc66cc;">111 #66cc66;">(Connection refused#66cc66;">)
mount: mount #993333; font-weight: bold;">TO NFS server #ff0000;">'srv01-18-103' failed: System Error: Connection refused#66cc66;">.

I had enabled the listener to trace on support level via the following so I could follow if at least the listener would try to pick up the mount request and do a hand off to a part in the database.

LSNRCTL#66cc66;">> #993333; font-weight: bold;">SET trc_level #cc66cc;">16
 
Connecting #993333; font-weight: bold;">TO #66cc66;">(ADDRESS#66cc66;">=#66cc66;">(PROTOCOL#66cc66;">=tcp#66cc66;">)#66cc66;">(HOST#66cc66;">=#66cc66;">)#66cc66;">(PORT#66cc66;">=#cc66cc;">1521#66cc66;">)#66cc66;">)
LISTENER parameter #ff0000;">"trc_level" #993333; font-weight: bold;">SET #993333; font-weight: bold;">TO support
 
The command completed successfully

I will spare you the whole listener and trace file (you could try it yourself anyway if your interested), but using the NFS4 protocol mount options were picked up by the listener.

#808080; font-style: italic;">-- listener.log
Thu Dec #cc66cc;">31 #cc66cc;">12:#cc66cc;">34:#cc66cc;">46 #cc66cc;">2009
#cc66cc;">31#66cc66;">-DEC#66cc66;">-#cc66cc;">2009 #cc66cc;">12:#cc66cc;">34:#cc66cc;">46 #66cc66;">* NFS #66cc66;">* #66cc66;">(ADDRESS#66cc66;">=#66cc66;">(PROTOCOL#66cc66;">=tcp#66cc66;">)#66cc66;">(HOST#66cc66;">=10#66cc66;">.252#66cc66;">.252#66cc66;">.103#66cc66;">)#66cc66;">(PORT#66cc66;">=#cc66cc;">65128#66cc66;">)#66cc66;">) #66cc66;">* handoff #66cc66;">* NFS #66cc66;">* #cc66cc;">0

and in the trace file…

#66cc66;">...
#cc66cc;">2009#66cc66;">-#cc66cc;">12#66cc66;">-#cc66cc;">31 #cc66cc;">12:#cc66cc;">34:#cc66cc;">46.775605 : nsglnphc:Connected #993333; font-weight: bold;">WITH client#66cc66;">. Protocol: tcp  Presentation: NFS#66cc66;">.
#66cc66;">...
#993333; font-weight: bold;">AND a bit later
#66cc66;">...
#cc66cc;">2009#66cc66;">-#cc66cc;">12#66cc66;">-#cc66cc;">31 #cc66cc;">12:#cc66cc;">34:#cc66cc;">46.787677 : nsbequeath:NSE#66cc66;">=#cc66cc;">12586
#cc66cc;">2009#66cc66;">-#cc66cc;">12#66cc66;">-#cc66cc;">31 #cc66cc;">12:#cc66cc;">34:#cc66cc;">46.787784 : nsbequeath:error reading REDIR#66cc66;">/NSE msg
#66cc66;">...

Summery

What did I learn? In principal not that much, just that some of the structures (or residue depending on the view) regarding XDB NFS Protocol Server adapter support is build in. On the other hand, besides the tedious monk work regarding trace 31098 levels, the whole exercise crawling through all those database setup and installation scripts, the tracing as is, and deep dive into database parts gave me a better understanding and insight of the internal workings of it all.

I would really like to see this functionality in a next release, not only because I think that the Securefile Development team functionality of the Database File System (DBFS) is a bit of a fuzz on pointers like having to use FUSE and a DBFS client to make it all happen. Despite they did great work to make it happen in the first place, but being me a XMLDB nerd, why not use a much more simpler set-up without all those prerequisites. The XDB Repository and the XDB Protocol Server architecture have so much to offer in terms of extra’s along the side like “Repository Events“, ACL Security build-in, Versioning, etc, to just name a few.

And yes, XMLDB has also a lot of hook-ins regarding securefile support or for instance DICOM support as a old “XFiles” demo application will prove automatically recognizing EXIF information in JPG/PNG and other pictures. Oracle please implement it after all, if not only due to enabling an extra possibility to exchange XML based data.

Just an idea.

M.

Want to see it all in action?

I will be giving some demonstrations on the mentioned subject in this post during a presentation of mine called: “Boost your environment with Oracle XMLDB”. You can attend this presentation during the “MOTS” and “MOW” events this year.

Thinking Clearly About Performance, revised to include Skew

I’ve just updated the “Thinking Clearly” paper to include an absolutely vital section that was, regrettably, missing from the first revision. It’s a section on the subject of skew.

I hope you enjoy.

Why does the size of my ORACLE_HOME increase?

Recently I discovered that the size of an ORACLE_HOME for a given release varies from machine to machine although the machines are of the same architecture and run the same operating system. A small difference in size can be explained by the fact that one ORACLE_HOME was re-linked in the past while the other wasn’t, […]

Yes, I'm still here

I admit it. I'm a binge blogger (I borrowed this term from a friend of mine who posted on the same topic). I make several blog entries over the course of a week or so and then it may be a month or two before I show up again. I suppose my good intentions of blogging regularly just get swept under the rug of day-to-day reality and how/where I choose to spend my time. But, regardless of frequency, I suppose it's fairly obvious from this post that I'm still here.

I was doing a bit of blog surfing tonight to catch up on a long list of blogs I enjoy reading and came across the following from a recent Seth Godin post:

"Firing the customers you can't possibly please gives you the bandwidth and resources to coddle the ones that truly deserve your attention and repay you with referrals, applause and loyalty."

Amen! This reminded me a a time early in my days as a self-employed consultant back in the mid-90's. I took on a client that turned out to be a royal pain-in-the-backside. They'd call me to complain about their fax not working (among other odd things) and even though the services I had contracted with them to provide had nothing to do with most of the things they called to rant about, they expected me to address and fix them all. I ended up giving them back the initial fee they'd paid and canceled the contract. I felt better immediately and the time I was spending dealing with them was quickly filled with several new (and much nicer and "normal") customers.

So, I can relate to this quote. I also think it just makes good sense across the board. Whatever you give your time and attention to (careers, relationships, etc) should yield positive (whatever positive means to you) results. If you find that you're always unhappy when dealing with certain aspects of life, then I think it's entirely reasonable to look for ways to eliminate/reduce whatever it is and replace it with something that brings more happiness and positiveness to you. It may seem difficult and a bit scary to do so, but I think in the long run it's the way to go.

On a different note, I spoke today at the MAOP (Mid Atlantic Association of Oracle Professionals) Conference held today in Reston VA. I really enjoyed being there and got some good feedback and questions after speaking. In my current work I don't get the opportunity to speak in front of folks like I used to when I taught classes at least a couple of weeks a month. So, I'm really grateful to MAOP for asking me to speak and for having the opportunity to get a "fix" from presenting to a great audience!

And speaking of conferences, the annual Hotsos Symposium is coming up March 7-11 in Dallas. This will be the first one I've missed since they started having them 8 years ago (bummer!). But, if you want to attend one of the best conferences anywhere, and the only one focused specifically on Oracle performance, there's still seats available. I highly recommend it!

Mapping the NoSQL space

NoSQL is an unfortunate name – it doesn’t give any description of what the product does except what query language it will not support. What’s worse, it makes people treat the various non-relational databases as interchangable, while in fact many of them solve completely different problems and have different trade-offs, strengths, etc.

What is common to all these DBs is that they don’t do ACID in an attempt to improve scalability, most of them are distributed and most of them were built to handle semi-structured or unstructured data.

The theoretical case for these databases starts from the CAP theorem which says you can’t have consistency, availability and partition tolerance all at once. Partition tolerance is the prevention of split-brain in a cluster or distributed system – you don’t want network failures to allow data corruptions or incorrect results.

Since you can’t have all three, you choose two. So RAC does partition tolerance and consistency at the expense of availability – if the voting disk crashes or loses network connectivity, the entire cluster will go down.

NoSQL databases keep availability and partition tolerance at the expense of consistency. They have something called “Soft-State” and “Eventual Consistency”. To the best of my understanding, “Eventual Consistency” means that all the DML statements in the transaction are inserted into a queue (or some equivalent), from which they are executed at different times by different servers. Eventually they are all executed and you reach a consistent state, but you don’t know when. Of course with such system, it appears nearly impossible to prevent lost updates.

This doesn’t seem like a good way to manage bank accounts, but when I reviewed the databases I manage, only very few of them really need full consistency. Many of them are not updated concurrently, or where there are no updates (just inserts) or contain data such as project plans where not being consistent at every single second would be OK.

Here’s a short list of the the non-relational databases I’m somewhat familiar with and the problems they solve:

Map-Reduce – not a database at all, its an algorithm or a design methodology that allows for massive scalability.

Hadoop – not a database. Its a platform – a distributed file-system and a map-reduce job manager.

Hive – Its a SQL like language allows for structured schema design and queries on top of Hadoop. It has some superficial similarities with RDBMS, but it is just the syntax – every query is translated to map-reduce code, execution is totally different and don’t expect most  RDBMS features.

HBase – Allows you to create tables with rows and columns (normally very large ones) and query them through several Java/HTTP interfaces. You query each table individually, no joins.

Cassandra - Does exactly the same as HBASE. To the best of my understanding it is more configurable and flexible but is not as well documented.

Tokyo Cabinet /Tyrant – Stores key/value pairs. There are no tables and no data types. You can store data in hash tables, b-trees or fixed-size arrays. It is not distributed. Said to have amazing performance.

Voldemort – Similar to Tokyo Cabinet, but distributed – although it appears that when adding nodes performance doesn’t scale well.

CouchDB - This is a document store, where each document contains multiple key-value pairs. It does include some of the traditional DB features, just in a different context. It has the concept of index, and you create an index for each report you want to run. It supports multiple-versions of each document, where a report is guaranteed to run on the same version from beginning to end. There is no schema – documents can contain different keys.

MongoDB – Similar to CouchDB, it is a document store. It is not distributed. It doesn’t have multiple document revisions – all updates are done on the same document. No indexes either, which allows for ad-hoc querying.

Hope this is useful :)

ILO Elapsed Time module available

For anyone who is interested in recording the process and cpu elapsed via instrumentation, I have made the elapsed time collection module I'm currently using available at the Expert Oracle Practices: Database Administration from the Oak Table source code download site.The first step is to download the Instrumentation Library for Oracle (ILO) version 2.3 from SourceForge.Next, install ILO per the

Cool but unknown RMAN feature

Unknown to me anyway until just this week.

Some time ago I read a post about RMAN on Oracle-L that detailed what seemed like a very good idea.

The poster's RMAN scripts were written so that the only connection while making backups was a local one using the control file only for the RMAN repository.

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">rman target sys/manager nocatalog

After the backups were made, a connection was made to the RMAN catalog and a SYNC command was issued.

The reason for this was that if the catalog was unavailable for some reason, the backups would still succeed, which would not be the case with this command:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">rman target sys/manager catalog rman/password@rcat

This week I found out this is not true.

Possibly this is news to no one but me, but I'm sharing anyway. :)

Last week I cloned an apps system and created a new OID database on a server. I remembered to do nearly everything, but I did forget to setup TNS so that the catalog database could be found.

After setting up the backups vie NetBackup, the logs showed that there was an error condition, but the backup obviously succeeded:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">archive log filename=/u01/oracle/oradata/oiddev/archive/oiddev_arch_1_294_709899427.dbf recid=232 stamp=710999909
deleted archive log
archive log filename=/u01/oracle/oradata/oiddev/archive/oiddev_arch_1_295_709899427.dbf recid=233 stamp=710999910
Deleted 11 objects


Starting backup at 16-FEB-10
released channel: ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=369 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: VERITAS NetBackup for Oracle - Release 6.0 (2008081305)
channel ORA_SBT_TAPE_1: starting full datafile backupset
channel ORA_SBT_TAPE_1: specifying datafile(s) in backupset
including current controlfile in backupset
channel ORA_SBT_TAPE_1: starting piece 1 at 16-FEB-10
channel ORA_SBT_TAPE_1: finished piece 1 at 16-FEB-10
piece handle=OIDDEV_T20100216_ctl_s73_p1_t711086776 comment=API Version 2.0,MMS Version 5.0.0.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:45
Finished backup at 16-FEB-10

Starting Control File and SPFILE Autobackup at 16-FEB-10
piece handle=c-3982952863-20100216-02 comment=API Version 2.0,MMS Version 5.0.0.0
Finished Control File and SPFILE Autobackup at 16-FEB-10

RMAN> RMAN>

Recovery Manager complete.

Script /usr/openv/netbackup/scripts/oiddev/oracle_db_rman.sh
==== ended in error on Tue Feb 16 04:07:59 PST 2010 ====

That seemed rather strange, and it was happening in both of the new databases.
The key to this was to look at the top of the log file, where I found the following:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">BACKUP_MODE: lvl_0
BACKUP_TYPE: INCREMENTAL LEVEL=0
ORACLE_SID : oiddev
PWD_SID : oiddev
ORACLE_HOME: /u01/oracle/oas
PATH: /sbin:/usr/sbin:/bin:/usr/bin:/usr/X11R6/bin

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.


RMAN;
connected to target database: OIDDEV (DBID=3982952863)

RMAN;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04004: error from recovery catalog database: ORA-12154: TNS:could not resolve the connect identifier specified

RMAN;
Starting backup at 16-FEB-10
using target database controlfile instead of recovery catalogallocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=369 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: VERITAS NetBackup for Oracle - Release 6.0 (2008081305)
channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backupset

Notice the line near the bottom of the displayed output?

The one that says "using target database controlfile instead of recovery catalog" ?

RMAN will go ahead with the backup of the database even though the connection to the catalog database failed.  This apparently only works when running in a scripted environment, as when I tried connecting on the command line RMAN would simply exit when the connection to the catalog could not be made.

The RMAN scripts are being run on a linux server in the following format:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">$OH/bin/rman target sys/manager catalog rman/password@rcat <<-EOF >> $LOGFILE

rman commands go here

EOF

This was quite interesting to discover, and my be old news to many of you, but it was new to me.

This is not exactly a new feature either - one of the databases being backed up is 9.2.0.6. And of course there is now no need to update the backup scripts.

Simple script to monitor dNFS activity

In my previous series regarding “dNFS” vs “kNFS” I reference a script that monitors dNFS traffic by sampling the v$dnfs_stats view.  A reader requested the script so I thought it might be useful to a wider audience.  This simple script samples some values from the view and outputs the a date/timestamp along with rate information.  I hope it is useful.

------ mondnfs.sql -------

set serveroutput on format wrapped size 1000000
create or replace directory mytmp as '/tmp';

DECLARE
n number;
m number;
x number := 1;
y number := 0;

bnio number;
anio number;

nfsiops number;

fd1 UTL_FILE.FILE_TYPE;

BEGIN
fd1 := UTL_FILE.FOPEN('MYTMP', 'dnfsmon.log', 'w');

LOOP
bnio := 0;
anio := 0;

select  sum(nfs_read+nfs_write) into bnio from v$dnfs_stats;

n := DBMS_UTILITY.GET_TIME;
DBMS_LOCK.SLEEP(5);

select  sum(nfs_read+nfs_write) into anio from v$dnfs_stats;

m := DBMS_UTILITY.GET_TIME - n ;

nfsiops := ( 100*(anio - bnio) / m ) ;

UTL_FILE.PUT_LINE(fd1, TO_CHAR(SYSDATE,'HH24:MI:SS') || '|' || nfsiops );
UTL_FILE.FFLUSH(fd1);
x := x + 1;
END LOOP;

UTL_FILE.FCLOSE(fd1);
END;
/

========================

Filed under: Oracle, Storage