目录
1. 数据库升级迁移概述 4
1.1. 数据库升级迁移背景 4
1.2. 基本信息 4
2. 前期数据库检查项 4
2.1. 迁移涉及到数据库用户/表清单 4
2.2. 涉及的连接ip信息 6
2.3. 涉及的连接程序信息 8
2.4. 角色与profile创建 14
2.5. 锁定账号用户对象检查 15
2.6. crontab检查 15
2.7. 数据库job检查 15
2.8. 触发器检查 16
2.9. 系统用户索引检查 16
2.10. dblink、tnsname检查 17
2.11. 表上统计信息是否锁定 18
2.12. sqlprofile 检查 18
2.13. directory 检查 18
2.14. public 对象检查 19
2.15. 失效对象检查 19
数据库升级迁移概述
数据库升级迁移背景
数据库迁移升级，版本为11.2.0.1，从单机x86平台迁移至ODA一体机RAC平台，版本为19.16 ，保障迁移后数据完整性能稳定。
基本信息
IP地址和数据库信息：
数据库检查项
迁移涉及到数据库用户/表清单
下面列出用户列表信息。
涉及的连接ip信息
下面列出状态是通过分析22年8月21到23年1月31号之间的监听日志，连接到CIF数据库的IP清单，以及每个IP的连接次数和最后一次的连接时间。
cat listener.log | awk -F'*' '{match($3,/[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/); ip = substr($3,RSTART,RLENGTH);cnt[ip]+=1;last[ip]=$1;}END {for (i in cnt) printf "%-16s %9s %19s\n",i,cnt[i],last[i];}' | sort -nk 2
涉及的连接程序信息
下面列出状态是通过近30天内的历史活动会话信息获取连接到CIF数据库的机器清单，以及每个机器的连接程序和活动的次数
select machine,program,count(*) from dba_hist_active_sess_history where machine !='cifdb03' group by machine,program order by 1,2,3
角色与profile创建
Profile创建
set long 1000000
SET PAGESIZE 3000
set lines 200
SET HEADING OFF
SET VERIFY OFF
set termout off
set echo OFF
set timing off
set wrap On
set trimspool on
SET LONGCHUNKSIZE 400
spool 1_create_profile.sql
with profile as(
select distinct profile from dba_profiles where profile!='DEFAULT')
select dbms_metadata.get_ddl('PROFILE',PROFILE)||';' from profile;
spool off
select 'alter profile default limit '||RESOURCE_NAME||' '||LIMIT||';' from dba_profiles where PROFILE='DEFAULT';
角色创建
角色role信息
set long 1000000
SET PAGESIZE 3000
set lines 200
SET HEADING OFF
SET VERIFY OFF
set termout off
set echo OFF
set timing off
set wrap On
set trimspool on
SET LONGCHUNKSIZE 400
spool 2_create_role.sql
select 'create role '||role||' ;' from dba_roles;
spool off
角色赋予的权限
set long 1000000
SET PAGESIZE 3000
set lines 200
SET HEADING OFF
SET VERIFY OFF
set termout off
set echo OFF
set timing off
set wrap On
set trimspool on
SET LONGCHUNKSIZE 400
spool 5_grant_role_priv.sql
select 'grant '||GRANTED_ROLE||' to '||grantee||';' from dba_role_privs where grantee in(select username from dba_users where ACCOUNT_STATUS NOT LIKE '%LOCKED%' and username not in ('SYS', 'SYSMAN', 'MGMT_VIEW','SYSTEM','OUTLN','DBSNMP','SPA'));
spool off
锁定账号用户对象检查
说明：检查锁定用户是否存在业务相关对象
SQL> select * from dba_tab_privs where owner in (select username from dba_users where account_status like '%LOCKED%') and grantee in (select username from dba_users where ACCOUNT_STATUS NOT LIKE '%LOCKED%' and username not in ('SYS', 'SYSMAN', 'MGMT_VIEW','SYSTEM','OUTLN','DBSNMP','SPA') );
no rows selected
不存在
crontab检查
说明：存在如下crontab，需要在目标库同步迁移
据库job检查
说明：检查相关job，确认迁移job步骤 （待补充）
select OWNER,JOB_NAME,JOB_ACTION,SCHEDULE_OWNER,enabled from dba_scheduler_jobs where enabled='TRUE';
select job,log_user,last_date,next_date,interval,what from dba_jobs where broken='N' and log_user not in ('SYS');
触发器检查
说明：检查系统用户下是否存在业务相关的触发器
select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_OWNER,TABLE_NAME,STATUS from dba_triggers where owner !=TABLE_OWNER;
说明：检查业务用户下是否存在业务相关的触发器
select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_OWNER,TABLE_NAME,STATUS from dba_triggers where owner in (select username from dba_users where ACCOUNT_STATUS NOT LIKE '%LOCKED%' and username not in ('SYS', 'SYSMAN', 'MGMT_VIEW','SYSTEM','OUTLN','DBSNMP','SPA'));
系统用户索引检查
说明：检查系统用户下是否存在业务相关的索引
select owner,INDEX_NAME,TABLE_OWNER,TABLE_NAME, TABLESPACE_NAME from dba_indexes where table_owner!=owner;
dblink、tnsname检查
DBLINK 源端需要调整的IP 列表
UAT环境上存在访问CIF数据库的dblink，升级后需要调整dblink
说明：由于ip改变，迁移后连接到该数据库的dblink需要重建，tnsname迁移
SYSTEM用户下存在dblink，需要在19C环境上重建
SQL> select owner,db_link,host from dba_db_links;
----dblink
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 9_create_dblink.sql
select DBMS_METADATA.GET_DDL('DB_LINK',DB_LINK,owner) ||';'from dba_db_links where owner in ('SYSTEM','PUBLIC');
spool off
表上统计信息是否锁定
说明：确认是否有统计信息锁定的表 ----无统计信息锁定
select distinct owner,table_name,stattype_locked from dba_tab_statistics where owner in (select username from dba_users where ACCOUNT_STATUS NOT LIKE '%LOCKED%' and username not in ('SYS', 'SYSMAN', 'MGMT_VIEW','SYSTEM','OUTLN','DBSNMP','SPA')) AND stattype_locked is not null;
no rows selected
sqlprofile 检查
说明：检查sqlprofile ，获取sql_id信息同步迁移
select name,CREATED,FORCE_MATCHING,STATUS from dba_sql_profiles;
directory 检查
说明：directory 相关目录创建
select * from dba_directories;
public 对象检查
说明：public dblink、public 同义词，需要单独创建 ----不能只检查open的账户
SQL> select object_type,owner,count(*) from dba_objects where owner='PUBLIC' group by object_type,owner order by 3;
OBJECT_TYPE OWNER COUNT(*)
------------------- ----------- ----------
SYNONYM PUBLIC 24743
-----同义词
SQL> select OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where owner='PUBLIC' and TABLE_OWNER in (select username from dba_users where ACCOUNT_STATUS NOT LIKE '%LOCKED%' and username not in ('SYS', 'SYSMAN', 'MGMT_VIEW','SYSTEM','OUTLN','DBSNMP','SPA'))
736 rows selected. 都已经创建
block size检查
说明：查看当前生产的数据块大小
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
失效对象检查
说明：查看当前生产的失效对象信息
SQL> select owner,object_type,status,count(*) from dba_objects where status='INVALID' and owner in(select username from dba_users where ACCOUNT_STATUS NOT LIKE '%LOCKED%' and username not in ('SYS', 'SYSMAN', 'MGMT_VIEW','SYSTEM','OUTLN','DBSNMP','SPA')) group by owner,object_type,status order by 1,2;
数据库组件检查
说明：查看当前生产的数据库组件
select comp_id,version,status from dba_registry;
COMP_ID VERSION STATUS
--------------- ------------------------------ ----------------------
EM 11.2.0.4.0 VALID
XDB 11.2.0.4.0 VALID
CONTEXT 11.2.0.4.0 VALID
EXF 11.2.0.4.0 VALID
RUL 11.2.0.4.0 VALID
OWM 11.2.0.4.0 VALID
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 VALID
JAVAVM 11.2.0.4.0 VALID
XML 11.2.0.4.0 VALID
CATJAVA 11.2.0.4.0 VALID
RAC 11.2.0.4.0 VALID
查看非业务用户对象权限
说明：查看非业务用户对象权限，比如sys上的对象给业务用户的赋权
生产上查询：
select 'GRANT ' || privilege|| ' ON ' ||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';' from dba_tab_privs WHERE GRANTEE IN
('EXPOPER','C06121','CIFCRS','CIFEIP','CIFEXPN','CIFFAS','CIFPUB','CLDBA','CRS','FLEET02_MV','SSIS_USER','SP_ADMIN','NEWMENU','PRLFAS','PUB','UATFAS','PCS','CIFDUN_CDC','HFM','CIFBIPS','CIFSK','CIFCHK','CIFSK2','CIFCAR','CCRS','CCRS_SOURCE','ZSEXPN','ZSCRS','ZSEIP','ZSCHK','ZS_ADMIN','ZSDBA','ZSFAS','ZSPUB','TMPACNT','CIFPAY','CIFPAYADMIN','CIFPAYTEST','TB_ZLGJ','C99579','CCRS_Q','MONITOR','CIFCONS','CCRS_SEC','CCRS_SEC_SOURCE') and OWNER not in('EXPOPER','C06121','CIFCRS','CIFEIP','CIFEXPN','CIFFAS','CIFPUB','CLDBA','CRS','FLEET02_MV','SSIS_USER','SP_ADMIN','NEWMENU','PRLFAS','PUB','UATFAS','PCS','CIFDUN_CDC','HFM','CIFBIPS','CIFSK','CIFCHK','CIFSK2','CIFCAR','CCRS','CCRS_SOURCE','ZSEXPN','ZSCRS','ZSEIP','ZSCHK','ZS_ADMIN','ZSDBA','ZSFAS','ZSPUB','TMPACNT','CIFPAY','CIFPAYADMIN','CIFPAYTEST','TB_ZLGJ','C99579','CCRS_Q','MONITOR','CIFCONS','CCRS_SEC','CCRS_SEC_SOURCE');
