Create logical backups server for oracle databases

Background:

Taking regular backups for database is crucial to recover from any disaster or from accidental operations on database by its users. Backups in Oracle Database can be taken in two ways one is physical and other is logical. Export/Export Dump is a kind of logical backups that could be taken in oracle databases. We can build a logical backups 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.
An oracle XE installation in the backups sever.
TNSNAMES entries of backing up databases.
Reaching backing up databases by creating database links.
Create backups directories in destination backups database server.
Logical backups scripts (Unix .sh, Windows .bat)
Scheduling the backup process(UNIX cronjob or Windows Task Scheduler).
Perform steps 3-7 for each database you want to be backed up.

Workaround:

Prepare a server Windows or Unix one with available storage for your database backups.
Install oracle XE on the newly adopted server.

Say for example we are going to backing up ABC database, lets create a TNSNAME entry for 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)
      )
  )

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'

Create 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';
```

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

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

For windows:

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

For Unix:

Login to the Oracle installed user.
From the shell prompt type crontab –e.
Edit the crontab entry for a daily backup of ABC database at every night 12:01 AM assuming the file resides under the oracle user home directory:

0 01 * * sun,mon,tue,wed,thu,fri,sat      sh /home/oracle/ABC_EXPORTS.sh

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

Leave a Reply Cancel reply