|
|
本地和远端都是linux 32位 ，数据库版本 目标机是11.2.0.3 本地是 11.2.0.1
create tablespace tbs_moving_data datafile '+DATA' size 10M
create table hr.test1 (a number)
tablespace tbs_moving_data
select * from hr.test1 for update ----插入数据
alter tablespace tbs_moving_data read only
-----备份数据文件------
[oracle@station4 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 12 09:26:07 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1359978017)
RMAN> backup as copy tablespace tbs_moving_data format '/home/oracle/tbs_moving_data.dbf';
Starting backup at 12-JUL-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=138 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=12 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=73 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=139 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=201 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=7 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=76 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DATA/orcl/datafile/tbs_moving_data.270.949137839
output file name=/home/oracle/tbs_moving_data.dbf tag=TAG20170712T092626 RECID=11 STAMP=949137989
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 12-JUL-17
RMAN>
导出元数据
[oracle@station4 ~]$ expdp system/oracle_4U directory=dir1 dumpfile=tbs_moving_data.dmp transport_tablespace=tbs_moving_data
Export: Release 11.2.0.1.0 - Production on Wed Jul 12 09:27:43 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, Replaced with: "transport_tablespaces="
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dir1 dumpfile=tbs_moving_data.dmp reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
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/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE" 6.328 KB 2 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES" 6.882 KB 28 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD" 5.835 KB 19 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS" 5.484 KB 3 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES" 6.289 KB 2 rows
. . exported "SYSTEM"."DEF$_AQCALL" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_AQERROR" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_CALLDEST" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_DEFAULTDEST" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_DESTINATION" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_ERROR" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_LOB" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_ORIGIN" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_PROPAGATOR" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS" 0 KB 0 rows
. . exported "SYSTEM"."MVIEW$_ADV_INDEX" 0 KB 0 rows
. . exported "SYSTEM"."MVIEW$_ADV_PARTITION" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_CONFLICT" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_DDL" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_EXCEPTIONS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_EXTENSION" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_FLAVORS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_GENERATED" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCAT" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCATLOG" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCOLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPOBJECT" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPPROP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPSCHEMA" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITES_NEW" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/tbs_moving_data.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:28:02
[oracle@station4 ~]$
拷到目标机
[oracle@station4 ~]$ scp tbs_moving_data.dbf tbs_moving_data.dmp root@192.168.0.90:/home/oracle/mydir
root@192.168.0.90's password:
tbs_moving_data.dbf 100% 10MB 10.0MB/s 00:00
tbs_moving_data.dmp 100% 2276KB 2.2MB/s 00:00
在目标机改变用户归属
[root@station90 mydir]# chown -R oracle install *
检查目标机是否存在hr用户
[oracle@station90 mydir]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 10 08:19:54 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
aConnected.
SQL> select * from hr.employees where employee_id=100;
SP2-0734: unknown command beginning "aselect * ..." - rest of line ignored.
SQL> select * from hr.employees where employee_id=100;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
100 Steven King
SKING 515.123.4567 17-JUN-03 AD_PRES 24000
90
-------导入--------
[oracle@station90 mydir]$ impdp system/oracle_4U directory=mydir dumpfile=tbs_moving_data.dmp transport_datafiles='/home/oracle/my_dir/tbs_moving_data.dbf'
Import: Release 11.2.0.3.0 - Production on Mon Jul 10 08:22:23 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORA-39002: invalid operation
ORA-39061: import mode TRANSPORTABLE conflicts with export mode SCHEMA
[oracle@station90 mydir]$
|
|
发表于 2017-7-12 09:38:58
发表于 2017-7-12 15:55:05
楼主