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 米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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 米乐app官网下载 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 米乐app官网下载 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
米乐app官网下载 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 米乐app官网下载 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的备库执行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 米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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
上面使用的数据泵传输表空间，就是被传输的表空间在传输过程中必须置为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.
经查，官方文档：
确实没有通配符%这种用法，但是之前确实是成功得。唯一得可能就是区分数据库版本。
回收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
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。