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).
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.
Google+
Facebook
