Automatic backup of Oracle database

  • 2020-05-27 07:25:07
  • OfStack

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

I believe that a number of system administrators are doing the same job every day - data backup. 1 once a day of negligence, and this day just happened to the system failure, need to carry out data recovery, then at this time can do nothing. If setting 1 fixed time every day, the system carries on the backup automatically, that much better! The following author combined with practical experience, talk about 1 talk about UNIX environment Oracle database automatic backup, in order to play a role in attracting jade.

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 the entire database mode. This article intends to adopt the user mode. Before backup, you should first set up a backup directory to accommodate backup files. For example, you can set up a /backup directory. We can then create two separate files ora -- backup and tar -- backup in the Oracle directory of UNIX (or any other directory). Note that the first file needs to initialize the parameters of Oracle, so for convenience, put the initialization command into a file (ora -- env) and call it from the first file.

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

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


Contents of ora -- backup:

Initialize the Oracle database

Give the date of the day to the variable rq

Clear the /backup directory

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 located in the /backup directory.

2. Tape backup

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

Contents of tar -- backup documents:

This command backs up files from the /backup directory to tape. In this file, the tar command takes three arguments, of which the r option means copying a file onto a tape without destroying the original contents of the tape, the v option means displaying file information during copying, the f option means adding the tape device name after it to specify where the file should be copied, and the n option means the tape drive does not go down. /dev/rmt/0 represents the first tape drive of the UNIX host, similarly, /dev/rmt/1 represents the second tape drive of the UNIX host, and so on.

Once the ora -- env, ora -- backup and tar -- backup documents have been written, use the following commands:

In this way, all three files become executable files.

3. Offsite backup

As we know, the FTP command is usually used to transfer data between two hosts, but 1 is usually done interactively, that is, manually entering the IP address, user name, password, etc. of the target host. Obviously, this does not meet the requirements of automatic backup. Fortunately, we can do 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 for this file should prevent read access from within the group or from other users. Thus, when a user USES the FTP command, the system will look for the.netrc file in the user's registered directory. If it can be found, the file will be executed first; otherwise, the user will be interactively prompted for a user name, password, etc.
1 3 4


Before using the FTP command, create a directory to hold the backup files on another UNIX machine, which is /pub for this article. 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 document 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 called init that will be executed at the end of the automatic registration process


The # file transfer is set to base 2


Enter the local working directory /backup


Enter the backup host directory /pub

mput �

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


Exit the FTP session process

After writing the.netrc file, use the following command:

chmod 600. netrc

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


4. Start the backup process

Cron is a permanent process that is started and executed by /etc/ rc.local. 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, with values ranging from 1 to 31

month: month, values range from 1 to 12

day of week: week, with values ranging from 0 to 6, with a daily value of 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 within the range between the two digits (including the two digits themselves).

If a field is composed of a series of 1 values separated by commas, 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. Once the editing is complete, you can view it with the crontab-l command at the $prompt of Oracle.

Content of Oracle:

All rights reserved. oracle/ora - backup

Perform a database backup at 23:00 each day

0 2 all sounds /oracle/tar - backup

Backup files to tape at 2 o 'clock daily

0 4 all right reserved

[1] [2] next page

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

Backup 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 the tape and another host. All the system administrators need to do is replace the 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 the tape backup, but also realized the remote backup, the corresponding security has been greatly improved.

On 1 page

Previous page [1] [2]

Related articles: