Oracle 12c RMAN 異機恢復
1、複製源庫的一份 RMAN 全備到目標庫。
查詢源庫的 RMAN 備份
RMAN> list backup of database;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
55 Full 1.60G DISK 00:00:02 16-DEC-15
BP Key: 55 Status: AVAILABLE Compressed: NO Tag: TAG20151216T031039
Piece Name: /oradata/bk/rman_20qou75f_1_1
List of Datafiles in backup set 55
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 20904683 16-DEC-15 /oradata/oradata/site/site/system01.dbf
2 Full 20904683 16-DEC-15 /oradata/oradata/site/site/sysaux01.dbf
3 Full 20904683 16-DEC-15 /oradata/oradata/site/site/undotbs01.dbf
4 Full 20904683 16-DEC-15 /oradata/oradata/site/site/users01.dbf
5 Full 20904683 16-DEC-15 /oradata/oradata/site/site/ts_site_data01.dbf
6 Full 20904683 16-DEC-15 /oradata/oradata/site/site/ts_site_index01.dbf
複製源庫的一份 RMAN 全備到目標庫。
[oracle@DB1 bk]$ scp rman_1vqou75e_1_1 rman_20qou75f_1_1 rman_21qou75m_1_1 oracle@10.100.30.20:/oradata/bk
oracle@10.100.30.20's password:
rman_1vqou75e_1_1 100% 147MB 146.6MB/s 00:01
rman_20qou75f_1_1 100% 1641MB 109.4MB/s 00:15
rman_21qou75m_1_1 100% 8704 8.5KB/s 00:00
檢視控制檔案備份。
需要注意的是，在11G，資料庫全備的時候，RMAN 不會備份控制檔案，除非在備份語句後面加上 INCLUDE CURRENT CONTROLFILE。
例如：BACKUP DATABASE INCLUDE CURRENT CONTROLFILE;
也可以單獨備份控制檔案。
例如：BACKUP CURRENT CONTROLFILE;
在11G的 RMAN 中，有 CONTROLFILE AUTOBACK 這引數，設定這個引數後，每當資料庫執行 RMAN 備份的時候，RMAN 會自動備份控制檔案和引數檔案到指定目錄下。
可以檢視中查詢指定備份是否包含控制檔案，下面檢視中的 CONTROLFILE_INCLUDED 表明備份是否包含控制檔案
SQL> SELECT S.RECID backset_id,
S.START_TIME,
ROUND(P.BYTES / 1024 / 1024 / 1024,2) GB,
S.BACKUP_TYPE,
INCREMENTAL_LEVEL,
P.STATUS,
ROUND(P.ELAPSED_SECONDS) ELAPSED_SECONDS,
P.HANDLE AS "MEDIA_HANDLE",
CONTROLFILE_INCLUDED
FROM V$BACKUP_PIECE P, V$BACKUP_SET S
WHERE P.SET_STAMP = S.SET_STAMP
AND P.SET_COUNT = S.SET_COUNT
ORDER BY P.COMPLETION_TIME;
BACKSET_ID START_TIME GB B INCREMENTAL_LEVEL S ELAPSED_SECONDS MEDIA_HANDLE CONTROLFILE_INCLUDED
---------- ------------------- ---------- - ----------------- - --------------- ---------------------------------------------------------------------- -------------------------
1737 2016-01-16 05:10:49 5.6 L A 171 naqrg5qp_1_1 NO
1738 2016-01-16 05:13:45 .03 D A 130 c-1864798816-20160116-01 YES
1739 2016-01-17 04:44:49 24.59 I 1 A 1195 ncqriom1_1_1 NO
1740 2016-01-17 05:04:45 .03 D A 128 c-1864798816-20160117-00 YES
1741 2016-01-17 05:07:08 5.64 L A 161 neqripvs_1_1 NO
1742 2016-01-17 05:09:54 5.53 L A 171 nfqriq52_1_1 NO
1743 2016-01-17 05:12:51 .03 D A 119 c-1864798816-20160117-01 YES
1744 2016-01-18 04:16:33 29.92 I 1 A 1578 nhqrlbd1_1_1 NO
1745 2016-01-18 04:42:59 .03 D A 120 c-1864798816-20160118-00 YES
1746 2016-01-18 04:45:13 5.33 L A 159 njqrld2p_1_1 NO
1747 2016-01-18 04:47:59 5.19 L A 169 nkqrld7v_1_1 NO
--查詢控制檔案備份
RMAN> LIST BACKUP OF CONTROLFILE;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 17.17M DISK 00:00:00 03-DEC-15
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20151203T233014
Piece Name: /oradata/bk/c-4224792395-20151203-00
Control File Included: Ckp SCN: 2165935 Ckp time: 03-DEC-15
由於源庫開啟了 CONTROLFILE AUTO BACKUP，將全備份時生成的控制檔案也複製到備庫。
[oracle@DB1 bk]$ scp c-4224792395-20151216-00 oracle@10.100.30.20:/oradata/bk
oracle@10.100.30.20's password:
c-4224792395-20151216-00 100% 17MB 17.4MB/s 00:00
2、在目標庫上進行 RMAN 恢復。
目標庫上面已經安裝了 ORACLE 軟體。
設定 ORACLE_SID
[oracle@DB2 ~]$ export ORACLE_SID=site
登入 RMAN ，啟動到 NOMOUNT
STARTUP NOMOUNT
恢復引數檔案
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bk/%F';
RESTORE SPFILE
TO PFILE '/u01/app/oracle/product/12.1.0/db_1/dbs/initsite.ora'
FROM AUTOBACKUP;
SHUTDOWN ABORT;
}
編輯生成的引數檔案，修改對應的控制檔案目錄、audit_file_dest、db_recovery_file_dest 等目錄，保證指定的目錄存在
oracle@DB2 dbs]$ vim initsite.ora
註釋掉 local_listener 引數，否則啟動會報錯
#*.local_listener='LISTENER_SITE'
用新還原的引數檔案啟動資料庫
STARTUP FORCE NOMOUNT PFILE='/u01/app/oracle/product/12.1.0/db_1/dbs/initsite.ora';
執行控制檔案恢復
rman target /
SET DBID 4224792395;
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bk/%F';
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
}
登記傳輸過來的備份到 RMAN
CATALOG START WITH '/oradata/bk/';
檢查資料備份，刪除過期的備份
RMAN> crosscheck backup;
RMAN> delete noprompt expired backup;
檢視歸檔日誌備份的 SCN，決定介質恢復的時間點
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
54 146.61M DISK 00:00:00 16-DEC-15
BP Key: 54 Status: AVAILABLE Compressed: NO Tag: TAG20151216T031038
Piece Name: /oradata/bk/rman_1vqou75e_1_1
List of Archived Logs in backup set 54
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 206 20633382 15-DEC-15 20716789 16-DEC-15
1 207 20716789 16-DEC-15 20895202 16-DEC-15
1 208 20895202 16-DEC-15 20904669 16-DEC-15
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
56 8.00K DISK 00:00:00 16-DEC-15
BP Key: 56 Status: AVAILABLE Compressed: NO Tag: TAG20151216T031046
Piece Name: /oradata/bk/rman_21qou75m_1_1
List of Archived Logs in backup set 56
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 209 20904669 16-DEC-15 20904701 16-DEC-15
執行 RESTORE 命令，恢復資料檔案
RMAN> RESTORE DATABASE;
執行介質恢復
SQL> RECOVER DATABASE using backup controlfile UNTIL CHANGE 20716789;
Media recovery complete.
3、開啟資料庫
[oracle@DB2 site]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 16 11:50:22 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 5 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/site/redo05.log'
出現報錯，清空報錯的日誌檔案
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;
Database altered.
開啟資料庫
SQL> alter database open resetlogs;
Database altered.
查詢源庫的 RMAN 備份
RMAN> list backup of database;
------- ---- -- ---------- ----------- ------------ ---------------
55 Full 1.60G DISK 00:00:02 16-DEC-15
BP Key: 55 Status: AVAILABLE Compressed: NO Tag: TAG20151216T031039
Piece Name: /oradata/bk/rman_20qou75f_1_1
List of Datafiles in backup set 55
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 20904683 16-DEC-15 /oradata/oradata/site/site/system01.dbf
2 Full 20904683 16-DEC-15 /oradata/oradata/site/site/sysaux01.dbf
3 Full 20904683 16-DEC-15 /oradata/oradata/site/site/undotbs01.dbf
4 Full 20904683 16-DEC-15 /oradata/oradata/site/site/users01.dbf
5 Full 20904683 16-DEC-15 /oradata/oradata/site/site/ts_site_data01.dbf
6 Full 20904683 16-DEC-15 /oradata/oradata/site/site/ts_site_index01.dbf
複製源庫的一份 RMAN 全備到目標庫。
oracle@10.100.30.20's password:
rman_1vqou75e_1_1 100% 147MB 146.6MB/s 00:01
rman_20qou75f_1_1 100% 1641MB 109.4MB/s 00:15
rman_21qou75m_1_1 100% 8704 8.5KB/s 00:00
檢視控制檔案備份。
需要注意的是，在11G，資料庫全備的時候，RMAN 不會備份控制檔案，除非在備份語句後面加上 INCLUDE CURRENT CONTROLFILE。
例如：BACKUP DATABASE INCLUDE CURRENT CONTROLFILE;
也可以單獨備份控制檔案。
例如：BACKUP CURRENT CONTROLFILE;
在11G的 RMAN 中，有 CONTROLFILE AUTOBACK 這引數，設定這個引數後，每當資料庫執行 RMAN 備份的時候，RMAN 會自動備份控制檔案和引數檔案到指定目錄下。
可以檢視中查詢指定備份是否包含控制檔案，下面檢視中的 CONTROLFILE_INCLUDED 表明備份是否包含控制檔案
SQL> SELECT S.RECID backset_id,
S.START_TIME,
ROUND(P.BYTES / 1024 / 1024 / 1024,2) GB,
S.BACKUP_TYPE,
INCREMENTAL_LEVEL,
P.STATUS,
ROUND(P.ELAPSED_SECONDS) ELAPSED_SECONDS,
P.HANDLE AS "MEDIA_HANDLE",
CONTROLFILE_INCLUDED
FROM V$BACKUP_PIECE P, V$BACKUP_SET S
WHERE P.SET_STAMP = S.SET_STAMP
AND P.SET_COUNT = S.SET_COUNT
ORDER BY P.COMPLETION_TIME;
BACKSET_ID START_TIME GB B INCREMENTAL_LEVEL S ELAPSED_SECONDS MEDIA_HANDLE CONTROLFILE_INCLUDED
---------- ------------------- ---------- - ----------------- - --------------- ---------------------------------------------------------------------- -------------------------
1737 2016-01-16 05:10:49 5.6 L A 171 naqrg5qp_1_1 NO
1738 2016-01-16 05:13:45 .03 D A 130 c-1864798816-20160116-01 YES
1739 2016-01-17 04:44:49 24.59 I 1 A 1195 ncqriom1_1_1 NO
1740 2016-01-17 05:04:45 .03 D A 128 c-1864798816-20160117-00 YES
1741 2016-01-17 05:07:08 5.64 L A 161 neqripvs_1_1 NO
1742 2016-01-17 05:09:54 5.53 L A 171 nfqriq52_1_1 NO
1743 2016-01-17 05:12:51 .03 D A 119 c-1864798816-20160117-01 YES
1744 2016-01-18 04:16:33 29.92 I 1 A 1578 nhqrlbd1_1_1 NO
1745 2016-01-18 04:42:59 .03 D A 120 c-1864798816-20160118-00 YES
1746 2016-01-18 04:45:13 5.33 L A 159 njqrld2p_1_1 NO
1747 2016-01-18 04:47:59 5.19 L A 169 nkqrld7v_1_1 NO
--查詢控制檔案備份
RMAN> LIST BACKUP OF CONTROLFILE;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 17.17M DISK 00:00:00 03-DEC-15
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20151203T233014
Piece Name: /oradata/bk/c-4224792395-20151203-00
Control File Included: Ckp SCN: 2165935 Ckp time: 03-DEC-15
由於源庫開啟了 CONTROLFILE AUTO BACKUP，將全備份時生成的控制檔案也複製到備庫。
[oracle@DB1 bk]$ scp c-4224792395-20151216-00 oracle@10.100.30.20:/oradata/bk
oracle@10.100.30.20's password:
c-4224792395-20151216-00 100% 17MB 17.4MB/s 00:00
2、在目標庫上進行 RMAN 恢復。
目標庫上面已經安裝了 ORACLE 軟體。
設定 ORACLE_SID
[oracle@DB2 ~]$ export ORACLE_SID=site
% rman NOCATALOG RMAN> CONNECT TARGET /
--DBID的值可以登入主庫的 RMAN 檢視到，也可以在 RMAN 的 CONTROLFILE AUTO BACKUP 生成的檔名中看到SET DBID 4224792395;
STARTUP NOMOUNT
恢復引數檔案
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bk/%F';
RESTORE SPFILE
TO PFILE '/u01/app/oracle/product/12.1.0/db_1/dbs/initsite.ora'
FROM AUTOBACKUP;
SHUTDOWN ABORT;
}
編輯生成的引數檔案，修改對應的控制檔案目錄、audit_file_dest、db_recovery_file_dest 等目錄，保證指定的目錄存在
oracle@DB2 dbs]$ vim initsite.ora
註釋掉 local_listener 引數，否則啟動會報錯
用新還原的引數檔案啟動資料庫
STARTUP FORCE NOMOUNT PFILE='/u01/app/oracle/product/12.1.0/db_1/dbs/initsite.ora';
執行控制檔案恢復
rman target /
SET DBID 4224792395;
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bk/%F';
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
}
登記傳輸過來的備份到 RMAN
CATALOG START WITH '/oradata/bk/';
檢查資料備份，刪除過期的備份
RMAN> crosscheck backup;
RMAN> delete noprompt expired backup;
檢視歸檔日誌備份的 SCN，決定介質恢復的時間點
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
54 146.61M DISK 00:00:00 16-DEC-15
BP Key: 54 Status: AVAILABLE Compressed: NO Tag: TAG20151216T031038
Piece Name: /oradata/bk/rman_1vqou75e_1_1
List of Archived Logs in backup set 54
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 206 20633382 15-DEC-15 20716789 16-DEC-15
1 207 20716789 16-DEC-15 20895202 16-DEC-15
1 208 20895202 16-DEC-15 20904669 16-DEC-15
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
56 8.00K DISK 00:00:00 16-DEC-15
BP Key: 56 Status: AVAILABLE Compressed: NO Tag: TAG20151216T031046
Piece Name: /oradata/bk/rman_21qou75m_1_1
List of Archived Logs in backup set 56
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 209 20904669 16-DEC-15 20904701 16-DEC-15
執行 RESTORE 命令，恢復資料檔案
RMAN> RESTORE DATABASE;
執行介質恢復
SQL> RECOVER DATABASE using backup controlfile UNTIL CHANGE 20716789;
Media recovery complete.
3、開啟資料庫
[oracle@DB2 site]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 16 11:50:22 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 5 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/site/redo05.log'
出現報錯，清空報錯的日誌檔案
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;
Database altered.
開啟資料庫
Database altered.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-1873417/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle RMAN異機恢復Oracle
- rman恢復方案和oracle異機恢復Oracle
- oracle的RMAN異機恢復Oracle
- ORACLE RMAN異機異目錄恢復Oracle
- Oracle 11g RMAN 異機恢復Oracle
- RMAN異機恢復總結
- oracle 12C rman下表的恢復Oracle
- oracle 異機恢復Oracle
- Oracle 12C新特性-RMAN恢復表Oracle
- oracle10gR2之RMAN異機恢復案例Oracle
- oracle 10.2.0.1 win 32 rman 備份異機恢復Oracle
- 【12c 庫異機恢復】實驗
- rman之同平臺異機恢復
- RMAN異機恢復：RAC到單例項單例
- 異機恢復RMAN-05517解決方法
- RMAN備份異機恢復流程總結
- oracle冷備份、恢復和異機恢復Oracle
- Oracle 11.2.0.4 從單例項，使用RMAN 異機恢復到RACOracle單例
- RMAN異地恢復實戰
- 兩篇oracle異機恢復文章Oracle
- 初試Oracle的RMAN異地異系統恢復…………失敗…………Oracle
- RMAN異機恢復異作業系統（Linux到Windows）作業系統LinuxWindows
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- ORACLE 11.2.0.4 RAC RMAN異機恢復之ORA-15001Oracle
- 用 RMAN 備份異機恢復 遷移資料
- oracle11gR2_64之rman異機恢復(FS->FS)完整案例Oracle
- oracle實驗記錄 （恢復-rman恢復）Oracle
- 12C grid CDB異機恢復+歸檔(DDL操作)
- rman 恢復機制與恢復測試
- Oracle 11g RMAN 異機恢復報錯 RMAN-06172、ORA-07202Oracle
- Oracle RMAN恢復測試Oracle
- Oracle rman 各種恢復Oracle
- 使用RMAN實現異機備份恢復(WIN平臺)
- standby全庫rman備份檔案恢復到異機
- G017-ORACLE-MIGRATION-01 RMAN備份異機不完全恢復Oracle
- ORACLE中採用rman備份異機恢復資料庫詳細過程Oracle資料庫
- Netbackup+oracle db 異機恢復案例Oracle
- oracle 12c新特性：Rman表恢復之直接匯入系統Oracle