ITPub博客

首页 > 数据库 > Oracle > xtts迁移实践

xtts迁移实践

原创 Oracle 作者:不一样的天空w 时间:2018-10-18 16:56:52 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle修改时区
请登录后发表评论 登录
全部评论

注册时间:2016-09-22

  • 博文量
    873
  • 访问量
    738507