1

:) I'm not even CLOSE to a DBA, just an engineer that finds myself learning Oracle. Any help is much appreciated!

Here's my question (which is a lot simpler than the sea of words which follow would seem to indicate):

How do I use sqlldr to load data files into my VM database from my MacBook when the following conditions are true:

  1. My database is in a Linux VM installed using VirtualBox (https://www.virtualbox.org/wiki/Downloads);

  2. The database itself was downloaded from the Oracle DB Developer VM (a.k.a. "appliance"), located here: https://www.oracle.com/database/technologies/databaseappdev-vm.html)

  3. I am running SQL Developer from my host desktop (my MacBook) and connected to the database inside the VM by 'talking' to port 1521 on my machine, which is setup to send traffic to port 1521 on the Linux image;

    enter image description here

  4. I created 6 tables from my host desktop version of SQL Developer, called D, M, P, R, S, and T;

    enter image description here

  5. All the data I want to load into these tables and the files I need to do the load (e.g., the control file, etc.) are located on my MacBook here: .../Desktop/Work/Database/DATA_TABLE/

  6. sqlldr exists inside my VM image (which I confirmed by getting back the expected output from a no-argument run of sqlldr inside the VM);

  7. I do not have sqlldr on my MacBook.


I'd like to be able to run the sqlldr command from my MacBook and have it do the load on the VM side. Here's what I imagine as possible scenarios:

(a) Issue the sqlldr command to run on the VM through an SSH connection to it...

ssh -p 2222 oracle@localhost

...with the VM accessing files through a mounted connection:

https://techsupport.screenplay.com/hc/en-us/articles/360035782711-Creating-a-Shared-Folder-between-your-Mac-running-Catalina-and-a-Virtual-Machine

One problem with this, though --- I ran the above ssh command, and the connection worked, but sqlldr could not be found:

enter image description here

(b) Forget ssh-ing, instead installing sqlldr on my MacBook, as shown in the link below...

https://medium.com/@niharikabitra/how-to-install-sqlldr-4541e91e67a

...then run my sqlldr commands from my MacBook.

Of course, this leaves me wondering --- how does the data actually make it to the VM database? For that matter, how did the tables I created above get into the database? Are they even there? Is all this happening through port 1521?


Putting the "solutions" I've imagined aside, what is the usual way folks do this? Say, an Oracle instructor using a VM in a class he or she is teaching?

For instance, this link seems pretty relevant...

https://www.thatjeffsmith.com/archive/2018/10/yes-sqlloader-is-faster-at-loading-records/

...because it runs the following type of sqlldr command:

sqlldr hr/oracle@localhost:1521/orcl CONTROL=TABLE_EXPORT_DATA.ctl LOG=run1.log BAD=records.bad

BUT, IT'S NOT CLEAR TO ME HOW HIS HOST MACHINE (IN HIS CASE, WINDOWS) EVEN KNOWS WHAT THE SQLLDR COMMAND EVEN IS. Did he install it on his Windows machine, or is the port forwarding somehow asking the VM version of sqlldr to run?

Thanks for ANY help here,

Sincerely, a confused dude,

Justin


EDIT TO INCLUDE THIS IMAGE FOR ED STEVENS:

enter image description here


EDIT TO GIVE MY FINAL RESOLUTION:

@EdStevens, @Suresh, and @Albert Godfrind, thank you for all of your input. Combining this input, and relying on Albert's installation instructions for Oracle Instant Client, I was able to finally load my data. Points of note are as follows:

  1. Instead of downloading the .zip files, I downloaded the .dmg files, which worked just great.

  2. I then combined the files from the different .dmg files into one directory per the instructions given at the download link, then I moved the combined directory (instantclient_19_8) from my Downloads folder into the /usr/local/oracle/ directory, as Albert suggested via his recommended ORACLE_HOME environment variable.

  3. Because I am on Big Sur, where zsh seems to be the default, I elected to modify my ~/.zshrc file instead of my ~/.bash_profile file. The added lines are similar to those from Albert, with one exception --- the TNS_ADMIN environment variable:

# Setup for Oracle / SQLPLUS
export ORACLE_HOME=/usr/local/oracle/instantclient_19_8
export PATH=$ORACLE_HOME:$PATH
export DYLD_LIBRARY_PATH="$ORACLE_HOME"
export SQLPATH="$ORACLE_HOME"
# export TNS_ADMIN="/usr/local/oracle/network/admin"
export TNS_ADMIN="$ORACLE_HOME/network/admin"
export NLS_LANG="AMERICAN_AMERICA.UTF8"

I do not have a tnsnames.ora file because, "[b]y default, when you install Instant Client, Oracle Universal Installer does not include a sample tnsnames.ora file nor the Oracle Net Configuration Assistant utility normally used to create it," as noted in Section 4.2.3.2 here:

https://docs.oracle.com/cd/B19306_01/install.102/b14312/post_install.htm

Therefore, I simply specified the TNS_ADMIN directory already created in the downloaded Oracle Instant Client directory structure.

  1. I created 6 separate parfiles, one for each table, so that I could simply have a single parameter in the sqlldr command. For example, for table 'D', the parfile looks like this:
userid=JUSTIN/oracle@localhost:1521/orcl
control=/Users/ricej/Desktop/Work/Database/DATA_TABLE/input_files/D/D.ctl
data=/Users/ricej/Desktop/Work/Database/DATA_TABLE/input_files/D/D.ldr
log=/Users/ricej/Desktop/Work/Database/DATA_TABLE/output_files/D.log
bad=/Users/ricej/Desktop/Work/Database/DATA_TABLE/output_files/D.bad
discard=/Users/ricej/Desktop/Work/Database/DATA_TABLE/output_files/D.dsc
direct=true

(P.S., @EdStevens, I certainly need more descriptive table names --- Your comment is noted. :))

