MSSQL Server 遷移至 ORACLE解決方案

sjw1933發表於2023-03-02

一、       概要

本方案旨在提供專案執行中的資料庫平臺及型別遷移轉換工作參考,閱讀本方案將有助於清楚瞭解資料庫型別轉換相關工作內容。預評估各項工作內容的工作量。

 

資料庫型別遷移的工作內容至少包含轉換資料庫結構(DDL )、檢視、儲存過程、函式、觸發器、業務查詢SQL 及相關SQL 指令碼轉換。

 

Ø    適用範圍

Microsoft   SQL   Server   2000 2014

ORACLE 10g 11g 12c

二、       遷移工具

OGG

 


三、       資料庫物件型別及語法轉換

1         資料庫結構轉換

Ø    常見資料庫資料型別:

SQL  Server Oracle 之間的資料型別對映。

 

字元和二進位制字串型別:

SQLServer

說明

ORACLE

CHAR (n)

Fixed-length non-Unicode string, 1 <=  n <= 8000

CHAR(n)

NCHAR(n)

Fixed-length Unicode UCS-2 string, 1 <=  n <= 4000

NCHAR(n)

NVARCHAR n

Variable-length Unicode UCS-2 string, 1 <=  n <= 4000

NVARCHAR2(n)

NVARCHAR m ax

2G

NCLOB

VARCHAR

Variable-length non-Unicode string, 1 <=  n <= 8000

VARCHAR2(n)

VARCHAR(max)

2GB

CLOB

 

數字型別:

SQLServer

說明

ORACLE

BIGINT

64 位整數

NUMBER(19)

DECIMAL(p,s) / DEC(p,s) 

Fixed-point number

NUMBER(p,s)

FLOAT(n)

Single ( n <= 24) and double ( n <= 53) 
precision floating-point number

NUMBER

INTERGER / INT

32-bit integer

NUMBER(10)

NUMBERIC(p,s)

Fixed-point number

NUMBER(p,s)

REAL

Single precision floating-point number

NUMBER

SMALLINT

16-bit integer

NUMBER(5)

TINYINT

0 to 255

NUMBER(3)

 

日期和時間:

SQLServer

說明

ORACLE

DATA

Date (year, month and day)

DATE includes time

DATATIME

Date and time with milliseconds (accuracy .000, .003, .007 seconds)

TIMESTAMP(3)

TIME(p)

Time, 0 <=  p <= 7 (100 nanoseconds accuracy)

TIMESTAMP(p)

 

其他資料型別:

SQLServer

說明

ORACLE

BIT

0,1 and NULL

NUMBER(1)

MONEY

Monetary data

NUMBER(19,4)

SMALLMONEY

Monetary data

NUMBER(10,4)

UNIQUEIDENTIFIER

GUID with dashes(-)

CHAR(36)

XML

XML data

XMLTYPE

 

Ø    常用內建SQL 函式:

 

轉換內建SQL 函式

SQLServer

說明

ORACLE

CONVERT CHAR | VARCHAR exp

Convert to string

TO_CHAR(exp)

GETDATE()

Get the current date and time

SYSTIMESTAMP

MONTH(datetime)

Extract month from datetime

EXTRACT(MONTH FROM datetime)

SYSTEM_USER

OS   user name

SYS_CONTEXT(‘USERENV‘,’OS_USER’)

YEAR(datatime)

Extract year from datetime

EXTRCT(YEAR FROM datetime)

 

Ø    建表語句宣告語法

 

SQLServer

說明

ORACLE

IDENTITY( start, increment)

Identity column

Emulated using a sequence and trigger

DEFAULT  exp

Column default

DEFAULT must be specified right after 
data type, before NOT NULL etc. 

CONSTRAINT  name DEFAULT  value

Named DEFAULT

DEFAULT  value

CLUSTERED | NONCLUSTERED

Clustered and non-clustered 
primary and unique key

Keyword removed

col type   CONSTRAINT  name 
PRIMARY KEY( col)

Inline primary key

col type   CONSTRAINT  name 
PRIMARY KEY - no column name 
in inline constraint

PRIMARY KEY( col ASC | DESC, …)

Sorting order in constraint

PRIMARY KEY( col, …) - No ASC, DESC allowed

ROWGUIDCOL

Indicates that the column is 
GUID, but IDs are not generated

Keyword removed

 

