Oracle data import and export command

2011-04-28  来源:本站原创  分类:Database  人气:98 

Oracle data import and export imp / exp is equivalent to oracle data restore and backup. exp command to export data from a remote database server to the local dmp file, imp command to import the dmp file to the distance from the local database server. This function can be built using two identical databases, one for testing, one for official use.
Execution environment: in SQLPLUS.EXE or DOS (command line) to perform
oracle using java prepared, SQLPLUS.EXE, EXP.EXE, IMP.EXE these two documents may have been packaged class files. SQLPLUS.EXE call EXP.EXE, IMP.EXE wrapped in class to complete import and export functions.

Here are examples of import and export.
Data Export:
TEST 1 full export database, user name, password manager system to export to the D: \ daochu.dmp in
exp system / manager @ TEST file = d: \ daochu.dmp full = y
2 database system user to export the table with the sys user
exp system / manager @ TEST file = d: \ daochu.dmp owner = (system, sys)
3 tables in the database inner_notify, notify_staff_relat Export
Exp system / manager @ test file = d: \ daochu.dmp tables = (inner_notify, notify_staff_relat)
4 in a database table table1 fields filed1 to "00" starts with the data derived
exp system / manager @ TEST file = d: \ daochu.dmp tables = (table1)
query = \ "where filed1 like '00% '\"

Derived above is used for compression, both the dmp file with winzip compression can be very good.
Also in the above command with compress = y to achieve.
Data import
1 D: \ daochu.dmp the data into the TEST database.
imp system / manager @ TEST file = d: \ daochu.dmp
imp aichannel / aichannel @ HUST full = y file = d: \ data \ newsmgnt.dmp ignore = y
Above may have a problem, because some of the table already exists, then it will error, not to import the table.
Followed by the ignore = y on it.
2 d: \ daochu.dmp import in the table table1.
imp system / manager @ TEST file = d: \ daochu.dmp tables = (table1)

Basically, the above import and export enough. First, many cases to completely remove the table, then import.
Note:
The operator must have sufficient permissions, the permissions it will not prompt.
Can connect to the database. Can tnsping TEST TEST able to get connected to the database.
Appendix I:
Permission to import the data to the user's operation to increase the first to start sql * puls
Second, the system / manager landed third, create user username IDENTIFIED BY password (if the user has been created, this step can be omitted)
Fourth, 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 of the fifth, run-cmd-dmp files into the directory where the
imp userid = system / manager full = y file =*. dmp
Or imp userid = system / manager full = y file = filename.dmp

Execution Example:
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 Thursday February 16 16:50:05 2006
(C) Copyright 2000 Oracle Corporation. All rights reserved.

Connect to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

Via conventional path export from the EXPORT: V08.01.07 created files have been completed ZHS16GBK ZHS16GBK NCHAR character set and character set import and export server uses UTF8 NCHAR character set (possible ncharset conversion)
The object being AICHANNEL into AICHANNEL
Importing table "INNER_NOTIFY" 4 lines are imported ready to enable constraints ...
Import terminated successfully with warnings.

Appendix II:
Oracle does not allow to change the table owner directly using the Export / Import can achieve this goal.
First establish import9.par, then, use the following command: imp parfile = / filepath/import9.par
Example import9.par as follows:
FROMUSER = TGPMS
TOUSER = TGPMS2 (Note: the owner of the table by the FROMUSER to TOUSER, FROMUSER and TOUSER user can be different)
ROWS = Y
INDEXES = Y
GRANTS = Y
CONSTRAINTS = Y
BUFFER = 409600
file == / backup/ctgpc_20030623.dmp
log == / backup/import_20030623.log

A database of a user's guide for all tables to another database following an example of a user
exp userid = system / manager owner = username1 file = expfile.dmp
imp userid = system / manager fromuser = username1 touser = username2
ignore = y file = expfile.dmp;

ORACLE database there are two types of backup methods. The first category is the physical backup, the method to achieve a complete database recovery, but the database must run in the return block mode (business database in the non-return block mode), and required a great deal of external storage devices such as tape libraries; first second-class backup for logical backup, business databases used in this way, this method does not require the database to run in the return block mode, not only back up is simple, and can not require an external storage device.

Logical database backup methods
ORACLE database logical backup is divided into three modes: table backup, user backup and full backup.

Table model backing up a user mode the specified object (table). Service databases often use this backup.
If the backup 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 the backup directly to tape devices, 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: The disk space permitting, should be back up to the local server and then copied to tape. For speed considerations, so as not to backup directly to tape devices.

User mode backup a user mode of all objects. Service databases often use this backup.
If the backup 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 the backup directly to tape devices, 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 disk space, we recommend backing up to disk, then copy to tape. If the database is small amount of data, this approach can be used to back up.