All said and done, I am going to select the resolution from @Albert Godfrind as the winner, but I want to thank @EdStevens for his prompt reply and back-and-forth exchange, which was very helpful for a new guy like me trying to learn the basics. Thank you all!

2 Answers 2

1

What you are asking is exactly the way I use my databases that I have running in a Linux VM.

The first thing to do is to download and install the Oracle Instant Client for macOS. You can get it from here: https://www.oracle.com/database/technologies/instant-client/macos-intel-x86-downloads.html. This contains all essential command line tools you need: sqlplus, sqlldr, imp/exp, impdp/expdp

Download the Basic, SQL*Plus and Tools packages. Simplest is to download them as zip, then unzip them all in the same place. On my mac, I placed everything in /usr/local/oracle/instantclient_19_8 i.e. I use a separate folder for each version, but you can use any location.

Installation instructions:

  1. Download from https://www.oracle.com/database/technologies/instant-client/macos-intel-x86-downloads.html
  • Basic Package: instantclient-basic-macos.x64-19.8.0.0.0dbru.zip
  • SQL*Plus Package: instantclient-sqlplus-macos.x64-19.8.0.0.0dbru.zip
  • Tools Package: instantclient-tools-macos.x64-19.8.0.0.0dbru.zip
  1. Unzip
unzip instantclient-basic-macos.x64-19.8.0.0.0dbru.zip   -d /usr/local/oracle/
unzip instantclient-sqlplus-macos.x64-19.8.0.0.0dbru.zip -d /usr/local/oracle/
unzip instantclient-tools-macos.x64-19.8.0.0.0dbru.zip   -d /usr/local/oracle/
  1. Update ~/.bash_profile

This is so that the command line tools are executable from your command line.

# Oracle / SQLPLUS setup
export ORACLE_HOME=/usr/local/oracle/instantclient_19_8
export PATH=$ORACLE_HOME:$PATH
export DYLD_LIBRARY_PATH="$ORACLE_HOME"
export SQLPATH="$ORACLE_HOME"
export TNS_ADMIN="/usr/local/oracle/network/admin"
export NLS_LANG="AMERICAN_AMERICA.UTF8"
  1. If necessary copy any customizations from previous release
cp -f /usr/local/oracle/instantclient_19_3/login.sql  /usr/local/oracle/instantclient_19_8
cp -f /usr/local/oracle/instantclient_19_3/glogin.sql  /usr/local/oracle/instantclient_19_8
  1. Remove quarantine from all executables

This is important for Catalina since those command line tools are automatically quarantined.

