This discussion is archived
13 Replies Latest reply: Feb 8, 2013 2:55 AM by 989408 RSS

Please Help, Spooling Data from Remote Server

989408 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Hi,

    again in unix could you try like this:
    tnsping $ORACLE_SID
    Regards.
    Al
  • 6. Re: Please Help, Spooling Data from Remote Server
    989408 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points