[20130104]快速移動資料檔案.txt

lfree發表於2013-01-04
[20130104]快速移動資料檔案.txt

如果要快速移動資料檔案，對業務的影響最小，可以使用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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章