主健和唯一索引選項:

SQLServer

說明

ORACLE

ALLOW_PAGE_LOCKS = ON | OFF

Allow to use page locks

Removed

ALLOW_ROW_LOCKS = ON | OFF

Allow to use row locks

Removed

FILLFACTOR =  num

Leave free space in leaf index nodes

Removed

IGNORE_DUP_KEY = ON | OFF

Ignore duplicate keys

Removed

PAD_INDEX = ON | OFF

Leave space in intermediate index nodes

Removed

STATISTICS_NORECOMPUTE = ON | OFF

Automatic statistics update

Removed

 

Ø    查詢語句

SQLServer

說明

ORACLE

SELECT @v = (SELECT  c FROM …)

Assignment statement

SELECT  c INTO  v FROM …

SELECT @v = c, @v2 = c2 FROM …

SELECT INTO statement

SELECT  c, c2 INTO  v, v2 FROM …

SELECT … FROM

Result set from a procedure

OPEN  out_refcur FOR SELECT … FROM

 

返回行數:

SQLServer

說明

ORACLE

SELECT TOP  n …

Without sorting

SELECT … WHERE rownum <=  n

SELECT TOP  n … ORDER BY

With sorting

SELECT * (SELECT … ORDER BY) WHERE rownum <=  n

SELECT TOP  n PERCENT …

% Without sorting

SELECT … WHERE rownum <=  n/100 * 
(SELECT COUNT(*) …)

SELECT TOP  n PERCENT … ORDER BY

% With sorting

SELECT * (SELECT … ORDER BY) rownum <=  n/100 * 
(SELECT COUNT(*) …)

 

儲存過程建立語句

SQLServer

說明

ORACLE

CREATE PROCEDURE | ALTER PROCEDURE  name


CREATE OR REPLACE PROCEDURE  name

@param   datatype   default OUT | OUTPUT


p_param   IN | OUT | IN OUT  datatype DEFAULT  default

Optional () for procedure parameters


() required

AS


IS | AS

RETURN  int

Return the status code

RETURN;

GO


/

 

SET  選項宣告

SQLServer

說明

ORACLE

SET ANSI_NULLS ON | OFF

Use = <> with NULLs

Commented

SET ANSI_PADDING ON | OFF

Insert trailing blanks to VARCHAR

Commented

SET NOCOUNT ON | OFF

Send messages on affected rows

Removed

SET QUOTED_IDENTIFIER ON | OFF

Quote identifiers with ""

Commented

 

 

SQLServer

說明

ORACLE







 

 

 

資料庫同步環境搭建

     實施環境如下:

 


版本

IP

OGG 版本

作業系統

源庫

008R2

192.168.242.117

12.3

WIN2016

目標庫

Oracle 11.2.0.4

192.168.238.56

12.2

Linux 6

            

 

參考文件:

 

 

  檢查生產庫

資料庫需要設定為full recovery

開啟sqlserver manager studio= 》展開database= 》右鍵點選源資料庫= 》選擇屬性= = 》點選選項= 》選擇恢復模式為全部

 

檢查

開啟CDC

SELECT  is_cdc_enabled , CASE   WHEN  is_cdc_enabled =

THEN   'CDC 功能禁用'

ELSE   'CDC 功能啟用'

END   描述

FROM   sys . databases

WHERE   NAME   =   'jmtest'

 

USE  jmtest

GO

EXECUTE   sys . sp_cdc_enable_db ;

GO

 

EXEC   sys . sp_cdc_enable_db

 

關閉資料庫 'trunc. log on chkpt'

use   master

go

EXEC   sp_dboption   'jmtest' ,   'trunc. log on chkpt' ,   'false'

go

 

資料庫須為完整回覆(FULL)模式

use   master

go

alter   database  jmtest  set   recovery   full

go

 

檢視資料庫或表是否啟用cdc (不需要啟動,配置完成自動啟動)

SELECT   name , is_cdc_enabled  FROM   sys . databases   WHERE  is_cdc_enabled  =  1  

SELECT   name , is_tracked_by_cdc  FROM  jmtest . sys . tables   WHERE  is_tracked_by_cdc  =  1 

GO

 

建立ODBC 資料來源

 

 

 

 

這裡最好選擇整合windows身份驗證

 

 

 

 

 

1 Ensure that the Extract has been deleted via  GGSCI: DELETE EXTRACT <extname>

