Set up an automatically Oracle database backup with expdp!

 

This article explains how to create an automatically backup for an Oracle schema. This is a response for a friend request.

Contents:

  • Create a backup of your Oracle database.
  • Configure the SSH connection on your Linux server (Oracle Database)  and Linux server (backup pc)
  • Automatic backup (crond)

 

1. Create a backup of your Oracle database:

To create an Oracle backup, I know two tools first rman and the second tool is expdp. In this article, I will use the tool expdp and I will export the HR schema, and If you want to know how to export database or table, look at this url (10g, 11gr1).

db-backup

Oracle database backup

Step 1: Connect with the same user which you installed the Oracle databse (for exemple the user oracle)

# su – oracle

Connect with sys user to grant user privilege and to create a directory for the backup.

$ mkdir /home/oracle/db/backup
$ sqlplus sys as sysdba
SQL> alter user hr identified by hr account unlock;
SQL> create or replace directory hr_dir as '/home/oracle/db/backup';
Directory created.
SQL> grant read, write on directory hr_dir to hr;
Grant succeeded.

Step 2: use the command/tool expdp to export the hr schema for example to the directory hr_dir

$ expdp hr/hr schemas=hr directory=hr_dir dumpfile=hr.dmp logfile=hr.log

You can verify

$ ls  -l /home/oracle/db/backup

2. Configure the SSH connection between Linux server (Oracle databse) and Linux server (backup-pc):

After the export, it is time to copy the backup to another host (backup-pc) and to secure the copy over Ethernet I will use a ssh tunnel and also to create an automatically backup (using crond), I will create a connection using the public/private key.

Step 1: On Linux Server (Oracle Database):

Create a public/private rsa key.

$ ssh-keygen
Generating public/private rsa key pair.
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:
ea:aa:e7:5c:be:84:9f:6d:e8:5f:1d:3c:3e:99:dd:b2 oracle@db

Step 2: On Linux Server (backup-pc):

Create the ssh folder if not exist. For example, I created a user named backup on my backup server.

$ su - backup
$ mkdir ~/.ssh
$ chmod 0700 ~/.ssh
$ touch ~/.ssh/authorized_keys
$ chmod 0644 ~/.ssh/authorized_keys

Step 3: On Linux Server (Oracle Database):

Copy the id_rsa.pub from Linux server (Oracle databse) to the Linux server (backup-pc)

$ su - oracle
$ scp .ssh/id_rsa.pub backup@backup-pc:~

Step 4: On Linux Server (backup-pc):

$ su - backup
$ cat id_rsa.pub > .ssh/authorized_keys

Step 5: On Linux Server (Oracle Database):

  • First solution (synchronization): every time we synchronize the same file (every time the file contains the newest modification)
$ rsync -av -e "ssh" /home/oracle/db/backup/hr.dmp backup@backup-pc:~
  • Second solution: create every time a new copy of the export file, also i recommend you to create backup file with a specific name containing the date of the backup for example.
$ vi /home/oracle/backup.sh
date_backup=$(date +'%d_%h_%y_%H_%M')
expdp hr/hr schemas=hr directory=hr_dir dumpfile=hr.dmp.$date_backup logfile=fhdmp.log.$date_backup
scp /home/oracle/db/backup/hr.dmp.$date_backup backup@backup-pc:~
$ chmod 0700 /home/oracle/backup.sh

Automatic backup (using crond), On Linux server (Oracle database):

If you want to execute the backup every day you can use the crontab (example at 20:00 from Monday to Friday).

$ su – oracle
$ crontab -e
00   20   *   *   1,2,3,4,5   bash /home/oracle/backup.sh

 

Conclusion:

I hope that this article can solve your problem.

 

 

Bookmark the permalink.
Loading Facebook Comments ...

Leave a Reply