ORACLE Backup Restore Command imp/exp

A few days to have nothing to do, just tidy up the next imp/exp

IMP/EXP is an Oracle Import Export command that can be used as a migration of data, EXPDP/IMDP is also a command for Oracle data Import export, and is more efficient than imp/exp, which is discussed later

One EXPEXP is the export command of data that can be used for tables, users, the entire database,
Exp-help


Export:release 11.2.0.1.0-production on Mon Dec 8 19:01:16 2014


Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


Can let Export prompt your for parameters by entering the EXP
Command followed by your Username/password:


Example:exp Scott/tiger


Or, can control how to Export runs by entering the EXP command followed
by various arguments. To specify parameters, your use keywords:


Format:exp Keyword=value or keyword= (value1,value2,..., Valuen)
Example:exp Scott/tiger grants=y tables= (emp,dept,mgr)
or tables= (T1:P1,T1:P2), if T1 is partitioned table


USERID must be the "the" and "the" "the" command line.


Keyword Description (default) Keyword Description (default)
--------------------------------------------------------------------------
USERID Username/password Full Export entire file (N)
BUFFER size of data buffer owner list of owner usernames
FILE output Files (expdat. DMP) TABLES List of table names
COMPRESS Import into one extent (Y) RecordLength length of IO record
Grants export Grants (Y) Inctype Incremental Export type
INDEXES export INDEXES (y) record track incr. Export (y)
Direct direct path (N) triggers export triggers (Y)
Log log file of screen output STATISTICS analyze objects (estimate)
Rows Export Data rows (Y) parfile parameter filename
Consistent cross-table Consistency (N) CONSTRAINTS export CONSTRAINTS (Y)