確保沒有挖掘程式存在,若有先刪除

 

2 、刪除已有的

EXECUTE sys.sp_cdc_drop_job 'cleanup'

 

3 Run the  ogg_cdc_cleanup_setup.bat  file, providing the following variable values:

執行 cdc cleanup 指令碼

模板:

ogg_cdc_cleanup_setup.bat deleteExtCheckpoint <userid> <password> <databasename> <servername\instancename> <schema>

 

C:\ogg>ogg_cdc_cleanup_setup.bat createJob ggsadmin Hzmc321# jmtest (local) dbo

 

 

有時候會報錯提示沒有cdc capture ,這時可以先新增trandata 表後再來做這個步驟

 

 

不支援的操作與型別

不支援truncate TextCopy WRITETEXT UPDATETEXT 操作

不支援SQL_Variant 型別

 

  安裝生產端ogg

解壓軟體,配置mgr

C:\odc>ggsci

GGSCI (WIN-ARKCDINPK6G) 1> create subdirs

 

C:\odc>install addservice

// 這個命令保證如果執行 mgr 可以在後臺跑,如果不新增這個服務執行程式的時候就會有一個視窗,且如果關掉程式就要停止

GGSCI> edit param mgr

引數:

PORT 7809

DYNAMICPORTLIST 7808-7809

autorestart extract *

PURGEOLDEXTRACTS C:\ogg\dirdat\* ,usecheckpoints, minkeepdays 1

 

ggsci>edit param ./GLOBALS

輸入:

GGSCHEMA dbo

 

GGSCI> start mgr

 

建立挖掘程式

GGSCI> dblogin sourcedb  ogg3  –dblogin sourcedb + ODBC 資料來源

GGSCI> add trandata ggsadmin.* -- 新增表級別附加日誌

GGSCI> add extract jmext tranlog begin now

GGSCI> edit param jmext

引數:

extract jmext

sourcedb ogg3 userid ggsadmin password Hzmc321#

exttrail C:\ogg\dirdat\jm,format release 12.2

COMPRESSUPDATES

WILDCARDRESOLVE DYNAMIC

numfiles 5000

DISCARDFILE  C:\ogg\dirdat\jm.dsc,append,megabytes 100

TRANLOGOPTIONS MANAGECDCCLEANUP

//  MANAGESECONDARYTRUNCATIONPOINT 這個引數在 12.3 for SQL server 中無效,需要更改為 MANAGECDCCLEANUP ,但這個引數的前提就是要建立 cdc cleanup process

table odc.test;

 

 

建立傳輸程式

引數:

extract jmdmp

TRANLOGOPTIONS MANAGECDCCLEANUP

RMTHOST 192.168.238.56, MGRPORT 7809

RMTTRAIL /ogg12/dirdat/jm,format release 12.2

table odc.test;

 

生成表定義檔案

GGSCI> edit param defgen

引數:

sourcedb ogg3 userid ggsadmin password Hzmc321#

table odc.test;

 

cmd 中進入ogg 安裝目錄,執行如下語句:

 C:\ogg\dirprm\defgen.prm

複製源端dirdef檔案下的def檔案到目標端的 dirdef檔案下

 

資料庫和日誌

資料庫備份:

》任務》備份

選擇磁碟方式及備份檔案,注意空間

覆蓋備份集

完成後驗證

不壓縮

 

 

日誌備份:

右鍵》任務》備份

 

選擇磁碟方式及備份檔案,注意空間

覆蓋備份集

完成後驗證

截斷日誌

不壓縮

 

 

 

 

 

  目標主機安裝ogg

建立使用者

SQL> create tablespace odc_tps datafile '/oradata/smkdb/odc01.dbf' size 128M autoextend on;

 

Tablespace created.

 

SQL> create user odc identified by odc default tablespace odc_tps;

 

GRANT CONNECT TO odc;

GRANT ALTER ANY TABLE TO odc;

GRANT ALTER SESSION TO odc;

GRANT CREATE SESSION TO odc;

GRANT FLASHBACK ANY TABLE TO odc;

GRANT SELECT ANY DICTIONARY TO odc;

GRANT SELECT ANY TABLE TO odc;

GRANT RESOURCE TO odc;

GRANT DBA TO odc;

 

建立checkpoint table

cd /odc

