Automatically backup Oracle database

  • 2020-05-07 20:36:18
  • OfStack

The ORACLE tutorial you are looking at is: automatically backup the Oracle database. I believe that many system administrators are doing the same thing every day - backing up data. Once a day of negligence, and this day just happened to be a failure of the system, the need for data recovery, then there is no way. If every day set a fixed time, the system automatically backup, that how good! The following author combined with practical experience, talk about 1 talk UNIX environment Oracle database automatic backup, in order to play a role.

We plan to have the database do an export export backup at 23pm, copy the backup file to tape at 2am, and copy the backup file to another UNIX machine at 4am. To do this, we can do the following:

1. Export the database

The export command backs up the data in the database into a binary file, which typically has three modes: user mode, table mode, and overall database mode. This article intends to adopt user mode. Before backup, one backup directory should be established to hold the backup files. For example, one /backup directory can be built. Then we can create two separate files ora -- backup and tar -- backup in the Oracle directory of UNIX (or any other directory). To be clear, the first file needs to initialize the Oracle parameter, so for convenience, we can put the initialization command into a file (let's say the file name is ora -- env) and call it by the first file.

1. The ora -- env file initializes the parameters of Oracle as follows:


ORACLE - SID = ora73; export ORACLE - SID

ORACLE - TERM = sun; export ORACLE - TERM


ORA - NLS32 = $ORACLE - HOME ocommon/nls/admin/data; export ORA - NLS

PATH =. : / usr/ccs/bin: / usr/ucb: $ORACLE - HOME/bin: $PATH; export PATH

DISPLAY=host1:0;export DISPLAY

NLS - LANG = american - america. zhs16cgb231280; export NLS - LANG

2. ora -- backup file export export to the database, the exported file name can be arbitrary, this article as the letter "xx" plus the date of the day, that is, if the date is December 10, the exported file name is "xx1210.dmp", to distinguish from other date backup files.

ora - backup file contents:

. / oracle/ora - env

Initialize the Oracle database

rq= 'date +' % m % d ' '

Assign the date of the day to the variable rq

rm/backup / �

Empty the /backup directory

exp test/test file=/backup/xx $rq.dmp log=/backup/xx $rq.log

This command is used to export the data of the test user (whose password is also test) at the $prompt. The export files and logs are placed in the /backup directory.

2. Tape backup

The tar -- backup file copies the data file exported with the export command onto the tape.

tar - backup file contents:

tar rvf/dev rmt / 0 n/backup / �

This command backs up files from the /backup directory that were generated that day to tape. In this file, the tar command USES three parameters, among which the r option means to copy a file onto a tape without destroying the original contents of the tape, the v option means to display the file information during the copying process, the f option means to add the tape device name after the f option to specify where the file is to be copied, and the n option means that the tape drive does not run. /dev/rmt/0 represents the first tape drive of the UNIX host, in the same way /dev/rmt/1 represents the second tape drive of the UNIX host, and so on.

After writing ora -- env, ora -- backup, tar -- backup, use the following command:

chmod env ora - 755

chmod backup ora - 755

chmod backup tar - 755

In this way, all three files become executable files.

3. Offsite backup

We know that the FTP command is usually used to transfer data between two hosts, but 1 is usually achieved interactively by manually entering the IP address, user name, password, and so on of the target host. Obviously, this does not meet the requirements of automatic backup. Fortunately, we can achieve this by writing a.netrc file. This 1 file must be named.netrc and must be stored in the user registration directory on the machine on which the FTP command is started. The permissions on this file should prevent read access by groups or other users. This way, when the user USES the FTP command, the system will look for the.netrc file in the user's registered directory, and if it can be found, it will execute the file first; otherwise, the user will be interactively prompted for a user name, password, and so on.

Before using the FTP command, you should set up a directory to hold the backup files on another UNIX machine that is used as a backup. The directory set up in this article is /pub. It should be noted that in order to speed up the backup, the transfer rate between the two hosts should be as high as possible, preferably on the same LAN.

.netrc reads as follows:

machine host2

# host2 is the hostname for the backup

login oracle

# oracle is one user on the backup host

password oracle

The password for # oracle users is oracle

macdef init

Define a macro named init that will be executed at the end of the auto-registration process


The # file transfer is set to base 2

lcd /backup

Enter the local working directory /backup

cd /pub

Go to the backup host directory /pub

mput �

Transfer all files in the /backup directory to the backup host


Exit the FTP session process

After the.netrc file has been prepared, use the following command:

chmod 600 .netrc

Thus, the.netrc file can only be accessed by that user.

4. Start the backup process

Cron is a permanent process that is executed by the /etc/ rc.local startup. Cron check/var/spool/cron crontabs / � files in the directory, find time to perform the task and a mission.

Each line of the Crontab file consists of six fields (minutes, hours, day of month, month, day of week, command) separated by Spaces or Tab, where:

minutes: minute domain, with values ranging from 0 to 59

hours: small time domain with values ranging from 0 to 23

day of month: date, values range from 1 to 31

month: month, with values ranging from 1 to 12

day of week: week, values range from 0 to 6, days of the week is 0

command: the command to run

If a field is a wok, the command can be executed in all possible values for that field.

If a field is two digits separated by a hyphen, the command can be executed in the range between the two digits (including the two digits themselves).

If a field is composed of 1 series of values separated by a comma, the command can be executed within the range of those values.

If both the date field and the week field have values, then both fields are valid.

Now, let's write a file to start the automatic backup process. It is important to note that the file can only be Oracle use crontab - e command to edit the user name, or you will not be performed regularly, file name as Oracle, file will be placed on/var/spool/cron/crontabs directory. After editing, you can view it at the $prompt of Oracle with the crontab-l command.

Contents of Oracle:

023 all right reserved. /oracle/ora -- backup

Perform a database backup at 23:00 each day

0 2 all /oracle/tar - backup

Backup files to tape at 2 o 'clock daily

[1]   [2]   next page

The ORACLE tutorial you are looking at is: automatically backup the Oracle database.

0 4, ftp-i host2

Backup the files to another host at 4 o 'clock every day

After the above operation, the system will automatically produce a backup every night, and automatically copy the backup files to tape and another host. What the system administrator needs to do is to replace a tape every few days (depending on the size of the backup file and the capacity of the tape) and clean up the backup directory. This frees them from the hassle of backing up their data and allows them to do other, more meaningful work. The database not only realized tape backup, and the realization of the remote backup, the corresponding security is greatly improved.

    [1]   [2]  

Related articles: