Use IMPDP to achieve rapid replication of data between different users and different instances

Disclaimer: This is a reference to Secooler's "Data Migration replication--network_link parameters between different users of the same database instance" (http://space.itpub.net/519536/viewspace-631571), thank you.

In actual development and operation, we often encounter the need to transfer the structure and content of a schema, a tablespace, or some data tables to another data schema, tablespace, or another instance.

There are many ways to achieve this need. More commonly used is the use of the EXP/EXPDP tool to export the data from the specified data source into a. dmp format file. Then point to the target data source and import using the IMP/IMPDP tool. This paper introduces a way to use the Network_link parameters of the IMPDP tool to realize the flexible and rapid replication of data between the same instance and the different instances.

The Exp/imp tool is a data export import tool introduced early in Oracle (Oracle Release 5) and has a strong vitality because of its simple and practical advantages. However, with the advent of the mass data era and the introduction of new Oracle features, the Exp/imp tool's adaptability is gradually appearing. As a result, ORACLE10G has launched the data Pump EXPDP/IMPDP component as a new generation of data management components.

In general, the data dump component of the database pump has the following characteristics:

• Data dump has better performance and manageability than the previous generation of exp/imp tools;

Üdata dump is a server-side tool, not an Oracle client;

u have the control function of breaking/restarting the import export job;

U Support Network network working mode;

U can reboot after job interruption;

• More flexible operating object range;

U provides a variety of ways to invoke the API organization;

To get back to the tasks that this article is going to accomplish, first let's prepare and view the environment. The current system is Oracle 11GR2 environment.

Create an experimental tablespace

sql> Create tablespace Test datafile '/u01/oradata/wilson/datafile/test.dbf ' size 100m extent management Local

2 uniform. Size 1m;

Tablespace created

Sql> select * from V$tablespace;

ts# NAME Included_in_database_backup

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

0 SYSTEM YES

1 Sysaux YES

2 UNDOTBS1 YES

4 USERS YES

TEST YES

(Space reason, have omitted ...) ）

Ten rows selected

Create Object Test.

Sql> create user test identified by test default tablespace test temporary tablespace TEMP;

User created

Sql> Grant connect to test;

Grant succeeded

Sql> grant resource to test;

Grant succeeded

Task Detail: Copy all objects under schema Scott including data to the test user schema. At the same time, the table space corresponding to these objects is transformed from the original users to the table space test.

To achieve this goal, you need to follow two steps:

1, in order to use the IMPDP tool Network_link parameters, to establish a database link object to the data source;

2, the use of IMPDP tools, such as the command line operation mode;

Below, we proceed according to the steps:

Build Database Link

For an Introduction to Oracle database link, refer to the appropriate description in the Distributed Database Component--database link) (http://space.itpub.net/17203031/viewspace-687469). One emphasis here is that when you set up Database link, the named connection name after the Using clause is required to be established in the Tnsname.ora file on the database server. Rather than a name created on the user's client machine.

First, build the local service name and rewrite the tnsname.ora manually.

[oracle@oracle11g admin]$ Cat Tnsnames.ora

# Tnsnames.ora Network Configuration File:/u01/oracle/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

WILSON =

(DESCRIPTION =

(address = (PROTOCOL = TCP) (HOST = oracle11g) (PORT = 1521))

(Connect_data =

(SERVER = dedicated)

(service_name = Wilson)

)

)

This example is directed from Wilson to Wilson, so it's OK to point to itself. If you are importing to a different instance database, you need to create the Databaselink on the target instance. The local service naming here refers to the source database naming.

Here's how to set up Database Link.

Database connection DB Link creation statement

Create Public database link to_self

Connect to system identified by system

Using ' Wilson ';

Meaning: Create a database link to connect to the local naming service named "Wilson" on the databases instance, use the system user logon, the password is system.

Test the situation with database link.

Connect using to_self Name

Sql> Select COUNT (*) from dba_objects@to_self;

COUNT (*)

----------

72470

Make command line Import

Before the import operation, let's look at the data object under Scott.

Sql> Select table_name, tablespace_name from Dba_tables where wner= ' SCOTT ';

TABLE_NAME Tablespace_name

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

DEPT USERS

EMP USERS

Salgrade USERS

T2 USERS

T1 USERS

BONUS USERS

6 Rows selected

Sql> Select Index_name, tablespace_name from dba_indexes where wner= ' SCOTT ';

Index_name Tablespace_name

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

Pk_dept USERS

Pk_emp USERS

At this point, we have two problems to solve, first how to convert the Scott user's datasheet to test user, and the other is how to transform the users table space into test table space.

Here we introduce the two parameters of the IMPDP tool: Remap_schema and Remap_tablespace. These two parameters implement a transformation mapping for a particular schema, a particular tablespace, respectively. With these two parameters, you can define the transformation rules.

The following starts with the import:

[oracle@oracle11g admin]$ impdp system/system network_link=to_self Schemas=scott remap_schema=scott:test remap_ Tablespace=users:test

Import:release 11.2.0.1.0-production on Thu 5 07:51:00 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Udi-28002:operation generated ORACLE error 28002

ora-28002:the password would expire within 7 days

Connected to:oracle Database 11g Enterprise Edition release 11.2.0.1.0-production

With the partitioning, OLAP, Data Mining and real application testing options

Starting "SYSTEM". " Sys_import_schema_01 ": system/******** network_link=to_self Schemas=scott remap_schema=scott:test remap_tablespace= Users:test

Estimate in progress using BLOCKS method ...

Processing Object Type Schema_export/table/table_data

Total estimation using BLOCKS method:192 KB

Processing Object Type Schema_export/user

Ora-31684:object type USER: "TEST" already exists

Processing Object Type Schema_export/system_grant

Processing Object Type Schema_export/role_grant

Processing Object Type Schema_export/default_role

Processing Object Type Schema_export/pre_schema/procact_schema

Processing Object Type Schema_export/table/table

. . Imported "TEST". " DEPT "4 rows

. . Imported "TEST". " EMP "Rows

. . Imported "TEST". " Salgrade "5 rows

. . Imported "TEST". " BONUS "0 rows

. . Imported "TEST". " T1 "0 rows

. . Imported "TEST". " T2 "0 rows