Oracle数据库采用exp/imp逻辑导出来完成迁移割接

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:352

说明:
数据库采用exp/imp逻辑导出来完成割接
(现场环境:原库是oracle9.2.0.4 x86-32位 on rhel4.6 x86-32位操作系统 ,要迁移到的新库是oracle10.2.0.1 x86-64位 for x86-64位 on rhel4.8 x86-64位操作系统,依据oracle 低版本exp,高版本imp原则,并测试通过。)
注意:在测试imp过程出现错误:IMP-00041: Warning: object created with compilation warnings,分析知stat用户中有调用harmony和mid另二个用户库中对象现象,在安装完成数据库后要在tnsname.ora保证有如下记录即可。

KDSWDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.46)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mseidb)
)
)

另创建好listener.ora
SID_LIST_MSEIDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mseidb)
(ORACLE_HOME = /opt/app/oracle/product/9.2.0)
(SID_NAME = mseidb)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = mseidb)
(ORACLE_HOME = /opt/app/oracle/product/9.2.0)
(SID_NAME = mseidb)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.46)(PORT = 1521))
)
)
)

割接步骤:

一、准备工作:新库创建表空间及用户过程
注意1、旧库导出导入新库时保证新旧库的表空间名称一样,但表空间的数据文件目录和名称不要求一致。

1、确定要导出的库
方法:查看用户和默认表空间的关系 (select username,default_tablespace from dba_users;)
MID STATDATA
STAT STATDATA
INFINITY RAP
HARMONY HARMONY1
PORTAL PORTAL
BLUES BLUES01

2、查看原有数据库文件(select name from v$datafile;)
/disk1/data1/rap01.dbf
/disk1/data1/statdata01.dbf
/disk1/data1/harmony01.dbf
/disk1/data1/portal01.dbf
/disk1/data1/blues01.dbf
/disk1/data1/statindex01.dbf
3、根据以上数据创建新库的用户sql如下:
ssh 192.168.1.48 (新数据库主机) 口令oracle/runway2012

将如下sql,保存为/opt/app/expbak/createuser.sql
sudo su - oacle
sqlplus /nolog
sqlplus / as sysdba;
@createuser.sql
----------start-----------------------
--harmony
create tablespace harmony1 logging datafile '/opt/app/oracle/disk1/data1/harmony01.dbf' size 2500m autoextend on next 50m maxsize 3000m extent management local;

create user harmony identified by harmony default tablespace harmony1 temporary tablespace temp;

grant dba to harmony;
commit;

--infinity
create tablespace rap logging datafile '/opt/app/oracle/disk1/data1/rap01.dbf' size 2500m autoextend on next 50m maxsize 3000m extent management local;
create user infinity identified by infinity default tablespace rap temporary tablespace temp;
grant dba to infinity;
commit;

--start and mid
create tablespace STATDATA logging datafile '/opt/app/oracle/disk1/data1/STATDATA01.dbf' size 2500m autoextend on next 50m maxsize 3000m extent management local;

create user stat identified by stat default tablespace STATDATA temporary tablespace temp;
grant connect,resource,dba to stat;
commit;
create user mid identified by mid default tablespace STATDATA temporary tablespace temp;
grant dba to mid;
commit;

--portal
create tablespace portal logging datafile '/opt/app/oracle/disk1/data1/portal01.dbf' size 2500m autoextend on next 50m maxsize 3000m extent management local;

create user portal identified by portal default tablespace portal temporary tablespace temp;

grant dba to portal;
commit;

--blues
create tablespace blues01 logging datafile '/opt/app/oracle/disk1/data1/blues01.dbf' size 2500m autoextend on next 50m maxsize 3000m extent management local;

create user blues identified by blues default tablespace blues01 temporary tablespace temp;

grant dba to blues;
commit;
----------end---------------
二、执行导出工作

登陆192.168.1.46现用生产库,执行exp备份脚本,生成备份文件
ssh mailto:seagull@192.168.1.46]seagull@192.168.1.46
sudo su - oracle
sh /disk1/orabak/expbak/exp_kdswnewdb.sh