$ sqlplus odc/odc

SQL> @chkpt_ora_create.sql

DROP TABLE ggs_checkpoint

           *

ERROR at line 1:

ORA-00942: table or view does not exist

 

Table created.

 

配置環境變數

HP-UX : SHLIB_PATH

AIX : LIBPATH

LINUX : LD_LIBRARY_PATH

例如:

export LD_LIBRARY_PATH=/odc:$LD_LIBRARY_PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

 

安裝ogg 配置 mgr

$ cd /odc

$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

$ ./ggsci

GGSCI> create subdirs

GGSCI> edit param mgr

引數:

port 7809

DYNAMICPORTLIST 7800-7810

PURGEOLDEXTRACTS ./dirdat/sm*, USECHECKPOINTS, MINKEEPHOURS 24

autorestart extract * retries 10 waitminutes 10

 

GGSCI> edit param ./GLOBALS

引數:

GGSCHEMA odc

CHECKPOINTTABLE odc.ggs_checkpoint --- 儲存退出

 

GGSCI> start mgr

 

目標端建立replicat 程式

GGSCI> dblogin userid odc password odc

GGSCI> add replicat jmrep exttrail ./dirdat/jm

GGSCI> edit param jmrep

引數:

replicat jmrep

setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

userid odc, password odc

--handlecollisions

sourcedefs ./dirdef/defgen.def

DISCARDFILE /ogg12/dirrpt/me.dsc, APPEND megabytes 20

DISCARDROLLOVER on sunday

map dbo.test target xjm.test;

 

 

  初始化同步

初始化同步有2 中方法,一種是透過中間庫的離線同步,一種是直接線上同步。

無論哪種方法,必須先在目標庫裡建立表結構,具體的資料型別轉換,檢視附錄。

 

透過中間庫的離線同步資料

4.1.1 還原資料庫和日誌

傳送備份檔案到中間庫(本地庫也可以)。

還原資料庫:

資料庫》還原資料庫

目標資料庫填寫資料庫名稱

目標時間點預設

還原裝置選擇資料庫備份檔案

源裝置》新增》選擇備份檔案,先還原資料庫

 

 

選擇with replace restore with norecovery

 

 

資料庫還原後,右鍵點選該資料庫= 》任務= 》還原= 》事務日誌

選擇從檔案或磁帶

 

 

選擇日誌備份檔案

 

時間點選擇備份完成時間(記錄該時間點,作為挖掘程式的起始挖掘點)

此處為 56


 

 

 

選擇restore with recovery

   

4.1.2 中間庫資料遷移到Oracle 資料庫

Kettle 將中間庫資料遷移到Oracle 資料庫

 

4.1.3 啟動ogg 程式

源端啟動抽取和傳輸程式

GGSCI>  35  ( 要保證剛才恢復的事務日誌還在原位置,否則無法啟動程式 )

GGSCI> start extmed

GGSCI> start dmpmed

目標端啟動應用程式

GGSCI> start repmed

 

線上同步資料

4.2.1 開啟抽取程式和傳輸程式

GGSCI> start extmed

GGSCI> start dmpmed

 

4.2.2 第三方工具遷移資料

Kettle sqlserver 資料遷移到Oracle 資料庫。

 

4.2.3 配置容錯引數,開啟應用程式

應用程式中新增handlecollisions 引數。

 

4.2.4 等到資料追平後,去掉容錯引數,重啟應用程式

GGSCI> edit param jmrep

引數:

replicat jmrep

setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

userid odc, password odc

--handlecollisions

sourcedefs ./dirdef/defgen.def

DISCARDFILE /ogg12/dirrpt/me.dsc, APPEND megabytes 20

DISCARDROLLOVER on sunday

map dbo.test target xjm.test;

 

GGSCI> stop jmrep

GGSCI> start jmrep

 

  資料比對方案

在正式遷移前多次校驗資料,提前準備好 SQL 指令碼

1)       veridata校驗資料

使用veridata對錶進行比對,提前多次校驗,確認大致需要的比對時間。

2)       veridata失敗的物件使用SQL指令碼校驗資料

對於veridata比對失敗的物件,使用sql語句比對記錄數。

 


 

  正式遷移

 

生產庫停止業務

1)       確保OGG應用完畢

select group#,thread#,status from v$log;

切換多個歸檔,確保OGG 應用完(至少切x 次,因為有x 組日誌組)。

