Solution for automatic Oracle Remote Data Backup

Release date: Source: collected:

In large commercial applications, Remote Disaster Recovery and backup of data are very important and necessary. Based on my practical experience, I designed a set of simple methods to implement remote data
The dynamic backup solution can be used for reference by database administrators. All the programs in this article have been tested and run well. The Simulated Remote running environment is one master, one slave, and two sets of sun
The Solaris system adopts the standard backup: Export provided by the Oracle database. ---Www.bianceng.cn

Related commands

This article mainly uses three commands. Here we will introduce them separately:

Export: backs up data in the database into a binary system file. It has three modes: user mode, table mode, and whole database mode. There are also three backup types: full, accumulative, and incremental. This article takes the full database model as an example. The format of export is exp userid file.

Here, userid is used as username/userpassword, that is, the username/password in Oracle. Userid must be the first parameter of Exp. file indicates the location and name of the backup file.

FTP:
In general, data transmission between two hosts can be achieved through interactive means, that is, you need to manually enter the IP address, user name, password, and so on of the target host. However, when you use an FTP command
Find the. netrc file in the registration directory of the user, and first execute the file. In this way, we can compile a. netrc file to achieve automatic backup. Note that the file must be named
Is. netrc, and must be stored in the user registration directory on the startup FTP command host. FTP common options:

-I: The Interaction prompt is disabled when multiple files are transferred;

-N: no automatic logon is performed after the connection.

This article uses the "-I" option to disable interaction prompts.

Crontab:
Cron is a permanent process started and executed by/etc/rc. Local. Cron check the files in the/var/spool/cron/crontabs/directory and find
The task to be executed and the time when the task is executed, and the task is automatically completed. The files in this directory are created by the crontab command. The crontab file created by the user is stored in/var/spool.
In/cron/crontabs, the file name is the same as the user name. In this article, the user using crontab is Oracle, And the created file name is also oracle.

Cron user permissions are recorded in the following two files:
/Usr/var/adm/cron. deny and/usr/var/adm/cron. allow. Users listed in the preceding file are not allowed
Use the crontab command. Users listed in the last file can use the crontab command. The common format of the crontab command is:

Crontab-L: displays the contents of your crontab file;

Crontab-R: deletes a crontab file from the crontabs directory;

Crontab-E: edit the user's crontab file.

Each row of the crontab file has six fields. The first five fields are set time segments, and the second field is the command to be executed. The time periods are: minutes, hours, and day
Month, month, and day of week. separate fields with spaces or tabs. If the field is "*", it indicates that the field is within all possible value ranges;
If a field is two numbers separated by a hyphen, the command can be executed within the range of two numbers.

Back up database

Set the database owner to Oracle, the username to mistest, the password to test, and the new backup directory to/export/home/Oracle/backup. Create a backup file named testbackup with the following content:

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 statements initialize the Oracle database, 3rd statements clear the backup directory, and 4th statements create a variable to take the current date, so that the last statement's backup file name contains the current date information. After writing testbackup, run the "chmod + x testbackup" command to grant it the executable permission.

Remote Transmission

Create a backup directory in the backup HOST:/data/oradata/newbackup,
Create a. netrc file in the registration directory/export/home/oracle of the local host Oracle user. Set the IP address of the backup host.
The password is oraclebk and testbk2 .. The content of the netrc file is:

Machine x.. x
Login oraclebk
Password testbk2
Macdef init
Bin
LCD/export/home/Oracle/backup
CD/data/oradata/backup
Mput *
Bye

Note:
The first three statements are used to log on to the backup host. The first three statements define a macro named init. The second statement represents binary transmission, and the second statement represents entry to the local working directory, expression of 7th statements
Go to the backup host directory and complete the 8th statements to transfer all files in the local host/export/home/Oracle/Backup Directory to the backup host/Data
In the/oradata/backup directory, the last statement exits the FTP session process .. After the netrc file is compiled, run the command "chmod 600
. Netrc "makes the file accessible only to the user.

Automatic Execution

Log on to the local host as an oracle user and use "crontab-e" to write the crontab file to start the automatic backup process. The content of the Oracle file is as follows:

10 23 ***/export/home/Oracle/testbackup
30 23 *** FTP-I x. x

Note: 1st statements specify that the database is automatically backed up at every day, and 2nd statements specify that FTP is automatically started at every day, transfers the backup file to the backup host (the backup host is the host with the specified IP address in the statement ).

The Oracle file can only be edited using the "crontab-e" command under the Oracle user name. After editing, you can run the "crontab-L" command to view the contents of the crontab file, and check whether an oracle file is added in the/var/spool/cron/crontabs directory.

At this point, the local host can regularly back up the database and regularly upload the backup data to the backup host. In this way, Oracle data is automatically backed up in different regions. If any fault occurs on the local host, the data is backed up reliably, to some extent, it also reduces the cumbersome and repetitive backup work of system administrators.