sudo xattr -d com.apple.quarantine /usr/local/oracle/instantclient_19_8/*
  1. Remove previous release if necessary
rm -rf /usr/local/oracle/instantclient_18_1

Usage

You can now use sqlldr to load csv files from your mac into your database, just like you would do when connected to the VM. In your mac terminal, just do:

$ sqlldr hr/hr@localhost:1521/orcl ...

Similarly, you can connect to your database from your mac:

$ sqlplus hr/hr@localhost:1521/orcl

You can use tnsnames.ora to keep shortcuts to your databases. Make sure to define TNS_ADMIN to point to a directory where you keep the file. For example:

export TNS_ADMIN="/usr/local/oracle/network/admin"

My tnsnames.ora file is like this:

spatialdb = (
  DESCRIPTION = (
    ADDRESS_LIST = (
      ADDRESS = (PROTOCOL = TCP) (HOST=localhost)(PORT = 1521)
    )
  )
  (
    CONNECT_DATA = (SERVICE_NAME = spatialdb)
  )
)
graphdb = (
  DESCRIPTION = (
    ADDRESS_LIST = (
      ADDRESS = (PROTOCOL = TCP) (HOST=localhost)(PORT = 1521)
    )
  )
  (
    CONNECT_DATA = (SERVICE_NAME = graphdb)
  )
)

In my case it points to two PDBs in my local VM.

2
  • Thanks Albert! See the section called "EDIT TO GIVE MY FINAL RESOLUTION" that I added to my original post. I selected you as the winner. For my understanding, do you use tnsnames.ora / listener.ora files? I'd love to see what your files look like, if so. I want to see what they do in the context of your setup.
    – Justin
    Feb 28, 2021 at 5:11
  • Yes I also use tnsnames on the Mac. I will update my answer to share a copy. Feb 28, 2021 at 9:20
1

Wow, so much to process ... where to start?!?!

First, realize that the VM could just as easily be a physical server, behind a locked door in a lights-out data center on another continent. Communication between your host (the mac) and the vm is still via the ip network.

connected to the database inside the VM by 'talking' to port 1521 on my machine

NO, you are not talking to port 1521 on your host. The client program (be it SQL Developer, sqlldr, sqlplus, or anything else) sends a request, wrapped in a tcp packet, to the db server's ip address and port 1521. At that address/port is an oracle process called a 'listener'. The listener receives the request then spawns a separate process called a 'dedicated server' to act as the intermediary between the client and the database. Communication between the client and the dedicated server occurrs over a different port, assigned by the listener. The listener is then out of the loop.

how does the data actually make it to the VM database?

the client (sqlldr in this case) passes it (along with instructions on what to do with it) to the 'dedicated server' process running on the db server machine, via the network connection that has been established. That process then communicates this to the database.

how did the tables I created above get into the database?

By your own admission, you created them using SQL Developer. Just like any other client, it requested a connection. That request was received and processed by the listener, and a dedicated server process was created to be the intermediary between your client (SQL Dev) and the database. Presumably, you used the GUI dialogs to define your tables (btw, it would have been better to create table names that are descriptive of the entities they hold) and SQL Dev used those inputs to construct CREATE TABLE sql statements and requested the database to process those statements.

BUT, IT'S NOT CLEAR TO ME HOW HIS HOST MACHINE (IN HIS CASE, WINDOWS) EVEN KNOWS WHAT THE SQLLDR COMMAND EVEN IS. Did he install it on his Windows machine, or is the port forwarding somehow asking the VM version of sqlldr to run?

OK, now this gets down to really, really basic computer stuff that has nothing to do with oracle, sqlldr, or such. Part of the memory of your computer holds a series of 'environment variables'. These are pairs of names and values. One of these variables is called PATH, and the value associated with it is a list of directories (folders). When a command is entered (say, 'sqlldr') the command processor searches the directories listed in PATH for an executable file by that name - 'sqlldr'. That file is then loaded into memory and control passed to it. When control is passed to it, it is given a pointer to the memory location that contains any command-line parameters. So if your command was 'sqlldr username/pswd@orcl parfile=sqlldr.par', when sqlldr is loaded it gets a pointer to the string 'username/pswd@orcl parfile=sqlldr.par'. It is then up to sqlldr to decide how to parse it and what to do with it.

10
  • thank you for the reply!
    – Justin
    Feb 26, 2021 at 20:37
  • Let me start by asking in reverse ... I am aware of environment variables, but perhaps I am unfamiliar with where those variables ARE. Meaning, I would think the VM and my Mac have completely different environment variables. Here's what I am getting at --- for me, 'sqlldr' exists in one and only one place --- the VM. So, where do I execute the 'sqlldr username/pswd@orcl parfile=sqlldr.par' command? I would like to run it from my Mac so that the 'sqlldr' on the VM gets run.
    – Justin
    Feb 26, 2021 at 20:42
  • Perhaps asked another way, ... if I run iTerm2 on my Mac, then run the command 'ssh -p 2222 oracle@localhost', I get prompted for my password, so I provide it, it gets accepted, and then my connection to localhost is good. But then why is 'sqlldr' not able to be found when I try running it on my iTerm2 connection, but it DOES get found from inside the VM itself? (i.e., in the VM's terminal)
    – Justin
    Feb 26, 2021 at 21:09
  • Env variables exist in memory. It's not just the vm vs the host. Every process gets its own, as part of the memory assigned to that process. At the OS level, there are defaults that are assigned to every process the OS runs, but in a lot of cases the process itself is able to create/clear/modify its env vars. If you have not installed the oracle client software (including sqlldr) on your host (mac) then no, it will not exist there, any more than MS Word would exist if you had not installed MS Office.
    – EdStevens
    Feb 26, 2021 at 21:15
  • I'd guess the reason sqlldr is not found in your ssh session is because the environment of that session is not set properly. What do you see when you 'echo $PATH'? What do you see from the command 'env | grep -i ora' ? One other point - it appears that you think there is more sharing between host and VM than there actually is. Yes, the VM utlimately depends on the host's hardware but it's best to think of it as if it were physically separate. Not even the OS itself knows that it is running on a VM.
    – EdStevens
    Feb 26, 2021 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.