第十章：Rman 不完全恢複
1、rman 不完全恢複：
1）基于時間和基于scn： 用于恢複過去某個時間被誤操作的重要table
案例1：
-------恢複過去某個時間點誤操作的table
1）測試環境
01:15:47 SQL> conn scott/tiger
Connected.
01:16:01 SQL> select * from lx01;
ID
----------
1
2
3
01:16:07 SQL> truncate table lx01;
Table truncated.
01:16:25 SQL> insert into lx01 values (111);
1 row created.
01:16:37 SQL> insert into lx01 values (222);
01:16:40 SQL> insert into lx01 values (333);
01:16:43 SQL> commit;
Commit complete.
01:16:44 SQL> select * from lx01;
111
222
333
01:16:49 SQL>
2）通過logmnr 找出誤操作的時間點
------配置 utl 參數
01:17:55 SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /home/oracle/logmnr
--------建立資料字典分析檔案
01:18:31 SQL> execute dbms_logmnr_d.build('dict.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);
PL/SQL procedure successfully completed.
--------添加日志分析
01:19:11 SQL> col member for a50
01:19:17 SQL>
1* select group#,member from v$logfile
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/prod/redo03.log
2 /u01/app/oracle/oradata/prod/redo02.log
1 /u01/app/oracle/oradata/prod/redo01.log
01:19:17 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 17 52428800 1 NO CURRENT 1424859 18-AUG-11
2 1 16 52428800 1 YES INACTIVE 1403618 18-AUG-11
3 1 15 52428800 1 YES INACTIVE 1383274 18-AUG-11
01:19:22 SQL>
01:19:22 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/prod/redo01.log',options=>dbms_logmnr.new);
----------執行分析
01:20:17 SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);
-----------檢視分析結果
01:22:17 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents
01:22:18 2 where lower(sql_redo) like '%table%' and username='SCOTT';
USERNAME SCN TIMESTAMP SQL_REDO
------------------------------ ---------- ------------------- --------------------------------------------------
SCOTT 1450811 2011-08-19 01:44:41 truncate table lx01;
---------結束logmnr
01:22:59 SQL> execute dbms_logmnr.end_logmnr;
01:23:23 SQL>
---------設定date 格式
01:24:31 SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
System altered.
3）恢複
[oracle@work ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 19 01:49:04 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=170319990)
run {
shutdown immediate;
startup mount;
allocate channel c1 type disk;
allocate channel c2 type disk;
set until time '2011-08-19 01:44:41';
restore database;
recover database;
alter database open resetlogs;
}
4）驗證
01:51:54 SQL> select * from scott.lx01;
01:51:58 SQL>
案例2：基于scn
01:34:32 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1450438
--------在實際的生産環境中，scn要通過logmnr找出，這裡隻是實驗
01:34:37 SQL> conn scott/tiger;
01:34:41 SQL>
01:34:41 SQL> select * from lx01;
01:34:58 SQL> truncate table lx01;
01:35:04 SQL> insert into lx01 values(1);
01:35:21 SQL> insert into lx01 values(2);
01:35:24 SQL> insert into lx01 values(4);
01:35:25 SQL> commit;
01:35:27 SQL> select * from lx01;
4
01:35:31 SQL>
2）通過rman恢複 ，lx01 被truncate 之前的data
run {
set until scn 1450438;
10> }
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 96470416 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
allocated channel: c1
channel c1: sid=157 devtype=DISK
allocated channel: c2
channel c2: sid=154 devtype=DISK
executing command: SET until clause
Starting restore at 19-AUG-11
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/prod/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/prod/users01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/prod/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/prod/index01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/prod/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/prod/test01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/prod/undo_tbs01.dbf
restoring datafile 00008 to /u01/app/oracle/oradata/prod/test02.dbf
restoring datafile 00009 to /u01/app/oracle/oradata/prod/cuug01.dbf
restoring datafile 00010 to /u01/app/oracle/oradata/prod/lx01.dbf
restoring datafile 00011 to /u01/app/oracle/oradata/prod/perfstat01.dbf
channel c1: reading from backup piece /disk1/rman/prod/PROD_54.bak
channel c1: restored backup piece 1
piece handle=/disk1/rman/prod/PROD_54.bak tag=TAG20110819T013156
channel c1: restore complete, elapsed time: 00:01:07
Finished restore at 19-AUG-11
Starting recover at 19-AUG-11
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 19-AUG-11
database opened
released channel: c1
released channel: c2
RMAN>
告警日志資訊：
Completed: alter database mount
Fri Aug 19 01:38:47 2011
Full restore complete of datafile 6 /u01/app/oracle/oradata/prod/test01.dbf. Elapsed time: 0:00:01
checkpoint is 1450186
Full restore complete of datafile 8 /u01/app/oracle/oradata/prod/test02.dbf. Elapsed time: 0:00:02
Full restore complete of datafile 9 /u01/app/oracle/oradata/prod/cuug01.dbf. Elapsed time: 0:00:02
Full restore complete of datafile 10 /u01/app/oracle/oradata/prod/lx01.dbf. Elapsed time: 0:00:01
Fri Aug 19 01:39:10 2011
Full restore complete of datafile 2 /u01/app/oracle/oradata/prod/users01.dbf. Elapsed time: 0:00:23
Full restore complete of datafile 4 /u01/app/oracle/oradata/prod/index01.dbf. Elapsed time: 0:00:27
Full restore complete of datafile 5 /u01/app/oracle/oradata/prod/example01.dbf. Elapsed time: 0:00:29
Full restore complete of datafile 7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf. Elapsed time: 0:00:30
Fri Aug 19 01:39:24 2011
Full restore complete of datafile 11 /u01/app/oracle/oradata/prod/perfstat01.dbf. Elapsed time: 0:00:38
Fri Aug 19 01:39:36 2011
Full restore complete of datafile 3 /u01/app/oracle/oradata/prod/sysaux01.dbf. Elapsed time: 0:00:51
Fri Aug 19 01:39:49 2011
Full restore complete of datafile 1 /u01/app/oracle/oradata/prod/system01.dbf. Elapsed time: 0:01:03
Fri Aug 19 01:39:52 2011
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11
Completed: alter database recover datafile list
alter database recover if needed
start until change 1450438
Media Recovery Start
Recovery of Online Redo Log: Thread 1 Group 3 Seq 18 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log
Fri Aug 19 01:39:53 2011
Incomplete Recovery applied until change 1450439
Media Recovery Complete (prod)
Completed: alter database recover if needed
Fri Aug 19 01:39:56 2011
alter database open resetlogs
3）驗證：
01:42:00 SQL> select * from scott.lx01;
案例3：
-----------在做完全恢複時，歸檔日志或current redo log 不完整，不能實作完全恢複
----------基于redo log的sequence （相當于手工恢複：基于cancel）
01:55:05 SQL> select * from scott.lx01;
01:55:22 SQL> insert into scott.lx01 values (4);
01:55:31 SQL> commit;
01:55:32 SQL> alter system archive log current;
01:55:40 SQL> insert into scott.lx01 values (5);
01:55:41 SQL> commit;
01:55:44 SQL> insert into scott.lx01 values (6);
01:55:47 SQL> commit;
01:55:51 SQL> alter system archive log current;
01:55:54 SQL> insert into scott.lx01 values (7);
01:56:00 SQL> commit;
01:56:02 SQL> alter system archive log current;
01:56:07 SQL> insert into scott.lx01 values (8);
01:56:10 SQL> commit;
01:56:11 SQL> alter system archive log current;
01:56:16 SQL> insert into scott.lx01 values (9);
01:56:18 SQL> commit;
01:56:20 SQL> select name from v$archived_log;
NAME
------------------------------------------------------------------------------------------------------------------------
/disk1/arch/prod/arch_2_1_759549082.log
/disk1/arch/prod/arch_3_1_759549082.log
/disk1/arch/prod/arch_4_1_759549082.log
/disk1/arch/prod/arch_5_1_759549082.log
/disk1/arch/prod/arch_6_1_759549082.log
01:57:19 SQL> shutdown abort;
ORACLE instance shut down.
01:57:28 SQL> !
[oracle@work ~]$ exit
exit
01:57:32 SQL> !
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/lx01.dbf
[oracle@work ~]$ mv /disk1/arch/prod/arch_5_1_759549082.log /disk1/arch/prod/arch_5_1_759549082.log.bak
2）啟動database
01:58:23 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/app/oracle/oradata/prod/lx01.dbf'
01:58:39 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
10 FILE NOT FOUND
3)對datafile 做完全恢複
RMAN> run {
2> restore datafile 10;
3> recover datafile 10;
4> alter database open;
5> }
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: reading from backup piece /disk1/rman/prod/PROD_54.bak
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
using channel ORA_DISK_1
archive log thread 1 sequence 18 is already on disk as file /disk1/arch/prod/arch_18_1_759396736.log
archive log thread 1 sequence 1 is already on disk as file /disk1/arch/prod/arch_1_1_759548396.log
archive log thread 1 sequence 2 is already on disk as file /disk1/arch/prod/arch_2_1_759548396.log
archive log thread 1 sequence 3 is already on disk as file /disk1/arch/prod/arch_3_1_759548396.log
archive log thread 1 sequence 1 is already on disk as file /disk1/arch/prod/arch_1_1_759549082.log
archive log thread 1 sequence 2 is already on disk as file /disk1/arch/prod/arch_2_1_759549082.log
archive log thread 1 sequence 3 is already on disk as file /disk1/arch/prod/arch_3_1_759549082.log
archive log thread 1 sequence 4 is already on disk as file /disk1/arch/prod/arch_4_1_759549082.log
archive log thread 1 sequence 6 is already on disk as file /disk1/arch/prod/arch_6_1_759549082.log
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-03002: failure of recover command at 08/19/2011 01:59:56
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 5 lowscn 1451172 found to restore
---------恢複失敗，因為 ‘log thread 1 seq 5’ 丢失
4）執行不完全恢複
2> startup force mount;
3> set until sequence 5;
4> restore database;
5> recover database;
6> alter database open resetlogs;
7> }
5）驗證
02:04:04 SQL>
02:04:04 SQL> select * from scott.lx01;
5
6
6 rows selected.
案例4：
-------------誤删除表空間（已備份），通過備份的控制檔案進行恢複
---------基于 backup controlfile
02:21:27 SQL> select * from scott.lx01;
-------備份目前控制檔案
RMAN> backup current controlfile format '/disk1/rman/prod/control.bak';
02:21:50 SQL> insert into scott.lx01 values (7);
02:23:24 SQL> insert into scott.lx01 values (8);
02:23:26 SQL> insert into scott.lx01 values (9);
02:23:27 SQL> commit;
02:23:29 SQL> select * from scott.lx01;
7
8
9
9 rows selected.
----------表空間被誤删除
02:23:35 SQL> drop tablespace lx including contents and datafiles;
Tablespace dropped.
02:24:05 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
8 /u01/app/oracle/oradata/prod/test02.dbf TESTS
3 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/prod/users01.dbf USERS
1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE
6 /u01/app/oracle/oradata/prod/test01.dbf TESTS
7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf UNDO_TBS
4 /u01/app/oracle/oradata/prod/index01.dbf INDEXES
9 /u01/app/oracle/oradata/prod/cuug01.dbf CUUG
11 /u01/app/oracle/oradata/prod/perfstat01.dbf PERFSTAT
10 rows selected.
檢視告警日志，找到drop tablespace的時間點：
Fri Aug 19 02:24:01 2011
drop tablespace lx including contents and datafiles
2）恢複
02:25:32 SQL> select dbid from v$database;
DBID
170319990
---------恢複備份控制檔案
RMAN> shutdown immediate
RMAN> startup nomount
Variable Size 83887504 bytes
Database Buffers 226492416 bytes
RMAN> set dbid=170319990;
executing command: SET DBID
RMAN> restore controlfile from '/disk1/rman/prod/control.bak';
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/prod/control01.ctl
output filename=/u01/app/oracle/oradata/prod/control02.ctl
output filename=/u01/app/oracle/oradata/prod/control03.ctl
-----------基于時間點的恢複
run {
startup force mount;
set until time '2011-08-19 02:24:01'; //删除tablespace的時間點
9> }
3）驗證
02:34:03 SQL> col file_name for a50
02:34:10 SQL> select file_id,file_name,tablespace_name from dba_data_files;
10 /u01/app/oracle/oradata/prod/lx01.dbf LX
11 rows selected.
02:34:22 SQL> select * from scott.lx01;
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)