Oracle10g資料庫恢複（RMAN 恢複）教程

Oracle10g資料庫恢複（RMAN 恢複）教程

一、自動管理檔案
 
二、使用RMAN功能，包括所有資料檔案庫維護和報告功能可通過使用Oracle Enterprise Manager來完成【調用RMAN恢複的指令碼】
 
RMAN恢複：restore和recover命令
 
restore：是RMAN從我們備份的地方找一下備份組，然後把這些備
 
份集移動到現在資料檔案的位置。【相當於使用者管理中的copy命令】
 
recover：開始使用歸檔記錄檔或者聯機重做記錄檔把你變化的資料恢複回來。
 
首先用RMAN做一下備份，備份到自己所建目錄rback下；
 
RMAN> backup database format '/u01/rback/rm_%U' plus archivelog delete input;
 
測試1（通過RMAN恢複一個資料表空間）：
 
【建立一些類比的資料，再類比破壞資料庫】
 
SQL>conn scott/tiger
 
SQL> select * from tab;
 
TNAME                          TABTYPE  CLUSTERID
 
------------------------------ ------- ----------
 
E1                             TABLE
 
E22                            TABLE
 
USER1                          TABLE
 
DEPT                           TABLE
 
EMP                            TABLE
 
BONUS                          TABLE
 
SALGRADE                       TABLE
 
刪除一些資訊
 
SQL> delete from e22;
 
56 rows deleted.
 
SQL> select * from e22;
 
no rows selected
 
【注意：在第一次做RMAN備份的時候e22中有資料，備份完成之後刪除e22表裡的資訊】
 
【類比資料檔案丟失】
 
$cd /u01/Oracle/oradata/orcl
 
$rm -rf users01.dbf
 
【到sysdba下清理一下記憶體】
 
SQL> alter system flush buffer_cache;
 
SQL> alter system flush shared_pool;
 
【進入scott查看e22表】
 
SQL> select * from e22;
 
select * from e22
 
*
 
ERROR at line 1:
 
ORA-01116: error in opening database file 4
 
ORA-01110: data file 4: '/u01/Oracle/oradata/orcl/users01.dbf'
 
ORA-27041: unable to open file
 
Linux-x86_64 Error: 2: No such file or directory
 
Additional information: 3
 
【不只是e22，其他表都不能查，因為資料檔案沒了】
 
執行指令碼
 
RMAN>
 
run{
 
sql"alter tablespace users offline immediate";
 
restore tablespace users;
 
recover tablespace users delete archivelog;
 
sql"alter tablespace users online";
 
}
 
sql statement: alter tablespace users offline immediate
 
Starting restore at 17-APR-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
 
/u01/Oracle/oradata/orcl/users01.dbf
 
channel ORA_DISK_1: reading from backup piece
 
/u01/rback/rm_4op5u3sf_1_1
 
channel ORA_DISK_1: restored backup piece 1
 
piece handle=/u01/rback/rm_4op5u3sf_1_1
 
tag=TAG20140417T032503
 
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
 
Finished restore at 17-APR-14
 
Starting recover at 17-APR-14
 
using channel ORA_DISK_1
 
using channel ORA_DISK_2
 
starting media recovery
 
media recovery complete, elapsed time: 00:00:06
 
Finished recover at 17-APR-14
 
sql statement: alter tablespace users online
 
恢複完成，發現scott使用者下e22表裡任然沒有資料
 
SQL> select * from e22;
 
no rows selected
 
恢複成功。
 
測試2 （恢複一個資料檔案）
 
【在e22表裡插入一些資料並查看】
 
SQL> select count(*) from e22;
 
COUNT(*)
 
----------
 
42
 
【再刪除資料檔案】
 
$cd /u01/Oracle/oradata/orcl
 
$ rm -rf users01.dbf
 
【到sysdba下清理一下記憶體】
 
SQL> alter system flush buffer_cache;
 
SQL> alter system flush shared_pool;
 
【查看資料】
 
SQL> select * from e22;
 
select * from e22
 
*
 
ERROR at line 1:
 
ORA-01116: error in opening database file 4
 
ORA-01110: data file 4: '/u01/Oracle/oradata/orcl/users01.dbf'
 
ORA-27041: unable to open file
 
Linux-x86_64 Error: 2: No such file or directory
 
Additional information: 3
 
【再次恢複資料】
 
run{
 
sql"alter database datafile 4 offline";
 
restore datafile 4;
 
recover datafile 4 delete archivelog;
 
sql"alter database datafile 4 online";
 
}
 
sql statement: alter database datafile 4 offline
 
Starting restore at 17-APR-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
 
/u01/Oracle/oradata/orcl/users01.dbf
 
channel ORA_DISK_1: reading from backup piece
 
/u01/rback/rm_4op5u3sf_1_1
 
channel ORA_DISK_1: restored backup piece 1
 
