IMP and the EXP Command
Oracle data import and export imp/exp is equivalent to Oracle data restore and backup. The EXP command can export data from a remote database server to a local DMP file, and the IMP command can import the DMP file from a local to a distant database server. With this feature you can build two identical databases, one for testing and one for formal use.
Execution environment: can be executed in SQLPLUS.EXE or DOS (command line), DOS can be executed because the installation directory in Oracle 8i \ora81\bin is set to the global path, the directory has EXP.EXE and IMP.EXE files are used to perform import and export.
Oracle is written in Java, SQLPLUS. EXE, EXP. EXE, IMP. EXE these two files are likely to be packaged after the class file. SQLPLUS. EXE calls EXP.EXE, IMP. EXE to complete the import and export function.
The following is an example of importing and exporting.
Data export:
1 full export of database test, user Name System Password Manager exported to D:\daochu.dmp
Exp System/[email protected] file=d:\daochu.dmp full=y
2 Exporting the system user in the database to the SYS user's table
Exp System/[email protected] file=d:\daochu.dmp owner= (System,sys)
3 Exporting Tables Inner_notify, Notify_staff_relat in the database
EXP System/[email protected]TEST file=d:\daochu.dmp tables= (table1, table2,table3 ... )
4 Export the field filed1 in table table1 in the database with data beginning with "00"
exp System/[email protected] file=d:\daochu.dmp tables= (table1)
Query=\ "where filed1 like ' 0% ' \"
The above is commonly used for the export, for compression, both with WinZip to the DMP file can be very good compression.
It can also be implemented by adding compress=y to the above command.
Import of data
1 Import the data from the D:\DAOCHU.DMP into the test database.
imp system/[email protected] file=d:\daochu.dmp
imp aichannel/[email protected] full=y file= d:\data\newsmgnt.dmp ignore=y
There may be a problem, because some tables already exist, and then it is an error, and the table is not imported.
Add Ignore=y to the back.
2 Import the table table1 in D:\daochu.dmp
imp system/[email protected] file=d:\daochu.dmp tables= (table1)
Basically, the above import and export is enough. In many cases, the table must be completely removed and then imported.
Attention:
The operator must have sufficient permissions, and it will prompt for insufficient permissions.
Database, you can connect to the. You can use tnsping test to get the database test to connect.
Appendix I:
Adding permissions to the import data to the user
First, start sql*puls
Second, landing with System/manager
Third, create user username identified by password (this step can be omitted if the user has already been created)
IV, GRANT create User,drop user,alter USER, create any VIEW,
DROP any view,exp_full_database,imp_full_database,
Dba,connect,resource,create SESSION to User name
V, run-cmd-into the directory where the DMP file is located,
Imp Userid=system/manager full=y file=*.dmp
or imp Userid=system/manager full=y file=filename.dmp
Example execution:
F:\work\oracle_data\backup>imp userid=test/test full=y file=inner_notify.dmp
Screen display
Import:release 8.1.7.0.0-production on Thu February 16 16:50:05 2006
(c) Copyright: Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0-production
With the partitioning option
Jserver Release 8.1.7.0.0-production
Export files created by export:v08.01.07 through a regular path
Import is completed in ZHS16GBK character set and ZHS16GBK NCHAR character set
The export server uses the UTF8 NCHAR character set (possible ncharset conversions)
Importing Aichannel objects into Aichannel
Importing table "Inner_notify" 4 rows are imported
Prepare to enable constraints ...
The import was terminated successfully, but a warning appears.
Appendix II:
Oracle does not allow direct changes to the table owner, which can be achieved with export/import.
First establish the Import9.par, then, use the command as follows: Imp Parfile=/filepath/import9.par
Example Import9.par content is as follows:
Fromuser=tgpms
TOUSER=TGPMS2 (Note: Users who change the owner of the table from Fromuser to Touser,fromuser and Touser can be different)
Rows=y
Indexes=y
Grants=y
Constraints=y
buffer=409600
File==/backup/ctgpc_20030623.dmp
Log==/backup/import_20030623.log
An example of a user of a database that guides all the tables of a user 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 ;
There are two types of backup methods for Oracle databases. The first class is a physical backup, which implements a full database recovery, but the database must be run in the back-up mode (the business database runs in non-return mode) and requires a great deal of external storage devices such as a tape library, a second type of backup as a logical backup, a business database in this way, This method does not require that the database be run in the back-up mode, it is simple to backup, and can not require an external storage device.
Database Logical Backup method
The logical backup of an Oracle database is divided into three modes: Table backup, user backup, and full backup.
Table mode
Backs up the specified object (table) in a user mode. The business database typically uses this backup method.
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 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 directly to the tape device.
User mode
Backs up all objects in a user mode. The business database typically uses this backup method.
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 the disk has space, it is recommended that you back up to disk and then copy to tape. If the amount of database data is small, this method can be used for backup.
Full mode
Back up the full database. The business database does not use 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 (tape device is/dev/rmt0)
Log=exp_fulldb_yyyymmdd.log
For database backups, it is recommended that you take an incremental backup that 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: For incremental backups, the following conditions must be met:
1. Only valid for full database backups, and the first time you need to full=y parameters, you need to inctype=incremental parameters later.
2. The user must have a exp_full_database system role.
3. Database backup can be used when the traffic volume is small.
4. If the disk has space, it is recommended that you back up to disk, and then back up to tape.
Service database backup method and cycle
Before backing up with Exp, run the Catexp.sql file under the SYS user (do not execute this script if the file has been previously run).
No special instructions are allowed to perform backup commands on the client.
The backup command refers to the backup command in table mode.
Backup from disk files to tape
If you first back up to a local disk file, you need to dump it on the tape device.
1. To view the tape devices configured on the host, use the following command:
LSDEV-CC Tape
The results shown are 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 a tape device that is available.
2. To view the contents of the tape storage, use the following command:
Tar-tvf/dev/rmt0
The results shown are shown in the following example:
-rw-r--r--8089600 Jan 14:33:57 2001 EXP_ICDMAIN_20010111.DMP
If the display resembles the following, the backup data for that tape store is backed up from the database directly to tape, not the backup file from the local disk to the tape, 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 equal to 25626.
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 names do 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.
In particular, if the backup is backed up directly from the database to tape, no additional files can be copied to the tape or the backup data will be invalidated.
5. If you want to copy the backup files to the local hard disk, use the following command:
A. Copy all the files on the tape to the current directory on your local hard disk
Tar-xvf/dev/rmt0
B. Copy the specified file on the tape to the current directory on the local hard disk
Tar-xvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp
Backup Schedule
Because of the large impact on system I/O during backup, it is recommended that you perform backup work after 11 o'clock in the evening.
Recovery of the Oracle version of the business database, the recovery scenario is determined based on the backup scenario. Due to the combination of table backup and user backup in the business database, the recovery of the business database needs to be based on the actual scenario of table recovery and user recovery.
Recovery scenarios
The logical recovery of the database is divided into three modes: Table recovery, user recovery and full recovery.
Table mode
This method restores the data backed up according to the table schema.
A. Restoring the full contents of the backup data
If recovering 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_csd_yyyymmdd.dmp
Log=imp_icdmain_csd_yyyymmdd.log
If 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. Recovering a specified table from backup data
If recovering 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 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 will be restored based on data backed up in user mode.
A. Restoring the full contents of the backup data
If recovering 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_yyyymmdd.dmp
Log=imp_icdmain_yyyymmdd.log
If 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. Recovering a specified table from backup data
If recovering 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_yyyymmdd.dmp
Log=imp_icdmain_yyyymmdd.log
Tables=commoninformation,serviceinfo
If 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 method is full mode, the following recovery methods are used:
If recovering 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 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 parameters
When Oracle recovers data, when a table is restored, the table already exists and depends on the setting of the ignore parameter to determine what to do.
If Ignore=y,oracle does not execute the CREATE TABLE statement, inserts the data directly into the table, and if the inserted record violates the constraint, such as a primary key constraint, the record of the error is not inserted, but the valid record is added to the table.
If Ignore=n,oracle does not execute the CREATE TABLE statement and does not insert the data into the table, it ignores the table error and resumes the next table.
2. Indexes parameters
In the process of recovering data, the index 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 indexes=n.
Character Set conversions
For a single-byte character set (for example, Us7ascii), when recovering, the database is automatically converted to the session's character set (Nls_lang parameter), and for multibyte character sets (for example, zhs16cgb231280), the character set should be the same as possible (avoid conversions), 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 recovering with IMP, run the Catexp.sql file under the SYS user (if the file was previously run, do not execute the script), and then execute 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: The table name to be restored references the table name of the backup
Recovery is the accumulation of data on the basis of the original table
No special instructions, do not allow recovery command on client
For more information, please login: online, www.xconnet.com
Oracle Data Import Export command