数据库文件移位(一)
来源:互联网 发布:怎样投诉淘宝卖家客服 编辑:程序博客网 时间:2024/06/05 12:42
前言:需要把本地磁盘的所有数据文件,都移动到存储上去。
步骤概要:
1.shut immediate 停库
2.host cp dbfile to new path
3.修改参数中controlfile的位置,把数据库启动到mount
4.rename相关文件
5.alter database open;
本次具体实施步骤:
一、查好需要移动的文件,以防漏掉。
SQL>
select name from v$datafile
union all
select name from v$controlfile
union all
select name from v$tempfile
union all
select member from v$logfile;
NAME
--------------------------------------------------
/oracle/oradata/test/system01.dbf
/oracle/oradata/test/undotbs01.dbf
/oracle/oradata/test/sysaux01.dbf
/oracle/oradata/test/users01.dbf
/oradata1/test01.dbf <--------------注意该文件不在默认目录下。
/oracle/oradata/test/control01.ctl
/oracle/oradata/test/control02.ctl
/oracle/oradata/test/control03.ctl
/oracle/oradata/test/temp01.dbf
/oracle/oradata/test/redo03.log
/oracle/oradata/test/redo02.log
/oracle/oradata/test/redo01.log
12 rows selected.
SQL>
二、用语句生成rename语句。
datafile:
SQL> select 'alter database rename file '||chr(10)||chr(39)||name ||chr(39)||chr(10)||' to '''||replace(name,'/oracle/oradata/test','/oracle/oradata/u1/test') ||''';' aaa from v$datafile;
AAA
--------------------------------------------------------------------------------------------------------------
alter database rename file
'/oracle/oradata/test/system01.dbf'
to '/oracle/oradata/u1/test/system01.dbf';
alter database rename file
'/oracle/oradata/test/undotbs01.dbf'
to '/oracle/oradata/u1/test/undotbs01.dbf';
alter database rename file
'/oracle/oradata/test/sysaux01.dbf'
to '/oracle/oradata/u1/test/sysaux01.dbf';
alter database rename file
'/oracle/oradata/test/users01.dbf'
to '/oracle/oradata/u1/test/users01.dbf';
alter database rename file
'/oradata1/test01.dbf'
to '/oracle/oradata/u1/test/test01.dbf'; <------------------------手动修改一下。
CONTROLFILE:
控制文件位置修改不需要通过rename,直接修改参数文件就行。
tempfile:
SQL> select 'alter database rename file '||chr(10)||chr(39)||name ||chr(39)||chr(10)||' to '''||replace(name,'/oracle/oradata/test','/oracle/oradata/u1/test') ||''';' aaa from v$tempfile;
AAA
--------------------------------------------------------------------------------------------------------------
alter database rename file
'/oracle/oradata/test/temp01.dbf'
to '/oracle/oradata/u1/test/temp01.dbf';
redolog
SQL> select 'alter database rename file '||chr(10)||chr(39)||member ||chr(39)||chr(10)||' to '''||replace(member,'/oracle/oradata/test','/oracle/oradata/u1/test') ||''';' aaa from v$logfile
2 ;
AAA
--------------------------------------------------------------------------------------------------------------
alter database rename file
'/oracle/oradata/test/redo03.log'
to '/oracle/oradata/u1/test/redo03.log';
alter database rename file
'/oracle/oradata/test/redo02.log'
to '/oracle/oradata/u1/test/redo02.log';
alter database rename file
'/oracle/oradata/test/redo01.log'
to '/oracle/oradata/u1/test/redo01.log';
三、停库,拷贝文件
SQL>shut immediate;
SQL>!
cp /oracle/oradata/test/* /oracle/oradata/u1/test/
cp /oradata1/test01.dbf /oracle/oradata/u1/test/
四、修改一下参数文件对controlfile的描述。
五、SQL>startup mount
SQL>执行第二步生成的rename语句。
SQL>alter database open;
步骤概要:
1.shut immediate 停库
2.host cp dbfile to new path
3.修改参数中controlfile的位置,把数据库启动到mount
4.rename相关文件
5.alter database open;
本次具体实施步骤:
一、查好需要移动的文件,以防漏掉。
SQL>
select name from v$datafile
union all
select name from v$controlfile
union all
select name from v$tempfile
union all
select member from v$logfile;
NAME
--------------------------------------------------
/oracle/oradata/test/system01.dbf
/oracle/oradata/test/undotbs01.dbf
/oracle/oradata/test/sysaux01.dbf
/oracle/oradata/test/users01.dbf
/oradata1/test01.dbf <--------------注意该文件不在默认目录下。
/oracle/oradata/test/control01.ctl
/oracle/oradata/test/control02.ctl
/oracle/oradata/test/control03.ctl
/oracle/oradata/test/temp01.dbf
/oracle/oradata/test/redo03.log
/oracle/oradata/test/redo02.log
/oracle/oradata/test/redo01.log
12 rows selected.
SQL>
二、用语句生成rename语句。
datafile:
SQL> select 'alter database rename file '||chr(10)||chr(39)||name ||chr(39)||chr(10)||' to '''||replace(name,'/oracle/oradata/test','/oracle/oradata/u1/test') ||''';' aaa from v$datafile;
AAA
--------------------------------------------------------------------------------------------------------------
alter database rename file
'/oracle/oradata/test/system01.dbf'
to '/oracle/oradata/u1/test/system01.dbf';
alter database rename file
'/oracle/oradata/test/undotbs01.dbf'
to '/oracle/oradata/u1/test/undotbs01.dbf';
alter database rename file
'/oracle/oradata/test/sysaux01.dbf'
to '/oracle/oradata/u1/test/sysaux01.dbf';
alter database rename file
'/oracle/oradata/test/users01.dbf'
to '/oracle/oradata/u1/test/users01.dbf';
alter database rename file
'/oradata1/test01.dbf'
to '/oracle/oradata/u1/test/test01.dbf'; <------------------------手动修改一下。
CONTROLFILE:
控制文件位置修改不需要通过rename,直接修改参数文件就行。
tempfile:
SQL> select 'alter database rename file '||chr(10)||chr(39)||name ||chr(39)||chr(10)||' to '''||replace(name,'/oracle/oradata/test','/oracle/oradata/u1/test') ||''';' aaa from v$tempfile;
AAA
--------------------------------------------------------------------------------------------------------------
alter database rename file
'/oracle/oradata/test/temp01.dbf'
to '/oracle/oradata/u1/test/temp01.dbf';
redolog
SQL> select 'alter database rename file '||chr(10)||chr(39)||member ||chr(39)||chr(10)||' to '''||replace(member,'/oracle/oradata/test','/oracle/oradata/u1/test') ||''';' aaa from v$logfile
2 ;
AAA
--------------------------------------------------------------------------------------------------------------
alter database rename file
'/oracle/oradata/test/redo03.log'
to '/oracle/oradata/u1/test/redo03.log';
alter database rename file
'/oracle/oradata/test/redo02.log'
to '/oracle/oradata/u1/test/redo02.log';
alter database rename file
'/oracle/oradata/test/redo01.log'
to '/oracle/oradata/u1/test/redo01.log';
三、停库,拷贝文件
SQL>shut immediate;
SQL>!
cp /oracle/oradata/test/* /oracle/oradata/u1/test/
cp /oradata1/test01.dbf /oracle/oradata/u1/test/
四、修改一下参数文件对controlfile的描述。
五、SQL>startup mount
SQL>执行第二步生成的rename语句。
SQL>alter database open;
- 数据库文件移位(一)
- Oracle数据库文件恢复与备份思路(一)
- 数据库文件
- JAVA移位运算符一
- Android SQLite的数据库文件存储在SD卡中(一)
- ADO创建数据库文件(*.MDB)
- 移位运算(Java)
- 循环移位(转)
- 习题(移位运算)
- 移位运算(转)
- 移位运算符(<<) (>>) (<<<)
- 移位
- 移位
- 移位
- 移位
- 移位
- 移位
- 移位
- w ! sudo tee%
- C#跨线程的给空间赋值
- Fixing XSS: A practical guide for developers
- gpio_request gpio_set_value __gpio_set_value
- Android学习之 The connection to adb is down, and a severe error has occured. 解决方法
- 数据库文件移位(一)
- WAF Testing Framework
- javascript 中contentWindow和 frames
- PyQuery Tutorial: Basic HTML Parsing with PyQuery
- oracle 使用database control 配置数据库时,要求在当前oracle主目录中配置监听程序,必须运行Netca以配置
- VC2008+OpenCV2.1安装过程
- in house penetration testing pci dss
- 如何利用Android XmlSerializer生成XML文件
- 前景检测算法-SACON(SAMPLE CONSENSUS)