資料庫升級之-XTTS

yingyifeng306發表於2020-08-11

說明

該文件為 XTTS 資料庫跨平臺遷移方案

環境說明

源端資料庫採用AIX 小機RAC 架構,資料庫版本11.2.0.4

目標端採用LINUX X86 RAC 架構,資料庫版本為11.2.0.4+ 最新PSU

 

硬體環境檢查

對於一套RAC 的實施,我們需要確保基本的硬體環境已經達到要求,具體如下:

1.        主機作業系統安裝,版本在RHEL 6 以上( 本文件針對的版本)

2.        儲存已經掛載,並且所有的儲存LUN 已經兩兩對映到對應的兩臺主機

3.        LUN 要求:需要1G LUN 3 1T LUN 若干(具體視資料庫空間) 並且互相對映

4.        網線已經正常接駁,心跳建議採用交換機模式連線

 

前置條件檢查

條目

是否滿足

備註

源端不能是windows


源庫的版本必須大於10.2


源庫的compatible 引數不能大於目標端


源庫和目標庫字符集需要保持一致


目標端db_files 引數必須大於源端


源庫必須處於歸檔模式


源庫的rman 配置中DEVICE TYPE DISK 不能設定為COMPRESSED


要遷移的表空間資料檔案必須都是online 的或者不包括offline 資料檔案


排除系統表空間,避免衝突並檢查業務表空間是否自包含


 

資源配置情況

配置型別

源  庫

目標庫

資料庫版本

11.2.0.4

11.2.0.4.161018

資料庫名稱

XXX

XXX

資料庫字符集

AMERICAN_AMERICA.ZHS16GBK

AMERICAN_AMERICA.ZHS16GBK

資料庫節點

RAC 1 節點

RAC 1 節點

作業系統版本

AIX6

Linux6.8

磁碟組大小



資料庫大小



Block size

8192

8192

 

生產基本資料統計

表空間統計

set linesize 200

col file_name   for a50

set pagesize 0

select   file_id,tablespace_name,file_name,status from dba_data_files;

 

需要傳輸的資料檔案大小

set linesize   1000

col file_name   for a50

SELECT   d.FILE_ID,

       d.TABLESPACE_NAME,

       (SELECT (SUM(nb.BYTES/1024/1024))

          FROM dba_data_files nb

         WHERE nb.TABLESPACE_NAME =   d.TABLESPACE_NAME) ts_size_m,

         d.FILE_NAME,

       (d.BYTES/1024/1024) file_size_m,

       (d.USER_BYTES/1024/1024)   file_use_size_m

  FROM dba_data_files d

WHERE   d.TABLESPACE_NAME  not in (‘SYSTEM’,’SYSAUX’,’UNDOTBS1’,’UNDOTBS2’)  

 ORDER BY    file_id;

 

需要遷移的使用者統計

alter session   set nls_date_format='yyyy-mm-dd hh24:mi:ss';

set line 200

SELECT   d.username,

       d.default_tablespace,

       d.temporary_tablespace,

       d.account_status,

       d.created

  from dba_users d

 where d.account_status = 'OPEN' and   d.username not like '%SYS%'

  order by d.CREATED desc;

 

select ‘‘‘‘||username||’’’,’   from dba_users where to_char(created,’yyyy-mm-dd’) > ‘2013-10-01’;

 

使用者許可權收集

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 d                                                                                   

 WHERE d.username   in    (‘TPCC’,’CURRY4’,’TEST24’,’JJF’,’CURRY3’,’CURRY2’,’CURRY1’,’HR’,’TEST’,’HZMCMEMAUDIT’,’CURRY’,’CURRY5’,’TEST1’,’JTEST’,’TEST02’,’ZJJBUNI2REP’,’ZJJBUNI2’,’QMCB_NEW’,’JBGC’))   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 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 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 d                                                                                        

       WHERE D.GRANTEE = CUR.USERNAME;                                                                              

    end loop;                                                                                                         

    COMMIT;                                                                                                          

end;                                                                                                                

/                                                                                                                    

SELECT * FROM t_tmp_user_lhr;

