XTTS迁移环境调研
--源库、目标库硬件及软件清理
源库： 192.168.56.20 hostname:slient rdbms:11.2.0.4 os:rhel 6.7
目标库: 192.168.56.12 hostname:wang rdbms:11.2.0.4 os:rhel 7.2
--源库创建需要迁移的用户、表空间等
SQL> create tablespace xtts datafile '/u01/app/oracle/oradata/test/xtts.dbf' size 1g autoextend off;
Tablespace created.
SQL> create user test identified by test default tablespace xtts temporary tablespace temp02 quota unlimited on xtts profile default account unlock;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
SQL> conn test/test;
Connected.
--创建测试表test_xtts
create table test_xtts(id number(10),create_date date,name varchar2(100),up_date date);
--使用批量绑定技术插入400万数据初始测试数据
declare
type t_mid is table of test_xtts%rowtype index by binary_integer;
l_tab_mid t_mid;
begin
for i in 1 .. 4000000 loop
l_tab_mid(i).id := i;
l_tab_mid(i).create_date := sysdate;
l_tab_mid(i).name := lpad('a', 100, 'a');
l_tab_mid(i).up_date := sysdate;
end loop;
forall i in 1 .. l_tab_mid.count
insert into test_xtts values l_tab_mid (i);
commit;
end;
/
--给表test_xtts增加主键约束及建索引
alter table test_xtts add (constraint test_xtts_pk primary key (id));
create index idx01_test_xtts on test_xtts(create_date);
--收集统计信息
exec dbms_stats.gather_table_stats(ownname => 'TEST',TABNAME => 'TEST_XTTS' ,cascade => true,estimate_percent => 100);
--查询验证
SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------- --------------- ---------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP02 Name of default temporary tablespace
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TEST';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST XTTS TEMP02
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,NUM_ROWS,BLOCKS from dba_tables where table_name='TEST_XTTS';
OWNER TABLE_NAME TABLESPACE_NAME STATUS NUM_ROWS BLOCKS
--------------- --------------- --------------- -------- ---------- ----------
TEST TEST_XTTS XTTS VALID 4000000 71297
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='TEST_XTTS';
OWNER SEGMENT_NAME TABLESPACE_NAME SIZE_M
--------------- --------------- --------------- ----------
TEST TEST_XTTS XTTS 560
一、前期准备，先决条件检查
1.1. 检查数据库时区，保持两边一致
SQL> select dbtimezone from dual;
DBTIME
------
+08:00
一致。
1.2. 检查数据库字符集，保持两边一致
SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET%';
PARAMETER VALUE
------------------------- -----------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16
一致。
1.3. 检查目标端补丁情况
目标端psu无特殊需求，可以随情况安装最新即可，测试环境省略。
如果准备阶段使用dbms_file_transfer方法，目标端建议安装的补丁如下：
Patch 19023822，修复目标端使用dbms_file_transfer.get_file包获取源端数据文件出现ORA-03106的情况。
Patch 22171097: MERGE REQUEST ON TOP OF DATABASE PSU 11.2.0.4.6 FOR BUGS 17534365 19023822
如果准备阶段使用rman方法，目标端没有小补丁安装需求
1.4. 检查组件安装情况，对于不一样的组件，需要有所标记
--源库
SQL> Select comp_name from dba_registry;
COMP_NAME
-------------------------------------------------------
Oracle Enterprise Manager
Spatial
Oracle interMedia
OLAP Catalog
Oracle XML Database
Oracle Text
Oracle Expression Filter
Oracle Rule Manager
Oracle Workspace Manager
Oracle Data Mining
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
OLAP Analytic Workspace
Oracle OLAP API
17 rows selected.
--目标库
SQL> Select comp_name from dba_registry;
COMP_NAME
-------------------------------------------------------
OWB
Oracle Application Express
Oracle Enterprise Manager
OLAP Catalog
Spatial
Oracle Multimedia
Oracle XML Database
Oracle Text
Oracle Expression Filter
Oracle Rules Manager
Oracle Workspace Manager
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
OLAP Analytic Workspace
Oracle OLAP API
18 rows selected.
备注：组件不同，可能导致源端的部分对象无法导入到目标端
1.5. 检查是否使用了key compression的索引组织表
--检查是否使用索引压缩
Select index_name,table_name from dba_indexes where compression='ENABLED' and owner='TEST';
--检查是否有索引组织表
Select owner,table_name from dba_tables where iot_type is not null and owner='TEST';
如果存在，目标端需要安装patch 14835322，否则索引组织表的对象无法导入到目标端
目前该补丁只在11.2.0.3版本下提供，针对其他版本，如果iot表或者键值压缩索引无法导入时，建议在元数据导入完成后，将该类对象传输过去。
1.6. 检查源端是否有cluster 对象
select OWNER,CLUSTER_NAME,TABLESPACE_NAME from dba_clusters where owner='TEST';
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where owner='TEST' and object_type='CLUSTER';
=======================================================================
If the source contains cluster objects, then run "analyze cluster &cluster_name validate structure cascade" after XTTS has been completed in the target database and if it reports an ORA-1499 open the trace file and review if it has entries like:
kdcchk: index points to block 0x01c034f2 slot 0x1 chain length is 256
kdcchk: chain count wrong 0x01c034f2.1 chain is 1 index says 256
last entry 0x01c034f2.1 blockcount = 1
kdavls: kdcchk returns 3 when checking cluster dba 0x01c034a1 objn 90376
Then to repair this inconsistency either:
1. rebuild the cluster index.
or
2. Install fix bug 17866999 and run dbms_repair.repair_cluster_index_keycount
If after repairing the inconsistency the "analyze cluster &cluster_name validate structure cascade" still reports issues then recreate the affected cluster which involves recreating its tables.
=========================================================================
1.7. 检查RMAN DEVICE TYPE DISK 是否配置 COMPRESSED
ORA-19837: invalid blocksize 0 in backup piece header
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 2338
ORA-06512: at line 40
配置压缩会导致以上报错，通过rman > show all; 检查
1.8. SYS、SYSTEM用户是否有对象存在于业务表空间
如数据库用户SYS、SYSTEM在业务表空间上创建有对象，则这些对象不能通过XTTS迁移，需要在目标库手工创建。
SQL> select table_name , owner, tablespace_name from dba_tables where tablespace_name not in ('SYSTEM','SYSAUX') and owner in ('SYS','SYSTEM');
no rows selected
1.9. 检查表空间自包含
--检查表空间时，只检查业务表空间的自包含情况；系统表空间，临时表空间，undo表空间不在检查列
--在表空间传输的中，要求表空间集为自包含的，自包含表示用于传输的内部表空间集没有引用指向外部表空间集。
常见的以下情况是违反自包含原则的：
- 索引在内部表空间集，而表在外部表空间集（相反地，如果表在内部表空间集，而索引在外部表空间集，则不违反自包含原则）。
- 分区表一部分区在内部表空间集，一部分在外部表空间集（对于分区表，要么全部包含在内部表空间集中，要么全不包含）。
- 如果在传输表空间时同时传输约束，则对于引用完整性约束，约束指向的表在外部表空间集，则违反自包含约束；如果不传输约束，则与约束指向无关。
- 表在内部表空间集，而lob列在外部表空间集，则违反自包含约束。
exec dbms_tts.transport_set_check('将所有需要传输的表空间名写在这里用逗号隔开' ,TRUE,TRUE);
select * from transport_set_violations;
SQL> exec dbms_tts.transport_set_check('XTTS',TRUE,TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
1.10. 检查源端compatible参数
source端不可以是windows P7,source端的compatible.rdbms必须大于10.2.0，且不大于目标端compatible.rdbms.
如果目标端数据库版本是11.2.0.3或更低。那么需要在目标端装11.2.0.4 并创建实例，然后用来进行备份集转换。如果11.2.0.4中转实例使用ASM。那么ASM版本也必须是11.2.0.4，否则报错ORA-15295
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
1.11. 如果启用block change tracking功能
如果源库是11g，延时段特性需要先禁用alter system set deferred_segment_creation=false sid='*' scope=spfile;
不然xtts不会将空表导入目标库
SQL> select status from v$block_change_tracking;
STATUS
----------
DISABLED
SQL> alter database enable block change tracking using file'/home/oracle/xtts/track.log';
Database altered.
SQL> select status from v$block_change_tracking;
STATUS
----------
ENABLED
1.12. 检查目标端的db_files参数
在元数据导入阶段，如果目标端的db_files参数小于源端的db_files参数，会导致元数据导入出错，所以要确保目标端参数比源端大于或者等于。
Show parameter db_files
1.13. 确认生产库的recyclebin功能是关闭，并手工清空回收站
show parameter recyclebin;
NAME TYPE VALUE
----------- ---------------------- --------
recyclebin string on(这里应该为off)
关闭回收站
alter system set recyclebin=off scope=spfile;
1.14. 数据文件状态检查
--正常返回应为：ONLINE、SYSTEM
Select distinct status from v$datafile;
1.15 源数据库必须处于ARCHIVELOG模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 73
Next log sequence to archive 82
Current log sequence 82
1.16 RMAN的默认设备类型应配置为DISK
show default device type;
1.17 当前版本不支持Windows
1.18 源数据库必须运行10.2.0.3或更高版本。
1.19 源端保留用户信息和权限
--源端保留用户信息和权限
spool create_user_LUOKLE.sql
select 'create user '||username||' identified by values '||''''||password||''''||';' from dba_users where default_tablespace in('XTTS');
spool off
--角色权限的语句：
spool grant_role_priv_LUOKLE.sql
select 'grant '||GRANTED_ROLE||' to '||grantee||';' from dba_role_privs where grantee in(select username from dba_users where default_tablespace in('XTTS'));
spool off
--sys权限的赋权语句：
spool grant_sys_priv_LUOKLE.sql
select 'grant '||privilege||' to '||grantee||';' from dba_sys_privs where grantee in(select username from dba_users where default_tablespace in('XTTS'));
spool off
--对表空间的配额权限语句：
spool unlimited_tablespace_LUOKLE.sql
select 'alter user '||username||' quota unlimited on XTTS;' from dba_users where default_tablespace in('XTTS');
spool off
--若后期存在用户与其他非本用户的对象权限问题，如Schema A对Schema B上表的访问和操作等权限，可以使用以下语句在源库检索出权限，并在目标端数据库进行赋权即可：
set line 200
set pages 0
spool grant_tab_priv.sql
select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||
' to ' || grantee || ';'
from dba_tab_privs
where owner in
(select username from dba_users where default_tablespace in ('XTTS'))
or grantee in
(select username from dba_users where default_tablespace in ('XTTS'))
and privilege in ('SELECT', 'DELETE', 'UPDATE', 'INSERT')
and grantable = 'NO'
union
select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||
' to ' || grantee || ' with grant option;'
from dba_tab_privs
where owner in
(select username from dba_users where default_tablespace in ('XTTS'))
or grantee in
(select username from dba_users where default_tablespace in ('XTTS'))
and privilege in ('SELECT', 'DELETE', 'UPDATE', 'INSERT')
and grantable = 'YES';
spool off
二. XTTS操作步骤
迁移步骤大致如下：
1. 初始化阶段，配置参数文件
2. 开启块追踪特性
3. 准备阶段，进行数据全量同步
4. 增量阶段，进行数据文件的增量同步
5. 在停机同步前，在目标端新建owner用户，临时表
6. 传输阶段，最后一次增量同步，并导入元数据
7. 将原库中的其余用户导入到目标端中
8. 校验数据文件，检查有无坏块
三. XTTS之RMAN方式迁移
注意：
(1). 使用rman方式有一个严重的限制：同一批次的表空间传输到目标端后，都在同一个目录下。即目标端的最终存放数据文件的目录大小要足够存放一个批次中所有的表空间数据文件。
(2). 源端，目标端需要挂载存储用于存放所有数据文件的镜像文件，建议使用ntp的方式将存储远程从源端挂载到目标端，减少备份传送时间。
3.1. 初始化阶段，配置参数文件
3.1.1. 创建dest端到source端的dblink
--该步骤创立的dblink用于在传输阶段的元数据导入时使用（目标库操作）
--tnsnames.ora
xttslink=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
--测试
tnsping xttslink
--创建公共link
create public database link testlink connect to system identified by oracle using 'xttslink';
select count(*) from test.TEST_XTTS@testlink;
3.1.2. source端和dest端都创建目录用于存放xtts脚本
[oracle@slient xtt]$ pwd
/home/oracle/xtt
[oracle@slient xtt]$ ls
rman_xttconvert_v3.zip
[oracle@slient xtt]$ unzip rman_xttconvert_v3.zip
Archive: rman_xttconvert_v3.zip
inflating: xtt.properties
inflating: xttcnvrtbkupdest.sql
inflating: xttdbopen.sql
inflating: xttdriver.pl
inflating: xttprep.tmpl
extracting: xttstartupnomount.sql
[oracle@slient xtt]$
3.1.3. 开启块追踪特性
前面已经开启
3.2. 准备阶段，进行数据全量同步
3.2.1. 修改参数
--源端平台id查询
select * from v$transportable_platform;
SQL> select * from v$transportable_platform;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little
20 rows selected.
--修改xtts配置文件属性参数
[oracle@slient xtt]$ cat xtt.properties
tablespaces=XTTS
platformid=13
dfcopydir=/home/oracle/xtt2/dfcopydir
backupformat=/home/oracle/xtt2/backup
stageondest=/home/oracle/xtt2/stageondest
storageondest=/home/oracle/xtt2/storageondest
backupondest=/home/oracle/xtt2/backupondest
cnvinst_sid=DBdb --dest:ORACLE_SID
parallel=2
rollparallel=2
getfileparallel=2
[oracle@slient xtt]$
--根据配置文件创建目录
--源库
mkdir -p /home/oracle/xtt2/dfcopydir
mkdir -p /home/oracle/xtt2/backup
--目标库
mkdir -p /home/oracle/xtt2/stageondest
mkdir -p /home/oracle/xtt2/storageondest
mkdir -p /home/oracle/xtt2/backupondest
3.2.2. 将xtt目录(即xtts配置信息）copy传送到目标端
scp -r /home/oracle/xtt 192.168.56.12:/home/oracle
[oracle@slient xtt]$ scp -r /home/oracle/xtt 192.168.56.12:/home/oracle/
oracle@192.168.56.12's password:
xttcnvrtbkupdest.sql 100% 1390 1.4KB/s 00:00
rman_xttconvert_v3.zip 100% 33KB 33.2KB/s 00:00
xttstartupnomount.sql 100% 52 0.1KB/s 00:00
xttdriver.pl 100% 136KB 136.1KB/s 00:00
xttprep.tmpl 100% 11KB 11.4KB/s 00:00
xttdbopen.sql 100% 71 0.1KB/s 00:00
xtt.properties 100% 320 0.3KB/s 00:00
[oracle@slient xtt]$
3.2.3. 设置TMPDIR
In the shell environment on both source and destination systems, set environment variable TMPDIR to the location where the supporting scripts exist. Use this shell to run the Perl script xttdriver.pl as shown in the steps below. If TMPDIR is not set, output files are created in and input files are expected to be in /tmp.
[oracle@slient xtt]$ export TMPDIR=/home/oracle/xtt
[oracle@wang xtt]$ export TMPDIR=/home/oracle/xtt
3.2.4. 源端prepare
perl /home/oracle/xtt/xttdriver.pl -p
初始化之后产生xttplan.txt rmanconvert.cmd,xttplan.txt 记录了当前SCN,也就是下次需要增量的开始SCN; rmanconvert.cmd 记录了文件转换的名字.
[oracle@slient xtt]$ perl /home/oracle/xtt/xttdriver.pl -p
============================================================
trace file is /home/oracle/xtt/prepare_Oct18_Thu_10_37_19_11//Oct18_Thu_10_37_19_11_.log
=============================================================
.....................................................
[oracle@slient xtt]$
[oracle@slient xtt]$ pwd
/home/oracle/xtt
[oracle@slient xtt]$ cat xttplan.txt
XTTS::::21769193
8
[oracle@slient xtt]$
[oracle@slient xtt]$ cat rmanconvert.cmd
host 'echo ts::XTTS';
convert from platform 'Linux x86 64-bit'
datafile
'/home/oracle/xtt2/stageondest/XTTS_8.tf'
format '/home/oracle/xtt2/storageondest/%N_%f.dbf'
parallelism 2;
[oracle@slient xtt]$
3.2.5. 源端生成的数据文件copy到目标端
scp /home/oracle/xtt2/dfcopydir/XTTS_8.tf 192.168.56.12:/home/oracle/xtt2/stageondest
[oracle@slient xtt]$ cd /home/oracle/xtt2/dfcopydir
[oracle@slient dfcopydir]$ ls -lrt
total 1048588
-rw-r----- 1 oracle oinstall 1073750016 Oct 18 10:38 XTTS_8.tf
[oracle@slient dfcopydir]$
[oracle@slient dfcopydir]$ scp /home/oracle/xtt2/dfcopydir/XTTS_8.tf 192.168.56.12:/home/oracle/xtt2/stageondest
oracle@192.168.56.12's password:
XTTS_8.tf 100% 1024MB 29.3MB/s 00:35
[oracle@slient dfcopydir]$
3.2.6. 转换数据文件copy的endian模式
--将源库的rmanconvert.cmd传到目标库
scp rmanconvert.cmd 192.168.56.12:/home/oracle/xtt
[oracle@slient xtt]$ scp rmanconvert.cmd 192.168.56.12:/home/oracle/xtt
oracle@192.168.56.12's password:
rmanconvert.cmd 100% 196 0.2KB/s 00:00
[oracle@slient xtt]$
--在目标库执行转换操作
export TMPDIR=/home/oracle/xtt
perl xttdriver.pl -c
[oracle@wang xtt]$ ls -lrt
total 208
-rw-r--r-- 1 oracle oinstall 1390 Jul 29 18:57 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 33949 Jul 29 18:57 rman_xttconvert_v3.zip
-rw-r--r-- 1 oracle oinstall 52 Jul 29 18:57 xttstartupnomount.sql
-rw-r--r-- 1 oracle oinstall 139331 Jul 29 18:57 xttdriver.pl
-rw-r--r-- 1 oracle oinstall 11710 Jul 29 18:57 xttprep.tmpl
-rw-r--r-- 1 oracle oinstall 71 Jul 29 18:57 xttdbopen.sql
-rw-r--r-- 1 oracle oinstall 320 Jul 29 18:57 xtt.properties
-rw-r--r-- 1 oracle oinstall 196 Jul 29 19:00 rmanconvert.cmd
[oracle@wang xtt]$
[oracle@wang xtt]$ export TMPDIR=/home/oracle/xtt
[oracle@wang xtt]$ perl xttdriver.pl -c
============================================================
trace file is /home/oracle/xtt/convert_Jul29_Sun_19_01_13_48//Jul29_Sun_19_01_13_48_.log
=============================================================
........................................
--------------------------------------------------------------------
Performing convert
--------------------------------------------------------------------
[oracle@wang xtt]$
--转换成功之后会生成xttnewdatafiles.txt
整个过程，存放在stageondest的copy，将会转换写入到storageondest参数设置的最终存放点。转换完成后，copy就可以删除了。
[oracle@wang xtt]$ ls -lrt
total 212
-rw-r--r-- 1 oracle oinstall 1390 Jul 29 18:57 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 33949 Jul 29 18:57 rman_xttconvert_v3.zip
-rw-r--r-- 1 oracle oinstall 52 Jul 29 18:57 xttstartupnomount.sql
-rw-r--r-- 1 oracle oinstall 139331 Jul 29 18:57 xttdriver.pl
-rw-r--r-- 1 oracle oinstall 11710 Jul 29 18:57 xttprep.tmpl
-rw-r--r-- 1 oracle oinstall 71 Jul 29 18:57 xttdbopen.sql
-rw-r--r-- 1 oracle oinstall 320 Jul 29 18:57 xtt.properties
-rw-r--r-- 1 oracle oinstall 196 Jul 29 19:00 rmanconvert.cmd
-rw-r--r-- 1 oracle oinstall 52 Jul 29 19:02 xttnewdatafiles.txt
drwxr-xr-x 2 oracle oinstall 65 Jul 29 19:02 convert_Jul29_Sun_19_01_13_48
[oracle@wang storageondest]$ cd /home/oracle/xtt2/stageondest/
[oracle@wang stageondest]$ ls
XTTS_8.tf
[oracle@wang stageondest]$ cd /home/oracle/xtt2/storageondest/
[oracle@wang storageondest]$ ls
XTTS_8.dbf
[oracle@wang storageondest]$
3.3. 增量阶段，进行数据文件的增量同步
3.3.1. 源端创建增量备份
--第一次增量备份之后产生的配置文件为tsbkupmap.txt/incrbackups.txt，这两个为增量与数据文件对应关系配置,在做增量恢复时候需要用到。增量备份文件放在了backupformat指定位置.
perl xttdriver.pl -i
[oracle@slient xtt]$ perl xttdriver.pl -i
============================================================
trace file is /home/oracle/xtt/incremental_Oct18_Thu_11_34_55_502//Oct18_Thu_11_34_55_502_.log
=============================================================
............................................
--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------
[oracle@slient xtt]$
[oracle@slient xtt]$ ls -lrt
total 236
-rw-r--r-- 1 oracle oinstall 1390 May 24 2017 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 52 May 24 2017 xttstartupnomount.sql
-rw-r--r-- 1 oracle oinstall 11710 May 24 2017 xttprep.tmpl
-rw-r--r-- 1 oracle oinstall 139331 May 24 2017 xttdriver.pl
-rw-r--r-- 1 oracle oinstall 71 May 24 2017 xttdbopen.sql
-rw-r--r-- 1 oracle oinstall 33949 Oct 16 21:12 rman_xttconvert_v3.zip
-rw-r--r-- 1 oracle oinstall 320 Oct 18 11:13 xtt.properties
-rw-r--r-- 1 oracle oinstall 19 Oct 18 11:16 xttplan.txt
-rw-r--r-- 1 oracle oinstall 196 Oct 18 11:16 rmanconvert.cmd
-rw-r--r-- 1 oracle oinstall 52 Oct 18 11:17 xttnewdatafiles.txt
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 11:17 prepare_Oct18_Thu_11_16_54_355
-rw-r--r-- 1 oracle oinstall 21 Oct 18 11:34 xttplan.txt.new
-rw-r--r-- 1 oracle oinstall 25 Oct 18 11:34 tsbkupmap.txt
-rw-r--r-- 1 oracle oinstall 38 Oct 18 11:34 incrbackups.txt
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 11:34 incremental_Oct18_Thu_11_34_55_502
[oracle@slient xtt]$ cat tsbkupmap.txt
XTTS::8:::1=03tfvgn1_1_1
[oracle@slient xtt]$
[oracle@slient xtt]$ cat incrbackups.txt
/home/oracle/xtt2/backup/03tfvgn1_1_1
[oracle@slient xtt]$
[oracle@slient xtt]$ cd /home/oracle/xtt2/backup/
[oracle@slient backup]$ ls
03tfvgn1_1_1
[oracle@slient backup]$
3.3.2. 传输增量备份到目标端
scp `cat incrbackups.txt` 192.168.56.12:/home/oracle/xtt2/stageondest
--源库
[oracle@slient xtt]$ scp `cat incrbackups.txt` 192.168.56.12:/home/oracle/xtt2/stageondest
oracle@192.168.56.12's password:
03tfvgn1_1_1 100% 40KB 40.0KB/s 00:00
[oracle@slient xtt]$
--目标库
[oracle@wang stageondest]$ cd /home/oracle/xtt2/stageondest
[oracle@wang stageondest]$ ls
03tfvgn1_1_1 XTTS_8.tf
[oracle@wang stageondest]$
3.3.3. 目标端应用增量备份
--源库操作：xttplan.txt,在做完增量之后会更新，xttplan.txt文件记录了增量SCN起始位置，tsbkupmap.txt和incrbackups.txt，这两个为增量与数据文件对应关系配置，在做增量恢复时候需要用到。
scp xttplan.txt tsbkupmap.txt 192.168.56.12:/home/oracle/xtt
[oracle@slient xtt]$ scp xttplan.txt tsbkupmap.txt 192.168.56.12:/home/oracle/xtt
oracle@192.168.56.12's password:
xttplan.txt 100% 19 0.0KB/s 00:00
tsbkupmap.txt 100% 25 0.0KB/s 00:00
[oracle@slient xtt]$
--目标库操作：增量恢复前需要检查xttnewdatafiles.txt（数据文件在ASM中MAP关系表）、tsbkupmap.txt和incrbackups.txt（增量与数据文件对应关系配置）、xttplan.txt（下次需要增量的开始SCN）这些配置文件是否存在,如不存在会出现报错。
perl xttdriver.pl -r
[oracle@wang xtt]$ ls -lrt
total 220
-rw-r--r-- 1 oracle oinstall 1390 Jul 29 18:57 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 33949 Jul 29 18:57 rman_xttconvert_v3.zip
-rw-r--r-- 1 oracle oinstall 52 Jul 29 18:57 xttstartupnomount.sql
-rw-r--r-- 1 oracle oinstall 139331 Jul 29 18:57 xttdriver.pl
-rw-r--r-- 1 oracle oinstall 11710 Jul 29 18:57 xttprep.tmpl
-rw-r--r-- 1 oracle oinstall 71 Jul 29 18:57 xttdbopen.sql
-rw-r--r-- 1 oracle oinstall 320 Jul 29 18:57 xtt.properties
-rw-r--r-- 1 oracle oinstall 196 Jul 29 19:00 rmanconvert.cmd
-rw-r--r-- 1 oracle oinstall 52 Jul 29 19:02 xttnewdatafiles.txt
drwxr-xr-x 2 oracle oinstall 65 Jul 29 19:02 convert_Jul29_Sun_19_01_13_48
-rw-r--r-- 1 oracle oinstall 19 Jul 29 19:19 xttplan.txt
-rw-r--r-- 1 oracle oinstall 25 Jul 29 19:19 tsbkupmap.txt
[oracle@wang xtt]$
[oracle@wang xtt]$ cat xttplan.txt
XTTS::::21769193
8
[oracle@wang xtt]$ cat xttnewdatafiles.txt
::XTTS
8,/home/oracle/xtt2/storageondest/XTTS_8.dbf
[oracle@wang xtt]$
[oracle@wang xtt]$ cat tsbkupmap.txt
XTTS::8:::1=03tfvgn1_1_1
[oracle@wang xtt]$
[oracle@wang xtt]$ perl xttdriver.pl -r
============================================================
trace file is /home/oracle/xtt/rollforward_Jul29_Sun_19_21_37_238//Jul29_Sun_19_21_37_238_.log
=============================================================
........................................................
--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------
[oracle@wang xtt]$
3.3.4. 源端为之后的增量备份确定新的scn
--该步骤会将-i时生成的xttplan.txt.new改名为xttplan.txt,并将原来的xttplan.txt备份
perl xttdriver.pl -s
--源库对表做插入操作
SQL> conn test/test;
Connected.
SQL> select * from tab;
SQL> insert into test_xtts values(4000001,sysdate,'wang',sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from TEST_XTTS;
COUNT(*)
----------
4000001
--源库执行确定新的scn
[oracle@slient xtt]$ perl xttdriver.pl -s
============================================================
trace file is /home/oracle/xtt/determinescn_Oct18_Thu_11_58_13_235//Oct18_Thu_11_58_13_235_.log
=============================================================
..................................................
Prepare newscn for Tablespaces: 'XTTS'
New /home/oracle/xtt/xttplan.txt with FROM SCN's generated
[oracle@slient xtt]$
[oracle@slient xtt]$ cat xttplan.txt
XTTS::::21771098
8
[oracle@slient xtt]$
3.4. 停机同步前，在目标端新建owner用户
3.4.1. 创建临时表空间
--依照原库上的临时表空间大小，在目标端创建同名临时表空间
SQL> create temporary tablespace temp02 tempfile '/u01/app/oracle/oradata/DBdb/temp02' size 512m;
Tablespace created.
3.4.2. 创建owner用户
--在源端执行下列语句(user$)
select 'create user '||name||' identified by values '''||password||''' default tablespace users temporary tablespace temp;' from user$ where name in ('TEST');
--将生成的语句，在目标端执行，创建用户
SQL> create user TEST identified by values '7A0F2B316C212D67' default tablespace users temporary tablespace temp;
User created.
3.5. 传输阶段，最后一次增量同步，并导入元数据
3.5.1. 将原库表空间置为只读状态
alter tablespace xtts read only;
SQL> alter tablespace xtts read only;
Tablespace altered.
3.5.2. 开始最后做增量备份
重复步骤3.3.1-3.3.3，做最后的增量恢复
--源端创建增量备份
export TMPDIR=/home/oracle/xtt
perl xttdriver.pl -i
[oracle@slient xtt]$ export TMPDIR=/home/oracle/xtt
[oracle@slient xtt]$ perl xttdriver.pl -i
============================================================
trace file is /home/oracle/xtt/incremental_Oct18_Thu_14_43_31_140//Oct18_Thu_14_43_31_140_.log
=============================================================
..........................
--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------
[oracle@slient xtt]$ ls -lrt
total 248
-rw-r--r-- 1 oracle oinstall 1390 May 24 2017 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 52 May 24 2017 xttstartupnomount.sql
-rw-r--r-- 1 oracle oinstall 11710 May 24 2017 xttprep.tmpl
-rw-r--r-- 1 oracle oinstall 139331 May 24 2017 xttdriver.pl
-rw-r--r-- 1 oracle oinstall 71 May 24 2017 xttdbopen.sql
-rw-r--r-- 1 oracle oinstall 33949 Oct 16 21:12 rman_xttconvert_v3.zip
-rw-r--r-- 1 oracle oinstall 320 Oct 18 11:13 xtt.properties
-rw-r--r-- 1 oracle oinstall 196 Oct 18 11:16 rmanconvert.cmd
-rw-r--r-- 1 oracle oinstall 52 Oct 18 11:17 xttnewdatafiles.txt
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 11:17 prepare_Oct18_Thu_11_16_54_355
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 11:34 incremental_Oct18_Thu_11_34_55_502
-rw-r--r-- 1 oracle oinstall 21 Oct 18 11:58 xttplan.txt
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 11:58 determinescn_Oct18_Thu_11_58_13_235
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 14:42 Oct18_Thu_14_42_37_778
-rw-r--r-- 1 oracle oinstall 21 Oct 18 14:43 xttplan.txt.new
-rw-r--r-- 1 oracle oinstall 25 Oct 18 14:43 tsbkupmap.txt
-rw-r--r-- 1 oracle oinstall 38 Oct 18 14:43 incrbackups.txt
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 14:43 incremental_Oct18_Thu_14_43_31_140
[oracle@slient xtt]$ cat xttplan.txt
XTTS::::21771098
8
[oracle@slient xtt]$ cat xttplan.txt.new
XTTS::::21780327
8
[oracle@slient xtt]$
[oracle@slient xtt]$ cat tsbkupmap.txt
XTTS::8:::1=04tfvrok_1_1
[oracle@slient xtt]$ cat incrbackups.txt
/home/oracle/xtt2/backup/04tfvrok_1_1
[oracle@slient xtt]$
--传输增量备份到目标端
scp `cat incrbackups.txt` 192.168.56.12:/home/oracle/xtt2/stageondest
[oracle@slient xtt]$ scp `cat incrbackups.txt` 192.168.56.12:/home/oracle/xtt2/stageondest
oracle@192.168.56.12's password:
05tfvshp_1_1 100% 64KB 64.0KB/s 00:00
[oracle@slient xtt]$
--目标端应用增量备份
--源库操作：xttplan.txt,在做完增量之后会更新，xttplan.txt文件记录了增量SCN起始位置，tsbkupmap.txt和incrbackups.txt，这两个为增量与数据文件对应关系配置，在做增量恢复时候需要用到
scp xttplan.txt tsbkupmap.txt 192.168.56.12:/home/oracle/xtt
[oracle@slient xtt]$ scp xttplan.txt tsbkupmap.txt 192.168.56.12:/home/oracle/xtt
oracle@192.168.56.12's password:
xttplan.txt 100% 21 0.0KB/s 00:00
tsbkupmap.txt 100% 25 0.0KB/s 00:00
[oracle@slient xtt]$
--目标库操作：增量恢复前需要检查xttnewdatafiles.txt（数据文件在ASM中MAP关系表）、tsbkupmap.txt和incrbackups.txt（增量与数据文件对应关系配置）、xttplan.txt（下次需要增量的开始SCN）这些配置文件是否存在,如不存在会出现报错
--scp xttplan.txt tsbkupmap.txt之前查看目标库
[oracle@wang xtt]$ cat xttplan.txt
XTTS::::21769193
8
[oracle@wang xtt]$
--scp xttplan.txt tsbkupmap.txt之后查看目标库
[oracle@wang xtt]$ cat xttplan.txt
XTTS::::21771098
8
[oracle@wang xtt]$ cat tsbkupmap.txt
XTTS::8:::1=05tfvshp_1_1
[oracle@wang xtt]$
--目标库实施最后的增量应用
export TMPDIR=/home/oracle/xtt
perl xttdriver.pl -r -d
[oracle@wang xtt]$ export TMPDIR=/home/oracle/xtt
[oracle@wang xtt]$ perl xttdriver.pl -r -d
============================================================
trace file is /home/oracle/xtt/rollforward_Jul29_Sun_22_36_34_381//Jul29_Sun_22_36_34_381_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
Key: backupondest
Values: /home/oracle/xtt2/backupondest
Key: platformid
Values: 13
Key: backupformat
Values: /home/oracle/xtt2/backup
Key: parallel
Values: 2
Key: storageondest
Values: /home/oracle/xtt2/storageondest
Key: dfcopydir
Values: /home/oracle/xtt2/dfcopydir
Key: cnvinst_sid
Values: DBdb --dest:ORACLE_SID
Key: rollparallel
Values: 2
Key: stageondest
Values: /home/oracle/xtt2/stageondest
Key: tablespaces
Values: XTTS
Key: getfileparallel
Values: 2
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat
ARGUMENT stageondest
ARGUMENT backupondest
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
ORACLE_SID : DBdb
ORACLE_HOME : /u01/app/oracle/product/11.2.0/db_1
--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
rdfno 8
BEFORE ROLLPLAN
datafile number : 8
datafile name : /home/oracle/xtt2/storageondest/XTTS_8.dbf
AFTER ROLLPLAN
CONVERTED BACKUP PIECE/home/oracle/xtt2/backupondest/xib_05tfvshp_1_1_8
PL/SQL procedure successfully completed.
Entering RollForward
After applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece
PL/SQL procedure successfully completed.
--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------
[oracle@wang xtt]$
3.5.3. 自定义profile处理
--执行源端保留的profile信息
set long 1000000
SET PAGESIZE 3000
set lines 200
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
set echo on
set timing off
set wrap On
SET LONGCHUNKSIZE 400
spool create_profile.sql
with profile as(
select distinct profile from dba_profiles where profile in(select PROFILE from dba_users where username='TEST'))
select dbms_metadata.get_ddl('PROFILE','DEFAULT') from profile;
不用做此步骤
3.5.6. 目标端导入元数据
3.5.6.1. 一次元数据导入
perl xttdriver.pl -e
这个步骤会产生一个导入样本脚本xttplugin.txt，需要修改如下：
在一次元数据导入阶段，不能开启并行，统计信息导入阶段，可以将会话杀除终止
------------------
cat xttplugin.txt
impdp directory=DATA_PUMP_DIR logfile=tts_imp.log \
network_link=testlink transport_full_check=no \
transport_tablespaces=XTTS \
transport_datafiles='/home/oracle/xtt2/storageondest/XTTS_8.dbf' exclude=statistics
将统计信息排除，后面开启并行收集统计信息
------------------
[oracle@wang xtt]$ perl xttdriver.pl -e
============================================================
trace file is /home/oracle/xtt/generate_Jul29_Sun_22_58_19_528//Jul29_Sun_22_58_19_528_.log
=============================================================
...........................
--------------------------------------------------------------------
Done generating plugin file /home/oracle/xtt/xttplugin.txt
--------------------------------------------------------------------
[oracle@wang xtt]$
[oracle@wang xtt]$ cat xttplugin.txt
impdp directory=DATA_PUMP_DIR logfile=tts_imp.log \
network_link=testlink transport_full_check=no \
transport_tablespaces=XTTS \
transport_datafiles='/home/oracle/xtt2/storageondest/XTTS_8.dbf' exclude=statistics
[oracle@wang xtt]$
--经过查询目录对象DATA_PUMP_DIR已经存在，默认值。
SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ---------------------------------------------------------------------------
SYS DATA_PUMP_DIR /u01/app/oracle/admin/DBdb/dpdump/
--执行
[oracle@wang xtt]$ sh xttplugin.txt
Import: Release 11.2.0.4.0 - Production on Sun Jul 29 23:25:26 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=DATA_PUMP_DIR logfile=tts_imp.log network_link=testlink transport_full_check=no transport_tablespaces=XTTS transport_datafiles=/home/oracle/xtt2/storageondest/XTTS_8.dbf exclude=statistics
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/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sun Jul 29 23:26:14 2018 elapsed 0 00:00:39
[oracle@wang xtt]$
3.5.6.2. 二次元数据导入
将第一次没有导入的过程，视图，包，触发器导入
impdp directory=DATA_PUMP_DIR logfile=tts_imp_2.log network_link=testlink schemas= 'TEST' content=metadata_only exclude=index,table,constraint,statistics
[oracle@wang xtt]$ impdp directory=DATA_PUMP_DIR logfile=tts_imp_2.log network_link=testlink schemas= 'TEST' content=metadata_only exclude=index,table,constraint,statistics
Import: Release 11.2.0.4.0 - Production on Sun Jul 29 23:29:36 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: SYSTEM
Password:
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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": SYSTEM/******** directory=DATA_PUMP_DIR logfile=tts_imp_2.log network_link=testlink schemas= content=metadata_only exclude=index,table,constraint,statistics
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Sun Jul 29 23:29:53 2018 elapsed 0 00:00:09
[oracle@wang xtt]$
3.5.6.3. 重新在目标端搜集对象统计信息
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TEST',ESTIMATE_PERCENT=>100,method_opt=>'for all columns size 1',cascade=>true,force=>true,degree=>2);
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TEST',ESTIMATE_PERCENT=>100,method_opt=>'for all columns size 1',cascade=>true,force=>true,degree=>2);
PL/SQL procedure successfully completed.
3.6. 将原库中的其余用户导入到目标端中，权限处理
使用如下脚本重新赋权
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET TRIMSPOOL ON
set long 999999
SET PAGESIZE 1000
spool grant_role_priv.sql
select 'grant '||GRANTED_ROLE||' to '||grantee||';' from dba_role_privs where grantee in('TEST') and admin_option='NO'
union
select 'grant '||GRANTED_ROLE||' to '||grantee||' with admin option;' from dba_role_privs where grantee in('TEST') and admin_option='YES';
spool off
===============
--目标库执行
SQL> grant CONNECT to TEST;
grant RESOURCE to TEST;
Grant succeeded.
===============
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999
SET TRIMSPOOL ON
SET PAGESIZE 1000
spool grant_sys_priv.sql
select 'grant '||privilege||' to '||grantee||';' from dba_sys_privs where grantee in('TEST') and admin_option='NO'
union
select 'grant '||privilege||' to '||grantee||' with admin option;' from dba_sys_privs where grantee in('TEST')and admin_option='YES';
spool off
===============
--目标库执行
SQL> grant UNLIMITED TABLESPACE to TEST;
Grant succeeded.
===============
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET TRIMSPOOL ON
set long 999999
SET PAGESIZE 1000
spool grant_tab_privs.sql
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';' from dba_tab_privs where grantee in('TEST') and grantable='NO'
union
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ' with grant option;' from dba_tab_privs where grantee in('TEST') and grantable='YES';
spool off
3.7. 更改用户（test)默认的永久表空间、临时表空间，比对源库
--源库执行
select 'alter user test default tablespace '||DEFAULT_TABLESPACE||' temporary tablespace '||TEMPORARY_TABLESPACE||';' from dba_users where USERNAME='TEST';
--根据上述查询出来的sql在目标库执行
SQL> alter user test default tablespace XTTS temporary tablespace TEMP02;
User altered.
3.8. 目标库校验数据文件，检查有无坏块
RMAN> validate tablespace xtts check logical;
编译无效对象，进行数据对比
[oracle@wang xtt]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jul 29 23:42:05 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBDB (DBID=3282897732)
RMAN> validate tablespace xtts check logical;
Starting validate at 29-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/home/oracle/xtt2/storageondest/XTTS_8.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:08
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 39556 131072 21772837
File Name: /home/oracle/xtt2/storageondest/XTTS_8.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 71297
Index 0 19423
Other 0 796
Finished validate at 29-JUL-18
3.9. 将目标库表空间xtts 置为读写状态
alter tablespace xtts read write;
SQL> alter tablespace xtts read write;
Tablespace altered.
3.10 目标库查询验证
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TEST';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
---------------- ------------------------------ ------------------------------
TEST XTTS TEMP02
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,NUM_ROWS,BLOCKS from dba_tables where table_name='TEST_XTTS';
OWNER TABLE_NAME TABLESPACE_NAME STATUS NUM_ROWS BLOCKS
------------ ---------------- -------------------- -------- ---------- ----------
TEST TEST_XTTS XTTS VALID 4000001 71297
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='TEST_XTTS';
OWNER SEGMENT_NAME TABLESPACE_NAME SIZE_M
-------------- ------------------------- -------------------- ----------
TEST TEST_XTTS XTTS 560
SQL> select * from test.TEST_XTTS where ID=4000001;
ID CREATE_DATE NAME UP_DATE
---------- ------------ --------------------- ------------
4000001 18-OCT-18 wang 18-OCT-18
SQL> col TABLESPACE_NAME for a20
SQL> col file_name for a60
SQL> select s.file_id,
2 s.file_name,
3 s.tablespace_name,
4 s.bytes / 1024 / 1024 size_m,
5 s.ONLINE_STATUS,
6 d.status,
7 d.contents
8 from dba_data_files s, dba_tablespaces d
9 where s.TABLESPACE_NAME = d.TABLESPACE_NAME and s.TABLESPACE_NAME ='XTTS';
FILE_ID FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
---------- ------------------------------------------------------------ -------------------- ---------- ------- --------- ---------
6 /home/oracle/xtt2/storageondest/XTTS_8.dbf XTTS 1024 ONLINE ONLINE PERMANENT
至此，XTTS迁移完成！！！！！！！！！！！！！！！！！！！！！！！！！
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2216817/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2216817/
扫一扫
1833
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