alter system archive log current;

alter system checkpoint;

 

比對物件

是否一致

Veridata 比對任務


使用者物件以及數量


索引以及數量


Lob 表數量


檢視數量


無效物件數量


物件許可權


Veridata 比對慢或者失敗的表


 

提前建立一個到EMR 生產庫的dblink ,方便資料比對

create public database link olddatabase connect to odc identified by odc using 'olddatabase';

 

中的任務

單表比對時間超過 10 分鐘的表以及比對失敗的表除外(根據客戶情況,以及停機時間決定)

veridataadmin/hzmc321#

 

SELECT D.OWNER, D.OBJECT_TYPE

FROM dba_objects d

WHERE d.OWNER in ('ADT','AIB','BEHAVIORLOG'……)

and object_name not like 'MLOG%'

AND NOT EXISTS (SELECT 1

FROM DBA_RECYCLEBIN@old B

WHERE B.object_name = D.OBJECT_NAME

AND D.OWNER = B.owner)

GROUP BY D.OWNER, D.OBJECT_TYPE

minus

SELECT D.OWNER, D.OBJECT_TYPE

FROM dba_objects d

WHERE d.OWNER in ('ADT','AIB','BEHAVIORLOG'……)

and object_name not like 'MLOG%'

AND NOT EXISTS (SELECT 1

FROM DBA_RECYCLEBIN B

WHERE B.object_name = D.OBJECT_NAME

AND D.OWNER = B.owner)

GROUP BY D.OWNER, D.OBJECT_TYPE;

 

SELECT D.OWNER,D.OBJECT_TYPE,COUNT(*)

FROM dba_objects@old d

WHERE d.OWNER in ('ADT','AIB','BEHAVIORLOG'……)

AND NOT EXISTS (SELECT * FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner) 

GROUP BY D.OWNER,D.OBJECT_TYPE

minus

SELECT D.OWNER,D.OBJECT_TYPE,COUNT(*)

FROM dba_objects d

WHERE d.OWNER in ('ADT','AIB','BEHAVIORLOG'……)

AND NOT EXISTS (SELECT * FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner) 

GROUP BY D.OWNER,D.OBJECT_TYPE;

select owner,count(*) from dba_indexes@old 

where owner in ('ADT','AIB','BEHAVIORLOG'……)

group by owner 

minus

select owner,count(*) from dba_indexes 

where owner in ('ADT','AIB','BEHAVIORLOG'……)

group by owner order by owner;

 

select owner,INDEX_NAME from dba_indexes@old 

where owner in ('ADT','AIB','BEHAVIORLOG'……) 

minus

select owner,INDEX_NAME from dba_indexes 

where owner in ('ADT','AIB','BEHAVIORLOG'……);

表數量

select owner,table_name from dba_lobs@old where owner in ('ADT','AIB','BEHAVIORLOG'……) 

minus

select owner,table_name from dba_lobs where owner in ('ADT','AIB','BEHAVIORLOG'……);

select owner,VIEW_NAME from dba_views@old 

where owner in ('ADT','AIB','BEHAVIORLOG'……)

minus

select owner,VIEW_NAME from dba_views 

where owner in ('ADT','AIB','BEHAVIORLOG'……);

select count(*) from dba_objects where status<>'VALID';

@?/rdbms/admin/utlrp.sql

EMR 生產庫許可權(透過dblink 查詢生產庫環境許可權)

drop table t_tmp_user_lhr;

create table t_tmp_user_lhr( id number, username varchar2(50), exec_sql varchar2(4000),create_type varchar2(20));

drop sequence s_t_tmp_user_lhr;

create sequence s_t_tmp_user_lhr;

 

