跨平台版本迁移之 XTTS 方案操作指南（3）
承接上篇：举一反三：跨平台版本迁移之 XTTS 方案操作指南
7XTTS 迁移后检查
7.1 更改用户默认表空间
更改用户默认表空间，将用户默认表空间设置与源数据库保持一致：
@default_tablespace.sql
源端执行：
spool default_tablespace.sql
select 'alter user '||username||' default tablespace '||default_tablespace||';' from dba_users where default_tablespace in(‘DATATBS ’);
spool off
添加表空间配额权限：
@unlimited_tablespace.sql
源库：
select 'alter user '||username||' quota unlimited on '|| default_tablespace||';' from dba_users where default_tablespace in (‘DATATBS ’);
7.2 数据库对象并行重编译
exec utl_recomp.recomp_parallel(32);
set echo off feedback off timing off verify off
set pagesize 0 linesize 500 trimspool on trimout on
Set heading off;
set feedback off;
set echo off;
Set lines 999;
spool compile.sql
select 'alter '||
decode(object_type,'SYNONYM',decode(owner,'PUBLIC','PUBLIC SYNONYM '||object_name,
'SYNONYM '||OWNER||'.'||OBJECT_NAME)||' compile;',
decode(OBJECT_TYPE ,'PACKAGE BODY','PACKAGE',OBJECT_TYPE)||
' '||owner||'.'||object_name||' compile '||
decode(OBJECT_TYPE ,'PACKAGE BODY','BODY;',' ;'))
from dba_objects where status<>'VALID'
order by owner,OBJECT_NAME;
spool off
@compile.sql
正式环境没有无效对象。
7.3 数据库对象数据比对
运行数据库对比脚本，通过创建 dblink，运行相关的数据库对象比对脚本。这里我们主要比对了存储过程，函数，触发器，试图，索引，表等等。
创建到生产环境 DB LINK
CREATE DATABASE LINK TEST_COMPARE CONNECT TO SYSTEM IDENTIFIED BY password xxx USING 'xxxx:1521/xxxx';
使用如下脚本对比数据库中对象个数：
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS@TEST_COMPARE
WHERE OBJECT_NAME NOT LIKE 'BIN%'
AND OBJECT_NAME NOT LIKE 'SYS_%'
AND OWNER IN ('LUOKLE')
MINUS
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OBJECT_NAME NOT LIKE 'BIN%'
AND OBJECT_NAME NOT LIKE 'SYS_%'
AND OWNER IN ('LUOKLE');
或
源库：
select object_type,count(*) from dba_objects where owner
in (select username from 源库) group by object_type;
目标：
select object_type,count(*) from dba_objects where owner
in (select username from 目标库) group by object_type;
如果索引缺失可能是由于没有存放在传输的表空间所以需要重新创建，而缺失的表可能是临时表，需要手工创建。
使用如下脚本进行创建：
CREATE INDEX "LUOKLE"."IDX_XXX" ON "LUOKLE"."BI_XXXX" TABLESPACEDATATBS parallel 8;
Alter index "LUOKLE"."IDX_XX" noparallel;
CREATE GLOBAL TEMPORARY TABLE "LUOKLE"."TEMP_PAY_BATCH_CREATE_INSTR"
( "BATCH_ID" NUMBER,
"STATUS" CHAR(1)
) ON COMMIT PRESERVE ROWS ;
使用 hash 函数进行数据对比
两边分别创建存放 hash 数据的表
create table system.get_has_value (dbname varchar2(20),owner varchar2(30),table_name varchar2(100),value varchar2(100),error varchar2(2000));
创建需要验证的表：
create sequence system.sequence_checkout_table start with 1 increment by 1 order cycle maxvalue 10 nocache;
CREATE TABLE SYSTEM.checkout_table as select sys_context('USERENV', 'INSTANCE_NAME') dbnme,owner,table_name, system.sequence_checkout_table.NEXTVAL groupid from dba_tables where owner='LUOKLE'
结果显示：
1 SELECT owner, groupid, COUNT (*)
2 FROM SYSTEM.checkout_table
3* GROUP BY owner, groupid,dbnme Order by owner,groupid
14:05:21 SQL> SELECT owner, groupid, COUNT (*)
14:05:31 2 FROM SYSTEM.checkout_table
14:05:32 3 GROUP BY owner, groupid,dbnme Order by owner,groupid;
OWNER GROUPID COUNT(*)
------------------------------ ---------- ----------
LUOKLE 1 32
LUOKLE 2 31
LUOKLE 3 31
LUOKLE 4 31
LUOKLE 5 31
LUOKLE 6 31
LUOKLE 7 31
LUOKLE 8 31
LUOKLE 9 31
LUOKLE 10 31
创建 hash 函数
grant select on sys.dba_tab_columns to system;
CREATE OR REPLACE PROCEDURE SYSTEM.get_hv_of_data (
avc_owner VARCHAR2,
avc_table VARCHAR2)
AS
lvc_sql_text VARCHAR2 (30000);
ln_hash_value NUMBER;
lvc_error VARCHAR2 (100);
BEGIN
SELECT 'select /*+parallel(a,25)*/sum(dbms_utility.get_hash_value('
|| column_name_path
|| ',0,power(2,30)) ) from '
|| owner
|| '.'
|| table_name
|| ' a '
INTO LVC_SQL_TEXT
FROM (SELECT owner,
table_name,
column_name_path,
ROW_NUMBER ()
OVER (PARTITION BY table_name
ORDER BY table_name, curr_level DESC)
column_name_path_rank
FROM ( SELECT owner,
table_name,
column_name,
RANK,
LEVEL AS curr_level,
LTRIM (
SYS_CONNECT_BY_PATH (column_name, '||''|''||'),
'||''|''||')
column_name_path
FROM ( SELECT owner,
table_name,
'"' || column_name || '"' column_name,
ROW_NUMBER ()
OVER (PARTITION BY table_name
ORDER BY table_name, column_name)
RANK
FROM dba_tab_columns
WHERE owner = UPPER (avc_owner)
AND table_name = UPPER (avc_table)
AND DATA_TYPE IN ('TIMESTAMP(3)',
'INTERVAL DAY(3) TO SECOND(0)',
'TIMESTAMP(6)',
'NVARCHAR2',
'CHAR',
'BINARY_DOUBLE',
'NCHAR',
'DATE',
'RAW',
'TIMESTAMP(6)',
'VARCHAR2',
'NUMBER')
ORDER BY table_name, column_name)
CONNECT BY table_name = PRIOR table_name
AND RANK - 1 = PRIOR RANK))
WHERE column_name_path_rank = 1;
EXECUTE IMMEDIATE lvc_sql_text INTO ln_hash_value;
lvc_sql_text :=
'insert into system.get_has_value(owner,table_name,value) values(:x1,:x2,:x3)';
EXECUTE IMMEDIATE lvc_sql_text USING avc_owner, avc_table, ln_hash_value;
commit;
DBMS_OUTPUT.put_line (
avc_owner || '.' || avc_table || ' ' || ln_hash_value);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lvc_error := 'NO DATA FOUND';
lvc_sql_text :=
'insert into system.get_has_value(owner,table_name,error) values(:x1,:x2,:x3)';
EXECUTE IMMEDIATE lvc_sql_text USING avc_owner, avc_table, lvc_error;
commit;
WHEN OTHERS
THEN
lvc_sql_text :=
'insert into system.get_has_value(owner,table_name,value) values(:x1,:x2,:x3)';
EXECUTE IMMEDIATE lvc_sql_text USING avc_owner, avc_table, SQLERRM;
commit;
END;
/
sqlplus system/oracle<<EOF
set heading off linesize 170 pagesize 0 feedback off echo off trimout on trimspool on termout off verify off
exit;
EOF
nohup ./check_source.sh LUOKLE 1 >./source_LUOKLE_cd_1.log 2>&1 &
nohup ./check_source.sh LUOKLE 2 >./source_LUOKLE_cd_1.log 2>&1 &
nohup ./check_source.sh LUOKLE 3 >./source_LUOKLE_cd_1.log 2>&1 &
nohup ./check_source.sh LUOKLE 4 >./source_LUOKLE_cd_1.log 2>&1 &
nohup ./check_source.sh LUOKLE 5 >./source_LUOKLE_cd_1.log 2>&1 &
nohup ./check_source.sh LUOKLE 6 >./source_LUOKLE_cd_1.log 2>&1 &
nohup ./check_source.sh LUOKLE 7 >./source_LUOKLE_cd_1.log 2>&1 &
nohup ./check_source.sh LUOKLE 8 >./source_LUOKLE_cd_1.log 2>&1 &
nohup ./check_source.sh LUOKLE 9 >./source_LUOKLE_cd_1.log 2>&1 &
nohup ./check_source.sh LUOKLE 10 >./source_LUOKLE_cd_1.log 2>&1 &
checkdata_source.sh
date
sqlplus system/oracle<<EOF
set heading off linesize 170 pagesize 0 feedback off
spool source_check_$1_$2.sql
SELECT 'exec system.get_hv_of_data('''
|| owner
|| ''','''
|| table_name
|| ''')'
FROM system.checkout_table
WHERE owner = UPPER ('$1') and groupid=$2
AND table_name NOT IN (SELECT table_name
FROM dba_tables
WHERE owner = UPPER ('$1')
AND iot_type IS NOT NULL)
AND table_name IN (SELECT table_name
FROM ( SELECT table_name, COUNT (*)
FROM dba_tab_columns
WHERE owner = UPPER ('$1')
AND DATA_TYPE IN ('TIMESTAMP(3)',
'INTERVAL DAY(3) TO SECOND(0)',
'TIMESTAMP(6)',
'NVARCHAR2',
'CHAR',
'BINARY_DOUBLE',
'NCHAR',
'DATE',
'RAW',
'VARCHAR2',
'NUMBER')
GROUP BY table_name
HAVING COUNT (*) > 0))
ORDER BY table_name;
spool off
set serveroutput on
@source_check_$1_$2.sql
exit;
EOF
date
运行 hash 计算函数脚本，在LINUX环境对 LUOKLE 下所有表进行 hash 计算耗时30分钟，总共311张表，有52张表没有计算出 hash 经分析发现这些表为空表。
SQL> select count(*) from LUOKLE.XXXX;
COUNT(*)
----------
0
7.4 数据库对象间权限比对处理
对比源库和目标库数据库的对象级别间权限，如若权限不一致建议将源库跑出的 grant_tab_privs.log 到目标端执行。
复核对象上的 select 和 DML 权限赋予给用户
@grant_tab_privs.sql
源库：
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';' from dba_tab_privs where (grantee in(select username from dba_users where default_tablespace in(‘DATATBS ’)) or owner in(select username from dba_users where default_tablespace in(DATATBS ))) 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 (grantee in(select username from dba_users where default_tablespace in(DATATBS )) or owner in(select username from dba_users where default_tablespace in(DATATBS ))) and privilege in('SELECT','DELETE','UPDATE','INSERT') and grantable='YES';
7.5 收集统计信息
为了防止同时收集统计信息，造成系统资源的消耗，建议提前关闭后台自动收集统计信息的任务。
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
查看柱状图信息：
select count(*),owner,table_name,column_name from dba_tab_histograms
group by owner,table_name,column_name
having count(*) > 2;
手工运行收集脚本：
exec DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');设置并发收集模式
exec
dbms_stats.gather_database_stats(
estimate_percent =>dbms_stats.AUTO_SAMPLE_SIZE, ///// for all columns size repeat
METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',
options=> 'GATHER',degree=>8,
granularity =>’all’,
cascade=> TRUE
);
select * from dba_scheduler_jobs where schedule_type = 'IMMEDIATE' and state = 'RUNNING';
收集数据字典统计信息：
exec DBMS_STATS.GATHER_DICTIONARY_STATS(degree=>16);
固定对象的统计信息：
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
开启默认收集
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
exec DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','false');
以下为测试过程：
13:23:41 SQL> select count(*),owner,table_name,column_name from dba_tab_histograms
13:23:45 2 where owner='LUOKLE'
13:23:46 3 group by owner,table_name,column_name
13:23:46 4 having count(*) > 2;
no rows selected
Elapsed: 00:00:00.10
13:28:06 SQL> exec dbms_stats.gather_database_stats(estimate_percent =>dbms_stats.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',options=> 'GATHER',degree=>8, granularity =>'all',cascade=> TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:26:51.34
13:55:05 SQL>
全库统计信息收集耗时26分钟
13:57:14 SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS(degree=>16);
PL/SQL procedure successfully completed.
Elapsed: 00:00:18.94
7.6 修改 job 参数
show parameter job_queue_processes;
alter system set job_queue_processes=100 scope=both;
资源下载
关注公众号：数据和云（OraNews）回复关键字获取
‘2017DTC’，2017 DTC 大会 PPT
‘DBALIFE’，“DBA 的一天”海报
‘DBA04’，DBA 手记4 经典篇章电子书
‘RACV1’, RAC 系列课程视频及 PPT
‘122ARCH’，Oracle 12.2 体系结构图
‘2017OOW’，Oracle OpenWorld 资料
‘PRELECTION’，大讲堂讲师课程资料