Full mode full database backup. Business databases do not use this backup. Backup command:
exp icdmain / icd rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0 full = y
file = exp_fulldb_yyyymmdd.dmp (compared to tape devices / dev/rmt0)
log = exp_fulldb_yyyymmdd.log
For database backup, incremental backup is recommended, that is, only backup changes since the last backup of the data. 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 (compared to tape devices / dev/rmt0)
log = exp_fulldb_yyyymmdd.log
Note: For incremental backups must meet the following conditions:
1 only full database backup is valid, and the first time need full = y parameters, the future need inctype = incremental parameters.
2. EXP_FULL_DATABASE system user must have a role.
3 small traffic may use the database backup.
4 If the disk space, we recommend backing up to disk, then backup to tape.
Database backup methods and business cycle with EXP backup before running the SYS user CATEXP.SQL file (If you have previously run the file, then do not execute this script).
No special instructions, allow the client to perform a backup command.

Backup command reference table mode, the backup command.
File from the disk backup to tape if the first backup to local disk files, you need to dump to tape devices.
1. For view the tape devices configured on the host, use the following command:
lsdev-Cc tape
The results 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
Available equipment is marked with tape devices available.
(2) For view the contents of tape storage, use the following command:
tar-tvf / dev/rmt0
The results shown in the following example:
-Rw-r - r - 300 400 8089 600 Jan 11 14:33:57 2001 exp_icdmain_20010111.dmp
If the display looks something like this, it means the tape backup data is stored directly from the database backup to tape, rather than from local disk backup dump file to tape, so the operating system does not recognize.
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 25626.
3 for a new tape or tapes without the need to retain the existing data, use the following command:
tar-cvf / dev/rmt0 exp_icdmain_yyyymmdd.dmp
Note: A. The command will unconditionally overwrite the existing data on the tape.
B. file name must not contain path information, such as: / backup / exp_icdmain_yyyymmdd.dmp.
4 for the need to retain the existing data on the tape, use the following command:
tar-rvf / dev/rmt0 exp_icdmain_yyyymmdd.dmp
Note: This command will append to the tape file exp_icdmain_yyyymmdd.dmp the end, does not overwrite existing data.
Special emphasis: If the backup directly from the database backup to tape, can not revert to an additional copy of the tape, any other document, or the failure of backup data.
5 For the dump to tape copy the backup file to a local hard disk, use the following command:
A. will copy all the files on the tape drive's current directory to the local
tar-xvf / dev/rmt0
B. Place the tape on the specified files to the local hard disk of the current directory
tar-xvf / dev/rmt0 exp_icdmain_yyyymmdd.dmp
Backup schedule when the backup system as I / O have a greater impact, therefore, recommended after 11 at night for backup jobs.
Business version of Oracle database recovery, backup recovery program based on identified program needs. As the backup database tables and business users a combination of backup programs, so businesses need to restore the database using the table based on the actual situation of the combination of recovery and restoration of the user program.

Recovery program to restore the database logic into the table to restore, user recovery, full recovery of three modes.

Table mode this way will be based on the data in table mode to restore the backup.
A. restore backup data of all the contents from a local file recovery, 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 recovery 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. restore backup data in the specified table, if recovery 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 recovery 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

This approach will be based on user-mode according to user mode to restore the backup data.
A. restore backup data of all the contents from a local file recovery, 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 recovery 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. restore backup data in the specified table, if recovery 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 recovery 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

If the full backup mode to full mode, use the following recovery methods:
If recovery 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 recovery 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
Parameters
1. Ignore parameters
Oracle in the process of restoring data, when restored to a table, the table already exists, it is necessary to set the parameters according to ignore to determine how to operate.
If ignore = y, Oracle does not execute the CREATE TABLE statement, data directly into a table, insert the record if the violation of constraints, such as primary key constraint, then the error record is not inserted, but the legal record is added to the table .
If ignore = n, Oracle does not execute the CREATE TABLE statement, and nor will the data into a table, but ignore the table of the error, continue to recover the next table.
2. Indexes parameters
In the process of restoring data, if indexes = n, then the index on the table will not be restored, but only the primary key index corresponding to the unconditional restoration, which is to ensure data integrity.

Character set conversion for single-byte character set (such as US7ASCII), recovery, the database automatically switch to the session character set (NLS_LANG parameter); for multi-byte character set (such as ZHS16CGB231280), recovery should be the same character set as far as possible (to avoid the conversion), if you want to convert the target database character set should be the output of the database character set superset.

Recovery method used business database table recovery program. With the IMP in the recovery before the SYS user to run CATEXP.SQL file (If you have previously run the file, then do not execute this script), then run the following command:
IMP ICDMAIN / ICD FILE = filename LOG = LOG file name ROWS = Y
COMMIT = Y BUFFER = Y IGNORE = Y TABLES = table Note: To restore the table refer to the table to restore the backup in the original table based on the cumulative data is not specified, the client does not allow the restore command

相关文章