begin

  for cur in (SELECT d.username,

                     d.default_tablespace,

                     d.account_status,

                     'create user ' || d.username || ' identified by ' ||

                     d.username || ' default tablespace ' ||

                     d.default_tablespace || ' TEMPORARY TABLESPACE ' ||

                     D.temporary_tablespace || ';' CREATE_USER,

                     replace(to_char(DBMS_METADATA.GET_DDL('USER',

                                                           D.username)),

                             chr(10),

                             '') create_USER1

                FROM dba_users@old d

               WHERE d.username in ('DXY')) loop

    INSERT INTO t_tmp_user_lhr

      (id, username, exec_sql, create_type)

    values

      (s_t_tmp_user_lhr.nextval, cur.username, cur.CREATE_USER, 'USER');

    INSERT INTO t_tmp_user_lhr

      (id, username, exec_sql, create_type)

      SELECT s_t_tmp_user_lhr.nextval,

             cur.username,

             CASE

               WHEN D.ADMIN_OPTION = 'YES' THEN

                'GRANT ' || d.privilege || ' TO ' || d.GRANTEE ||

                ' WITH GRANT OPTION ;'

               ELSE

                'GRANT ' || d.privilege || ' TO ' || d.GRANTEE || ';'

             END priv,

             'DBA_SYS_PRIVS'

        FROM dba_sys_privs@old d

       WHERE D.GRANTEE = CUR.USERNAME;

  

    INSERT INTO t_tmp_user_lhr

      (id, username, exec_sql, create_type)

      SELECT s_t_tmp_user_lhr.nextval,

             cur.username,

             CASE

               WHEN D.ADMIN_OPTION = 'YES' THEN

                'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE ||

                ' WITH GRANT OPTION;'

               ELSE

                'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE || ';'

             END priv,

             'DBA_ROLE_PRIVS'

        FROM DBA_ROLE_PRIVS@old d

       WHERE D.GRANTEE = CUR.USERNAME;

    INSERT INTO t_tmp_user_lhr

      (id, username, exec_sql, create_type)

      SELECT s_t_tmp_user_lhr.nextval,

             cur.username,

             CASE

               WHEN d.grantable = 'YES' THEN

                'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||

                d.table_name || ' TO ' || d.GRANTEE ||

                ' WITH GRANT OPTION ;'

               ELSE

                'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||

                d.table_name || ' TO ' || d.GRANTEE || ';'

             END priv,

             'DBA_TAB_PRIVS'

        FROM DBA_TAB_PRIVS@old d

       WHERE D.GRANTEE = CUR.USERNAME;

  end loop;

  COMMIT;

end;

/

 

新生產許可權

drop table t_tmp_user_lhr_new;

create table t_tmp_user_lhr_new( id number, username varchar2(50), exec_sql varchar2(4000),create_type varchar2(20));

drop sequence s_t_tmp_user_lhr_new;

create sequence s_t_tmp_user_lhr_new;

 

begin

  for cur in (SELECT d.username,

                     d.default_tablespace,

                     d.account_status,

                     'create user ' || d.username || ' identified by ' ||

                     d.username || ' default tablespace ' ||

                     d.default_tablespace || ' TEMPORARY TABLESPACE ' ||

                     D.temporary_tablespace || ';' CREATE_USER,

                     replace(to_char(DBMS_METADATA.GET_DDL('USER',

                                                           D.username)),

                             chr(10),

                             '') create_USER1

                FROM dba_users d

               WHERE d.username in ('DXY')) loop

    INSERT INTO t_tmp_user_lhr_new

      (id, username, exec_sql, create_type)

    values

      (s_t_tmp_user_lhr_new.nextval, cur.username, cur.CREATE_USER, 'USER');

    INSERT INTO t_tmp_user_lhr_new

      (id, username, exec_sql, create_type)

      SELECT s_t_tmp_user_lhr_new.nextval,

             cur.username,

             CASE

               WHEN D.ADMIN_OPTION = 'YES' THEN

                'GRANT ' || d.privilege || ' TO ' || d.GRANTEE ||

                ' WITH GRANT OPTION ;'

               ELSE

                'GRANT ' || d.privilege || ' TO ' || d.GRANTEE || ';'

             END priv,

             'DBA_SYS_PRIVS'

        FROM dba_sys_privs d

       WHERE D.GRANTEE = CUR.USERNAME;

    INSERT INTO t_tmp_user_lhr_new

      (id, username, exec_sql, create_type)

      SELECT s_t_tmp_user_lhr_new.nextval,

             cur.username,

             CASE

               WHEN D.ADMIN_OPTION = 'YES' THEN

                'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE ||

                ' WITH GRANT OPTION;'

               ELSE

                'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE || ';'

             END priv,

             'DBA_ROLE_PRIVS'

        FROM DBA_ROLE_PRIVS d

       WHERE D.GRANTEE = CUR.USERNAME;

    INSERT INTO t_tmp_user_lhr_new

      (id, username, exec_sql, create_type)

      SELECT s_t_tmp_user_lhr_new.nextval,

             cur.username,

             CASE

               WHEN d.grantable = 'YES' THEN

                'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||

                d.table_name || ' TO ' || d.GRANTEE ||

                ' WITH GRANT OPTION ;'

               ELSE

                'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||

                d.table_name || ' TO ' || d.GRANTEE || ';'

             END priv,

             'DBA_TAB_PRIVS'

        FROM DBA_TAB_PRIVS d

       WHERE D.GRANTEE = CUR.USERNAME;

  end loop;

  COMMIT;

