Oracle 11g r2 migrates from CentOS to Windows 10 using Data dump

****** Expdp Export, Impdp Import ****
  • 1
Source system:
Operating System: CentOS
 Oracle database version: Oracle 11g Enterprise Edition Release 11.2.0.1.0
SID: orcl
 Port: 1521
  • 1
  • 2
  • 3
  • 4
  • 5
Target system:
Operating System: Windows 10
 Oracle database version: Oracle 11g Enterprise Edition Release 11.2.0.1.0
SID: orcl
 Port: 1521
  • 1
  • 2
  • 3
  • 4
  • 5

Accounts are established for both source and target systems

create user username identified by password;
grant connect,resoure to username;
  • 1
  • 2

Give expdp and impdp command permissions:

grant exp_full_database,imp_full_database to username;
  • 1
SQL> create user migrate identified by migrate;
User created
  • 1
  • 2
SQL> grant connect,resource,dba to migrate;
Grant succeeded
  • 1
  • 2
SQL> grant exp_full_database,imp_full_database to migrate;
Grant succeeded
  • 1
  • 2
SQL> select * from dba_directories;
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR          /oracle/app/product/11.2.0/dbhome_1/ccr/state
SYS                            DATA_PUMP_DIR                  /oracle/app/admin/orcl/dpdump/
SYS                            XMLDIR                         /ade/b/2125410156/oracle/rdbms/xml
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

Step 1: Create directories on the source system

Create directory DPDMP as'/oracle/app/admin/orcl/dpdmp/'; -- The directory must be an existing directory (which should be created by an oracle user), otherwise an error will occur.
  • 1
SQL> select * from dba_directories;
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR          /oracle/app/product/11.2.0/dbhome_1/ccr/state
SYS                            DATA_PUMP_DIR                  /oracle/app/admin/orcl/dpdump/
SYS                            DPDMP                          /oracle/app/admin/orcl/dpdmp/
SYS                            XMLDIR                         /ade/b/2125410156/oracle/rdbms/xml
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

Step 2: Give the account permission to the directory

grant read,write on directory DPDMP to username;
  • 1

Step 3: Expdp at the source system end (based on a user)

expdp username/password@orcl directory= DPDMP dumpfile=XX_schema%U.dmp logfile=XX_schema.log schemas=XX_schema 
Or export the entire library:
expdp username/password@orcl directory= DPDMP dumpfile=all%U.dmp logfile=XX_schema.log parallel=10 full=y

Step 4: Copy the directory/XX_schema%U.dmp file of the source system to the target system

This step is omitted

Step 5: Create directories on the target system

Create directory DPDMP as'E: oracle app Administrator admin orcl dpdmp'; -- The directory must be an existing directory, otherwise it will report an error
  • 1

Step 6: Give permission

grant read,write on directory impdp to username;
  • 1

Step 7: impdp at the target system end

impdp username/password@orcl directory= DPDMP dumpfile=XX_schema%U.dmp logfile=XX_schema.log schemas=XX_schema
//Or import the entire library:
impdp username/password@orcl directory= DPDMP dumpfile=all%U.dmp logfile=XX_schema.log parallel=10 full=y
impdp migrate/migrate@orcl directory=dpdmp dumpfile=migrate_full.dmp logfile=migrate_imp_full.log full=y parfile=e:\payroll.par parallel=10 
payroll.par put to directory impdp Catalog or place E Packing directory:
payroll.par Document content
remap_datafile="'/oracle/app/oradata/orcl/XXX.dbf':'E:\ORACLE\APP\ADMINISTRATOR\ORADATA\ORCL\XXX.DBF'"
remap_datafile="'/oracle/app/oradata/orcl/XXX.dbf':'E:\ORACLE\APP\ADMINISTRATOR\ORADATA\ORCL\XXX.DBF'"
remap_datafile="'/oracle/app/oradata/orcl/XXX.dbf':'E:\ORACLE\APP\ADMINISTRATOR\ORADATA\ORCL\XXX.DBF'"
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

> The following are possible uses in migration

  • If you need to import and export from linux to window s, it's better to create tablespaces and data files first.
    Table space DDL:
select ts.tablespace_name,to_char(dbms_metadata.get_DDl('TABLESPACE',ts.tablespace_name))from dba_tablespaces ts;
  • 1
  • User DDL:
select username,default_tablespace,temporary_tablespace,profile,dbms_metadata.get_DDl('USER',username) from dba_users where 
account_status='OPEN' ;
  • 1
  • 2
  • Number of records:
 select table_name, count_rows(table_name) nrows from user_tables ;
  • 1
  • View the currently imported job:
 select * from dba_datapump_jobs;
  • 1
  • If an exception exits in the impdp process, import from the interrupt and execute as follows:

    impdp migrate/migrate@orcl attach=JOB_NAME (JOB_NAME can be queried by dba_datapump_jobs)
    For example: > impdp mig_test/mig_test attach = SYS_IMPORT_TABLE_02

    Import status/continue_client returns record mode/stop_job YES/.
    Common commands in interactive mode:
    CONTINUE_CLIENT returns to record mode. If it is idle, the job will be restarted.  
    START_JOB starts restoring the current job.  
    The frequency (in seconds) job status to be monitored by STATUS when the default value (0) will display the new state when available.  
    STATUS=[interval] 
    STOP_JOB sequentially closes the executing job and exits the client.  
    STOP_JOB=IMMEDIATE will immediately close the data pump operation.

  • If the whole library is imported, the card will be suspended there.

    You can check diag rdbms orcl orcl orcl trace alert_orcl. log to see the log. When the current problem is resolved, impdp will continue.
    If ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
    You need to increase db_recovery_file_dest_size:

SQL> alter system set db_recovery_file_dest_size=50G scope=spfile(Need to restart the database to take effect)
 SQL> shutdown immediate;
 SQL> startup;
 SQL> show parameter db_recovery_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\oracle\app\Administrator\flash_recovery_area
db_recovery_file_dest_size           big integer 50G
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8


Tags: Oracle SQL Database xml

Posted on Tue, 25 Dec 2018 20:42:06 -0500 by blackhawk08