Explanation of exp/IMP commands in Oracle

Oracle databases have two backup methods. The first type is physical backup. This method enables complete database recovery, but the database must run in the recovery mode (the business database runs in the non-recovery mode ), this method requires a large number of external storage devices, such as tape libraries. The second type of backup mode is logical backup, which is used by the business database. This method does not require the database to run in the back-to-back mode, which is not only simple, in addition, external storage devices are not required.
　　
Database logical backup method
　　
There are three logical backup modes for Oracle databases: Table backup, user backup, and full backup.
　　
Table mode
　　
Back up the specified object (table) in a user mode ). Business databases usually adopt this backup method.
　　
To back up a local file, run 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 back up the data directly to a tape device, run 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: If the disk space permits, back up the data to the local server and copy the data to the tape. For speed considerations, do not back up data directly to tape devices.
　　
User Mode
　　
Back up all objects in a user mode. Business databases usually adopt this backup method.
To back up a local file, run 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 back up the data directly to a tape device, run 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 the disk has space, we recommend that you back up the disk and then copy it to the tape. If the database data volume is small, backup can be performed in this way.
　　
Full Mode
　　
Back up the complete database. Business databases do not adopt this backup method. 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 (the tape device is/dev/rmt0)
Log = exp_fulldb_yyyymmdd.log
For database backup, Incremental backup is recommended, that is, only the data changed since the last backup is backed up. 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 (the tape device is/dev/rmt0)
Log = exp_fulldb_yyyymmdd.log
Note: Incremental Backup must meet the following requirements:
1. It is only valid for full database backup and requires the full = y parameter for the first time. The inctype = Incremental parameter will be required later.
2. You must have an exp_full_database system role.
3. Database Backup can be used if the traffic volume is small.
4. If the disk has space, we recommend that you back up it to the disk and back up it to the tape.
　　
Business Database Backup methods and cycles
　　
Run the catexp. SQL file under the Sys user before backing up with exp (if this file has been run before, do not execute this script ).
The backup command cannot be executed on the client unless otherwise specified.
For the BACKUP command, refer to the BACKUP command in Table mode.
Back up disk files to tape
If you back up a file to a local disk, You need to dump it to the tape device.
1. To view the tape devices configured on the host, run the following command:
Lsdev-CC tape
The result is as follows:
Rmt0 available 30-58-00-4mm SCSI tape drive
Rmt1 defined 30-58-00-4mm SCSI tape drive
The device indicating available is an available tape device.
2. To view the tape storage content, run the following command:
Tar-tvf/dev/rmt0
The result is as follows:
-RW-r -- 300 400 8089600 Jan 11 14:33:57 2001 exp_icdmain_20010111.dmp
If the following content is displayed, it indicates that the backup data stored on the tape is backed up directly from the database to the tape, rather than from the local disk to the backup file of the tape, therefore, the operating system cannot be identified.
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
2
5626.
3. For new tapes or tapes without retaining existing data, run the following command:
Tar-CVF/dev/rmt0 exp_icdmain_yyyymmdd.dmp
Note: A. This command will overwrite the existing data on the tape unconditionally.
B. The file name cannot contain path information, such as/backup/exp_icdmain_yyyymmdd.dmp.
4. Use the following command to retain existing data on tapes:
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.
Note: If the backup is directly backed up from the database to the tape, you cannot append or copy any other files to the tape. Otherwise, the backup data becomes invalid.
5. to copy the backup file dumped to the tape to the local hard disk, run the following command:
A. Copy all files on the tape to the current directory of the local hard disk.
Tar-xvf/dev/rmt0
B. Copy the specified file on the tape to the current directory of the local hard disk.
Tar-xvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp
Backup schedule
Because the backup has a great impact on system I/O, we recommend that you back up the data after.
　　
Recovery of the Oracle version of the Business Database
　　
The recovery plan should be determined based on the backup plan. Because the business database adopts a combination of table backup and user backup, the recovery of the business database should adopt a combination of table recovery and user recovery based on the actual situation.
　　
Recovery Plan
　　
The logical recovery of a database is divided into three modes: Table recovery, user recovery, and full recovery.
　　
Table mode
　　
This method restores data backed up in Table mode.
A. Restore all the backup data
To restore data from a local file, run 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
To recover from a tape device, run 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. Restore the specified table in the backup data
To restore data from a local file, run 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
To recover from a tape device, run 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 will be restored Based on the Data backed up in user mode.
A. Restore all the backup data
To restore data from a local file, run 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
To recover from a tape device, run 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. Restore the specified table in the backup data
To restore data from a local file, run 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
To recover from a tape device, run 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, use the following restoration methods:
To restore data from a local file, run the following command:
IMP system/manager rows = y indexes = ncommit = y buffer = 65536
Feedback = 100000 ignore = y volsize = 0 full = y
File = exp_icdmain_yyyymmdd.dmp
Log = imp_icdmain_yyyymmdd.log
To recover from a tape device, run the following command:
IMP system/manager rows = y indexes = ncommit = y buffer = 65536
Feedback = 100000 ignore = y volsize = 0 full = y
File =/dev/rmt0
Log = imp_icdmain_yyyymmdd.log
　　
Parameter description
　　
1. Ignore Parameters
When Oracle recovers a table during data recovery, the table already exists, and you need to determine how to operate according to the ignore parameter settings.
If ignore = Y, Oracle directly inserts data into the table without executing the create table statement. If the inserted record violates the constraints, such as the primary key constraint, the error record is not inserted, however, valid records are added to the table.
If ignore = N, Oracle does not execute the create table statement and does not insert data into the table. Instead, it ignores the error of the table and continues to restore the next table.
2. Indexes Parameters
During data restoration, 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 to ensure data integrity.
　　
Character Set Conversion
　　
For a single-byte character set (such as us7ascii), the database is automatically converted to the character set of the session (the nls_lang parameter) during restoration. For a multi-byte character set (such as zhs16cgb231280, the character set should be the same as possible (avoid conversion). If conversion is required, the character set of the target database should be the superset of the output database character set.
　　
Recovery Method
　　
The business database adopts the table recovery solution. Run the catexp. SQL file under the Sys user before restoring it with IMP (if the file has been run before, do not execute this script), and then run the following command:
IMP icdmain/ICD file = File Name Log = Log File Name rows = y
Commit = y buffer = y ignore = y tables = table name
NOTE: For the table name to be restored, refer to the backup table name.
. Data is accumulated on the basis of the original table.
. No special instructions. You cannot execute recovery commands on the client.

The following example shows how to export all the tables of a database user to another database user.
Exp userid = system/manager owner = username1 file = expfile. dmp
IMP userid = system/manager fromuser = username1 touser = username2 ignore = y file = expfile. dmp

The following example shows how to export all the tables of a database user to another database user.

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 backup methods. The first type is physical backup. This method enables complete database recovery, but the database must run in the recovery mode (the business database runs in the non-recovery mode ), this method requires a large number of external storage devices, such as tape libraries. The second type of backup mode is logical backup, which is used by the business database. This method does not require the database to run in the back-to-back mode, which is not only simple, in addition, external storage devices are not required.