使用者管理恢複 一、Oracle恢複的體系結構
二、恢複類型：執行個體恢複、媒體恢複
1、執行個體恢複的前提是資料檔案、控制檔案和線上日志均沒有損壞，完全自動
2、媒體恢複：在檔案有實體損壞的情況下，将其還原、恢複、最後使資料庫得以正常打開的操作稱為媒體恢複，人工幹預
三、恢複方式：完全恢複、不完全恢複
四、恢複技術：使用者管理恢複和RMAN恢複
五、完全恢複案例
v$recover_file //哪個檔案需要恢複 select file#,change# from v$recover_file;
v$log //線上日志 select min(first_change#) from v$log where status!='INACTIVE';
v$archived_log //歸檔日志 select min(first_change#) from v$archived_log;
v$recovery_log //需要哪些archvielog做恢複 1、*場景1:資料庫關閉時的恢複(system,sysaux,undo,whole database):system表空間為例(冷備)
select * from v$dbfile;
shutdown immediate;
cp -rf /u01/app/oracle/oradata/ocp/* /backup/cold --冷備整個資料檔案
startup
conn gyj/gyj
insert into t1 values(4,'gyj4');
commit;
alter system switch logfile; --切換日志
rm -rf system01.dbf --冊除system系統表空間
shutdown abort; --異常關機
cp /backup/cold/system01.dbf /u01/app/oracle/oradata/ocp/ --用備份先做還原
startup; --會報錯:ORA-01157，ORA-01110
recover datafile 1; --恢複
一直回車
alter database open; 2、*場景2:資料庫打開時的恢複以使用者資料的表空間為例(熱備)
create tablespace tp5 datafile '/u01/app/oracle/oradata/ocp/tp5.dbf' size 10M;
conn gyj/gyj
create table t5 (id int,name varchar2(10)) tablespace tp5;
insert into t5 values(1,'gyj1');
commit;
alter tablespace tp5 begin backup; --熱備
!cp /u01/app/oracle/oradata/ocp/tp5.dbf /backup/hot
alter tablespace tp5 end backup;
conn gyj/gyj
insert into t5 values(2,'gyj2');
commit;
rm -rf tp5.dbf;
alter database datafile 9 offline;
!cp /backup/hot/tp5.dbf /u01/app/oracle/oradata/ocp/ --用備份先做還原
recover datafile 9; --恢複
alter database datafile 11 online;
3、控制檔案的恢複
select controlfile_sequence# from v$database;--目前控件檔案記錄的序列号
select hxfil as file#,FHCSQ from x$kcvfh;
select controlfile_change# from v$database; --控制自身的檢查點
select file#,checkpoint_change# from v$datafile_header;
select checkpoint_change# from v$database; --資料庫的檢查點
select file#,checkpoint_change# from v$datafile; --從控制檔案中讀的scn
(1)***場景1:控制檔案沒有全壞,有一個是好的
rm -rf control02.ctl
shutdown abort;
cp control01.ctl control02.ctl
startup
(2)***場景2:所有控制檔案都壞了,但損壞前有備份(二進制)
alter database backup controlfile to '/backup/control/control.bin';
alter system checkpoint;
rm -rf control*
shutdown abort;
cp /backup/control.bin control01.ctl
cp /backup/control.bin control02.ctl
cp /backup/control.bin control03.ctl
startup
select checkpoint_change# from v$database;
select checkpoint_change# from v$datafile;
select checkpoint_change# from v$datafile_header;
recover database using backup controlfile;
redologfile 一個個試
alter database open resetlogs; (3)*場景3:所有控制檔案都壞了,但損壞前有備份(文本)
alter database backup controlfile to trace as '/backup/control/control.txt';
rm -rf control*
shutdown abort;
vi control.bak --重構一個新的控制檔案
conn /as sysdba
@/backup/control/control.txt
recover database;
alter database open;
(4) *場景4:備份控制檔案的恢複,控制檔案中不包含資料檔案,但聯機日志中有
alter database backup controlfile to '/backup/control/control.bin';
create tablespace tp6 datafile '/u01/app/oracle/oradata/ocp/tp6.dbf' size 10M;
conn gyj/gyj
create table t6(id int,name varchar2(10)) tablespace tp6;
insert into t6 values(1,'gyj1');
commit;
rm -rf control*
shutdown abort;
cp /backup/control/control.bin control01.ctl
cp /backup/control/control.bin control02.ctl
cp /backup/control/control.bin control03.ctl
startup mount;
select file#,checkpoint_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
alter database open; //提示控制檔案舊了
recover database using backup controlfile;
輸入:/oradata/orcl/redo01.log --狀态是current
提示有一個檔案不識别
select file#,name from v$datafile;
alter database rename file '/u01/app/oracle/product/11g/dbs/UNNAMED00005' to '/u01/app/oracle/oradata/ocp/tp6.dbf'; --看上面提示或 看alert.log日志
recover database using backup controlfile;
輸入:/oradata/orcl/redo01.log --一select * from v$log;
alter database open resetlogs;
二、不完全恢複案例
1、不完全恢複的動因
(1)被動恢複：原計劃執行完全恢複，但是恢複的過程中發現recover指令需要的至少一個歸檔日志或未歸檔的ACTIVE/CURRENT線上日志損壞
(2)主動恢複：由于人為錯誤碼或惡意篡改破壞了資料庫内大量的資訊（update,truncate） 2、不完全恢複：recover指令的四種情況
(1)精确到重做日志的基于日志序列号的“recover database until sequence”--僅适用RMAN
(2)精确到到重做日志的基于手動互動式的“recover database until cancel”--僅适用SQL*PLUS
(3)精确到重做記錄的基于時間的 “recover database until time”--适用RMAN和SQL*PLUS
(4)精确到重做記錄的基于SCN的“recover database until scn”--僅适用RMAN 3、基本時間的不完全恢複
(1)*場景１:誤操作删除表，控制檔案沒損壞
shutdown immediate;
!cp /u01/app/oracle/oradata/ocp/* /backup/cold/ --做一個冷備
startup
conn gyj/gyj
insert into t1 values(3,'gyj3');
commit;
select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') from dual;--恢複此時間之前的資料
insert into t1 values(4,'gyj4');
commit;
drop table t1; --模拟誤操作
shutdown immediate;
rm -rf /u01/app/oracle/oradata/ocp/*.dbf --删除所有資料(不删控制檔案和重做日志檔案)
cp -rf /backup/cold/*.dbf /u01/app/oracle/oradata/ocp/ --restore所有資料
startup mount;
recover database until time '2013-03-29 09:49:05'; ---基于時間的不完全恢複
alter database open resetlogs; (2) *場景1:誤操作删除表，控制檔案也被損壞了
shutdown immediate;
!cp /u01/app/oracle/oradata/ocp/* /backup/cold/ --做一個冷備
startup
conn gyj/gyj
insert into t1 values(6,'gyj6');
commit;
select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') from dual;--誤操作前的時間
2012-06-22:17:09:08
insert into t1 values(7,'gyj7');
commit;
drop table t1; --模拟誤操作
shutdown abort;
rm -rf /u01/app/oracle/oradata/ocp/*.dbf --删除資料檔案
rm -rf /u01/app/oracle/oradata/ocp/*.ctl --删除控制檔案,(不删除重做日志檔案)
cp -rf /backup/cold/*.dbf /u01/app/oracle/oradata/ocp/ --還原所有資料
cp -rf /backup/cold/*.ctl /u01/app/oracle/oradata/ocp/ --還原控制檔案
startup mount;
recover database until time '2012-10-31:11:47:15' using backup controlfile; --用了備份的控制檔案
alter database open resetlogs; 4、基本Cancel的不完全恢複
(1)***場景1:寫到資料檔案,歸檔沒保留
shutdown immediate;
!cp -rf /u01/app/oracle/oradata/ocp/* /backup/cold/ --重生做一個冷備 startup
conn gyj/gyj
insert into t1 values(4,'gyj4');
commit;
alter system switch logfile;
insert into t1 values(5,'gyj5');
commit;
select group#,status from v$log;
alter system checkpoint; --寫到資料檔案,歸檔沒保留 rm -rf /u01/app/oracle/oradata/ocp/ --把所有的/oradata/orcl/ 全部删掉
shutdown abort;
cp /backup/cold/*.dbf /u01/app/oracle/oradata/ocp/ --controlfile,redolog都沒了
cp /backup/cold/*.ctl /u01/app/oracle/oradata/ocp/
startup mount; ; recover database using backup controlfile until cancel; --用了備份的控制檔案
--如歸檔存在,直接回車
--如歸檔不存在,輸入cancel,因為redolog全删了
alter database open resetlogs;
alter tablespace temp add tempfile '/u01/app/oracle/oradata/bxdb/temp.dbf' size 10M;
select * from t; --資料少了一條
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' REUSE;
RMAN恢複 1、RMAN完全恢複
(1)*場景1:參數檔案損壞
backup spfile;
--create pfile='/tmp/pfile.ora' from spfile;
shutdown immediate;
rm -rf $ORACLE_HOME/dbs/spfileocp.ora
startup pfile='/tmp/pfile.ora' nomount;
set dbid 43434343
restore spfile from autobackup;
restore spfile to '/temp/spfile.ora' from autobackup;
(2)*場景2:控制檔案損壞
backup current controlfile;
rm -rf control*
startup nomount;
set dbid=2690543208;
restore controlfile from autobackup;--db_recovery_file_dest或$ORACLE_HOME/dbs(set dbid)
alter database mount;
recover database;
alter database open resetlogs; (3) *場景3 system表空間資料檔案損壞
backup database;
rm -rf system01.dbf;
shutdown abort;
startup mount;
restore datafile 1;
recover datafile 1;
alter database open; (4)*場景4 undo表空間損壞
startup mount;
restore tablespace UNDOTBS1;
recover database;
alter database open; (5)*場景5:磁盤損壞
rm -rf tp1.dbf
select name,file# from v$datafile;
run{ shutdown abort;
startup mount;
set newname for datafile 6 to '/u01/app/oracle/oradata/ocm/tp1.dbf';
restore datafile 6;
switch datafile 6 ;
recover database;
alter database open;} (6)*場景6:普通資料檔案損壞
rm -rf tp1.dbf 方法一
run{
shutdown abort;
startup mount;
restore datafile 6;
recover datafile 6;
alter database open;} 方法二
sql 'alter database datafile 6 offline';
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';} 方法三
shutdown abort;
startup mount;
sql 'alter database datafile 6 offline';
alter database open;
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';} (7)控制檔案無損的情況下，隻讀檔案損壞
*場景1:隻讀資料檔案丢失
run{
shutdown abort;
startup mount;
restore datafile 5;
alter database open;} *場景2:隻讀資料檔案在執行個體運作時丢失
run{
sql 'alter database datafile 5 offline';
restore datafile 5;
sql 'alter database datafile 5 online';} *場景3:隻讀資料檔案内部資料塊損壞
run{
sql 'alter database datafile 5 offline';
restore datafile 5 force;
sql 'alter database datafile 5 online';} *場景4:起先資料檔案備份時是隻讀狀态，後來其表空間改為讀寫狀态，但沒有備份，現資料檔案壞了
run{
sql 'alter database datafile 6 offline';
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';} (8)控制檔案損壞的情況下，隻讀檔案損壞
分析隻讀表空間在控制檔案的情況
alter database backup controlfile to trace as '/backup/control.bak01';
alter tablespace read only;
alter database backup controlfile to trace as '/backup/control.bak02';
diff control.bak01 control.bak02 --比較兩個備份的控制檔案 alter database rename file '' to ''; --隻讀表空間在重構時不考慮,要在資料庫打開後,重命名資料檔案
alter database tp1 online; *場景1:隻讀資料檔案連同控件檔案一并損壞或丢失
run{
shutdown abort;
startup nomount;
restore controlfile from autobackup;
alter database mount;
restore datafile 6;
recover database;
alter database open resetlogs;}
*場景2:在資料檔案為隻讀狀态時，對該檔案和控制檔案進行了備份，随且對該表空間改為讀寫狀态，但該檔案和控制檔案均未再有過備份，此時該資料檔案連同控件檔案一并損壞或丢失
run{
shutdown abort;
startup nomount;
restore controlfile from '/backup/rman/full_0cnnlmfd_1_1';
mount database;
restore datafile 6;
recover database;
recover database; --注意再次執行
alter database open resetlogs;}
(9)表空間的恢複
[過程：全備->建立表空間->在此表空間上進行業務->此表空間被 DROP->恢複此表空間
[oracle@redhat55 ~]$ rman target/
Recovery Manager:Release 10.2.0.1.0 - Production on 星期二 4月 30 23:23:19 2013
Copyright (c) 1982,2005, Oracle. All rights reserved.
connected to targetdatabase: ORCL (DBID=1341748519)
RMAN> list backup;
using targetdatabase control file instead of recovery catalog
List of Backup Sets
BS Key Type LV Size Device Type Elapsed Time CompletionTime
1 Full 30.83M DISK 00:01:00 2013-04-30 23:21:46
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20130430T232045
Piece Name:/disk1/backup/orcl/02o8dnae_1_1.bak
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
2 Full 573612 2013-04-3023:20:46 /u01/app/oracle/oradata/orcl/undotbs01.dbf
3 Full 573612 2013-04-3023:20:46 /u01/app/oracle/oradata/orcl/sysaux01.dbf
5 Full 573612 2013-04-3023:20:46 /u01/app/oracle/oradata/orcl/example01.dbf
2 Full 80.84M DISK 00:01:19 2013-04-30 23:22:05
BP Key: 2 Status: AVAILABLE Compressed: YES Tag: TAG20130430T232045
Piece Name:/disk1/backup/orcl/01o8dnae_1_1.bak
List of Datafiles in backup set 2
1 Full 573611 2013-04-3023:20:46 /u01/app/oracle/oradata/orcl/system01.dbf
4 Full 573611 2013-04-3023:20:46 /u01/app/oracle/oradata/orcl/users01.dbf
3 Full 6.80M DISK 00:00:00 2013-04-30 23:22:07
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20130430T232207
Piece Name: /disk1/backup/orcl/c-1341748519-20130430-00.clt
Control File Included: Ckp SCN: 573653 Ckp time: 2013-04-30 23:22:07
SPFILE Included: Modification time:2013-04-30 23:17:26
RMAN>
[oracle@redhat55 ~]$ sqlplus /as sysdba
SQL*Plus: Release10.2.0.1.0 - Production on 星期二 4月 30 23:24:35 2013
Connected to:
Oracle Database 10gEnterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL>
SQL> create tablespace tbsdatafile '/u01/app/oracle/oradata/orcl/tbs.dbf' size 5m;
Tablespace created.
SQL> create table t (id int)tablespace tbs;
Table created.
SQL> insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
ID
1
SQL> select file_name fromdba_data_files;
FILE_NAME
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/tbs.dbf
6 rows selected.
SQL> select current_scn fromv$database;
CURRENT_SCN
573950 // 恢複時恢複到這個SCN号
SQL> alter system archive logcurrent;
System altered.
SQL> drop tablespace tbsINCLUDING CONTENTS and datafiles;
Tablespace dropped.
SQL> select file_namefrom dba_data_files; // 表空間對應的資料檔案已經沒有了
SQL> shutdown abort;
ORACLE instance shutdown.
SQL> startup nomount;
ORACLE instancestarted.
Total System GlobalArea 1224736768 bytes
Fixed Size 2020384 bytes
Variable Size 318770144 bytes
DatabaseBuffers 889192448 bytes
Redo Buffers 14753792 bytes
SQL> exit
Disconnected fromOracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
Recovery Manager:Release 10.2.0.1.0 - Production on 星期二 4月 30 23:29:31 2013
connected to targetdatabase: orcl (not mounted)
RMAN> restore controlfilefrom '/disk1/backup/orcl/c-1341748519-20130430-00.clt';
Starting restore at2013-04-30 23:29:54
allocated channel:ORA_DISK_1
channel ORA_DISK_1:sid=156 devtype=DISK
channel ORA_DISK_1:restoring control file
channel ORA_DISK_1:restore complete, elapsed time: 00:00:02
outputfilename=/u01/app/oracle/oradata/orcl/control01.ctl
outputfilename=/u01/app/oracle/oradata/orcl/control02.ctl
outputfilename=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at2013-04-30 23:29:57
RMAN> shutdown abort;
Oracle instance shutdown
RMAN> startup mount;
connected to targetdatabase (not started)
Oracle instancestarted
database mounted
RMAN> restore database;
Starting restore at2013-04-30 23:30:30
Starting implicitcrosscheck backup at 2013-04-30 23:30:30
channel ORA_DISK_1:sid=157 devtype=DISK
allocated channel:ORA_DISK_2
channel ORA_DISK_2:sid=155 devtype=DISK
Crosschecked 2objects
Finished implicitcrosscheck backup at 2013-04-30 23:30:33
Starting implicitcrosscheck copy at 2013-04-30 23:30:33
using channelORA_DISK_1
using channelORA_DISK_2
Finished implicitcrosscheck copy at 2013-04-30 23:30:33
searching for allfiles in the recovery area
cataloging files...
no files cataloged
channel ORA_DISK_1:starting datafile backupset restore
channel ORA_DISK_1:specifying datafile(s) to restore from backup set
restoring datafile00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
restoring datafile00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
restoring datafile00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1:reading from backup piece /disk1/backup/orcl/02o8dnae_1_1.bak
channel ORA_DISK_2:starting datafile backupset restore
channel ORA_DISK_2:specifying datafile(s) to restore from backup set
restoring datafile00001 to /u01/app/oracle/oradata/orcl/system01.dbf
restoring datafile00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_2:reading from backup piece /disk1/backup/orcl/01o8dnae_1_1.bak
channel ORA_DISK_1:restored backup piece 1
piecehandle=/disk1/backup/orcl/02o8dnae_1_1.bak tag=TAG20130430T232045
channel ORA_DISK_1:restore complete, elapsed time: 00:00:46
channel ORA_DISK_2:restored backup piece 1
piecehandle=/disk1/backup/orcl/01o8dnae_1_1.bak tag=TAG20130430T232045
channel ORA_DISK_2:restore complete, elapsed time: 00:01:11
Finished restore at2013-04-30 23:31:45
RMAN> run{
2> set until scn 573950;
3> recover database;
4> }
executing command:SET until clause
Starting recover at2013-04-30 23:33:37
starting mediarecovery
archive logfilename=/disk1/backup/orcl/1_3_813967785.arc thread=1 sequence=3
creating datafilefno=6 name=/u01/app/oracle/oradata/orcl/tbs.dbf
media recoverycomplete, elapsed time: 00:00:03
Finished recover at2013-04-30 23:33:43
RMAN> sql'alter database openresetlogs';
sql statement: alterdatabase open resetlogs
RMAN> exit
Recovery Managercomplete.
SQL*Plus: Release10.2.0.1.0 - Production on 星期二 4月 30 23:34:03 2013
/u01/app/oracle/oradata/orcl/tbs.dbf // 表空間已經恢複
總結：表空間在沒有備份的情況下被DROP，此時若想恢複被DROP的表空間，必須使用DROP之前的備份及控制檔案，然後利用歸檔日志再将資料庫恢複到DROP之間的點，表空間會被歸檔日志裡重新建立。
二、RMAN不完全恢複
(1)*場景1:基本時間的不完全恢複
startup
conn gyj/gyj
insert into t1 values(6,'gyj6');
commit;
select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') from dual;
insert into t1 values(9,'gyj9');
commit;
drop table t1; --誤操作
run{
shutdown abort;
startup mount;
set until time "to_date('2013-04-14:11:42:18','yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;}
(2)*場景2:基于sequence的不完全恢複
conn gyj/gyj
insert into t1 values(9,'gyj9');
commit;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
insert into t1 values(10,'gyj10');
commit;
alter system switch logfile;
alter system switch logfile;
insert into t1 values(11,'gyj11');
alter system switch logfile;
rman target /
run{
shutdown abort;
startup mount;
set until sequence 10 thread 1;
restore database;
recover database;
alter database open resetlogs;}
總結 一、關于recover database指令
（1）SQL>recover database;
該指令用來對所有資料檔案進行恢複，并且隻能使用儲存在檔案系統上的歸檔日志及線上日志。使用此指令的前提是控制檔案不可以是還原或重建得來的。
Oracle會以目前controlfile所記錄的SCN為準，利用archive log和 redo log的redo entry, 把相關的datafile 的 block恢複到“目前controlfile所記錄的SCN” （2）SQL>recover database using backup controlfile;
該指令用來對所有資料檔案及控制檔案進行恢複，并且隻使用儲存在檔案系統上的歸檔及線上日志。
Oracle需要把資料恢複到比目前controlfile所記錄的SCN還要靠後的位置（比如說，control file是backup controlfile , 或者 controlfile是根據trace create的。）,這時候，就需要用using backup controlfile. 恢複就不會受“目前controlfile所記錄的SCN”的限制。 （3）RMAN>recover database;
該指令用來對所有資料檔案及控制檔案進行恢複，并且可以使用增量備份、備份中的和檔案系統上的歸檔日志,以及線上日志。 二、關于resetlogs指令
（1）用備份的控制檔案做恢複必須要resetlogs，使用resetlogs的原因是recover指令隻能修複控制檔案中資料庫實體結構資訊，而無法修改控制檔案中的目前重做日志的序列号等資訊
（2）不完全恢複的resetlogs
三、資料庫的化身－－Incarnation
v$database_incarnation
RMAN>list incarnation
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)