col username for a17

col CREATE_TYPE for a17

col EXEC_SQL for a110

set line 200

SELECT   id,username,CREATE_TYPE,EXEC_SQL FROM t_tmp_user_lhr where CREATE_TYPE not in   (‘USER’);                                                                                

 

使用者表格規模統計

select d.owner, (sum(bytes) / 1024   / 1024) sizes_m

    from dba_segments d

    where d.owner   in (‘TPCC’,’CURRY4’,’TEST24’,’JJF’,’CURRY3’,’CURRY2’,’CURRY1’,’HR’,’TEST’,’HZMCMEMAUDIT’,’CURRY’,’CURRY5’,’TEST1’,’JTEST’,’TEST02’,’ZJJBUNI2REP’,’ZJJBUNI2’,’QMCB_NEW’,’JBGC’)

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

 GROUP BY d.owner

 order by sum(bytes) desc;

使用者物件個數統計

基於使用者物件統計

  FROM dba_objects d

 WHERE d.OWNER   in (‘TPCC’,’CURRY4’,’TEST24’,’JJF’,’CURRY3’,’CURRY2’,’CURRY1’,’HR’,’TEST’,’HZMCMEMAUDIT’,’CURRY’,’CURRY5’,’TEST1’,’JTEST’,’TEST02’,’ZJJBUNI2REP’,’ZJJBUNI2’,’QMCB_NEW’,’JBGC’)

 and d.OWNER not in (‘PUBLIC’)  

 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

 ORDER BY COUNT(1) desc;

 

基於使用者物件型別統計

   FROM dba_objects d

  WHERE d.OWNER in (‘TPCC’,’CURRY4’,’TEST24’,’JJF’,’CURRY3’,’CURRY2’,’CURRY1’,’HR’,’TEST’,’HZMCMEMAUDIT’,’CURRY’,’CURRY5’,’TEST1’,’JTEST’,’TEST02’,’ZJJBUNI2REP’,’ZJJBUNI2’,’QMCB_NEW’,’JBGC’)

    and d.OWNER not in (‘PUBLIC’)

    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

  ORDER BY D.OWNER ,COUNT(1)   desc;

 

備份物件資訊,方便無效物件比對

無效物件統計

spool invald.lst

select   owner,object_name,object_type,status from dba_objects where status<>‘VALID’   ;

select count(*),status from   dba_objects group by status;

select   owner,object_name,object_type,status from dba_objects where status<>‘VALID’   and owner in ;

select count(*),status from   dba_objects where owner in (‘TPCC’,’CURRY4’,’TEST24’,’JJF’,’CURRY3’,’CURRY2’,’CURRY1’,’HR’,’TEST’,’HZMCMEMAUDIT’,’CURRY’,’CURRY5’,’TEST1’,’JTEST’,’TEST02’,’ZJJBUNI2REP’,’ZJJBUNI2’,’QMCB_NEW’,’JBGC’)   group by status;

spool off

create table invalid_object_201702   as select * from dba_objects where status<>‘VALID’;

 

 

select count(*),status from dba_objects where owner in (‘TPCC’,’CURRY4’,’TEST24’,’JJF’,’CURRY3’,’CURRY2’,’CURRY1’,’HR’,’TEST’,’HZMCMEMAUDIT’,’CURRY’,’CURRY5’,’TEST1’,’JTEST’,’TEST02’,’ZJJBUNI2REP’,’ZJJBUNI2’,’QMCB_NEW’,’JBGC’)   and object_type like ‘PACKAGE%’ group by status;

 

確定 SYS SYSTEM 下是否存在業務物件

SYS SYSTEM 重複物件檢查

SQL> select object_name, object_type from dba_objects where   (object_name,object_type) in (select object_name,object_type from dba_objects   where owner = ‘SYS’) and owner = ‘SYSTEM’;

 

SYSTEM 表空間下是否存在物件

