第五章：手工完全恢复(归档模式）
1、完全恢复；通过备份、归档日志、current redo ，将database恢复到failure 前的最后一次commit 状态。
media recover的原因？
----------由于media failure 导致数据文件或controlfile 丢失media recover的分类？
------------归档模式 1）完全恢复 2）不完全恢复 --------------非归档模式 1)恢复到最后一次备份2、instance recover 和 media recover 区别：
-------instance recover ：instance 没有正常关闭 ，由smon 执行 -------media recover：因为介质failure，文件丢失，需dba 通过备份和redo 来恢复3、media recover的步骤：
1、restore 转储：将备份恢复到丢失文件的原位置 2、recover 恢复： 利用redo 日志，将备份点后的数据块通过redo 日志进行重做4、如何restore 和 recover
1）restore：手工恢复用的是os 下的拷贝命令。如cp
2）recover： sql 命令5、非归档模式下的数据恢复
1）转储所有的datafile 和controlfile 2）如果日志以切换，历史日志被覆盖，只能恢复到最近备份；如果日志没有发生切换，可以恢复到最后commit 状态6、归档模式下的数据恢复
1）完全恢复 2）不完全恢复7、完全恢复和不完全恢复的区别
1）完全恢复：需要所有的备份和redo 日志，可以将datafile恢复到failure前得最后一次commit，不会出现数据丢失 2）不完全恢复：通过备份和日志将database恢复到过去的某个时间点，有数据丢失。（尽量避免） 8、完全恢复的步骤 1）restore ：转储datafile 2）recover：利用归档日志和当前的redo 做recover9、recover database：当大部分datafile丢失，只能mount状态下
recover tablespace：tablespace 的数据文件都丢失了，在open状态 recover datafile ：当单个datafile丢失，可以在mount 或 open 状态10、恢复过程查看的视图：
1）v$recover_file：查看需要恢复的datafile 2）v$recovery_log： 查看recover 需要的redo 日志 3）v$archvied_log：查看已经归档的日志 案例1：recover database 1、 media failure 丢失大部分数据文件 1）模拟环境 05:45:49 SQL> select * from test;ID
---------- 1 2 305:45:52 SQL> insert into test values (4);
1 row created.
05:46:01 SQL> commit;
Commit complete.
05:46:02 SQL> insert into test values (5);
1 row created.
05:46:32 SQL> commit;
Commit complete.
05:46:34 SQL> insert into test values (6);
1 row created.
05:46:48 SQL> commit;
Commit complete.
05:46:49 SQL> insert into test values (7);
1 row created.
05:47:15 SQL> commit;
Commit complete.
05:46:08 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 38 52428800 1 NO CURRENT 1187992 16-AUG-11 2 1 36 52428800 1 YES INACTIVE 1184326 16-AUG-11 3 1 37 52428800 1 YES INACTIVE 1187989 16-AUG-1105:46:13 SQL> alter system switch logfile;
System altered.
05:46:43 SQL> alter system archive log current;
System altered.
05:46:58 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 38 52428800 1 YES ACTIVE 1187992 16-AUG-11 2 1 39 52428800 1 YES ACTIVE 1188675 16-AUG-11 3 1 40 52428800 1 NO CURRENT 1188689 16-AUG-1105:47:03 SQL> alter system archive log current;
System altered.
05:47:25 SQL>
05:47:16 SQL> insert into test values (8);
1 row created.
05:47:29 SQL> commit;
Commit complete.
05:47:30 SQL> insert into test values (9);
1 row created.
05:47:32 SQL> select * from test;
ID
---------- 1 2 3 4 5 6 7 8 99 rows selected.
05:47:38 SQL>
2）模拟介质失败
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/*.dbf3）启动database
05:48:57 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 1 - see DBWR trace file ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf' 05:49:03 SQL> select file#,error from v$recover_file;FILE# ERROR
---------- ----------------------------------------------------------------- 1 FILE NOT FOUND 3 FILE NOT FOUND 5 FILE NOT FOUND 6 FILE NOT FOUND 7 FILE NOT FOUND 4） 启动失败，需要做介质恢复 ，首先restore [oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/ --------recover database 05:51:48 SQL> select * from v$recovery_log;THREAD# SEQUENCE# TIME
---------- ---------- --------- ARCHIVE_NAME ------------------------------------------------------------------------------------------------------------------------ 1 38 16-AUG-11 /disk1/arch/prod/arch_38_1_758481658.log---------查看恢复需要的归档日志
05:51:58 SQL> select file#,checkpoint_change# from v$datafile;FILE# CHECKPOINT_CHANGE#
---------- ------------------ 1 1188700 2 1188700 3 1188700 4 1188700 5 1188700 6 1188700 7 11887007 rows selected.
05:52:42 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------ 1 1188419 2 1188700 3 1188419 4 1188700 5 1188419 6 1188419 7 11884197 rows selected.
-----------控制文件记录的scn 应大于需恢复的数据文件头部的scn 5） recover database 05:52:49 SQL> recover database; ORA-00279: change 1188419 generated at 08/16/2011 05:43:18 needed for thread 1 ORA-00289: suggestion : /disk1/arch/prod/arch_38_1_758481658.log ORA-00280: change 1188419 for thread 1 is in sequence #38 05:53:46 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto Log applied. Media recovery complete.查看告警日志：
ALTER DATABASE RECOVER database Tue Aug 16 05:53:46 2011 Media Recovery Start ORA-279 signalled during: ALTER DATABASE RECOVER database ... Tue Aug 16 05:54:13 2011 ALTER DATABASE RECOVER CONTINUE DEFAULT Tue Aug 16 05:54:13 2011 Media Recovery Log /disk1/arch/prod/arch_38_1_758481658.log Tue Aug 16 05:54:14 2011 Recovery of Online Redo Log: Thread 1 Group 2 Seq 39 Reading mem 0 Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo02.log Tue Aug 16 05:54:14 2011 Recovery of Online Redo Log: Thread 1 Group 3 Seq 40 Reading mem 0 Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log Tue Aug 16 05:54:14 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 41 Reading mem 0 Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log Tue Aug 16 05:54:14 2011 Media Recovery Complete (prod) Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT6）验证：
05:54:17 SQL> alter database open;Database altered.
05:55:31 SQL> select * from scott.test;
ID
---------- 1 2 3 4 5 6 7 88 rows selected.
05:55:40 SQL> select file#,checkpoint_change# from v$datafile;FILE# CHECKPOINT_CHANGE#
---------- ------------------ 1 1208722 2 1208722 3 1208722 4 1208722 5 1208722 6 1208722 7 12087227 rows selected.
05:57:58 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------ 1 1208722 2 1208722 3 1208722 4 1208722 5 1208722 6 1208722 7 12087227 rows selected.
05:58:03 SQL>
案例2： recover tablespace
2、恢复表空间（删除了tablespace的所有的datafile）
1）模拟环境 SQL> conn scott/tiger Connected. 06:05:36 SQL> 06:05:36 SQL> select * from tab;DEPT TABLE
EMP TABLE BONUS TABLE SALGRADE TABLE TEST TABLE 06:05:39 SQL> create table t01 (id int ) tablespace test; 06:06:13 SQL> insert into t01 values (1) ; 06:06:23 SQL> insert into t01 values (2) ; 06:06:25 SQL> insert into t01 values (3) ; 06:06:27 SQL> commit; 06:06:28 SQL> select * from t01;1
2 3 06:06:32 SQL> 06:06:55 SQL> shutdown abort ORACLE instance shut down. 06:06:59 SQL> ! [oracle@work ~]$ exit exit06:07:05 SQL> !
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/test*.dbf [oracle@work ~]$2）启动数据库
06:07:34 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 6 - see DBWR trace file ORA-01110: data file 6: '/u01/app/oracle/oradata/prod/test01.dbf' 06:07:41 SQL> select file#,error from v$recover_file;FILE# ERROR
---------- ----------------------------------------------------------------- 6 FILE NOT FOUND 8 FILE NOT FOUND 2）转储数据文件 [oracle@work ~]$ cp /disk1/backup/prod/close_bak/test*.dbf /u01/app/oracle/oradata/prod/3）数据文件offline
06:09:10 SQL> alter database datafile 6,8 offline;Database altered.
06:09:40 SQL> alter database open;
Database altered.
06:09:49 SQL>
4） recover tablespace
06:09:49 SQL> recover tablespace test;
Media recovery complete.查看告警日志：
ALTER DATABASE RECOVER tablespace test Tue Aug 16 06:10:33 2011 Media Recovery Start Tue Aug 16 06:10:33 2011 Recovery of Online Redo Log: Thread 1 Group 2 Seq 45 Reading mem 0 Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo02.log Tue Aug 16 06:10:33 2011 Recovery of Online Redo Log: Thread 1 Group 3 Seq 46 Reading mem 0 Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log Tue Aug 16 06:10:34 2011 Media Recovery Complete (prod) Completed: ALTER DATABASE RECOVER tablespace test5）验证：
06:10:36 SQL> alter database datafile 6,8 online;Database altered.
06:10:46 SQL> select * from scott.t01;
ID
---------- 1 2 306:10:52 SQL>
案例4：（recover tablespace ，database open状态）
--------------database在open 状态下恢复数据文件（除了system tablespace）1） 模拟环境：
06:10:52 SQL> insert into scott.t01 values (4);1 row created.
06:13:12 SQL> insert into scott.t01 values (5);
1 row created.
06:13:13 SQL> insert into scott.t01 values (6);
1 row created.
06:13:15 SQL> commit;
Commit complete.
06:13:17 SQL> select * from scott.t01;
ID
---------- 1 2 3 4 5 66 rows selected.
--------在open 状态下删除datafile
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/test*.dbf
[oracle@work ~]$06:14:57 SQL> alter system flush buffer_cache; //清除data buffer
System altered.
06:15:09 SQL> select * from scott.t01;
select * from scott.t01 * ERROR at line 1: ORA-01116: error in opening database file 8 ORA-01110: data file 8: '/u01/app/oracle/oradata/prod/test02.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 32）查看datafile信息
06:16:59 SQL> select a.name,b.file#,b.name from v$tablespace a,v$datafile b 06:17:15 2 where a.ts#=b.ts#; 06:17:21 SQL> col name for a50 06:17:25 SQL> /NAME FILE# NAME
-------------------------------------------------- ---------- -------------------------------------------------- SYSTEM 1 /u01/app/oracle/oradata/prod/system01.dbf SYSAUX 3 /u01/app/oracle/oradata/prod/sysaux01.dbf USERS 2 /u01/app/oracle/oradata/prod/users01.dbf EXAMPLE 5 /u01/app/oracle/oradata/prod/example01.dbf TEST 8 /u01/app/oracle/oradata/prod/test02.dbf TEST 6 /u01/app/oracle/oradata/prod/test01.dbf UNDO_TBS 7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf INDEXES 4 /u01/app/oracle/oradata/prod/index01.dbf8 rows selected.
---------对数据文件脱机
06:17:39 SQL> alter database datafile 6,8 offline;Database altered.
3）转储datafile [oracle@work ~]$ cp /disk1/backup/prod/close_bak/test*.dbf /u01/app/oracle/oradata/prod/4）recover datafile 或 recover tablespace
06:19:39 SQL> recover datafile 6,8; Media recovery complete.告警日志信息：
ALTER DATABASE RECOVER datafile 6,8 Tue Aug 16 06:19:44 2011 Media Recovery Start Tue Aug 16 06:19:44 2011 Recovery of Online Redo Log: Thread 1 Group 2 Seq 45 Reading mem 0 Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo02.log Tue Aug 16 06:19:44 2011 Recovery of Online Redo Log: Thread 1 Group 3 Seq 46 Reading mem 0 Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log Tue Aug 16 06:19:44 2011 Media Recovery Complete (prod) Completed: ALTER DATABASE RECOVER datafile 6,8 Tue Aug 16 06:19:55 2011 alter database datafile 6,8 online Tue Aug 16 06:19:55 2011 Completed: alter database datafile 6,8 online 4）验证 06:19:45 SQL> alter database datafile 6,8 online;Database altered.
06:19:55 SQL> select * from scott.t01;
ID
---------- 1 2 3 4 5 6 77 rows selected.
06:19:59 SQL>
案例4：recover datafile
---------------新建的表空间，没有备份，datafile被删除 1)模拟环境 06:21:47 SQL> create tablespace cuug 06:21:55 2 datafile '/u01/app/oracle/oradata/prod/cuug01.dbf' size 10m;Tablespace created.
06:22:06 SQL> conn scott/tiger
Connected. 06:22:11 SQL> 06:22:22 SQL> create table t02 (id int) tablespace cuug;Table created.
06:22:25 SQL> insert into t02 values (1);
1 row created.
06:22:33 SQL> insert into t02 values (2);
1 row created.
06:22:34 SQL> insert into t02 values (3);
1 row created.
06:22:36 SQL> commit;
Commit complete.
06:22:38 SQL> select * from t02;
ID
---------- 1 2 3 06:22:44 SQL> conn /as sysdba Connected. 06:23:34 SQL> 06:23:34 SQL> shutdown abort ORACLE instance shut down. 06:23:38 SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [oracle@work ~]$ rm /u01/app/oracle/oradata/prod/cuug01.dbf [oracle@work ~]$2）启动 database
06:24:07 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 9 - see DBWR trace file ORA-01110: data file 9: '/u01/app/oracle/oradata/prod/cuug01.dbf' 06:24:13 SQL> select file# ,error from v$recover_file;FILE# ERROR
---------- ----------------------------------------------------------------- 9 FILE NOT FOUND 3）恢复 06:24:26 SQL> alter database datafile 9 offline;Database altered.
06:24:46 SQL> alter database open;
Database altered.
06:24:57 SQL>
-----没有备份，不能做restore
06:24:57 SQL> alter database create datafile '/u01/app/oracle/oradata/prod/cuug01.dbf';Database altered.
-------重建数据文件（通过os 删除，在controlfile文件仍然有datafile 记录），然后recover datafile
06:25:45 SQL> recover datafile 9; Media recovery complete.告警日志信息：
ALTER DATABASE RECOVER datafile 9 Tue Aug 16 06:26:05 2011 Media Recovery Start Tue Aug 16 06:26:05 2011 Recovery of Online Redo Log: Thread 1 Group 3 Seq 46 Reading mem 0 Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log Tue Aug 16 06:26:05 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 47 Reading mem 0 Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log Tue Aug 16 06:26:05 2011 Media Recovery Complete (prod) Completed: ALTER DATABASE RECOVER datafile 94）验证
06:26:09 SQL> alter database datafile 9 online;Database altered.
06:26:16 SQL> select * from scott.t02;
ID
---------- 1 2 306:26:21 SQL>
---------将数据文件恢复到新的位置
1、模拟环境
[oracle@work ~]$ sqlplus '/as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 22 23:41:12 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options23:41:13 SQL>
23:41:20 SQL> insert into scott.lxtb2 values (3);
1 row created.
23:41:25 SQL> insert into scott.lxtb2 values (4);
1 row created.
23:41:26 SQL> insert into scott.lxtb2 values (5);
1 row created.
23:41:28 SQL> commit;
Commit complete.
23:41:29 SQL> shutdown abort
ORACLE instance shut down.[oracle@work ~]$ rm /u01/app/oracle/oradata/test/lxtbs01.dbf
23:41:35 SQL> startup
ORACLE instance started.Total System Global Area 440401920 bytes
Fixed Size 1219904 bytes Variable Size 171967168 bytes Database Buffers 264241152 bytes Redo Buffers 2973696 bytes Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/u01/app/oracle/oradata/test/lxtbs01.dbf' 23:42:01 SQL> select file#,error from v$recover_file;FILE# ERROR
---------- ----------------------------------------------------------------- 6 FILE NOT FOUND 2、对数据文件进行恢复，并恢复到新的位置 23:43:50 SQL> alter database datafile 6 offline;Database altered.
[oracle@work ~]$ cp /disk1/backup/test/close_bak/lxtbs01.dbf /disk1/oradata/test/
23:43:53 SQL> alter database open;
Database altered.
23:44:01 SQL> alter database rename file '/u01/app/oracle/oradata/test/lxtbs01.dbf' to '/disk1/oradata/test/lxtbs01.dbf';
Database altered.
23:44:47 SQL> recover datafile 6;
Media recovery complete. 23:44:56 SQL> alter database datafile 6 online;Database altered.
23:45:03 SQL> select * from scott.lxtb2;
ID
---------- 1 2 3 4 523:45:10 SQL> col file_name for a50
23:45:15 SQL> select file_id,file_name,tablespace_name from dba_data_files;FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------ 5 /u01/app/oracle/oradata/test/lob_16k01.dbf LOB_16K 4 /u01/app/oracle/oradata/test/users01.dbf USERS 3 /u01/app/oracle/oradata/test/sysaux01.dbf SYSAUX 2 /u01/app/oracle/oradata/test/rtbs01.dbf RTBS 1 /u01/app/oracle/oradata/test/system01.dbf SYSTEM 6 /disk1/oradata/test/lxtbs01.dbf LXTBS1 9 /u01/app/oracle/oradata/test/undotbs1.dbf UNDOTBS1 14 /u01/app/oracle/oradata/test/indx01.dbf INDX8 rows selected.
3、将数据文件迁移到原来的位置
23:45:26 SQL> alter tablespace lxtbs1 offline;
Tablespace altered.
23:46:15 SQL> alter database rename file '/disk1/oradata/test/lxtbs01.dbf' to '/u01/app/oracle/oradata/test/lxtbs01.dbf' ;
Database altered.
23:47:08 SQL> alter tablespace lxtbs online;
alter tablespace lxtbs online * ERROR at line 1: ORA-00959: tablespace 'LXTBS' does not exist 23:47:15 SQL> alter tablespace lxtbs1 online;Tablespace altered.
23:47:19 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------ 5 /u01/app/oracle/oradata/test/lob_16k01.dbf LOB_16K 4 /u01/app/oracle/oradata/test/users01.dbf USERS 3 /u01/app/oracle/oradata/test/sysaux01.dbf SYSAUX 2 /u01/app/oracle/oradata/test/rtbs01.dbf RTBS 1 /u01/app/oracle/oradata/test/system01.dbf SYSTEM 6 /u01/app/oracle/oradata/test/lxtbs01.dbf LXTBS1 9 /u01/app/oracle/oradata/test/undotbs1.dbf UNDOTBS1 14 /u01/app/oracle/oradata/test/indx01.dbf INDX8 rows selected.
[oracle@work close_bak]$ rm /disk1/oradata/test/lxtbs01.dbf