Create logical backups server for oracle databases

Taking regular backups for a database is crucial to recover from any disaster or from accidental operations on the database by its users. Backups in Oracle Database can be taken in two ways one is physical and the other is logical. Export/Export Dump is a kind of logical backup that could be taken in Oracle databases. We can build a logical backup server for Oracle databases by using Oracle export dump utility NETWORK_LINK option.

Requirements:

A server(Windows/Unix) with adequate storage, storage depends on the number of redundant backups and the size of backups of your databases.

1. An Oracle XE installation in the backups sever.
2. TNSNAMES entries of backing up databases.
3. Reaching backing up databases by creating database links.
4. Create backups directories in the destination backups database server.
5. Logical backups scripts (Unix .sh, Windows .bat)
6. Scheduling the backup process(UNIX cronjob or Windows Task Scheduler).
7. Perform steps 3-7 for each database you want to be backed up.

Workaround:

Step 1: Prepare a server Windows or Unix one with available storage for your database backups.

Step 2: Install Oracle XE on the newly adopted server.

Step 3: Say for example we are going to back up the ABC database, let's create a TNSNAME entry for the ABC database in tnsnames.ora file in the new server XE database.

ABC =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.x.xx)(PORT = 1521))

      (CONNECT_DATA =

       (SERVER = DEDICATED)

       (SERVICE_NAME = ABC)

      )

  )

Step 4: Create a database link in the new XE instance to access the ABC database.

CREATE DATABASE LINK "ABC"

CONNECT TO SYSTEM

IDENTIFIED BY "password"

USING 'ABC'

Step 5: Create a directory for data pump export backup in the new XE instance. Select or create an operating system directory where you want your export dumps backups will be taken. Name the new directory as ABC_EXP_DIR for example.

CREATE OR REPLACE DIRECTORY 

ABC_EXP_DIR AS 

'D:\ABC\Backup\Export';

Step 6: Create export backup scripts called as ABC_EXPORTS.bat or ABC_EXPORTS.sh and supply the following script.

For Windows .bat file:

EXPDP SYSTEM/password NETWORK_LINK=ABC FULL=Y DIRECTORY=ABC_EXP_DIR DUMPFILE=ABC_FULL_EXP_%date:~10,4%%date:~7,2%%date:~4,2%.DMP LOGFILE=ABC_FULL_EXP_%date:~10,4%%date:~7,2%%date:~4,2%

For Unix .sh file:

echo off;

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=ABC

EXPDP SYSTEM/password FULL=y DIRECTORY=ABC_EXP_DIR DUMPFILE = ABC_FULL_EXP_`date +%d%m%y_%H%M%S`.DMP LOGFILE = ABC_FULL_EXP_`date +%d%m%y_%H%M%S`.LOG

EOF

Step 7: Create a scheduled task from the operating system to take the export backups on a regular basis.

Task creation in Windows:

1. Go to Control Panel > Task Scheduler
2. Under Actions click Create Task.
3. Under the General tab of Create Task.
4. Give the task a name like ‘Daily ABC Exports’.
5. Choose run whether the user is logged on or not.
6. Go to the Actions Tab of Create Task.
7. Click New will open the New Action window.
8. Select Start a program.
9. In the Program/Script option provide the path of the script file created above in step 6.
10. Click ok to save.
11. Go to the Triggers tab.
12. Click New will open the New Trigger window.
13. Choose On a schedule from drop-down list of Begin the task.
14. Under Settings choose Daily, provide the Start time also the Recur Every 1 day or as your need.
15. Under Advanced Settings choose Enabled.
16. Provide Administrator password if prompted.
17. Click Ok to save and exit.

Task creation in Unix:

1. Login to the oracle user.
2. From the shell prompt type crontab –e.
3. Edit the crontab entry for a daily backup of the ABC database at every night 12:01 AM assuming the file resides under the oracle user home directory: 
4. 0 01 * * sun,mon,tue,wed,thu,fri,sat      sh /home/oracle/ABC_EXPORTS.sh

Note: The ORACLE_BASE, ORACLE_HOME, LD_LIBRARY_PATH, and PATH may vary depending on your Oracle installation and also accessing the cron scheduler.