Summary of Character Set and Oracle export and import problems

This section describes the environment variable NLS_LANG. The client uses the first NLS_LANG as the reference character set. The sequence is user-level environment variable and system-level environment variable.

This section describes the environment variable NLS_LANG. The client uses the first NLS_LANG as the reference character set. The sequence is user-level environment variable and system-level environment variable.

1. Summary of Character Set and Export Import Problems

First, ensure that the Oracle database server is consistent with the client character set to ensure data encoding consistency.

When the database server selects ZHS16GBK, when exporting and importing new data, the database server performs the ZHS16GBK encoding check on the imported binary database. When the data length is odd, if the last byte of the data is in decimal format: 129 to 254, this byte is lost. For details, see section 2.

When WE8ISO8859P1 is selected for the database server, when the new data is exported and imported, the database server performs WE8ISO8859P1 encoding check on the imported binary database, in bytes one by one, no data loss occurs. For details, see section 2;

Data loss occurs when data in the ciphertext table is exported and imported under different character sets.

Description of ZHS160GBK and WE8ISO8859P1 character sets

For the two character sets mentioned above, ZHS16GBK is the default database character set when Oracle is installed in Windows. WE8ISO8859P1 is the default database character set installed in Linux. Both character sets support Chinese characters. For the selection of national character sets, AF16UTF16 is selected here. After the database is installed, you can view it using the select userenv ('language') from dual statement. For the ZHS16GBK character set, the database processes the data in Double Bytes. When the decimal number of bytes ranges from 129 to 254, Oracle processes the data in double bytes to find the next byte, the two bytes constitute a whole for processing. However, for WE8ISO8859P1, the database is completely processed by single byte, so there is no Bytes check.

Iii. Description of the situation where the database is ZHS16GBK

1. Database Configuration

In Windows, we use the default Oracle Installation. When DBCoffer works normally, we need to modify listener. ora. The reference configuration is as follows:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = ljb)

(ORACLE_HOME = E: \ oracle \ product \ 10.2.0 \ db_1)

)

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = E: \ oracle \ product \ 10.2.0 \ db_1)

(ENVS = EXTPROC_DLLS = ANY)

(PROGRAM = extproc)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC) (KEY = extproc ))

(ADDRESS = (PROTOCOL = TCP) (HOST = Jiabo) (PORT = 1521 ))

)

)

Table 1

In this case, you can find the following settings in the registry:

NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK

2 Description of encryption and decryption

After DBCoffer is properly installed, data can be encrypted and decrypted.

3. Import Export description

After a table is encrypted, we know that the encrypted ciphertext length is an odd value. After exporting the ciphertext table to the database using the exp command, rename the original ciphertext table, then, import the exported data to the database. In this case, the last byte of the data in the ciphertext field is lost. When the imported ciphertext table is exported again, when compared with the binary file of the original exported table, the apparent data is not matched, and the lost byte size ranges from 129 to 254 in the corresponding decimal format, it can be determined that the problem is caused by the processing of the database character set, because the ciphertext length is an odd value. If the last byte of the ciphertext data is between 129 and 254, as described above, the Oracle server processes the last byte in Double Bytes. If the next byte is not found

This byte is lost.

We can use lengthb (ciphertext field) to view the above information. The ciphertext length of the original ciphertext table is a fixed odd value, the ciphertext fields in the ciphertext table imported again are 1 less than the odd value. Therefore, the query for the ciphertext table reports-10004, And the encrypted data is incomplete.

Iv. Description of the situation when the database is WE8ISO8859P1

1. Create an Oracle database instance iso8859

In this step, you can use the DBCA tool to set the database character set as shown in.

Figure 1

2. modify the configuration of listener. ora and tnames. ora in Oracle.

The configuration of listener. ora is as follows:

# Listener. ora Network Configuration File: E: \ oracle \ product \ 10.2.0 \ db_1 \ network \ admin \ listener. ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = ljb)

(ORACLE_HOME = E: \ oracle \ product \ 10.2.0 \ db_1)

)

(SID_DESC =

(SID_NAME = iso8859)

(ORACLE_HOME = E: \ oracle \ product \ 10.2.0 \ db_1)

)

(SID_DESC =

(SID_NAME = orcl)

(ORACLE_HOME = E: \ oracle \ product \ 10.2.0 \ db_1)

)

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = E: \ oracle \ product \ 10.2.0 \ db_1)

(ENVS = "EXTPROC_DLLS = ANY, ODC_SECURE_SERVICE_API_CONFIG_FILE = C: \ DBCoffer \ DBCSecureServiceAPI \ secureServiceAPI. conf, NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1 ")

(PROGRAM = extproc)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC) (KEY = extproc ))

(ADDRESS = (PROTOCOL = TCP) (HOST = Jiabo) (PORT = 1521 ))

)

)

Table 2

Pay attention to the red part. If you use Net Manager to modify the relevant configuration, use this file as a reference. Otherwise, DBCoffer may not work normally, but in tnames. the configuration of ora is relatively simple. Just add the following content: