oracle備份如何到異機還原____oracle

 情景描述：

 

有朋友問，MSSQLSERVER將Database Backup還原到其它機器很方便，基本就是傻瓜式操作，oracle有控制檔案、參數檔案一堆東西，

覺得還原很複雜；其實不然，我抽出了點時間，簡單示範下案例，朋友們，參考下。為了簡單快速，我採用RMAN備份與還原

 

 

 

 

--說明：
(1).RMAN備份到異機恢複的時候，db_name需要相同。

如果說要想改成其他的執行個體名，可以在恢複成功後，用nid 命令修改。 執行個體名的資訊會
記錄到控制檔案裡，所以如果在恢複的時候，如果執行個體名不一致，恢複的時候會報錯。

(2).恢複的路徑和源庫不一致時，就需要在restore命令中使用set 命令指定新位置，
並且使用switch datafile all將變更資訊更新的到控制檔案中。

 

測試環境：

來源資料庫伺服器A,安裝在E,備份目錄已在E盤；

來源資料庫伺服器B,安裝在F盤.

 

 

 

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

一、來源資料庫準備工作

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

--1. 查詢DBID

 

SQL> select name,dbid from v$database;

NAME            DBID
--------- ----------
ORCL       1320546556

 

--2. 備份來源資料DB

run {
configure retention policy to recovery window of 14 days;
configure controlfile autobackup on;  --自動開啟控制檔案備份
configure controlfile autobackup format for device type disk to 'E:\backup\controlfile\bak_%F';
allocate channel c1 device type disk format  'E:\backup\data\bak_%u';
allocate channel c2 device type disk format 'E:\backup\data\bak_%u';
sql 'alter system archive log current';
backup incremental level=0 database skip inaccessible
plus archivelog filesperset 20
delete all input;
release channel c1;
release channel c2;
}
allocate channel for maintenance device type disk;
crosscheck backupset;
delete noprompt obsolete;

--或

run {
configure retention policy to recovery window of 14 days;
allocate channel c1 device type disk format  'E:\backup\data\bak_%u';
allocate channel c2 device type disk format 'E:\backup\data\bak_%u';
sql 'alter system archive log current';
backup incremental level=0 database skip inaccessible
plus archivelog filesperset 20
delete all input;
--手動直接指明備份檔案名和路徑
backup current controlfile tag='bak_ctrollfile' format='E:\backup\controlfile\bak_ctl_file_%U_%T';
backup spfile tag='bak_spfile' format='E:\backup\controlfile\bak_spfile_%U_%T';
release channel c1;
release channel c2;
}
allocate channel for maintenance device type disk;
crosscheck backupset;
delete noprompt obsolete;

 

--3.手動備份spfile

create pfile='E:\backup\inittest.ora' from spfile;

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

二、目標庫準備工作：

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

---步驟1. 建立口令檔案

--如果有就不需要建立.

orapwd file=F:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ora password=password

 

--步驟2.恢複參數檔案

--方法1.來源資料庫spfile並拷貝到B資料庫伺服器

\\192.168.2.25\e$\backup\inittest.ORA
複製到
E:\bk\inittest.ORA

 

 

--步驟3.編輯參數檔案中的檔案路徑

如路徑和原來一樣，則不需修改

 eg：
 - *_DUMP_DEST
 - LOG_ARCHIVE_DEST*
 - CONTROL_FILES
 

---執行個體：

orcl.__db_cache_size=939524096
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='F:\app\Administrator'#修改路徑
orcl.__pga_aggregate_target=855638016
orcl.__sga_target=2550136832
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=1526726656
orcl.__streams_pool_size=16777216
*.audit_file_dest='F:\app\Administrator\admin\orcl\adump'  #修改路徑
*.audit_trail='db'
*.compatible='11.2.0.0.0'
#修改路徑
*.control_files='F:\app\Administrator\oradata\orcl\control01.ctl','F:\app\Administrator\oradata\orcl\control02.ctl','F:\app\Administrator\oradata\orcl\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='F:\arch'  #修改路徑
*.db_recovery_file_dest_size=6442450944
*.diagnostic_dest='F:\app\Administrator'  #修改路徑
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='location=f:\arch'  #修改路徑
*.nls_date_format='yyyy-mm-dd hh:mi:ss'
*.open_cursors=300
*.optimizer_capture_sql_plan_baselines=TRUE
*.pga_aggregate_target=845152256
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2536505344
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTBS1'

 

 

--步驟4.重啟執行個體，使用已編輯好的參數檔案

將用pfile將B資料庫伺服器啟動到nomout 狀態

rman target /

startup nomount pfile='e:\bk\inittest.ora'

 

 

--步驟5.恢複控制檔案，裝載資料庫
 

rman target /

set dbid 1320546556

restore controlfile from 'e:\bk\controlfile\BAK_CTL_FILE_6LO6SON4_1_1_20130412';

啟動 restore 於 2013-04-11 13:58:58
使用目標資料庫控制檔案替代恢複目錄
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=129 裝置類型=DISK

通道 ORA_DISK_1: 正在還原控制檔案
通道 ORA_DISK_1: 還原完成, 用時: 00:00:01
輸出檔案名=F:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL
輸出檔案名=F:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL02.CTL
輸出檔案名=F:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL03.CTL
完成 restore 於 2013-04-11 13:59:00

 

--步驟6.將DB啟動到mout狀態

alter database mount;

資料庫已裝載
釋放的通道: ORA_DISK_1

--載入備份目錄

CATALOG START WITH 'E:\bk';

搜尋與樣式 E:\bk 匹配的所有檔案

資料庫未知檔案的列表
====================================
檔案名稱: E:\bk\controlfile\BAK_C-1320546556-20130411-03
檔案名稱: E:\bk\controlfile\BAK_CTL_FILE_65O6QILO_1_1_20130411
檔案名稱: E:\bk\controlfile\BAK_SPFILE_66O6QILQ_1_1_20130411
檔案名稱: E:\bk\data\BAK_60O6QIGV
檔案名稱: E:\bk\data\BAK_61O6QIGV
檔案名稱: E:\bk\data\BAK_62O6QIH1
檔案名稱: E:\bk\data\BAK_63O6QIH1
檔案名稱: E:\bk\data\BAK_64O6QILM

是否確實要將上述檔案列入目錄 (輸入 YES 或 NO)? yes
正在編製檔案目錄...
目錄編製完畢

已列入目錄的檔案的列表
======================
檔案名稱: E:\bk\controlfile\BAK_C-1320546556-20130411-03
檔案名稱: E:\bk\controlfile\BAK_CTL_FILE_65O6QILO_1_1_20130411
檔案名稱: E:\bk\controlfile\BAK_SPFILE_66O6QILQ_1_1_20130411
檔案名稱: E:\bk\data\BAK_60O6QIGV
檔案名稱: E:\bk\data\BAK_61O6QIGV
檔案名稱: E:\bk\data\BAK_62O6QIH1
檔案名稱: E:\bk\data\BAK_63O6QIH1
檔案名稱: E:\bk\data\BAK_64O6QILM

 

 

--步驟7.restore 資料庫

7.1 恢複目錄不同的情況：

 

到Target 庫查詢一下：
SQL>  select trim(file_id),trim(file_name) from dba_data_files ORDER BY FILE_ID ASC;

1 E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
2 E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
3 E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
4 E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
6 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY02.DBF
7 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY03.DBF
8 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY04.DBF
9 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY05.DBF
11 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY06.DBF
12 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY07.DBF
13 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY08.DBF
14 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY09.DBF
15 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY10.DBF
16 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY01.DBF
17 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY11.DBF
18 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY12.DBF
19 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY13.DBF
20 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY14.DBF
21 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY15.DBF
22 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY16.DBF
23 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY17.DBF
24 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY18.DBF
25 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY19.DBF
26 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY20.DBF

 

SQL>select trim(file_id),trim(file_name) from dba_temp_files;

1 E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF

 

 

select 'SET NEWNAME FOR DATAFILE'|| ' '||file_id||' '||'TO'|| ' '||''''||trim(file_name)||'''' ||';'
from dba_data_files ORDER BY FILE_ID ASC;

---如下執行個體:

