单实例数据库expdp迁移到RAC库

最新推荐文章于 2025-04-07 09:14:18 发布

转载最新推荐文章于 2025-04-07 09:14:18 发布 · 337 阅读

文章标签：

#数据库 #操作系统 #网络

本文详述了从Oracle10g单实例数据库迁移到三节点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/，如需转载，请注明出处，否则将追究法律责任。