Object_consistent transaction set to read only during object Export (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot
Flashback_time time used to get the SCN closest to the specified time
QUERY SELECT clause used to export a subset of a table
Resumable suspend when a spaces related error is encountered (N)
Resumable_name text string used to identify resumable statement
Resumable_timeout Wait time for resumable
Tts_full_check perform full or partial dependency CHECK for TTS
Volsize number of bytes to write to each tape volume (bytes to disk)
Tablespaces List of tablespaces to export
Transport_tablespace Export Transportable tablespace metadata (N)
TEMPLATE TEMPLATE name which invokes IAS mode export


It's related to exp-help parameters.
1, 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 Username/password rows=y indexes=n compress=n buffer=65536 feedback=100000 volsize=0 file=exp.dmp log=exp.log tables =tab1,tab2,tab3


If you are backing up to a tape device directly, use the following command:
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.
Exp Scott/tiger owner=icdmain rows=y indexes=n compress=n buffer=65536 feedback=100000 volsize=0 file=/dev/rmt0 log= Exp.log


eg:[Oracle@dragon chenlong]$ exp scott/tiger rows=y indexes=n compress=n buffer=65536 feedback=100000 volsize=0 file=/home/ Oracle/chenlong/exp.dmp Log=/home/oracle/chenlong/exp.log tables=emp,dept


Export:release 11.2.0.1.0-production on Mon Dec 8 19:05:32 2014


Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


Connected to:oracle Database 11g Enterprise Edition release 11.2.0.1.0-64bit Production
With the partitioning, OLAP, Data Mining and real application testing options
Export done in Us7ascii character set and Al16utf16 NCHAR character set
Server uses Al32utf8 character set (possible charset conversion)
Note:indexes on tables won't be exported


About to export specified tables via conventional Path ...
. . Exporting Table EMP
Rows exported
Exp-00091:exporting questionable statistics.
. . Exporting Table DEPT
4 Rows Exported
Export terminated successfully with warnings.





2, 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 Scott/tiger Owner=scott rows=y indexes=n compress=n buffer=65536 feedback=100000 file=exp.dmp log=exp.log

If you are backing up to a tape device directly, use the following command:
Exp Scott/tiger Owner=scott rows=y indexes=n compress=n buffer=65536 feedback=100000 volsize=0 file=/dev/rmt0 log= Exp.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.
3, Full mode:Back up the complete database. The business database does not take this form of backup. The backup command is:
Exp Usernumber/password rows=y indexes=n compress=n buffer=65536 feedback=100000 full=y file=exp_.dmp log=exp.log


II: IMPImport should correspond with export. Is the way to export, you need to adopt the way import.

So there are three modes of import: Table recovery, user recovery, and full recovery.




Can let Import prompt to parameters by entering the IMP
Command followed by your Username/password:


Example:imp Scott/tiger


Or, can control how to Import runs by entering the IMP command followed
by various arguments. To specify parameters, your use keywords:


Format:imp Keyword=value or keyword= (value1,value2,..., Valuen)
Example:imp Scott/tiger ignore=y tables= (emp,dept) full=n
or tables= (T1:P1,T1:P2), if T1 is partitioned table


USERID must be the "the" and "the" "the" command line.


Keyword Description (default) Keyword Description (default)
--------------------------------------------------------------------------
USERID Username/password Full Import entire file (N)
BUFFER size of data buffer Fromuser list of owner usernames
FILE input Files (expdat. DMP) touser List of usernames
Show just list file contents (N) TABLES List of table names
IGNORE IGNORE Create errors (N) recordlength length of IO record
Grants import grants (Y) Inctype incremental import type
INDEXES Import INDEXES (Y) Commit commit array insert (N)
Rows Import data rows (Y) parfile parameter filename
Log log file of screen output CONSTRAINTS import CONSTRAINTS (Y)
DESTROY Overwrite tablespace data file (N)
Indexfile Write Table/index info to specified file
Skip_unusable_indexes Skip maintenance of unusable INDEXES (N)
FEEDBACK display progress every x rows (0)
Toid_novalidate Skip validation of specified type IDs
FILESIZE maximum size of each dump file
STATISTICS Import precomputed STATISTICS (always)
Resumable suspend when a spaces related error is encountered (N)
Resumable_name text string used to identify resumable statement
Resumable_timeout Wait time for resumable
COMPILE COMPILE procedures, packages, and functions (Y)
Streams_configuration Import STREAMS General metadata (Y)
Streams_instantiation Import STREAMS Instantiation metadata (N)
Data_only Import only data (N)
Volsize number of bytes in file, each volume of a file on tape


The following keywords only apply to transportable tablespaces
Transport_tablespace Import Transportable tablespace metadata (N)
Tablespaces tablespaces to is transported into database
Datafiles datafiles to is transported into database
Tts_owners users that own data in the transportable tablespace set


Import terminated successfully without warnings


1. Table ModeThis method restores the data that is backed up according to the table pattern.

1.1 Restore the full contents of the backed-up data
Imp scott/tiger fromuser=scott touser=scott rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n file=exp.dmp Log=imp.log

If you are recovering from a tape device, use the following command:
Imp scott/tiger fromuser=scott touser=scott rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n volsize=0 E=/dev/rmt0 Log=imp.log

1.2 Restore the specified table in the backup data:
If you are restoring from a local file, use the following command:
Imp scott/tiger fromuser=scott touser=scott rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n file=exp.dmp Log=imp.log TABLES=T1,T2,T3

If you are recovering from a tape device, use the following command:
Imp Scott/icd fromuser=scott touser=scott rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n volsize=0 /dev/rmt0
Log=imp.tiger TABLES=T1,T2,T3

2. User ModeThis method restores the data that is backed up according to user mode.

2.1. 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 file= Exp.dmp Log=imp.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.log

2.2. Restore 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.dmp Log=imp.log Tables=t1,t2,t3;


Three: Parameter description 3.1. Ignore parametersOracle 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. ­

3.2. Indexes ParametersIn 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.

3.3 Character Set conversionFor a single-byte character set (for example, US7ASCII), the database is automatically converted to the character set (Nls_lang parameter) of the session when it is restored;
For multibyte character sets (for example, zhs16cgb231280), when restoring, you should 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.
3.4 GrantsImport permissions
3.5 CommitWhen the parameter is Y, the import process submits the data row, and if the buffer is set too small, it will be used to submit the data frequently, which has a certain effect on the performance.

Four common problems and solutions 4.1 database objects already existIn general, you should delete the table, sequence, function/process, triggers, etc. before the data is imported. The database object already exists, and the default IMP parameter will import failure if the parameter ignore=y is used, the data content in the exp file will be imported if the table has a unique keyword constraint, the condition will not be imported if the table does not have a unique keyword constraint, it will cause the record to repeat

4.2 database objects have primary foreign key constraintsWhen the primary foreign key constraint is not met, the data fails to import.
Solution:
Import primary table First, then import dependency table
Disable the primary foreign KEY constraint on the target import object, and then enable them after importing the data

4.3 Insufficient privilegesIf you want to import a user's data into a B user, a user needs to have Imp_full_database permissions

4.4 When importing large tables (greater than 80M), storage allocation failedThe default exp, compress = Y, is to compress all the data on a block of data.
When imported, failure is imported if there is no contiguous large block of data. When you export a large table above 80M, remember compress= N, this error is not caused.

4.5 IMP and exp use different character setsIf the character set is different, the import fails, and you can change the UNIX environment variable or the NT registry Nls_lang related information. When the import is complete, change it back.

4.6 IMP and EXP versions are not compatibleYou can import a high version from a lower version, but you cannot import from a high version to a lower version.
If you experience a migration due to a different version of the issue, you can export to a lower version of exports to the low version.

Attention:

1, to pay attention to the character set when importing, character set can not be from the high end (superset to subset)

2, you should pay attention to the version problem when importing.

We'll talk about EXPDP/IMPDP later.

Reference to: http://blog.csdn.net/tianlesoftware/article/details/4718366