oracle 10g RMAN備份及恢復
Copy ---物理備份 原始檔大小與備份的檔案大小一致
Backup ---邏輯物理備份，基於塊級別的備份。備份的是已經使用的塊也就是熱點塊。
RMAN的使用前提條件
1、歸檔模式
2、資料庫啟動在mount或者open狀態
RMAN 使用方式
確認呼叫的是oracle的RMAN而不是OS自帶的RMAN
|
[oracle@yang ~]$ which rman /u01/app/oracle/product/10.2.0/db_1/bin/rman [oracle@yang ~]$ [root@yang ~]# find / -name rman /u01/app/oracle/product/10.2.0/db_1/bin/rman /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/WEB-INF/perl/db/rman /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/database/rman /u01/app/oracle/product/10.2.0/db_1/sysman/admin/scripts/db/rman /home/oracle/bf/db_1/bin/rman /home/oracle/bf/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/WEB-INF/perl/db/rman /home/oracle/bf/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/database/rman /home/oracle/bf/db_1/sysman/admin/scripts/db/rman
如果最先呼叫的不是oracle的RMAN可以修改環境變數 [oracle@yang ~]$ vi ./.bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH unset USERNAME export DISPLAY=192.168.56.1:0.0 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH (讓$ORACLE_HOME/bin在前，優先執行) export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib export ORACLE_SID=orcl export NLS_LANG=american_america.zhs16gbk （儲存退出） 生效的簡單方法（重新登入就可以） [oracle@yang~]$ su - Password: [root@yang ~]# su - oracle [oracle@yangzai ~]$ 確認是否生效 [oracle@yang ~]$ which rman /u01/app/oracle/product/10.2.0/db_1/bin/rman
|
本地連線方式與遠端連線方式
|
[oracle@yang ~]$ rman target / （/ 的意思是本地預設例項） [oracle@yang ~]$ echo $ORACLE_SID orcl 遠端連線方式 [oracle@yang ~]$ rman target sys/oracle@orcl 或者使用下面方法 [oracle@yang ~]$ rman
Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 12 02:22:25 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: ORCL (DBID=1374419528)
RMAN>connect target sys/oracle@orcl 通過連線RMAN 識別狀態 [oracle@yang ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 12 02:33:15 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1374419528)
RMAN> 通過SQL 查詢 SQL> select dbid,name,open_mode from v$database;
DBID NAME OPEN_MODE ---------- --------- ---------- 1374419528 ORCL READ WRITE
SQL>
|
|
|
RMAN備份
|
資料庫全備 （不會備份聯機日誌檔案） [oracle@yang ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 12 02:33:15 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1374419528)
RMAN> backup database;
備份引數檔案 RMAN> backup spfile; 備份當前使用的控制檔案 RMAN> backup current controlfile; 備份單獨資料檔案 RMAN> backup datafile 2; 備份表空間 RMAN> backup tablespace users; 備份歸檔日誌 RMAN> backup archivelog all; 備份歸檔日誌並刪除被備份的歸檔日誌 RMAN> backup archivelog all delete input; 全庫備份幷包含歸檔日誌（也可以加 delete input） RMAN> backup full database plus archivelog; 備份全庫跳過某個表空間不備份（以users為例） SQL> alter tablespace users read only; ---需要將表空間置為只讀 RMAN>backup database skip readonly; --跳過只讀的 SQL> alter tablespace users offline; --置為離線 RMAN>backup database skip offline; --跳過離線的
備份存放的預設路徑 檔案系統： [oracle@yang oracle]$ ls /u01/app/oracle/flash_recover_area/ORCL/backupset/ ASM ASMCMD> pwd +data/orcl/backupset
SQL> show parameter db_rec
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +DATA db_recovery_file_dest_size big integer 2G db_recycle_cache_size big integer 0 SQL>
備份時指定存放路徑 RMAN>backup database format ‘/u02/dbfall%U’; ---必須要使用變數
format的替換變數 1. %d --資料庫的db_name 2. %n --資料庫的8位長度的db_name，不足部分用“x”後面填充 3. %N --資料庫表空間的name 4. %I --資料庫的dbid 5. %T --年月日（YYYYMMDD） == %Y%M%D （%M 位於該年中第幾個月%D位於該月中第幾天） 6. %t --9位字元的timestamp 7. %s --備份集序號 8. %p --備份片序號 9. %c --備份片的多個copy的序號 10. %e --archived redo file 的序列號，只能用在archived redo 上 11. %f --datafile filenmuber，只能用在備份datafile、tablespace上，否則沒有意義 12. %F --複合format == c-IIIIIIIIII-YYYYMMDD-QQ，其中IIIIIIIIII為dbid，YYYYMMDD為年月日，QQ為十六進位制的備份片的多個copy的序號（00-ff）。 13. %u --8為字母唯一串 14. %U --複合format == · backupset：%u_%p_%c · copy of datafile：data-D-%d_id-%I_TS-%N_FNO-%f_%u · copy of archived log：arch-D_%d-id-%I_S-%e_T-%h_A-%a_%u · copy of controlfile：cf-D_%d-id-%I_%u
增量備份 RMAN> backup incremental level=0 database; 或者 run { allocate channel cl type disk; backup incremental level 0 format "/u01/rmanbak/inc0_%u_%T" tag monday_inc0 database; release channel cl; }
|
Copy 備份
|
備份當前控制檔案到/u02/backctl.ctl下 RMAN> copy current controlfile to '/u02/backctl.ctl'; ----使用copy必須要指定一個路徑 SQL>alter database backup controlfile to ‘/u02/bakctl.ctl’; ---與上面語句是等效的 備份資料檔案 RMAN> copy datafile 4 to '/u02/dbf4.dbf';
備份全庫 RMAN> backup as copy database;
|
檢視曾經做過的備份
|
檢視所有backup的操作 ***（備份集只需要將backup變為backupset） RMAN> list backup;
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 631.66M DISK 00:03:13 12-MAY-14 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/nnndf0_tag20140512t025337_0.276.847335217 List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 715714 12-MAY-14 +DATA/orcl/datafile/system.256.846638591 2 Full 715714 12-MAY-14 +DATA/orcl/datafile/undotbs1.258.846638595 3 Full 715714 12-MAY-14 +DATA/orcl/datafile/sysaux.257.846638593 4 Full 715714 12-MAY-14 +DATA/orcl/datafile/users.259.846638599 5 Full 715714 12-MAY-14 +DATA/orcl/datafile/example.269.846638953
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 6.80M DISK 00:00:07 12-MAY-14 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 Control File Included: Ckp SCN: 715784 Ckp time: 12-MAY-14 SPFILE Included: Modification time: 12-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3 Full 80.00K DISK 00:00:03 12-MAY-14 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20140512T031638 Piece Name: +DATA/orcl/backupset/2014_05_12/nnsnf0_tag20140512t031638_0.278.847336599 SPFILE Included: Modification time: 12-MAY-14
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 4 211.67M DISK 00:01:13 12-MAY-14 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20140512T035343 Piece Name: +DATA/orcl/backupset/2014_05_12/annnf0_tag20140512t035343_0.280.847338827
List of Archived Logs in backup set 4 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 3 556989 04-MAY-14 581849 04-MAY-14 1 4 581849 04-MAY-14 618249 06-MAY-14 1 5 618249 06-MAY-14 646250 07-MAY-14 1 6 646250 07-MAY-14 694945 08-MAY-14 1 7 694945 08-MAY-14 695857 08-MAY-14 1 8 695857 08-MAY-14 717051 12-MAY-14
RMAN>
檢視所有copy的記錄 RMAN> list copy;
List of Datafile Copies Key File S Completion Time Ckp SCN Ckp Time Name ------- ---- - --------------- ---------- --------------- ---- 3 4 A 12-MAY-14 719685 12-MAY-14 /u02/dbf4.dbf
List of Control File Copies Key S Completion Time Ckp SCN Ckp Time Name ------- - --------------- ---------- --------------- ---- 2 A 12-MAY-14 719581 12-MAY-14 /u02/backctl.ctl
List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - --------- ---- 1 1 3 A 04-MAY-14 +DATA/orcl/1_3_846638803.dbf 2 1 4 A 04-MAY-14 +DATA/orcl/1_4_846638803.dbf 3 1 5 A 06-MAY-14 +DATA/orcl/1_5_846638803.dbf 4 1 6 A 07-MAY-14 +DATA/orcl/1_6_846638803.dbf 5 1 7 A 08-MAY-14 +DATA/orcl/1_7_846638803.dbf 6 1 8 A 08-MAY-14 +DATA/orcl/1_8_846638803.dbf
RMAN> 可以單獨檢視某一個備份 RMAN> list backup of archivelog all; ---檢視所有歸檔日誌備份
List of Backup Sets ===================
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 4 211.67M DISK 00:01:13 12-MAY-14 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20140512T035343 Piece Name: +DATA/orcl/backupset/2014_05_12/annnf0_tag20140512t035343_0.280.847338827
List of Archived Logs in backup set 4 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 3 556989 04-MAY-14 581849 04-MAY-14 1 4 581849 04-MAY-14 618249 06-MAY-14 1 5 618249 06-MAY-14 646250 07-MAY-14 1 6 646250 07-MAY-14 694945 08-MAY-14 1 7 694945 08-MAY-14 695857 08-MAY-14 1 8 695857 08-MAY-14 717051 12-MAY- RMAN> list backup of controlfile; ------檢視所有控制檔案備份
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 6.80M DISK 00:00:07 12-MAY-14 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 Control File Included: Ckp SCN: 715784 Ckp time: 12-MAY-14
RMAN> list backup of database; -----檢視所有資料檔案備份
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 631.66M DISK 00:03:13 12-MAY-14 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/nnndf0_tag20140512t025337_0.276.847335217 List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 715714 12-MAY-14 +DATA/orcl/datafile/system.256.846638591 2 Full 715714 12-MAY-14 +DATA/orcl/datafile/undotbs1.258.846638595 3 Full 715714 12-MAY-14 +DATA/orcl/datafile/sysaux.257.846638593 4 Full 715714 12-MAY-14 +DATA/orcl/datafile/users.259.846638599 5 Full 715714 12-MAY-14 +DATA/orcl/datafile/example.269.846638953
RMAN> list backup of spfile; ------檢視所有spfile備份
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 6.80M DISK 00:00:07 12-MAY-14 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 SPFILE Included: Modification time: 12-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3 Full 80.00K DISK 00:00:03 12-MAY-14 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20140512T031638 Piece Name: +DATA/orcl/backupset/2014_05_12/nnsnf0_tag20140512t031638_0.278.847336599 SPFILE Included: Modification time: 12-MAY-14
RMAN> |
RMAN的環境變數及配置
檢視環境變數
|
[oracle@yang ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 13 01:51:24 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1374419528)
RMAN> show all;
using target database control file instead of recovery catalog RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default -------定義RMAN備份的冗餘度 1表示備份集有1個，也可以定義保留備份時間 CONFIGURE BACKUP OPTIMIZATION OFF; # default ------定義對備份進行優化 預設為off CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default ----定義備份的存放裝置預設為磁碟，可以改為磁帶 CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default ----定義控制檔案是否自動備份，預設為off CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default –--定義如果控制檔案自動備份那麼備份到磁碟且為%F複合format CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default ----定義並行度也就是備份時開啟多少通道 CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default ---定義copy或者備份資料檔案的裝置為disk 且只備份1分 CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default --定義copy或者備份歸檔日誌的裝置是disk 且只備份1分 CONFIGURE MAXSETSIZE TO UNLIMITED; # default ----定義了在備份時備份片的大小的限制 預設為UNLIMITED無限制 CONFIGURE ENCRYPTION FOR DATABASE OFF; # default -----定義了RMAN備份時是否加密，預設為off CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default -------定義了加密的運算，預設為AES128 CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default ---定義了歸檔日誌失效後是否刪除，預設為none不刪除 CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default—開啟了自動備份控制檔案會在後面路徑生成快照
RMAN>
|
修改環境變數
|
設定備份保留策略為基於時間 RMAN> CONFIGURE RETENTION POLICY TO ;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found ";": expecting one of: "none, redundancy, recovery" –RMAN會告訴你後面要跟多有哪些引數 RMAN-01007: at line 2 column 1 file: standard input 根據提示定義 RMAN> CONFIGURE RETENTION POLICY TO recovery window of 10 days; ---定義了備份保留策略為10天也就是可以恢復到10天前
new RMAN configuration parameters: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS; new RMAN configuration parameters are successfully stored
RMAN>
RMAN> show all;
RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS; ---要麼基於時間，要麼基於冗餘度
設定控制檔案的自動備份 RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored
RMAN> RMAN> show all;
RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; 建立表空間確認是否會自動備份 （資料庫結構發生變化以及資料庫全備控制檔案都會自動備份） RMAN> list backup of controlfile; ---檢視當前有幾個控制檔案備份
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 6.80M DISK 00:00:07 12-MAY-14 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 Control File Included: Ckp SCN: 715784 Ckp time: 12-MAY-14
RMAN> SQL> create tablespace u2 datafile '+data' size 10m;
Tablespace created.
SQL> RMAN> list backup of controlfile;
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 6.80M DISK 00:00:07 12-MAY-14 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 Control File Included: Ckp SCN: 715784 Ckp time: 12-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 5 Full 6.80M DISK 00:00:07 13-MAY-14 BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20140513T023148 Piece Name: +DATA/orcl/autobackup/2014_05_13/s_847420308.282.847420313 Control File Included: Ckp SCN: 731317 Ckp time: 13-MAY-14
RMAN> 設定並行度的數量
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters are successfully stored
RMAN>
設定基於冗餘的備份
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2; --配置備份資料檔案為2份
new RMAN configuration parameters: CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2; new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2; --配置備份歸檔日誌為2份
new RMAN configuration parameters: CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2; new RMAN configuration parameters are successfully stored
RMAN> RMAN> configure channel 2 device type disk format '/u02/%U','/u01/app/%U '; -- 設定備份的存放路徑為2個
new RMAN configuration parameters: CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/u02/%U', '/u01/app/%U'; new RMAN configuration parameters are successfully stored
RMAN> 這時候備份時無效的 RMAN> backup spfile;
Starting backup at 13-MAY-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=143 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=140 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 13-MAY-14 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/13/2014 03:00:02 ORA-19806: cannot make duplex backups in recovery area
RMAN> 需要修改db_recovery_file_dest引數 SQL> show parameter db_re
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +DATA db_recovery_file_dest_size big integer 2G db_recycle_cache_size big integer 0 SQL> alter system set db_recovery_file_dest='';
System altered.
SQL> 開啟優化備份
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters are successfully stored
RMAN> 設定壓縮備份 RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found ";": expecting one of: "backupset, copy, compressed" ---根據提示輸入引數 RMAN-01007: at line 1 column 56 file: standard input
RMAN> RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
old RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET; new RMAN configuration parameters are successfully stored released channel: ORA_DISK_1 released channel: ORA_DISK_2
RMAN> |
臨時變數的修改（使用RUN）
|
定義兩個通道備份全庫 RMAN> run { 2> allocate channel a1 type disk; 3> allocate channel a2 type disk; 4> backup database; 5> release channel a1; 6> release channel a2; 7> } 定義備份存放裝置為磁帶備份users表空間 RMAN> run { 2> allocate channel a1 type sbt; 3> allocate channel a2 type sbt; 4> backup tablespace users; 5> release channel a1; 6> release channel a2; 7> } 指定一個備份集放多少檔案 RMAN> run { 2> allocate channel a1 type disk; 3> allocate channel a2 type disk; 4> backup database filesperset 3; 5> release channel a1; 6> release channel a2; 7> }
定義備份片的大小 （也就是控制單個備份片的大小） RMAN> run { 2> allocate channel a1 type disk maxpiecesize 1g; 3> allocate channel a2 type disk maxpiecesize 1g; 4> backup database; 5> release channel a1; 6> release channel a2; 7> }
可以指定路徑 RMAN> run{ --執行 2> allocate channel cha1 type disk; --開始分配名稱為cha1型別為磁碟的通道 3> backup -- 備份 4> format '/u01/rmanbak/full_%t' --備份到目錄為/u01/rmanbak/名為full_備份集時間戳的檔案 5> tag full_backup_bat --設定標籤為full_backup_bat 6> database; --備份的是資料庫 7> release channel cha1; --完成後釋放通道 8> }
|
備份的管理
|
檢查所有備份是有效還是無效 RMAN> crosscheck backup;
allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=150 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=154 devtype=DISK crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_12/nnndf0_tag20140512t025337_0.276.847335217 recid=1 stamp=847335217 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 recid=2 stamp=847335417 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_12/nnsnf0_tag20140512t031638_0.278.847336599 recid=3 stamp=847336599 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_12/annnf0_tag20140512t035343_0.280.847338827 recid=4 stamp=847338826 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/autobackup/2014_05_13/s_847420308.282.847420313 recid=5 stamp=847420311 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/0ap858n7_1_1 recid=6 stamp=847422185 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/0ap858n7_1_2 recid=7 stamp=847422185 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/c-1374419528-20140513-01 recid=8 stamp=847422190 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_13/nnsnf0_tag20140513t032608_0.283.847423569 recid=9 stamp=847423569 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/autobackup/2014_05_13/s_847423572.284.847423575 recid=10 stamp=847423574 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_13/nnndf0_tag20140513t034819_0.286.847424903 recid=11 stamp=847424901 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_13/nnndf0_tag20140513t034819_0.285.847424901 recid=12 stamp=847424900 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=+DATA/orcl/autobackup/2014_05_13/s_847425002.287.847425003 recid=13 stamp=847425003 Crosschecked 13 objects RMAN> 報告需要做備份的檔案 RMAN> report need backup days 7;
Report of files whose recovery needs more than 7 days of archived logs File Days Name ---- ----- -----------------------------------------------------
RMAN>
刪除過期的備份 RMAN> delete expired backup; Do you really want to delete the above objects (enter YES or NO)? YES 可以直接刪除不用輸入YES RMAN> delete noprompt expired backup; 刪除無效的備份 RMAN> delete obsolete;
RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 10 days -----定義刪除的是10天前的 using channel ORA_DISK_1 using channel ORA_DISK_2 no obsolete backups found
RMAN>
|
RMAN恢復
資料檔案丟失
資料檔案的改名
|
RMAN> run { 2> allocate channel a1 type disk; 3> sql 'alter tablespace users offline'; 4> set newname for datafile '+DATA/orcl/datafile/users.259.846638599' to '/u01/app/oracle/oradata/users.dbf'; 5> restore tablespace users; 6> switch datafile '/u01/app/oracle/oradata/users.dbf'; 7> recover tablespace users; 8> sql 'alter tablespace users online'; 9> }
SQL> select name from v$datafile;
NAME -------------------------------------------------------------------------------- +DATA/orcl/datafile/system.256.846638591 +DATA/orcl/datafile/undotbs1.258.846638595 +DATA/orcl/datafile/sysaux.257.846638593 /u01/app/oracle/oradata/users.dbf +DATA/orcl/datafile/example.269.846638953 +DATA/orcl/datafile/u2.281.847420303
6 rows selected.
SQL> SQL> |
能離線的表空間
|
Users表空間中資料檔案丟失 RMAN> sql 'alter tablespace users offline'; -----可以強制離線 加引數 immediate
sql statement: alter tablespace users offline
RMAN> restore tablespace users;
Starting restore at 14-MAY-14 using channel ORA_DISK_1 using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00004 to +DATA/orcl/datafile/users.259.846638599 channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2014_05_14/nnndf0_tag20140514t020858_0.284.847505349 channel ORA_DISK_1: restored backup piece 1 piece handle=+DATA/orcl/backupset/2014_05_14/nnndf0_tag20140514t020858_0.284.847505349 tag=TAG20140514T020858 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 14-MAY-14
RMAN>recover tablespace users;
Starting recover at 14-MAY-14 using channel ORA_DISK_1 using channel ORA_DISK_2
starting media recovery media recovery complete, elapsed time: 00:00:00
Finished recover at 14-MAY-14
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online
RMAN> |
不能離線的表空間
|
Undo表空間丟失 RMAN> shutdown immediate
database closed database dismounted Oracle instance shut down
RMAN> startup mount
connected to target database (not started) Oracle instance started database mounted
Total System Global Area 599785472 bytes
Fixed Size 2022632 bytes Variable Size 201327384 bytes Database Buffers 394264576 bytes Redo Buffers 2170880 bytes
RMAN> SQL> select name,file#,status,enabled from v$datafile; -----檢視資料檔案的路徑或者直接找到編號
NAME -------------------------------------------------------------------------------- FILE# STATUS ENABLED ---------- ------- ---------- +DATA/orcl/datafile/system.256.846638591 1 SYSTEM READ WRITE
+DATA/orcl/datafile/undotbs1.258.846638595 2 ONLINE READ WRITE
+DATA/orcl/datafile/sysaux.257.846638593 3 ONLINE READ WRITE
NAME -------------------------------------------------------------------------------- FILE# STATUS ENABLED ---------- ------- ---------- +DATA/orcl/datafile/users.259.846638599 4 ONLINE READ WRITE
+DATA/orcl/datafile/example.269.846638953 5 ONLINE READ WRITE
+DATA/orcl/datafile/u2.281.847420303 6 ONLINE READ WRITE
6 rows selected.
SQL> RMAN> restore datafile 2; ---
Starting restore at 14-MAY-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=152 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=151 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00002 to +DATA/orcl/datafile/undotbs1.258.846638595 channel ORA_DISK_1: reading from backup piece /u02/0lp87ptq_1_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/0lp87ptq_1_1 tag=TAG20140514T020858 channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 Finished restore at 14-MAY-14
RMAN>recover datafile 2;
Starting recover at 14-MAY-14 using channel ORA_DISK_1 using channel ORA_DISK_2
starting media recovery media recovery complete, elapsed time: 00:00:01
Finished recover at 14-MAY-14
RMAN> alter database open;
database opened
RMAN> |
可以指定備份片的名字恢復
|
RMAN> list backup;
List of Backup Sets ===================
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 13 35.55M DISK 00:00:22 14-MAY-14 BP Key: 14 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020815 Piece Name: /u02/0ip87psh_1_1
List of Archived Logs in backup set 13 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 6 646250 07-MAY-14 694945 08-MAY-14 1 7 694945 08-MAY-14 695857 08-MAY-14 1 8 695857 08-MAY-14 717051 12-MAY-14 1 9 717051 12-MAY-14 764006 14-MAY-14
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 14 49.31M DISK 00:00:27 14-MAY-14 BP Key: 15 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020815 Piece Name: +DATA/orcl/backupset/2014_05_14/annnf0_tag20140514t020815_0.285.847505299
List of Archived Logs in backup set 14 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 3 556989 04-MAY-14 581849 04-MAY-14 1 4 581849 04-MAY-14 618249 06-MAY-14 1 5 618249 06-MAY-14 646250 07-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 15 Full 6.80M DISK 00:00:02 14-MAY-14 BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20140514T020844 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505324.286.847505327 Control File Included: Ckp SCN: 764026 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 16 Full 38.24M DISK 00:01:08 14-MAY-14 BP Key: 17 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020858 Piece Name: /u02/0lp87ptq_1_1 List of Datafiles in backup set 16 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 2 Full 764041 14-MAY-14 +DATA/orcl/datafile/undotbs1.258.846638595 3 Full 764041 14-MAY-14 +DATA/orcl/datafile/sysaux.257.846638593 5 Full 764041 14-MAY-14 +DATA/orcl/datafile/example.269.846638953
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 17 Full 81.47M DISK 00:01:24 14-MAY-14 BP Key: 18 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020858 Piece Name: +DATA/orcl/backupset/2014_05_14/nnndf0_tag20140514t020858_0.284.847505349 List of Datafiles in backup set 17 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 764040 14-MAY-14 +DATA/orcl/datafile/system.256.846638591 4 Full 764040 14-MAY-14 +DATA/orcl/datafile/users.259.846638599 6 Full 764040 14-MAY-14 +DATA/orcl/datafile/u2.281.847420303
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 18 Full 6.80M DISK 00:00:03 14-MAY-14 BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021025 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505425.283.847505427 Control File Included: Ckp SCN: 764073 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 19 Full 6.80M DISK 00:00:03 14-MAY-14 BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021310 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505590.282.847505593 Control File Included: Ckp SCN: 764152 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 20 Full 6.80M DISK 00:00:06 14-MAY-14 BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021522 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505722.280.847505727 Control File Included: Ckp SCN: 764244 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
RMAN> restore tablespace users from tag='TAG20140514T020858'; RMAN>recove tablespace users; |
基於時間的不完全恢復
----不完全恢復，也就是將資料庫恢復到過去的某一個時刻的狀態。
----很多情況下都必須進行不完全恢復，如：
-恢復使用者的誤操作，可以將資料庫恢復到誤操作前的狀態
當前聯機重做日誌檔案損壞或丟失時，必須進行不完全恢復
不完全恢復的方案：
1、基於使用者管理的不完全恢復
2、在RMAN中進行不完全恢復
3、可以是用閃回資料庫
4、可以進行日誌挖掘的不完全恢復
|
Export NLS_LANG=American
RMAN>run { Sql ‘alter session set NLS_TATE_FORMAT=”yyyy-mm-dd :hh24:mi:ss” ’; set until time = ‘2014-05-13 10:11:40’; restore databse; recover database; alter database open resetlogs; }
|
使用RMAN基於資料庫時間點的不完全恢復
|
Scott使用者下的emp表於2014-05-16 09:30:25 誤刪除 SQL> conn scott/tiger Connected. SQL> select * from tab;
TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE LOGT TABLE EMPSAL TABLE V1 VIEW A1 TABLE A2 TABLE RUPD$_EMP TABLE MLOG$_EMP TABLE
11 rows selected.
SQL> drop table emp;
克隆資料庫完成不完全恢復 建立引數檔案 [oracle@yang dbs]$ cd $ORACLE_HOME/dbs [oracle@yang dbs]$ vi initqwe.ora compatible='10.2.0.1.0' db_name='qwe' db_block_size=8192 control_files='/u01/app/oracle/oradata/qwe/control01.ctl' undo_management=auto undo_tablespace='UNDOTBS1' db_recovery_file_dest='/u01/app/oracle/flash_recovery_area/' db_recovery_file_dest_size=2g 建立必要的目錄 [oracle@yang dbs]$ mkdir /u01/app/oracle/oradata/qwe [oracle@yang dbs]$ mkdir /u01/app/oracle/admin/qwe/{a,b,c}dump –p 手動切換一下orcl例項日誌實現歸檔 SQL> alter system switch logfile; System altered. SQL> 啟動qwe例項的資料庫到nomount狀態 [oracle@yang dbs]$ export ORACLE_SID=qwe [oracle@yang dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 16 05:22:47 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount ORACLE instance started.
Total System Global Area 167772160 bytes Fixed Size 2019320 bytes Variable Size 113246216 bytes Database Buffers 50331648 bytes Redo Buffers 2174976 bytes SQL> 使用RMAN連線到orcl例項資料庫以qwe例項資料庫為輔助資料庫 [oracle@yang dbs]$ rman target sys/oracle@orcl10g auxiliary /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 16 05:27:47 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1374419528) connected to auxiliary database: QWE (not mounted)
RMAN> 執行克隆指令碼 [oracle@yang ~]$ pwd /home/oracle [oracle@yang ~]$ vi dup.rman run { set until time "to_date('2014-05-16 09:30:00','yyyy-mm-dd hh24:mi:ss')"; duplicate target database to qwe db_file_name_convert=(' +data/orcl/datafile/','/u01/app/oracle/oradata/qwe/') logfile '/u01/app/oracle/oradata/qwe/redo01.log' size 10m, '/u01/app/oracle/oradata/qwe/redo02.log' size 10m; } RMAN>@/home/oracle/dup.rman
RMAN-05517: temporary file +DATA/orcl/tempfile/temp.268.846638915 conflicts 解決方法：刪除原臨時表空間檔案在重建 SQL> alter database tempfile '+DATA/orcl/tempfile/temp.268.846638915' drop;
Database altered.
SQL> alter tablespace temp add tempfile '+DATA/orcl/tempfile/temp1.dbf' size 50m;
Tablespace altered.
SQL> alter database tempfile '+DATA/orcl/tempfile/temp1.dbf' online;
Database altered.
SQL> 繼續執行指令碼 RMAN>@/home/oracle/dup.rman ……. …….. contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script
database opened Finished Duplicate Db at 16-MAY-14
RMAN> RMAN> **end-of-file**
RMAN> 登入qwe例項資料庫檢視錶emp [oracle@yang dbs]$ export ORACLE_SID=qwe [oracle@yang dbs]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 16 06:34:32 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> select count(*) from emp;
COUNT(*) ---------- 16
SQL> 將需要的資料匯出 [oracle@yang dbs]$ export ORACLE_SID=qwe [oracle@yang dbs]$ exp scott/tiger tables=emp file=scott1.dmp 匯入到目標資料庫 [oracle@yang dbs]$ export ORACLE_SID=orcl [oracle@yang dbs]$ imp scott/tiger tables=emp file=scott1.dmp ignore=y 到orcl資料庫檢視資料是否存在 SQL> conn scott/tiger Connected. SQL> select count(*) from emp;
COUNT(*) ---------- 16
SQL> OK關閉qwe例項資料庫刪除沒有的檔案 [oracle@yang dbs]$ export ORACLE_SID=qwe [oracle@yang dbs]$ sqlplus /nolog SQL> conn / as sysdba SQL> shutdown abort [oracle@yang dbs]$ rm initqwe.ora -rf [oracle@yang dbs]$ rm /u01/app/oracle/admin/qwe/ -rf [oracle@yang dbs]$ rm /u01/app/oracle/oradata/qwe/ -rf [oracle@yang dbs]$ rm /u01/app/oracle/flash_recovery_area/QWE/ -rf [oracle@yang dbs]$ |
使用RMAN基於表空間時間點的不完全恢復
|
首先備份全庫及控制檔案 RMAN> backup full database plus archivelog; RMAN> backup current controlfile; 實驗刪除emp表 SQL> show user USER is "SCOTT" SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY ------------------- 2014-05-16 07:08:05
SQL> drop table emp purge;
Table dropped.
SQL> [oracle@yang ~]$ vi tspitr.rma
run { recover tablespace users until time "to_date('2014-05-16 07:08:00','yyyy-mm-dd h h24:mi:ss')" auxiliary destination '/tmp'; }
|
引數檔案、控制檔案與資料檔案都丟失
|
RMAN> startup nomount RMAN> restore spfile; RMAN> restore spfile from autobackup; 以上方法如果無效那麼 RMAN> list backup;
List of Backup Sets ===================
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 13 35.55M DISK 00:00:22 14-MAY-14 BP Key: 14 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020815 Piece Name: /u02/0ip87psh_1_1
List of Archived Logs in backup set 13 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 6 646250 07-MAY-14 694945 08-MAY-14 1 7 694945 08-MAY-14 695857 08-MAY-14 1 8 695857 08-MAY-14 717051 12-MAY-14 1 9 717051 12-MAY-14 764006 14-MAY-14
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 14 49.31M DISK 00:00:27 14-MAY-14 BP Key: 15 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020815 Piece Name: +DATA/orcl/backupset/2014_05_14/annnf0_tag20140514t020815_0.285.847505299
List of Archived Logs in backup set 14 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 3 556989 04-MAY-14 581849 04-MAY-14 1 4 581849 04-MAY-14 618249 06-MAY-14 1 5 618249 06-MAY-14 646250 07-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 15 Full 6.80M DISK 00:00:02 14-MAY-14 BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20140514T020844 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505324.286.847505327 Control File Included: Ckp SCN: 764026 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 16 Full 38.24M DISK 00:01:08 14-MAY-14 BP Key: 17 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020858 Piece Name: /u02/0lp87ptq_1_1 List of Datafiles in backup set 16 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 2 Full 764041 14-MAY-14 +DATA/orcl/datafile/undotbs1.258.846638595 3 Full 764041 14-MAY-14 +DATA/orcl/datafile/sysaux.257.846638593 5 Full 764041 14-MAY-14 +DATA/orcl/datafile/example.269.846638953
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 17 Full 81.47M DISK 00:01:24 14-MAY-14 BP Key: 18 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020858 Piece Name: +DATA/orcl/backupset/2014_05_14/nnndf0_tag20140514t020858_0.284.847505349 List of Datafiles in backup set 17 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 764040 14-MAY-14 +DATA/orcl/datafile/system.256.846638591 4 Full 764040 14-MAY-14 +DATA/orcl/datafile/users.259.846638599 6 Full 764040 14-MAY-14 +DATA/orcl/datafile/u2.281.847420303
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 18 Full 6.80M DISK 00:00:03 14-MAY-14 BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021025 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505425.283.847505427 Control File Included: Ckp SCN: 764073 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 19 Full 6.80M DISK 00:00:03 14-MAY-14 BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021310 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505590.282.847505593 Control File Included: Ckp SCN: 764152 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 20 Full 6.80M DISK 00:00:06 14-MAY-14 BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021522 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505722.280.847505727 Control File Included: Ckp SCN: 764244 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 21 Full 6.80M DISK 00:00:04 14-MAY-14 BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20140514T071650 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847523810.278.847523811 Control File Included: Ckp SCN: 772202 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 22 Full 6.80M DISK 00:00:04 14-MAY-14 BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20140514T071705 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847523825.277.847523827 Control File Included: Ckp SCN: 772255 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 23 Full 6.80M DISK 00:00:04 14-MAY-14 BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20140514T071920 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847523960.276.847523963 Control File Included: Ckp SCN: 772330 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 24 Full 6.80M DISK 00:00:04 14-MAY-14 BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20140514T071927 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847523967.288.847523969 Control File Included: Ckp SCN: 772379 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
RMAN> restore spfile from ‘+DATA/orcl/autobackup/2014_05_14/s_847523967.288.847523969’; RMAN>startup force nomount RMAN>restore controlfile from ‘+DATA/orcl/autobackup/2014_05_14/s_847523967.288.847523969’; RMAN>alter database mount RMAN>restore database; RMAN>recover database; RMAN>alter database open resetlogs; |
在RMAN中以spfile得到pfile
|
RMAN> restore spfile to pfile '/u02/orcl.ora';
Starting restore at 16-MAY-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=158 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=138 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: restoring SPFILE to PFILE output filename=/u02/orcl.ora channel ORA_DISK_1: reading from backup piece +DATA/orcl/autobackup/2014_05_14/s_847523967.288.847523969 channel ORA_DISK_1: restored backup piece 1 piece handle=+DATA/orcl/autobackup/2014_05_14/s_847523967.288.847523969 tag=TAG20140514T071927 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 16-MAY-14
RMAN> [oracle@yang u02]$ ll orcl.ora -rw-r--r-- 1 oracle oinstall 1191 May 16 01:51 orcl.ora [oracle@yang u02]$ cat orcl.ora orcl.__db_cache_size=390070272 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__shared_pool_size=197132288 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='NONE' *.background_dump_dest='/u01/app/oracle/admin/orcl/bdump' *.compatible='10.2.0.1.0' *.control_files='+DATA/orcl/controlfile/current.261.846638793','+DATA/orcl/controlfile/current.260.846638797' *.core_dump_dest='/u01/app/oracle/admin/orcl/cdump' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_flashback_retention_target=5400 *.db_name='orcl' *.db_recovery_file_dest_size=2147483648 *.db_recovery_file_dest='+DATA' *.dispatchers='(protocol=tcp)(service=ora10gs)(dispatchers=2)' *.job_queue_processes=10 *.log_archive_dest_1='LOCATION=+DATA/orcl/' *.log_archive_format='%t_%s_%r.dbf' *.open_cursors=300 *.pga_aggregate_target=199229440 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' orcl.resource_manager_plan='UPLAN' *.resource_manager_plan='UPLAN' *.sga_target=598736896 *.shared_servers=2 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/orcl/udump' [oracle@yang u02]$ |
CATLOG方式
|
Oracle 10G資料庫 使用rman 備份（裸裝置管理）1檢查資料庫模式：oracle@yangzai ~]$ uniread sqlplus / as sysdba [uniread] Loaded history (149 lines)
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 13 06:25:23 2014
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>startup 。。。 SQL> archive log list （檢視是否在歸檔模式下） Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 9 Next log sequence to archive 10 Current log sequence 10 SQL>
若為非歸檔則修改資料庫歸檔模式
SQL> startup mount ORA-01081: cannot start already-running ORACLE - shut it down first 需要關閉資料庫 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 啟動到mount狀態 SQL> startup mount ORACLE instance started.
Total System Global Area 608174080 bytes Fixed Size 2098240 bytes Variable Size 155192256 bytes Database Buffers 444596224 bytes Redo Buffers 6287360 bytes Database mounted. SQL> 更改資料庫為歸檔模式 SQL> alter database archivelog; Database altered. SQL> 開啟資料庫 SQL> alter database open; Database altered. SQL> 2連線到target資料庫[oracle@yangzai bdump]$ rman target 'ORCL' catalog rman/rman
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 13 07:10:34 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password: connected to target database: ORCL (DBID=1354749965) connected to recovery catalog database
RMAN>
1.首先檢視一下Oracle的資料檔案SQL> select name from v$datafile；
NAME -------------------------------------------------------------------------------- /dev/raw/raw14 /dev/raw/raw16 /dev/raw/raw13 /dev/raw/raw17 /dev/raw/raw18 /dev/raw/raw19 /dev/raw/raw20 /dev/raw/raw21
8 rows selected.
SQL> 2.建立RMAN所需的表空間新增裸裝置/raw/raw22檢視vg狀態 [root@yangzai ~]# vgdisplay --- Volume group --- VG Name vg_oracle System ID Format lvm2 Metadata Areas 5 Metadata Sequence No 70 VG Access read/write VG Status resizable MAX LV 0 Cur LV 21 Open LV 0 Max PV 0 Cur PV 5 Act PV 5 VG Size 20.46 GB PE Size 4.00 MB Total PE 5239 Alloc PE / Size 3102 / 12.12 GB Free PE / Size 2137 / 8.35 GB VG UUID c7sMGI-Ow5z-OO0F-y47X-7BcE-mqtB-goAsUN
建立lv [root@yangzai ~]# lvcreate -n rmanup -L 600M vg_oracle Logical volume "rmanup" created 指定raw22 [root@yangzai ~]# vi /etc/sysconfig/rawdevices /dev/raw/raw17 /dev/vg_oracle/users /dev/raw/raw18 /dev/vg_oracle/user2 /dev/raw/raw19 /dev/vg_oracle/qqqqq /dev/raw/raw20 /dev/vg_oracle/tianjia1 /dev/raw/raw21 /dev/vg_oracle/qqqtmp /dev/raw/raw22 /dev/vg_oracle/rmanup 重啟裸裝置 [root@yangzai ~]# /sbin/service rawdevices restart Assigning devices: /dev/raw/raw1 --> /dev/vg_oracle/control1 /dev/raw/raw1: bound to major 253, minor 0 /dev/raw/raw2 --> /dev/vg_oracle/control2 /dev/raw/raw2: bound to major 253, minor 11 /dev/raw/raw3 --> /dev/vg_oracle/control3 /dev/raw/raw3: bound to major 253, minor 12 /dev/raw/raw4 --> /dev/vg_oracle/example /dev/raw/raw4: bound to major 253, minor 5 /dev/raw/raw5 --> /dev/vg_oracle/passwordfile /dev/raw/raw5: bound to major 253, minor 14 /dev/raw/raw6 --> /dev/vg_oracle/redo1_1 /dev/raw/raw6: bound to major 253, minor 15 /dev/raw/raw7 --> /dev/vg_oracle/redo1_2 /dev/raw/raw7: bound to major 253, minor 16 /dev/raw/raw8 --> /dev/vg_oracle/redo2a /dev/raw/raw8: bound to major 253, minor 7 /dev/raw/raw9 --> /dev/vg_oracle/redo2b /dev/raw/raw9: bound to major 253, minor 8 /dev/raw/raw10 --> /dev/vg_oracle/redo3a /dev/raw/raw10: bound to major 253, minor 9 /dev/raw/raw11 --> /dev/vg_oracle/redo3b /dev/raw/raw11: bound to major 253, minor 10 /dev/raw/raw12 --> /dev/vg_oracle/lv_spf_spfile /dev/raw/raw12: bound to major 253, minor 13 /dev/raw/raw13 --> /dev/vg_oracle/sysaux /dev/raw/raw13: bound to major 253, minor 2 /dev/raw/raw14 --> /dev/vg_oracle/system /dev/raw/raw14: bound to major 253, minor 1 /dev/raw/raw15 --> /dev/vg_oracle/temp /dev/raw/raw15: bound to major 253, minor 4 /dev/raw/raw16 --> /dev/vg_oracle/undotbs1 /dev/raw/raw16: bound to major 253, minor 3 /dev/raw/raw17 --> /dev/vg_oracle/users /dev/raw/raw17: bound to major 253, minor 6 /dev/raw/raw18 --> /dev/vg_oracle/user2 /dev/raw/raw18: bound to major 253, minor 17 /dev/raw/raw19 --> /dev/vg_oracle/qqqqq /dev/raw/raw19: bound to major 253, minor 18 /dev/raw/raw20 --> /dev/vg_oracle/tianjia1 /dev/raw/raw20: bound to major 253, minor 19 /dev/raw/raw21 --> /dev/vg_oracle/qqqtmp /dev/raw/raw21: bound to major 253, minor 20 /dev/raw/raw22 --> /dev/vg_oracle/rmanup /dev/raw/raw22: bound to major 253, minor 21 Done 永久載入裸裝置 [root@yangzai ~]# chown oracle:dba /dev/raw/raw* [root@yangzai ~]# chmod 660 /dev/raw/raw* [root@yangzai ~]# vi /etc/rc.local
#!/bin/sh # # This script will be executed *after* all the other init scripts. # You can put your own initialization stuff in here if you don't # want to do the full Sys V style init stuff.
touch /var/lock/subsys/local chown oracle:dba /dev/raw/raw* chmod 660 /dev/raw/raw*
檢視裸裝置/RAW/RAW22大小 [root@yangzai ~]# blockdev --getsize /dev/raw/raw22 1228800 登入sql建立表空間建立資料檔案為裸裝置/raw/raw22名為ora_backup 表空間 SQL> create tablespace ora_backup datafile'/dev/raw/raw22' size 500M;
Tablespace created.
SQL>
進行檢視確認一下 SQL> select name from v$datafile;
NAME -------------------------------------------------------------------------------- /dev/raw/raw14 /dev/raw/raw16 /dev/raw/raw13 /dev/raw/raw17 /dev/raw/raw18 /dev/raw/raw19 /dev/raw/raw20 /dev/raw/raw21 /dev/raw/raw22
9 rows selected.
SQL> 3建立RMAN使用者以及設定密碼和使用者表空間SQL> create user rman identified by rman default tablespace ora_backup temporary tablespace temp; 可以加上quota unlimited on ora_backup 命令的意思是rman使用者可以使用該表空間的全部空間）
User created.
SQL> 4.給使用者授權SQL> grant connect,resource,recovery_catalog_owner to rman;
Grant succeeded.
SQL>
看看都給了那些許可權 SQL> select * from dba_sys_privs where grantee='CONNECT';
GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CONNECT CREATE SESSION NO
SQL> select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- RESOURCE CREATE TRIGGER NO RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TYPE NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE INDEXTYPE NO RESOURCE CREATE TABLE NO
8 rows selected.
SQL> select * from dba_sys_privs where grantee='RECOVERY_CATALOG_OWNER';
GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- RECOVERY_CATALOG_OWNER CREATE SYNONYM NO RECOVERY_CATALOG_OWNER CREATE CLUSTER NO RECOVERY_CATALOG_OWNER ALTER SESSION NO RECOVERY_CATALOG_OWNER CREATE DATABASE LINK NO RECOVERY_CATALOG_OWNER CREATE PROCEDURE NO RECOVERY_CATALOG_OWNER CREATE SEQUENCE NO RECOVERY_CATALOG_OWNER CREATE TABLE NO RECOVERY_CATALOG_OWNER CREATE SESSION NO RECOVERY_CATALOG_OWNER CREATE TYPE NO RECOVERY_CATALOG_OWNER CREATE VIEW NO RECOVERY_CATALOG_OWNER CREATE TRIGGER NO
11 rows selected.
SQL>
5退出資料庫重新.連線到資料庫退出資料庫 SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options exit [uniread] Saved history (149 lines) 重連 [oracle@yangzai ~]$ rman target 'orcl' catalog rman/rman
Argument Value Description ----------------------------------------------------------------------------- target quoted-string connect-string for target database catalog quoted-string connect-string for recovery catalog nocatalog none if specified, then no recovery catalog cmdfile quoted-string name of input command file log quoted-string name of output message log file trace quoted-string name of output debugging message log file append none if specified, log is opened in append mode debug optional-args activate debugging msgno none show RMAN-nnnn prefix for all messages send quoted-string send a command to the media manager pipe string building block for pipe names timeout integer number of seconds to wait for pipe input checksyntax none check the command file for syntax errors ----------------------------------------------------------------------------- Both single and double quotes (' or ") are accepted for a quoted-string. Quotes are not required unless the string contains embedded white-space.
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00552: syntax error in command line arguments RMAN-01009: syntax error: found "identifier": expecting one of: "append, at, auxiliary, catalog, cmdfile, clone, checksyntax, debug, log, msglog, mask, msgno, nocatalog, pipe, rcvcat, script, slaxdebug, send, target, timeout, trace" RMAN-01008: the bad identifier was: targetzhiruicatalog RMAN-01007: at line 2 column 1 file: command line arguments
看看rman是否可用 [oracle@yangzai ~]$ rman
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 13 03:08:33 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved. 是可用 退出在連線 RMAN> exit
Recovery Manager complete. 資料庫名用大寫試試 [oracle@yangzai ~]$ rman target 'ORCL' catalog rman/rman
[oracle@yangzai ~]$ rman catalog rman/rman （2個命令一樣）
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 20 02:15:43 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to recovery catalog database
RMAN>
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 13 03:10:15 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password: connected to target database: ORCL (DBID=1354749965) connected to recovery catalog database
RMAN> 連線成功 6.註冊資料庫直接註冊 RMAN> register database;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of register command at 01/13/2014 03:15:52 RMAN-06428: recovery catalog is not installed 報錯：恢復目錄沒有安裝，需要自己建立 RMAN> 7.建立catalog表空間到ora_backupRMAN> create catalog tablespace ora_backup
recovery catalog created
RMAN> 在註冊試試 RMAN> register database;
database registered in recovery catalog starting full resync of recovery catalog full resync complete 註冊成功 RMAN>
通過dbca資料庫連線到orcl資料庫中catalog目錄資料庫配置資料庫DBCA例項中的tnsnames.ora檔案新增ORCL例項資料庫中的網段。使DBCA例項資料庫能連線到ORCL資料庫
[oracle@dbca admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools.
ORALOCAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.188)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = dbca) ) )
ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.187)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = orcl) ) ) 重新啟動監聽 連線測試[oracle@dbca admin]$ rman target sys/oracle@ORALOCAL catalog rman/rman@ORCL RMAN 連線 連線符為@ORALOCAL 的RMAN 在連線目錄資料庫為@ORCL連線符的RMAN
這時候使用備份，會將備份集存放在catlog rman使用者下的表空間中。 |
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29532781/viewspace-1174656/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle RMAN 備份恢復總結Oracle
- oracle RMAN 備份恢復總結Oracle
- oracle rman備份恢復的例子Oracle
- Oracle9i RMAN備份及恢復步驟Oracle
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- ORACLE DG從庫 Rman備份恢復Oracle
- Oracle中使用RMAN備份及一些恢復方法Oracle
- Oracle9i RMAN備份及恢復步驟(zt)Oracle
- RMAN備份恢復原理
- rman備份恢復-rman入門
- Oracle9i RMAN 的優缺點及RMAN 備份及恢復步驟Oracle
- Oracle 備份恢復篇之RMAN catalogOracle
- Oracle塊損壞恢復（有rman備份）Oracle
- [記錄]oracle RMAN 備份恢復總結Oracle
- RMAN備份與恢復之加密備份加密
- Oracle的RMAN備份恢復繼續，RMAN部分引數Oracle
- Oracle 10G RAC的ocr備份恢復Oracle 10g
- Oracle DG從庫 Rman備份恢復測試Oracle
- 【RMAN】Oracle11g備份恢復新特性Oracle
- Oracle資料庫備份與恢復之RMANOracle資料庫
- oracle實驗記錄 (恢復-rman增量備份)Oracle
- oracle rman備份驗證和備份/恢復進度監控Oracle
- rman備份恢復-rman恢復資料檔案測試
- RMAN備份恢復典型案例——RMAN備份&系統變慢
- Oracle 10g備份與恢復高階使用者指南--第二章 RMAN備份概念Oracle 10g
- RMAN備份恢復效能優化優化
- rman備份恢復命令之switch
- RMAN備份恢復整個庫
- Linux下Oracle9i RMAN備份及恢復步驟(轉)LinuxOracle
- oracle 10.2.0.1 win 32 rman 備份異機恢復Oracle
- RMAN備份恢復——備份到帶庫的效能
- 循序漸進oracle第7章：備份與恢復之RMAN的簡單備份與恢復Oracle
- 【RMAN】Oracle12c以後rman 備份恢復命令參考Oracle
- RMAN備份及恢復歸檔日誌的語法
- Oracle 11G 備份與恢復 使用RMAN建立備份集舉例Oracle
- Oracle 10g備份與恢復高階使用者指南--第三章 RMAN恢復（Recovery）概念Oracle 10g
- Oracle 備份恢復概念Oracle
- oracle備份恢復PPTOracle