Oracle Database Backup and Recovery Summary -exp/imp

1.1 Basic commands

1. Get Help



$ Exp help = y

$ Imp help = y



2. Three kinds of work



(1) an interactive way

$ Exp / / Then prompted to enter the parameters needed for

(2) command line mode

$ Exp user / pwd @ dbname file = / oracle / test.dmp full = y / / Enter the required command line parameters



(3) parameters of papers

$ Exp parfile = username.par / / In the parameter file, enter the required parameters

Parameter file username.par content userid = username / userpassword buffer = 8192000

compress = n grants = y

file = / oracle / test.dmp full = y



3. Three models



(1) Table means the table will specify the data export / import.

Export:

Export one or a few table:

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log tables = table1, table2

Export of a portion of the data tables

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log tables = table1 query = \ "where col1 = \ '... \'

and col2 \ <... \ "

Import:

Import one or a few tables

$ imp user / pwd file = / dir / xxx.dmp log = xxx.log tables = table1, table2 fromuser = dbuser touser = dbuser2 commit = y ignore = y



(2) user mode, all objects in the specified user and data export / import.

Export:

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log owner = (xx, yy)

Export only the data objects, non-export data (rows = n)

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log owner = user rows = n

Import:

$ imp user / pwd file = / dir / xxx.dmp log = xxx.log fromuser = dbuser touser = dbuser2

commit = y ignore = y

(3) Full library way, all objects in the database export / import

Export:

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log full = ycommit = y ignore = y

Import:

$ Imp user / pwd file = / dir / xxx.dmp log = xxx.log fromuser = dbuser touser = dbuser2

1.2 Advanced Options

1. Split into multiple files



Of a number of fixed-size papers Export: This is usually a larger amount of data used in the table, a single dump file may be

Would exceed the limit of the file system

$ Exp user / pwd file = 1.dmp, 2.dmp, 3.dmp, ... filesize = 1000m log = xxx.log full = y

Of a number of fixed-size papers into

$ imp user / pwd file = 1.dmp, 2.dmp, 3.dmp, ... filesize = 1000m tables = xxx fromuser = dbuser touser = dbuser2 commit = y ignore = y



2. Incremental Export / Import



/ / Oracle 9i After exp Is no longer supported inctype

Must SYS Or SYSTEM Before implementation of the incremental export import

Incremental Export: Consists of three types:

(1) "full" incremental export (Complete) / / Backup the entire database

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log inctype = complete

(2) "incremental" incremental export Export changed after the last backup of data.

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log inctype = incremental

(3) "Cumulative type" incremental export (Cumulative) to export only since the last "full" Export changed after the database information.

$ Exp user / pwd file = / dir / xxx.dmp log = xxx.log inctype = cumulative

Incremental Import:

$ Imp usr / pwd FULL = y inctype = system / restore / inct ype

Of which:

SYSTEM: Importing System Objects

RESTORE: Import all user objects



3. To SYSDBA for Export / Import



1. Technical support for Oracle

2. For the table space transmission

Example:

$ imp \ 'usr / pwd @ instance as sysdba \ ' tablespaces = xx transport_tablespace = y file = xxx.dmp datafiles = xxx.dbf

$ imp file = expdat.dmp userid = "" "sys / password as sysdba "" " transport_tablespace = y

"Datafile = (c: tempapp_data, c: tempapp_index)"



4. Tablespace transfer (Fast)



Table space transmission is 8i A quick additional data in the database as a way to move is to a database format data file attached to another database, rather than to export the data into the dmp Document, which is sometimes very useful, because the space of mobile data transmission tables as fast as copying the files.

1. With regard to transfer the table space in a number of rules (10g ago):

? The source database and target database must be running on the same hardware platform.



? Source database and target database must use the same character set.

? Source database and target database must have the same size of data blocks

? Target database can not have and migration of the same name as the table space tablespace

? SYS objects can not migrate

? Be transmitted self-contained set of objects

? Some objects, such as materialized views, function-based index can not be transferred, etc.

(The same byte order the file cross-platform data files can replace the header of the method)

(10g Support cross-platform tablespace transport, as long as the operating system, the same byte order, you can transfer the table space. Need to use the RMAN convert the file format, abbreviated)

2. Detection of a table space is in line with the transmission standard method:

SQL> exec sys.dbms_tts.transport_set_check ( 'tablespace_name', true); SQL> select * from sys.transport_set_violations;

If no row selection, indicating that the table space contains only the table data, and is a self-contained. For some non-self-packets

Containing the table space, such as data table space and index table spaces, and you can transfer.



3. Briefly the use of steps:

If you want to refer in detail to use, you can also refer to ORACLE online help.

1. Set tablespace read-only (assuming the table space name is APP_Data And APP_Index)

SQL> alter tablespace app_data read only; SQL> alter tablespace app_index read only;

2. To issue EXP command

SQL> host exp userid = "" "sys / password as sysdba" "" transport_tablespace = y tablespaces = (app_data, app_index)

Note that the above

In order to execute SQL * EXP, USERID must be three quotes, in UNIX, must also be taken to avoid "/" Use of

In 816 and beyond must use sysdba in order to operate

* This command in the SQL must be placed in the line (in this case because the display problem on two lines)

3. Copy. Dbf data files (and. Dmp files) to another location, that is the target database

Can be cp (unix) or cop y (windows) or by ftp to transfer files (must be in the bin method)

4. To the local tablespace set to read and write

$ Alter tablespace app_data read write;

$ Alter tablespace app_index read write;

5. In the target database, attach the data file (Either directly specify the data file name)

(Table space can not exist, we must establish the appropriate user name or use fromuser / touser)

$ Imp file = expdat.dmp userid = "" "sys / password as sysdba" "" transport_tablespace = y datafiles = ( "c: \ app_data.dbf, c: \ app_index.dbf") tablespaces = app_data, app_index tts_owners = hr, oe

6. Set target database table space for reading and writing

$ Alter tablespace app_data read write;

$ Alter tablespace app_index read write;



1.3 Optimization

1. Accelerate the pace of exp



Increase large_pool_size, can improve the speed of exp using direct path method (direct = y), the data did not require the integration of memory and checks. To set a larger buffer, if the exporting large objects, small buffer will fail.

export file is not used on the drive ORACLE not export to the NFS file system

UNIX Environment: pipe model directly into lead out to improve imp / exp performance



2. Accelerate the pace of imp



The establishment of a indexfile, after the completion of the data import indexing

The import file on a different drive to increase DB_BLOCK_BUFFERS

Increase LOG_BUFFER

Archiving run a non-ORACLE: ALTER DATABASE NOARCHIVELOG; the establishment of a large table space and rollback segments, OFFLINE other rollback segments, rollback segment size of the largest table 1 / 2 to use COMMIT = N

Use ANALYZE = N

Single-user mode to import

UNIX Environment: pipe model directly into lead out to improve imp / exp performance



3. Through the unix / Linux PIPE pipeline to speed up exp / imp speed



Through pipelines to export data:

1. Through establishment of pipe mknod-p

$ Mknod / home / exppipe p / / In the directory / home to build a pipeline under the exppipe attention parameters p

2. By exp, and gzip export data to build the pipeline and compression

$ Exp test / test file = / home / exppipe & gzip </ home / exppipe> exp.dmp.gz

$ Exp test / test tables = bitmap file = / home / newsys / test.pipe &

gzip </ home / newsys / test.pipe> bitmap.dmp.gz

3. Export to delete the establishment of the successful completion of the pipeline

$ Rm -rf / home / exppipe



Export script:

# # # UNIX pipes under the ORACLE database, back up through the PIPE

###### Using "export" and "tar" command to bakup oracle datebase #######



trap "" 1 # nohup

LOGFILE = / opt / bakup / log / bakup_ora.log



export LOGFILE

DUMPDIR = / archlog_node1

export DUMPDIR

exec> $ LOGFILE 2> & 1



echo

echo 'Begin at' `date`

echo



# clear old result file

cd $ DUMPDIR

if [-f exp.dmp.Z]

then

echo "clear old result file"

rm exp.dmp.Z

fi



# make pipe

mkfifo exp.pipe

chmod a + rw exp.pipe



# gain the dmp.Z file

compress <exp.pipe> exp.dmp.Z &

su-u oracle-c "exp userid = ll / ll file = $ DUMPDIR / exp.pipe full = y buffer = 20000000"



echo

echo ' exp end at ' `date`

echo





# Rm pipe

rm exp.pipe





# tar the dmp.Z file to tape

mt-f / dev/rmt/0 rew

tar cvf / dev/rmt/0 exp.dmp.Z



echo

echo ' tar end at ' `date`

echo



Through pipelines to import generated file:

1. Through establishment of pipe mknod-p



$ Mknod / home / exppipe p

2. To import the generated compressed file

$ Imp test / test file = / home / exppipe fromuser = test touser = macro &

gunzip <exp.dmp.gz> / home / exppipe

3. Remove the pipe

$ Rm-fr / home / exppipe



4. The general steps to import the whole library



