本文共 16465 字，大约阅读时间需要 54 分钟。
第十章：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 301: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);
1 row created.
01:16:40 SQL> insert into lx01 values (333);
1 row created.
01:16:43 SQL> commit;Commit complete.
01:16:44 SQL> select * from lx01;
ID
---------- 111 222 33301:16:49 SQL>
2）通过logmnr 找出误操作的时间点
------配置 utl 参数
01:17:55 SQL> show parameter utlNAME 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$logfileGROUP# MEMBER
---------- -------------------------------------------------- 3 /u01/app/oracle/oradata/prod/redo03.log 2 /u01/app/oracle/oradata/prod/redo02.log 1 /u01/app/oracle/oradata/prod/redo01.log01: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-1101:19:22 SQL>
01:19:22 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/prod/redo01.log',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
----------执行分析
01:20:17 SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);
PL/SQL procedure successfully completed.
-----------查看分析结果
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;PL/SQL procedure successfully completed.
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;
ID
---------- 1 2 301:51:58 SQL>
案例2：基于scn
1）测试环境
01:34:32 SQL> select current_scn from v$database;CURRENT_SCN
----------- 1450438 --------在实际的生产环境中，scn要通过logmnr找出，这里只是实验01:34:37 SQL> conn scott/tiger;
Connected. 01:34:41 SQL> 01:34:41 SQL> select * from lx01;ID
---------- 111 222 333 01:34:58 SQL> truncate table lx01;Table truncated.
01:35:04 SQL> insert into lx01 values(1);
1 row created.
01:35:21 SQL> insert into lx01 values(2);
1 row created.
01:35:24 SQL> insert into lx01 values(4);
1 row created.
01:35:25 SQL> commit;
Commit complete.
01:35:27 SQL> select * from lx01;
ID
---------- 1 2 401:35:31 SQL>
2）通过rman恢复 ，lx01 被truncate 之前的data
run { shutdown immediate; startup mount; allocate channel c1 type disk; allocate channel c2 type disk; set until scn 1450438; restore database; recover database; alter database open resetlogs; 10> }database closed
database dismounted Oracle instance shut downconnected to target database (not started)
Oracle instance started database mountedTotal System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 96470416 bytes Database Buffers 213909504 bytes Redo Buffers 2973696 bytesallocated channel: c1
channel c1: sid=157 devtype=DISKallocated channel: c2
channel c2: sid=154 devtype=DISKexecuting 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-11Starting recover at 19-AUG-11
starting media recovery
media recovery complete, elapsed time: 00:00:04Finished recover at 19-AUG-11
database opened
released channel: c1 released channel: c2RMAN>
告警日志信息： 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 checkpoint is 1450186 Full restore complete of datafile 9 /u01/app/oracle/oradata/prod/cuug01.dbf. Elapsed time: 0:00:02 checkpoint is 1450186 Full restore complete of datafile 10 /u01/app/oracle/oradata/prod/lx01.dbf. Elapsed time: 0:00:01 checkpoint is 1450186 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 checkpoint is 1450186 Full restore complete of datafile 4 /u01/app/oracle/oradata/prod/index01.dbf. Elapsed time: 0:00:27 checkpoint is 1450186 Full restore complete of datafile 5 /u01/app/oracle/oradata/prod/example01.dbf. Elapsed time: 0:00:29 checkpoint is 1450186 Full restore complete of datafile 7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf. Elapsed time: 0:00:30 checkpoint is 1450186 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 checkpoint is 1450186 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 checkpoint is 1450186 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 checkpoint is 1450186 Fri Aug 19 01:39:52 2011 alter database recover datafile list clear Fri Aug 19 01:39:52 2011 Completed: alter database recover datafile list clear Fri Aug 19 01:39:52 2011 alter database recover datafile list 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 Completed: alter database recover datafile list 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 Fri Aug 19 01:39:52 2011 alter database recover if needed start until change 1450438 Media Recovery Start Fri Aug 19 01:39:52 2011 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 Fri Aug 19 01:39:53 2011 Media Recovery Complete (prod) Completed: alter database recover if needed start until change 1450438 Fri Aug 19 01:39:56 2011 alter database open resetlogs3）验证：
01:42:00 SQL> select * from scott.lx01;
ID
---------- 111 222 333 案例3： -----------在做完全恢复时，归档日志或current redo log 不完整，不能实现完全恢复----------基于redo log的sequence （相当于手工恢复：基于cancel）
1）测试环境
01:55:05 SQL> select * from scott.lx01;ID
---------- 1 2 301:55:22 SQL> insert into scott.lx01 values (4);
1 row created.
01:55:31 SQL> commit;
Commit complete.
01:55:32 SQL> alter system archive log current;
System altered.
01:55:40 SQL> insert into scott.lx01 values (5);
1 row created.
01:55:41 SQL> commit;
Commit complete.
01:55:44 SQL> insert into scott.lx01 values (6);
1 row created.
01:55:47 SQL> commit;
Commit complete.
01:55:51 SQL> alter system archive log current;
System altered.
01:55:54 SQL> insert into scott.lx01 values (7);
1 row created.
01:56:00 SQL> commit;
Commit complete.
01:56:02 SQL> alter system archive log current;
System altered.
01:56:07 SQL> insert into scott.lx01 values (8);
1 row created.
01:56:10 SQL> commit;
Commit complete.
01:56:11 SQL> alter system archive log current;
System altered.
01:56:16 SQL> insert into scott.lx01 values (9);
1 row created.
01:56:18 SQL> commit;
Commit complete.
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.log01:57:19 SQL> shutdown abort;
ORACLE instance shut down. 01:57:28 SQL> ! [oracle@work ~]$ exit exit01: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.bak2）启动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> }Starting restore at 19-AUG-11
using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=154 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00010 to /u01/app/oracle/oradata/prod/lx01.dbf channel ORA_DISK_1: reading from backup piece /disk1/rman/prod/PROD_54.bak channel ORA_DISK_1: restored backup piece 1 piece handle=/disk1/rman/prod/PROD_54.bak tag=TAG20110819T013156 channel ORA_DISK_1: restore complete, elapsed time: 00:00:08 Finished restore at 19-AUG-11Starting recover at 19-AUG-11
using channel ORA_DISK_1starting media recovery
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-00571: =========================================================== 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）执行不完全恢复
RMAN> run { 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;ID
---------- 4 5 6 1 2 36 rows selected.
案例4：
-------------误删除表空间（已备份），通过备份的控制文件进行恢复 ---------基于 backup controlfile 1）测试环境 02:21:27 SQL> select * from scott.lx01;ID
---------- 4 5 6 1 2 36 rows selected.
-------备份当前控制文件
RMAN> backup current controlfile format '/disk1/rman/prod/control.bak';
02:21:50 SQL> insert into scott.lx01 values (7);
1 row created.
02:23:24 SQL> insert into scott.lx01 values (8);
1 row created.
02:23:26 SQL> insert into scott.lx01 values (9);
1 row created.
02:23:27 SQL> commit;
Commit complete.
02:23:29 SQL> select * from scott.lx01;
ID
---------- 4 5 6 1 2 3 7 8 99 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 PERFSTAT10 rows selected.
查看告警日志，找到drop tablespace的时间点：
Fri Aug 19 02:24:01 2011 drop tablespace lx including contents and datafiles2）恢复
02:25:32 SQL> select dbid from v$database;DBID
---------- 170319990 ---------恢复备份控制文件 RMAN> shutdown immediatedatabase closed
database dismounted Oracle instance shut downRMAN> startup nomount
connected to target database (not started)
Oracle instance startedTotal System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 83887504 bytes Database Buffers 226492416 bytes Redo Buffers 2973696 bytesRMAN> set dbid=170319990;
executing command: SET DBID
RMAN> restore controlfile from '/disk1/rman/prod/control.bak';Starting restore at 19-AUG-11
allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=155 devtype=DISKchannel 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 Finished restore at 19-AUG-11-----------基于时间点的恢复
run {
startup force mount; allocate channel c1 type disk; allocate channel c2 type disk; set until time '2011-08-19 02:24:01'; //删除tablespace的时间点 restore database; recover database; alter database open resetlogs; 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;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 10 /u01/app/oracle/oradata/prod/lx01.dbf LX 11 /u01/app/oracle/oradata/prod/perfstat01.dbf PERFSTAT11 rows selected.
02:34:22 SQL> select * from scott.lx01;
ID
---------- 4 5 6 1 2 3 7 8 99 rows selected.
02:34:32 SQL>