Automatically backup Oracle database
- 2020-05-07 20:36:18
- OfStack
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 - HOME = $ORACLE - HOME; export ORACLE - HOME
ORACLE - SID = ora73; export ORACLE - SID
ORACLE - TERM = sun; export ORACLE - TERM
LD - LIBRARY - PATH = $ORACLE - HOME/lib; export LD - LIBRARY - PATH
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
bin
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
bye
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]