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('','
开始导出:
在源库上执行
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/,如需转载,请注明出处,否则将追究法律责任。
![user_pic_default.png](http://blog.itpub.net/images/user_pic_default.png)
<%=items[i].content%>
<%if(items[i].items.items.length) { %><%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
转载于:http://blog.itpub.net/35489/viewspace-621079/