Oracle Automatic Data Backup

Based on practical experience, I will talk about the automatic backup of Oracle databases in UNIX environments. We plan to allow the database to perform export backup at a.m., copy the backup file to the tape at a.m., and copy the backup file to another UNIX machine at a.m, to do this, we can perform the following operations:

1. Export the database

The export command backs up data in the database into a binary file. There are usually three modes: user mode, table mode, and whole database mode. This document uses the user mode. before backup, you must create a backup directory to hold backup files. For example, you can create a/backup directory. Then we can create two files, ora-backup and tar-backup, respectively, in the UNIX Oracle directory or in other directories. It should be noted that the Oracle parameter needs to be initialized in the previous file. For convenience, we may wish to put the initialization command in a file named ora-env ), then the first file calls it.

1. The ora-env file initializes Oracle parameters. The content is 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. the ora-backup file exports the Database export, and the exported file name can be set at will. This document specifies the letter "xx" and the date of the day, that is, if the date of the day is January 1, December 10, the exported file name is "xx1210.dmp", which is different from the backup file of other dates.

Ora-backup file content:

./Oracle/ora-env

# Initializing an Oracle database

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

# Assign the date of the day to the variable rq

Rm/backup/

# Clearing/backup directories

Exp test/test file =/backup/xx $ rq. dmp log =/backup/xx $ rq. log

This command is used to export the data of user test at the $ prompt. The exported files and logs are stored in the/backup directory.

Ii. tape backup

The tar-backup file copies the data files exported using the export command to the tape.

Tar-backup file content:

Tar rvf/dev/rmt/0n/backup/

This command backs up files generated on the current day in the/backup Directory to the tape. In this file, the tar command uses three parameters. The r option indicates that a file is written to the tape without damaging the original content of the tape. The v option indicates that the file information is displayed during the copy process, the f option is followed by the tape device name, specifying where the file is copied. The n option indicates that the tape drive is not replayed. /Dev/rmt/0 indicates the first tape drive of the UNIX host. Similarly,/dev/rmt/1 indicates the second tape drive of the UNIX host, and so on.

After writing the ora-env, ora-backup, and tar-backup files, run the following commands:

Chmod 755 ora-env

Chmod 755 ora-backup

Chmod 755 tar-backup

In this way, all three files become executable files.

Iii. Remote Backup

We know that FTP commands are usually used to transmit data between two hosts, but they are generally implemented through interaction, that is, you need to manually enter the IP address, user name, and password of the target host. Obviously, this does not meet the requirements of automatic backup. Fortunately, we can achieve our goal by writing a. netrc file. This file must be named. netrc and stored in the user registration directory on the machine where the FTP command is started. The file permission should be disabled for read access by the group or other users. In this way, when a user uses an FTP command, the system will find it in the user's registration directory. if you can find the netrc file, the file will be executed first. Otherwise, the user will be prompted interactively to enter the user name and password.

Before using the FTP command, you should first create a directory on another UNIX machine for backup to accommodate the backup files. The directory created in this document is/pub. It should be noted that, in order to speed up the backup, the transmission rate between the two hosts should be as high as possible, preferably on the same LAN.

The content of the. netrc file is as follows:

Machine host2

# Host2 is the host name used for backup

Login oracle

# Oracle is a user on the backup host

Password oracle

# Oracle user password

Macdef init

# Define a macro named init, which will be executed at the end of the automatic registration process

Bin

# Set the file transmission mode to binary

LCD/backup

# Go to the local working directory/backup

Cd/pub

# Enter 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 is compiled, run the following command:

Chmod 600. netrc

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

4. Start the backup process

Cron is a permanent process started and executed by/etc/rc. local. Cron checks the files in the/var/spool/cron/crontabs/directory and finds the task to be executed and the time when the task is executed.

Each row of the Crontab file consists of six fields (minutes, hours, day of month, month, day of week, and command). The fields are separated by spaces or tabs:

Minutes: minute field. The value ranges from 0 to 59.

Hours: small time domain. The value ranges from 0 to 23.

Day of month: date. The value ranges from 1 to 31.

Month: month. The value ranges from 1 to 12.

Day of week: day of the week. The value ranges from 0 to 6. The value of Sunday is 0.

Command: the command to be run

If a domain is, the command can be executed within all possible values of the domain.

If a field is two numbers separated by a hyphen, the command can include two numbers in the range between the two numbers ).

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

If both the date and week fields have values, these two fields are valid.

Now, we write a file to start the automatic backup process. It is worth noting that the file can only be edited using the crontab-e command under the Oracle user name. Otherwise, it will not be scheduled and the file name is set to Oracle, the file will be stored in the/var/spool/cron/crontabs directory. After editing, you can run the crontab-l command at the $ prompt in Oracle.

Oracle file content:

0 23/oracle/ora-backup

# Perform database backup at 23 o'clock every day

0 2/oracle/tar-backup

# Back Up Files to tape at 2 o'clock every day

0 4 ftp-I host2

# Back Up Files to another host at 4 o'clock every day

After the above operations, the system will automatically generate a backup every night, and automatically copy the backup files to the tape and the other host. What the system administrator needs to do is to change a disk of tape every several days, depending on the size of the backup file and the size of the tape) and clean up the backup directory. In this way, they can free themselves from the hassle of backing up data and do other meaningful work. The database implements both tape backup and remote backup, which greatly improves the security.