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 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

venerdì, giugno 03, 2016

Instance Needs Recovery: Introduction (2/3)

Part 1
Part 3

Closing a Redo Log file

There is always a CURRENT Redo Log file (one for each Instance of RAC).
When Oracle begin to write the CURRENT Redo Log file, it set the Low SCN (V$LOG.FIRST_CHANGE#) to the current SCN of the database and set the High SCN (V$LOG.NEXT_CHANGE#) to 281474976710655. Think this number as a infinite value.

Oracle do this because it don't know the final value of the High SCN,

When there is a "Log Switch (it appen wheh the CURRENT Redo Log is full or a DBA run "ALTER SYSTEM SWITCH LOGFILE statement) Oracle set the V$LOG.NEXT_CHANGE# column  to current SCN +1, and than begin to use the next Redo Log file.

The operation that set the V$LOG.NEXT_CHANGE# to a finite value, is called "closing the Redo Log file".

Archived Log

Some definition:

"A log switch is the event during which LGWR stops writing to one online redo log group and starts writing to another" (1). "The background process ARCn automates archiving operations when automatic archiving is enabled" (2)

"An archived redo log file is a copy of one of the filled members of a redo log group". "The process of turning online redo log files into archived redo log files is called archiving" (2)

"When running in ARCHIVELOG mode, the log writer process (LGWR) is not allowed to reuse and hence overwrite an online redo log group until it has been archived" (2)

When the log switch occur
(i) LGWR starts writing to another one
(ii) the ARCn process automates archive in backup the CURRENT redolog (3)

so, a user session not need to wait the arichive finish



(1) https://www.bnl.gov/phobos/Detectors/Computing/Orant/doc/database.804/a58397/ch5.htm
(2) https://docs.oracle.com/cd/B19306_01/server.102/b14231/archredo.htm
(3) http://www.datadisk.co.uk/html_docs/oracle/redo.htm
(4) Master Note: Troubleshooting Redo Logs and Archiving (Doc ID 1507157.1)

Part 1
Part 3