Oracle 9i升级19C 逻辑迁移详细方法（一）
#0.00 目标端检查job
show parameter job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 100
alter system set job_queue_processes=0 scope=both;
#0.02 确认导出用户数
重点用户4个：
#0.03 关闭2节点，关闭归档
SYS@SP1>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@SP1>startup mount
ORACLE instance started.
Total System Global Area 6.8622E+10 bytes
Fixed Size 27791464 bytes
Variable Size 2.3757E+10 bytes
Database Buffers 4.4560E+10 bytes
Redo Buffers 276926464 bytes
Database mounted.
SYS@SP1>alter database noarchivelog;
Database altered.
SYS@SP1>alter database open;
Database altered.
SYS@SP1>
#0.04 将FWSZP的表分组存到表exp_group中
cd /u01/dumdata/oracle9iDump
sh init_group.sh > init_group.sql
sqlplus XX/XX
@init_group.sql
-- 更新中间库上的表
sqlplus XX/XX@XX:1521/XX
drop table exp_group purge;
create table exp_group as select * from kevin.exp_group@to19cdcdb;
-- 更新创建索引脚本
-----------------------正式割接：------------------------------------------------------------------------
#1.2 关闭相关job
-- 原服务器(XX)执行
执行 1-2_JobList.txt
#1.3 导入准备
-- 在目标服务器(XX)执行
执行以下脚本：
1-3_createCheckDBList.txt <<<<<<< 中间库上执行，准备对比表格
1-4_clearOldDBData.txt <<<<<<< 删除测试用户，重新创建
## 如果一直有连接，则启动数据库到 startup RESTRICT
drop user XX cascade;
grant create database link to XX;
grant create database link to XX;
-- 清理oem：
sqlplus sys/XX as sysdba
drop user XX cascade;
## 删除用户：10分钟
1-5_mountDumdata.txt <<<<<<< 挂载导出dmp目录
1-6_closeODARAC2.txt <<<<<<< 关闭集群2节点
#1.4 关闭应用
执行1-7_APClose.txt
#1.7 检查数据库环境
执行 1-8_checkDBEnv.txt，将DG备端启动到readonly
#1.9 ~ 3.3 Exp导出
执行Exp导出脚本，并行执行
172.18.2.121
cd /dmpdata/oracle9iDump
rm -rf Exp*.dmp
cd /dmpdata/logs
rm -rf *.log
cd /dmpdata/config
sh 16
#3.4 将导出磁盘挂载到ODA上
#3.6~5.0 Imp导入
并行导入数据，fwszp用户，先导入表数据，再导入其他对象
--检查用户：
--删除导入日志
cd /u01/dumdata/logs
rm -rf Imp*19C.log
rm -rf Imp*19C.sql
rm -rf Imp*19C_sys.sql
cd /u01/dumdata/config
nohup sh 03-ImpXXGP0119C.sh & --27分钟15秒
nohup sh 02-ImpXX19C.sh & --5分钟38秒
索引创建完成后，再导入metadata
--创建索引：
cd /u01/dumdata/index
rm -rf *.log
sh create_index_g01.sh ---4分钟
sh create_index_g02.sh ---3分钟
sh create_index_g03.sh ---2分钟
sh create_index_g04.sh ---2分钟
sh create_index_g05.sh ---2分钟
sh create_index_g06.sh ---2分钟
sh create_index_g07.sh
sh create_index_g08.sh
sh create_index_g09.sh
耗时：15分钟
-- 创建缺失索引 --1秒
更新 miss_index.sql
sh create_miss_index.sh
-- 创建主键约束 --52秒
更新 miss_cons.sql
sh create_miss_cons.sh
-- 以下单独执行（生成出来的脚本有错误，原因是原9i库里面dba_constraints视图里的SYS_C008891所指向的索引不对）
alter table FWSZP.FWCATNS_EXTENDDIESINVENTORY add constraint SYS_C008891 primary key (LOTOBJECT,LOTOBJECTLINE) using index enable;
-- 导入metadata
cd /u01/dumdata/config
nohup sh 01-ImpFwszpMetadata19C.sh & --1分钟
# 数据库对象比对，按schema逐个对比
sqlplus XX/XX@XX:1521/XX
select s.object_type, s.count_9i, t.count_19c from
(select object_type, count(1) count_9i from dba_objects@to9idb where owner = 'XX' group by object_type) s,
(select object_type, count(1) count_19c from dba_objects@to19cdcdb where owner = 'XX' group by object_type) t
where s.object_type = t.object_type(+)
order by s.object_type;
--查看缺少详细对象，如索引
select owner, object_name from dba_objects@to9idb where owner = 'XX' and object_type = 'INDEX'
minus
select owner, object_name from dba_objects@to19cdcdb where owner = 'XX' and object_type = 'INDEX';
-- XXP缺一个函数索引
sqlplus XX/XX@XX
## 检查主键约束
select s.owner, s.table_name, s.count_9i, t.count_19c from
(select owner, table_name, count(1) count_9i
from dba_constraints@to9idb
where owner in ('XX')
and constraint_type = 'P'
and table_name in (select tab_name from exp_group)
group by owner, table_name) s,
(select owner, table_name, count(1) count_19c
from dba_constraints@to19cdcdb
where owner in ('XX')
and constraint_type = 'P'
and table_name in (select tab_name from exp_group)
group by owner, table_name) t
where s.table_name = t.table_name(+)
and count_9i != count_19c
order by s.owner, s.table_name;
select s.owner, s.table_name, s.count_9i, t.count_19c from
(select owner, table_name, count(1) count_9i
from dba_constraints@to9idb
where owner in ('XX')
and constraint_type = 'U'
and table_name in (select tab_name from exp_group)
group by owner, table_name) s,
(select owner, table_name, count(1) count_19c
from dba_constraints@to19cdcdb
where owner in ('XX')
and constraint_type = 'U'
and table_name in (select tab_name from exp_group)
group by owner, table_name) t
where s.table_name = t.table_name(+)
and count_9i != count_19c
order by s.owner, s.table_name;
--
-- 主键约束表
select owner, table_name from dba_constraints@to9idb t
where owner in ('XX')
and t.constraint_type = 'P'
minus
select owner, table_name from dba_constraints@to19cdcdb t
where owner in ('XX')
and t.constraint_type = 'P'
--- SNAPSHOT
select log_owner, master, log_table from dba_snapshot_logs@to9idb
minus
select log_owner, master, log_table from dba_snapshot_logs@to19cdcdb
## 检查dblink
根据沟通，只保留3个dblink，其他的删除
select * from dba_db_links;
grant create database link to XX;
-- Create database link
XX用户：
conn XX/"XX"
sqlplus XX/XX@XX
drop database link XX;
create database link XX connect to XX identified by "XX" using 'XX';
-- 创建缺失的dblink
/*
select owner, 'create database link ' || db_link || ' connect to ' || username || ' identified by XXX using ''' || host || ''';'
from(
select owner, db_link, username, host from dba_db_links@to9idb
where owner in ('PUBLIC', 'XX')
minus
select owner, db_link, username, host from dba_db_links@to19cdcdb
where owner in ('PUBLIC', 'XX')
)
order by owner;
*/
-- 测试db links是否连通
set line 200
col owner for a30
col db_link for a30
col username for a30
col host for a50
select owner, db_link, username, host from dba_db_links;
# 同步同义词
select 'create synonym ' || owner || '.' || synonym_name || ' for ' || table_owner || '.' || table_name || ';'
from(
select owner, synonym_name, table_owner, table_name from dba_synonyms@todev19cdb
where owner in ('XXX')
minus
select owner, synonym_name, table_owner, table_name from dba_synonyms@to19cdcdb
where owner in ('XXX')
) order by owner, synonym_name
-- 1. 更新synonym.sql文件
-- 2. 执行create_synonym.sh
# 同步权限
#### role权限
select 'grant ' || granted_role || ' to ' || grantee || ';'
from (
select grantee, granted_role from dba_role_privs@todev19cdb
where grantee in ('XXX')
minus
select grantee, granted_role from dba_role_privs@to19cdcdb
where grantee in ('XXX')
);
#### sys权限
select 'grant ' || privilege || ' to ' || grantee || ';'
from(
select grantee, privilege from dba_sys_privs@todev19cdb
where grantee in ('XX')
minus
select grantee, privilege from dba_sys_privs@to19cdcdb
where grantee in ('XX')
);
#### table权限
-- other schema
select owner, 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';'
from (
select grantee, owner, table_name, privilege, type from dba_tab_privs@todev19cdb
where owner in ('XX')
and table_name not like 'BIN$%'
minus
select grantee, owner, table_name, privilege, type from dba_tab_privs@to19cdcdb
where owner in ('XX')
)
where grantee in ('XX')
order by owner;
-- 一般只有XX用户的少量权限需要更新
-- FWMESP 用户的权限
select owner, 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';'
from (
select grantee, owner, table_name, privilege, type from dba_tab_privs@todev19cdb
where owner in ('XX')
and table_name not like 'BIN$%'
minus
select grantee, owner, table_name, privilege, type from dba_tab_privs@to19cdcdb
where owner in ('XX')
);
sqlplus XX/"XX"
-- XX 用户的权限
select owner, 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';'
from (
select grantee, owner, table_name, privilege, type from dba_tab_privs@todev19cdb
where owner in ('XX')
and table_name not like 'BIN$%'
minus
select grantee, owner, table_name, privilege, type from dba_tab_privs@to19cdcdb
where owner in ('XX')
);
conn meadmin/"meadmin.0505"
-- XX 用户的权限单独处理
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';'
from (
select grantee, owner, table_name, privilege, type from dba_tab_privs@todev19cdb
where owner in ('XX') and table_name not like 'BIN$%'
minus
select grantee, owner, table_name, privilege, type from dba_tab_privs@to19cdcdb
where owner in ('XX')
)
where grantee in ('XX')
--查询结果更新到 /u01/dumdata/index/grant.sql
sqlplus / as sysdba
conn XX/"XX"
@/u01/dumdata/index/grant.sql
-- 根据需求，移除权限，OEM和FW开头的表
---OEM表去除delete/update权限：112rows
select 'grant '||a.privilege||' on '||a.owner||'.'||a.table_name||' to '||a.grantee||';' grantUser,
'revoke '||a.privilege||' on '||a.owner||'.'||a.TABLE_NAME||' from '||a.GRANTEE ||';' revokeSql,
a.*
from cmp_tabprivs_19CDC a
where a.table_name not like'FWCATNS_%' and a.privilege in('UPDATE','DELETE')
and a.owner='XX' and a.grantee not in('MWPSZ','TSARCH') and a.table_name like'FW%'
and a.type not in('VIEW')
;
-- 19c库上执行
sqlplus / as sysdba
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31547506/viewspace-2987246/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- Oracle 2026年5月安全补丁：CSPU月度更新机制与紧急应对指南
- Oracle 26ai安全实战：构建基于AI的SQL防火墙与智能体防御体系
- Oracle 26ai智能运维：基于DeepSeek的AWR报告自动诊断
- Oracle 26ai自治湖仓一体：打通Iceberg数据湖与AI分析
- Oracle 26ai实战：零代码构建企业级RAG知识库
- Oracle 26Ai自然语言搜索实战：基于生成式AI的数据库查询
- Oracle 26Ai智能索引推荐实战：AI驱动的自动化性能调优
- Oracle 23c安全性增强实战：细粒度访问控制与数据屏蔽
- Oracle 23c分区增强实战：自动分区管理简化运维
- Oracle 23c区块链表实战：构建不可篡改的数据审计系统
