数据文件位置迁移方式整理
来源:互联网 发布:联想网络唤醒bios设置 编辑:程序博客网 时间:2024/05/16 08:39
最近一气之下把好好地dba工作辞了,转做了大数据,刚到公司项目组没几天,就碰到了公司搭建的测试库空间快满了,需要把数据文件移动到新添加的硬盘上,项目组内的基本上都是java、pl/sql出身,迁移数据的任务就落到我身上了,然后问我怎么做,让我拿出迁移方案,最好最近二天内搞定它。
下面是我给出的几种迁移方案:
1、不关闭数据库情况下(非系统表空间):offline表空间
* 主要步骤: ** 1、offline表空间:alter tablespace tablespace_name offline; *
* 2、复制数据文件到新的目录; *
* 3、rename修改表空间对应数据文件位置,并把信息记录到控制文件;*
* 4、online表空间; *
例子:
alter tablespace users add datafile '/u01/app/oracle/oradata/chen/users02.dbf' size 100M autoextend on next 10M;
alter tablespace users offline;
mkdir -p /oradata/chen/
chown oracle:oinstall -R oradata
chmod 755 -R oradata
cd /u01/app/oracle/oradata/chen
bogon-> pwd
/u01/app/oracle/oradata/chen
bogon-> cp user* /oradata/chen/
select name,status from v$datafile;
alter tablespace users rename datafile '/u01/app/oracle/oradata/chen/users01.dbf' to '/oradata/chen/users01.dbf';
alter tablespace users rename datafile '/u01/app/oracle/oradata/chen/users02.dbf' to '/oradata/chen/users02.dbf';
alter tablespace users online;
select file_name,tablespace_name,status from dba_data_files;
select name,status from v$datafile;
2、关闭数据库迁移整个库数据文件位置
* 主要步骤: ** 1、首先关闭数据库 *
* 2、复制数据文件到新的目录; *
* 3、数据库启动到mount状态; *
* 4、执行sql语句修改数据文件位置; *
例子:
SQL> shutdown immediate;
bogon->cd /u01/app/oracle/oradata/chen
bogon-> cp exam* /oradata/chen/
bogon-> cp red* /oradata/chen/
bogon-> cp temp0* /oradata/chen/
bogon-> cp sys* /oradata/chen/
bogon-> cp un* /oradata/chen/
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 406851416 bytes
Database Buffers 654311424 bytes
Redo Buffers 5554176 bytes
Database mounted.
SQL> alter database rename file '/u01/app/oracle/oradata/chen/system01.dbf' to '/oradata/chen/system01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/chen/sysaux01.dbf' to '/oradata/chen/sysaux01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/chen/example01.dbf' to '/oradata/chen/example01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/chen/temp01.dbf' to '/oradata/chen/temp01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/chen/undotbs01.dbf' to '/oradata/chen/undotbs01.dbf';
SQL> alter database open;
select file_name,tablespace_name,status from dba_data_files;
select name,status from v$datafile;
注:以上二种只能迁移数据文件位置,无法迁移控制文件
上述二种方式迁移redo log方法相同,以下是迁移redo log:
select group#,members,archived,status from v$log;
select group#,member,status from v$logfile;
创建redo日志:
alter database add logfile group 4 '/oradata/chen/redo04.log' size 50M;
alter database add logfile group 5 '/oradata/chen/redo05.log' size 50M;alter database add logfile group 6 '/oradata/chen/redo06.log' size 50M;
alter database add logfile group 7 '/oradata/chen/redo07.log' size 50M;
alter system switch logfile;
alter system checkpoint;select group#,members,archived,status from v$log;
alter database drop logfile group 1;
alter database add logfile group 1 '/oradata/chen/redo01.log' size 50M;
alter database add logfile group 2 '/oradata/chen/redo02.log' size 50M;
alter database add logfile group 3 '/oradata/chen/redo03.log' size 50M;
alter database drop logfile group 4,5,6,7;
rm -rf redo0[4-7]*
select group#,members,archived,status from v$log;
select group#,member,status from v$logfile;
alter system checkpoint;
3、最直接的办法就是直接修改控制文件,同时也是最危险的(不建议),对数据库理解比较深可以一试:
* 主要步骤: ** 1、创建controlfile语句trace出来 *
* 2、修改相应文件位置; *
* 3、把控制文件、redo、数据文件拷贝到相应位置 *
* 4、打开数据库; *
例子:
SQL> alter database backup controlfile to trace;
或
SQL> alter database backup controlfile to trace as '/u01/control_new.trc';
bogon-> pwd
/u01/app/oracle/diag/rdbms/chen/chen/trace
bogon-> tail -200f aler*
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/chen/chen/trace/chen_ora_6323.trc
Completed: alter database backup controlfile to trace
创建controlfile:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CHEN" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/oradata/chen/redo01a.dbf',
'/oradata/chen/redo01b.dbf'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/oradata/chen/redo02a.dbf',
'/oradata/chen/redo02b.dbf'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'/oradata/chen/redo03a.dbf',
'/oradata/chen/redo03b.dbf'
) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oradata/chen/system01.dbf',
'/oradata/chen/sysaux01.dbf',
'/oradata/chen/undotbs01.dbf',
'/oradata/chen/users01.dbf',
'/oradata/chen/example01.dbf',
'/oradata/chen/users02.dbf'
CHARACTER SET WE8MSWIN1252
;
4、通过rman备份恢复
* 主要步骤: ** 1、把备份考到指定目录:/u05/backup *
* 2、把指定目录注册到catalog中 *
* 3、恢复controlfile到指定目录 *
* 4、set newname for指定位置 *
* 5、打开数据库 *
例子:
rman target /
RMAN> set DBID=1383628889
RMAN> restore spfile to '/u01/app/oracle/product/11.2.0/db_1/dbs/spfileORCL.ora' from '/u05/backup/SPFILE_0JPH1D70_1_1.BAK';
RMAN> restore controlfile to '/u01/app/oracle/oradata/orcl/control01.ctl' from '/u05/backup/CTL_0QPH9K9G_1_1.BAK';
RMAN> restore controlfile to '/u01/app/oracle/oradata/orcl/control02.ctl' from '/u05/backup/CTL_0QPH9K9G_1_1.BAK';
create pfile='/u05/initorcl.ora' from spfile;
修改initorcl.ora文件:调整SGA大小及创建对应目录:
orcl.__oracle_base='/u01/app/oracle'
*.db_name='orcl'
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.diagnostic_dest='/u01/app/oracle'
[root@bogon ~]# chown oracle:oinstall -R /u01/app/oracle/oradata/orcl
[root@bogon ~]# chown 755 -R /u01/app/oracle/oradata/orcl
将备份级的路径记录到控制文件内
RMAN> catalog start with '/u05/backup/';
RMAN>run {
allocate channel c1 type disk;
allocate channel c2 type disk;
SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/orcl/SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/orcl/SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/orcl/UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/orcl/USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO '/u01/app/oracle/oradata/orcl/EXAMPLE01.DBF';
SET NEWNAME FOR DATAFILE 6 TO '/u01/app/oracle/oradata/orcl/REP_DATA.DBF';
restore database;
switch datafile all;
release channel c1;
release channel c2;
}
crosscheck copy;
delete noprompt expired copy;
list backup;
recover database;
alter database open resetlogs;
以上4种方式中,3是最快最简洁的方式,同时也是最危险的,4是最复杂的,3、4也是作为dba必须掌握的,同时也要对数据库结构熟悉,4种方法仅供参考,这是小弟的一些见解!!有不对的地方请读者标注出来请留言!!!
0 0
- 数据文件位置迁移方式整理
- oracle迁移数据文件位置
- Oracle数据文件位置迁移笔记
- Oracle数据文件位置重建迁移
- mysql数据文件位置和迁移windows2008
- mysql通过拷贝数据文件方式迁移数据库
- 采用offline的方式更改数据文件位置
- 数据文件迁移
- mysql通过拷贝数据文件的方式进行数据库迁移
- mysql通过拷贝数据文件的方式进行数据库迁移
- Oracle HowTo:如何通过只读方式更改Oracle数据文件位置
- Oracle HowTo:如何通过只读方式更改Oracle数据文件位置
- 数据库数据文件迁移
- oracle迁移数据文件方法
- 数据文件迁移案例
- rman数据文件迁移
- ORACLE 如何迁移数据文件
- mysql 数据文件目录迁移
- Java 枚举常见7种用法
- Square Coins(dp-母函数)
- Oracle index】SQL语句利用函数索引注意点
- php学习准备
- 链表小程序-插入新节点
- 数据文件位置迁移方式整理
- hdu1828 线段树扫描线求矩形面积的周长
- NYOJ 221 Tree
- 我做的功能
- HDU 4183 Pahom on Water
- 哪个对象才是锁?
- [递归与分治]棋盘覆盖问题
- POJ2001——Shortest Prefixes
- 华为机试练习---进制数的相互转换