Oracle 数据迁移

42日43

oracle 数据库迁移

########################################################################################
########################################################################################

在源库中导出数据（备份）

exp OMS_lvnain01/OMS_lvnain01 owner=OMS_lvnain01 file=/home/oracle/gaotest/OMS_lvnain01.dmp
exp OMS_lvnain02/OMS_lvnain02 owner=OMS_lvnain02 file=/home/oracle/gaotest/OMS_lvnain02.dmp
exp OMS_lvnain03/OMS_lvnain03 owner=OMS_lvnain03 file=/home/oracle/gaotest/OMS_lvnain03.dmp

exp PORTAL_lvnain01/PORTAL_lvnain01 owner=PORTAL_lvnain01 file=/home/oracle/gaotest/PORTAL_lvnain01.dmp
exp PORTAL_lvnain02/PORTAL_lvnain02 owner=PORTAL_lvnain02 file=/home/oracle/gaotest/PORTAL_lvnain02.dmp
exp PORTAL_lvnain03/PORTAL_lvnain03 owner=PORTAL_lvnain03 file=/home/oracle/gaotest/PORTAL_lvnain03.dmp

########################################################################################
########################################################################################
把导出的备份数据传送到迁移的新服务器上
cd /home/oracle/gaotest/
scp -P 6022 ./*  root@192.168.35.178:/data/oracle/gaotest/oms  

cd cd /home/oracle/gaotest/
scp -P 6022 ./*  root@192.168.35.178:/data/oracle/gaotest/portal

########################################################################################
########################################################################################

在新的服务器上创建数据库表空间，用户的等

1.OMS_lvnain01（数据文件大小2G）
create tablespace OMS_lvnain01 logging  datafile '/data/oracle/app/oracle/OMS_lvnain01.dbf' size 2000m autoextend on next 2000m maxsize unlimited;                                          ##这个表结构的OMS_lvnain01.dbf被我删除，但是内存不变
create user c##OMS_lvnain01 identified by OMS_lvnain01 default tablespace OMS_lvnain01;
grant connect,resource,dba to c##OMS_lvnain01;
==========
#drop tablespace OMS_lvnain01;

2. OMS_lvnain02（数据文件大小5G）
create tablespace OMS_lvnain02 logging  datafile '/data/oracle/app/oradata/OMS_lvnain02.dbf' size 5000m autoextend on next 5000m maxsize unlimited;
create user c##OMS_lvnain02 identified by OMS_lvnain02 default tablespace OMS_lvnain02;
grant connect,resource,dba to c##OMS_lvnain02;
==========
3.OMS_lvnain03（数据文件大小6G）
create tablespace OMS_lvnain03 logging  datafile '/data/oracle/app/oradata/OMS_lvnain03.dbf' size 6000m autoextend on next 6000m maxsize unlimited;
create user c##OMS_lvnain03 identified by OMS_lvnain03 default tablespace OMS_lvnain03;
grant connect,resource,dba to c##OMS_lvnain03;
#drop tablespace c##OMS_lvnain03;
==========
4.PORTAL_lvnain01（数据文件大小4G）
create tablespace PORTAL_lvnain01 logging  datafile '/data/oracle/app/oradata/PORTAL_lvnain01.dbf' size 4000m autoextend on next 4000m maxsize unlimited;
create user PORTAL_lvnain01 identified by PORTAL_lvnain01 default tablespace PORTAL_lvnain01;
grant connect,resource,dba to PORTAL_lvnain01;
==========
5.PORTAL_lvnain02（数据文件大小2G）
create tablespace PORTAL_lvnain02 logging  datafile '/data/oracle/app/oradata/PORTAL_lvnain02.dbf' size 5000m autoextend on next 5000m maxsize unlimited;
create user c##PORTAL_lvnain02 identified by PORTAL_lvnain02 default tablespace PORTAL_lvnain02;
grant connect,resource,dba to c##PORTAL_lvnain02;
==========
6.PORTAL_lvnain03（数据文件大小2G）
create tablespace PORTAL_lvnain03 logging  datafile '/data/oracle/app/oradata/PORTAL_lvnain03.dbf' size 5000m autoextend on next 5000m maxsize unlimited;
create user c##PORTAL_lvnain03 identified by PORTAL_lvnain03 default tablespace PORTAL_lvnain03;
grant connect,resource,dba to c##PORTAL_lvnain03;
########################################################################################
########################################################################################
########################################################################################
########################################################################################

往新服务器中导入数据

imp c##OMS_lvnain01/OMS_lvnain01 fromuser=c##OMS_lvnain01 touser=c##OMS_lvnain01 buffer=150009081 commit=y file=/data/oracle/gaotest/oms/OMS_lvnain01.dmp ignore=y

imp c##OMS_lvnain02/OMS_lvnain02 fromuser=c##OMS_lvnain02 touser=c##OMS_lvnain02 buffer=150009081 commit=y file=/data/oracle/gaotest/oms/OMS_lvnain02.dmp ignore=y

imp c##OMS_lvnain03/OMS_lvnain03 fromuser=c##OMS_lvnain03 touser=c##OMS_lvnain03 buffer=150009081 commit=y file=/data/oracle/gaotest/oms/OMS_lvnain03.dmp ignore=y


==============================
imp c##PORTAL_lvnain01/PORTAL_lvnain01 fromuser=c##PORTAL_lvnain01 touser=c##PORTAL_lvnain01 buffer=150009081 commit=y file=/data/oracle/gaotest/portal/PORTAL_lvnain01.dmp ignore=y


imp c##PORTAL_lvnain02/PORTAL_lvnain02 fromuser=c##PORTAL_lvnain02 touser=c##PORTAL_lvnain02  buffer=150009081 commit=y file=/data/oracle/gaotest/portal/PORTAL_lvnain02.dbf ignore=y

imp c##PORTAL_lvnain03/PORTAL_lvnain03 fromuser=c##PORTAL_lvnain03 touser=c##PORTAL_lvnain03  buffer=150009081 commit=y file=/data/oracle/gaotest/portal/PORTAL_lvnain03.dmp  ignore=y