单实例数据库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('','


开始导出:


在源库上执行

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
请登录后发表评论 登录
全部评论
<%=items[i].createtime%>

<%=items[i].content%>

<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%=items[i].items.items[j].createtime%> 回复

<%=items[i].items.items[j].username%>   回复   <%=items[i].items.items[j].tousername%><%=items[i].items.items[j].content%>

<%}%> <%if(items[i].items.total > 5) { %>
还有<%=items[i].items.total-5%>条评论 ) data-count=1 data-flag=true>点击查看
<%}%>
<%}%> <%}%>

转载于:http://blog.itpub.net/35489/viewspace-621079/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值