Oracle Incremental Backup Recovery Strategy (Fundamentals)

Exp and IMP are a logical backup tool provided by Oracle. Logical backup creates a logical copy of the database object and stores it in a binary dump file. This logical backup needs to be used in the case of a database startup, whose export is essentially reading a database recordset (or even a data dictionary) and writing the recordset to a file whose export is independent of its physical location, which is essentially reading the dump file and executing its commands. This backup is done through Oracle'sUseful ToolsExport and import to implement, exports is the data in the database exported, import is to import the export data imported into the database. This tool can derive a variety of functions, such as the whole database backup, table structure reconstruction, data transmission, user changes and so on.

The Oracle Export/import tool offers flexible features and export/import modes, the most commonly used three modes are user, table, and full database. In addition, you can decide whether to include data dictionary information about the object, such as indexes, constraints, permissions, and so on when exporting/importing.

Note: You can back up the entire database with a logical backup, or just back up some of the important data. Because it is a logical backup, it can only be used for logical recovery data, once the database is physically corrupted, causing the failure to start, the data of the logical backup does not help to recover the database.

1. Examples of use of Export/import: (The following operations are
(1). Import one user's object into another user mode:
Export users from the database to a DMP file (such as the user Olduser object exported to file Olduser0701.dmp):
$ exp Sys/password file= olduser0701 owner=olduser grant=n indexes=y rows=y
Import a user's object from the DMP file into a user's mode (from Olduser0701.dmp, the user Olduser object is imported into the user NewUser):
$imp sys/password file=olduser0701 fromuser=olduser touser=newuser indexes=y rows=y
(2). Avoid ORA-00942 (table or diagram not present) error when importing:
Because the export is done in the order in which the objects are established in the database, the dependency between the database objects during the import causes some of the lead-in objects to find their dependent objects (such as view, procedure, and so on), causing the ORA-00942 (table or graph not to exist) error. The usual way to solve this problem is to do two import work, the first time the import is set as follows:
$ imp sys/password file=demo0701 full=y commit=y rows=y
If an error occurs during import, modify the settings: Rows=n,ignore=n:
$ imp sys/password file=demo0701 full=y rows=n commit=y ignore=n
(3). Examples of non-incremental full-database backup and recovery:
Full database Export to DMP file (e.g. Sidfull0701.dmp):
$ exp Sys/password file=sidfull0701.dmp full=y--Rows to save Y
Put the wholeDatabase BackupSIDFULL0701.DMP file to the database:
$ImpSys/password file=sidfull0701.dmp ignore=y full=y--rows to save Y
Note: Import/export is the most thorough way to reduce disk fragmentation.
2. Backup strategy and comprehensive application examples:
(1). How to do a full database incremental backup and restore:
Full database Export to DMP file (e.g. Sidfull0701.dmp):
$ exp Sys/manager file= sidfull0701.dmp full=y inctype=complete
First day incremental backup export to DMP file (e.g. Sidincr1.dmp):
$ exp Sys/manager file= sidincr1.dmp inctype=incremental
The next day an incremental backup is export to a DMP file (such as SIDINCR2.DMP):
$ exp Sys/manager file= sidincr2.dmp inctype=incremental
Third day incremental backup export to DMP file (e.g. Sidincr3.dmp):
$ exp Sys/manager file= sidincr3.dmp inctype=incremental
It is assumed that the Oracle database was destroyed on the third day to rebuild an Oracle database, first to imp the last DMP file into the database:
$ImpSys/manager file= sidincr3.dmp full=y ignore=y Inctype=system
Put the wholeDatabase BackupDMP file for imp into the database:
$ imp sys/manager file= sidfull0701.dmp ignore=y full=y inctype=restore
Imp the DMP file for the first day of incremental backups into the database:
$ imp sys/manager file= sidincr1.dmp ignore=y full=y inctype=restore
Remove the DMP file from the incremental backup the next day to the database:
$ imp sys/manager file= sidincr2.dmp ignore=y full=y inctype=restore
Remove the DMP file from the incremental backup for the third day into the database
$ImpSys/manager file= sidincr3.dmp ignore=y full=y inctype=restore
(2). The development of a database logical backup strategy:
The database administrator can schedule a backup schedule that is reasonable, efficient, and reliable, in combination with the three different ways in which data is exported. For example, the backup task of the database can be arranged as follows:
Monday: Full Export (A)
Tuesday: Incremental Export (B)
Wednesday: Incremental Export (C)
Thursday: Incremental Export (D)
Friday: Cumulative Export (E)
Saturday: Incremental Export (F)
Sunday: Incremental Export (G)
If in Sunday, the database was accidentally destroyed,Database AdministratorYou can restore the database by following these steps:
First step: Create database with command to regenerateDatabaseStructure
Step Two: Create an additional rollback segment that is large enough.
Step Three: Fully incremental import a:
$ imp system/manager inctype=rectore full=y file=a
Fourth Step: Cumulative incremental Import E:
$ imp system/manager inctype=rectore full=y FILE =e
Fifth step: The most recent incremental import F:
$ImpSystem/manager Inctype=restore full=y file=f
Note: The name of the backup file is best added to the date, which facilitates later recovery.
The operation is as follows:
$ TAR-CVF Full ' date +%y-%m-%d-%h-%m '. DMP ABC
$ TAR-CVF Full ' date +%y-%m-%d-%h-%m-%s '. DMP ABC
(3). Backup the database with cron schedule:
Linux, UNIX provides a timer service cron program that can perform a series of tasks in a defined time, soDatabaseBackup must be done in cooperation with cron, or using the daily,weekly,monthly file in/etc to do timedDatabase BackupOperation.
For example: Write the sh file for the Oracle logical backup and use the CRON program to make the backup time.
I. Edit the full backup script file (exp_comp.sh):
rq= ' date + '%m%d '
SU-ORACLE-C "Exp System/manager full=y inctype=complete file=/oracle/
Export/db_comp$rq.dmp "
II. Edit the cumulative Backup script file (exp_cumu.sh):
rq= ' date + '%m%d '
Su-Oracle-C "Exp System/manager full=y inctype=cumulative file=/oracle
/export/db_cumu$rq.dmp "
Iii. editing the incremental backup script file (exp_incr.sh):
rq= ' date + '%m%d '
SU-ORACLE-C "Exp System/manager full=y inctype=incremental file=/oracle
/export/db_incr$rq.dmp "
Iv. Edit the root user crontab file and use the above script command to make a cron programDatabase BackupThe time:
$ vi/var/spool/cron/crontabs/root
V. Add the following in the/var/spool/cron/crontabs/root file:
0 2 1 * */oracle/exp_comp.sh
2 * * 0-5/oracle/exp_incr.sh
45 2 * * 6/Oracle/exp_cumu.sh
Of course, the above schedule can be changed according to different needs. For example, follow the backup schedule of the above database to make this cron program, as follows:
Monday: Full Export (A)
Tuesday: Incremental Export (B)
Wednesday: Incremental Export (C)
Thursday: Incremental Export (D)
Friday: Cumulative Export (E)
Saturday: Incremental Export (F)
Sunday: Incremental Export (G)
(4). Precautions to use Export/import:
When doing export and import, please pay attention to your character set settings, if you do export or import, UINX environment variable if and character set settings inconsistent, will causeDatabaseThe recovered information is not read correctly, especially the Chinese character set of non-default values used by the database. Keep in mind that the requirements must be consistent, and if you have special requests, please contact Oracle Aftermarket support first. Here's how to determine the character set settings for the Oracle database and the environment variables for the operating system:
$SQLPlus Sys/manager
Sql> select * from Nls_database_parameters;
PARAMETER VALUE
------------------------- -----------------------------
Nls_language AMERICAN
Nls_territory AMERICA
Nls_characterset UTF8
Environment variables should be set
$ export Nls_lang=american_america. UTF8
Export/import supported incremental and cumulative backups are actually table-level, that is, tables that change after the last backup are unloaded instead of changing records. Therefore, in the OLTP application-basedDatabase, incremental and cumulative offload does not significantly improve the efficiency of backups because the tables that hold the primary business data are in frequent entry and update.
When doing the entire database import, the default state is a table for a transaction, so if there are some tables with a large amount of data, it is recommended to use commit=y and to provide a larger buffer value, while creating a temporary rollback Segment, its default storage parameter initial and next are to be set larger, and this rollback segment Online, while offline other small rollback segments. Ensure that a table can be completeimp ort. Because the database will automatically create indexes and integrity constraints when doing import, to speed up data loading and the probability of a success, consider first disable all integrity constraints before export, and enable all integrity constraints after import. For indexes, you can consider a separate export index. By using the command "Imp indexfile= ..." The index is created separately after the import data.
     If you want to Export database , directly writes the resulting DMP file to the peripheral, you can use the "exp file= device name ... Volsize= the capacity of the device.
because the export file may be large, and some operating systems (such as Linux) have a limit on file size, such as cannot be greater than 2G. The DMP file can therefore be set to several files of the specified size. Example:
$exp sys/manager buffer=4096000 file= (full1.dmp,full2.dmp,full3.dmp,full4.dmp) filesize=2g Full=y inctype= Complete Log=full.log

http://www.knowsky.com/540062.html

Oracle Incremental Backup recovery policy (basic knowledge)