环境:
OS:Centos 7
DB:12.2.0.1
源库:2节点RAC
目的库:单节点
1.源库创建表空间
create tablespace tps_test
logging datafile '+DATA' size 50m
autoextend on
next 10m maxsize 2048m
extent management local
segment space management auto;
create tablespace tps_test01
logging datafile '+DATA' size 50m
autoextend on
next 10m maxsize 2048m
extent management local
segment space management auto;
2.创建用户和表并写入数据
create user hxl identified by oracle;
alter user hxl default tablespace tps_test;
grant dba to hxl;
connect hxl/oracle
create table tb_test
(
id number not null primary key,
name1 varchar(32),
name2 varchar(32),
name3 varchar(32),
name4 varchar(32),
name5 varchar(32),
name6 varchar(32),
createtime date default sysdate,
modifytime date default sysdate
);
写入数据省略
索引创建在另外一个表空间
connect hxl/oracle
create index idx_createtime on tb_test(createtime) online tablespace tps_test01;
3.源端表空间自包含(独立性)检查
SQL> connect / as sysdba
Connected.
SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('TPS_TEST',TRUE,TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
ORA-39907: Index HXL.IDX_CREATETIME in tablespace TPS_TEST01 points to table HXL
.TB_TEST in tablespace TPS_TEST.
我们这里计划将两个表空间TPS_TEST,TPS_TEST01都进行迁移
2个表空间同时一起检查
SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('TPS_TEST,TPS_TEST01',TRUE,TRUE);
SQL> select * from transport_set_violations;
no rows selected
若不想迁移表空间TPS_TEST01,解决办法把索引迁移到TPS_TEST表空间如下:
connect hxl/oracle
SQL> alter index IDX_CREATETIME rebuild tablespace TPS_TEST;
Index altered.
SQL> set linesize 1000;
SQL>column index_name format a32;
SQL>column table_name format a32;
SQL>column tablespace_name format a32;
SQL>select index_name,table_name,tablespace_name from user_indexes;
INDEX_NAME TABLE_NAME TABLESPACE_NAME
-------------------------------- -------------------------------- --------------------------------
IDX_CREATETIME TB_TEST TPS_TEST
4.创建目录(原库和目标库,用于导出导入使用)
源库(在其中一个节点上操作):
asm创建目录
su - grid
asmcmd
ASMCMD> pwd
+data/slnngk
ASMCMD> mkdir dumpdir
ASMCMD> cd dumpdir
ASMCMD> pwd
+data/slnngk/dumpdir
su - oracle
SQL> connect / as sysdba
Connected.
SQL> create directory datapump_dir as '+data/slnngk/dumpdir';
Directory created.
授予权限给导出用户(我这里使用system账号导出)
connect / as sysdba
grant write,read on directory datapump_dir to system;
目的库:
Os创建目录
mkdir -p /u01/dumpdir
connect / as sysdba
create directory datapump_dir as '/u01/dumpdir';
grant write,read on directory datapump_dir to system; ##赋予权限给到导入账号
5.将源库设置为只读模式
SQL> connect / as sysdba
SQL> alter tablespace tps_test read only;
SQL> alter tablespace tps_test01 read only;
6.源端数据泵导出表空间元数据(2选1)
不带日志导出:
[oracle@rac01 ~]$ expdp system/oracle dumpfile=expdat.dmp directory=datapump_dir transport_tablespaces=tps_test,tps_test01 nologfile=YES
Export: Release 12.2.0.1.0 - Production on Tue Dec 12 16:36:27 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=expdat.dmp directory=datapump_dir transport_tablespaces=tps_test,tps_test01 nologfile=YES
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
+DATA/slnngk/dumpdir/expdat.dmp
******************************************************************************
Datafiles required for transportable tablespace TPS_TEST:
+DATA/SLNNGK/DATAFILE/tps_test.269.1155390091
Datafiles required for transportable tablespace TPS_TEST01:
+DATA/SLNNGK/DATAFILE/tps_test01.271.1155399745
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Tue Dec 12 16:37:36 2023 elapsed 0 00:01:03
带日志导出:
create directory logdump as '/home/oracle';##创建文件级别的目录
expdp system/oracle dumpfile=expdat.dmp directory=datapump_dir transport_tablespaces=tps_test,tps_test01 logfile=logdump:tts_export.log
7.asm导出dump文件拷贝到本地并传输到目的机器
ASMCMD> cp expdat.dmp /tmp
copying +data/slnngk/dumpdir/expdat.dmp -> /tmp/expdat.dmp
拷贝到目的机器:
su - orace
scp /tmp/expdat.dmp oracle@192.168.56.103:/u01/dumpdir/
目的机器需要对该文件修改权限(看情况需要)
su - root
cd /u01/dumpdir/
chown oracle:oinstall expdat.dmp
8.源端的数据文件拷贝到目的机器
从asm拷贝到文件系统
ASMCMD> pwd
+data/slnngk/datafile
ASMCMD> cp TPS_TEST.269.1155390091 /tmp/TPS_TEST.dbf
copying +data/slnngk/datafile/TPS_TEST.269.1155390091 -> /tmp/TPS_TEST.dbf
ASMCMD> cp TPS_TEST01.271.1155399745 /tmp/TPS_TEST01.dbf
copying +data/slnngk/datafile/TPS_TEST01.271.1155399745 -> /tmp/TPS_TEST01.dbf
scp到远程机器
su - oracle
scp /tmp/TPS_TEST.dbf oracle@192.168.56.103:/u01/dumpdir/
scp /tmp/TPS_TEST01.dbf oracle@192.168.56.103:/u01/dumpdir/
转换
我这里两边的都是相同的操作系统,不需要进行转换,需要转换的化可以参考
https://www.cnblogs.com/hxlasky/p/12334747.html
9.目标端将表空间文件拷贝到数据库目录
SQL> connect / as sysdba
SQL> set linesize 1000
SQL> column file_name format a64
SQL> column tablespace_name format a16
SQL> select file_name,tablespace_name from dba_data_files;
获取目的库的数据文件路径
su - oracle
cp /u01/dumpdir/TPS_TEST.dbf /u01/app/oracle/oradata/slnngkb/
cp /u01/dumpdir/TPS_TEST01.dbf /u01/app/oracle/oradata/slnngkb/
注意权限,文件权限必须为oracle:oinstall
10.目标库创建用户并进行导入
SQL> create user hxl01 identified by oracle; ##创建一个新用户,原来是hxl
SQL> grant connect ,resource to hxl01;
[oracle@12c slnngkb]$ impdp system/oracle dumpfile=expdat.dmp directory=datapump_dir transport_datafiles='/u01/app/oracle/oradata/slnngkb/TPS_TEST.dbf','/u01/app/oracle/oradata/slnngkb/TPS_TEST01.dbf' remap_schema=hxl:hxl01 logfile=import.log EXCLUDE=STATISTICS cluster=N
Import: Release 12.2.0.1.0 - Production on Tue Dec 12 15:10:05 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=expdat.dmp directory=datapump_dir transport_datafiles=/u01/app/oracle/oradata/slnngkb/TPS_TEST01.dbf remap_schema=hxl:hxl01 logfile=import.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Dec 12 15:10:25 2023 elapsed 0 00:00:18
11.验证数据
SQL> connect / as sysdba
Connected.
SQL> select count(1) from hxl01.tb_test;
COUNT(1)
----------
1700000
新创建的用户
SQL> set linesize 1000;
SQL>column username format a32;
SQL>column default_tablespace format a32;
SQL> column username format a32;
SQL> column default_tablespace format a32;
SQL> select username,default_tablespace from dba_users where username='HXL01';
USERNAME DEFAULT_TABLESPACE
-------------------------------- --------------------------------
HXL01 USERS
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TPS_HXL ONLINE
TPS_GOLDENGATE ONLINE
TPS_TEST READ ONLY
TPS_TEST01 READ ONLY
8 rows selected.
SQL> set linesize 1000;
SQL> column file_name format a64;
SQL> column TABLESPACE_NAME format a32;
SQL> column file_name format a64;
SQL> column TABLESPACE_NAME format a32;
SQL> select file_name,TABLESPACE_NAME from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------- --------------------------------
/u01/app/oracle/oradata/slnngkb/system.257.1076388899 SYSTEM
/u01/app/oracle/oradata/slnngkb/sysaux.258.1076388933 SYSAUX
/u01/app/oracle/oradata/slnngkb/undotbs1.259.1076388959 UNDOTBS1
/u01/app/oracle/oradata/slnngkb/users.260.1076388961 USERS
/u01/app/oracle/oradata/slnngkb/tps_hxl.269.1076470423 TPS_HXL
/u01/app/oracle/oradata/slnngkb/tps_goldengate.270.1076472453 TPS_GOLDENGATE
/u01/app/oracle/oradata/slnngkb/TPS_TEST.dbf TPS_TEST
/u01/app/oracle/oradata/slnngkb/TPS_TEST01.dbf TPS_TEST01
8 rows selected.
SQL> column owner format a10;
SQL> column table_name format a32;
SQL> column tablespace_name format a32;
SQL> column index_name format a32;
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='TB_TEST';
OWNER TABLE_NAME TABLESPACE_NAME
---------- -------------------------------- --------------------------------
HXL01 TB_TEST TPS_TEST
SQL> select owner,index_name,table_name,tablespace_name from dba_indexes where table_name='TB_TEST';
OWNER INDEX_NAME TABLE_NAME TABLESPACE_NAME
---------- -------------------------------- -------------------------------- --------------------------------
HXL01 IDX_CREATETIME TB_TEST TPS_TEST01
HXL01 SYS_C007744 TB_TEST TPS_TEST
12.源库目的库表空间修改为读写
源库和目的库都需要执行
alter tablespace TPS_TEST read write;
alter tablespace TPS_TEST01 read write;