1/12用友股份-支持服务事业部技术方案--《如何移动oracle数据文件的位置》建立日期：2014-02-25修改日期：2014-02-25文档属性：客户文控编号：LE-DN-TS-2014-00302/12文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围内部员工伙伴客户发布人姓名所属部门发布时间李志军服务业务管理部2014-02-28日期作者所属部门邮件地址版本2014-02-25用友NC技术支持部V1.0版本领域模块备注3/12目录如何移动oracle数据文件的位置...........................................................................4一、解决方案........................................................................................................4二、操作步骤........................................................................................................44/12如何移动oracle数据文件的位置注：请由计算机专业人士操作，并且操作前请做好备份工作！一、解决方案采用offline的方式第一步,将表空间offlinealtertablespacetablespace_nameoffline;第二步,cp文件到新的目录并rename修改控制文件第三步,将相应表空间online当然这种方式同样会影响期间的表空间使用。二、操作步骤以下是简单的示范步骤:1.将表空间offline[oracle@jumperoracle]$sqlplus"/assysdba"SQL*Plus:Release9.2.0.4.0-ProductiononSatNov1218:14:212005Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioningoptionJServerRelease9.2.0.4.0-ProductionSQL>archiveloglist;DatabaselogmodeNoArchiveModeAutomaticarchivalEnabled5/12Archivedestination/opt/oracle/oradata/conner/archiveOldestonlinelogsequence6Currentlogsequence9SQL>selectnamefromv$datafile;NAME---------------------------------------------------------/opt/oracle/oradata/conner/system01.dbf/opt/oracle/oradata/conner/undotbs01.dbf/opt/oracle/oradata/conner/users01.dbfSQL>altertablespaceusersoffline;Tablespacealtered.2.拷贝相应的数据文件SQL>!cp/opt/oracle/oradata/conner/users01.dbf/opt/oracle/oradata/users01.dbfSQL>altertablespaceusersrenamedatafile'/opt/oracle/oradata/conner/users01.dbf'to'/opt/oracle/oradata/users01.dbf';Tablespacealtered.3.将表空间onlineSQL>altertablespaceusersonline;Tablespacealtered.SQL>selectnamefromv$datafile;NAME------------------------------------------------------------------------/opt/oracle/oradata/conner/system01.dbf/opt/oracle/oradata/conner/undotbs01.dbf/opt/oracle/oradata/users01.dbfD:>sqlplus"/assysdba"SQL*Plus:Release9.2.0.1.0-Productionon星期日1月723:11:542007Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.6/12已连接到空闲例程。SQL>select*fromv$datafile;select*fromv$datafile*ERROR位于第1行:ORA-01034:ORACLEnotavailableSQL>startup;ORACLE例程已经启动。TotalSystemGlobalArea126950220bytesFixedSize453452bytesVariableSize109051904bytesDatabaseBuffers16777216bytesRedoBuffers667648bytes数据库装载完毕。数据库已经打开。SQL>selectnamefromv$datafile;NAME--------------------------------------------------------------------------------E:ORACLESONBWEBSYSTEM01.DBFE:ORACLESONBWEBUNDOTBS01.DBFE:ORACLESONBWEBCWMLITE01.DBFE:ORACLESONBWEBDRSYS01.DBFE:ORACLESONBWEBEXAMPLE01.DBFE:ORACLESONBWEBINDX01.DBFE:ORACLESONBWEBODM01.DBFE:ORACLESONBWEBTOOLS01.DBFE:ORACLESONBWEBUSERS01.DBFE:ORACLESONBWEBXDB01.DBFE:ORACLESONBWEBHOUSEWEB.ORANAME7/12--------------------------------------------------------------------------------E:ORACLESONBWEBSDE.DBFE:ORACLESONBWEBREALSTAR.ORA已选择13行。SQL>selectnamefromv$controlfile;NAME--------------------------------------------------------------------------------E:ORACLESONBWEBCONTROL01.CTLE:ORACLESONBWEBCONTROL02.CTLE:ORACLESONBWEBCONTROL03.CTLSQL>select*fromv$logfile;GROUP#STATUSTYPE------------------------MEMBER--------------------------------------------------------------------------------3STALEONLINEE:ORACLESONBWEBREDO03.LOG2ONLINEE:ORACLESONBWEBREDO02.LOG1STALEONLINEE:ORACLESONBWEBREDO01.LOGSQL>shutdownimmediate;数据库已经关闭。已经卸载数据库。ORACLE例程已经关闭。8/12SQL>hostcopyE:ORACLESONBWEB*.dbfD:oracleoradataSONBWEB;E:ORACLESONBWEBCWMLITE01.DBFE:ORACLESONBWEBDRSYS01.DBFE:ORACLESONBWEBEXAMPLE01.DBFE:ORACLESONBWEBINDX01.DBFE:ORACLESONBWEBODM01.DBFE:ORACLESONBWEBSDE.DBFE:ORACLESONBWEBSYSTEM01.DBFE:ORACLESONBWEBTEMP01.DBFE:ORACLESONBWEBTOOLS01.DBFE:ORACLESONBWEBUNDOTBS01.DBFE:ORACLESONBWEBUSERS01.DBFE:ORACLESONBWEBXDB01.DBF已复制12个文件。SQL>hostcopyE:ORACLESONBWEB*.oraD:oracleoradataSONBWEB;E:ORACLESONBWEBHOUSEWEB.ORAE:ORACLESONBWEBREALSTAR.ORA已复制2个文件。SQL>hostcopyE:ORACLESONBWEB*.ctlD:oracleoradataSONBWEB;E:ORACLESONBWEBCONTROL01.CTLE:ORACLESONBWEBCONTROL02.CTLE:ORACLESONBWEBCONTROL03.CTL已复制3个文件。SQL>hostcopyE:ORACLESONBWEB*.logD:oracleoradataSONBWEB;E:ORACLESONBWEBREDO01.LOGE:ORACLESONBWEBREDO02.LOGE:ORACLESONBWEBREDO03.LOG已复制3个文件。SQL>createpfilefromspfile;文件已创建。编辑生成的pfile即INIT.ORA默认在$Oracle_HOME\database下。此例中为INITsonbweb.ORA。将文件中控制文件的路径改成迁移后的路径。SQL>createspfilefrompfile;9/12文件已创建。SQL>startupmount;ORACLE例程已经启动。TotalSystemGlobalArea126950220bytesFixedSize453452bytesVariableSize109051904bytesDatabaseBuffers16777216bytesRedoBuffers667648bytes数据库装载完毕。SQL>alterdatabaserenamefile'E:ORACLESONBWEBSYSTEM01.DBF'2to'D:oracleoradataSONBWEBsystem01.dbf';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBCWMLITE01.DBF'2to'D:oracleoradataSONBWEBcwmlite01.dbf';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBDRSYS01.DBF'2to'D:oracleoradataSONBWEBdrsys01.dbf';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBEXAMPLE01.DBF'2to'D:oracleoradataSONBWEBexample01.dbf';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBINDX01.DBF'2to'D:oracleoradataSONBWEBindx01.dbf';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBODM01.DBF'2to'D:oracleoradataSONBWEBodm01.dbf';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBTOOLS01.DBF'2to'D:oracleoradataSONBWEBools01.dbf';数据库已更改。10/12SQL>alterdatabaserenamefile'E:ORACLESONBWEBUSERS01.DBF'2to'D:oracleoradataSONBWEBusers01.dbf';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBXDB01.DBF'2to'D:oracleoradataSONBWEBsdb01.dbf';alterdatabaserenamefile'E:ORACLESONBWEBXDB01.DBF'*ERROR位于第1行:ORA-01511:重命名日志/数据文件时出错ORA-01141:重命名数据文件10时出错-未找到新文件'D:oracleoradataSONBWEBsdb01.dbf'ORA-01110:数据文件10:'E:ORACLESONBWEBXDB01.DBF'ORA-27041:无法打开文件OSD-04002:无法打开文件O/S-Error:(OS2)系统找不到指定的文件。SQL>alterdatabaserenamefile'E:ORACLESONBWEBXDB01.DBF'2to'D:oracleoradataSONBWEBxdb01.dbf';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBHOUSEWEB.ORA'2to'D:oracleoradataSONBWEBhouseweb.ora';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBsde.dbf'2to'D:oracleoradataSONBWEBsde.dbf';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBREALSTAR.ORA'2to'D:oracleoradataSONBWEBREALSTAR.ORA';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBREDO01.LOG'2to'D:oracleoradataSONBWEBREDO01.LOG';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBREDO02.LOG'2to'D:oracleoradataSONBWEBREDO02.LOG';数据库已更改。SQL>alterdatabaserenamefile'E:ORACLESONBWEBREDO03.LOG'2to'D:oracleoradataSONBWEBREDO03.LOG';11/12数据库已更改。临时文件更改无效，必须删除原先的临时文件重新生成。SQL>startup;ORA-01081:无法启动已在运行的ORACLE---请首先关闭SQL>shutdownimmediate;ORA-01109:数据库未打开已经卸载数据库。ORACLE例程已经关闭。SQL>startup;ORACLE例程已经启动。TotalSystemGlobalArea126950220bytesFixedSize453452bytesVariableSize109051904bytesDatabaseBuffers16777216bytesRedoBuffers667648bytes数据库装载完毕。数据库已经打开。SQL>select*fromv$tempfile;FILE#CREATION_CHANGE#CREATION_TTS#RFILE#STATUSENABLED-------------------------------------------------------------------------BYTESBLOCKSCREATE_BYTESBLOCK_SIZE------------------------------------------NAME--------------------------------------------------------------------------------3023ONLINEREADWRITE104857601280104857608192E:ORACLESONBWEBTEMP01.DBFSQL>alterdatabasetempfile'E:ORACLESONBWEBTEMP01.DBF'drop;数据库已更改。SQL>altertablespacetempaddtempfile'D:oracleoradataSONBWEBTEMP01.ora'size100Mreuse;12/12表空间已更改。SQL>select*fromv$tempfile;FILE#CREATION_CHANGE#CREATION_TTS#RFILE#STATUSENABLED-------------------------------------------------------------------------BYTESBLOCKSCREATE_BYTESBLOCK_SIZE------------------------------------------NAME--------------------------------------------------------------------------------1021ONLINEREADWRITE104857600128001048576008192D:ORACLEORADATASONBWEBTEMP01.ORASQL>
