Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "how to achieve automatic backup of Oracle database under UNIX". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Data backup is a task that almost every system administrator does every day, but it is very tedious-it would be nice to set a fixed time every day and the system would back up automatically! The following author combined with practical experience, talk about how to achieve automatic backup of Oracle database under the UNIX environment, in order to play a role.

We plan to have the database do an eXPort export backup at 23:00, copy the backup file to tape at 2 a.m., and copy the backup file to another UNIX machine at 4 a.m., we can do the following:

1. Export Oracle database

The export command backs up the data in the database into a binary file, which usually has three modes: the user mode, the table mode, and the entire database schema. This article intends to use user mode, before backup, you should first establish a backup directory to accommodate backup files, such as a / backup directory. Then we can create two files ora-backup,tar-backup under the Oracle directory of UNIX (or other directories). It should be noted that the previous file needs to initialize the parameters of Oracle. For convenience, we might as well put the initialization command in a file (file name and set it to ora-env), and then call it by * * files.

1. Initialize the parameters of Oracle in ora-env file, which 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. Ora-backup file export export to the database, the exported file name can be arbitrarily determined, this article is defined as the letter "xx" plus the date of the day, that is, if the date of the day is December 10, then the exported file name is "xx1210.dmp" to distinguish it from other backup files on other dates.

Contents of ora-backup file:

. / oracle/ora-env

# initialize Oracle database

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

# assign the date of the day to the variable rq

Rm / backup/?

# clear 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 export files and logs are placed in the / backup directory.

2. Tape backup of automatic backup of Oracle database

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

Contents of tar-backup file:

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

This command backs up the files generated on the same day in the / backup directory to tape. In this file, the tar command uses three parameters, where the r option indicates that the file is copied to the tape without destroying the original contents 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, and the n option indicates that the tape drive does not rewind. / dev/rmt/0 represents the UNIX host * tape drives, similarly, / dev/rmt/1 represents the second tape drive of the UNIX host, and so on.

After the ora-env, ora-backup, and tar-backup files are written, use the following commands:

Chmod 755 ora-env

Chmod 755 ora-backup

Chmod 755 tar-backup

In this way, all three files become executable.

III. Remote backup of automatic backup of Oracle database

We know that it is usually possible to use the FTP command to transfer data between two hosts, but it is generally achieved through interaction, that is, you need to manually enter 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 our goal by writing a .netrc file. This file must be named .netrc and must be stored in the user registration directory on the machine that started the FTP command, and the permissions of the file should prohibit read access within the group or by other users. In this way, when the user uses the FTP command, the system will look for the .netrc file in the user's registration directory, and if it can be found, the file will be executed first, otherwise, the user will be prompted interactively for a user name, password, and so on.

Before using the FTP command, you should create a directory on another UNIX machine for backup to hold the backup files. The directory created in this article is / pub. It should be pointed out that in order to speed up the backup speed, the transfer rate between the two hosts should be as high as possible, * on the same local area network.

The .netrc file is as follows:

Machine host2

# host2 is the hostname for backup

Login oracle

# oracle is a user on the backup host

PassWord oracle

# the password of oracle user is oracle

Macdef init

# define a macro named init, which will be executed in the * of the auto-registration process

Bin

# the transfer mode of files is set to binary

Lcd / backup

# enter 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 written, use the following command:

Chmod 600 .netrc

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

Start the backup process

Cron is a * process that is started and executed by / etc/rc.local. Cron check / var/spool/cron/crontabs/? The files in the directory to find the task to be performed and the time to execute the task.

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 range, values range from 0 to 59

Hours: hour range, values range from 0 to 23

Day of month: date, with values ranging from 1 to 31

Month: month, with values ranging from 1 to 12

Day of week: week, values range from 0 to 6, Sunday values are 0

Command: the command to run

If a field is?, the command can be executed within all possible values for that field.

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

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

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

Now, let's write a file to start the automatic backup process. It is worth noting that the file can only be edited under the Oracle user name with the crontab-e command, otherwise it will not be executed regularly, the file name will be set to Oracle, and the file will be placed in the / var/spool/cron/crontabs directory. After editing, you can view it with the crontab-l command at the $prompt in Oracle.

Contents of Oracle file:

0 23? / oracle/ora-backup

# backup the database at 23:00 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 the files to another host at 4: 00 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 another host, so as to realize the automatic backup of Oracle database. What the system administrator needs to do is to change the tape every few days (the cycle of changing the tape 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 free themselves from the hassle of backing up data and do other more meaningful work. The database not only realizes tape backup, but also realizes remote backup, and the corresponding security is greatly improved.

"how to achieve automatic backup of Oracle database under UNIX" is introduced here. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.