單例項資料庫expdp遷移到RAC庫

Oracle10g 單例項資料庫expdp遷移到三節點的RAC庫
1. 首先準備安裝RAC的軟體：

Red Hat Enterprise Linux Server release 5.3 (Tikanga) X86_64Bit
Kernel: 2.6.18-128.el5 #1 SMP

ocfs2-2.6.18-128.el5-1.4.1-1.el5.x86_64.rpm
ocfs2-tools-1.4.1-1.el5.x86_64.rpm
ocfs2console-1.4.1-1.el5.x86_64.rpm

10201_clusterware_linux_x86_64.cpio
10201_database_linux_x86_64.cpio
p6810189_10204_Linux-x86-64.zip    (Including 10204_clusterware)
升級的時候先升級CRS然後是DB .

glibc-2.5-24.x86_64.rpm (rpm -q glibc)
glibc-common-2.5-24.x86_64 (rpm -q glibc-common)
glibc-devel-2.5-24.i386.rpm (rpm -q glibc-devel) (此32bit rpm 必須安裝)
glibc-devel-2.5-24.x86_64.rpm (rpm -q glibc-devel)
libXp-1.0.0-8.1.el5.i386.rpm （rpm -q libXp）（32bit rpm 必須安裝）
libXp-1.0.0-8.1.el5.x86_64.rpm （rpm -q libXp）
binutils-2.17.50.0.6-6.el5.x86_64.rpm (rpm -q binutils)
compat-db-4.2.52-5.1.x86_64.rpm (rpm -q compat-db)
control-center-2.16.0-16.el5.x86_64.rpm (rpm -q control-center)
gcc-4.1.2-42.el5.x86_64.rpm (rpm -q gcc)
gcc-c++-4.1.2-42.el5.x86_64.rpm (rpm -q gcc-c++)
libstdc++-4.1.2-42.el5.x86_64.rpm (rpm -q libstdc++)
libstdc++-devel-4.1.2-42.el5.x86_64.rpm (rpm -q libstdc++-devel)
make-3.81-3.el5.x86_64.rpm (rpm -q make)
ksh-20080202-2.el5.x86_64.rpm (rpm -q ksh)
sysstat-7.0.2-1.el5.x86_64.rpm (rpm -q sysstat)
gnome-screensaver-2.16.1-8.el5.x86_64.rpm (rpm -q gnome-screensaver)
libaio-devel-0.3.106-3.2.x86_64.rpm (rpm -q libaio-devel)
libaio-0.3.106-3.2.x86_64.rpm (rpm -q libaio)

準備安裝前配置引數：

/etc/sysctl.conf

# Added for Oracle 10g RAC
kernel.shmall = 16777216
kernel.shmmax = 68719476736
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

備註：
Physical Mem = 64G
shmall = shmmax/4K
set shmmax > sga_max_size
Note : 4K is linux memory page size

2. 安裝完成CRS, Oracle 10.2.0.1 資料庫，開始升級CRS到10.2.0.4，然後升級資料庫到10.2.0.4 ；
然後執行NETCA 配置監聽，最後DBCA建庫。

3. 建立使用者使用的Tablespace, 根據不同的靜態及動態表劃分不同的tablespace . 使用者表空間採用LMT本地管理，
使用ASSM 自動管理方式(SEGMENT SPACE MANAGEMENT AUTO),並開啟FLASHBACK . 類似：

CREATE TABLESPACE LOG_DATA DATAFILE
'/ocfs_data1/mxdell/log_data01.dbf' SIZE 2048064K AUTOEXTEND OFF,
'/ocfs_data1/mxdell/log_data02.dbf' SIZE 2048064K AUTOEXTEND OFF,
'/ocfs_data1/mxdell/log_data03.dbf' SIZE 2048064K AUTOEXTEND OFF,
'/ocfs_data1/mxdell/log_data04.dbf' SIZE 2048064K AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 20M
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

4. 如果有大資料量Table需要從普通錶轉化為分割槽表，遷移是一個機會。給分割槽表及分割槽索引單獨的表空間。

