Oracle XTTS实施前的检查项
适用范围
11g，12c，19c 使用xtts技术进行迁移实施前的检查。
方案概述
XTTS (Cross Platform Transportable Tablespaces) 跨平台迁移表空间，是 Oracle 自10g 推出的一个用来移动单个表空间数据以及创建一个完整的数据库从一个平台移动到另一个平台的迁移备份方法。本文通过总结XTTS技术实施前的各项检查确保迁移顺利实施。
实施步骤
1、检查数据库补丁
oracle$ cd $ORACLE_HOME/OPatch
[oracle]$ ./opatch lspatches
SQL> col action_time for a32
SQL> col action for a10
SQL> col namespace for a10
SQL> col version for a10
SQL> col BUNDLE_SERIES for a10
SQL> col comments for a30
SQL> select * from dba_registry_history;
2、组件检查
确保源和目标组件一致，组件状态正常
SQL> SET lines 90 NUMWIDTH 12 PAGES 10000 LONG 2000000000
SQL> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
SQL> COL version FORMAT a12
SQL> COL comp_id FORMAT a8
SQL> COL schema LIKE version
SQL> COL comp_name FORMAT a35
SQL> COL status FORMAT a12
SQL> SELECT comp_id,schema,status,version,comp_name FROM dba_registry
ORDER BY 1;
3、检查数据库归档模式
源和目标段均要开启归档模式
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 198426
Next log sequence to archive 0
Current log sequence 198429
4、检查数据库时区
源和目标段时区要一致
源端
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
目标端
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
5、检查数据库字符集
源端和目标端字符集要一致
源端字符集
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET ZHS16GBK
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET UTF8
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_RDBMS_VERSION 11.2.0.4.0
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
目标端字符集
SQL> select * from nls_database_parameters;
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.4.0
20 rows selected.
6、检查 COMPATIBLE
检查源端compatible参数是否大于11.1.0，并且不大于等于目标端。
源端
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
compatible string 11.2.0.4.0
目标端
SQL> Show parameter COMPATIBLE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0
7、db_file参数值检查
目标端此参数值至少应大于等于源端;否则请修改至和源端相同值
-源端
SQL>show parameter db_files
NAME TYPE VALUE
---------------
db_files integer 200
-目标
show parameter db_files
NAME TYPE VALUE
-----------------
db_files integer 2000
#如果目标端此值少于源端，则进行下面命令修改
SQL>alter system set db_files=1000 scope=spfile sid='*';
SQL>shutdown immediate
SQL>startup
SQL>show parameter db_files
8、目标端废弃参数检查
如果目标端数据库存在使用被废弃的参数时，在目标库进行恢复操作时，会出现如下报错：
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
Error:
Error in executing xttstartupnomount.sq
#workaround
remote_os_authent has been deprecated
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: SEC_CASE_SENSITIVE_LOGON initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecatedError in executing xttstartupnomount.sql
因此需要在目标库检查alert日志启动信息，是否有类似如下警告信息：
WARNING： THE SEC_CASE_SENSITIVE_LOGON initialization parameter has been deprecated
如果有请将其从SPFILE参数文件中除去。
注意下面参数但不限于下面参数，则请在参数文件中除去
remote_os_authent
BACKGROUND_DUMP_DEST
SEC_CASE_SENSITIVE_LOGON
USER_DUMP_DEST
9、检查回收站
清空并关闭回收站功能，待迁移完成后开启回收站功能
SQL> select count(*) from dba_recyclebin;
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> show parameter recyclebin
NAME TYPE VALUE
recyclebin string on
SQL> alter system set recyclebin='off' scope=spfile;
System altered.
10、检查源端DG同步状态
说明：如果实施的环境是DG环境，需要检查DG状态
SQL>set pagesize 10000;
SQL>col name for a40;
SQL>set linesize 130;
SQL>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL>select NAME,THREAD#,SEQUENCE#,REGISTRAR,applied,completion_time from v$archived_log where name is not null and completion_time>sysdate-1
11、检查无效对象
SQL>select owner, object_name, object_type from dba_objects
where status !='VALID' order by owner, object_type, object_name;
12、检查无效索引
SQL>select owner, index_name, status from dba_indexes
where status='UNUSABLE' order by 1,2;
SQL>select i.owner, i.index_name, p.partition_name, p.status
from dba_ind_partitions p,dba_indexes i
where p.index_name=i.index_name and p.status='UNUSABLE'
order by 1,2,3;
SQL>select i.owner,i.index_name,s.subpartition_name,s.status from
dba_ind_subpartitions s,dba_indexes i where
s.index_name=i.index_name and s.status='UNUSABLE'
order by 1,2,3;
13、compatible Advanced Queues检查
SQL>select owner,queue_table,recipients,compatible from dba_queue_tables
where recipients='MULTIPLE' and compatible like '%8.0%';
14、基于XMLSchema的XMLType对象检查
SQL>SELECT distinct OWNER FROM DBA_XML_SCHEMAS;
SQL>select distinct p.tablespace_name
from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t
where t.table_name=x.table_name and
t.tablespace_name=p.tablespace_name and
x.owner=u.username;
15、SPATIAL空间组件对象检查
SQL>select owner,index_name from dba_indexes
where ityp_name = 'SPATIAL_INDEX';
SQL>select owner, table_name, column_name
from dba_tab_columns
where data_type = 'SDO_GEOMETRY'
and owner != 'MDSYS'
order by 1,2,3;
16、外部表检查
SQL>select distinct owner from DBA_EXTERNAL_TABLES;
17、IOT表检查
SQL>select distinct owner from dba_tables where IOT_TYPE is not null;
18、检查临时表
SQL>SELECT owner,table_name FROM DBA_TABLES WHERE
TEMPORARY='Y' AND OWNER IN(需要迁移用户列表);
19、物化视图检查
SQL>select owner,count(*) from dba_mviews group by owner;
20、检查是否存在应用户使用TSTZ 字段
select c.owner || '.' || c.table_name || '(' || c.column_name || ') -'
|| c.data_type || ' ' col
from dba_tab_cols c, dba_objects o
where c.data_type like '%WITH TIME ZONE'
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = 'TABLE'
order by col;
21、检查表空间是否加密
SQL>select tablespace_name,ENCRYPTED from dba_tablespaces;
22、检查是否存在加密字段
SQL>select * from DBA_ENCRYPTED_COLUMNS;
23、检查Opaque Types类型字段
SQL>select distinct owner ,DATA_TYPE from dba_tab_columns where
owner in (需要迁移用户列表);
24、检查表空间和数据文件状态
SQL>select tablespace_name,status from dba_tablespaces;
select STATUS,ONLINE_STATUS,count(*) from dba_data_files
group by STATUS,ONLINE_STATUS;
25、检查数据文件头信息
SQL>select STATUS,ERROR,TABLESPACE_NAME from V$DATAFILE_HEADER;
26、检查用户profile
比对新旧环境profile是否一致,提前创建目标端到源端的DBLIK XTTS_DBLINK
SQL>select distinct(t.pro) from
(select s.profile pro, l.profile pro2
from dba_profiles@XTTS_DBLINK s, dba_profiles l
where s.profile = l.profile(+)) t where t.pro2 is null
order by t.pro;
-the end-