Note: In the export, you need through toad or any other tool to extract a source database to create the primary key and indexes in the script

1. The first full-Cuga rows = n the structure of lead into the

$ Imp system / manager file = exp.dmp log = imp.log full = y rows = n indexes = n

2. So that business users trigger failure / Remove the primary key and unique index

spool drop_pk_u.sql

select 'alter table' | | table_name | | 'drop constraint' | | constraint_name ||';'

from user_constraints

where constraint_type in ( 'P', 'U');

/

spool off

spool disable_trigger.sql

select 'alter trigger' | | trigger_name | | 'disable;'

from user_triggers;

/

spool off



@ drop_pk_u.sql

@ disable_trigger.sql



3. To ignore = y full database import

$ Imp system / manager file = exp.dmp log = imp.log full = y ignore = y

4. By toad or any other tool to extract a source database to create the primary key and indexes of scripts in the target database to create the primary key

And indexes. To trigger the entry into force.



1.3 Optimization

1. Accelerate the pace of exp



Increase large_pool_size, can improve the speed of exp using direct path method (direct = y), the data did not require the integration of memory and checks. To set a larger buffer, if the exporting large objects, small buffer will fail.

export file is not used on the drive ORACLE not export to the NFS file system

UNIX Environment: pipe model directly into lead out to improve imp / exp performance



2. Accelerate the pace of imp



The establishment of a indexfile, after the completion of the data import indexing

The import file on a different drive to increase DB_BLOCK_BUFFERS

Increase LOG_BUFFER

Archiving run a non-ORACLE: ALTER DATABASE NOARCHIVELOG; the establishment of a large table space and rollback segments, OFFLINE other rollback segments, rollback segment size of the largest table 1 / 2 to use COMMIT = N

Use ANALYZE = N

Single-user mode to import

UNIX Environment: pipe model directly into lead out to improve imp / exp performance



3. Through the unix / Linux PIPE pipeline to speed up exp / imp speed



Through pipelines to export data:

1. Through establishment of pipe mknod-p

$ Mknod / home / exppipe p / / In the directory / home to build a pipeline under the exppipe attention parameters p

2. By exp, and gzip export data to build the pipeline and compression

$ Exp test / test file = / home / exppipe & gzip </ home / exppipe> exp.dmp.gz

$ Exp test / test tables = bitmap file = / home / newsys / test.pipe &

gzip </ home / newsys / test.pipe> bitmap.dmp.gz

3. Export to delete the establishment of the successful completion of the pipeline

$ Rm -rf / home / exppipe



Export script:

# # # UNIX pipes under the ORACLE database, back up through the PIPE

###### Using "export" and "tar" command to bakup oracle datebase #######



trap "" 1 # nohup

LOGFILE = / opt / bakup / log / bakup_ora.log



export LOGFILE

DUMPDIR = / archlog_node1

export DUMPDIR

exec> $ LOGFILE 2> & 1





echo

echo 'Begin at' `date`

echo





# clear old result file

cd $ DUMPDIR

if [-f exp.dmp.Z]

then

echo "clear old result file"

rm exp.dmp.Z

fi





# make pipe

mkfifo exp.pipe

chmod a + rw exp.pipe





# gain the dmp.Z file

compress <exp.pipe> exp.dmp.Z &

su-u oracle-c "exp userid = ll / ll file = $ DUMPDIR / exp.pipe full = y buffer = 20000000"





echo

echo ' exp end at ' `date`

echo





# rm pipe

rm exp.pipe





# tar the dmp.Z file to tape

mt-f / dev/rmt/0 rew

tar cvf / dev/rmt/0 exp.dmp.Z





echo

echo ' tar end at ' `date`

echo



Through pipelines to import generated file:

1. Through establishment of pipe mknod-p



$ Mknod / home / exppipe p

2. To import the generated compressed file

$ Imp test / test file = / home / exppipe fromuser = test touser = macro &

gunzip <exp.dmp.gz> / home / exppipe

3. Remove the pipe

$ Rm-fr / home / exppipe





4. The general steps to import the whole library



Note: In the export, you need through toad or any other tool to extract a source database to create the primary key and indexes in the script

1. The first full-Cuga rows = n the structure of lead into the

$ Imp system / manager file = exp.dmp log = imp.log full = y rows = n indexes = n

2. So that business users trigger failure / Remove the primary key and unique index

spool drop_pk_u.sql

select 'alter table' | | table_name | | 'drop constraint' | | constraint_name ||';'

from user_constraints

where constraint_type in ( 'P', 'U');

/

spool off

spool disable_trigger.sql

select 'alter trigger' | | trigger_name | | 'disable;'