select   owner,segment_name,segment_type,tablespace_name from dba_segments where   tablespace_name in (‘SYSTEM’,’SYSAUX’) and owner in (‘TPCC’,’CURRY4’,’TEST24’,’JJF’,’CURRY3’,’CURRY2’,’CURRY1’,’HR’,’TEST’,’HZMCMEMAUDIT’,’CURRY’,’CURRY5’,’TEST1’,’JTEST’,’TEST02’,’ZJJBUNI2REP’,’ZJJBUNI2’,’QMCB_NEW’,’JBGC’);

 

確定平臺位元組序

col platform_name for a40

select tp.platform_name,   tp.endian_format

  from   v$transportable_platform tp

where tp.platform_name in (‘Linux   x86 64-bit’, ‘AIX-Based Systems (64-bit)’);

 

判斷表空間是否自包含

當檢測到表空間不是自包含時,需要遷移跨表空間物件至同一表空間。 詳見附錄一:

execute sys.dbms_tts.transport_set_check(‘CURRY,CURRY1,CURRY2,CURRY3,CURRY4,CURRY5,USERS’,true);

col violations for a150

select * from   sys.transport_set_violations;

 

獲取需傳輸的表空間

set serveroutput on

declare

    tsname varchar(30);

    i number := 0;

begin

    dbms_output.put(‘tablespaces=‘);

    for ts in 

      (select tablespace_name from dba_tablespaces

     where tablespace_name not in (‘SYSTEM’,’SYSAUX’)  

       and contents = ‘PERMANENT’

     order by tablespace_name)

    loop

      if (i!=0) then

      dbms_output.put_line(tsname||’,’);

      end if;

      i := 1;

      tsname := ts.tablespace_name;

    end loop;

    dbms_output.put_line(tsname);

    dbms_output.put_line(‘‘);

end;

/

 

端環境準備( 目標端)

配置到生產的NFS

LINUX 端準備

開啟NFS

service nfs start

chkconfig nfs on

vi /etc/exports

/backup 192.168.200.*(rw,sync,all_squash)

exportfs –a

showmount -e

 

AIX 源端配置:

檢查aix 是否啟用自定義埠

# nfso -a|grep nfs_use_reserved_ports

啟用aix 自定義埠

nfso -p -o nfs_use_reserved_ports=1

# showmount -e

# mount -o cio,rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,vers=3,timeo=600 192.168.200.90:/backup /backup

 

至此,源端和目標端均有/backup 目錄

注意:在源端掛載目標端 NFS 檔案系統時,在目標端 /etc/exports 中需指定源端物理 IP ,否則會造成源端掛載失敗

安裝叢集建立資料庫

安裝最新11.2.0.4 叢集+ 最新PSU

建立資料庫,原則上和生產保持一致名稱,特別注意字符集,需要和生產保持一致。

注意:需保持源端和目標端資料庫 service_names 引數設定一致,否則會造成應用連線失敗

 

準備XTTS 介質

上傳介質到生產源端環境/backup 目錄下

 

生產端操作

生產庫必須滿足歸檔開啟策略

 

建立directory

源端建立directory 指向源端資料檔案所在的位置

create directory sourcedir as ‘+DATA/XXX/datafile’;

grant all on directory sourcedir to public;

 

編輯xtt.properties 檔案

$ mv xtt.properties xtt.properties.bak

$ touch xtt.properties

vi xtt.properties 填寫以下內容

tablespaces=CURRY,CURRY1,CURRY2,CURRY3,CURRY4,CURRY5,USERS

platformid=6

srcdir=SOURCEDIR

dstdir=DESTDIR

srclink=TTSLINK

dfcopydir=/backup

backupformat=/backup

stageondest=/backup

storageondest=+DATA

backupondest=+DATA

asm_home=/oracle/grid/crs_1

asm_sid=+ASM1

需要遷移的表空間

源端資料庫平臺引數, V$DATABASE.PLATFORM_ID

源端根據資料檔案所在目錄

目標端最終資料檔案所在的目錄

目標端連線源端建立的db link

源端rman 備份目錄

源端增量備份目錄

目標端存放備份目錄

目標端rman 恢復目錄

目標端增量恢復目錄

目標端asm home 目錄

目標端asm 例項名

 

