Oracle Read-Only vs Read/Write Connection in Map - Geospatial Desktop Forum - Geospatial Desktop - Bentley Communities

Oracle Read-Only vs Read/Write Connection in Map

Geospatial Desktop

Geospatial Desktop
This is where you can find and contribute to discussions, ideas, and other information about Bentley Geospatial Desktop products.
Geospatial Desktop Forum

Oracle Read-Only vs Read/Write Connection in Map

  • I am having problems creating a read/write connection to an Oracle Spatail database. I can establish a read-only connection and see the Oracle spatial "layers" with no problems. However, when I attempt to establish a read/write connection, Bentley Map sits for a few minutes, then reports an ORA-12154 (TNS could not resolve the connect identifier) message back to me. I'm using the same connection info as I did for the successful read-only connection. Why would a read-only connection work, and a read/write connection fail? I suspect there is some other issue rather than resolving the connect string, as it takes a minute or two to return the error message. Other apps that have that issue with name resolution respond immediately that the user/password/instance is incorrect. Our database does have Oracle Spatial installed and does not have the Workspace Management/Versioning extension installed.
  • The Bentley Map version would be helpful . 

    So to test this, I guess you have 2 different Oracle users. One had the connect and select roles and the other has the connect, select, update, delete and insert roles,

    If you are using the same TNS connection, but with different users, then I see no problem with this. 

    Some reasons for this error can be:

    1. Make sure that the TNSNAMES.ORA file exists and is in the correct directory.
    2. Make sure that the service name that you are connecting to is included in the TNSNAMES.ORA file and that it is correctly defined.
    3. Make sure that there are no syntax errors in the TNSNAMES.ORA file. For example, if there are unmatched brackets in the file (ie: open bracket without the corresponding close bracket), the file will be rendered unusable.
    4. Also multiple ORA homes tend to monkey up the works.

     

    Since you do have Oracle spatial installed, are you trying to connect to the database via the Spatial Connection or through the regular database connection method ?? If through the Oralce Spatial method, then the tables need to be spatial tables, with the correct columns, indexes, etc to work correctly. This is explained in the Bentley Map docs.

     

    HTH

    Jerry

     

     

     

      

  • MicroStation version 8.11.5.17

    Bentley Map version 8.11.5.49

    Oracle client version 11.1.0

     

    The test was done using the same Oracle user, with CONNECT, SELECT, UPDATE, DELETE, and INSERT privs. SInce the same "user" was able to connect read-only and not read/write to the same Oracle instance, it would (I think) resolve any issues with the TNS stuff (even though that's the error message that Bentley Map returns). There are multiple versions of the Oracle client installed on the PC, but I have specified the MS_ORACLE_HOME for the MAP workspace being used to ensure the appropriate Oracle client is being used. The Oracle user schema account is "empty" - i.e. there are no existing Oracle Spatial tables. There ARE Oracle spatial tables (SDO_GEOMETRY, and spatial indexes) in other schemas and access (SELECT) has been granted to the test user account. The connection being attempted is via the Interoperability dialog. not the "standard" MicroStation way requiring the MSCATALOG table. I am able to connect using "plain" MicroStation via an ODBC connection to the Oracle database in question. Interestingly, I am unable to connect in plain MicroStation using the "Oracle" tab of the "Connect to Database" dialog (again, using the same parameters used to successfully establish a read-only Oracle connectino in the Interoperability dialog).

     

    Any other thoughts/ideas ?

     

    Bruce

     

  • I'd check to ensure that the Oracle Database Server version is supported.   The BMAP readme should outline this.

  • The database is "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64 bit production", which appears to be supported (from readme: Oracle Database 10g Release 2 Enterprise Edition).
  • If you happen to be connecting on the server itself, using a 64 bit client, you may instead try connecting from a seperate machine using 32 bit drivers.

  • Database server is on different box from end-user client. One issue I did encounter was when (attempting) creating the read/write Oracle connection, I received an Oracle error ORA-02020: (Too many database links in use). I contacted the DBA folks and they increased the database paramater(s). After reboot of database, now I get an ORA-12154 (TNS could not resolve the connect identifier). I did not receive that error intially, so the ORA-12154 error may be reated to how the ORA-02020 was resolved. We have another Oracle DB with Spatial installed, so today I tried to create a read/write connection to it. I received the ORA-02020 error (too many links), and the DBA for that instance made some changes, rebooted the instance and I was able to successfully create a connection. I have put both DBA's in touch to try and figure out if something done in initial ORA-02020 resolution wasn't enough. Unfortunately (for me), the Oracle instance where I was successful is being retired, in favor of the newer instance (where I can't create the connection).

     

    I tried to do a "session debug on" while in Map, but it looks like Map must remove/disable that command. I was hoping to be able to get some type of a trace that might allow me to determine better what the real issue is.

     

    Bruce