13 Replies Latest reply on Jan 21, 2013 12:42 PM by 978049

    Querying SES database


      I have a problem which I am hoping one you SES gurus can help me with!

      Simplified scenario: I have 2 sources containing 10 documents each. All documents have the attribute attr1, which basically is a hash code calculated based on the documents content.
      One document is duplicated across the two sources, hence, the hash code is the same for two of the 20 indexed documents.

      When the above occurs I need a mechanism which can detect this and handle it appropriately. I was hoping that i could query the SES database and find the values for attr1 which are not unique, however, I have absolutely no idea which table(s) to query and what properties they have.

      Any suggestions will be much appreciated!

        • 1. Re: Querying SES database
          I found the table EQ_TEST.EQ$DOC which seems to hold the information I am looking and even better there is a checksum and signature for all documents. Any thoughts on the difference checksum and signature in this and on whether this is the right table for me?
          • 2. Re: Querying SES database

            I need some information on how to conenct to the Oracle SES db. What userid and password should I use to connect to the db since during installation process it doesn't ask for any credential ? Also what's the SID/Service Name that I should use to connect to the database ?
            • 3. Re: Querying SES database
              Roger Ford-Oracle
              What are you aiming to do in the DB?

              The standard user is EQSYS, whose database password is the same as the SES admin password. Administrative information is stored in the EQSYS schema. The actual document table, indexes, etc are stored in the EQ_TEST schema, but you can't log in to the db as EQ_TEST since the password is auto-generated and encrypted. EQSYS has access to all the tables in the EQ_TEST schema, however.
              • 4. Re: Querying SES database
                Thanks Roger for the response. I am trying to figure out if SES indexes can be exposed to external entities such as other search engines for using these indexes. Can you tell me which table being used to store all the indexes ? Does it store the index in different tables based on the source type ?
                • 5. Re: Querying SES database
                  Roger Ford-Oracle
                  There's only one index for all sources. It's an Oracle Text index, so in theory you could run Oracle Text queries against that index. But it wouldn't be easy, especially if you want to do attribute-based searches or secure searches.

                  It would be far better to use the Web Services API to run the searches on behalf of the external search engine.
                  • 6. Re: Querying SES database
                    Thanks Roger for the reponse.

                    Firstly, I am not able to login to the database using eqsys and the password for the admin user which I use to log into Oracle SES admin console.

                    Secondly, If I want to use that my external seach engine such as Microsoft FAST tries to search through Oracle SES, is it sufficient to give that interface just the indexed files in a zip ? But still FAST needs to understand the APIs for searching from the index, right ? Is that FAST needs to use direct Oracle Text queries to search from the indexed files ?

                    Also if FAST uses the WebService APIs instead of accessing the indexed files directly, is there any performance implication since there will be an overhead of SOAP calls over HTTP ?

                    We are trying to access if we can keep Oracle SES as the underlying search interface since it has the capability to search through multiple Oracle products including WebCenter, spaces, content server etc. On the enterprise level, we will have Microsoft FAST. So, we are thinking of keeping OSES to work as the underlying search engine and then wrap it with FAST for enterprise search. Let me know if you have any thoughts on this approach.
                    • 7. Re: Querying SES database
                      Roger Ford-Oracle
                      FAST will not understand Oracle Text indexes. I don't know what capabilities FAST provides for query federation, but it would need to submit queries to SES in some fashion. Creating Oracle Text queries at the database level would be difficult and unsupported. Using the Web Services API would involve a small amount of overhead in the SOAP layer but would be fully supported.

                      I'm not sure why you can't log in using EQSYS. Are you doing it on the SES machine itself using SQL*Plus, or are you trying to connect from a remote machine (perhaps using SQL Developer on a PC?). Remote connections are purposely disabled on the SES database for security reasons, but it's easy enough to reenable them if you want to.
                      • 8. Re: Querying SES database
                        Thanks Roger for the quick response.

                        I tried to run sqlplus from the SES machine itself. But I am getting some issues. I hope the SID for the database instance is "ses". Let me know if it may be something different.

                        Also I have some other questions.

                        1. I am trying to integrate OSES, Oracle Content Server, and Webcenter Portal with Oracle Internet Directory. Now, I have integrated Oracle Content Server with OID and also did the same for OSES. In OSES, when I am creating the identity plug-in, I am putting the admin userid for OID there. Now, I can log into OSES only with OID admin user but it doesn't allow me log into OSES with any other valid users from OID.

                        2. I am not able to perform the secure search from Oracle Content Server through OSES. Since both these are associated with OID, I can log in with the same user such as OID admin user. But, I am not able to perform secure search for content which will be accesed by the user based on the OID groups and Content Server (UCM) roles.
                        I have tried couple of procedures such as "Crawl time ACL Stamping" with Oracle Secure Enterprise Search ACL. I am not able to find where the actual configuration needs t be done for searching the secure content based on the user roles and authorization.

                        Thanks for giving your inputs.
                        • 9. Re: Querying SES database
                          Roger Ford-Oracle
                          The SID is "ses" by default, unless you changed the "Searchserver name" on install. Make sure you have the environment variable ORACLE_HOME pointing to the "seshome" directory.

                          Your two issues 1 and 2 look related. If you can't log in to SES as anything other than the OID admin user, then it's likely the secure search from UCM won't work.

                          What value have you got for "Authentication Attribute" in your OID identity manager? It would normally be "nickname".
                          • 10. Re: Querying SES database
                            Hi Roger,

                            I have updated ORACLE_HOME. But still I am getting the exception "ORA-12705: Cannot access NLS data files or invalid environment specified". The SID is the default one as "ses".

                            I am using nickname as the authentication attribute. I am not sure why am I not able to login in OSES using any other user except OID admin. There are some other issues. I can do some secure search based on the access control from UCM. I have mapped the UCM role to OID groups and then added the security groups in UCM. Therefore, when I am logged in with OID admin user in OSES, I am able to search secure contents. But when I deassociate the OID admin user from OID/UCM role, ideally I shouldn't be able to search the same content from OSES. But still the admin user is able to search the document. On the other hand, after the deassociation the access control in UCM is working fine. The admin user is not able to search the same document again. Do you have any suggestion ?
                            • 11. Re: Querying SES database
                              Roger Ford-Oracle
                              ORA-12705 is normally caused by an invalid setting for NLS_LANG.
                              [oracle@rf1112test thes]$ sqlplus eqsys/password
                              SQL*Plus: Release - Production on Wed Jun 13 09:14:00 2012
                              Copyright (c) 1982, 2008, Oracle.  All rights reserved.
                              Connected to:
                              Oracle Database 11g Enterprise Edition Release - 64bit Production
                              With the Partitioning option
                              SQL> quit
                              Disconnected from Oracle Database 11g Enterprise Edition Release - 64bit Production
                              With the Partitioning option
                              [oracle@rf1112test thes]$ export NLS_LANG=foobar
                              [oracle@rf1112test thes]$ sqlplus eqsys/password
                              SQL*Plus: Release - Production on Wed Jun 13 09:14:15 2012
                              Copyright (c) 1982, 2008, Oracle.  All rights reserved.
                              ORA-12705: Cannot access NLS data files or invalid environment specified
                              I think you'll have to open an SR on the OID issues, or wait to see if a current SES expert comes along (I'm an ex-SES-expert!)
                              • 12. Re: Querying SES database
                                Hi Roger,

                                Thanks for the reply. I think your comments have helped me immensely to find the answers for some of my questions. I have been exploring OSES for last 2 months but I am not able to find solutions for some of the problems that I am facing.

                                I am trying to integrate the Oracle Webcenter discussion server with OSES. But it is not working. I am mentioning the parameters that I am using below.

                                View: DEV_DISCUSSIONS_CRAWLER
                                URL Prefix : http://delvmpllrr03.sapient.com:9888/webcenter

                                I am not sure what to use for Query and Query File.

                                Grant Security Attribute : WCSECATTR

                                Authorization userid : DEV_DISCUSSIONS_CRAWLER
                                Authorization User ID Format - What will be the value for this ?

                                If you have any idea on how can I make it work, please let me know.

                                Also in your last post, you had mentioned on some expert for OSES to help me for the issues. Do you know anyone from this group who has worked or is working on the latest OSES 11g ?
                                • 13. Re: Querying SES database
                                  Hello. I've got the same issue. Well, not exactly, but pretty similar. According to SES crawling statistics my discussions database DEV_DISCUSSIONS_CRAWLER view has been successfully indexed, but I don't get any result searching with SES. Can you please let me know if you solved this issue? Thank you.