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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 九 GBase 8a備份和恢復
- RAC備份恢復之Voting備份與恢復
- 備份與恢復:polardb資料庫備份與恢復資料庫
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- mydumper備份恢復
- Mysql備份恢復MySql
- 備份和恢復
- 詳解叢集級備份恢復:物理細粒度備份恢復
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- Mysql備份與恢復(1)---物理備份MySql
- rman 增量備份恢復
- Jenkins備份與恢復Jenkins
- Postgresql 備份與恢復SQL
- MySQL 備份與恢復MySql
- KunlunDB備份和恢復
- RMAN備份恢復技巧
- redis 備份和恢復Redis
- Grafana 備份恢復教程Grafana
- 【PG備份恢復】pg_basebackup 多表空間備份恢復測試
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- MySQL 非常規恢復與物理備份恢復MySql
- Mysql備份與恢復(2)---邏輯備份MySql
- 使用 Android 備份和恢復功能留住使用者Android
- SqlServer備份和恢復(二)SQLServer
- Oracle 備份 與 恢復 概述Oracle
- Oracle 備份恢復之 FlashbackOracle
- SqlServer 備份和恢復(一)SQLServer
- 【MySQL】MySQL備份和恢復MySql
- DB的備份與恢復
- ORACLE備份&恢復案例(轉)Oracle
- GitLab的備份與恢復Gitlab
- 資料庫備份恢復資料庫
- RMAN備份異機恢復
- tore 命令來恢復備份
- 賦能雲HBase備份恢復 百T級別資料量備份恢復支援
- GitLab的自動備份、清理備份與恢復Gitlab
- RMAN備份恢復典型案例——異機恢復未知DBID
- innobackupex 部分表備份和恢復
- 備份與恢復oracle_homeOracle