直接拷贝数据文件实现Oracle数据迁移
背景介绍
由于机房服务器变更,需要将Oracle迁移到一台新服务器上去。
以下是环境说明:
- 原服务器地址:192.168.1.15
- 新服务器地址:192.168.1.18
- 操作系统:都是CentOS6.5
- 数据库版本:都是11.2.0.1
新服务器上安装和配置Oracle
- 192.168.1.18
在新服务器(192.168.1.18)上安装了Oracle,为了保险,主机名、数据库实例名、安装目录都和原数据库保持一致。具体安装方法可参考:centos6.5下安装oracle11gR2与Oracle自动启动的配置
查询需要拷贝的文件
- 192.168.1.15
- 需要切换到oracle用户
- 使用sqlplus登录进Oracle数据库
sqlplus/assysdba
SQL>showparameterpfile NAMETYPEVALUE ----------------------------------------------------------------------------- spfilestring/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora SQL>showparametercontrol NAMETYPEVALUE ----------------------------------------------------------------------------- control_file_record_keep_timeinteger7 control_filesstring/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/recovery_area/orcl/control02.ctl control_management_pack_accessstringDIAGNOSTIC+TUNING SQL>select*fromv$logfile; GROUP#STATUSTYPEMEMBERIS_RECOVERY_DEST_FILE ----------------------------------------------------------------------------------------------------------------------------- 3ONLINE/u01/app/oracle/oradata/orcl/redo03.logNO 2ONLINE/u01/app/oracle/oradata/orcl/redo02.logNO 1ONLINE/u01/app/oracle/oradata/orcl/redo01.logNO SQL>selectnamefromv$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/orcl/users02.dbf SQL>selectnamefromv$tempfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/temp01.dbf
根据以上查询结果,发现有以下文件需要拷贝:
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/recovery_area/orcl/control02.ctl /u01/app/oracle/oradata/orcl/redo03.log /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/orcl/redo01.log /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/orcl/users02.dbf /u01/app/oracle/oradata/orcl/users03.dbf /u01/app/oracle/oradata/orcl/temp01.dbf
停掉原数据库与新数据库
- 192.168.1.15和192.168.1.18
- 使用root用户
serviceoraclestop
使用scp拷贝文件到新服务器
- 192.168.1.15
- scp命令可以把文件复制到远程Linux服务器,可以参考:scp命令
scp/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.oraoracle@192.168.1.18:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora scp/u01/app/oracle/oradata/orcl/control01.ctloracle@192.168.1.18:/u01/app/oracle/oradata/orcl/control01.ctl scp/u01/app/oracle/recovery_area/orcl/control02.ctloracle@192.168.1.18:/u01/app/oracle/recovery_area/orcl/control02.ctl scp/u01/app/oracle/oradata/orcl/redo03.logoracle@192.168.1.18:/u01/app/oracle/oradata/orcl/redo03.log scp/u01/app/oracle/oradata/orcl/redo02.logoracle@192.168.1.18:/u01/app/oracle/oradata/orcl/redo02.log scp/u01/app/oracle/oradata/orcl/redo01.logoracle@192.168.1.18:/u01/app/oracle/oradata/orcl/redo01.log scp/u01/app/oracle/oradata/orcl/system01.dbforacle@192.168.1.18:/u01/app/oracle/oradata/orcl/system01.dbf scp/u01/app/oracle/oradata/orcl/sysaux01.dbforacle@192.168.1.18:/u01/app/oracle/oradata/orcl/sysaux01.dbf scp/u01/app/oracle/oradata/orcl/undotbs01.dbforacle@192.168.1.18:/u01/app/oracle/oradata/orcl/undotbs01.dbf scp/u01/app/oracle/oradata/orcl/users01.dbforacle@192.168.1.18:/u01/app/oracle/oradata/orcl/users01.dbf scp/u01/app/oracle/oradata/orcl/users02.dbforacle@192.168.1.18:/u01/app/oracle/oradata/orcl/users02.dbf scp/u01/app/oracle/oradata/orcl/users03.dbforacle@192.168.1.18:/u01/app/oracle/oradata/orcl/users03.dbf scp/u01/app/oracle/oradata/orcl/temp01.dbforacle@192.168.1.18:/u01/app/oracle/oradata/orcl/temp01.dbf
等待拷贝完成
尝试启动新数据库
- 192.168.1.18
- 使用oracle用户
dba登录进行启动数据库
[oracle@oracle~]$sqlplus/assysdba SQL*Plus:Release11.2.0.1.0ProductiononThuSep1709:26:112015 Copyright(c)1982,2009,Oracle.Allrightsreserved. Connectedtoanidleinstance. SQL>startup ORACLEinstancestarted. TotalSystemGlobalArea6747725824bytes FixedSize2213976bytes VariableSize5100275624bytes DatabaseBuffers1610612736bytes RedoBuffers34623488bytes Databasemounted. Databaseopened.
注意最后一句,到这里就启动成功了。这次很顺利,没有出现意外。也可以通过以下语句检测数据库的状态:
SQL>selectstatusfromv$instance; STATUS ------------ OPEN
如果Databasemounted成功后报错,也就是数据库最终不是open状态,只是mounted状态,可以尝试恢复数据库。
recoverdatabase;
完成后,再打开数据库,一般可以成功。
alterdatabaseopen;
验证两个库的数据
根据自己的实际情况进行验证,这里不再赘述.
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流。