Translation

The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

venerdì, giugno 17, 2016

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 and press RETURN any time. 

[oracle@src_hst]$ ssh-keygen
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 into the /home/oracle/.ssh
  • id_rsa
  • id_rsa.pub

You have to copy the content of the /home/oracle/.ssh/id_rsa.pub (this is 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

.....into the /home/oracle/.ssh/authorized_keys file of the target host (this is 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

Nessun commento: