Use copy command to copy tables on local or remote database.

Generally we use CREATE TABLE tab1 AS SELECT..... to copy a table's data to some other table.

There are some limitations with this method.

1) Oracle doesn't commit in between while copying data, so undo segment may run out of space.

2) This technique cannot be used to copy LONG type columns in a table.

Let's see how to use copy command to get rid of these problems.

Suppose we want to create a new table tab_new with data of table tab_old. In this case copy command will be used as

COPY FROM username/password@db_name
CREATE tab_new
USING SELECT * FROM tab_old;

This will create a new table tab_new with data of table tab_old.

Some other options which can be used in place of CREATE are

INSERT : Inserts records in an existing table.

APPEND : Creates the target table, if it doesn't exists, and inserts records.

REPLACE : Drops the existing table, creates new table and inserts records.

For more info visit.

How to use rsync over ssh for a secure and fast transfer?

rsync is a free utility for unix based systems which can be used to transfer files between remote machine and local machine. It does same work as rcp, but it's much faster than rcp. The logic behind its agility is that it doesn't transfer the whole file or directory, it uses checksum-search algorithm to transfer only the differences.

rsync in itself doesn't offer any security, but when used over ssh, it is best way to transfer files securely and that also faster than other copying utilities.

rsync can be used over ssh or it can be directly connected to rsync daemon running over the remote machine.

NOTE: rsync cannot be used for transfer between two remote hosts.

Now, we will see how to use rsync over ssh to synchronize files or directories between remote and local machine.

To synchronize directory on local machine with that of remote machine

rsync -aervz "ssh -l USER" --delete HOST:REMOTE_DIR LOCAL_DIR

In the same way way, to synchronize a remote directory with a local directory

rsync -aervz "ssh -l USER" --delete LOCAL_DIR HOST:REMOTE_DIR

 

Let's see what all these options are

a : stands for sync in archive mode, i.e. it offers the functionality that archive offers.

e: specifies the shell program used for communication, default is ssh.

r: transfer recursively from directories

v: stands for verbose mode

z: used for compressing the data before transmitting

delete: deletes the extraneous file at receiving side not present in sending side.

For more info visit

How to connect to a remote SSH server using public/private key cryptography?

Generally we use username and password to connect to a remote SSH server. Connecting using password is a cumbersome and less secure approach. Here are some of the drawbacks of connecting using password.

1) If you use more than one account then you need to remember password for all the accounts.

2) Changing password is an annoying task and you need to communicate password change to everyone who is using shared account.

3) Passwords are not as secure way of authentication as using keys. Each time you use password, it is passed over the network for authentication.

Now, let's see what is a key?. When we use keys to authenticate over the network, we actually use public/private key cryptography for authentication.

How does a public/private key cryptography work?

In this method, we generate two keys, a public key and a private key. Public key is known to everyone, we can also transfer it over the network. Private key is known only to us, we do not transmit it over the network, nor do we tell it to anyone. Private key is present only on your local machine and that also in a way, that only the authorized account/user can access it.

Now, when both the keys are generated, we install public key on remote machine and keep private key with us.

Following steps are followed when we try to authenticate to the server using keys.

1) Local machine requests the server for connection.

2) Server sends some data known as challenge, encrypted by public key, to the local machine.

3) Local machine/account uses its private key to decrypt the data and sends it back to the server.

4) If the server finds that both the data(sent and received) match then it allows the connection, otherwise it refuses the connection.

Let's see, how we can actually set key based authentication

1) Generate keys

Run the following program on shell

ssh-keygen

This will generate both, public as well as private key. When this program is run, it asks for the file name in which you want to save the keys and a passphrase for private key. I will discuss passphrase after sometime. For the time being, if you want, you can enter it, or leave it and press enter. Supposing, you gave file name for the keys to be my_secret_key then two files will be generated.

my_secret_key will have the private key.

my_secret_key.pub will have the public key.

2) After the keys are generated, you will have to install public key on the remote server. For this, secure copy public key from your local machine to remote machine.

scp /home/XYZ/my_secret_key.pub remote_user@host.com:/home/remote_user/

Now the public key is copied to the home directory of remote_user.

3) After this, log in to the remote host using ssh with the account for which you want to install the public key, in this case it is remote_user. This is the last time you will be logging using your password.

Make .ssh directory inside your home directory, only if it is not present. Assuming that you are in home directory.

mkdir ./.ssh (if not present)

Note: This directory is hidden, so use ls -a to check for its presence.

If .ssh directory is present then check for the file authorized_keys. If present, then append the content of my_secret_key.pub into it as

cat my_secret_key.pub >> /home/remote_user/.ssh/authorized_keys

or else, make the file.

Anyways, append will make the file if not present, so you need not make it explicitly. You only have to take care that you might not delete someone else's public key present for the same account.

Your public key is installed on the remote machine.

Come back to your local machine. Remember you entered a passphrase(if entered) while generating keys. This passphrase will be used to encrypt your private key. The private key will be stored in an encrypted form, if passphrase is used, on the local machine. So, even in the case your encrypted private key becomes vulnerable, nobody will be able to decrypt it and use it to decrypt challenge(which the server sends), unless they know the passphrase. Now you get the importance of passphrase.

Everything set, you can try connecting to remote SSH server using keys.

ssh -i my_secret_key remote_user@host.com

It will ask for your passphrase(if set).

After you enter the passphrase, you are connected to the server.

The best part of connecting by this approach is that your password is not passed over the network, not even your passphrase.

If you don't want to enter your passphrase again and again, you can set up your passphrase in an agent. An agent is a program which remembers the passphrase for you, and whenever you try to use SSH client to connect to the host, it enters the passphrase on your behalf.

For more info visit

How to use FTP in a shell script?

FTP is a standard protocol for file transfer. With FTP, files can be transferred to and from a remote host. Remote host has a FTP server installed on it which listens to FTP clients. FTP client is the program which communicates with FTP server.

Suppose that a user has an account on FTP server with username name and password pass.

Now, let's write a script to transfer a file from a remote host to local machine.

#! /bin/sh 
ftp -in host/ip<<END 
user name pass 
cd /var/myremotedir 
lcd /var/mylocaldir 
get file 
close 
bye 
END 
echo 'transfer completed' 

 In the above script, we are transferring file from remote directory /var/myremotedir to local directory /var/mylocaldir. cd is used to change directory at remote server. lcd is used to change directory at local machine. get is used to transfer file from remote directory to local directory. Similarly, we can use put to transfer file from local directory to remote directory. Note: Here cd, lcd, get are ftp commands and not unix commands. FTP server should be installed and listening on remote machine for FTP client to communicate. The username and password used by FTP client should be registered with FTP server.

For more info visit