Using Crontab to implement timed backup of Oracle database

using crontab to implement timed backup of Oracle database Xu Yuze

01-4-29 09:53:52

Timed hot Backup (EXP) for an Oracle database can usually be implemented in two ways, using the Task Queue Manager (Job queue) that it provides in an Oracle database, and using the operating system's timed execution commands to do so. In the first method, the database is required to start the background process SNP, and the task queue's startup parameters are added to the Oracle initialization file, and the second method does not require any changes to the database. This example describes how to use a timed trigger crontab under digital Unix 4.0F to complete a hot backup of the Oracle8i database.

How to use a command

1, the use of Crontab method

Crontab is a timed task trigger under a UNIX system whose user permissions are recorded in the following two files:

/usr/var/adm/cron/cron.deny: The user listed in this file is not allowed to use the crontab command;

/usr/var/adm/cron/cron.allow: The user listed in this document is allowed to use the crontab command;

The format of the crontab command is: crontab–l|-v|-r|-e [username], whose parameter meaning is as table one:

The use method of table one crontab parameter

Parameter name Xml:namespace PREFIX = O/> Meaning -L Displays the contents of the user's crontab file -V Displays the contents of the user's crontab file and the time it was submitted, only applicable to the Compaq series -R Remove a user's crontab file from the crontab directory -E Edit a user's crontab file

The crontab file created by the user is stored in/var/spool/cron/crontabs and has the same filename as the user name. Its format is divided into six paragraphs, the first five paragraphs for the time set segment, the sixth paragraph is the command segment to be executed, the format is as follows:

* * * * * * <command>

The meaning of its time period is as table two:

Table two meaning of time period in crontab file

Paragraph Meaning Value range First paragraph Represents minutes 0-59 Second paragraph Rep hours 0-23 Third paragraph Rep date 1-31 Fourth paragraph On behalf of the month 1- Fifth On behalf of the week, 0 for Sunday 0-6

Example: If the contents of the user's crontab file are: * * * * echo its dinner time, the system's 19:29 per day displays ' its dinner time '.

2, the use of exp method

EXP is a hot backup command for an Oracle database, which is frequently used in the following format:

Exp userid file= ... owner=[username]

Because of the more parameters of exp command, this is not introduced here, the reader can use the exp help=y command to understand the parameters of the EXP command, the following only the meaning of the three parameters will be used to introduce to everyone, see table three:

Table three common parameters of exp

Parameter name Meaning Userid Written as [username/password in username]/[userpassword],oracle, it must be the first parameter of exp File Location and name of the backup file Owner Backs up the Oracle objects (tables, stored procedures, etc.) that the user owns

Ii. Methods of implementation

Assuming that the owner of the database is ORACLE and that the user of the database is Scott, whose password is the trigger,oracle database parameter $oracle_home to/usr/oracle, $ORACLE _sid to ORCL, the following steps are implemented:

1, the establishment of the Shell to achieve backup

In/usr/oracle, the file backup.sh is established with the VI command, and its contents are:

Oracle_home=/usr/oracle;

Export Oracle_home;

ORACLE_SID=ORCL;

Export Oracle_sid;

/usr/oracle/bin/exp Scott/trigger file=/usr/oracle/backup.dmp Owner=scott

Use the chmod command to modify the properties of backup.sh to perform:

chmod +x backup.sh

Up to this, an executable (shell) backup.sh with backup capabilities has been built. The result is a backup of the Oracle object Scott owns to the Backup.dmp in/usr/oracle, which the reader can run directly to see the effect. It is noteworthy that the two environment parameters of Oracle in backup.sh must be declared, otherwise the system will complain. The next step is to start building the crontab file for the Oracle user.

2, view the use of crontab rights

Log in as Superuser (root), view the Cron.deny file under/usr/var/adm/cron through the VI command, and delete the database owner if Oracle is listed inside.

3, the establishment of Oracle CRONTAB files

Log on with an Oracle user to start building an Oracle user's crontab file by using the command Crontab–e Oracle. Its contents are:

* * 1-5 "/usr/oracle/backup.sh"

After you have saved the file, you will find a new name for Oracle in/var/spool/cron/crontabs, which is to run "/usr/oracle/backup.sh" 17:00 per day from Monday to Friday.

By doing so, the system sends the information for each backup to the Oracle user in the form of mail. In doing so, it is recommended that you back up the crontab files of other users in the/var/spool/cron/crontabs, especially root, so that the system will not be adversely affected by the misuse of the operation.