第一條tablespaces 引數,命令參考指令碼:

declare  

  tsname varchar(30);

  i number := 0;

begin  

  dbms_output.put(‘tablespaces=‘);

  for   ts in 

    (select tablespace_name from   dba_tablespaces

     where tablespace_name not in (‘SYSTEM’,’SYSAUX’)  

       and contents = ‘PERMANENT’

     order by tablespace_name)

  loop

    if (i!=0) then

      dbms_output.put_line(tsname||’,’);

    end   if;

    i := 1;

    tsname := ts.tablespace_name;

  end loop;

  dbms_output.put_line(tsname);

  dbms_output.put_line(‘‘);

end;  

/

 

由於是NFS 掛載的,所以所有檔案都不需要複製到目標端


目標端操作

建立目標directory

destdir 目標端資料檔案最終所在的目錄:

create directory destdir as ‘+DATA/XXX’;

 

建立DATA_PUMP_DIR

DATA_PUMP_DIR 目錄建立用於後設資料匯入

create or replace directory   as ‘/backup’;

grant all on directory DATA_PUMP_DIR to   public;

 

建立DB_LINK

xtts =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.71)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XXX)

    )

  )

建立連線源端資料庫的dblink

      

備註:

如果users 表空間已經存在了,這裡把target 端的users 表空間重新命名一下就可以了:

alter tablespace users rename to users01;

 

臨時建立所有表空間語句

臨時建立所有表空間語句,用於從生產匯入所有的許可權及使用者

相關指令碼如下:(在生產端執行並生產檔案)

set heading off feedback off trimspool on   linesize 500

spool tts_create_ts.sql

prompt /* ===================== */

prompt /* Create user tablespaces */

prompt /* ===================== */

select ‘create TABLESPACE ‘ ||   tablespace_name ||

         ‘ DATAFILE ‘ ||’’’+DATA/XXX/’||tablespace_name||’.dbf’’’||’ size 10M autoextend on;’

     from dba_tablespaces

     where tablespace_name not in (‘SYSTEM’,’SYSAUX’)

        and contents = ‘PERMANENT’;

spool off

 


匯入生產庫使用者及許可權

同步使用者和許可權

impdp \’/ as sysdba\’ DIRECTORY=DATA_PUMP_DIR   LOGFILE=dp_userimp01.log NETWORK_LINK=ttslink FULL=y   INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE

 

 

刪除表空間:該操作要明確,是在目標端操作的,即新環境端,操作前謹記,再三確認,操作前必須關閉生產庫所有介面,只保留目標端,並且操作完後清理掉 tts_drop_ts.sql

set   heading off feedback off trimspool on linesize 500

spool   tts_drop_ts.sql

prompt   /* ===================== */

prompt   /* Drop user tablespaces */

prompt   /* ===================== */

select   ‘DROP TABLESPACE ‘ || tablespace_name ||

       ‘ INCLUDING CONTENTS AND DATAFILES;’

   from dba_tablespaces

   where tablespace_name not in (‘SYSTEM’,’SYSAUX’)  

      and contents = ‘PERMANENT’;

spool   off

刪除$ rm tts_drop_ts.sql 指令碼

 

 

刪除目標端USERS 表空間

為使得源端USERS 表空間順利傳輸過來,刪除USERS 表空間:

alter database default tablespace SYSAUX;

SQL> drop tablespace users including contents and datafiles;

 

設定 TMPDIR

源端(生產端):export TMPDIR=/home/oracle/tmp

目標端(新庫):export TMPDIR=/home/oracle/tmp

  

資料全量傳輸階段

資料全量傳輸採用兩種方式:

Phase 2A - dbms_file_transfer Method

Phase 2B - RMAN Backup Method

增量階段發現還是應該開啟塊跟蹤會比較好,加快速度,應該在全同步之前開啟

alter database enable block change tracking using file ‘+DATA/trace.log’;

alter database disable block change tracking;

 

 

生成增量所需檔案

[oracle@source]$ export XTTDEBUG=1

