Offsite automatic backup of database Oracle data _oracle

The Oracle tutorial you are looking at is an off-site automatic backup of database 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.

[NextPage]

Offsite transmission

Establish a backup directory in the backup host:/data/oradata/newbackup, create a. netrc file in the local host Oracle user's registered directory/export/home/oracle. Set up the IP address of the backup host, the user on the backup host is ORACLEBK, the password is testbk2. netrc file contents are:

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 statements complete the login on the backup host, the 4th statement defines a macro named Init, the 5th statement represents the binary transmission, the 6th statement represents the entry into the local working directory, the 7th statement represents the entry into the backup host directory, and the 8th statement completes the local host/export/home/ All files under the Oracle/backup directory are transferred to the backup host/data/oradata/backup directory, and the last statement exits the FTP session process.

After the writing of the. netrc file, use the command "chmod 600

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

Automatic execution

We log on to the local host with an Oracle user and write the crontab file with "Crontab-e" to start the automatic backup process. The contents of the Oracle file are as follows:

* * */export/home/oracle/testbackup

* * * * ftp-i x.x.x.x

Note: The 1th statement specifies that a backup of the database is automatically performed at 23:10 every day, and the 2nd statement specifies that the FTP is automatically started every day at 23:30, and the backup file is transferred to the backup host (the backup host is the host that matches the specified IP address in the statement).

Oracle files can only be edited with the "CRONTAB-E" command under the Oracle user name. After editing, you can use the "crontab-l" command to view the contents of the crontab file and to see if an additional Oracle file is added to the/var/spool/cron/crontabs directory.

At this point, the local host can regularly back up the database on a daily basis, and regularly upload the backup data to the backup host. In this way, it is better to realize the automatic backup of Oracle data off-site, if there is any fault, the data has a reliable backup, at the same time, to a certain extent, also reduces the system management personnel cumbersome, repetitive backup work.

Previous page