RUN
{
# rename the datafiles
SET NEWNAME FOR DATAFILE 1 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF';
SET NEWNAME FOR DATAFILE 6 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY02.DBF';
SET NEWNAME FOR DATAFILE 7 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY03.DBF';
SET NEWNAME FOR DATAFILE 8 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY04.DBF';
SET NEWNAME FOR DATAFILE 9 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY05.DBF';
SET NEWNAME FOR DATAFILE 11 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY06.DBF';
SET NEWNAME FOR DATAFILE 12 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY07.DBF';
SET NEWNAME FOR DATAFILE 13 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY08.DBF';
SET NEWNAME FOR DATAFILE 14 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY09.DBF';
SET NEWNAME FOR DATAFILE 15 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY10.DBF';
SET NEWNAME FOR DATAFILE 16 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY01.DBF';
SET NEWNAME FOR DATAFILE 17 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY11.DBF';
SET NEWNAME FOR DATAFILE 18 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY12.DBF';
SET NEWNAME FOR DATAFILE 19 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY13.DBF';
SET NEWNAME FOR DATAFILE 20 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY14.DBF';
SET NEWNAME FOR DATAFILE 21 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY15.DBF';
SET NEWNAME FOR DATAFILE 22 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY16.DBF';
SET NEWNAME FOR DATAFILE 23 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY17.DBF';
SET NEWNAME FOR DATAFILE 24 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY18.DBF';
SET NEWNAME FOR DATAFILE 25 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY19.DBF';
SET NEWNAME FOR DATAFILE 26 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY20.DBF';
SQL "ALTER DATABASE RENAME FILE ''E:\app\Administrator\oradata\orcl\REDO01.LOG''
TO ''F:\app\Administrator\oradata\orcl\REDO01.LOG'' ";
SQL "ALTER DATABASE RENAME FILE ''E:\app\Administrator\oradata\orcl\REDO02.LOG''
TO ''F:\app\Administrator\oradata\orcl\REDO02.LOG'' ";
SQL "ALTER DATABASE RENAME FILE ''E:\app\Administrator\oradata\orcl\REDO03.LOG''
TO ''F:\app\Administrator\oradata\orcl\REDO03.LOG'' ";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}

 

對switch datafile all的說明：
--對於nocatalog 模式下，rman備份的資訊是儲存在控制檔案裡的，包括檔案的路徑資訊。 這裡的switch datafile all的作用，就是更新控制檔案裡的資訊。

--步驟8.recover 資料庫

RECOVER DATABASE;

/***常見錯誤:

歸檔日誌線程=1 序列=1696
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: recover 命令 (在 04/11/2013 15:18:07 上) 失敗
RMAN-06054: 介質恢複正在請求未知的線程 1 序列 1696 的歸檔日誌以及起始 SCN 240793
29

---解決方案:

RMAN> recover database until scn 24159677;

啟動 recover 於 2013-04-11 15:29:05
使用通道 ORA_DISK_1

正在開始介質的恢複
介質恢複完成, 用時: 00:00:02

完成 recover 於 2013-04-11 15:29:11

***/

 

 

----步驟9.open...resetlogs開啟資料庫

alter database open resetlogs;

 

/*******常見錯誤:

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: alter db 命令 (在 04/11/2013 15:38:25 上) 失敗
ORA-00392: 日誌 1 (用於線程 1) 正被清除, 不允許操作
ORA-00312: 聯機日誌 1 線程 1: 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG'

 

----解決辦法:

SQL> select group#,bytes/1024/1024||'M',status from v$log;

    GROUP# BYTES/1024/1024||'M'                      STATUS
---------- ----------------------------------------- ----------------
         1 50M                                       CLEARING_CURRENT
         3 50M                                       CLEARING
         2 50M                                       CLEARING

 

SQL> alter database clear logfile group 1;  --清空記錄檔

 

SQL> alter database clear logfile group 2;

 

SQL> alter database clear logfile group 3;

SQL> select group#,bytes/1024/1024||'M',status from v$log;

   GROUP# BYTES/1024/1024||'M'                      STATUS
--------- ----------------------------------------- ----------------
        1 50M                                       CURRENT
        3 50M                                       UNUSED
        2 50M                                       UNUSED

 

 

*******/

 

---步驟10:處理temp 暫存資料表空間

sqlplus / as sysdba

SQL> select name from v$tempfile;

NAME

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

E:\app\Administrator\oradata\orcl\TEMP01.DBF   --控制檔案中路徑還在E盤

 

--A資料庫資料表空間offline

SQL> alter database tempfile 'E:\app\Administrator\oradata\orcl\TEMP01.DBF' offline;

Database altered.

 

--在OS 層級移動temp的資料檔案到B資料庫伺服器

SQL> mv E:\app\Administrator\oradata\orcl\TEMP01.DBF  F:\app\Administrator\oradata\orcl\TEMP01.DBF

 

--修改控制檔案中temp檔案的資訊

SQL> alter database rename file 'E:\app\Administrator\oradata\orcl\TEMP01.DBF' to 'F:\app\Administrator\oradata\orcl\TEMP01.DBF';

 

 

--temp 資料表空間online

SQL> alter database tempfile 'F:\app\Administrator\oradata\orcl\TEMP01.DBF' online;

Database altered.

 

--驗證

SQL> select name from v$tempfile;

NAME

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

F:\app\Administrator\oradata\orcl\TEMP01.DBF