from user_triggers;

/

spool off





@ drop_pk_u.sql

@ disable_trigger.sql



3. To ignore = y full database import

$ Imp system / manager file = exp.dmp log = imp.log full = y ignore = y

4. By toad or any other tool to extract a source database to create the primary key and indexes of scripts in the target database to create the primary key

And indexes. To trigger the entry into force.



1.4 Frequently Asked Questions

1. Problems With Character Sets



ORACLE Multi-language setting is to support the world's languages and character sets, the general language tips, currency format, sorting methods and CHAR, VARCHAR2, C LOB, LONG Field displays the data efficiently. ORACLE The multi-language settings of the two most important features is the national language settings and character sets, national language setting determines the language used interface or prompt type the database character set determines the character set the data preservation and

(Eg, text) when the encoding rules.

ORACLE character set settings, is divided into the database character set and client character set environment settings. In the database side,



Character set when creating a database set up, and stored in a database props $ table.

In the client's character set is relatively simple, the main is the environment variables or registry entries NLS_ LANG, note that the priority level of NLS_LANG as follows: parameter file "registry" environment variable <alter session. If the client character set and server-side character set is different, and the character set conversion is not compatible, then the client's data show that with the export / import character set with all the relevant data will be garbled.

Use a little skill, you can export / import in a different database character set conversion data. Here

The need for a two binary file editing tool can be, such as uedit32. Open the exported with the editing dmp file, be

Take 2, 3 Bytes of content, such as 00 01, first it is converted to 10 Binary number, in order to 1, using the function

NLS_CHARSET_NAME you can get the character set: SQL> select nls_charset_name (1) from dual; NLS_CHARSET_NAME (1)

------------------- US7ASCII

Dmp file to know the character set for the US7ASCII, this dmp file if you need to change the character set

Cheng ZHS16GBK, you need to get the character set to use NLS_CHARSET_ID number: SQL> select nls_charset_id ( 'zhs16gbk') from dual; NLS_CHARSET_ID ( 'ZHS16GBK')

--------------------------

The 852 replaced by 16 hexadecimal number, for the 354, the 2,3-byte 00 01 replaced by 03 54, which completed the character set to the dmp file from us7ascii to zhs16gbk conversion, so that then the dmp file into the database character set zhs16gbk on it.



2. Version of the problem



Exp / Imp In many cases, can be used across versions, such as between version 7 and version 8 export import data, but it must choose the correct version, the rules as follows:

* Always use IMP's version of the matching version of the database, if you want to import into the 816, use 816 of the Import Tool.

* Always use the version of EXP match the two databases that version of the low, such as between 815 and 816 each guide, use 815 of the EXP tool.

imp and exp can not be upward compatible with versions: imp can import a low version of the exp-generated documents, Can not import the high version of the exp generated files.





Original Address http://blog.sina.com.cn/s/blog_525394060100gbnx.html Published on: 2010-01-27, modified on: 2010-01-27 12:26 has been here 12 times there have been comments 0 recommend Complaints

User Reviews Comments

• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 

Recent

• expand the hard disk in "oracle virtualbox
• ora-00600 2662 startup upgrade
• what is rhel5u3 kernel
• objective c redraw
• how to purge perfstat tablespace in oracle 10g
• impdp parallel slow
• where must ojdbc14.jar file be placed
• gedit adding ^m characters at end of line
• ERROR: JAVA_HOME is set to an invalid directory.
• Check DataBase failed - [00000]Not Exist

Recent Entries

• oracle asmlib in the trap of using multiple paths
• tnsnames.ora configured incorrectly can cause serious problems
• create / rebuild index online Do not cancel or killed
• SQL language of the natural connection
• test oracle transaction isolation level serializable

Tag Cloud

Random Entries

• forall bulk
• Database users to create and modify user information
• Physically established under oracle data guard (6): static and dynamic
• Article Index ---- yangtingkun personal BLOG aspects of the problem diagnosis
• Use ssh to achieve oracle database backup file, automatic off-site storage

Latest Comments

• Selim Hi, We've experienced the same trouble. ...
• dba thank's for this information
• mj farmad Helo I am thankful for your descriptions ...
• Don_Usman Ingame name: Don_Usman Real life name: M ...
• Jakes ftp hosting This makes the process slightly slower b ...
• prakash gfhgfhgfhgfhgf
• IVANOV FLORIN OK
• azer 334bdf4effba2fb4ec3976effe6ffce0
• Varun Rajasekaran this solution worked...I was facing ORA- ...
• Dr.Vet. Cumpanasu Florin Why no INSTR function for SQLite?