Oracle数据恢复情景2-模拟redo log丢失(3)

最新推荐文章于 2019-02-14 15:40:21 发布

weixin_33751566

最新推荐文章于 2019-02-14 15:40:21 发布

阅读量155

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/liuliufa/blog/65070

本文模拟了Oracle数据库中所有在线重做日志被删除的情况，并详细记录了这一过程及后续的日志切换、数据库恢复步骤。通过清除未归档的日志文件并进行全局检查点操作，最终成功恢复数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

在Oracle数据恢复情景2的前两篇中，我模拟的情况都是删除current redo log,基本上都是删除部分的redo logs，如果将DB中的所有redo logs全部删除呢？

DB version: Release 11.2.0.2.0

1.查看online redo logs

SQL> select t1.GROUP#,t1.MEMBER,t1.TYPE,t2.STATUS,t2.bytes from v$logfile t1,v$log t2 where t1.GROUP#=t2.GROUP#;

GROUP# MEMBER                                   TYPE     STATUS        BYTES
------ ---------------------------------------- -------- -------- ----------
     3 /opt/oracle/oradata/ora/redo03.log   ONLINE   CURRENT    52428800
     2 /opt/oracle/oradata/ora/redo02.log   ONLINE   INACTIVE   52428800
     1 /opt/oracle/oradata/ora/redo01.log   ONLINE   INACTIVE   52428800

2.删除数据

SQL> @del.sql

SQL> select count(1) from UAS.DBA_TEMP1;

  COUNT(1)
----------
     71000

3.移除所有redo logs

$ mv redo0* ..

4.试着日志切换，竟然也执行成功了

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;   --hanging...
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

5.恢复

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance ora (thread 1)

ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ora/redo02.log'

此错误的解决方法是对DB做一下checkpoint：

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select t1.GROUP#,t1.MEMBER,t1.TYPE,t2.STATUS,t2.bytes from v$logfile t1,v$log t2 where t1.GROUP#=t2.GROUP#;

GROUP# MEMBER                                   TYPE     STATUS        BYTES
------ ---------------------------------------- -------- -------- ----------
     3 /opt/oracle/oradata/ora/redo03.log   ONLINE   CURRENT    52428800
     2 /opt/oracle/oradata/ora/redo02.log   ONLINE   INACTIVE   52428800
     1 /opt/oracle/oradata/ora/redo01.log   ONLINE   INACTIVE   52428800

6.检查数据

SQL> select count(1) from UAS.DBA_TEMP1;

  COUNT(1)
----------
     71000

转载于:https://my.oschina.net/liuliufa/blog/65070