Forum Stats

  • 3,827,538 Users
  • 2,260,790 Discussions
  • 7,897,295 Comments

Discussions

How to convert Oracle DB to SQL ?

vmat
vmat Member Posts: 49
edited Apr 7, 2015 8:34AM in General Database Discussions

Hello everyone, nice to meet you!

First of all I apologize for :

-my pretty terrible English

-the fact that I'm a complete greenhorn in databases and that my question could sound as a complete evidence for most of you

-the fact that there is probably a bilion threads on this topic, but after many hours lost in searches in this forum or elsewhere, I've just gave up..

So now that these points are mentionned, here is my problem with my own green words :

Goal : Be able to log on and restore an Oracle DB from a dump file (.dmp), and then convert it to SQL using SSMA.

My first step was to be able to install Oracle Databese Express 11gR2.

Then, I installed SSMA and launch it to fulfill my goal.

To connect to Oracle, I now have to choose a connexion mode, notably one of these two ( text from https://msdn.microsoft.com/en-us/library/hh313203(v=sql.110).aspx ):

  1. If you select Standard mode, provide the following values:
    1. In the Server name box, enter or select the name or IP address of the database server.
    2. If the database server is not configured to accept connections on the default port (1521), enter the port number that is used for Oracle connections in the Server port box.
    3. In the Oracle SID box, enter the system identifier.
    4. In the User name box, enter an Oracle account that has the necessary permissions.
    5. In the Password box, enter the password for the specified user name.
  2. If you select TNSNAME mode, provide the following values:
    1. In the Connect identifier box, enter connect identifier (TNS alias) of the database.
    2. In the User name box, enter an Oracle account that has the necessary permissions.
    3. In the Password box, enter the password for the specified user name.

...but I don't even know how to find the Oracle SID...I've tried with Server name : localhost, Oracle SID :XE, and SYSTEM/password, but it didn't work.

So I tried with TNSNAME mode, Connect identifier : XE (found on my default tnsnames.ora file ), SYSTEM/password, and it still doesn't work.

It would be really nice of you to reflect on this and share your ideas on how to get throught this step of the process and wether you have any idea to accomplish the rest ( BD restoration/conversion) too!

Thank you very much.

Tagged:
jgarryGeert Gruwezvmat

Best Answer

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Mar 27, 2015 11:54AM Answer ✓
    vmat wrote:
    
    
    EdStevens a écrit:
    [...] or if the database IS local, an incorrectly specified ORACLE_SID.  [...]
    
    That's all my point. I don't even know what the "ORACLE_SID" refers to. "XE"? Something else? I don't know where to find it!
    

    Everything we've seen so far says your ORACLE_SID is XE.

    BTW, ORACLE_SID is the name of an enviornment variable.  The value associated with that variable should be 'XE'

    As I said I'm stucked at level zero here.
    
    I've certainly miss to execute a crucial step to, I don't know, create a standard database from where I could log or something I don't know, but the fact is that I cannot even use impdp on my .dmp file because I fail to log before...
    
    All I've done is installing Oracle DB Express and SSMA concretely. Did I miss something to do after that?
    

    If your install of Oracle DB Express went ok, you should have a database named XE and you should be able to connect to it.

«13456711

Answers

  • Osama_Mustafa
    Osama_Mustafa Member Posts: 6,086 Gold Trophy
    edited Mar 27, 2015 4:12AM

    If i Understand well and you don't mention which option of dmp you ae using ?

    Let 's assume :-

    in impdp you can use option sqlfile parameter to read more about it Check Oracle documentation

    in imp you can use show=y

  • Unknown
    edited Mar 27, 2015 4:34AM

    'It doesn't work' doesn't contain any specifc error message.

    As nobody is at your desk, there are no clues.

    It would be really nice if your post would contain more information than

    'My car is broken, please fix my car'

    ---------

    Sybrand Bakker

    Senior Oracle DBA

  • Shivendra Narain Nirala
    Shivendra Narain Nirala Member Posts: 421
    edited Mar 27, 2015 5:06AM

    Hi,

    If you are using Oracle on window then go to the services , here the database name will appear as service otherwise if you are using Linux/Unix , then fire the command ps -ef | grep pmon.

    Thanks ,

    Shivendra Narain Nirala

  • vmat
    vmat Member Posts: 49
    edited Mar 27, 2015 10:30AM

    Indeed, I forgot to report the error message.

    It's an ORA-12514 : TNS:listener does not currently know of service requested in connect descriptor

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Mar 27, 2015 10:36AM
    vmat wrote:
    
    Indeed, I forgot to report the error message.
    
    It's an ORA-12514 : TNS:listener does not currently know of service requested in connect descriptor
    

    And what did you get when you googled 'ORA-12514'?  (You did research the error yourself, didn't you?)

    When I google it, this is the second hit it gets:  https://edstevensdba.wordpress.com/2011/03/19/ora-12514/

    That single post gets over 300 hits per day.  I know for a fact it has resolved the error for scores of people.

    jgarry
  • vmat
    vmat Member Posts: 49
    edited Mar 27, 2015 10:45AM

    In fact, it's quite embarassing because when I try to import the database from the dump file, it gives me an ORA-12560 error as you can see below :

    C:\Users\...\expdp_FULL_pt22_11032015_15433501.dmp>impdp

    Import: Release 11.2.0.2.0 - Production on Ven. Mars 27 15:41:43 2015

    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

    Username: SYSTEM

    Password:

    UDI-12560: operation generated ORACLE error 12560

    ORA-12560: TNS:protocol adapter error

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Mar 27, 2015 10:51AM
    vmat wrote:
    
    In fact, it's quite embarassing because when I try to import the database from the dump file, it gives me an ORA-12560 error as you can see below :
    
    C:\Users\...\expdp_FULL_pt22_11032015_15433501.dmp>impdp
    
    Import: Release 11.2.0.2.0 - Production on Ven. Mars 27 15:41:43 2015
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    Username: SYSTEM
    Password:
    
    UDI-12560: operation generated ORACLE error 12560
    ORA-12560: TNS:protocol adapter error
    

    now that's a different error.  Most like caused by trying to connect to a non-existent local database, or if the database IS local, an incorrectly specified ORACLE_SID.  If your database is not on the same machine as on which you are executing impdp, you must specify a net service name connection.

  • vmat
    vmat Member Posts: 49
    edited Mar 31, 2015 5:30AM

    Hello,

    You're right I've googled for this error and followed this : http://www.dba-oracle.com/t_ora_12154_tns_resolve_service_name.htm

    Your site is good as well, but as I said I'm at the point 0 in DB knowledge and it's not often as easy as it may appear to an expert like you undoubtably are.

    For exemple, that's what my tnsnames.ora file looks like :

    XE =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = PC-PB.gl-s.com)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = XE)

        )

      )

    EXTPROC_CONNECTION_DATA =

      (DESCRIPTION =

        (ADDRESS_LIST =

          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

        )

        (CONNECT_DATA =

          (SID = PLSExtProc)

          (PRESENTATION = RO)

        )

      )

    ORACLR_CONNECTION_DATA =

      (DESCRIPTION =

        (ADDRESS_LIST =

          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

        )

        (CONNECT_DATA =

          (SID = CLRExtProc)

          (PRESENTATION = RO)

        )

      )

    ...and thereafter, using  lsnrctl status, I get this, specifying that my services are in an "Unknown" Status :

    >lsnrctl status

    LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 27-MARS -2015 15:

    54:56

    Copyright (c) 1991, 2014, Oracle.  All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

    STATUS of the LISTENER

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

    Alias                     LISTENER

    Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production

    Start Date                26-MARS -2015 17:02:20

    Uptime                    0 days 22 hr. 52 min. 35 sec

    Trace Level               off

    Security                  ON: Local OS Authentication

    SNMP                      OFF

    Default Service           XE

    Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora

    Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\PC-PB\listener\alert\log.xml

    Listening Endpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PC-PB.g-s.com)(PORT=1521)))

    Services Summary...

    Service "CLRExtProc" has 1 instance(s).

      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Service "PLSExtProc" has 1 instance(s).

      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

    The command completed successfully

    ...so I deduced I should try to connect with a Connection Identifier "CLRExtProc" instead of "XE"...but even if the loading after this trial took more time than before, I finally get the same  Error 12514...

  • vmat
    vmat Member Posts: 49
    edited Mar 27, 2015 11:25AM
    EdStevens a écrit:
    [...] or if the database IS local, an incorrectly specified ORACLE_SID.  [...]
    

    That's all my point. I don't even know what the "ORACLE_SID" refers to. "XE"? Something else? I don't know where to find it!

    As I said I'm stucked at level zero here.

    I've certainly miss to execute a crucial step to, I don't know, create a standard database from where I could log or something I don't know, but the fact is that I cannot even use impdp on my .dmp file because I fail to log before...

    All I've done is installing Oracle DB Express and SSMA concretely. Did I miss something to do after that?

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Mar 27, 2015 11:51AM
    vmat wrote:
    
    Hello,
    
    You're right I've googled for this error and followed this : http://www.dba-oracle.com/t_ora_12154_tns_resolve_service_name.htm
    
    Your site is good as well, but as I said I'm at the point 0 in DB knowledge and it's not often as easy as it may appear to an expert like you undoubtably are.
    
    For exemple, that's what my tnsnames.ora file looks like :
    
    XE =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = PC-PBOINNARD.globalliance-solutions.com)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = XE)
        )
      )
    
    
    EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        )
        (CONNECT_DATA =
          (SID = PLSExtProc)
          (PRESENTATION = RO)
        )
      )
    
    
    ORACLR_CONNECTION_DATA = 
      (DESCRIPTION = 
        (ADDRESS_LIST = 
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
        ) 
        (CONNECT_DATA = 
          (SID = CLRExtProc) 
          (PRESENTATION = RO) 
        ) 
      ) 
    
    
    
    ...and thereafter, using  lsnrctl status, I get this, specifying that my services are in an "Unknown" Status :
    
    >lsnrctl status
    
    LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 27-MARS -2015 15:
    54:56
    Copyright (c) 1991, 2014, Oracle.  All rights reserved.
    
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
    Start Date                26-MARS -2015 17:02:20
    Uptime                    0 days 22 hr. 52 min. 35 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Default Service           XE
    Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
    Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\PC-PB\listener\alert\log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PC-PB.g-s.com)(PORT=1521)))
    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    
    ...so I deduced I should try to connect with a Connection Identifier "CLRExtProc" instead of "XE"...but even if the loading after this trial took more time than before, I finally get the same  Error 12514...
    

    Notice that in the services list, there is no listing for 'XE'.  That means your database (whose name is .. 'XE') is not registered with the listener. And the reason it is not registered with the listener?  Do we know that the database is even started?

    Let's get back to basics here and establish a few things I don't see in this thread..

    1- is the database executing on your desktop machine, or is it on some other (we'll call 'server')?

    2 - did you execute the above 'lsnrctl' command on the same machine on which the database is running?

    3 - if the datbase is on a windows machine (as it appears to be) is the Windows service (OracleServiceXE) started?

    4 - At a command prompt on the machine where the database is running, and with the OracleServiceXE started, what do you get when you do the following:

        c:\  sqlplus / as sysdba

        sql>  select * from v$instance;

This discussion has been closed.