CREATE TABLESPACE PART_D_TS1 DATAFILE
'/ocfs_data1/mxdell/part_d_ts1_1.dbf' SIZE 2048064K AUTOEXTEND OFF,
'/ocfs_data1/mxdell/part_d_ts1_2.dbf' SIZE 2048064K AUTOEXTEND OFF,
'/ocfs_data1/mxdell/part_d_ts1_3.dbf' SIZE 2048064K AUTOEXTEND OFF,
'/ocfs_data1/mxdell/part_d_ts1_4.dbf' SIZE 2048064K AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 50M
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TABLESPACE PART_D_TS2 DATAFILE
'/ocfs_data1/mxdell/part_d_ts2_1.dbf' SIZE 2048064K AUTOEXTEND OFF,
'/ocfs_data1/mxdell/part_d_ts2_2.dbf' SIZE 2048064K AUTOEXTEND OFF,
'/ocfs_data1/mxdell/part_d_ts2_3.dbf' SIZE 2048064K AUTOEXTEND OFF,
'/ocfs_data1/mxdell/part_d_ts2_4.dbf' SIZE 2048064K AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 50M
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

5. 建立使用者。
CREATE USER DFMS
IDENTIFIED BY VALUES 'A52A32FF1E905156' -- DFMS$MX
DEFAULT TABLESPACE LOG_DATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for DFMS
GRANT DBA TO DFMS;
GRANT CONNECT TO DFMS;
GRANT RESOURCE TO DFMS;
ALTER USER DFMS DEFAULT ROLE ALL;
-- 1 System Privilege for DFMS
GRANT UNLIMITED TABLESPACE TO DFMS;
-- 8 Tablespace Quotas for DFMS
ALTER USER DFMS QUOTA UNLIMITED ON SN_IDX;
ALTER USER DFMS QUOTA UNLIMITED ON BASE_IDX;
ALTER USER DFMS QUOTA UNLIMITED ON SN_DATA;
ALTER USER DFMS QUOTA UNLIMITED ON LOG_IDX;
ALTER USER DFMS QUOTA UNLIMITED ON WIP_DATA;
ALTER USER DFMS QUOTA UNLIMITED ON LOG_DATA;
ALTER USER DFMS QUOTA UNLIMITED ON BASE_DATA;
ALTER USER DFMS QUOTA UNLIMITED ON WIP_IDX;

ALTER USER DFMS QUOTA UNLIMITED ON PART_D_TS1;
ALTER USER DFMS QUOTA UNLIMITED ON PART_I_TS1;
ALTER USER DFMS QUOTA UNLIMITED ON PART_D_TS2;
ALTER USER DFMS QUOTA UNLIMITED ON PART_I_TS2;

CREATE USER MES_MX
IDENTIFIED BY VALUES '65A5BAC4F618E0A3' -- jrzsfc$pcebg
DEFAULT TABLESPACE LOG_DATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 1 Role for MES_MX
GRANT SELECT_CATALOG_ROLE TO MES_MX;
ALTER USER MES_MX DEFAULT ROLE ALL;
-- 3 System Privileges for MES_MX
GRANT DEBUG ANY PROCEDURE TO MES_MX;
GRANT DEBUG CONNECT SESSION TO MES_MX;
GRANT CREATE SESSION TO MES_MX;

6. 配置spfile重要引數以及修改system , sysaux等表空間屬性。

db_block_size = 16384
db_files = 300
db_recovery_file_dest_size = 4G
job_queue_processes = 50
sga_target =55G
sga_max_size = 55G
timed_statistics = TRUE
processes = 1500
pga_aggregate_target = 3G
optimizer_mode = ALL_ROWS
optimizer_index_cost_adj = 100
open_cursors = 600
log_buffer = 5M
workarea_size_policy = AUTO

7. 配置redo log 多路傳輸及修改大小

alter database add logfile thread 1
     group 7    ('/ocfs_ctrl_redo/mxdell/redo07.log','/ocfs_data/mxdell/redo07.log') size 100M,
     group 8    ('/ocfs_ctrl_redo/mxdell/redo08.log','/ocfs_data/mxdell/redo08.log') size 100M,
     group 9    ('/ocfs_ctrl_redo/mxdell/redo09.log','/ocfs_data/mxdell/redo09.log') size 100M,
     group 10   ('/ocfs_ctrl_redo/mxdell/redo10.log','/ocfs_data/mxdell/redo10.log') size 100M,
     group 11   ('/ocfs_ctrl_redo/mxdell/redo11.log','/ocfs_data/mxdell/redo11.log') size 100M,
     group 12   ('/ocfs_ctrl_redo/mxdell/redo12.log','/ocfs_data/mxdell/redo12.log') size 100M;

