遷移資料檔案到ASM【轉】

1.遷移資料檔案到asm 

1）資料庫一緻性情況下遷移: 

将資料庫啟動到mount狀态,生成rman copy 語句,然後在rman中執行: 

sql> startup mount 

sql> select q'(copy datafile ')'||name||q'(' to '+oradata';)' from v$datafile; 

q'(copydatafile')'||name||q'('to'+oradata';)' 

------------------------------------------------------------------------------- 

copy datafile 'd:\nsoadp\system01.dbf' to '+oradata'; 

copy datafile 'd:\nsoadp\sysaux01.dbf' to '+oradata'; 

copy datafile 'f:\nsoadp\undotbs01.dbf' to '+oradata'; 

copy datafile 'd:\nsoadp\users01.dbf' to '+oradata'; 

rman target / 

rman> run 

2> { 

3> copy datafile 'd:\nsoadp\system01.dbf' to '+oradata'; 

4> copy datafile 'd:\nsoadp\sysaux01.dbf' to '+oradata'; 

5> copy datafile 'f:\nsoadp\undotbs01.dbf' to '+oradata'; 

6> copy datafile 'd:\nsoadp\users01.dbf' to '+oradata'; 

7>} 

啟動 backup 于 2015-04-15 10:02:44 

使用通道 ora_disk_1 

通道 ora_disk_1: 啟動資料檔案副本 

輸入資料檔案: 檔案号=00001 名稱=d:\nsoadp\system01.dbf 

輸出檔案名=+oradata/nsoa/datafile/system.260.877082571 标記=tag20150415t100250 recid=265 stamp=877082603 

通道 ora_disk_1: 資料檔案複制完畢, 經過時間: 00:00:35 

完成 backup 于 2015-04-15 10:03:25 

生成更改控制檔案資料名的語句,然後執行更改: 

sql> select --t.name,t.file#,c.file#,c.name 

  2  q'(alter database rename file ')'||c.name||q'(' to ')'||t.name||q'(';)' 

  3   from v$datafile_copy t left join v$datafile c 

  4  on t.file#=c.file# 

  5  where t.name is not null  order by completion_time desc; 

sql> alter database rename file 'd:\nsoadp\system01.dbf' to '+oradata/nsoa/datafile/system.260.877082571'; 

.... 

2)資料庫非一緻性情況下遷移(歸檔模式): 

使用alterdatabase datafile '' offline指令将資料檔案offline,然後同上面的方式一樣處理,最後将資料檔案online 

sql> alter database datafile '/u01/oradata/sydb/user01.dbf' offline; 

rman> copy datafile '/u01/oradata/sydb/user01.dbf' to '+oradata'; 

starting backup at 23-apr-15 

using target database control file instead of recovery catalog 

allocated channel: ora_disk_1 

channel ora_disk_1: sid=180 device type=disk 

channel ora_disk_1: starting datafile copy 

input datafile file number=00004 name=/u01/oradata/sydb/user01.dbf 

output file name=+oradata/sydb/datafile/users.257.877803115 tag=tag20150423t181155 recid=1 stamp=877803123 

channel ora_disk_1: datafile copy complete, elapsed time: 00:00:15 

finished backup at 23-apr-15 

sql> alter database rename file '/u01/oradata/sydb/user01.dbf' to '+oradata/sydb/datafile/users.257.877803115'; 

sql> alter database datafile '+oradata/sydb/datafile/users.257.877803115' online; 

alter database datafile '+oradata/sydb/datafile/users.257.877803115' online 

* 

error at line 1: 

ora-01113: file 4 needs media recovery 

ora-01110: data file 4: '+oradata/sydb/datafile/users.257.877803115' 

sql> recover datafile '+oradata/sydb/datafile/users.257.877803115'; 

media recovery complete. 

2.遷移日志檔案到asm 

可以在mount或open狀态遷移非current或active日志檔案到asm;方法将已有的日志檔案(每個日志組必須有一個member)或日志組 

(資料庫必須有兩個日志組存在)删除,然後添加新的日志檔案或日志組; 

alter database drop logfile member 'e:\nsoadp\redo07.log';  #删除日志檔案 

alter database drop logfile group 2;  #删除日志組 

alter database add logfile member '+oradata' to group 7;   #添加日志檔案 

alter database add logfile ( '+oradata','oradata') size 50m;  #添加有兩個成員的日志組 

alter database add logfile '+oradata','oradata' size 50m;  #同時添加兩個日志組 

3.遷移控制檔案到asm 

首先确定好控制檔案位置和名稱 

sql> show parameter control_file 

name                                 type        value 

------------------------------------ ----------- ------------------------------ 

control_file_record_keep_time        integer     7 

control_files                        string      f:\nsoadp\control01.ctl 

修改control_files初始化參數值為asm,然後重新開機資料庫到nomount狀态: 

sql> alter system set control_files='+oradata' scope=spfile; 

sql> shutdown immediate; 

sql> startup nomount 

使用rman複制目前的控制檔案到asm 

rman> restore controlfile from 'f:\nsoadp\control01.ctl'; 

啟動 restore 于 2015-04-22 09:40:30 

使用目标資料庫控制檔案替代恢複目錄 

配置設定的通道: ora_disk_1 

通道 ora_disk_1: sid=70 裝置類型=disk 

通道 ora_disk_1: 已複制控制檔案副本 

輸出檔案名=+oradata/nsoa/controlfile/current.314.877686033 

完成 restore 于 2015-04-22 09:40:39 

複制代碼 

注:如果使用備份的控制檔案進行還原,而資料庫以前有過resetlogs操作或建立控制檔案時建立語句指定了resetlogs,則必須以resetlogs方式打開資料庫。 

sql> alter database mount; 

name                                 type                   value 

------------------------------------ ---------------------- ------------------------------ 

control_file_record_keep_time        integer                7 

control_files                        string                 +oradata/nsoa/controlfile/curr 

                                                            ent.314.877686033 

sql> alter database open;