Export/Import: on the fly. A new challenge.

This is the scenario.

I have a zipped export file on the source machine and I want to import it into a database on different host.

All is done on the fly.

In this picture:

• "src_hst", is the source machine
• "trg_db", is the target database
• "trg_hst", is the target machine
• "trg_usr", is the user, on the target database, to be imported
• "gunzip", is the unzip command
• "imp", is the import command
• "net", is the network between source host and target host 

Configuring the target and the source

First of all, you have to configure the source and target machine. To do this, please see "Configuring the target and the resource" of my previous post. 

Now you can run the import.

The taget

[oracle@trg_hst]$ mknod /tmp/trg_usr.pipe p
[oracle@trg_hst]$ nohup imp system/manager \
> fromuser=src_usr touser=trg_usr \
> file=/tmp/trg_usr.pipe \
> log=/tmp/dpdump/trg_usr.YYYYMMDD.log \
> 2>&1 > /tmp/dpdump/trg_usr.YYYYMMDD.hohup &

Please, note that I used YYYYMMDD. In this case I have to use the current date. For example: 20160924

The source

nohup gunzip -c src_hst.dmp.gz | ssh oracle@trg_hst 'cat > /tmp/trg_usr.pipe' &

Export/Import: on the fly

Here I show how to run export and import from a machine to another, just using pipes.

This is the configuration:

• Two unix machine
• source => HP-UX B.11.11
• target => Red Hat Enterprise Linux 7.1
•  Two database Oracle
• source => Oracle 9.2.0.6
• target => Oracle 11.2.0.4

This is what we want:

In this picture:

• "src_hst", is the source machine
• "src_db", is the source database
• "src_usr", is the user, on the source database, which we export
• "trc_db", is the target database
• "trc_hst", is the target machine
• "trg_user", is the user, on the target database, to be imported
• "exp", is the export command
• "imp", is the import command
• "net", is the network between src_db and trg_db

To do this job (exp/imp), I configure the target machine, first and the source machine at least.

Starting from the target, you start to create a tube from the end to the biginning. In this way, when you fill the tube (start the export), the data flow through it and when they arrived to the end, they find someone (the import) that empty the tube.

Configuring the target and the source

This is the step number 0. In order to avoid the password when you connect from source to the target, you need to exchange the ssh-key.

You have just to run the "ssh-keygen" command on the source, and press RETURN any time. 

[oracle@src_hst]$ ssh-keygen (see update)
Generating public/private rsa key pair.
Please be patient....   Key generation may take a few minutes
Enter file in which to save the key (/home/oracle/.ssh/id_rsa): Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
bc:23:30:20:37:20:5b:9b:17:f5:5f:f9:10:cd:62:bd oracle@ht1-jim
The key's randomart image is:
+--[ RSA 2048]----+
|o . ...     .+   |
|.+ o . .    oo+  |
|o * .   .  .+. . |
| o +   . . . oE  |
|    o   S .   .  |
|     o   .       |
|      . o        |
|       . .       |
|                 |
+-----------------+


At this point you have two files in the /home/oracle/.ssh, direcotry

• id_rsa
• id_rsa.pub

You have to copy the content of the /home/oracle/.ssh/id_rsa.pub (following an example).....

[oracle@src_hst]$ cat /home/oracle/.ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEArRfiCNq6nodHj5EKWeQ6ILOe/yICYVONaZa0SKF09msdZvfXYU+rN55fbubonwftyUjZbMRnMIoKTrsxi+UhFe8jIS2l/oVbOerHlCeyhXgCu+NrGM6Q56QPmrJgBMm7b1rb2Tsnh3jdTo3R00i7aCdaLvrVL3oK7IFS2jaVGGhJYTU1LXi8mhXQ3oQXkQ2bHkGGQ9Z68CD4cyYZ4St6lF7xLamwtvXwpC+M8m4s6zM/YiYTKib6RohLt7+kfXfzF3Boyx9AXOmTu93IS2GsRaiMcGVW/GH6T7qVvwPQej0vxpALOf2wdkRL5uSjVdGFUxwgXWDOtKlFDoWHEyYuuw== oracle@src_hst

.....in the /home/oracle/.ssh/authorized_keys file of the target host (following an example)

[oracle@trc_hst]$ cat /home/oracle/.ssh/authorized_keys
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEArRfiCNq6nodHj5EKWeQ6ILOe/yICYVONaZa0SKF09msdZvfXYU+rN55fbubonwftyUjZbMRnMIoKTrsxi+UhFe8jIS2l/oVbOerHlCeyhXgCu+NrGM6Q56QPmrJgBMm7b1rb2Tsnh3jdTo3R00i7aCdaLvrVL3oK7IFS2jaVGGhJYTU1LXi8mhXQ3oQXkQ2bHkGGQ9Z68CD4cyYZ4St6lF7xLamwtvXwpC+M8m4s6zM/YiYTKib6RohLt7+kfXfzF3Boyx9AXOmTu93IS2GsRaiMcGVW/GH6T7qVvwPQej0vxpALOf2wdkRL5uSjVdGFUxwgXWDOtKlFDoWHEyYuuw== oracle@src_hst

Now you are ready to start the import/export job.

Configuring the target

[oracle@trc_hst]$ mkdir /home/oracle/IMPORT
[oracle@trc_hst]$ mknod /tmp/impTrcUsr.pipe p
[oracle@trc_hst]$ nohup imp trc_usr/trc_pwd \
                        file=/tmp/impTrcUsr.pipe \
                        parfile=/home/oracle/IMPORT/impTrcUsr.par \
                        > /home/oracle/IMPORT/impTrcUsr.nohup 2>&1 &

I used a parfile. Because I don't want to import the index, I used these options:

[oracle@trc_hst]$ cat /home/oracle/IMPORT/impTrcUsr.par
BUFFER=5000000
INDEXES=N
 

Configuring the source

[oracle@src_hst]$ mkdir /home/oracle/EXPORT
[oracle@src_hst]$ mknod /tmp/expTrcUsr.pipe p
[oracle@src_hst]$ nohup ssh oracle@src_hst "cat > /tmp/impSrcUsr.pipe" < /tmp/expTrcUsr.pipe &
[oracle@src_hst]$ time nohup exp src_usr/src_pwd \
                       file=/tmp/expTrcUsr.pipe \
                       parfile=/home/oracle/EXPORT/expSrcUsr.par \
                       > /home/oracle/EXPORT/expSrcUsr.nohup 2>&1 &

I used a parfile. Because I want to export only 2 tables, I used these options:

[oracle@src_hst]$ cat /home/oracle/EXPORT/expSrcUsr.par
DIRECT=Y
BUFFER=5000000
RECORDLENGTH=5000000
TABLES=(MYTAB01, MYTAB02)
 

Note on the import

I'm using a pipe instead a standard file. Except this, all is usual.

Note on the export

* I'm using a pipe instead a standard file
* I'm using a remote command "cat", in order to write the exported data into the remote pipe
* The source pipe is used as standard of input of ssh command. In this way all that is wrote in the source pipe is also wrote in the remote pipe
* I use the "time" command, in order to know how long the export/import process takes

Update

19/July/2017
Sometimes the ssh-keygen command want the type of  encryption. In this case you have to use the -t switch. For example

$ ssh-keygen -t rsa