熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle數據文件位置遷移

2013-11-13 15:51:37  來源: Oracle 

  操作系統Windows Server

  Oracle Version:

  下為非歸檔模式的遷移過程

  D:>sqlplus /as sysdba

  SQL*Plus: Release Production on 星期日 ::

  Copyright (c) Oracle Corporation All rights reserved

  已連接到空閒例程

  SQL> select * from v$datafile;

  select * from v$datafile

  *

  ERROR位於第行:

  ORA: ORACLE not available

  SQL> startup;

  ORACLE 例程已經啟動

  Total System Global Area bytes

  Fixed Size bytes

  Variable Size bytes

  Database Buffers bytes

  Redo Buffers bytes

  數據庫裝載完畢

  數據庫已經打開

  SQL> select name from v$datafile;

  NAME

  

  E:ORACLESONBWEBSYSTEMDBF E:ORACLESONBWEBUNDOTBSDBF E:ORACLESONBWEBCWMLITEDBF E:ORACLESONBWEBDRSYSDBF E:ORACLESONBWEBEXAMPLEDBF E:ORACLESONBWEBINDXDBF E:ORACLESONBWEBODMDBF E:ORACLESONBWEBTOOLSDBF E:ORACLESONBWEBUSERSDBF E:ORACLESONBWEBXDBDBF E:ORACLESONBWEBHOUSEWEBORA

  NAME

  

  E:ORACLESONBWEBSDEDBF E:ORACLESONBWEBREALSTARORA

  已選擇

  SQL> select name from v$controlfile;

  NAME

  

  E:ORACLESONBWEBCONTROLCTL E:ORACLESONBWEBCONTROLCTL E:ORACLESONBWEBCONTROLCTL

  SQL> select * from v$logfile;

  GROUP# STATUS TYPE

  

  MEMBER

  

   STALE ONLINE

  E:ORACLESONBWEBREDOLOG

   ONLINE

  E:ORACLESONBWEBREDOLOG

   STALE ONLINE

  E:ORACLESONBWEBREDOLOG

  SQL> shutdown immediate;

  數據庫已經關閉

  已經卸載數據庫

  ORACLE 例程已經關閉

  SQL> host copy E:ORACLESONBWEB*dbf D:oracleoradataSONBWEB;

  E:ORACLESONBWEBCWMLITEDBF E:ORACLESONBWEBDRSYSDBF E:ORACLESONBWEBEXAMPLEDBF E:ORACLESONBWEBINDXDBF E:ORACLESONBWEBODMDBF E:ORACLESONBWEBSDEDBF E:ORACLESONBWEBSYSTEMDBF E:ORACLESONBWEBTEMPDBF E:ORACLESONBWEBTOOLSDBF E:ORACLESONBWEBUNDOTBSDBF E:ORACLESONBWEBUSERSDBF E:ORACLESONBWEBXDBDBF已復制        個文件

  SQL> host copy E:ORACLESONBWEB*ora D:oracleoradataSONBWEB;

  E:ORACLESONBWEBHOUSEWEBORA E:ORACLESONBWEBREALSTARORA已復制         個文件

  SQL> host copy E:ORACLESONBWEB*ctl D:oracleoradataSONBWEB;

  E:ORACLESONBWEBCONTROLCTL E:ORACLESONBWEBCONTROLCTL E:ORACLESONBWEBCONTROLCTL已復制         個文件

  SQL> host copy E:ORACLESONBWEB*log D:oracleoradataSONBWEB;

  E:ORACLESONBWEBREDOLOG E:ORACLESONBWEBREDOLOG E:ORACLESONBWEBREDOLOG已復制         個文件

  SQL> create pfile from spfile;

  文件已創建

  編輯生成的pfile即INIT<SID>ORA默認在$Oracle_HOME\database下此例中為INITsonbwebORA將文件中控制文件的路徑改成遷移後的路徑

  SQL> create spfile from pfile;

  文件已創建

  SQL> startup mount;

  ORACLE 例程已經啟動

  Total System Global Area bytes

  Fixed Size bytes

  Variable Size bytes

  Database Buffers bytes

  Redo Buffers bytes

  數據庫裝載完畢

  SQL> alter database rename file E:ORACLESONBWEBSYSTEMDBF to D:oracleoradataSONBWEBsystemdbf;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBCWMLITEDBF to D:oracleoradataSONBWEBcwmlitedbf;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBDRSYSDBF to D:oracleoradataSONBWEBdrsysdbf;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBEXAMPLEDBF to D:oracleoradataSONBWEBexampledbf;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBINDXDBF to D:oracleoradataSONBWEBindxdbf;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBODMDBF to D:oracleoradataSONBWEBodmdbf;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBTOOLSDBF to D:oracleoradataSONBWEB oolsdbf;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBUSERSDBF to D:oracleoradataSONBWEBusersdbf;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBXDBDBF to D:oracleoradataSONBWEBsdbdbf;

  alter database rename file E:ORACLESONBWEBXDBDBF

  *

  ERROR位於第行:

  ORA:重命名日志/數據文件時出錯

  ORA:重命名數據文件時出錯未找到新文件

  D:oracleoradataSONBWEBsdbdbf ORA: 數據文件 : E:ORACLESONBWEBXDBDBF

  ORA:無法打開文件

  OSD:無法打開文件

  O/SError: (OS ) 系統找不到指定的文件

  SQL> alter database rename file E:ORACLESONBWEBXDBDBF to D:oracleoradataSONBWEBxdbdbf;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBHOUSEWEBORA to D:oracleoradataSONBWEBhousewebora;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBsdedbf to D:oracleoradataSONBWEBsdedbf;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBREALSTARORA to D:oracleoradataSONBWEBREALSTARORA;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBREDOLOG to D:oracleoradataSONBWEBREDOLOG;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBREDOLOG to D:oracleoradataSONBWEBREDOLOG;

  數據庫已更改

  SQL> alter database rename file E:ORACLESONBWEBREDOLOG to D:oracleoradataSONBWEBREDOLOG;

  數據庫已更改

  臨時文件更改無效必須刪除原先的臨時文件重新生成

  SQL> startup;

  ORA:無法啟動已在運行的ORACLE 請首先關閉

  SQL> shutdown immediate;

  ORA:數據庫未打開

  已經卸載數據庫

  ORACLE 例程已經關閉

  SQL> startup;

  ORACLE 例程已經啟動

  Total System Global Area bytes

  Fixed Size bytes

  Variable Size bytes

  Database Buffers bytes

  Redo Buffers bytes

  數據庫裝載完畢

  數據庫已經打開

  SQL> select * from v$tempfile;

  FILE# CREATION_CHANGE# CREATION_T TS# RFILE# STATUS ENABLED

  

  BYTES BLOCKS CREATE_BYTES BLOCK_SIZE

  

  NAME

  

   ONLINE READ WRITE

  

  E:ORACLESONBWEBTEMPDBF

  SQL> alter database tempfile E:ORACLESONBWEBTEMPDBF drop;

  數據庫已更改

  SQL> alter tablespace temp add tempfile D:oracleoradataSONBWEBTEMPora s

  ize M reuse;

  表空間已更改

  SQL> select * from v$tempfile;

  FILE# CREATION_CHANGE# CREATION_T TS# RFILE# STATUS ENABLED

  

  BYTES BLOCKS CREATE_BYTES BLOCK_SIZE

  

  NAME

  

   ONLINE READ WRITE

  

  D:ORACLEORADATASONBWEBTEMPORA

  本文來自CSDN博客

  

  SQL>

  遷移完成.


From:http://tw.wingwit.com/Article/program/Oracle/201311/17411.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.