alter database add logfile thread 2
     group 13    ('/ocfs_ctrl_redo/mxdell/redo13.log','/ocfs_data/mxdell/redo13.log') size 100M,
     group 14    ('/ocfs_ctrl_redo/mxdell/redo14.log','/ocfs_data/mxdell/redo14.log') size 100M,
     group 15    ('/ocfs_ctrl_redo/mxdell/redo15.log','/ocfs_data/mxdell/redo15.log') size 100M,
     group 16    ('/ocfs_ctrl_redo/mxdell/redo16.log','/ocfs_data/mxdell/redo16.log') size 100M,
     group 17    ('/ocfs_ctrl_redo/mxdell/redo17.log','/ocfs_data/mxdell/redo17.log') size 100M,
     group 18    ('/ocfs_ctrl_redo/mxdell/redo18.log','/ocfs_data/mxdell/redo18.log') size 100M;

ALTER system switch logfile;
ALTER system switch logfile;
ALTER system switch logfile;
ALTER system switch logfile;

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;

8. 停機，準備開始匯出匯入資料。

注意點：

A. 匯出匯入之前Check 每個table之間是否有嚴格的關聯，比如PK, FK , 使用如下SQL查詢，如果有，DISABLE先。
select * from all_constraints where constraint_type='R' and WNER='DFMS' ;

B. 自動歸檔模式下，匯入資料的時候注意手工清理歸檔。

C. 最重要也是做容易忽視的一點： exp 使用了 rows=n ，以及expdp 使用了 content=metadata_only 都會導致
匯入後再次分析表的時候報ora-20005這個錯誤。那麼我們需要在exp rows=n 的時候加入statistics=none 。
或者在expdp content=metadata_only的時候使用 exclude=(table_statistics,index_statistics) 來避免這個
錯誤的發生。也即是匯出沒有資料的結構的時候不需要匯出統計資訊。如果匯出後面匯入後，統計資訊會出於鎖定
狀態，必須使用 execute DBMS_STATS.UNLOCK_TABLE_STATS('','

'); 來幫所有table解掉
統計資訊上的鎖，否則不能再次統計。

開始匯出：

在源庫上執行

SQL>create directory dmpfiledir as '/data1/expbak';
SQL>grant read,write on directory dmpfiledir to dfms ;

$ expdp system/system directory=dmpfiledir content=metadata_only dumpfile=dfms_metadata.dmp
schemas=dfms exclude=(table_statistics,index_statistics)

$expdp system/system directory=dmpfiledir content=data_only dumpfile=dfms_data.dmp schemas=dfms

拷貝兩個dmp檔案到目標庫，在目標庫上執行：

SQL>create directory dmpfiledir as '/ocfs_data/dmpfile';
SQL>grant read,write on directory dmpfiledir to dfms ;

$impdp system/system directory=dmpfiledir content=metadata_only dumpfile=dfms_metadata.dmp schemas=dfms
建立好Table結構後，注意：A. disable掉所有的trigger 及定時執行的一些Job ; B. 如果你需要修改大資料量Table為分割槽表，
這裡可以drop原來的Table, 建立分割槽Table(但是Index最好在導完資料後再建立) 。

$impdp system/system directory=dmpfiledir content=data_only dumpfile=dfms_data.dmp schemas=dfms

匯入資料之後，建立大分割槽表的Index(因為他們是重新定義的)及賦予許可權，建立Trigger。
最後開啟所有Table的Trigger，重新建立Job(因為Job的時間性非常苛刻，所以必須最後建立)

最後Check所有物件的同義詞，是否存在invalid的procedure, function, packages, job 等。

9. 非常重要的一步：Table, Index 統計資訊，因為匯入的時候沒有包含統計資訊，所以需要重新收集統計。

exec dbms_stats.gather_schema_stats(ownname => 'DFMS',estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns sizeauto',
cascade=>TRUE,
degree => 8 ) ;

如果前面expdp的時候沒有加入 exclude=(table_statistics,index_statistics) ，這時候你就會碰到類似下面的錯誤：

ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1

辦法就是解鎖：

找到哪些table,index
select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and
owner='DFMS' ;

批量修改為unlock .

select 'exec dbms_stats.unlock_table_stats(''DFMS'','''||table_name||'''); ' from sys.dba_tab_statistics
where stattype_locked is not null and wner='DFMS' ;

10. 修改客戶端連線，測試。注意檢查程式，DBLINK等。

--三節點

MX_DELL_RAC =
(DESCRIPTION =
    (FAILOVER=ON)
    (LOAD_BALANCE = ON)
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.67.181)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.67.182)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.67.183)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = mxdell)
      (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC))
    )
)

--單機

MX_DELL_RAC3 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.67.183)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mxdell)
      (INSTANCE_NAME = mxdell1)
    )
)

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