Oracle数据文件位置迁移以下文字资料是由(历史新知网www.lishixinzhi.com)小编为大家搜集整理后发布的内容,让我们赶快一起来看一下吧!
操作系统 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:ORACLESONBWEBSYSTEM DBF E:ORACLESONBWEBUNDOTBS DBF E:ORACLESONBWEBCWMLITE DBF E:ORACLESONBWEBDRSYS DBF E:ORACLESONBWEBEXAMPLE DBF E:ORACLESONBWEBINDX DBF E:ORACLESONBWEBODM DBF E:ORACLESONBWEBTOOLS DBF E:ORACLESONBWEBUSERS DBF E:ORACLESONBWEBXDB DBF E:ORACLESONBWEBHOUSEWEB ORA
NAME
E:ORACLESONBWEBSDE DBF E:ORACLESONBWEBREALSTAR ORA
已选择 行
SQL> select name from v$controlfile;
NAME
E:ORACLESONBWEBCONTROL CTL E:ORACLESONBWEBCONTROL CTL E:ORACLESONBWEBCONTROL CTL
SQL> select * from v$logfile;
GROUP# STATUS TYPE
MEMBER
STALE ONLINE
E:ORACLESONBWEBREDO LOG
ONLINE
E:ORACLESONBWEBREDO LOG
STALE ONLINE
E:ORACLESONBWEBREDO LOG
SQL> shutdown immediate;
数据库已经关闭
已经卸载数据库
ORACLE 例程已经关闭
SQL> host copy E:ORACLESONBWEB* dbf D:oracleoradataSONBWEB;
E:ORACLESONBWEBCWMLITE DBF E:ORACLESONBWEBDRSYS DBF E:ORACLESONBWEBEXAMPLE DBF E:ORACLESONBWEBINDX DBF E:ORACLESONBWEBODM DBF E:ORACLESONBWEBSDE DBF E:ORACLESONBWEBSYSTEM DBF E:ORACLESONBWEBTEMP DBF E:ORACLESONBWEBTOOLS DBF E:ORACLESONBWEBUNDOTBS DBF E:ORACLESONBWEBUSERS DBF E:ORACLESONBWEBXDB DBF已复制 个文件
SQL> host copy E:ORACLESONBWEB* ora D:oracleoradataSONBWEB;
E:ORACLESONBWEBHOUSEWEB ORA E:ORACLESONBWEBREALSTAR ORA已复制 个文件
SQL> host copy E:ORACLESONBWEB* ctl D:oracleoradataSONBWEB;
E:ORACLESONBWEBCONTROL CTL E:ORACLESONBWEBCONTROL CTL E:ORACLESONBWEBCONTROL CTL已复制 个文件
SQL> host copy E:ORACLESONBWEB* log D:oracleoradataSONBWEB;
E:ORACLESONBWEBREDO LOG E:ORACLESONBWEBREDO LOG E:ORACLESONBWEBREDO LOG已复制 个文件
SQL> create pfile from spfile;
文件已创建
编辑生成的pfile即INIT ORA默认在$Oracle_HOME\database下 此例中为INITsonbweb ORA 将文件中控制文件的路径改成迁移后的路径
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:ORACLESONBWEBSYSTEM DBF to D:oracleoradataSONBWEBsystem dbf ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBCWMLITE DBF to D:oracleoradataSONBWEBcwmlite dbf ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBDRSYS DBF to D:oracleoradataSONBWEBdrsys dbf ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBEXAMPLE DBF to D:oracleoradataSONBWEBexample dbf ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBINDX DBF to D:oracleoradataSONBWEBindx dbf ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBODM DBF to D:oracleoradataSONBWEBodm dbf ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBTOOLS DBF to D:oracleoradataSONBWEB ools dbf ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBUSERS DBF to D:oracleoradataSONBWEBusers dbf ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBXDB DBF to D:oracleoradataSONBWEBsdb dbf ;
alter database rename file E:ORACLESONBWEBXDB DBF
*
ERROR位于第 行:
ORA :重命名日志/数据文件时出错
ORA :重命名数据文件 时出错 未找到新文件
D:oracleoradataSONBWEBsdb dbf ORA : 数据文件 : E:ORACLESONBWEBXDB DBF
ORA :无法打开文件
OSD :无法打开文件
O/S Error: (OS ) 系统找不到指定的文件
SQL> alter database rename file E:ORACLESONBWEBXDB DBF to D:oracleoradataSONBWEBxdb dbf ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBHOUSEWEB ORA to D:oracleoradataSONBWEBhouseweb ora ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBsde dbf to D:oracleoradataSONBWEBsde dbf ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBREALSTAR ORA to D:oracleoradataSONBWEBREALSTAR ORA ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBREDO LOG to D:oracleoradataSONBWEBREDO LOG ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBREDO LOG to D:oracleoradataSONBWEBREDO LOG ;
数据库已更改
SQL> alter database rename file E:ORACLESONBWEBREDO LOG to D:oracleoradataSONBWEBREDO LOG ;
数据库已更改
临时文件更改无效 必须删除原先的临时文件重新生成
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:ORACLESONBWEBTEMP DBF
SQL> alter database tempfile E:ORACLESONBWEBTEMP DBF drop;
数据库已更改
SQL> alter tablespace temp add tempfile D:oracleoradataSONBWEBTEMP ora 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:ORACLEORADATASONBWEBTEMP ORA
本文来自CSDN博客
SQL> lishixinzhi/Article/program/Oracle/201311/17411
分页:123