Automatic offsite backup of database Oracle data

The ORACLE tutorial you are looking at is: offsite automatic backup of database Oracle data. In large-scale commercial applications, remote disaster backup of data is important and indispensable. According to the author's own practical experience, designed a set of concise realization of remote data automatic backup method, for database managers reference. All the programs in this article have been tested and run well. The simulated remote operating environment here is 1 master and 1 standby two sets of Sun Solaris systems. The backup method adopted is the standard backup provided by Oracle database: export.

Relevant command

Three commands are mainly used in this paper. Here is a separate introduction:

export: backup the data in the database into a binary system file, which has three modes: user mode, table mode, and the whole database mode. There are also three types of backup: full, cumulative, and incremental. This article takes the complete type of the entire database schema as an example to illustrate. export USES the following format:

exp userid file

Where: the usage of userid is username/userpassword, that is, the user name/password in Oracle. userid must be the first parameter of exp. file refers to the location and name of the backup file.

ftp: 1. Normally, data transmission between two hosts can be realized by means of interaction, that is, the IP address, user name and password of the target host need to be manually entered. However, when a user USES the ftp command, the system will first look for the.netrc file in the user's registered directory and execute the file first. In this way, we can achieve the purpose of automatic backup by writing a file of.netrc. Note that this file must be named.netrc and must be stored in the user registration directory on the host of the launch ftp command. ftp common options:

-i: turn off the interactive prompt when transferring multiple files;

-n: no automatic login after connection.

This article USES the "-i" option to turn off interactive prompts.

crontab: 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, and automatically finished. The files in this directory are created by the crontab command. User the established crontab file in/var/spool/cron/crontabs, its file name and user name 1, this article USES crontab Oracle of the user and the established filename for Oracle.

cron user permissions recorded in the following two files: / usr var adm/cron/cron deny and/usr/var adm/cron/cron allow. Users listed in the previous file are not allowed to use the crontab command; The users listed in the last 1 file are allowed to use the crontab command. The common format of the crontab command is:

crontab-l: displays the contents of the user's crontab files;

crontab-r: delete the user's crontab files from the crontabs directory;

crontab-e: edit the user's crontab file.

The crontab file has six fields per line, with the first five being the time segment and the sixth the command to execute. The time periods are: minutes, hours, day of month, month, day of week, and the fields are separated by Spaces or Tab. If the field is "*", it means that the value of the field is within the range of all possible values. If a field is two digits separated by a hyphen, the command can be executed within the range between the two digits.

Backup database

Set the owner of the database, for Oracle user name mistest, its password is test, new backup directory is/export/home/oracle/backup. Create a backup file named testbackup. The file contents are as follows:

ORACLE - HOME = / export/home/oracle / 815; export ORACLE - HOME

ORACLE - SID = ora815; export ORACLE - SID

rm export/home oracle/backup / *

rq=, date + "%m%d",

/ export/home/oracle / 815 / bin/exp mistest/test file = / export/home/oracle

/ backup/exp $rq dmp log = / exoport/home/oracle/backup/exp $rq log

Note: the first two sentences initialize the Oracle database. Statement 3 clears the backup directory. Statement 4 creates a variable that takes the current date so that the backup file name of the last statement contains the current date information.

[NextPage]

Long distance transmission

Set up a backup in the backup host directory: / data/oradata/newbackup, registered in local host Oracle user directory/export/home/build a oracle. netrc file. Set the IP address of the backup host, the user on the backup host is oraclebk, and the password is testbk2. .netrc:

machine x. x. x.. x

login oraclebk

password testbk2

macdef init

bin

lcd export/home/oracle/backup

cd/data/oradata/backup

mput *

bye

Description: the first 3 statement to complete the backup host login, article 4 statement defines a macro called init, article 5 statement said with 2 into the system transmission, article 6 statement said into the local working directory, article 7 statement said into the backup host directories, article 8 statement to complete the local host/export/home/oracle/backup directory all the copies of the file transfer to the host/data/oradata/backup directory, the last 1 statement exit ftp dialogue process.

Once the.netrc document has been prepared, use the command "chmod 600

.netrc "makes the file accessible only to that user.

automated

We log on to the localhost as oracle user and write crontab files using "crontab-e" to start the automatic backup process. The content of oracle is as follows:

October 23 * * * / export home/oracle/testbackup

30 * * * ftp - 23 i x. x. x. x

Note: statement 1 specifies that the database backup is automatically performed at 23:10 each day, and statement 2 specifies that ftp is automatically started at 23:30 each day to transfer the backup files to the backup host (the backup host is the host that matches the IP address specified in the statement).

Oracle files can only be edited under the Oracle user name with the command "crontab-e". Editing is finished, "crontab - l" command can be used to view the content crontab, at the same time/var spool cron/crontabs directory to check whether increased 1 Oracle file.

At this point, the localhost can backup the database at a regular time every day and periodically transfer the backup data to the backup host. In this way, the Oracle data can be automatically backed up in different places. If the local host has any fault, the data can be backed up reliably. At the same time, the tedious and repetitive backup work of system administrators can be alleviated to a certain extent.

On 1 page