piece handle=/u01/rback/rm_4op5u3sf_1_1
 
tag=TAG20140417T032503
 
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
 
Finished restore at 17-APR-14
 
Starting recover at 17-APR-14
 
using channel ORA_DISK_1
 
using channel ORA_DISK_2
 
starting media recovery
 
media recovery complete, elapsed time: 00:00:04
 
Finished recover at 17-APR-14
 
sql statement: alter database datafile 4 online
 
RMAN>
 
進入檔案$cd /u01/Oracle/oradata/orcl查看發現有user01.dbf
 
再次進入scott使用者下，查看
 
SQL> select count(*) from e22;
 
COUNT(*)
 
----------
 
42
 
探索資料恢複了。
 
恢複成功。

當然如果你有異地備份我們可以直接使用異地恢複來協助我們實現了,下面就一直來看看

oracle10g資料庫恢複（異地恢複）

丟失所有的資料檔案、參數檔案、控制檔案、密碼檔案

開啟控制檔案與參數檔案的自動備份
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;

做一個RMAN完全備份
RMAN>backup database format'/u01/rback/rm_22 _%U' plus archivelog delete input;

切換記錄檔
SQL>alter system switch logfile；

關閉資料庫
SQL> shutdown immediate;

***********
【類比丟失所有檔案】
刪除所有檔案
cd /u01/oracle/oradata/orcl
$rm -f *

刪除所有參數檔案
cd $ORACLE_HOME/dbs
$ rm -f spfileorcl.ora
$rm -f pfileorcl.ora
刪除密碼檔案
$ rm -f orapworcl
**************

1.找任意一個參數檔案啟動資料庫
$ /u01/oracle/admin/orcl/pfile

2.啟動到mount狀態前設定一下環境變數
$ export ORACLE_SID=orcl

3.啟動到nomount【注意用init.ora啟動】
SQL> startup nomount pfile='/u01/oracle/admin/orcl/pfile/init.ora';

4.還原參數檔案。
RMAN> restore spfile from autobackup;

Starting restore at 20-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /u01/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/oracle/flash_recovery_area/ORCL/autobackup/2014_03_20/o1_mf_s_842721192_9lod9bdd_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 20-MAR-14

5.重啟資料庫【用恢複的參數檔案啟動資料庫】
RMAN> shutdown immediate;

RMAN> startup nomount;

6.恢複控制檔案
RMAN> restore controlfile from autobackup;

Starting restore at 20-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /u01/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/oracle/flash_recovery_area/ORCL/autobackup/2014_03_20/o1_mf_s_842721192_9lod9bdd_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/oracle/oradata/orcl/control01.ctl
output filename=/u01/oracle/oradata/orcl/control02.ctl
output filename=/u01/oracle/oradata/orcl/control03.ctl
Finished restore at 20-MAR-14

7.啟動到mount狀態
RMAN> alter database mount;

8.還原資料庫
RMAN> restore database;

Starting restore at 20-MAR-14
Starting implicit crosscheck backup at 20-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 20-MAR-14
Starting implicit crosscheck copy at 20-MAR-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-MAR-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/oracle/flash_recovery_area/ORCL/autobackup/2014_03_20/o1_mf_s_842721192_9lod9bdd_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oracle/oradata/orcl/system01.dbf
restoring datafile 00002 to /u01/oracle/oradata/orcl/undotbs01.dbf
restoring datafile 00003 to /u01/oracle/oradata/orcl/sysaux01.dbf
restoring datafile 00004 to /u01/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmbak/rm_22_03p3lprb_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rmbak/rm_22_03p3lprb_1_1 tag=TAG20140320T171211
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 20-MAR-14

 

9.恢複資料庫
RMAN> recover database;

Starting recover at 20-MAR-14
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=8
channel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/ORCL/backupset/2014_03_20/o1_mf_annnn_TAG20140320T171310_9lod97wc_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/flash_recovery_area/ORCL/backupset/2014_03_20/o1_mf_annnn_TAG20140320T171310_9lod97wc_.bkp tag=TAG20140320T171310
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/arch/1_8_842690420.dbf thread=1 sequence=8
archive log filename=/u01/arch/1_9_842690420.dbf thread=1 sequence=9
archive log filename=/u01/arch/1_9_842690420.dbf thread=1 sequence=10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/20/2014 17:34:53
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/arch/1_9_842690420.dbf'
ORA-00310: archived log contains sequence 9; sequence 10 required
ORA-00334: archived log: '/u01/arch/1_9_842690420.dbf'
【這個錯誤可以不用考慮】

10.以resetlogs方式開啟資料庫
RMAN> alter database open resetlogs;

11.添加暫存資料表空間
SQL>alter tablespace temp add tempfile'/u01/oracle/oradata/orcl/temp01.dbf'size 10m;

建立密碼檔案
$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle force=y
【force=y要是以前有的話就替換掉】