Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use DataPump to migrate Oracle database, the article is very detailed, has a certain reference value, interested friends must read it!

I have previously tested using RMAN to migrate Oracle databases (Oracle11.2.0.1-Oracle11.2.0.4,Windows to Linux). Here is a brief description of how to migrate using the data pump.

Relatively speaking, it is indeed relatively simple, export and import, but it can also be divided into two ways, one is to export according to business users, and the other is not only one more full=y, but also some different ones. OK, let's take a look.

Environment introduction, source-side Windows2008 x64+Oracle11.2.0.1

Destination side: Redhat6.7 x64+Oracle11.2.0.4

First of all, the environment preparation, that is, the target environment preparation, the installation of Oracle database under Linux and the latest patches, which are ignored here.

Whether it is full database export and import or business users, you need to create tablespaces in advance, especially for those with a large amount of data, which also saves time. So how do we get the ddl for the source database to create the tablespace?

The following statement lists all tablespace creation statements. Please note the system-related tablespace size, such as SYSTEM and USERS. It is possible that the tablespace is large and there will be multiple data files.

Set long 1000000

Set pages 9999

Select dbms_metadata.get_ddl ('TABLESPACE',tablespace_name) from dba_tablespaces

Query the DDL statement of a table space

Select dbms_metadata.get_ddl ('TABLESPACE','MYDB') from dual

The source side can perform a full library export operation. First we need to create a directory.

Create directory exp as'd:\ datapump';-Export using sys users without authorization

Expdp'/ as sysdba' directory=my_exp dumpfile=myexpdb%U.dmp logfile=myexpdb.log parallel=2 EXCLUDE=STATISTICS parfile=mypar.par

# mypar.par m Note system users and non-OPEN users can be excluded from full library export without creating users

FULL=Y

EXCLUDE=SCHEMA: "in (select username from dba_users where account_status'OPEN' or username in ('SYS','SYSTEM'))"

The following is simple, and the target side can import

Note: full library export, import will import role, DIRECTORY, TABLESPACE, CONTEXT, etc. We can choose to exclude

Impdp\'/ as sysdba\ 'directory=exp dumpfile=myexpdb%U.dmp logfile=myimpdb01.log parallel=2 exclude=DIRECTORY exclude=role exclude=CONTEXT exclude=TABLESPACE

Import complete, collect statistics

Exec dbms_stats.GATHER_DATABASE_STATS (estimate_percent= > null)

So if only business users are exported, we need to create users and give them relevant permissions.

Generate the statement that creates the user (Oracle11g) with the following statement

Generate the grant statement with the following statement

The export statement looks like this:

Expdp'/ as sysdba' directory=my_exp dumpfile=myexpdb%U.dmp logfile=myexpdb.log parallel=2 EXCLUDE=STATISTICS schema=mydb,test

Import statement:

Impdp\'/ as sysdba\ 'directory=exp dumpfile=myexpdb%U.dmp logfile=myimpdb01.log parallel=2

Collect database statistics

Of course, this requires downtime, if the downtime requirements are particularly strict, it is recommended to use OGG-related synchronization software, rman incremental backup can also be tried.

The above is all the contents of the article "how to use DataPump to migrate Oracle databases". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.