[20130104]快速移动数据文件.txt
如果要快速移动数据文件，对业务的影响最小，可以使用rman的backup as copy功能，先拷贝文件到
需要移动的目录，然后再追加增量变化，再利用增量备份来恢复copy文件，再切换数据文件。
做一个例子来说明整个过程:
--原来的数据文件自动变成了copy记录在控制文件中。切换回来。
如果要快速移动数据文件，对业务的影响最小，可以使用rman的backup as copy功能，先拷贝文件到
需要移动的目录，然后再追加增量变化，再利用增量备份来恢复copy文件，再切换数据文件。
做一个例子来说明整个过程:
SQL> select * from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
RMAN> report schema ;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /u01/app/oracle11g/oradata/test/system01.dbf
2 840 SYSAUX *** /u01/app/oracle11g/oradata/test/sysaux01.dbf
3 1024 UNDOTBS1 *** /u01/app/oracle11g/oradata/test/undotbs01.dbf
4 656 USERS *** /u01/app/oracle11g/oradata/test/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle11g/oradata/test/example01.dbf
6 64 RMAN *** /u01/app/oracle11g/oradata/test/rman01.dbf
7 64 TOOLS *** /u01/app/oracle11g/oradata/test/tools01.dbf
8 64 TEST *** /u01/app/oracle11g/oradata/test/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 339 TEMP 339 /u01/app/oracle11g/oradata/test/temp01.dbf
RMAN> backup as copy datafile 8 format '/data/testtest/test01.dbf';
Starting backup at 2013-01-04 11:36:47
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u01/app/oracle11g/oradata/test/test01.dbf
output file name=/data/testtest/test01.dbf tag=TAG20130104T113649 RECID=1 STAMP=803821013
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2013-01-04 11:36:56
RMAN> list copy of datafile 8;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - ------------------- ---------- -------------------
1 8 A 2013-01-04 11:36:53 3221576548 2013-01-04 11:36:49
Name: /data/testtest/test01.dbf
Tag: TAG20130104T113649
--注意显示的Ckp SCN=3221576548.
--在test表空间上建立一些表。
create table t2 tablespace test as select * from dba_objects;
RMAN> backup incremental from scn 3221576548 datafile 8 format '/data/testtest/%U';
Starting backup at 2013-01-04 11:42:17
using channel ORA_DISK_1
backup will be obsolete on date 2013-01-11 11:42:18
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle11g/oradata/test/test01.dbf
channel ORA_DISK_1: starting piece 1 at 2013-01-04 11:42:18
channel ORA_DISK_1: finished piece 1 at 2013-01-04 11:42:19
piece handle=/data/testtest/0bnuiloq_1_1 tag=TAG20130104T114217 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
using channel ORA_DISK_1
backup will be obsolete on date 2013-01-11 11:42:20
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2013-01-04 11:42:22
channel ORA_DISK_1: finished piece 1 at 2013-01-04 11:42:23
piece handle=/data/testtest/0cnuilot_1_1 tag=TAG20130104T114217 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2013-01-04 11:42:23
RMAN> recover copy of datafile 8;
Starting recover at 2013-01-04 11:43:16
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00008 name=/data/testtest/test01.dbf
channel ORA_DISK_1: reading from backup piece /data/testtest/0bnuiloq_1_1
channel ORA_DISK_1: piece handle=/data/testtest/0bnuiloq_1_1 tag=TAG20130104T114217
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 2013-01-04 11:43:20
RMAN> list copy of datafile 8;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - ------------------- ---------- -------------------
2 8 A 2013-01-04 11:43:19 3221577302 2013-01-04 11:42:18
Name: /data/testtest/test01.dbf
Tag: TAG20130104T113649
--可以发现Ckp SCN=3221577302.
--再执行如下脚本，实现切换。
run
{
sql 'alter database datafile 8 offline';
switch datafile 8 to datafilecopy '/data/testtest/test01.dbf';
recover datafile 8;
sql 'alter database datafile 8 online';
}
sql statement: alter database datafile 8 offline
datafile 8 switched to datafile copy
input datafile copy RECID=2 STAMP=803821399 file name=/data/testtest/test01.dbf
Starting recover at 2013-01-04 11:46:38
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2013-01-04 11:46:42
sql statement: alter database datafile 8 online
RMAN> report schema ;
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /u01/app/oracle11g/oradata/test/system01.dbf
2 840 SYSAUX *** /u01/app/oracle11g/oradata/test/sysaux01.dbf
3 1024 UNDOTBS1 *** /u01/app/oracle11g/oradata/test/undotbs01.dbf
4 656 USERS *** /u01/app/oracle11g/oradata/test/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle11g/oradata/test/example01.dbf
6 64 RMAN *** /u01/app/oracle11g/oradata/test/rman01.dbf
7 64 TOOLS *** /u01/app/oracle11g/oradata/test/tools01.dbf
8 64 TEST *** /data/testtest/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 339 TEMP 339 /u01/app/oracle11g/oradata/test/temp01.dbf
--可以发现已经移动了数据文件。这样对业务影响最小。
RMAN> list copy of datafile 8;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - ------------------- ---------- -------------------
5 8 A 2013-01-04 11:50:17 3221577612 2013-01-04 11:49:18
Name: /u01/app/oracle11g/oradata/test/test01.dbf
Tag: TAG20120916T093538
--原来的数据文件自动变成了copy记录在控制文件中。切换回来。
run
{
sql 'alter database datafile 8 offline';
switch datafile 8 to datafilecopy '/u01/app/oracle11g/oradata/test/test01.dbf';
recover datafile 8;
sql 'alter database datafile 8 online';
}
sql statement: alter database datafile 8 offline
datafile 8 switched to datafile copy
input datafile copy RECID=5 STAMP=803821817 file name=/u01/app/oracle11g/oradata/test/test01.dbf
Starting recover at 2013-01-04 11:51:40
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2013-01-04 11:51:44
sql statement: alter database datafile 8 online
RMAN> list copy of datafile 8;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - ------------------- ---------- -------------------
6 8 A 2013-01-04 11:51:39 3221577711 2013-01-04 11:50:24
Name: /data/testtest/test01.dbf
Tag: TAG20130104T113649
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-752054/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-752054/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