[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -S

xttplan.txt             # 記錄資料檔案 SCN

xttnewdatafiles.txt      # 記錄資料檔案位置

 

手動 rman 複製方式傳輸檔案

查詢語句源端查詢,複製source 端資料檔案至NFS 共享目錄/backup

select ‘copy datafile ‘||’’’’||file_name||’’’’||’   to ‘||’’’’||’/backup/’||substr(file_name,instr(file_name,’/’,-1)+1)||’’’;’   from dba_data_files;

 

copy datafile ‘+DATA/XXX/users01.dbf’   to ‘/backup/users01.dbf’;

copy datafile ‘+DATA/XXX/curry01.dbf’   to ‘/backup/curry01.dbf’;

copy datafile ‘+DATA/XXX/curry1.dbf’   to ‘/backup/curry1.dbf’;

copy datafile ‘+DATA/XXX/curry2.dbf’   to ‘/backup/curry2.dbf’;

copy datafile ‘+DATA/XXX/curry3.dbf’   to ‘/backup/curry3.dbf’;

copy datafile ‘+DATA/XXX/curry4.dbf’   to ‘/backup/curry4.dbf’;

copy   datafile ‘+DATA/XXX/curry5.dbf’ to ‘/backup/curry5.dbf’;

 

目標端手動 convert 資料檔案

修改資料檔案許可權

chown grid.oinstall *.dbf

 

查詢語句源端查詢,在目標端執行convert 操作

select   ‘CONVERT FROM PLATFORM ‘||’’’’||’AIX-Based Systems (64-bit)’||’’’’||’   PARALLELISM 10’||’ datafile ‘||’’’’||’/backup/’||SUBSTR(NAME,INSTR(NAME,’/’,-1)+1)||’’’’||’   format ‘||’’’’||’+DATA/XXX/’||SUBSTR(NAME,INSTR(NAME,’/’,-1)+1)||’’’;’ from   v$datafile;

 

CONVERT   FROM PLATFORM ‘AIX-Based Systems (64-bit)’ PARALLELISM 10 datafile ‘/backup/users01.dbf’   format ‘+DATA/XXX/users01.dbf’;

CONVERT   FROM PLATFORM ‘AIX-Based Systems (64-bit)’ PARALLELISM 10 datafile ‘/backup/curry01.dbf’   format ‘+DATA/XXX/curry01.dbf’;

CONVERT   FROM PLATFORM ‘AIX-Based Systems (64-bit)’ PARALLELISM 10 datafile ‘/backup/curry1.dbf’   format ‘+DATA/XXX/curry1.dbf’;

CONVERT   FROM PLATFORM ‘AIX-Based Systems (64-bit)’ PARALLELISM 10 datafile ‘/backup/curry2.dbf’   format ‘+DATA/XXX/curry2.dbf’;

CONVERT   FROM PLATFORM ‘AIX-Based Systems (64-bit)’ PARALLELISM 10 datafile ‘/backup/curry3.dbf’   format ‘+DATA/XXX/curry3.dbf’;

CONVERT   FROM PLATFORM ‘AIX-Based Systems (64-bit)’ PARALLELISM 10 datafile ‘/backup/curry4.dbf’   format ‘+DATA/XXX/curry4.dbf’;

CONVERT   FROM PLATFORM ‘AIX-Based Systems (64-bit)’ PARALLELISM 10 datafile ‘/backup/curry5.dbf’   format ‘+DATA/XXX/curry5.dbf’;

 

前滾階段(增量恢復階段)

源端執行

檢查增量備份的路徑,比如 xtt.properties 檔案的路徑資訊許可權等

 

源端增量備份階段:

$export XTTDEBUG=1

$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i

 

複製或替換源端以下檔案至目標端

xttnewdatafiles.txt

xttplan.txt

tsbkupmap.txt

 

 

目標端執行

修改增量備份片許可權

chown grid.oinstall *_1_1

 

目標端增量應用日誌:

$export XTTDEBUG=1

$ $ORACLE_HOME/perl/bin/perl xttdriver.pl   -r

 

 

源端確認下一個增量的SCN

源端執行:

[oracle@source]$   $ORACLE_HOME/perl/bin/perl xttdriver.pl -s

 

繼續輪詢增量恢復

如果還需要增量恢復,重複執行7.1~7.3


最後一次增量前滾

源端表空間READ ONLY

生產端執行如下指令碼,獲取READ ONLY 指令碼

 

指令碼如下:

set heading off feedback off trimspool on   linesize 500

spool tts_tsro.sql

prompt /*   =================================== */

prompt /* Make all user tablespaces READ   ONLY */

prompt /*   =================================== */

select ‘ALTER TABLESPACE ‘ ||   tablespace_name || ‘ READ ONLY;’

     from dba_tablespaces

     where tablespace_name not in (‘SYSTEM’,’SYSAUX’)

        and contents = ‘PERMANENT’;

spool off

 

檢查狀態:

SQL> select tablespace_name,status,contents from dba_tablespaces;

 

最後一次增量備份

生產端執行:

[oracle@source]$   $ORACLE_HOME/perl/bin/perl xttdriver.pl -i

 

最後一次增量結束關閉塊跟蹤

alter database disable block change tracking;

 

目標端執行:

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl   xttdriver.pl -r

 

資料同步

後設資料同步

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl   xttdriver.pl -e

相關語句如下:(transport_tablespaces transport_datafiles 根據實際情況修改)

impdp "‘"/   as sysdba"‘" directory=DATA_PUMP_DIR logfile=tts_imp.log \

network_link=ttslink   transport_full_check=no \

transport_tablespaces=CURRY,CURRY1,CURRY2,CURRY3,CURRY4,CURRY5,USERS   \

transport_datafiles=‘+DATA/XXX/curry01.dbf’,’+DATA/XXX/curry1.dbf’,’+DATA/XXX/curry2.dbf’,’+DATA/XXX/curry3.dbf’,’+DATA/XXX/curry4.dbf’,’+DATA/XXX/curry5.dbf’,’+DATA

/nocdb/users01.dbf’  

 

測試過程由於各種原因導致時間不夠,因此為了加快進度, 沒有將統計資訊匯入,採用後續手工統計

 

獲取transport_tablespaces

declare

    tsname varchar(30);

  i   number := 0;

begin

    dbms_output.put(‘transport_tablespaces=‘);

    for ts in 

    (select tablespace_name from dba_tablespaces  

       where tablespace_name not in (‘SYSTEM’,’SYSAUX’)

         and contents = ‘PERMANENT’

       order by tablespace_name)

    loop

      if (i!=0) then

        dbms_output.put_line(tsname||’, \’);

      end if;

    i := 1;

      tsname := ts.tablespace_name;

    end loop;

    dbms_output.put_line(tsname);

    dbms_output.put_line(‘‘);

end;

/

 

獲取transport_datafiles

declare

    fname varchar(513);

  i   number := 0;

begin

    dbms_output.put(‘transport_datafiles=‘);

  for df in

      (select file_name from dba_tablespaces a, dba_data_files b

       where a.tablespace_name = b.tablespace_name

         and a.tablespace_name not in (‘SYSTEM’,’SYSAUX’)

         and contents = ‘PERMANENT’

       order by a.tablespace_name)

    loop

      if (i!=0) then

        dbms_output.put_line(‘‘‘+DATA/XXX/’||substr(fname,instr(fname,’/’,-1)+1)||’’’,   \’);

      end if;

      i := 1;

      fname := df.file_name;

    end loop;

    dbms_output.put_line(‘‘‘+DATA/XXX/’||substr(fname,instr(fname,’/’,-1)+1)||’’’’);  

    dbms_output.put_line(‘‘);

end;

/

 

轉換結束,檢查目標端表空間、資料檔案狀態:

col file_name for a40

set linesize 1000

select tablespace_name,file_name,status   from dba_data_files;

 

col name for a40

select name,status from v$datafile;

 

 

目標庫表空間置成 READ WRITE 模式

目標端執行如下指令碼,獲取READ WRITE 指令碼

指令碼如下:

set heading off feedback off trimspool on   linesize 500

spool tts_tsro.sql

prompt /*   ==================================== */

prompt /* Make all user tablespaces READ   WRITE */

prompt /*   ==================================== */

select ‘ALTER TABLESPACE ‘ ||   tablespace_name || ‘ READ WRITE;’

     from dba_tablespaces

     where tablespace_name not in (‘SYSTEM’,’SYSAUX’)

        and contents = ‘PERMANENT’;

spool off

檢查狀態:

SQL> select tablespace_name,status,contents from dba_tablespaces;

 

臨時表同步& 資料同步

生產端臨時表格處理:

 

生產端獲取建表語句

set echo off;

Set pages 999;

set long 90000;

spool get_tmptable_ddl.sql

select dbms_metadata.get_ddl(‘TABLE’,’TMPTAB’,’BUDGET’) FROM dual;

.

.

.

.

 

 

此時手動授權 GRANT 語句,會發現很多物件不存在,在後設資料同步之後還是有 部分物件缺失,所以再導一次相關物件:

 

進行缺失物件匯入時,優先匯入DB_LINK

impdp \’/   as sysdba\’ DIRECTORY=DATA_PUMP_DIR LOGFILE=dp_userimp.log   NETWORK_LINK=ttslink FULL=y INCLUDE=DB_LINK

 

 

確認DB_LINK 匯入無誤後,再匯入其他物件

impdp   \’/ as sysdba\’ DIRECTORY=DATA_PUMP_DIR LOGFILE=dp_userimp.log   NETWORK_LINK=ttslink FULL=y INCLUDE=INDEX,FUNCTION,VIEW,SYNONYM,PACKAGE   BODY,SEQUENCE,PACKAGE,JOB,TRIGGER,PROCEDURE

 

確認缺少物件匯入之後,進行許可權GRANT 匯入

impdp   \’/ as sysdba\’ DIRECTORY=DATA_PUMP_DIR LOGFILE=dp_userimp01.log   NETWORK_LINK=ttslink FULL=y INCLUDE= GRANT

 

在測試遷移階段,發現即使進行許可權 GRANT 完全匯入之後,還是有部分基於 sys 下一些資料字典許可權沒有同步至目標庫。此時需要手動執行 grant 語句授權。

@user_privs_collect.sql

 

待相關物件同步好之後,再開始進行資料校驗。

 

目標庫統計資訊收集

資料校驗完全一致後,收集目標庫統計資訊

exec   dbms_stats.gather_database_stats(estimate_percent=>10,Degree=>8,Cascade=>TRUE,Granularity=>‘ALL’);

exec dbms_stats.gather_dictionary_stats(estimate_percent=>100,Degree=>8,Cascade=>TRUE,Granularity=>‘ALL’);

exec   dbms_stats.gather_fixed_objects_stats;

 

備忘錄

源庫禁用塊跟蹤

測試遷移結束之後,源庫禁用塊跟蹤。正式遷移跳過此步。

select * from v$block_change_tracking;

alter database disable block change tracking;

 

源端表空間READ WRITE

READ WRITE 指令碼如下:

spool tts_tsro.sql

prompt /* ==================================== */

prompt /* Make all user tablespaces READ WRITE */

prompt /* ==================================== */

select ‘ALTER TABLESPACE ‘ || tablespace_name || ‘ READ WRITE;’

   from dba_tablespaces

   where tablespace_name not   in (‘SYSTEM’,’SYSAUX’)

      and contents = ‘PERMANENT’;

spool off

SQL>

 

源庫JOB 禁用& 啟用

由於遷移時間是月初,時間比較敏感,大量JOB 可能都在該時段觸發,與客戶和應用確認是否要禁用生產庫JOB

alter system set job_queue_processes=0 scope=both sid=‘*’;

 

目標庫JOB 禁用& 啟用

為了防止JOB 排程而引起的資料校驗不一致,在遷移前禁用所有JOB

alter system set job_queue_processes=0 scope=both sid=‘*’;

 

遷移結束,應用測試結束,三方確認無誤後,開啟JOB

alter system set job_queue_processes=1000 scope=both sid=‘*’;


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

相關文章