DB-hub Technology Oracle Lab – 跨操作系统迁移表空间
Oracle Lab – 跨操作系统迁移表空间
1.实验环境
1）源端：
CentOS
Oracle version: 12.2.0.1.0
Database Name: SLOTH
endianness： little
2）目标端：
CentOS
Oracle version: 12.2.0.1.0
Database Name: MAMMAL
endianness： little
3）源端和目标端db_block_size相同：
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
4）源端和目标端DB 版本相同
5）源端和目标端time zone version 相同
6）操作系统可以不同，例如: Windows Server –> Redhat Linux
7）endianness 格式
查看 endianness
set linesize 500
set pages 50000
col name for a45
SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30) NAME, ENDIAN_FORMAT
FROM VTRANSPORTABLE_PLATFORM tp, VDATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_ID NAME ENDIAN_FORMAT
----------- -------------------- --------------
13 Linux x86 64-bit Little
如果endianness 格式不同，需要预先转换。再拷贝数据文件到目标端。
2.源端创建测试数据
Login as SYS :
CREATE TABLESPACE TS_SALES
DATAFILE '/oradata/data/HAMSTER/ts_sales01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE 100M
EXTENT MANAGEMENT LOCAL;
CREATE USER peacock IDENTIFIED BY peacock
default tablespace TS_SALES
temporary tablespace TEMP
quota unlimited on TS_SALES;
grant connect , resource, create session, alter session to peacock;
grant insert any table, update any table,delete any table, drop any table to peacock;
grant create table, create sequence to peacock;
Login as PEACOCK :
-- 创建数据表
CREATE TABLE dept (
deptno NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
dname VARCHAR2(14) ,
loc VARCHAR2(13)
) ;
CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
CREATE TABLE bonus (
enamE VARCHAR2(10) ,
job VARCHAR2(9) ,
sal NUMBER,
comm NUMBER
) ;
CREATE TABLE salgrade
grade NUMBER,
losal NUMBER,
hisal NUMBER
);
-- 插入测试数据 —— dept
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
-- 插入测试数据 —— emp
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-07-87','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-07-87','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
-- 插入测试数据 —— salgrade
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
-- 事务提交
COMMIT;
3.创建expdp目录
第一步：在服务器上创建export目录
mkdir -p /bck/export
第二步：创建逻辑目录
用sys管理员登录sqlplus
create directory exp_tbs as '/bck/export';
第三步：检查是否存在
SQL> col OWNER for a20
SQL> col DIRECTORY_NAME for a30
SQL> col DIRECTORY_PATH for a70
SQL> select * from dba_directories;
第四步：给指定用户赋予在该目录的操作权限（不是必须）
grant read,write on directory exp_tbs to peacock;
4.检查表空间自包含
自包含（Self-Contained）概念：
自包含表示用于传输的内部表空间集没有引用指向外部表空间集。自包含分为两种：一般自包含表空间集和完全（严格）自包含表空间集。
常见的以下情况是违反自包含原则的：
- 索引在内部表空间集，而表在外部表空间集（相反地，如果表在内部表空间集，而索引在外部表空间集，则不违反自包含原则）。
- 分区表一部分区在内部表空间集，一部分在外部表空间集（对于分区表，要么全部包含在内部表空间集中，要么全不包含）。
- 如果在传输表空间时同时传输约束，则对于引用完整性约束，约束指向的表在外部表空间集，则违反自包含约束；如果不传输约束，则与约束指向无关。
- 表在内部表空间集，而lob列在外部表空间集，则违反自包含约束。
通常可以通过系统包DBMS_TTS来检查表空间是否自包含，验证可以以两种方式执行：非严格方式和严格方式。（full_check=false/true）
执行完过程dbms_tts.transport_set_check之后，系统将违反自包含表空间的信息存入临时表transport_set_violations。查询该表时，如果没有返回任何信息，说明表空间是自包含的.
语法:DBMS_TTS.TRANSPORT_SET_CHECK(
Ts_list IN CLOB,
Incl_constraints IN BOOLEAN DEFAULT FALSE,
Full_check IN BOOLEAN DEFAULT FALSE);
其中：
ts_list 是要检查的表空间列表,有多个的话，中间用逗号隔开
incl_constraints： 是否检查约束，默认为否
full_check： 是否执行严格自包含方式的检查，默认为否
例如：
DBMS_TTS.TRANSPORT_SET_CHECK('MYTSB',TRUE); --进行非严格方式的检查
DBMS_TTS.TRANSPORT_SET_CHECK('MYTSB',TRUE,TRUE); ---进行严格方式的检查
SQL> execute dbms_tts.transport_set_check('TS_SALES', TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
5.将表空间设置成只读
alter tablespace TS_SALES read only;
select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
BIGTBS ONLINE
TS_SALES READ ONLY
6.导出表空间metadata
expdp \'sys/oracle as sysdba\' dumpfile=ts.dmp directory=exp_tbs transport_tablespaces=ts_sales transport_full_check=y logfile=ts.log
Export: Release 12.2.0.1.0 - Production on Thu Jan 21 21:31:14 2021
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 "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" dumpfile=ts.dmp directory=exp_tbs transport_tablespaces=ts_sales transport_full_check=y logfile=ts.log
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/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/bck/export/ts.dmp
******************************************************************************
Datafiles required for transportable tablespace TS_SALES:
/oradata/data/HAMSTER/ts_sales01.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Jan 21 21:31:58 2021 elapsed 0 00:00:38
7. 转换数据文件
如果endianness 格式不同，需要转换数据文件。转换可以在源端进行，也可以在目标端进行。
$rman target /
RMAN> convert tablespace 'TS_SALES'
to platform="Linux x86 64-bit"
db_file_name_convert="/oradata/data/HAMSTER/ts_sales01.dbf", "/tmp/ts_sales01.dbf";
Starting conversion at source at 22-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/oradata/data/HAMSTER/ts_sales01.dbf
converted datafile=/tmp/ts_sales01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 22-JAN-21
8.创建impdp目录
第一步：在服务器上创建export目录
mkdir -p /bck/import
第二步：创建逻辑目录
用sys管理员登录sqlplus
create directory imp_tbs as '/bck/import';
第三步：检查是否存在
col OWNER for a20
col DIRECTORY_NAME for a30
col DIRECTORY_PATH for a70
select * from dba_directories;
9.拷贝文件到目标端
1）拷贝dump 文件
[oracle@poc12a:/bck/import]$scp oracle@orapg:/bck/export/ts.dmp /bck/import
2）拷贝表空间数据文件
[oracle@poc12a:/bck/import]$scp oracle@orapg:/oradata/data/HAMSTER/ts_sales01.dbf /oradata/data/SLOTH/
10.创建用户
创建用户，不指定默认表空间，默认表空间是users。
提前创建用户与默认表空间与临时表空间，否则报下面的错:
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user PEACOCK does not exist in the database
CREATE USER peacock IDENTIFIED BY peacock temporary tablespace TEMP;
11.Import表空间
impdp \'sys/oracle as sysdba\' dumpfile=ts.dmp transport_datafiles=/oradata/data/SLOTH/ts_sales01.dbf directory=imp_tbs remap_schema=peacock:peacock logfile=ts.log
- TRANSPORT_DATAFILES 该选项用于指定搬移空间时要被导入到目标数据库的数据文件
修改表空间read & write
alter tablespace TS_SALES read write;
12.修改用户
alter user peacock default tablespace TS_SALES;
alter user peacock quota unlimited on TS_SALES;
grant connect, resource to peacock;
13.验证
以peacock登录sqlplus:
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------------
DEPT
EMP
BONUS
SALGRADE
SQL> select * from DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
14. Reference
Database Backup and Recovery Advanced User’s Guide
12c How Perform Cross-Platform Database Transport to different Endian Platform with RMAN Backup Sets
RMAN> BACKUP
TO PLATFORM 'Linux x86 64-bit'
FORMAT '/tmp/xplat_backups/trans_ts.bck'
DATAPUMP FORMAT '/tmp/xplat_backups/trans_ts_dmp.bck'
TABLESPACE test1;
RMAN> RESTORE
FOREIGN TABLESPACE TEST1 to NEW
FROM BACKUPSET '/u01/app/oracle/oradata/test/trans_ts.bck'
DUMP FILE FROM BACKUPSET '/u01/app/oracle/oradata/test/trans_ts_dmp.bck';