Shulou(Shulou.com)06/01 Report--

The company has an old Oracle 9i test database. Because the computer room is relocated, data needs to be migrated. The new database is Oracle 11g. It is a relatively simple requirement, but some problems are encountered in the process. It seems trivial and worth summarizing.

Since the source library is 9i, you can only use imp/exp, not data pumps.

Question 1: Import default tablespaces for target library users

Source library due to non-standard use, the default object storage is the database default table space USERS, since it is migration, the new library should try to standardize some. But the question arises, impdp/expdp can use remap_tablespace to map old and new tablespaces, what should exp/imp do?

There is a saying on the Internet that first withdraw the unlimited tablespace permission of the user user, then set the user default tablespace to bank_tbs, and then set the user quota for the system and users tablespaces to 0. The intention is that when imp imports, if it finds that the users tablespace has no permission, it will automatically find the user's default tablespace bank_tbs.

revoke unlimited tablespace from user;

alter user user quota unlimited on bank_tbs;

alter user user quota 0 on system;

alter user user quota 0 on users;

However, from my actual measurement, this is not the case. You can use the show option of the imp command to see the contents of the dmp file. The create table clause will be followed by tablespace users, that is, the name of the table space used by the table will be specified. Since the user quota in the users table space is 0, it will report quota-related errors and will not find the user default bank_tbs table space.

Let's just go through this again,

1. The dump file has tablespaces specified for tablespace users.

2. The target inventory is in the users table space, but the user quota in the users table space is 0, and its default table space is bank_tbs.

3. Imp performs import and reports users table space quota error.

The function of user default table space is that if the create table statement does not specify the table space clause, it will store this table space by default. In this case, since this is a set of test libraries, first change the users table space name.

alter tablespace users rename to users_k;

After imp import, it can be stored in the default bank_tbs of user. Following the train of thought, you can change the default table space users of the database. As long as there is no users table space, the create table statement in dmp cannot insert the corresponding table space according to the tableapce clause, but find the user default table space.

In addition, you can import the users table space initially, and then concatenate SQL statements to move objects to other table spaces, which of course requires twice the space. You can also change the table space corresponding to the tablespace clause in the dmp file, but only for small files.

Here are some facts worth noting,

1. The unlimited tablespace permission is granted to the user and the resource role is automatically added. However, from a security point of view, after creating the user and granting the resource role, the unlimited tablespace permission should be withdrawn. The reason is that with this permission, the user can create objects in any table space, which may maliciously occupy the system table space and affect the normal operation of the database.

2. Before Oracle 9i, the database default user table space is SYSTEM, which is extremely unreasonable, because SYSTEM stores the database important underlying data dictionary information, if unlimited storage of user data, it is very likely to affect the operation of the database. From 9i onwards, the default table space becomes USERS, which is created by default when the database is built.

You can query the current system default table space using the following statement,

select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

Use the following statement to change the default user and temporary tablespaces for the current database,

alter database default [temporary] tablespace tablespace_name;

Question 2: Database Character Set

In order to ensure that data export import, will not appear garbled characters, character sets should be consistent as far as possible, you can use the following statement to retrieve the current database used character sets,

select userenv('language') from dual;

For example, the return result is AMERICAN_AMERICA.ZHS16GBK.

To retrieve the current operating system character set, use

echo $NLS_LANG

For example, the return result is AMERICAN_AMERICA.AL32UTF8.

To update the operating system character set, use

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

Question 3: Some errors in the import process

Error 1:

Export file created by EXPORT:V09.02.00 via conventional path

IMP-00013: only a DBA can import a file exported by another DBA

IMP-00000: Import terminated unsuccessfully

The error message indicates that only DBA users can import files exported by another DBA. This means that in this dmp file, the exported user has the DBA role, so the imported user must have the DBA role.

Workaround 1: Use non-DBA users, re-exp export, and import with non-DBA users imp.

Workaround 2: Use the DBA user to perform the imp import operation.

In contrast, the production system generally chooses Scheme 1. After all, the owner of general business data will not be a user in the DBA role. If Scheme 2 is adopted, the target user is required to have the DBA role. If there is any export import requirement in the future, the DBA role will still be required.

Error 2:

Export file created by EXPORT:V09.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments

IMP-00000: Import terminated unsuccessfully

Imp can be executed with full=y, or with fromuser and touser parameters, for example,

imp user/user file=... log=... fromuser=user touser=user

Clarify user names for export and import.

Problem 4: Create View Error

Import log shows that an error was reported when creating the view,

ORA-01031: insufficient privileges

The reason is that the user is granted the resource and connect regular roles, and the permission to create views is not automatically granted. For details, please refer to (http://blog.csdn.net/bisal/article/details/31735185). In this case, you can grant

SQL> grant createany view to user;

Grant succeeded.

Import again, that is, it can be completed normally.

For testing data migration, there is actually another point, that is, does all data need to be migrated? Because there are often some information in the test library that is only temporarily used, such as table objects, if you filter the data you really need before executing, and then start exporting and importing, you may only need to migrate a small part of the data, and you can ignore the garbage data directly. This is what people often say about optimization, that is, do nothing.

If you think this article is helpful to you, welcome to pay attention to Weixin Official Accounts: bisal's personal grocery store. Your support is my greatest encouragement! Learning together and progressing together:)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.