Offsite automatic backup of Oracle data

In large commercial applications, the data disaster recovery backup is very important and necessary. Based on his own practical experience, the author designs A set of methods for automatic data backup in different places, which can be used for reference by database administrators. All the procedures in this paper have been tested and run well. The simulated offsite environment here is a host of two sets of Sun Solaris systems, with a backup method that provides a standard backup of Oracle databases: Export.

Related commands

The main use of the article three commands, here to introduce a separate:

Export: Back up the data in the database into a binary system file, it has three modes: User mode, table mode, entire database schema. can also be divided into three types of backup: complete, Cumulative, incremental type. This article takes the complete model in the whole database mode as an example to illustrate. Export uses the following format:

Exp UserID File

Where: The use of UserID is Username/userpassword, that is, the username/password in Oracle. UserID must be the first parameter of exp, file refers to the location and name of the backup file.

ftp: Under normal circumstances can be interactive way to achieve the data transmission between the two hosts, that is, the need to manually enter the target host IP address, user name, password and so on. However, when the user uses the FTP command, the system will first look for the. netrc file in the user's registry directory and execute the file first. In this way, we can achieve the purpose of automatic backup by writing a. netrc file. Note that the file must be named. NETRC and must be stored in the user registry directory on which the FTP command host is started. FTP Common options:

-I: Turn off interactive prompts for multiple file transfers;

-N: Do not log on automatically after the connection.

This article uses the "-i" option to turn off interaction hints.

Crontab:cron is a permanent process that is initiated by/etc/rc.local. Cron checks the files in the/var/spool/cron/crontabs/directory, finds the task to be performed and the time it takes to perform the task, and automatically completes it. The files in this directory are established by the crontab command. The crontab file created by the user is stored in the/var/spool/cron/crontabs with the same file name as the user name, and the crontab user is Oracle and the file name is Oracle.

The permissions of cron users are documented in the following two files:/usr/var/adm/cron/cron.deny and/usr/var/adm/cron/cron.allow. The user listed in the previous file is not allowed to use the crontab command, and the user listed in the latter file allows the crontab command to be used. The common format for the crontab command is:

Crontab-l: Displays the contents of the user's crontab file;

Crontab-r: Deletes the user's crontab file from the Crontabs directory;

CRONTAB-E: Edit the user's crontab file.

The crontab file has 6 fields per line, the first 5 is the time set, and the 6th is the command to be executed. The time periods are: minutes, hours, day of month, month, days of week, separated by spaces or tabs. If the field is "*", it means that the field is evaluated within all possible ranges of values; If a field is two digits separated by a hyphen, it indicates that the command can be executed within the range of two digits.

Backing up a database

The creator of the database is Oracle, the username is mistest, the password is test, and the new backup directory is/export/home/oracle/backup. Create a backup file named Testbackup with the following contents:

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 are initialized to the Oracle database, the 3rd statement empties the backup directory, and the 4th statement creates a variable that takes the current date, so that the current date information is included in the backup file name of the last statement.

Once the Testbackup is written, grant it executable permissions with the "chmod +x testbackup" command.