Oracle11新特性——備份恢復功能增強(九)
打算寫一系列的文章介紹11g的新特性和變化。
Oracle11g在備份和恢復方面新增了很多的功能,無論是效能、功能性、安全性和可操作性方面都有了不同程度的提高。
這一篇介紹11g的新特性Data Recovery Advisor。
Oracle11新特性——備份恢復功能增強(一):http://yangtingkun.itpub.net/post/468/412991
Oracle11新特性——備份恢復功能增強(二):http://yangtingkun.itpub.net/post/468/414647
Oracle11新特性——備份恢復功能增強(三):http://yangtingkun.itpub.net/post/468/414834
Oracle11新特性——備份恢復功能增強(四):http://yangtingkun.itpub.net/post/468/414941
Oracle11新特性——備份恢復功能增強(五):http://yangtingkun.itpub.net/post/468/416015
Oracle11新特性——備份恢復功能增強(六):http://yangtingkun.itpub.net/post/468/423531
Oracle11新特性——備份恢復功能增強(七):http://yangtingkun.itpub.net/post/468/426943
Oracle11新特性——備份恢復功能增強(八):http://yangtingkun.itpub.net/post/468/427877
11g新增特性Data Recovery Advisor,這個內嵌工具可以自動檢測物理資料錯誤,可以給出建議修改方式,執行修復操作。
看一個簡單的例子,首先對測試表空間進行備份:
[oracle@yangtk ~]$ rman target /
Recovery Manager: Release 11.1.0.6.0 - Production on Fri Nov 9 00:35:19 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORA11G (DBID=4026820313)
RMAN> list failure;
using target database control file instead of recovery catalog
no failures found that match specification
RMAN> backup tablespace users;
Starting backup at 09-NOV-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=118 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=00004 name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf
channel ORA_DISK_1: starting piece 1 at 09-NOV-07
channel ORA_DISK_1: finished piece 1 at 09-NOV-07
piece handle=/data1/backup/1ij0irqj_1_1 tag=TAG20071109T003531 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 09-NOV-07
Starting Control File and SPFILE Autobackup at 09-NOV-07
piece handle=/data1/backup/c-4026820313-20071109-00 comment=NONE
Finished Control File and SPFILE Autobackup at 09-NOV-07
RMAN> exit
Recovery Manager complete.
下面建立測試用表:
[oracle@yangtk ~]$ sqlplus yangtk/yangtk
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Nov 9 00:36:27 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t_failure tablespace users as select * from dba_objects;
Table created.
SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid)
2 from t_failure where rownum = 1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 12
SQL> select count(*) from t_failure;
COUNT(*)
----------
68918
下面透過文字編輯工具如UltraEdit或vi,對資料檔案中表的內容直接進行修改。
修改之後,透過alter tablespace offline的方式,使得Oracle檢查到錯誤:
SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
Process ID: 3144
Session ID: 118 Serial number: 241
SQL> select * from dba_tablespaces where tablespace_name = 'USERS';
ERROR:
ORA-03114: not connected to ORACLE
SQL> conn / as sysdba
Connected.
SQL> select status from dba_tablespaces where tablespace_name = 'USERS';
STATUS
---------
ONLINE
Oracle直接中止了程式,這說明Oracle已經檢查到了這個錯誤:
[oracle@yangtk datafile]$ rman target /
Recovery Manager: Release 11.1.0.6.0 - Production on Fri Nov 9 00:49:08 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORA11G (DBID=4026820313)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7582 HIGH OPEN 09-NOV-07 One or more non-system datafiles are corrupt
果然,Oracle已經檢查到了錯誤,使用detail可以看到更詳細的資訊:
RMAN> list failure detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7582 HIGH OPEN 09-NOV-07 One or more non-system datafiles are corrupt
Impact: See impact for individual child failures
List of child failures for parent failure ID 7582
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7585 HIGH OPEN 09-NOV-07 Datafile 4: '/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf' is corrupt
Impact: Some objects in tablespace USERS might be unavailable
下面可以使用advise failure獲取恢復建議:
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7582 HIGH OPEN 09-NOV-07 One or more non-system datafiles are corrupt
Impact: See impact for individual child failures
List of child failures for parent failure ID 7582
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7585 HIGH OPEN 09-NOV-07 Datafile 4: '/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf' is corrupt
Impact: Some objects in tablespace USERS might be unavailable
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=116 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /data/oracle/diag/rdbms/ora11g_p/ora11g/hm/reco_1941598854.hm
下面可以透過repair failure來自動修復錯誤,如果對Oracle的repair failure不放心,還可以透過preview來檢查具體的恢復步驟:
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /data/oracle/diag/rdbms/ora11g_p/ora11g/hm/reco_1941598854.hm
contents of repair script:
# restore and recover datafile
sql 'alter database datafile 4 offline';
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';
現在可以執行恢復了:
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /data/oracle/diag/rdbms/ora11g_p/ora11g/hm/reco_1941598854.hm
contents of repair script:
# restore and recover datafile
sql 'alter database datafile 4 offline';
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
sql statement: alter database datafile 4 offline
Starting restore at 09-NOV-07
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 00004 to /data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf
channel ORA_DISK_1: reading from backup piece /data1/backup/1ij0irqj_1_1
channel ORA_DISK_1: piece handle=/data1/backup/1ij0irqj_1_1 tag=TAG20071109T003531
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-NOV-07
Starting recover at 09-NOV-07
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 09-NOV-07
sql statement: alter database datafile 4 online
repair failure complete
恢復完成,最後再檢查一下failure的狀態:
RMAN> list failure;
no failures found that match specification
RMAN> list failure closed;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7585 HIGH CLOSED 09-NOV-07 Datafile 4: '/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf' is corrupt
Impact: Some objects in tablespace USERS might be unavailable
7582 HIGH CLOSED 09-NOV-07 One or more non-system datafiles are corrupt
這個新功能使得系統恢復變得更加容易。
這裡介紹的是Data Recovery Advisor的命令列介面,這個功能還提供一個圖形化的介面,整合在OEM中,這裡就不描述了。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-69534/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle11新特性——備份恢復功能增強Oracle
- Oracle11新特性——備份恢復功能增強(六)Oracle
- Oracle11新特性——備份恢復功能增強(五)Oracle
- Oracle11新特性——備份恢復功能增強(四)Oracle
- Oracle11新特性——備份恢復功能增強(三)Oracle
- Oracle11新特性——備份恢復功能增強(二)Oracle
- Oracle11新特性——備份恢復功能增強(一)Oracle
- Oracle11新特性——備份恢復功能增強(十)Oracle
- Oracle11新特性——備份恢復功能增強(八)Oracle
- Oracle11新特性——備份恢復功能增強(七)Oracle
- Oracle11新特性——備份恢復功能增強(十一)Oracle
- Oracle11新特性——分割槽功能增強Oracle
- Oracle11新特性——分割槽功能增強(五)Oracle
- Oracle11新特性——分割槽功能增強(四)Oracle
- Oracle11新特性——分割槽功能增強(三)Oracle
- Oracle11新特性——分割槽功能增強(二)Oracle
- Oracle11新特性——分割槽功能增強(一)Oracle
- Oracle11新特性——線上操作功能增強(七)Oracle
- Oracle11新特性——線上操作功能增強(六)Oracle
- Oracle11新特性——線上操作功能增強(五)Oracle
- Oracle11新特性——線上操作功能增強(四)Oracle
- Oracle11新特性——線上操作功能增強(三)Oracle
- Oracle11新特性——線上操作功能增強(二)Oracle
- Oracle11新特性——線上操作功能增強(一)Oracle
- 10G新特性筆記之備份恢復新特性筆記
- 【RMAN】Oracle11g備份恢復新特性Oracle
- Oracle12c功能增強 新特性之管理功能的增強Oracle
- Oracle12c功能增強新特性之維護&升級&恢復&資料泵等Oracle
- 【備份恢復】從備份恢復資料庫資料庫
- 【管理篇備份恢復】備份恢復基礎
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- 備份與恢復--利用備份的控制檔案恢復
- Oracle11新特性——PLSQL新特性(七)OracleSQL
- Oracle11新特性——PLSQL新特性(六)OracleSQL
- Oracle11新特性——PLSQL新特性(五)OracleSQL
- Oracle11新特性——PLSQL新特性(四)OracleSQL
- Oracle11新特性——PLSQL新特性(三)OracleSQL