TTS--利用传输表空间迁移数据库中单个表空间
在实际工作中，可能有种需求，库里面的某个表空间存的全部是历史数据，由于某种原因（历史数据归档，迁移至历史库或者由于正式库存储压力，迁移到别的空间充足的库归档），需要把某个具体的表空间迁移到另一套库中。你能想到什么办法哪？首当其冲就是impdp+network，当然可以，缺点就是执行时间长，而且一般表空间里的表很大的时候，会报错ORA-01555。还有可能就是XTTS，这个利器一般用于跨版本，跨平台的，停机窗口短的整库迁移，因为它是集成好的脚本。用于此处迁移一个表空间，有点大材小用。当然还有它的前身，TTS，用在这个场景就比较合适。
传输表空间特性的英文全称为Transportable Tablespaces，该特性实现的复制数据的方式介于物理和逻辑方式之间，实现原理是这样的:首先通过 Export 逻辑导出工具或 DataPump Export数据泵导出工具，导出操作的表空间中对象的元数据(Metadata)，然后复制表空间对应的数据文件和刚刚导出生成的Dump文件到目标服务器的适当路径下，最后再导入前面逻辑导出工具生成的Dump文件即可。
虽然它跟XTTS一样，有很多限制，但是，毕竟迁移的只有一个表空间，TTS操作方便简单，手动执行几个命令就能迁移成功，相对于复杂的步骤XTTS，在这个场景下，还是比较适合的。
环境准备
先不考虑TTS的限制，以下只为验证某个具体过程。
以下测试环境均为同版本，同平台，对下面两种情况进行简单说明。如果是跨平台，即比下面的步骤多转换步骤。
| column1 | 源端 | 目标端 |
|---|---|---|
| db类型 | 单实例 | 单实例 |
| db version | 11.2.0.4 | 11.2.0.4 |
| db 存储 | 文件系统 | ASM |
| OS版本及kernel版本 | RHEL 6.8 | RHEL 6.8 |
| db name | zhuo | orcl |
源端创建测试环境：
create user xtts identified by xtts default tablespace xtts;
grant connect,resource to xtts;
conn xtts/xtts;
create table TB0101_08_09(id number,name varchar2(1000),other_col char(1000));
begin
for i in 1..1000 loop
insert into TB0101_08_09 values(i,lpad('a',995,'a')||i,'other col..');
end loop;
commit;
end;
/
create index idx_id_name_TB0101_08_09 on TB0101_08_09(id,name) online;
create index idx_name_id_TB0101_08_09 on TB0101_08_09(name,id) online;
exec dbms_stats.gather_table_stats(null,'TB0101_08_09',NO_INVALIDATE => FALSE);
我们创建了一个XTTS表空间，里面创建了一些对象，现在利用TTS技术把这个表空间从源端迁移到目标端，包括里面的对象。
使用数据泵传输表空间
生成可传输表空间集
源端：
SQL> alter tablespace xtts read only;
Tablespace altered.
[oracle@oracle11g ~]$ expdp \'/ as sysdba\' directory=impdp dumpfile=xtts.dmp TRANSPORT_TABLESPACES=xtts Export: Release 11.2.0.4.0 - Production on Fri Oct 13 11:13:33 2023 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=impdp dumpfile=xtts.dmp TRANSPORT_TABLESPACES=xtts Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is: /home/oracle/xtts.dmp ****************************************************************************** Datafiles required for transportable tablespace XTTS: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_xtts_llkd435n_.dbf Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri Oct 13 11:13:47 2023 elapsed 0 00:00:13
这里EXPDP命令只是号出待传输表空间的目录结构信息 (元数据)，并不包含实际数据，因此导出的速度非常快，千万别看到它很小(文件小的另一个原因是该表空间内总共只有1张表1000条记录)，就以为导出的文件有问题。
此处如果没有把表空间置为read only，生成表空间集的时候，会报错：
复制传输集到目标端
复制表空间对应的数据文件及导出表空间元数据生成的 Dump 文件到目标库。
源端： [oracle@oracle11g ~]$ scp xtts.dmp 10.1.11.12:/home/oracle/ [oracle@oracle11g ~]$ scp /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_xtts_llkd435n_.dbf 10.1.11.12:/tmp
数据文件传输完成后，表空间及时read write。
SQL> alter tablespace xtts read write;
Tablespace altered.
目标端：
[grid@11gasm tmp]$ asmcmd
ASMCMD> cp /tmp/o1_mf_xtts_llkd435n_.dbf +DATADG/orcl/datafile/xtts.dbf
copying /tmp/o1_mf_xtts_llkd435n_.dbf -> +DATADG/orcl/datafile/xtts.dbf
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE OCT 13 10:00:00 Y SYSAUX.261.1023146139
DATAFILE UNPROT COARSE OCT 13 10:00:00 Y SYSTEM.260.1023146137
DATAFILE UNPROT COARSE OCT 13 10:00:00 Y UNDOTBS1.262.1023146139
DATAFILE UNPROT COARSE OCT 13 10:00:00 Y USERS.264.1023146143
DATAFILE UNPROT COARSE OCT 13 10:00:00 Y ZHUO.266.1024792071
N xtts.dbf => +DATADG/ASM/DATAFILE/xtts.dbf.267.1150111081
导入表空间集
目标端：
提前创建用户：
--此处为了避免指定的表空间不存在，就是用数据库默认的表空间即可，如果提前创建用户默认表空间，后面impdp肯定会报错，因为是我们要传输的表空间。先不指定，后面再把权限等导过来。
SQL> create user xtts identified by xtts;
User created.
--此处TRANSPORT_datafiles指定为表空间中的数据文件新路径，如果有多个，用逗号隔开。
[oracle@11gasm ~]$ impdp \'/ as sysdba\' dumpfile=xtts.dmp directory=impdp TRANSPORT_datafiles=+datadg/orcl/datafile/xtts.dbf
Import: Release 11.2.0.4.0 - Production on Fri Oct 13 12:33:37 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" dumpfile=xtts.dmp directory=impdp TRANSPORT_datafiles=+datadg/orcl/datafile/xtts.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Oct 13 12:33:39 2023 elapsed 0 00:00:01
[oracle@11gasm ~]$ sqlplus xtts/xtts
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 12:41:58 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01045: user XTTS lacks CREATE SESSION privilege; logon denied
Enter user-name:
没有权限，单独导入权限：
源端：
[oracle@oracle11g ~]$ expdp \'/ as sysdba\' directory=impdp dumpfile=user.dmp schemas=xtts include=USER,system_grant,OBJECT_GRANT,ROLE_GRANT,role Export: Release 11.2.0.4.0 - Production on Fri Oct 13 12:48:50 2023 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=impdp dumpfile=user.dmp schemas=xtts include=USER,system_grant,OBJECT_GRANT,ROLE_GRANT,role Estimate in progress using BLOCKS method... Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT ORA-39168: Object path OBJECT_GRANT was not found. ORA-39168: Object path ROLE was not found. Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /home/oracle/user.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at Fri Oct 13 12:48:53 2023 elapsed 0 00:00:02 [oracle@oracle11g ~]$ scp user.dmp 10.1.11.12:/home/oracle oracle@10.1.11.12's password: user.dmp
目标端导入：
[oracle@11gasm ~]$ impdp \'/ as sysdba\' directory=impdp dumpfile=user.dmp Import: Release 11.2.0.4.0 - Production on Fri Oct 13 12:50:43 2023 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=impdp dumpfile=user.dmp Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"XTTS" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Oct 13 12:50:44 2023 elapsed 0 00:00:01
只有用户存在创建失败，忽略即可。主要把权限导入。
验证数据，并把表空间online：
[oracle@11gasm ~]$ sqlplus xtts/xtts
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 12:51:22 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select count(*) from TB0101_08_09;
COUNT(*)
----------
1000
SQL> set pages 1000 lines 1000
SQL> col object_name for a30
SQL> col object_type for a10
SQL> col owner for a10
SQL> select owner,object_name,object_type,object_id,data_object_id,created,last_ddl_time from dba_objects where object_name in(select index_name from dba_indexes where table_name='TB0101_08_09');
OWNER OBJECT_NAME OBJECT_TYP OBJECT_ID DATA_OBJECT_ID CREATED LAST_DDL_
---------- ------------------------------ ---------- ---------- -------------- --------- ---------
XTTS IDX_NAME_ID_TB0101_08_09 INDEX 80951 80626 13-OCT-23 13-OCT-23
XTTS IDX_ID_NAME_TB0101_08_09 INDEX 80952 80623 13-OCT-23 13-OCT-23
SQL> conn / as sysdba
Connected.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where TABLESPACE_NAME='XTTS';
TABLESPACE_NAME STATUS
------------------------------ ---------
ZHUO READ ONLY
SQL> alter tablespace xtts online;
Tablespace altered.
修改默认表空间：
SQL> conn / as sysdba
Connected.
SQL> select username,default_tablespace from dba_users where username='XTTS';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
XTTS USERS
SQL> alter user xtts default tablespace xtts;
User altered.
SQL> select username,default_tablespace from dba_users where username='XTTS';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
XTTS XTTS
至此，整个迁移过程就算全部完成了。
只需要3步简单操作，就能完成整个迁移。但是也有缺点：
1、表空间必须要read only，以为着要停业务。
2、如果是ASM，源端和目标端本地必须要有足够的空间，来容纳整个表空间的数据文件，才能进行scp。
说明–是否提前创建用户
关于是否提前创建传输表空间里面的对象对应的这些用户，有本书里面对这个参数的说明，有错误：
[oracle@11gasm ~]$ impdp \'/ as sysdba\' dumpfile=xtts.dmp directory=impdp TRANSPORT_datafiles=+datadg/orcl/datafile/xtts.dbf Import: Release 11.2.0.4.0 - Production on Fri Oct 13 11:33:36 2023 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" dumpfile=xtts.dmp directory=impdp TRANSPORT_datafiles=+datadg/orcl/datafile/xtts.dbf Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK ORA-39123: Data Pump transportable tablespace job aborted ORA-29342: user XTTS does not exist in the database Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Fri Oct 13 11:33:38 2023 elapsed 0 00:00:01
如果目标端没有提前创建这个用户，他报错：ORA-39123 ORA-29342。必须提前创建这些对象对应的用户，impdp的导入元数据文件的时候，并不会默认创建这些用户。
这个情况也可以从expdp的导出日志看出：
导出日志里面没有user相关的对象导出，只有table，index，PLUGTS_BLK的导出。
那么我们expdp的时候，是否可以加上include，把创建user语句一起导出：
可以看到他会报错：ORA-39168。可见，TRANSPORT_TABLESPACES参数和其他参数是互斥的，只能单独使用。
所以，必须提前创建这些对象对应的用户。除非使用repmap_schema参数。对于大量的用户，可以参考如下创建脚本：
set serveroutput ON echo on
DECLARE
v_sql VARCHAR2 (2000);
BEGIN
FOR c_username IN (SELECT name, password
FROM sys.user$@to_old
WHERE name NOT IN ('ANONYMOUS',
'APEX_030200',
'APEX_PUBLIC_USER',
'APPQOSSYS',
'CTXSYS',
'DBSNMP',
'DIP',
'EXFSYS',
'FLOWS_FILES',
'MDDATA',
'MDSYS',
'MGMT_VIEW',
'OLAPSYS',
'ORACLE_OCM',
'ORDDATA',
'ORDPLUGINS',
'ORDSYS',
'OUTLN',
'OWBSYS',
'OWBSYS_AUDIT',
'SI_INFORMTN_SCHEMA',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'SYS',
'SYSMAN',
'SYSTEM',
'WMSYS',
'XDB',
'XS$NULL','DMSYS','TSMSYS')
AND TYPE# = 1)
LOOP
v_sql :=
'create user '
|| c_username.name
|| ' identified by values '||chr(39)
|| c_username.password||chr(39)
|| ';';
DBMS_OUTPUT.put_line (v_sql);
END LOOP;
END;
/
目标端执行，但是必须提前创建dblink。
ADG备库生成传输表空间集
ADG的备库执行expdp会报错：ORA-16000
qhstadb1:/home/oracle(qhstatdb1)$expdp \'/ as sysdba\' directory=dir dumpfile=xtts.dmp TRANSPORT_TABLESPACES=TBS_TOPTEA_DATA Export: Release 19.0.0.0.0 - Production on Fri Oct 13 17:31:25 2023 Version 19.13.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-31626: job does not exist ORA-00604: error occurred at recursive SQL level 1 ORA-06512: at "SYS.KUPV$FT", line 1142 ORA-16000: database or pluggable database open for read-only access ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY" ORA-06512: at "SYS.KUPV$FT", line 926
所以可以使用exp
[oracle@oracle11g ~]$ exp \'/ as sysdba\' file=/home/oracle/xtts.dmp TRANSPORT_TABLESPACE=y TABLESPACES=zhuo
Export: Release 11.2.0.4.0 - Production on Sat May 9 16:24:50 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 29335 encountered
ORA-29335: tablespace 'ZHUO' is not read only
ORA-06512: at "SYS.DBMS_PLUGTS", line 688
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
还是会报错
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where TABLESPACE_NAME='ZHUO';
TABLESPACE_NAME STATUS
------------------------------ ---------
ZHUO ONLINE
SQL> alter tablespace zhuo read only;
alter tablespace zhuo read only
*
ERROR at line 1:
ORA-16000: database open for read-only access
所以针对ADG备库，难点就是如何把表空间置为read only了。
可以采用如下方法：1、需要将备库打开到读写状态。2、表空间置为read only；3、重复上面步骤，生成传输表空间集，传送数据文件。4、将ADG备库启动到备库恢复状态。再次期间，归档日志一定要在，要不然主备就会不同步。也就是1-3步骤之间的归档日志，要全部保留。
将备库打开到读写状态
alter database recover managed standby database cancel;
--备库创建闪回点
CREATE RESTORE POINT STANDBY_XTTS GUARANTEE FLASHBACK DATABASE;
--激活备库为read write
ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE OPEN;
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> CREATE RESTORE POINT STANDBY_XTTS GUARANTEE FLASHBACK DATABASE;
Restore point created.
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
表空间置为read only
--这下就可以置为read only了。
SQL> alter tablespace zhuo read only;
Tablespace altered.
生成传输表空间集，传送数据文件
本次采用不落地的方式进行导入元数据。
目标端创建针对ADG备库的dblink
SQL> create public database link to_zhuodg connect to system identified by oracle using 'zhuodg';
Database link created.
ADG源端传输表空间对应的数据文件
[oracle@oracle11g ~]$ scp /u01/app/oracle/oradata/zhuodg/ZHUODG/datafile/o1_mf_zhuo_04uvp9bj_.dbf 10.1.11.12:/tmp The authenticity of host '10.1.11.12 (10.1.11.12)' can't be established. RSA key fingerprint is 11:1b:05:8d:81:24:b3:b1:68:26:47:78:76:ae:a1:5c. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.1.11.12' (RSA) to the list of known hosts. oracle@10.1.11.12's password: o1_mf_zhuo_04uvp9bj_.dbf 100% 5120MB 121.9MB/s 00:42
目标端拷贝至正确位置：
ASMCMD> cp /tmp/o1_mf_zhuo_04uvp9bj_.dbf +datadg/orcl/datafile/zhuo.dbf
copying /tmp/o1_mf_zhuo_04uvp9bj_.dbf -> +datadg/orcl/datafile/zhuo.dbf
目标端执行元数据导入:
impdp \'/ as sysdba\' directory=impdp logfile=zhuo.log \ network_link=to_zhuodg \ transport_tablespaces=zhuo \ transport_datafiles='+datadg/orcl/datafile/zhuo.dbf'
报错，没有对应的用户，还是必须提前创建用户。
执行上面创建必要用户的脚本，创建用户：
SQL>
SQL> set serveroutput ON echo on
SQL> DECLARE
2 v_sql VARCHAR2 (2000);
3 BEGIN
4 FOR c_username IN (SELECT name, password
5 FROM sys.user$@to_zhuodg
6 WHERE name NOT IN ('ANONYMOUS',
7 'APEX_030200',
8 'APEX_PUBLIC_USER',
9 'APPQOSSYS',
10 'CTXSYS',
11 'DBSNMP',
12 'DIP',
13 'EXFSYS',
14 'FLOWS_FILES',
15 'MDDATA',
16 'MDSYS',
17 'MGMT_VIEW',
18 'OLAPSYS',
19 'ORACLE_OCM',
20 'ORDDATA',
21 'ORDPLUGINS',
22 'ORDSYS',
23 'OUTLN',
24 'OWBSYS',
25 'OWBSYS_AUDIT',
26 'SI_INFORMTN_SCHEMA',
27 'SPATIAL_CSW_ADMIN_USR',
28 'SPATIAL_WFS_ADMIN_USR',
29 'SYS',
30 'SYSMAN',
31 'SYSTEM',
32 'WMSYS',
33 'XDB',
34 'XS$NULL','DMSYS','TSMSYS')
35 AND TYPE# = 1)
36 LOOP
37 v_sql :=
38 'create user '
39 || c_username.name
40 || ' identified by values '||chr(39)
41 || c_username.password||chr(39)
42 || ';';
43 DBMS_OUTPUT.put_line (v_sql);
44 END LOOP;
45 END;
46 /
create user ZHUO identified by values '97FC9C262995417F';
PL/SQL procedure successfully completed.
SQL> create user ZHUO identified by values '97FC9C262995417F';
User created.
目标端执行元数据导入：
[oracle@11gasm ~]$ impdp \'/ as sysdba\' directory=impdp logfile=zhuo.log \
> network_link=to_zhuodg \
> transport_tablespaces=zhuo \
> transport_datafiles='+datadg/orcl/datafile/zhuo.dbf'
Import: Release 11.2.0.4.0 - Production on Fri Oct 13 15:30:26 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=impdp logfile=zhuo.log network_link=to_zhuodg transport_tablespaces=zhuo transport_datafiles=+datadg/orcl/datafile/zhuo.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Oct 13 15:30:44 2023 elapsed 0 00:00:17
导入权限
[oracle@11gasm ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 15:32:23 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> conn zhuo/zhuo
ERROR:
ORA-01045: user ZHUO lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> exit
没有权限，无法验证数据。
[oracle@11gasm ~]$ impdp \'/ as sysdba\' directory=impdp logfile=zhuo.log \
> network_link=to_zhuodg \
> schemas=zhuo \
> include=USER,system_grant,OBJECT_GRANT,ROLE_GRANT,role
Import: Release 11.2.0.4.0 - Production on Fri Oct 13 15:34:25 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=impdp logfile=zhuo.log network_link=to_zhuodg schemas=zhuo include=USER,system_grant,OBJECT_GRANT,ROLE_GRANT,role
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ZHUO" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
ORA-39168: Object path SYSTEM_GRANT was not found.
ORA-39168: Object path OBJECT_GRANT was not found.
ORA-39168: Object path ROLE was not found.
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 4 error(s) at Fri Oct 13 15:34:27 2023 elapsed 0 00:00:02
验证数据,并online表空间：
[oracle@11gasm ~]$ sqlplus zhuo/zhuo
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 15:34:36 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select count(*) from test;
COUNT(*)
----------
80065
SQL> conn / as sysdba
Connected.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where TABLESPACE_NAME='ZHUO';
TABLESPACE_NAME STATUS
------------------------------ ---------
ZHUO READ ONLY
SQL> alter tablespace zhuo online;
Tablespace altered.
SQL> alter user zhuo default tablespace zhuo;
User altered.
将ADG备库启动到备库恢复状态
--启动mount状态
shutdown immediate
startup mount
--闪回到还原点
flashback database to restore point STANDBY_XTTS;
--转化为备库类型(rac集群这一步只能启动一个节点)
alter database convert to physical standby;
--转化之后，数据库会启为started，需要关闭再重新启到mount
shutdown immediate;
startup mount
--恢复
alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database cancel;
--删除还原点
drop restore point STANDBY_XTTS;
--开启adg
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
具体过程如下：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 352323608 bytes
Database Buffers 163577856 bytes
Redo Buffers 3780608 bytes
Database mounted.
SQL> flashback database to restore point STANDBY_XTTS;
Flashback complete.
SQL> alter database convert to physical standby;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 352323608 bytes
Database Buffers 163577856 bytes
Redo Buffers 3780608 bytes
Database mounted.
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> set echo off
SQL> set lines 300 pages 50
SQL> set heading on
SQL> set verify off
SQL> col name for a30
SQL> col value for a30
SQL> col TIME_COMPUTED for a20
SQL> col datum_time for a20 heading 'LAST_RECEIVED_TIME'
SQL> col inst_id for 99 heading 'ID'
SQL> break on inst_id
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select inst_id,name,value,time_computed,DATUM_TIME,sysdate from gv$dataguard_stats order by inst_id;
ID NAME VALUE TIME_COMPUTED LAST_RECEIVED_TIME SYSDATE
--- ------------------------------ ------------------------------ -------------------- -------------------- -------------------
1 transport lag +00 00:00:00 05/09/2020 17:27:50 05/09/2020 17:27:48 2020-05-09 17:27:50
estimated startup time 6 05/09/2020 17:27:50 2020-05-09 17:27:50
apply finish time 05/09/2020 17:27:50 2020-05-09 17:27:50
apply lag +00 00:00:00 05/09/2020 17:27:50 05/09/2020 17:27:48 2020-05-09 17:27:50
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> drop restore point STANDBY_XTTS;
Restore point dropped.
SQL> alter database open read only;
Database altered.
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> set echo off
SQL> set lines 300 pages 50
SQL> set heading on
SQL> set verify off
SQL> col name for a30
SQL> col value for a30
SQL> col TIME_COMPUTED for a20
SQL> col datum_time for a20 heading 'LAST_RECEIVED_TIME'
SQL> col inst_id for 99 heading 'ID'
SQL> break on inst_id
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select inst_id,name,value,time_computed,DATUM_TIME,sysdate from gv$dataguard_stats order by inst_id;
Session altered.
SQL>
ID NAME VALUE TIME_COMPUTED LAST_RECEIVED_TIME SYSDATE
--- ------------------------------ ------------------------------ -------------------- -------------------- -------------------
1 transport lag +00 00:00:00 05/09/2020 17:28:56 05/09/2020 17:28:54 2020-05-09 17:28:56
estimated startup time 6 05/09/2020 17:28:56 2020-05-09 17:28:56
apply finish time 05/09/2020 17:28:56 2020-05-09 17:28:56
apply lag +00 00:00:00 05/09/2020 17:28:56 05/09/2020 17:28:54 2020-05-09 17:28:56
备库又恢复到了之前的同步状态。
以上操作均在目标端和ADG的备库操作，不涉及主库，所以对主库没有任何影响。但是唯一要注意的就是从打开read write模式，到数据文件传输完成期间的归档日志，一定要在，不然后面确实归档日志，重新转化为ADG备库，同步有问题。
参考：https://blog.csdn.net/sinat_36757755/article/details/132483315
使用RMAN传输
上面使用的数据泵传输表空间，就是被传输的表空间在传输过程中必须置为READONLY。READ ONLY的时候，就是expdp开始导出元数据的时间到数据文件传输到目标端完成的之间的时长。而在实际操作过程中，对于某些生产数据库，将表空间置为 READONLY 是件非常复杂甚至不允许的事情，在这种情况下如果要通过传输表空间特性，实现快速迁移数据，恐怕只能在备份数据库上操作了。
不过，一般机构都没有专用的备份数据库将Standby。如果Data Guard 环境也没有那就麻烦了，首先必须找台临时的机器通过备份恢复一个临时数据库，然后在其中生成传输集，最后再将这个临时数据库删除。步骤麻烦不说，只想想这其中数据文件要被来回复制多次就让人头疼(至少三次)，如果数据量大的话，仅I/O的开销就要花费相当长的时间。本来使用传输表空间特性是为了提高效率，结果由于操作烦琐、步骤、重复，反倒要比其他方式花费更多时间，有没有简单的方式，只需要执行一个命令，就能直接使用备份创建出传输集?于是Oracle在RMAN中提供了TRANSPORT TABLESPACE命令。
数据泵迁移
源端执行匿名块，生成创建用户DDL
以下适用于19c环境，默认用户有区别
set serveroutput ON echo on
DECLARE
v_sql VARCHAR2 (2000);
BEGIN
FOR c_username IN (SELECT name, password
FROM sys.user$
WHERE name NOT IN ('SYS',
'SYSTEM',
'SYSDG',
'SYSKM',
'AUDSYS',
'SYSRAC',
'SYSBACKUP',
'OUTLN',
'GSMADMIN_INTERNAL',
'GSMUSER',
'DIP',
'XS$NULL',
'REMOTE_SCHEDULER_AGENT',
'DBSFWUSER',
'ORACLE_OCM',
'SYS$UMF',
'DBSNMP',
'APPQOSSYS',
'GSMCATUSER',
'GGSYS',
'ANONYMOUS',
'XDB',
'WMSYS',
'OJVMSYS',
'CTXSYS',
'ORDPLUGINS',
'ORDSYS',
'ORDDATA',
'MDSYS',
'SI_INFORMTN_SCHEMA',
'OLAPSYS',
'MDDATA')
AND TYPE# = 1)
LOOP
v_sql :=
'create user '
|| c_username.name
|| ' identified by values '||chr(39)
|| c_username.password||chr(39)
|| ';';
DBMS_OUTPUT.put_line (v_sql);
END LOOP;
END;
/
迁移数据
导入权限，为了变报错，先都授予dba权限，后面再收回，重新从源库导入权限。
授予dba权限
set serveroutput ON echo on
DECLARE
v_sql VARCHAR2 (2000);
BEGIN
FOR c_username IN (SELECT name, password
FROM sys.user$
WHERE name NOT IN ('ANONYMOUS',
'APEX_030200',
'APEX_PUBLIC_USER',
'APPQOSSYS',
'CTXSYS',
'DBSNMP',
'DIP',
'EXFSYS',
'FLOWS_FILES',
'MDDATA',
'MDSYS',
'MGMT_VIEW',
'OLAPSYS',
'ORACLE_OCM',
'ORDDATA',
'ORDPLUGINS',
'ORDSYS',
'OUTLN',
'OWBSYS',
'OWBSYS_AUDIT',
'SI_INFORMTN_SCHEMA',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'SYS',
'SYSMAN',
'SYSTEM',
'WMSYS',
'XDB',
'XS$NULL','DMSYS','TSMSYS')
AND TYPE# = 1)
LOOP
v_sql :=
'grant dba to '
|| c_username.name|| ';';
DBMS_OUTPUT.put_line (v_sql);
END LOOP;
END;
/
导数据
nohup nohup impdp system/Qhyd_2020@oldact parallel=10 metrics=yes directory=impdp network_link=to_act cluster=N logfile=act.log TABLESPACES=TBS_OLD_DATA exclude=TABLE_STATISTICS,INDEX_STATISTICS REMAP_TABLESPACE=USERS:TBS_OLD_DATA,TS_PUB_DATA:TBS_OLD_DATA,TS_PUB_INDEX:TBS_OLD_DATA,TMP_TS_VB_ACCT_01:TBS_OLD_DATA,TS_VB_ACCT_02:TBS_OLD_DATA,TS_VB_ACCT_03:TBS_OLD_DATA,TS_VB_ACCT_04:TBS_OLD_DATA,TS_VB_ACCT_05:TBS_OLD_DATA,TS_VB_ACCT_IDX_01:TBS_OLD_DATA,TS_VB_ACCT_IDX_02:TBS_OLD_DATA,TS_VB_ACCT_IDX_03:TBS_OLD_DATA,TS_VB_ACCT_IDX_04:TBS_OLD_DATA,TS_VB_ACCT_IDX_05:TBS_OLD_DATA,TS_VB_BASE_DAT:TBS_OLD_DATA,TS_VB_BASE_IND:TBS_OLD_DATA,TS_VB_INFO:TBS_OLD_DATA,TS_VB_INFO_IDX:TBS_OLD_DATA,TS_VB_INTF_DAT:TBS_OLD_DATA,TS_VB_INTF_IND:TBS_OLD_DATA,TBS_INFO_DATA:TBS_OLD_DATA,TBS_INFO_INDEX:TBS_OLD_DATA,TBS_INFO_HDATA:TBS_OLD_DATA,TBS_INFO_HINDEX:TBS_OLD_DATA,TBS_IBOSS_DATA:TBS_OLD_DATA,TBS_IBOSS_INDEX:TBS_OLD_DATA,TBS_VBLOG_DATA:TBS_OLD_DATA,TBS_VBLOG_INDEX:TBS_OLD_DATA,TBS_ACCT_DATA_01:TBS_OLD_DATA,TBS_DH_DATA:TBS_OLD_DATA,TBS_OLD_DATA:TBS_OLD_DATA,TBS_TOPTEA_DATA:TBS_OLD_DATA,TBS_MVLOG_DATA:TBS_OLD_DATA,TS_VB_ACCT_01:TBS_OLD_DATA,TBS_COM:TBS_OLD_DATA & &
注意参数：REMAP_TABLESPACE。之前用过通用表达式，%:TBS_OLD_DATA，但是在此处不生效。仍然会报ORA-00959.
经查，官方文档：https://docs.oracle.com/database/121/SUTIL/GUID-F467946E-50B3-4CC0-937A-CC8C18AAF8C1.htm#SUTIL929
确实没有通配符%这种用法，但是之前确实是成功得。唯一得可能就是区分数据库版本。
回收dba权限，重新导入权限：
set serveroutput ON echo on
DECLARE
v_sql VARCHAR2 (2000);
BEGIN
FOR c_username IN (SELECT name, password
FROM sys.user$
WHERE name NOT IN ('ANONYMOUS',
'APEX_030200',
'APEX_PUBLIC_USER',
'APPQOSSYS',
'CTXSYS',
'DBSNMP',
'DIP',
'EXFSYS',
'FLOWS_FILES',
'MDDATA',
'MDSYS',
'MGMT_VIEW',
'OLAPSYS',
'ORACLE_OCM',
'ORDDATA',
'ORDPLUGINS',
'ORDSYS',
'OUTLN',
'OWBSYS',
'OWBSYS_AUDIT',
'SI_INFORMTN_SCHEMA',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'SYS',
'SYSMAN',
'SYSTEM',
'WMSYS',
'XDB',
'XS$NULL','DMSYS','TSMSYS')
AND TYPE# = 1)
LOOP
v_sql :=
'revoke dba from '
|| c_username.name|| ';';
DBMS_OUTPUT.put_line (v_sql);
END LOOP;
END;
/
impdp system/oracle parallel=10 metrics=yes directory=DATA_PUMP_DIR network_link=to_zhuo cluster=N logfile=act.log schemas=zhuo content=metadata_only exclude=table,index REMAP_TABLESPACE=zhuo:test
总结
- 如果没有ADG，但是这套库的表空间有停机窗口来read only，那么使用默认的数据泵传输表空间。
- 如果刚好有ADG，那么就不用停机，在备库直接使用传输表空间即可。但是生成传输表空间集的时候，必须使用exp命令。
- 如有没有ADG，也没有停机窗口，那么就使用RMAN TRANSPORT TABLESPACE。
最后修改时间：2023-10-19 21:25:03
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
