locked
How to Connect SSMA for Oracle to Oracle RAC RRS feed

  • Question

  • I'm working on a project to convert a database from Oracle to SQL Server.  I don't seem to be able to connect to the Oracle database from SSMA 7.3.0.  The Oracle database is in a RAC environment.  I've tried service name, TNS name, etc. to connect via the Oracle OLEDB provider provided by the Oracle 12c client, but can't seem to make a connection.  Any ideas out there?

    Michael Brule Senior Database Specialist Microsoft SQL Server Voya Financial

    Tuesday, June 6, 2017 3:44 PM

Answers

  • I tried using the Connection String mode in SSMA and got a bit further.  The error I get is "Error occurred while collecting data."  I entered the connection string in this format:  

    Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VIP.For.SCANNAME)(PORT=12345))(CONNECT_DATA=(SERVICE_NAME=db_srvc.oracle.db)));
    User Id
    =myUsername;Password=myPassword;

    I got these results:

    Connection to Oracle established successfully.
    Connection string: [see above]
    Error occurred while collecting data.


    Michael Brule Senior Database Specialist Microsoft SQL Server Voya Financial

    Thursday, June 8, 2017 6:33 PM

All replies

  • Hi Michael,

    Could you please share the error message when you connecting to Oracle to us for analysis?

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 7, 2017 6:49 AM
  • I get these error messages:

    Using Oracle OLEDB provider in TNSName mode providing TNS alias - Error:  Connection to Oracle failed.  ORA-12154:  TNS: Could not resolve the connect identifier specified.

    Using Oracle OLEDB provider in Standard mode using database VIP - Error:  ORA-12505:  TNS: listener does not currently know of SID given in connect descriptor.

    I've also tried using the database service name from the database's TNS entry with similar result.  I've been told by Oracle DBAs that you can't connect to RAC using an Oracle SID.  


    Michael Brule Senior Database Specialist Microsoft SQL Server Voya Financial

    Wednesday, June 7, 2017 5:28 PM
  • Michael,

    Since you are connecting to RAC, does your tnsnames.ora have an entry for the second node? or SCAN (Single Client Access Name) name instead of just single hostname?

    You need to have the SCAN listener name in your tns entry, if you are connecting to RAC database. Once that is done, do a tnsping on the source server and see if it returns 'OK'.

    See more here: http://www.dba-oracle.com/t_scan_rac.htm

    Mohsin, M.S., M.C.S.E., O.C.P.

    ---------------------------------------------

    Please click "Mark as Answer" if my response answered your question or vote as helpful if it helped you in any way 

    • Proposed as answer by Teige Gao Thursday, June 8, 2017 8:18 AM
    Wednesday, June 7, 2017 6:08 PM
  • The tnsnames.ora file I'm using has a virtual IP name representing the SCAN name.  It does not have an entry for the second node. 

    A tnsping of the virtual IP name returns this message:

    Message 3511 not found; No message file for product=NETWORK, facility=TNSMessage
     3512 not found; No message file for product=NETWORK, facility=TNSMessage 3513 n
    ot found; No message file for product=NETWORK, facility=TNSTNS-12541: Message 12
    541 not found; No message file for product=NETWORK, facility=TNS


    Michael Brule Senior Database Specialist Microsoft SQL Server Voya Financial

    Thursday, June 8, 2017 6:01 PM
  • I also get the same message when I tnsping of the SCAN name or the IP address.


    Michael Brule Senior Database Specialist Microsoft SQL Server Voya Financial

    Thursday, June 8, 2017 6:04 PM
  • I tried using the Connection String mode in SSMA and got a bit further.  The error I get is "Error occurred while collecting data."  I entered the connection string in this format:  

    Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VIP.For.SCANNAME)(PORT=12345))(CONNECT_DATA=(SERVICE_NAME=db_srvc.oracle.db)));
    User Id
    =myUsername;Password=myPassword;

    I got these results:

    Connection to Oracle established successfully.
    Connection string: [see above]
    Error occurred while collecting data.


    Michael Brule Senior Database Specialist Microsoft SQL Server Voya Financial

    Thursday, June 8, 2017 6:33 PM
  • I tried using the Connection String mode in SSMA and got a bit further.  The error I get is "Error occurred while collecting data."  I entered the connection string in this format:  

    Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VIP.For.SCANNAME)(PORT=12345))(CONNECT_DATA=(SERVICE_NAME=db_srvc.oracle.db)));
    User Id
    =myUsername;Password=myPassword;

    I got these results:

    Connection to Oracle established successfully.
    Connection string: [see above]
    Error occurred while collecting data.

    Could you check the log file and see if it gives any pointers? Check the link below:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9ea87a56-3cce-483e-bd05-6a534e8a581b/ssma-when-try-to-connect-to-oracle-server-message-appeared-error-occurred-while-collecting-data?forum=sqlservermigration


    Saturday, June 10, 2017 9:18 AM
  • Turns out that the "Error occurred while collecting data" message above was due to some missing permissions on the Oracle side.  Once they were granted, I was able to successfully connect.

    Michael Brule Senior Database Specialist Microsoft SQL Server Voya Financial

    Monday, June 12, 2017 12:47 PM