13 Replies Latest reply: Feb 8, 2013 4:55 AM by 989408 RSS

    Please Help, Spooling Data from Remote Server

    989408
      Hello Experts,

      Kindly help, I am learning how to spool data from remote server. I need a detailed steps to make settings in my local machine to connect remote Oracle server using SQL*PLUS.

      I installed Oracle 11g release 2 on my Local machine which is 32 bit:+

      SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 8 12:02:30 2013
      Copyright (c) 1982, 2010, Oracle. All rights reserved.

      Enter user-name: SYSTEM
      Enter password:

      Connected to:
      Oracle Database 11g Release 11.2.0.1.0 - Production

      SQL>

      and On remote server Below is the version installed+

      login as: B_DB1user
      B_DB1user@156.1.3.4's password:
      Last login: Thu Feb 7 18:19:35 2013 from 10.10.167.8
      [B_DB1user@bamdb001 ~]$ sqlplus

      SQL*Plus: Release 11.1.0.7.0 - Production on Fri Feb 8 12:06:35 2013

      Copyright (c) 1982, 2008, Oracle. All rights reserved.

      Enter user-name: B_DB1user
      Enter password:

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      With the Partitioning, Real Application Clusters, OLAP, Data Mining
      and Real Application Testing options

      SQL>

      ----------------------------------------------------------------------------------------------------------------------------
      this is what i have in tnsnames.ora

      # tnsnames.ora Network Configuration File: E:\app\managerproj3\product\11.2.0\dbhome_5\network\admin\tnsnames.ora
      # Generated by Oracle configuration tools.

      LISTENER_ORCL =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


      ORCL =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.10.167.8)
      )
      )

      ---------------------------------------------------------------------------------------------------------------------------------
      this is what i have in listener.ora


      # listener.ora Network Configuration File: E:\app\managerproj3\product\11.2.0\dbhome_5\network\admin\listener.ora
      # Generated by Oracle configuration tools.

      LISTENER1 =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.167.8)(PORT = 1521))
      )
      )

      ADR_BASE_LISTENER1 = E:\app\managerproj3

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
      )

      ADR_BASE_LISTENER = E:\app\managerproj3

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


      Database server : ORACLE
      HOSTNAME : BAMDB001
      IP Address : 156.1.3.4


      and below is the script i tried to spool data into local machine using SQL*PLUS

      set colsep ,
      set pagesize 120
      set trimspool on
      set headsep off
      set linesize 1000

      spool D:\test\myfile.csv

      select table_name, tablespace_name from all_tables;

      spool off

      it is successful on local machine...please help me to get data from remote server to local machine?

      Kindly let me know if u require anything.

      Thanks in Advance

      Regards,
      Bluerose
        • 1. Re: Please Help, Spooling Data from Remote Server
          AlbertoFaenza
          Isn't more or less the same question you asked in {message:id=10835497}?

          You need to have a user for the remote database and an entry for the remote database in your tnsnames.ora file

          If you just have to spool from remote database then local database does not need to be involved:
          Edit your tnsnames.ora and add an entry with the remotedb information and you need to have the following information:

          Server name or ip address (BAMDB001?)
          Service name (??)
          Port configured for that database. (??)

          Also you need to be sure that connectivity from your local computer to remote server is granted (no firewall block).
          Only when you have these information you can connect to the remote database from your laptop.

          From the Unix server where you connect to database you can get the information to insert into your local tnsnames.ora by typing:
          tnsping dbname
          Of course you have to replace dbname with your database name.
          Another way is to check the remote tnsnames.ora when you are connected in Unix:
          cat $ORACLE_HOME\network\admin\tnsnames.ora
          and check the configuration there.

          Regards.
          Al
          • 2. Re: Please Help, Spooling Data from Remote Server
            989408
            Thank you Alberto! :-)

            Isn't more or less the same question you asked in Spooling data from remote server to Local Machine?*

            Yes, I did asked this question and I got to know where i am doing wrong. So I installed Oracle 11g release 2 for SQL*PLUS. And I m successfull in spooling local data in my machine.

            You need to have a user for the remote database and an entry for the remote database in your tnsnames.ora file*


            yes I have a user for the remote database i.e., Username: B_DB1user & Password: B_DB1user.
            As I am new to this technology I have minimal knowledge in defining entries in tnsnames.ora


            Server name or ip address (BAMDB001?)*
            Service name (??)*
            Port configured for that database. (??)*

            Server name or ip address = 156.1.3.4
            Service name (??) = I didn't get this
            Port configured for that database. (22??)

            Also you need to be sure that connectivity from your local computer to remote server is granted (no firewall block).
            Only when you have these information you can connect to the remote database from your laptop.

            There is a requirment, Some XYZ company is manageing our company data and given us read only access to the database. They provided us the below information to connect via Putty.

            Application : WAMS
            Database Server : ORACLE
            Hostname : BAMDB001
            IP Address : 156.1.3.4


            To access the remote database using putty i hade given the following credentials:

            login as: B_DB1user
            B_DB1user@156.1.3.4's password: p@ssw0rd
            Last login: Fri Feb 8 13:06:53 2013 from 10.10.167.8
            [B_DB1user@bamdb001 ~]$ sqlplus

            SQL*Plus: Release 11.1.0.7.0 - Production on Fri Feb 8 15:00:04 2013

            Copyright (c) 1982, 2008, Oracle. All rights reserved.

            Enter user-name: B_DB1user
            Enter password: B_DB1user

            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
            With the Partitioning, Real Application Clusters, OLAP, Data Mining
            and Real Application Testing options

            SQL>

            I m successfully connected to that remote server in putty and also can access the database.

            When i run a spooling script, it runs without any error but file dint generated in mentioned path(local machine)


            P.S: I am newly joined Technical assistant to one of the reputed company and this is my first task given to me. I am learning lot of things from this forum. Please help me to understand it in better way.


            Regards,
            Bluerose
            • 3. Re: Please Help, Spooling Data from Remote Server
              AlbertoFaenza
              Check my previous message and try:

              Connect to unix:
              login as: B_DB1user
              B_DB1user@156.1.3.4's password: p@ssw0rd
              Last login: Fri Feb 8 13:06:53 2013 from 10.10.167.8
              [B_DB1user@bamdb001 ~]$
              At this point try to type
              [B_DB1user@bamdb001 ~]$ tnsping ORACLE
              If this is not giving you a connection detail check the entries in tnsnames.ora
              [B_DB1user@bamdb001 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
              Regards.
              Al
              • 4. Re: Please Help, Spooling Data from Remote Server
                989408
                I tried what you have asked me to and below is the result I got:

                login as: B_DB1user
                B_DB1user@156.1.3.4's password: p@ssw0rd
                Last login: Fri Feb 8 14:59:58 2013 from 10.10.167.8

                [B_DB1user@bamdb001 ~]$ tnsping ORACLE

                TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 08-FEB-2013 15:29 :02

                Copyright (c) 1997, 2008, Oracle. All rights reserved.

                Used parameter files:

                TNS-03505: Failed to resolve name

                [B_DB1user@bamdb001 ~]$


                I also checked for tnsname.ora file n changed the entries as u mentioned:

                # tnsnames.ora Network Configuration File: E:\app\managerproj3\product\11.2.0\dbhome_5\network\admin\tnsnames.ora
                # Generated by Oracle configuration tools.

                LISTENER_ORCL =
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


                ORCL =
                (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = orcl.10.167.8)
                )
                )

                WAMS =
                *(DESCRIPTION =*
                *(ADDRESS = (PROTOCOL = TCP)(HOST = BAMDB001)(PORT = 1521))*
                *(CONNECT_DATA =*
                *(SERVER = ORACLE)*
                * (SERVICE_NAME = 156.1.3.4)*
                *)*
                * )*


                Am I doing it in correct way?

                Regards,
                Bluerose
                • 5. Re: Please Help, Spooling Data from Remote Server
                  AlbertoFaenza
                  Hi,

                  again in unix could you try like this:
                  tnsping $ORACLE_SID
                  Regards.
                  Al
                  • 6. Re: Please Help, Spooling Data from Remote Server
                    989408
                    Hi Alberto,

                    I tried tnsping $ORACLE_SID n below what I got.

                    [B_DB1user@bamdb001 ~]$ tnsping $ORACLE_SID

                    TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 08-FEB-2013 15:42:30

                    Copyright (c) 1997, 2008, Oracle. All rights reserved.

                    Used parameter files:

                    TNS-03505: Failed to resolve name
                    [B_DB1user@bamdb001 ~]$


                    Regards,
                    Bluerose
                    • 7. Re: Please Help, Spooling Data from Remote Server
                      989408
                      Is there anything I missed defining like Environment Variables
                      or
                      might be the problem in remote server?
                      • 8. Re: Please Help, Spooling Data from Remote Server
                        AlbertoFaenza
                        bluerose999 wrote:
                        Is there anything I missed defining like Environment Variables
                        or
                        might be the problem in remote server?
                        Yes, you missed the basic information you need to connect to a database server (tns entry information) and I'm trying to understand.
                        You don't even know how your database is called and which port it is listening.

                        Without these information it' quite difficult to configure your local tnsnames.ora

                        When you type sqlplus in unix without providing the database, sqlplus is connecting to a default database which is either defined by environment variable $ORACLE_SID or $TWO_TASK.

                        For general information:
                        Another variable which may be set is TNS_ADMIN. It will
                        point to a directory containing the tnsnames.ora file. This
                        file contains the mapping between the connect identifier
                        (the "cid" in scott/tiger@cid) and the DB's hostname, port etc.
                        If TNS_ADMIN is not set, then a system specific search path is
                        used. Most commonly $ORACLE_HOME/network/admin/tnsnames.ora
                        is used.

                        You need to have the following information that you have to put in your local tnsnames.ora
                        WAMS =
                        (DESCRIPTION =
                        (ADDRESS = (PROTOCOL = TCP)(HOST = *156.1.3.4*)(PORT = *???*))
                        (CONNECT_DATA =
                        (SERVER = *DEDICATED OR SHARED*)
                        (*SID OR SERVICE NAME* = *NAME OF DB INSTANCE*)
                        )
                        )
                        Can't you ask these information to the company that is providing the service?

                        Regards.
                        Al

                        Edited by: Alberto Faenza on Feb 8, 2013 11:32 AM
                        • 9. Re: Please Help, Spooling Data from Remote Server
                          989408
                          Hi Alberto,

                          Few days ago I read in some forum that we can use WinScp tool to transfer files from remote server to our local machine.

                          Is it possible that I can do spooling in remote server itself and later using WinScp I can transfer that file to my local machine?

                          Kindly let me know is this possible, mean while i will try this option.

                          Regards,
                          Bluerose
                          • 10. Re: Please Help, Spooling Data from Remote Server
                            AlbertoFaenza
                            bluerose999 wrote:
                            Hi Alberto,

                            Few days ago I read in some forum that we can use WinScp tool to transfer files from remote server to our local machine.

                            Is it possible that I can do spooling in remote server itself and later using WinScp I can transfer that file to my local machine?

                            Kindly let me know is this possible, mean while i will try this option.

                            Regards,
                            Bluerose
                            If you don't have any quota limit in your Unix environment and you are allowed to write in a directory then you can spool to that directory and transfer the file later on with any file transfer tool that it is working.

                            This can be FTP, SFTP or whatever has been allowed to be used by Unix administrators.

                            Regards.
                            Al

                            Edited by: Alberto Faenza on Feb 8, 2013 11:35 AM
                            • 11. Re: Please Help, Spooling Data from Remote Server
                              989408
                              bluerose999 wrote:
                              Is there anything I missed defining like Environment Variables

                              or
                              might be the problem in remote server?

                              Yes, you missed the basic information you need to connect to a database server (tns entry information) and I'm trying to understand.
                              You don't even know how your database is called and which port it is listening.

                              Without these information it' quite difficult to configure your local tnsnames.ora

                              When you type sqlplus in unix without providing the database, sqlplus is connecting to a default database which is either defined by environment variable $ORACLE_SID or $TWO_TASK.

                              For general information:
                              Another variable which may be set is TNS_ADMIN. It will
                              point to a directory containing the tnsnames.ora file. This
                              file contains the mapping between the connect identifier
                              +(the "cid" in scott/tiger@cid) and the DB's hostname, port etc.+
                              If TNS_ADMIN is not set, then a system specific search path is
                              used. Most commonly $ORACLE_HOME/network/admin/tnsnames.ora
                              is used.

                              You need to have the following information that you have to put in your local tnsnames.ora


                              WAMS =
                              +(DESCRIPTION =+
                              +(ADDRESS = (PROTOCOL = TCP)(HOST = *156.1.3.4*)(PORT = *???*))+
                              +(CONNECT_DATA =+
                              +(SERVER = DEDICATED OR SHARED)+
                              +(*SID OR SERVICE NAME* = NAME OF DB INSTANCE)+
                              +)+
                              +)+


                              Can't you ask these information to the company that is providing the service?

                              Regards.
                              Al

                              Yeah! I got it now! Thanks a ton Alberto for your patience n detailed solution. I am able to connect now. The problem was in tnsname.ora only. Thank you so much.

                              Regards,
                              Bluerose
                              • 12. Re: Please Help, Spooling Data from Remote Server
                                AlbertoFaenza
                                I'm curious now,

                                how did you find details about your database connection?

                                Regards.
                                Al

                                Edited by: Alberto Faenza on Feb 8, 2013 11:44 AM
                                • 13. Re: Please Help, Spooling Data from Remote Server
                                  989408
                                  I called that technical guy who has provided me a remote server credentials. He sent me the tnsname.ora file according to my local machine entires..and also i tried with winscp. And more over I am able to copy the spooled data files directly from remote server to my local machine.

                                  thank u once again! I learnt something new today!

                                  Regards
                                  Bluerose