ORACLE migration from one instance to another instance combat record

ORACLE across Schema The Data Migration

The test environment server is not enough, and colleagues need a new Oracle environment, the production environment to pull the data to build an internal system to run; this instance is not frequently used and requires a lot of resources, so prepare to reopen a new instance on the original, low-load Oracle server, This can save database server resources.

1,DBCABuild Library

Using DBCA to build a second Oracle instance on Linux, build the process reference: http://blog.csdn.net/mchdba/article/details/51418063

2, exporting the database from the wire

Because the online environment is different from the new instance, if you use Rman Backup, you need to set a lot of parameters when recovering, not too convenient, so prepare to use EXPDP to export data, and then use IMPDP for data import work.

The main line has 2 schemas, so export 2 schema data can be, one is PowerDesk, one is Plas.

(2.1)Exporting Data

expdppowerdesk/pl_eahys0418 DIRECTORY=DIR_DP Dumpfile=powerdesk_20160829.dmpschemas=powerdesk LOGFILE=zxg.log;

expdppowerdesk/pl_ethys0418 DIRECTORY=DIR_DP dumpfile=plas_20160829.dmp Schemas=plas LOGFILE=zxg.log;

(2.2)Transferring Data

[Email protected]_earth_dbm1_3_111 dir_dp]$ scpplas_20160829.dmp powerdesk_20160829.dmp 192.168.121.61:/home/ oracle/

The authenticity of host ' 192.168.121.61 (192.168.121.61) ' can ' t be established.

RSA key fingerprint is0a:c7:1c:89:1d:9d:a2:e1:6c:36:68:d9:18:b4:ab:cc.

Is you sure want to continueconnecting (yes/no)? Yes

warning:permanently added ' 192.168.121.61 ' (RSA) to the list of known hosts.

[email protected] ' s password:

plas_20160829.dmp                                                                                                                                                                                                                                      100% 1067mb   5.4mb/s   03:16   

powerdesk_20160829.dmp                                                                                                                                                                                                                                100%   13GB   4.9MB/s   44:24   

[[Email protected]_earth_dbm1_3_111 dir_dp]$

3, prepare the account on the new instance

When using IMPDP, you can create the corresponding schema account, so that when the import, the schema to the schema of the data migration.

(3.1)Create a new table space

Createtablespace PowerDesk

Logging

DataFile '/home/oradata/ysdb3/powerdesk01. DBF '

size50m

Autoextendon

next50m

Extentmanagement Local;

Createtablespace Plas

Logging

DataFile '/home/oradata/ysdb3/plas01. DBF '

size50m

Autoextendon

next50m

Extentmanagement Local;

(3.2)Create a new user

CREATEUSER PowerDesk profile ' default ' identified by ' powerdes0418 ' default tablespace ' PowerDesk ' account UNLOCK;

CREATEUSER Plas Profile ' default ' identified by ' plas0418 ' default tablespace ' Plas ' account UNLOCK;

(3.2)Empowering new users

Grantconnect,resource to PowerDesk;

Grantdba to PowerDesk;

Grantcreate session to Plas;

Grantconnect,resource to Plas;

4, import data on a new instance(4.1)Prepare the Catalog

         # Create an import directory first

CreateDirectory dir_dp1 as '/home/oracle/';

         # Give directory Permissions

Grantcreate,write,read to DIR_DP1;

         # give the user permission to manipulate the directory

Grantread,write on directory dir_dp1 to PowerDesk;

Grantread,write on directory Dir_dp1 to Plas;

(4.2)UseIMPDPImport Data

Time IMPDP SYSTEM/YUEWORLDPDDIRECTORY=DIR_DP1 Dumpfile=powerdesk_20160829.dmpremap_schema=powerdesk:powerdesk Table_exists_action=replace parallel=8

Time IMPDP SYSTEM/YUEWORLDPD directory=dir_dp1dumpfile=plas_20160829.dmp Remap_schema=plas:plas TABLE_EXISTS_ACTION =replaceparallel=8

5,SCHEMA1to theSchema2the Migration

Oracle exports from one user expdp and then IMPDP to another user, you can use Remap_schema=user1:user2 to:

If the user you want to import already exists :

1. Export user EXPDP User1/pass1 Directory=dumpdir dumpfile=user1.dmp

2. Import user IMPDP User2/pass2 directory=dumpdir dumpfile=user1.dmpremap_schema=user1:user2 EXCLUDE=USER

If the user you want to import does not exist :

1. Export user EXPDP User1/pass1 Directory=dumpdir dumpfile=user1.dmp

2. Import user IMPDP System/passsystem Directory=dumpdir dumpfile=user1.dmpremap_schema=user1:user2

3.user2 will be automatically established, with the same permissions and use of the table space as User1, but at this time with User2 can not log in, you must modify the User2 password

ORACLE migration from one instance to another instance combat record