迁移表空间databump
使用databump导入导出,两个库用户必须一致,否则另一个库导入的时候会报错。所以两个库都是用helei用户。
给两个数据库的用户分别授予dba权限,这里只是实验更清晰而已。
SQL> create user helei identified by MANAGER; User created. SQL> grant connect,resource to helei; Grant succeeded. SQL> grant dba to helei; Grantsucceeded.
我们先查看表空间,我们要把主机HE3中的heleitbs表空间空间迁移到HE4的数据库当中。
SQL>select TABLESPACE_NAME,STATUS from dba_tablespaces; TABLESPACE_NAME STATUS --------------------------------------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE 6 rowsselected.
我们在HE3上的heleitbs表空间中创建一张表,所有的操作都用到helei用户
SQL>createtablespace heleitbs datafile '/u01/app/oracle/oradata/orcl/heleitbs1.dbf' size10m; Tablespacecreated. SQL> createtable TTT (a int,b varchar2(20)); Tablecreated. SQL> alter table TTT add constraint TTT_PRIKEYprimary key (a); insert into ttt values(1,'helei1'); insert into ttt values(2,'helei2'); SQL> commit; Commit complete.
2.先在两个虚拟机上创建目录,并且授权
[oracle@HE3~]$ mkdir -p /home/oracle/dumpfile [oracle@HE3~]$ chown -R oracle. dumpfile [oracle@HE3~]$ chmod -R 755 dumpfile
在HE3数据库中给文件夹做授权
SQL>createdirectory dumpfile as '/home/oracle/dumpfile'; Directorycreated. SQL> grant all on directory dumpfile to public; Grantsucceeded.
在HE4数据库中给文件夹做授权
SQL>createdirectory dumpfile as '/home/oracle/dumpfile'; Directorycreated. SQL> grant all on directory dumpfile to public; Grantsucceeded.
3.在HE3库中,需要用sys登录,检查一下表空间里面的表是否可以迁移。
查询代码:
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('需要迁移的表空间名字', TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
SQL> conn / as sysdba Connected. SQL>show user USER is"SYS" SQL>EXECUTEDBMS_TTS.TRANSPORT_SET_CHECK('heleitbs',true); PL/SQLprocedure successfully completed. SQL> select * from transport_set_violations; no rowsselected
4.在HE3库中,把heleitbs表空间变为只读。
SQL> conn / as sysdba Connected. SQL> alter tablespace heleitbs read only; Tablespacealtered. SQL>select TABLESPACE_NAME,STATUS from dba_tablespaces; TABLESPACE_NAME STATUS --------------------------------------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE HELEITBS READ ONLY 7 rowsselected.
5. 使用databump导入导出把helei用户的heleitbs表空间导出到系统中的文件夹中。
[oracle@HE3dumpfile]$ expdp helei/MANAGERdumpfile=helei.dmp directory=dumpfile transport_tablespaces=heleitbs Export: Release11.2.0.1.0 - Production on Sun Dec 13 23:59:37 2015 Copyright (c) 1982,2009, Oracle and/or its affiliates. Allrights reserved. Connected to: OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With thePartitioning, OLAP, Data Mining and Real Application Testing options Starting"HELEI"."SYS_EXPORT_TRANSPORTABLE_01": helei/******** dumpfile=helei.dmpdirectory=dumpfile transport_tablespaces=heleitbs Processing objecttype TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing objecttype TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table"HELEI"."SYS_EXPORT_TRANSPORTABLE_01" successfullyloaded/unloaded ****************************************************************************** Dump file set forHELEI.SYS_EXPORT_TRANSPORTABLE_01 is: /home/oracle/dumpfile/helei.dmp ****************************************************************************** Datafiles requiredfor transportable tablespace HELEITBS: /u01/app/oracle/oradata/orcl/heleitbs1.dbf Job"HELEI"."SYS_EXPORT_TRANSPORTABLE_01" successfullycompleted at 23:59:56
6.用scp把HE3的dumpfile文件夹里面的helei.dmp拷贝到HE4的dumpfile文件夹中。
[oracle@HE3dumpfile]$ scp -rp helei.dmpHE4:/home/oracle/dumpfile/ oracle@he4'spassword: helei.dmp 100% 80KB 80.0KB/s
在HE4虚拟机里查看一下dumpfile文件夹有没有helei.dmp
[oracle@HE4~]$ cd dumpfile/
[oracle@HE4dumpfile]$ ls
helei.dmp
7.分别查看HE3库和HE4库的数据文件存在的位置。
HE3库
SQL>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/heleitbs1.dbf
6 rowsselected.
HE4库
SQL>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
8.把HE3库的数据文件heleitbs1.dbf拷贝到HE4库里的数据文件当中。
[oracle@HE3~]$ cd /u01/app/oracle/oradata/orcl/ [oracle@HE3orcl]$ scp heleitbs1.dbfHE4:/u01/app/oracle/oradata/orcl/ oracle@he4'spassword: heleitbs1.dbf 100% 10MB 10.0MB/s 00:00
然后查看一下HE4有没有heleitbs1.dbf文件
[oracle@HE4dumpfile]$ cd /u01/app/oracle/oradata/orcl/
[oracle@HE4orcl]$ ls
control01.ctl example01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl heleitbs1.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
9.这时用使用databump导入导出把HE4的dumpfile文件家里面的helei.dmp导入到自己的数据库中
[oracle@HE4~]$ impdp helei/MANAGERdumpfile=helei.dmp directory=dumpfile transport_datafiles='/u01/app/oracle/oradata/orcl/heleitbs1.dbf' Import:Release 11.2.0.1.0 - Production on Mon Dec 14 00:36:33 2015 Copyright(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction With thePartitioning, OLAP, Data Mining and Real Application Testing options Mastertable "HELEI"."SYS_IMPORT_TRANSPORTABLE_01" successfullyloaded/unloaded Starting"HELEI"."SYS_IMPORT_TRANSPORTABLE_01": helei/******** dumpfile=helei.dmpdirectory=dumpfiletransport_datafiles=/u01/app/oracle/oradata/orcl/heleitbs1.dbf Processingobject type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processingobject type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job"HELEI"."SYS_IMPORT_TRANSPORTABLE_01" successfullycompleted at 00:36:49
10.把两个库中的heleitbs表空间都设置为读写模式。
两个库命令是一致的使用dba用户和weixiaobin用户都可以
SQL> alter tablespace heleitbs read write; Tablespacealtered.
11.验证,看看HE4虚拟数据中是不是HELEITBS表空间。看看表空间里有没有TTT的表
SQL> select TABLE_NAME,TABLESPACE_NAME fromdba_tables where TABLESPACE_NAME='HELEITBS'; TABLE_NAME TABLESPACE_NAME ------------------------------------------------------------ TTT HELEITBS