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