首页 > 其他分享 >表空间传输TTS(RAC-to-单机)

表空间传输TTS(RAC-to-单机)

时间：2023-12-12 17:11:54浏览次数：35  
标签：RAC 单机 TTS TEST TPS SQL oracle tablespace name

环境:
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;

 

标签：RAC,单机,TTS,TEST,TPS,SQL,oracle,tablespace,name
From： https://www.cnblogs.com/hxlasky/p/17897336.html

相关文章

  • .net批量插入到oracle数据库
    最近用到批量插入数据到oracle，总结了网上的方案 方式一：使用数组绑定https://www.codenong.com/343299/数据库：CREATE TABLE jkl_test (id NUMBER(9));USINGOracle.DataAccess.Client;namespaceOracleArrayInsertExample{classProgram{staticv......
  • Oracle 表空间常用SQL
    --1、查看表空间的名称、大小、剩余空间SELECTt.tablespace_nameas表空间名,round(SUM(bytes/(1024*1024)),0)as大小_MFROMdba_tablespacestinnerjoindba_data_filesd on t.tablespace_name=d.tablespace_nameGROUPBYt.tablespace_name;--2、查看表空间物理......
  • 【常见问题】Python报错SyntaxError: Non-ASCII character '\\xe7' in file
    错误原因：windows默认编码格式是GBK，macOS，linux是utf-8。当使用windows且代码内有GBK不支持的字符集的时候，就会报错。解决方法：方法一在python文件的顶部加上编码格式#-*-coding:utf-8-*-方法二在python3.7以及之后，使用utf-8模式https://peps.python.org/pep-0540/pyt......
  • Oracle-修改数据库密码
    当Oracle数据库用户的密码过期时，你可以采取以下步骤来处理：1、连接到数据库：使用具有管理员权限的账户（比如SYS或SYSTEM用户）连接到Oracle数据库。查看过期用户：运行以下SQL查询语句查看已过期的用户列表：SELECTusernameFROMdba_usersWHEREaccount_status='EXPIRED......
  • abc.abstractmethod + property
    abc.abstractmethod+propertyhttps://stackoverflow.com/questions/14671095/abc-abstractmethod-property importabcclassFooBase(metaclass=abc.ABCMeta):@property@abc.abstractmethoddefgreet(self):"""mustbeimpl......
  • 从Linux到Laxcus，从单机操作系统到多机操作系统
    继续回答一位网友的提问，贴出来给大家看看，欢迎大家在下方留言交流。问：博主，据我所知，现在操作系统很多，它们做的都比你们好，分布式技术也很成熟，有不少产品，你们再搞一个Laxcus分布式操作系统，有这个必要吗？谁会买谁会用？Laxcus分布式操作系统有地方超过它们？你觉得你们能干过一票操作系统大......
  • CMC-Oracle科学计数法转换控制小数显示位数
    当使用oracle进行数据计算时，当数据过小会出现科学计数法，例如：4.13693246457752E-6这个时候可以使用to_char函数进行数字位数控制SELECTTO_CHAR(number,'fmt')FROMDUAL; selecta.arrivalavgratearrivalavgrate1,to_char(a.arrivalavgrate,'FM9999990.00999......
  • 深入理解Oracle 的 connect by level
    1、connectby connectby中的条件就表示了父子之间的连接关系比如connectbyid=priorpid默认是从根开始，如connectbypriorid=pid，表示构造树时，本记录的id是下条的pid，即找pid=本条id的记录做下条记录。交换prior位置，表示从叶开始。如connectbyid=priorpi......
  • ORACLE EBS R12 - 寄售功能知多少
    ORACLEEBSR12-寄售功能知多少-全威儒-博客园(cnblogs.com) 材料以寄售方式供应，是制造业实现零库存管理的一种方式。东西放在我这里，不用的时候，东西还是供应商的，用的时候，按“装机”数量进行付款。这种业务模式，对于竞争日趋白热化的家电行业，非常重要。库存量为零，库存占......
  • Oracle多层子查询无法识别外层table
    selectsomething,somthingelse,(select*from(selectQUOTE_PRICEasold_pricefromprice_historywhereprice_history.part_no=article_table.part_noorderbyvalid_fromdesc)whererownum=1)fromarticle_tab......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99