[20130104]快速移動資料檔案.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQLServer移動資料檔案SQLServer
- 線上移動資料檔案
- ORACLE移動資料檔案Oracle
- oracle資料庫移動資料檔案、日誌檔案和控制檔案Oracle資料庫
- [20181031]12c 線上移動資料檔案.txt
- Oracle 資料檔案移動步驟Oracle
- 移動Oracle資料檔案的方法Oracle
- 在Oracle中移動資料檔案Oracle
- 在ORACLE移動資料庫檔案Oracle資料庫
- oracle中移動控制檔案、資料檔案、日誌檔案Oracle
- 移動資料檔案從ASM到檔案系統ASM
- 資料檔案,表空間的移動
- 在ASM Diskgroup間移動資料檔案ASM
- 移動資料檔案，平衡磁碟負載負載
- 在ORACLE中移動資料庫檔案Oracle資料庫
- 通過移動資料檔案來均衡檔案I/O
- 透過移動資料檔案來均衡檔案I/O
- 在Oracle中移動資料檔案、控制檔案和日誌檔案Oracle
- 移動資料檔名中含有特殊字元的資料檔案方法字元
- 資料檔案遷移
- 12c pdb線上移動資料檔案或者重新命名資料檔案
- oracle 修改資料檔案位置（路徑）（移動）Oracle
- Oracle資料庫新增和移動控制檔案Oracle資料庫
- Oracle 移動資料檔案的操作方法Oracle
- 移動資料庫物理檔案 Move Physical Files資料庫
- 在ORACLE中移動資料庫檔案(轉)Oracle資料庫
- 移動資料檔案從檔案系統到ASM磁碟組中ASM
- 資料檔案的遷移
- oracle 資料檔案遷移Oracle
- oracle資料檔案遷移Oracle
- 表空間online移動資料檔案
- dataguard之邏輯備庫移動資料檔案
- 在ORACLE中移動資料庫檔案在（轉）Oracle資料庫
- 移動檔案
- 移動資料檔案、系統表空間檔案、臨時表空間檔案
- 怎麼快速複製移動大量電腦檔案？
- PostgreSQL在不同的表空間移動資料檔案SQL
- 12c 聯機狀態移動資料檔案