Automatic Remote Backup of Oracle Data

In large commercial applications, Remote Disaster Recovery and backup of data are very important and necessary. Based on my practical experience, I have designed a simple method to implement automatic backup of remote data for the reference of database administrators. All the programs in this article have been tested and run well. The Simulated Remote running environment is a master-slave system with two Sun Solaris systems. The backup method used is the standard backup: export provided by the Oracle database.

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: Generally, 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 the ftp command, the system will first find the. netrc file in the user's registration directory 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. netrc and 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 that is started and executed by/etc/rc. local. Cron checks the files in the/var/spool/cron/crontabs/directory, finds the task to be executed and the time of the task to be executed, and automatically completes the task. The files in this directory are created by the crontab command. The crontab file created by the user is stored in/var/spool/cron/crontabs. The file name is consistent with the user name. In this article, the crontab user 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 to use the crontab command; users listed in the last file are allowed to 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, day of month, month, and day of week. fields are separated by spaces or tabs. If the field is "*", it indicates that the field is within the range of all possible values. If a field is two numbers separated by a hyphen, indicates that 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, and 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 user on the backup host is oraclebk and the password is 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, and the second statements are transmitted in binary format, the first statement indicates to enter the local working directory, and the second statement indicates to enter the backup host directory, after 8th statements are completed, all files in the local host/export/home/oracle/backup Directory are transmitted to the backup host/data/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.