Automatic Oracle backup

Recently, because the Oracle database backup problem is involved every day in the re-project, and the server runs remotely, it is very time-consuming to manually back up data every day, so I checked some information about Oracle database backup. I feel that I am not very clear about it in many places on the Internet. Here I will talk about Oracle's automatic backup.

The running environment of the server is Windows-Server 2003, and the database is 9I (some parameters may change in other versions ).

First, create a batch file with the following content:

Exp user/password rows = y indexes = n compress = n buffer = 65536 file = 'd:/databackup/DMP/% Date :~ 0, 10%. dmp 'full = y log = 'd:/databackup/log/% Date :~ 0, 10%. Log'

The following describes the specific meanings of the parameters:

Exp is an oracle Export Database Operation Command;

User is the user name; password is the password;

Rows = y indicates the data row to be exported (Y by default );

Indexes = n indicates that the index is not exported (Y by default );

Full = y indicates that the database needs to be completely backed up (default value: N );

Compress = n indicates not to compress all data into one data block (Note: compress = y when exp is exported by default, that is, to compress all data into one data block, if there is no continuous big data block during import, the import will fail. Setting compress = n when exporting a table larger than 80 Mb does not cause this error .);

Buffer = 65536 indicates that the data buffer size is set to 65536, and the default o9i buffer size on my side is 4096. When the backup database is large, we recommend that you set a larger buffer value. For example, if the database I backed up is close to 140 MB, I didn't set the buffer option before, and adopted the default 4096, I calculated the backup time for a specific time, which is about 2 minutes to 3 minutes. Later, I set the buffer to 65536 and it will take less than half a minute.

File = 'd:/databackup/DMP/% Date :~ 0, 10%. DMP indicates that the backup data file is stored in D:/databackup/DMP/. The file name is the date on the current backup server, and today the day is. The backup file name is 2008-01-11.dmp.

Log = 'd:/databackup/log/% Date :~ %. Log' indicates that the log file during the backup operation is stored under D:/databackup/log/. The file name is in the format of date plus suffix. Today, the log file backed up is 2008-01-11.log.

After a batch file is created, it is placed in the task option of the attachment in windows, set the Administrator's username and password, and set the automatic running time. The task runs automatically on a regular basis.

Configuration complete.

Next, let's talk about my understanding of how to export Oracle databases:

(1) simple export Mode

Oracle supports three types of output:

(1) Export the data of the specified table in the T mode.

(2) user mode (U mode), which exports all objects and data of the specified user.

(3) full database mode (full mode) to export all objects in the database.

The above example uses the full database mode (full mode ).

Now we use the user mode (U mode), we change the batch file

Exp user/password owner = user rows = y indexes = n compress = n buffer = 65536 file = 'd:/databackup/DMP/% Date :~ 0, 10%. dmp 'Log = 'd:/databackup/log/% Date :~ 0, 10%. Log'

Note that deleting "Full = Y" and adding "owner = user" indicate that only the database of the user is exported, and the backup data is greatly reduced during my actual operation.

Finally, we use the table (t) method for operations and change the batch processing file

Exp user/password tables = user. Table1, user. Table2 rows = y indexes = n compress = n buffer = 65536 file = 'd:/databackup/DMP/% Date :~ 0, 10%. dmp 'Log = 'd:/databackup/log/% Date :~ 0, 10%. Log'

Note the difference with the U method. I will delete the owner = user and add it to tables = user. table1, user. table2, multiple tablespaces are separated by commas (,). Therefore, only user exists in the backup file. table1 and user. table 2.

(2) incremental export Mode

Incremental export is a common data backup method. It can only be implemented for the entire database and must be exported as a system. During this export, the system does not require any answers. The default export file name is export. dmp. If you do not want your output file to be named export. DMP, you must specify the file name to use in the command line.

Incremental export includes three types:

(1) "full" incremental Export (complete) backs up three databases, for example, exp user/password inctype = complete file = 'd:/databackup/DMP/% Date: ~ 0, 10%. dmp '.

(2) incremental export the data changed after the last backup, for example, exp user/password inctype = incremental file = 'd: /databackup/DMP/% Date :~ 0, 10%. dmp '.

(3) "accumulative" incremental export the accumulative export mode is to export the changed information in the database since the last "complete" export. For example, exp user/password inctype = cumulative file = 'd:/databackup/DMP/% Date :~ 0, 10%. dmp '.

I have not used or figured out the Incremental backup. I will talk about it later!