Oracle EXP/IMP backup Overview

Oracle EXP/IMP backup is one of the most common backup methods. In fact, Exp/Imp is not a good backup method. The correct statement is that Exp/Imp can only be a good dumping tool.

Oracle EXP/IMP backup:

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. Oracle EXP/IMP backup 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. Next we will briefly introduce the use of Oracle EXP/IMP backup.

I. Oracle EXP/IMP backup usage
Exp parameter_name = value
Or Exp parameter_name = (value1, value2 ......)
You only need to input help = y to view all the help information.
For example:
C: \> set nls_lang = simplified chinese_china.zhs16gbk
C: \> exp-help
Export: Release 8.1.6.0.0-Production on Thursday April 10 19:09:21 2003
(C) Copyright 1999 Oracle Corporation. All rights reserved.

By entering the EXP command and user name/password, you can run the following command after the user/password:
Example: exp scott/TIGER
Alternatively, you can control the running mode of "Export" by entering the EXP command with various parameters. To specify parameters, you can use the Keyword:

Format: exp keyword = value or KEYWORD = (value1, value2,..., valueN)
Instance: exp scott/tiger grants = y tables = (EMP, DEPT, MGR) or TABLES = (T1: P1, T1: P2 ), if T1 is the Partition Table USERID, it must be the first parameter in the command line. Keyword description default) keyword description default)
--------------------------------------------------------------------------
USERID username/password FULL export the entire file (N)
BUFFER data BUFFER size OWNER User Name List
FILE output FILE (EXPDAT. DMP) TABLES Table Name List
COMPRESS imports the length of a range (Y) recordlength io record
GRANTS export permission (Y) INCTYPE incremental export type
INDEXES export index (Y) RECORD trace incremental Export (Y)
ROWS export data row (Y) PARFILE parameter file name
CONSTRAINTS export restriction (Y) CONSISTENT cross tabulation consistency
The LOG file STATISTICS analysis object (ESTIMATE) output by the LOG Screen)
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 the deletable tablespace TRANSPORT_TABLESPACE to export the deletable tablespace metadata (N) List of TABLESPACES that TABLESPACES will transmit
The export is successfully terminated without warning.
C: \>

The help has explained in detail the meaning and usage of parameters, and has listed several simple examples. Note that the methods that support data subsets have started from 8i, you can specify your own Where condition and export one or more rows of data from the table. Note that the preceding set nls_lang = simplified chinese_china.zhs16gbk allows exp help to be displayed in Chinese by setting environment variables. If set nls_lang = American_america. Character set is set, your help is in English. Incremental and accumulative export are only valid in full database mode. In most cases, incremental and accumulative export are not as effective as expected. ORACLE does not support incremental export and accumulative export since 9i.

Ii. 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 platform.
· The source database and target database must use the same character set.
· The source database and target database must have 9i 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.

A. Set the tablespace to read-only. Suppose the tablespace names are APP_Data and APP_Index)
Alter tablespace app_data read only;
Alter tablespace app_index read only;

B. 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 line in SQL because the display problem is placed in two lines)

C. copy the data file to another location, that is, the target database can be cp (unix), copy (windows), or file transfer through ftp must be in the bin Mode)

D. Set the local tablespace to read/write.

E. 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 )"

F. Set the tablespace of the target database to read/write.
Alter tablespace app_data read write;
Alter tablespace app_index read write;

Iii. Export/Import and Character Set

Understand the multi-language settings of ORACLE. ORACLE multi-language settings are designed to support languages and character sets worldwide. Generally, they are used for language prompts, currency forms, sorting methods, and CHAR, VARCHAR2, CLOB, the data display of the LONG field is valid. The two main features of ORACLE's multi-language settings are the National Language settings and Character Set settings. The national language settings determine the language type of the interface or prompt, character Set determines the encoding rules when the database stores data related to the character set, such as text. As in the small example above, the environment variable NLS_LANG is different, resulting in the change of EXP help. This is the role of multi-language settings. NLS_LANG includes the National Language settings and Character Set settings, the function here is the national language setting, not the character set ).

ORACLE Character Set settings include database character set and client Character Set environment settings. On the database end, the character set is set when the database is created and saved in the database props $ table. For products above 8 I, you can use "Alter database character set" to modify the character set of the database, but it is only from the subset to the superset. Do not update props $ to modify the character set. If the conversion is not supported, all data related to character sets may be lost, that is, the conversion is supported, and the database may not work properly. Character sets include single-byte character sets and multi-byte character sets. US7ASCII is a typical single-byte character set. In this character set, length = lengthb, and ZHS16GBK is a commonly used double-byte character set, here lengthb = 2 * length.

The character set environment on the client is relatively simple, mainly environment variables or registry entry NLS_LANG. Note that the priority level of NLS_LANG is: parameter file à registry à environment variable à alter session. The composition of NLS_LANG is "National Language settings. Character Set", for example, nls_lang = simplified chinese_china.zhs16gbk. The character set of the client should be the same as that of the database. For example, for the character set of zhs16gbk, the client can be nls_lang = simplified chinese_china.zhs16gbk or Ameircan_America.zhs16gbk, without affecting normal display ), if the character set is different and the conversion of the character set is not compatible, the data displayed on the client is garbled with the data related to the exported/imported character set.

With a few tips, you can export/import data to a database with different character sets for conversion. Here we need a binary file editing tool, such as uedit32. Open the exported dmp file in editing mode and get 2 and 3 bytes, for example, 00 01. First convert it to a 10-digit number, which is 1, use the NLS_CHARSET_NAME function to obtain the character set:
SQL> select nls_charset_name (1) from dual;
NLS_CHARSET_NAME (1)
-------------------
US7ASCII
You can know that the dmp file's character set is US7ASCII. To replace the dmp file's Character Set with ZHS16GBK, you need to use NLS_CHARSET_ID to obtain the character set number:
SQL> select nls_charset_id ('zhs16gbk') from dual;
NLS_CHARSET_ID ('zhs16gbk ')
--------------------------
852

Replace 852 with the hexadecimal number, which is 354, and replace 00 01 of 2 and 3 bytes with 03 54. This completes the conversion of the dmp file character set from us7ascii to zhs16gbk, import the dmp file to the database of the zhs16gbk character set. Note: the conversion between the decimal number and the hexadecimal number can be understood)

Iv. Use Exp/Imp in different versions

Exp/Imp can be used across versions. For example, to export data between versions 7 and 8, You must select the correct version. The rule is as follows:
· The IMP version is always used to match the database version. If you want to import data to analyticdb 816, use the import tool 816.
· The EXP version is always used to match the medium and low versions of two databases. For example, if the data is exported between 815 and 816, The EXP tool 815 is used.

Importance of Oracle Database Backup

How to Implement ORACLE backup

Oracle backup command to use instances

Oracle single row date functions

Use of oracle Aggregate functions