Oracle exp/imp

Oracle exp/imp

Import/export is the oldest two surviving Oracle command line tools. In fact, I never think exp/imp is a good backup method, the correct statement is that exp/IMP can only be a good dump tool, especially in small database dump, tablespace migration, table extraction, and detection of logical and physical conflicts. Of course, we can also use it as a logical secondary backup after physical backup of small databases, which is also a good suggestion. EXP/IMP becomes increasingly inadequate for larger databases, especially TB-level databases and more data warehouses. At this time, database backup is switched to RMAN and third-party tools. The following describes the use of exp/imp.

 

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 = increment Al 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.

 

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. Use the following command to restore all the backup data from a local file:

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

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. If the specified table in the backup data is recovered 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_csd_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. Use the following command to restore all the backup data from a local file:

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

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. If the specified table in the backup data is recovered 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_yyyymmdd.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

 

All exp parameters (the default values in parentheses ):

Userid username/password, for example, userid = duanl/duanl

Full export the entire database (N)

Buffer data buffer size

Owner owner user name list. If you want to export the user's object, use owner = Username

File output file (expdat. dmp)

Tables Table Name List, specifying the name of the exported table, such as: tables = Table1, Table2

Compress imports an extent (y)

Recordlength Io record length

Grants export permission (y)

Inctype incremental export type

Indexes export index (y)

Incremental export of record tracking (y)

Rows export data rows (y)

Parfile parameter file name. If you have many exp parameters, you can save them as parameter files.

Constraints export constraint (y)

Consistent cross tabulation consistency

Log File output by log Screen

Statistics Analysis object (estimate)

Direct direct path (N)

Triggers export trigger (y)

Feedback displays the progress of each X row (0)

Filesize maximum size of each dump file

Query the clause of the selected export table subset

The following keywords are only used for table spaces that can be transferred.

Transport_tablespace export the table space metadata that can be transferred (N)

Table space list of tablespaces to be transmitted

 

All imp parameters (the default value of the parameter in parentheses ):

Userid username/password

Full import of the entire file (N)

Buffer data buffer size

Fromuser User Name List

File input file (expdat. dmp)

Touser User Name List

Show only lists file content (N)

Tables Table Name List

Ignore ignore creation error (N)

Recordlength Io record length

Grants import permission (y)

Inctype incremental Import Type

Indexes import index (y)

Commit submits array insert (N)

Rows import data rows (y)

Parfile parameter file name

Log File output by log Screen

Constraints import restrictions (y)

Destroy overwrite the tablespace data file (N)

Indexfile writes table/index information to the specified file

Skip_unusable_indexes skips maintenance of unavailable indexes (N)

Analyze executes the analyze Statement (y) in the dump file)

Feedback displays the progress of each X row (0)

Toid_novalidate skips the verification of the specified type ID

Filesize maximum size of each dump file

Recalculate_statistics recalculates the statistical value (N)

The following keywords are only used for table spaces that can be transferred.

Transport_tablespace import the deletable tablespace metadata (N)

Tablespaces tablespace to be transmitted to the database

Data files to be transmitted to the database

Tts_owners has users who can transmit table space centralized data.

 

Tablespace Transmission

Table space transfer is a newly added 8 I Method to quickly move data between databases. It is to attach the format data files of a database to another database, instead of exporting data to a DMP file, this is very useful in some cases, because the transfer of tablespace moving data is as fast as copying a file.

There are some rules for the tablespace to be transmitted, namely:

· The source database and target database must run on the same hardware platform.

· The source database and target database must use the same character set.

· The source database and target database must have data blocks of the same size

· The target database cannot have a tablespace with the same name as the migrated tablespace.

· Sys objects cannot be migrated.

· The self-contained object set must be transmitted

· Some objects, such as materialized views and function-based indexes, cannot be transmitted.

You can use the following methods to check whether a tablespace or a set of tablespaces meets the transmission standard:

Exec SYS. dbms_tts.transport_set_check ('tablespace _ name', true );

Select * From SYS. transport_set_violation;

If no row is selected, the tablespace only contains table data and is self-contained. Some non-self-contained tablespaces, such as data table spaces and index tablespaces, can be transmitted together.

The following is a brief procedure. For more information, see Oracle online help.

1. Set the tablespace to read-only (assuming the tablespace name is app_data and app_index)

Alter tablespace app_data read only;

Alter tablespace app_index read only;

2. Issue the exp command

SQL> host exp userid = "sys/password as sysdba """

Transport_tablespace = y tablespace = (app_data, app_index)

Note that

· To execute exp in SQL, userid must be enclosed in three quotation marks, and "/" must be avoided in UNIX.

· After 816 and later, you must use sysdba to operate

· This command must be placed in one row in SQL (this is because the display problem is placed in two rows)

3. copy the data file to another location, that is, the target database.

It can be CP (UNIX), copy (Windows), or transfer files through FTP (it must be in Bin Mode)

4. Set the local tablespace to read/write

5. append the data file to the target database.

IMP file = expdat. DMP userid = "sys/password as sysdba" transport_tablespace = y "datafile = (C:/temp/app_data, C:/temp/app_index )"

6. Set the tablespace of the target database to read/write.

Alter tablespace app_data read write;

Alter tablespace app_index read write;