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