152
利用 RMAN 進行異機全恢複
服務器資料信息
|
servername |
role |
sid |
db_unique_name |
db_name |
lsn name |
ip addr |
|
node222 |
primary |
TBDB |
node222 |
node222 |
node222 |
192.168.17.222 |
|
node173 |
standby |
TBDB |
node173 |
node222 |
node173 |
192.168.17.173 |
node222, node173 數據文件，日誌文件，歸檔日誌存放位置一致
為實現物理standby
|
物理standby與primary數據庫完全一模一樣 同一鏡像數據庫，則 sid, db_name 必須一致 為標示不同數據庫， db_unique_name 必須不一樣 為連接不同數據庫，連接器命令也必須不一樣 |
目標
|
在 primary 中導入測試數據，創建 rman 進行全備，複製備份片至standby 然後導入 rman |
創建數據表空間 (node173,node222)
|
create tablespace RECHARGE datafile '/u01/app/oracle/oradata/node222/recharge.dbf' size 500M extent management local; |
創建應用連接的用戶(node173,node222)
|
SQL> grant connect, resource to RECHARGE identified by "RECHARGE"; SQL> alter user RECHARGE default tablespace RECHARGE; |
exp數據導入
注意, 數據導入過程中需要操作係統語言, 數據庫語言, 客戶端語言同步
查詢並修改數據庫語言環境
查詢方法
|
SQL> select userenv('language') from dual;
USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.AL32UTF8 |
修改方法
|
SQL> SHUTDOWN immediate; SQL> STARTUP RESTRICT SQL> alter database character set internal_use AL32UTF8 ; SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION; SQL> shutdown immediate; SQL> startup |
確保 oracle 語言環境一致後
修改係統語言環境並導入數據 (node222)
|
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 imp userid=system/oracle touser=RECHARGE file=day_new.dmp |
創建RMAN(node173,node222)
|
SQL> create tablespace rman logging datafile '/u01/app/oracle/oradata/node222/rman.dbf' size 1024M extent management local segment space management auto; |
創建 RMAN 授權(node173,node222)
|
create user rman identified by rman default tablespace rman; grant connect,resource to rman; grant recovery_catalog_owner to rman; grant unlimited tablespace to rman; grant sysdba to rman; |
建立catalog數據庫及注冊數據庫(node173,node222)
|
rman catalog rman/rman RMAN> create catalog tablespace "RMAN"; RMAN> exit |
連接 catalog(node173,node222)
|
rman target sys/oracle@node222 catalog rman/rman@node222 |
注: 需啟動連接器，node222為客戶端連接服務器的標識符
返回下麵信息為正常連接到 RMAN
|
connected to target database: NODE222 (DBID=3694045568) connected to recovery catalog database |
注冊 catalog(node173,node222)
|
RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete |
創建備份目錄
|
mkdir /u01/app/oracle/backup cd /u01/app/oracle/backup mkdir controlfileback dbback log logback controlfileback 存放備份的控製文件 dbback 存放備份的數據文件 log 存放備份生成的日誌，堅持查詢該日誌查看備份是否成功 logback 存放歸檔日誌文件 |
備份數據(node222)
|
rman target sys/oracle@node222 catalog rman/rman@node222 |
注: @node222 是 listener 監聽器連接（配置略）
顯示一下全局配置信息
|
show all; |
修改自動備份 control 功能
默認狀態
|
CONFIGURE BACKUP OPTIMIZATION OFF; # 默認狀態 CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db1/dbs/snapcf_TBDB.f'; # 默認保存位置 |
修改
|
CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/product/11.2.0/db1/dbs/cf%F'; |
修改備份數據最長時間
|
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS; |
如果需要刪除過時備份
|
DELETE OBSOLETE |
全庫備份
|
RMAN> sql 'alter system archive log current'; RMAN> backup database format '/u01/app/oracle/backup/dbback/TBDB_%d_%T_%U'; |
備份過程信息如下
|
Starting backup at 28-JUN-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=198 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u01/app/oracle/oradata/node222/rman.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/node222/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/node222/sysaux01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/node222/recharge.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/node222/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/node222/example01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/node222/users01.dbf channel ORA_DISK_1: starting piece 1 at 28-JUN-13 channel ORA_DISK_1: finished piece 1 at 28-JUN-13 piece handle=/u01/app/oracle/backup/dbback/TBDB_NODE222_20130628_01odb0sc_1_1 tag=TAG20130628T150651 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 Finished backup at 28-JUN-13
Starting Control File and SPFILE Autobackup at 28-JUN-13 piece handle=/u01/app/oracle/product/11.2.0/db1/dbs/cfc-3695602788-20130628-00 comment=NONE Finished Control File and SPFILE Autobackup at 28-JUN-13 |
歸檔備份方法
|
sql 'alter system archive log current'; backup archivelog all format '/u01/app/oracle/backup/logback/arc_%t_%s' delete all input; |
控製文件手動備份方法
|
backup current controlfile format '/u01/app/oracle/backup/controlfileback/cntrl_%s_%p_%s'; |
查詢備份信息
|
RMAN> list backup; |
備份信息顯示
|
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 159 Full 1.56G DISK 00:01:02 27-JUN-13 BP Key: 162 Status: AVAILABLE Compressed: NO Tag: TAG20130627T121557 Piece Name: /u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_01od82ft_1_1 List of Datafiles in backup set 159 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/system01.dbf 2 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/sysaux01.dbf 3 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/undotbs01.dbf 4 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/users01.dbf 5 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/example01.dbf 6 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/recharge.dbf 7 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/rman.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 160 Full 9.36M DISK 00:00:02 27-JUN-13 BP Key: 163 Status: AVAILABLE Compressed: NO Tag: TAG20130627T121557 Piece Name: /u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_02od82hv_1_1 SPFILE Included: Modification time: 27-JUN-13 SPFILE db_unique_name: NODE222 Control File Included: Ckp SCN: 3122444 Ckp time: 27-JUN-13
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 191 746.56M DISK 00:00:26 27-JUN-13 BP Key: 194 Status: AVAILABLE Compressed: NO Tag: TAG20130627T121920 Piece Name: /u01/app/oracle/backup/logback/arc_819202760_3
List of Archived Logs in backup set 191 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 85 2901879 25-JUN-13 2936275 25-JUN-13 1 86 2936275 25-JUN-13 2952511 25-JUN-13 1 87 2952511 25-JUN-13 2974058 26-JUN-13 1 88 2974058 26-JUN-13 3000002 26-JUN-13 1 89 3000002 26-JUN-13 3018605 26-JUN-13 1 90 3018605 26-JUN-13 3046423 26-JUN-13 1 91 3046423 26-JUN-13 3056627 27-JUN-13 1 92 3056627 27-JUN-13 3080492 27-JUN-13 1 93 3080492 27-JUN-13 3094323 27-JUN-13 1 94 3094323 27-JUN-13 3098061 27-JUN-13 1 95 3098061 27-JUN-13 3098704 27-JUN-13 1 96 3098704 27-JUN-13 3099248 27-JUN-13 1 97 3099248 27-JUN-13 3099871 27-JUN-13 1 98 3099871 27-JUN-13 3100505 27-JUN-13 1 99 3100505 27-JUN-13 3101436 27-JUN-13 1 100 3101436 27-JUN-13 3101973 27-JUN-13 1 101 3101973 27-JUN-13 3107360 27-JUN-13 1 102 3107360 27-JUN-13 3120392 27-JUN-13 1 103 3120392 27-JUN-13 3122322 27-JUN-13 1 104 3122322 27-JUN-13 3122845 27-JUN-13 1 105 3122845 27-JUN-13 3122892 27-JUN-13
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 221 Full 9.33M DISK 00:00:02 27-JUN-13 BP Key: 243 Status: AVAILABLE Compressed: NO Tag: TAG20130627T121956 Piece Name: /u01/app/oracle/backup/controlfileback/cntrl_4_1_4 Control File Included: Ckp SCN: 3123051 Ckp time: 27-JUN-1 |
把 node222 RMAN 備份數據複製至 node173 然後做全庫恢複
按照默認位置，把文件存放 /u01/app/oracle/backup/dbback 對應目錄下
|
logback/arc_819202760_3 (archive log backup) controlfileback/cntrl_4_1_4 (control file backup) dbback/TBDB_NODE222_20130627_01od82ft_1_1 (datafile backup) dbback/TBDB_NODE222_20130627_02od82hv_1_1 (datafile backup) |
執行 node173 恢複(下麵所有操作在 node173 執行)
恢複控製文件，必須以 nomount 啟動
|
shutdown immediate; startup nomount; |
注: node222, node173當前 db_name,sid, 數據存放位置一致，否則需要重寫 pfile(略)
恢複控製文件過程
|
rman target /
RMAN> restore controlfile from '/u01/app/oracle/backup/controlfileback/cntrl_4_1_4'; |
恢複過程
|
channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/node222/control01.ctl output file name=/u01/app/oracle/flash_recovery_area/node222/control02.ctl Finished restore at 28-JUN-13 |
恢複數據，切換數據庫至 mount 狀態
|
SQL> alter database mount; |
直接恢複數據庫則可
|
RMAN> restore database; |
恢複過程
|
Starting restore at 28-JUN-13 Starting implicit crosscheck backup at 28-JUN-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device type=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 28-JUN-13
Starting implicit crosscheck copy at 28-JUN-13 using channel ORA_DISK_1 Finished implicit crosscheck copy at 28-JUN-13
searching for all files in the recovery area cataloging files... no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/node222/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/node222/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/node222/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/node222/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/node222/example01.dbf channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/node222/recharge.dbf channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/node222/rman.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_01od82ft_1_1 channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_01od82ft_1_1 tag=TAG20130627T121557 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:55 Finished restore at 28-JUN-13 |
修複數據庫 (主要利用 archive log 進行修複)
|
RMAN> restore database; |
修複過程
|
Starting restore at 28-JUN-13 using channel ORA_DISK_1
skipping datafile 1; already restored to file /u01/app/oracle/oradata/node222/system01.dbf skipping datafile 2; already restored to file /u01/app/oracle/oradata/node222/sysaux01.dbf skipping datafile 3; already restored to file /u01/app/oracle/oradata/node222/undotbs01.dbf skipping datafile 4; already restored to file /u01/app/oracle/oradata/node222/users01.dbf skipping datafile 5; already restored to file /u01/app/oracle/oradata/node222/example01.dbf skipping datafile 6; already restored to file /u01/app/oracle/oradata/node222/recharge.dbf skipping datafile 7; already restored to file /u01/app/oracle/oradata/node222/rman.dbf restore not done; all files read only, offline, or already restored Finished restore at 28-JUN-13 |
修複完成後啟動數據庫
|
SQL> alter database open resetlogs;
ERROR at line 1: ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/u01/app/oracle/oradata/node222/system01.dbf' |
檢測係統 SCN
|
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE# ------------------ 3122322 |
檢測數據文件 SCN
|
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE# ------------------ 3122357 3122357 3122357 3122357 3122357 3122357 3122357
7 rows selected.
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE# ------------------ 3122357 3122357 3122357 3122357 3122357 3122357 3122357
7 rows selected. |
結論: 係統 SCN < 數據文件 SCN 導致報錯
解決方法, 重新恢複一下控製文件
|
SQL> recover database using backup controlfile until cancel; ORA-00279: change 3122357 generated at 06/27/2013 12:15:57 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/NODE173/archivelog/2013_06_28/o1_mf_1_104_%u _.arc ORA-00280: change 3122357 for thread 1 is in sequence #104
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. |
重新啟動數據庫
|
SQL> alter database open resetlogs;
Database altered. |
檢測 node173, node222 數據，同步成功, 建議對服務器進行全備.
最後更新：2017-04-03 16:59:46
上一篇：
eclipse package,source folder,folder區別及相互轉換
下一篇： iOS訪問通訊錄開發-讀取聯係人信息
- Android開發小白日記1 25th Mar
- JEESZ分布式框架--單點登錄集成方案
- 《正則表達式經典實例（第2版）》——2.15 避免失控重複
- 走在專家的路上，每天一條SQL優化（3）
- 『0013』 - Solidity Types - 固定大小字節數組(Fixed-size byte arrays)
- 阿裏雲服務器CentOS7啟動tomcat7巨慢
- Android2.3 編譯出錯：No rule to make InterpAsm-x86.S
- Airbnb技術大牛桑立鋒解析：數據加密都有哪些套路？
- UVA之10361 - Automatic Poetry
- 使用SOCKET實現TCP/IP協議的通訊