Oracle automatically backs up data

Oracle automatically backs up data

It is believed that a large number of system administrators are doing the same job every day-backing up the data. Once a day of negligence, and this day the system is precisely the fault, the need for data recovery, then there is nothing at this time. If you set a fixed time every day, the system automatically backup, then how good! The following author combined with practical experience, to talk about the UNIX environment of Oracle database automatic backup, to play a useful role.

We plan to have the database export backup at 23 o'clock in the evening, copy the backup files to tape at 2 o'clock in the morning, and copy the backup files to another UNIX machine at 4 o'clock in the morning, so we can do the following:

First, export the database

The export command backs up data from the database into a binary file, which typically has three modes: User mode, table mode, and the entire database schema. This article is intended to take user mode, before backup, should establish a backup directory to accommodate backup files, such as a/backup directory can be built. We can then create two file Ora-backup,tar-backup in the Unix Oracle directory (or other directories). To be sure, the previous file needs to initialize the parameters of Oracle, and for convenience, we might want to put the initialization command in a file (the filename is ora-env), and then call it by the first file.

1. The ora-env file initializes the Oracle parameters 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 files to export the database, export the file name can be set arbitrarily, this article is defined as the letter "XX" plus the day date, that is, if the day date is December 10, then the exported file name "Xx1210.dmp" to distinguish the other date of the backup file.

Ora-backup File Contents:

./oracle/ora-env

# Initialize Oracle Database

rq= "data+ '%m%d '"

# Assign the day date 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 test user's data (the password is also test) at the $ prompt, and the exported files and logs are placed under the/backup directory.

Second, tape backup

The Tar-backup file copies the data files exported with the Export command to the tape.

Tar-backup File Contents:

Tar Rvf/dev/rmt/0n/backup/

This command backs up the files produced on the day of the/backup directory to tape. In this document, the TAR command uses three parameters, where the R option indicates that the file is copied to the tape without breaking the original contents of the tape, the V option indicates that the file information is displayed during the copy, the F option is followed by the tape device name, where the file is copied, and the N option indicates that the tape drive does /dev/rmt/0 represents the first tape drive of a UNIX host, and, similarly,/DEV/RMT/1 represents the second tape drive of a UNIX host, and so on.

Ora-env, Ora-backup, tar-backup after the completion of the preparation of documents, respectively, use the following command:

chmod 755 ora-env

chmod 755 Ora-backup

chmod 755 Tar-backup

In this way, all three files become executable files.

Third, offsite backup

We know that the FTP command can usually be used to transfer data between the two hosts, but generally through interactive means, that is, the need to manually enter the target host IP address, user name, password and so on. Obviously, this does not meet the requirements of automatic backup. Fortunately, we can achieve our goals by writing a. netrc file. This file must be named. NETRC and must be located in the user registry directory on the machine on which the FTP command is started, and the permissions of that file should prevent read access by the group or other users. This way, when the user uses the FTP command, the system will look for the. netrc file in the user's registry directory, and if so, the file will be executed first, otherwise the user will be interactively prompted to enter the username, password, etc.

Before using FTP commands, you should first build a directory on a separate UNIX machine for backup to accommodate backup files, and the directory is/pub. It should be noted that in order to speed up the backup rate, the transfer rate between the two hosts should be as high as possible, preferably on the same LAN.

The contents of the. netrc file are as follows:

Machine Host2

# HOST2 The host name used for backup

Login Oracle

#oracle is a user on the backup host

Password Oracle

#oracle User's password is Oracle

Macdef Init

# defines a macro named Init that will be executed at the end of the autoenrollment process

Bin

# file transmission is set to binary

Lcd/backup

# into the local working directory/backup

Cd/pub

# Enter backup host directory/pub

Mput

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

Bye

# exit the FTP session process

When the. netrc file is written, use the following command:

chmod netrc.

In this way, the. netrc file can only be accessed by that user.

Four, start the backup process

Cron is a permanent process that is initiated by/etc/rc.local. Cron checks the files in the/var/spool/cron/crontabs/ directory to find the task to perform and the time to perform the task.

Each line of the crontab file consists of six domains (minutes, hours, day of month, month, Day of Week, command), separated by spaces or tabs, where:

Minutes: Minutes field, the range of values is 0 to 59

Hours: The hour field, the range of values is 0 to 23

Day of Month: date, value range is 1 to 31

Month: month, the range of values is 1 to 12

Day of week: Week, values range from 0 to 6, Sunday value is 0

Command: Commands to run

If a domain is a, it indicates that the command can be executed in all possible ranges of the domain.

If a field is two digits separated by hyphens, the command can be executed within the range of two digits (including two digits themselves).

If a field is made up of a series of values separated by commas, the command can be executed within the range of these values.

Both fields are valid if both the Date field and the week field have values.

Now we write a file to start the automatic backup process. It is noteworthy that the file can only be edited with the CRONTAB-E command under the Oracle user name, otherwise it will not be executed regularly, the filename is Oracle, and the file will be placed in the/var/spool/cron/crontabs directory. Once the edits are complete, you can view them with the CRONTAB-L command at Oracle's $ prompt.

Oracle File content:

0 23/oracle/ora-backup

# Perform a backup of the database every 23 O ' Day

0 2/oracle/tar-backup

# Backup files to tape every 2 O ' Day

0 4ftp-i Host2

# Backup files to another host at 4 a day.

After that, the system automatically generates a backup every night and automatically copies the backup files to the tape and to another host. What the system administrator needs to do is change the tape every few days (the cycle of the tape change depends on the size of the backup file and the capacity of the tape) and clean up the backup directory. In this way, they can be freed from the cumbersome backup data to do other more meaningful work. But the database not only realizes the tape backup, but also realizes the offsite backup, the corresponding security also greatly enhances.