Oracle EXP/IMP Command Detailed ____oracle

Oracle databases have two types of backup methods. The first class is a physical backup, this method realizes the complete recovery of the database, but the database must run in the return mode (the business database runs in the non-return mode), and it needs a great external storage device, such as a tape library; the second type of backup is a logical backup, and the business database uses this method, This method does not require the database to run in the return mode, not only the backup is simple, but also can not need external storage devices.
　　
Database Logical Backup method
　　
Logical backups of Oracle databases are grouped into three modes: Table backup, user backup, and full backup.
　　
Table mode
　　
Backs up the objects (tables) specified in a user mode. Business databases typically take this form of backup.
　　
If you are backing up to a local file, use the following command:
　　
Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=exp_icdmain_csd_yyyymmdd.dmp
Log=exp_icdmain_csd_yyyymmdd.log
Tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo
　　
If you are backing up to a tape device directly, use the following command:
Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=/dev/rmt0
Log=exp_icdmain_csd_yyyymmdd.log
Tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo
　　
Note: In the case of disk space permitting, you should back up to the local server before copying to tape. For speed reasons, try not to back up to tape devices directly.
　　
User mode
　　
Backs up all objects in a user mode. Business databases typically take this form of backup.
If you are backing up to a local file, use the following command:
Exp ICDMAIN/ICD owner=icdmain rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=exp_icdmain_yyyymmdd.dmp
Log=exp_icdmain_yyyymmdd.log
If you are backing up to a tape device directly, use the following command:
Exp ICDMAIN/ICD owner=icdmain rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=/dev/rmt0
Log=exp_icdmain_yyyymmdd.log
Note: If there is space on the disk, it is recommended that you back up to disk and then copy to tape. If the amount of database data is small, this approach can be used to back up.
　　
Full mode
　　
Back up the complete database. The business database does not take this form of backup. The backup command is:
Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0 full=y
FILE=EXP_FULLDB_YYYYMMDD.DMP (tape device is/dev/rmt0)
Log=exp_fulldb_yyyymmdd.log
For database backups, an incremental backup is recommended, which backs up only the data that has changed since the last backup. Incremental backup command:
Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0 full=y Inctype=incremental
FILE=EXP_FULLDB_YYYYMMDD.DMP (tape device is/dev/rmt0)
Log=exp_fulldb_yyyymmdd.log
Note: The following conditions must be met for incremental backups:
1. Only valid for full database backup, and need full=y parameter for the first time, inctype=incremental parameter will be needed later.
2. The user must have a exp_full_database system role.
3. Database backup can be used when traffic volume is small.
4. If there is space on the disk, it is recommended that you back up to disk and then back up to tape.
　　
Methods and cycles of business database backup
　　
Before backing up with Exp, run the Catexp.sql file under the SYS user (do not execute this script if the file was previously run).
No special instructions are allowed to perform a backup command on the client.
The backup command references the backup command in the table mode.
Backup from disk files to tape
If you first back up to a local disk file, you need to dump to a tape device.
1. If you need to see the tape device configured on the host, use the following command:
LSDEV-CC Tape
The results shown are as shown in the following example:
Rmt0 Available 30-58-00-2,0 SCSI 4mm Tape Drive
RMT1 Defined 30-58-00-0,0 SCSI 4mm Tape Drive
The device marked available is an available tape device.
2. If you need to view the contents of the tape storage, use the following command:
Tar-tvf/dev/rmt0
The results shown are as shown in the following example:
-rw-r--r--8089600 14:33:57 2001 EXP_ICDMAIN_20010111.DMP
If the display resembles the following, the backup data that is stored by the tape is backed up from the database directly to the tape, not from the local disk to the tape's backup file, so the operating system is not recognized.
tar:0511-193 An error occurred while reading from the media.
There is an input or output error.
Or
tar:0511-169 A Directory checksum error on media; -267331077 Not equal to
2
5626.
3. For new tapes or tapes that do not need to retain existing data, use the following command:
Tar-cvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp
Note: A. This command will unconditionally overwrite the existing data on the tape.
B. File name does not allow path information, such as:/backup/exp_icdmain_yyyymmdd.dmp.
4. For tapes that need to retain existing data, use the following command:
Tar-rvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp
Note: This command appends the file exp_icdmain_yyyymmdd.dmp to the end of the tape and does not overwrite the existing data.
Special emphasis: If the backup is backed up directly from the database to tape, no additional files can be replicated to the tape, otherwise the backup data is invalidated.
5. If you want to copy the backup files from the dump to the local hard drive, use the following command:
A. Copy all files on the tape to the current directory on the local hard drive
Tar-xvf/dev/rmt0
B. Copy the specified file on the tape to the current directory on the local hard drive
Tar-xvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp
Backup time Schedule
Since backup has a significant impact on system I/O, it is recommended that backups be done after 11 o'clock in the evening.
　　
Recovery of Oracle version of business database
　　
Recovery scenarios are determined according to the backup scenario. Because the business database uses the combination of table backup and user backup, the recovery of the business database needs the combination of table recovery and user recovery in accordance with the actual situation.
　　
Recovery scenarios
　　
The logical recovery of database is divided into three modes: Table recovery, user recovery, and full recovery.
　　
Table mode
　　
This method restores the data that is backed up according to the table pattern.
A. Restore the full contents of the backed-up data
If you are restoring from a local file, use the following command:
Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n
Commit=y buffer=65536 feedback=100000 ignore=n volsize=0
File=exp_icdmain_cs
D_yyyymmdd.dmp
Log=imp_icdmain_csd_yyyymmdd.log
If you are recovering from a tape device, use the following command:
Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n
Commit=y buffer=65536 feedback=100000 ignore=n volsize=0 file=/dev/rmt0
Log=imp_icdmain_csd_yyyymmdd.log
B. Restoring the specified table in the backup data
If you are restoring from a local file, use the following command:
Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n
Commit=y buffer=65536 feedback=100000 ignore=n volsize=0
File=exp_icdmain_cs
D_yyyymmdd.dmp
Log=imp_icdmain_csd_yyyymmdd.log
Tables=commoninformation,serviceinfo
If you are recovering from a tape device, use the following command:
Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n
Commit=y buffer=65536 feedback=100000 ignore=n volsize=0
File=/dev/rmt0
Log=imp_icdmain_csd_yyyymmdd.log
Tables=commoninformation,serviceinfo
　　
User mode
　　
This method restores the data that is backed up according to user mode.
A. Restore the full contents of the backed-up data
If you are restoring from a local file, use the following command:
Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n
Commit=y buffer=65536 feedback=100000 ignore=n volsize=0
File=exp_icdmain_yy
Yymmdd.dmp
Log=imp_icdmain_yyyymmdd.log
If you are recovering from a tape device, use the following command:
Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n
Commit=y buffer=65536 feedback=100000 ignore=n volsize=0 file=/dev/rmt0
Log=imp_icdmain_yyyymmdd.log
B. Restoring the specified table in the backup data
If you are restoring from a local file, use the following command:
Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n
Commit=y buffer=65536 feedback=100000 ignore=n volsize=0
File=exp_icdmain_yy
Yymmdd.dmp
Log=imp_icdmain_yyyymmdd.log
Tables=commoninformation,serviceinfo
If you are recovering from a tape device, use the following command:
Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n
Commit=y buffer=65536 feedback=100000 ignore=n volsize=0 file=/dev/rmt0
Log=imp_icdmain_yyyymmdd.log
Tables=commoninformation,serviceinfo
　　
Full mode
　　
If the backup mode is full mode, the following recovery methods are used:
If you are restoring from a local file, use the following command:
Imp system/manager rows=y indexes=n commit=y buffer=65536
feedback=100000 ignore=y volsize=0 full=y
File=exp_icdmain_yyyymmdd.dmp
Log=imp_icdmain_yyyymmdd.log
If you are recovering from a tape device, use the following command:
Imp system/manager rows=y indexes=n commit=y buffer=65536
feedback=100000 ignore=y volsize=0 full=y
File=/dev/rmt0
Log=imp_icdmain_yyyymmdd.log
　　
Parameter description
　　
1. Ignore parameter
Oracle in the process of recovering data, when a table is restored, the table already exists, depends on the setting of the Ignore parameter to determine how to operate.
If the ignore=y,oracle does not execute the CREATE TABLE statement and inserts data directly into the table, if the inserted record violates the constraint, such as a primary key constraint, the record of the error is not inserted, but the legitimate record is added to the table.
If Ignore=n,oracle does not execute the CREATE TABLE statement and does not insert data into the table, it ignores the table's errors and resumes the next table.
2. Indexes parameter
In the process of recovering data, if indexes=n, the indexes on the table will not be restored, but the unique index corresponding to the primary key will be restored unconditionally, in order to ensure the integrity of the data.
　　
Character Set conversion
　　
For Single-byte character sets (for example, US7ASCII), the database is automatically converted to the character set (Nls_lang parameter) of the session when it is restored, and for multibyte character sets (for example, zhs16cgb231280), when restoring, try to make the character set the same (avoid conversion), and if you want to convert, The character set of the target database should be a superset of the output database character set.
　　
Recovery method
　　
The business database uses a table recovery scheme. Before you recover with IMP, run the Catexp.sql file under the SYS user (do not execute this script if you have previously run the file), and then execute the following command:
IMP icdmain/icd file= filename log=log filename rows=y
Commit=y buffer=y ignore=y tables= table name
Note: The table name to be restored refers to the table name of the backup
。 Recovery is the accumulation of data based on the original table
。 No special instructions, do not allow restore command on client

Example of a user who guides all the tables of a user of a database to another database
Exp Userid=system/manager owner=username1 File=expfile.dmp
Imp userid=system/manager fromuser=username1 touser=username2 ignore=y file=expfile.dmp

Example of a user who guides all the tables of a user of a database to another database

Exp Userid=system/manager owner=username1 File=expfile.dmp
Imp userid=system/manager fromuser=username1 touser=username2 ignore=y file=expfile.dmp

Oracle databases have two types of backup methods. The first class is a physical backup, this method realizes the complete recovery of the database, but the database must run in the return mode (the business database runs in the non-return mode), and it needs a great external storage device, such as a tape library; the second type of backup is a logical backup, and the business database uses this method, This method does not require the database to run in the return mode, not only the backup is simple, but also can not need external storage devices.