Considerations for Oracle expdp/impdp data migration

 

 

For details about how to use the expdp/impdp command, refer:

Oracle expdp/impdp example

Http://blog.csdn.net/tianlesoftware/article/details/6260138

 

Comparison between exp/IMP and expdp/impdp and some optimization items in use

Http://blog.csdn.net/tianlesoftware/article/details/6093973

 

Example of Oracle client using expdp/impdp

Http://blog.csdn.net/tianlesoftware/article/details/7075188

 

 

Note:

 

1. User-based migration

You only need to create the relevant tablespace. You do not need to create a user. During the import process, the data pump automatically creates relevant users and grants permissions. Even if we do not know the user password, the data pump can import the original password. This can simplify our work.

 

For example:

Expdp directory = backupdumpfile = Dave. dmp logfile = Dave. Log schemas = user1, user2, user3;

Impdp directory = backupdumpfile = Dave. dmp logfile = Dave. Log schemas = user1, user2, user3;

 

 

2. When using a data pump, if its parallel feature is used, the number of parallel lines must be the same as the number of corresponding dump files.

For example:

Expdp full = ydirectory = backup dumpfile = Dave _ % u. dmp parallel = 4

Expdp creates four files for parallel: dave_01.dmp, Dave _ 02.dmp, Dave _ 03.dmp, and Dave _ 04.dmp. Each process has one file. In this way, the size of each file varies with the process. A file may be large but small. To solve this problem, set the filesize parameter. To specify the maximum value of each file. In this way, when a file reaches the maximum value, a new file will be created.

 

For example:

Expdp full = ydirectory = dump dumpfile = Dave _ % u. dmp parallel = 4 filesize = 1g

 

The exported dump file is related to paralle, so the import is also related. Paralle must be smaller than the number of dump files. If paralle is greater than the number of dump files, the performance will not be improved because the process that exceeds the limit cannot obtain the files.

 

 

3. During migration, pay attention to two types of objects

Generally, pay attention to the following information before migration:

(1) Total number of objects to be migrated and detailed information

SQL> select count (*) from all_objectswhere owner in ('user1', 'user2 ')

 

SQL> select object_type, count (1) as "num" from all_objects where owner in ('user1', 'user2') group byobject_type;

 

Object_type num

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

Sequence 85

Procedure 74

Package 6

Package body 6

Trigger 5

Table 219

Index 229

Synonym 1

View 4

Function 3

Type 2

 

11 rows selected.

 

 

(2) Invalid object information

SQL> select count (*) from all_objectswhere owner in ('user1', 'user2') and status = 'invalid ';

SQL> select owner, object_name fromall_objects where owner in ('user1', 'user2') and status = 'invalid ';

 

After the migration is complete, compare the number of objects before and after migration and the number of invalid objects. The two objects that need attention here are dblink and job. In my tests, these two types of objects are basically not automatically imported. You need to manually re-create these objects after the import is complete.

 

View dblink information:

SQL> select owner, object_name, object_type from dba_objects where object_type = 'databaselink ';

 

View job information:

SQL> select count (*) from dba_jobs where schema_user in ('user1', 'user2 ');

 

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Email: tianlesoftware@gmail.com

Skype: tianlesoftware

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Group dba6: 158654907 dba7: 172855474