end;

/

 

老環境的許可權減去新環境的,產生的結果是新庫缺少的許可權,將結果賦予新環境

SELECT EXEC_SQL FROM t_tmp_user_lhr where CREATE_TYPE not in ('USER')

minus

SELECT EXEC_SQL FROM t_tmp_user_lhr_new where CREATE_TYPE not in ('USER');

 

比對慢或者失敗的表

比對veridata 比對過慢以及比對失敗的表的記錄總數

具體根據veridata 比對結果。提前準備好指令碼,例如

vi /home/oracle/mc_scripts/table_count.sql

 

set serveroutput on;

declare

  v_tablename varchar2(60);

  v_count     int;

  v_sql       varchar2(2000);

  cursor cur_tablename is

    select table_name

      from dba_tables

     where table_name in (' DXY_TEMP')

       and owner = ' DXY' order by table_name;

begin

  open cur_tablename;

  loop

    fetch cur_tablename

      into v_tablename;

    exit when cur_tablename%notfound;

    v_sql := 'select count(*) from DXY.' || v_tablename || '';

    execute immediate v_sql

      into v_count;

    dbms_output.put_line(v_tablename || ':' || v_count);

  end loop;

  close cur_tablename;

end;

/

 

set serveroutput off;

 

重導部分物件

排除的表

若存在OGG 不支援同步的表,則需要重新匯入。

sysdate+0) NEXT (SYSDATE + 3600/86400);

 

@?/rdbms/admin/utlrp.sql

 

修改資料庫 IP 地址

老生產庫調整

注意備份,方便回退

6.3.1.1 停止雙節點服務

停止兩邊節點資料庫,監聽 , 並且停止  crs

srvctl stop database -d kobra

srvctl stop asm -n zclcrac1

srvctl stop asm -n zclcrac2

srvctl stop nodeapps -n zclcrac1

srvctl stop nodeapps -n zclcrac2

 

雙節點關停叢集:

#crsctl stop crs

確認叢集完全停止

 

6.3.1.2 修改雙節點 /etc/hosts 檔案

修改前:

10.0.18.8  zclcrac1

10.0.18.9  zclcrac2

10.0.18.10  zclcrac1-vip

10.0.18.11  zclcrac2-vip

10.0.18.12  zclcrac-scan

 

修改後:

10.0.105.3  zclcrac1

10.0.105.4  zclcrac2

10.0.105.5  zclcrac1-vip

10.0.105.6  zclcrac2-vip

10.0.105.7      zclcrac-scan

 

6.3.1.3public IP 修改

root 使用者修改 bond0 檔案:

#cd /etc/sysconfig/network-scripts/

#vi ifcfg-bond0

DEVICE=bond0

NM_CONTROLLED=no

>

IPADDR=10.0.105.3

BOOTPROTO=static

NETMASK=255.255.255.0

GATEWAY=10.0.105.254

DNS1=10.0.8.3( DNS 需確認是否要修改 )

IPV6INIT=no

USERCTL=no

PREFIX=24

DEFROUTE=yes

IPV4_FAILURE_FATAL=yes

NAME="System bond0"

 

二節點一樣修改:

將10.0.18.9改成10.0.105.4
重啟雙節點網路服務:
#service network restart
 

雙節點啟動CRS,修改共享盤中資訊:(該操作在一個節點上完成)
grid使用者切換到root使用者(帶環境變數)
#啟動雙節點CRS:
#crsctl start crs
Root使用者修改共享盤網路配置:
#oifcfg getif

bond0  10.0.18.0  global  public 

bond1  195.195.195.0  global  cluster_interconnect
 

修改

# 檢視當前配置:

#oifcfg iflist

bond0 10.0.105.0

bond1 195.195.195.0

刪除並新增

