Oracle丢失重做日志的几种场景恢复
实验环境：RHEL6.4 + Oracle 11.2.0.4
二、丢失重做日志组
环境准备
SQL> set linesize 160
SQL> col member for a80
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
3 ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_3_c3g92rg3_.log NO
3 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_3_c3g92rmf_.log YES
2 ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log NO
2 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_2_c3g92qoq_.log YES
1 ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_1_c3g92prr_.log NO
1 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_1_c3g92px4_.log YES
6 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------
1 1 4 52428800 512 2 NO CURRENT 1917689 03-NOV-15 2.8147E+14
2 1 2 52428800 512 2 YES INACTIVE 1861002 02-NOV-15 1888519 03-NOV-15
3 1 3 52428800 512 2 YES INACTIVE 1888519 03-NOV-15 1917689 03-NOV-15
一、丢失重做日志组中成员
1.1 故障模拟
模拟第2组日志组一个成员丢失。
```
SQL> startup force
ORACLE instance started.
Total System Global Area 304807936 bytes
Fixed Size 2252744 bytes
Variable Size 205520952 bytes
Database Buffers 92274688 bytes
Redo Buffers 4759552 bytes
Database mounted.
Database opened.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
3 ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_3_c3g92rg3_.log NO
3 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_3_c3g92rmf_.log YES
2 INVALID ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log NO
2 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_2_c3g92qoq_.log YES
1 ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_1_c3g92prr_.log NO
1 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_1_c3g92px4_.log YES
6 rows selected.
<h2 id="1.2">1.2 处理方法</h2>
删除无效成员，然后在组中添加新成员。
SQL> alter database drop logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log';
SQL> alter database add logfile member '/u01/oradata/ORADB/onlinelog/redo02a.log' to group 2;
<h2 id="1.3">1.3 实际处理过程</h2>
SQL> alter database drop logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log';
alter database drop logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_2_c3g92qoq_.log'
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
1 1 7 52428800 512 2 YES INACTIVE 1920749 03-NOV-15 1940772 03-NOV-15
2 1 8 52428800 512 2 NO CURRENT 1940772 03-NOV-15 2.8147E+14
3 1 6 52428800 512 2 YES INACTIVE 1920739 03-NOV-15 1920749 03-NOV-15
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
1 1 7 52428800 512 2 YES INACTIVE 1920749 03-NOV-15 1940772 03-NOV-15
2 1 8 52428800 512 2 YES ACTIVE 1940772 03-NOV-15 1941407 03-NOV-15
3 1 9 52428800 512 2 NO CURRENT 1941407 03-NOV-15 2.8147E+14
SQL> alter database drop logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log';
Database altered.
SQL> alter database add logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log' to group 2;
alter database add logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log' to group 2
*
ERROR at line 1:
ORA-01276: Cannot add file /u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log. File has an Oracle Managed Files file name.
SQL> alter database add logfile member '/u01/oradata/ORADB/onlinelog/redo02a.log' to group 2;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
3 ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_3_c3g92rg3_.log NO
3 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_3_c3g92rmf_.log YES
2 INVALID ONLINE /u01/oradata/ORADB/onlinelog/redo02a.log NO
2 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_2_c3g92qoq_.log YES
1 ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_1_c3g92prr_.log NO
1 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_1_c3g92px4_.log YES
6 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
1 1 7 52428800 512 2 YES INACTIVE 1920749 03-NOV-15 1940772 03-NOV-15
2 1 8 52428800 512 2 YES ACTIVE 1940772 03-NOV-15 1941407 03-NOV-15
3 1 9 52428800 512 2 NO CURRENT 1941407 03-NOV-15 2.8147E+14
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
3 ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_3_c3g92rg3_.log NO
3 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_3_c3g92rmf_.log YES
2 ONLINE /u01/oradata/ORADB/onlinelog/redo02a.log NO
2 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_2_c3g92qoq_.log YES
1 ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_1_c3g92prr_.log NO
1 ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_1_c3g92px4_.log YES
6 rows selected.
<h1 id="2">二、丢失重做日志组</h1>
<h2 id="2.1"> 2.1 丢失INACTIVE重做日志组</h2>
### 2.1.1 清除归档的INACTIVE重做日志组 ###
SQL> alter database clear logfile group 2;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
1 1 4 52428800 512 2 YES INACTIVE 1962650 03-NOV-15 1962653 03-NOV-15
2 1 0 52428800 512 3 YES UNUSED 1962653 03-NOV-15 1962685 03-NOV-15
3 1 6 52428800 512 2 NO CURRENT 1962685 03-NOV-15 2.8147E+14
可以看到，clear后的日志组状态为UNUSED。
### 2.1.2 清除未归档的INACTIVE重做日志组 ###
清除未归档的INACTIVE重做日志组，不会丢失任何已提交事物，但清除后必须完全备份，从而确保可以执行完整恢复。
SQL> alter database clear logfile unarchived group 2;
需要考虑脱机文件是否需要已经清除的日志文件组才能重新联机。如果需要，那么只能删除包含脱机文件的表空间。
SQL> alter database clear logfile unarchived group 2 unrecoverable datafile;
控制文件备份到跟踪文件目录中,默认存放是udump目录下
SQL> alter database backup controlfile to trace;
<h2 id="2.2">2.2 丢失ACTIVE重做日志组</h2>
这个时候尝试生成检查点，
SQL> alter system checkpoint;
### 2.2.1 第一种情况：命令成功执行 ###
命令成功执行，会将所有已经提交的更改写入磁盘的数据文件中。就跟INACTIVE重做日志组处理流程一致了。
### 2.2.2 第二种情况：命令执行出现故障 ###
命令执行出现故障，就只能执行不完整恢复。
<h2 id="2.3">2.3 丢失CURRENT重做日志组</h2>
数据库mount模式下执行不完整恢复，最后使用RESETLOGS打开数据库。
SQL> alter database open resetlogs;
<h1 id="3">Reference</h1>
- OCP 认证考试指南 (1Z0-053)[M]. 清华大学出版社, 2010.最新文章
- Google C++单元测试框架GoogleTest---Extending Google Test by Handling Test Events
- 机器学习实战knn
- c.BIO连接器与NIO连接器的对比
- chrome拓展开发实战
- 如何在eclipse中使用XYLayout布局？在此介绍如何把XYLayout导入到eclipse .
- Oracle ->> 随机函数
- uva 10330 最大流
- C++与Lua交互（五）
- 【git学习五】git基础之git分支
- 安装Team Foundation Server 2012过程截图
- CentOS系统通过PXE实现批量无人值守安装
- Callback 与 Promise 间的桥梁 —— promisify
- VS2010灵活运用快捷操作功能（总结）
- 深度爬取之rules
- 转：window与linux互相拷贝文件
- 不为人知的网络编程(八)：从数据传输层深度解密HTTP
- 安装Blend+SketchFlow Preview for Visual Studio 2012出现错误
- zabbix3.4.7利用Windows性能监视器监控各项资源指标
- UVa 11627 - Slalom 二分. oj错误题目 难度: 0
- BASH 基本语法