第五章：手工完全恢复(归档模式）
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 做recover
9、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
3
05: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-11
05: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-11
05: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
9
9 rows selected.
05:47:38 SQL>
2）模拟介质失败
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/*.dbf
3）启动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 1188700
7 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 1188419
7 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 DEFAULT
6）验证：
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
8
8 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 1208722
7 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 1208722
7 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
exit
06: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 test
5）验证：
06:10:36 SQL> alter database datafile 6,8 online;
Database altered.
06:10:46 SQL> select * from scott.t01;
ID
----------
1
2
3
06: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
6
6 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: 3
2）查看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.dbf
8 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
7
7 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 9
4）验证
06:26:09 SQL> alter database datafile 9 online;
Database altered.
06:26:16 SQL> select * from scott.t02;
ID
----------
1
2
3
06: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 options
23: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
5
23: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 INDX
8 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 INDX
8 rows selected.
[oracle@work close_bak]$ rm /disk1/oradata/test/lxtbs01.dbf