sh /disk1/orabak/shell/alldb_bak_mseidb.sh
脚本内容如下:
###-----------start-----------------------
#!/bin/sh

ORACLE_BASE=/opt/app/oracle; export ORACLE_BASE
ORACLE_HOME=/opt/app/oracle/product/9.2.0; export ORACLE_HOME
ORACLE_SID=mseidb; export ORACLE_SID
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
NLS_LANG=american_america.utf8; export NLS_LANG
PATH=$ORACLE_HOME/bin:/usr/bin:/usr/ucb:/etc:/usr/openwin/bin:/usr/ccs/bin:/usr/local/bin;
export PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH
SHLIB_PATH=/opt/app/oracle/product/9.2.0/lib:/usr/lib; export SHLIB_PATH
ORACLE_LIB=/opt/app/oracle/product/9.2.0/lib; export ORACLE_LIB

name=`/bin/date +exp_infinity_%u`
/opt/app/oracle/product/9.2.0/bin/exp infinity/infinity@kdswdb owner=infinity consistent=y direct=y file=/disk1/orabak/expbak/$name.dmp log=/disk1/orabak/expbak/$name.log

name=`/bin/date +exp_harmony_%u`
/opt/app/oracle/product/9.2.0/bin/exp harmony/harmony@kdswdb owner=harmony consistent=y direct=y file=/disk1/orabak/expbak/$name.dmp log=/disk1/orabak/expbak/$name.log

name=`/bin/date +exp_stat_%u`
/opt/app/oracle/product/9.2.0/bin/exp stat/stat@kdswdb owner=stat consistent=y direct=y file=/disk1/orabak/expbak/$name.dmp log=/disk1/orabak/expbak/$name.log

name=`/bin/date +exp_mid_%u`
/opt/app/oracle/product/9.2.0/bin/exp mid/mid@kdswdb owner=mid consistent=y direct=y file=/disk1/orabak/expbak/$name.dmp log=/disk1/orabak/expbak/$name.log

name=`/bin/date +exp_blues_%u`
/opt/app/oracle/product/9.2.0/bin/exp blues/blues@kdswdb owner=blues consistent=y direct=y file=/disk1/orabak/expbak/$name.dmp log=/disk1/orabak/expbak/$name.log

name=`/bin/date +exp_portal_%u`
/opt/app/oracle/product/9.2.0/bin/exp portal/portal@kdswdb owner=portal consistent=y direct=y file=/disk1/orabak/expbak/$name.dmp log=/disk1/orabak/expbak/$name.log
cd /disk1/orabak/expbak
name=`/bin/date +%u`

###------------end----------------------

5、顺序执行如下imp语句,直到imp语句执行成功。
imp userid=harmony/harmony fromuser=harmony touser=harmony file=exp_harmony_2.dmp log=harmony_imp.log
imp userid=infinity/infinity fromuser=infinity touser=infinity file=exp_infinity_2.dmp log=infinity_imp.log
imp userid=portal/portal fromuser=portal touser=portal file=exp_portal_2.dmp log=portal_imp.log
imp userid=blues/blues fromuser=blues touser=blues file=exp_blues_2.dmp log=blues_imp.log
imp userid=stat/stat fromuser=stat touser=stat file=exp_stat_2.dmp log=stat_imp.log
imp userid=mid/mid fromuser=mid touser=mid file=exp_mid_2.dmp log=mid_imp.log

6、完成割接,如果imp导入过程出错,需要重新导入,只需要执行
drop user user_name cascade; (删除用户以及用户所有的对象)

参考内容:
先删除用户文件
drop user user_name cascade; (删除用户以及用户所有的对象。imp失败后,执行此语句可清除导入部分,进行重新imp操作,cascade参数是级联删除该用户所有对象,经常遇到如用户有对象而未加此参数则用户删不了的问题,所以习惯性的加此参数)
drop user stat cascade;
再删除表空间
drop tablespace tablespace_name including contents and datafiles;

drop tablespace harmony1
drop user harmony cascade;
--drop tablespace tablespace_name including contents;
drop tablespace harmony1 including contents;

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】