#oifcfg delif -global bond0 

#oifcfg setif -global bond0/10.0.105.0:public
雙節點確認:

./oifcfg getif 

bond0  10.0.105.0  global  public 

bond1  195.195.195.0  global  cluster_interconnect

 

6.3.1.4 修改 VIP

關閉資料庫:

srvctl stop database -d kobra

檢查 vip 配置: (類似以下輸出)

$ srvctl config vip -n zclcrac1 

VIP exists.: zclcrac1

VIP exists.: / zclcrac1-vip/10.0.18.10/255.255.255.0/bond0

 

$ srvctl config vip -n zclcrac2

VIP exists.: zclcrac2

VIP exists.: / zclcrac2-vip/10.0.18.11/255.255.255.0/ bond0

 

停止  vip  服務和修改  vip

$srvctl stop listener -n zclcrac1 

$srvctl stop listener -n zclcrac2 

$srvctl stop vip -n zclcrac1

$srvctl stop vip -n zclcrac2

#srvctl modify nodeapps -n zclcrac1 -A 10.0.105.5/255.255.255.0/bond0 

#srvctl modify nodeapps -n zclcrac2 -A 10.0.105.6/255.255.255.0/bond0

 

確認是否修改成功:

srvctl config vip -n zclcrac1

srvctl config vip -n zclcrac2

 

啟動  vip  服務 , 監聽,資料庫

 

srvctl start vip -n zclcrac1

srvctl start vip -n zclcrac2 

srvctl start listener -n zclcrac1 

srvctl start listener -n zclcrac2 

srvctl start database  d kobra

 

雙節點修改 local_listener  引數

alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.105.5)(PORT=1521))))' scope=both sid='kobra1';

alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.105.6)(PORT=1521))))' scope=both sid='kobra2';

 

6.2.1.5 修改 SCAN IP

srvctl config scan  檢視當前  vip  設定

#./srvctl config scan 

SCAN name:  zclcrac-scanNetwork: 1/10.0.18.0/255.255.255.0/bond0 

SCAN VIP name: scan1, IP:  /scan-vip/10.0.18.12

 

停止  scan_listener   sacn vip

$srvctl stop scan_listener 

$srvctl stop scan 

$srvctl status scan 

SCAN VIP scan1 is enabled 

SCAN VIP scan1 is not running  

$srvctl status scan_listener 

SCAN Listener LISTENER_SCAN1 is enabled 

SCAN listener LISTENER_SCAN1 is not running

 

 root  使用者修改  scan vip

#srvctl modify scan  n zclcrac-scan

 

檢查 scanip 修改:

srvctl config scan

 

啟動  scan   scan_listener 

srvctl start scan 

srvctl start scan_listener

 

新生產庫調整

步驟與老生產庫一致,注意備份,方便回退

  業務測試

由業務層面進行

  回退方案

配置OGG 反向同步。

ggsci> add extract bk_ext, tranlog, threads 2, begin now

ggsci> add exttrail ./dirdat/bk extract bk_ext

ggsci> edit param bk_ext

 

extract bk_ext

setenv (NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK")

userid odc,password odc

exttrail ./dirdat/bk

discardfile ./dirrpt/bk.dsc, append megabytes 100

discardrollover on sunday

tranlogoptions dblogreader

FETCHOPTIONS FETCHPKUPDATECOLS

ddl include objname ADT.* exclude objtype 'TRIGGER' &

include objname AIB.* exclude objtype 'TRIGGER' &

include objname BEHAVIORLOG.* exclude objtype 'TRIGGER'

ddloptions addtrandata

table ADT.*;

table AIB.*;

table BEHAVIORLOG.*;

 

只挖掘,不應用。確保挖掘正常後,可以開始正式業務。

 

修改資料庫IP

 

 

 

  注意事項

1、   His 應用程式更換資料庫平臺需要his 廠商業務支援改造,確認his 廠商是否可以由sqlserver 資料庫改造成oracle 資料庫。光資料遷移到oracle 資料庫his 應用將無法使用。面臨這類問題一般有兩種解決方案:1 his 廠商支援oracle 資料庫,可以付費改造應用;2 、更換his 廠商。

兩種資料庫型別相差較大。故存在物件無法建立或者欄位不支援的情況。


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23825935/viewspace-2937917/,如需轉載,請註明出處,否則將追究法律責任。

相關文章