Oracle数据恢复情景2-模拟redo log丢失(1)-优快云博客

本文详细介绍了Oracle数据库在Redo Log Group损坏后进行恢复的步骤，包括检查当前日志、模拟数据库活动、删除当前日志、切换日志、查看alter.log、取消日志切换、清除未归档的日志组以及最终切换日志恢复数据库。

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

oracle redo log(当前或非当前日志)损坏之后的db恢复(1)

(1)数据库没有异常关闭

DB Version:Release 11.2.0.2.0

1.查看当前日志情况

SQL> col GROUP# format 9;
SQL> col TYPE format a8;
SQL> col MEMBER format a40;
SQL> col STATUS format a8;
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/oraddim/redo03.log   ONLINE   INACTIVE   52428800
     2 /opt/oracle/oradata/oraddim/redo02.log   ONLINE   CURRENT    52428800
     1 /opt/oracle/oradata/oraddim/redo01.log   ONLINE   INACTIVE   52428800

2.模拟数据库活动--删除数据

$ cat del.sql 
delete from DBA_temp1 where rownum<100;
commit;

SQL> @del.sql

SQL> select count(*) from DBA_temp1;
  COUNT(*)
----------
     72683

3.模拟删除current redo log

$ mv redo03.log ..

SQL> @del.sql
99 rows deleted.
Commit complete.

SQL> select count(*) from uas.DBA_temp1;
  COUNT(*)
----------
     72584

SQL>

4.模拟切换日志

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile; --切换一个循环(再次到redo03)后，hang住了

5.查看alter.log

Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Jul 04 18:10:22 2012
Errors in file /opt/oracle/diag/rdbms/oraddim/oraddim/trace/oraddim_arc0_10985.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/oraddim/redo03.log'
ORA-27037: unable to obtain file status

6.取消日志切换

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

SQL>

7.清除未归档的日志组3

SQL> alter database clear unarchived logfile group 3; 
Database 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/oraddim/redo03.log   ONLINE   UNUSED     52428800
     2 /opt/oracle/oradata/oraddim/redo02.log   ONLINE   CURRENT    52428800
     1 /opt/oracle/oradata/oraddim/redo01.log   ONLINE   INACTIVE   52428800

SQL> select count(*) from uas.DBA_temp1;
  COUNT(*)
----------
     72584

8.切换日志

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/oraddim/redo03.log   ONLINE   CURRENT    52428800
     2 /opt/oracle/oradata/oraddim/redo02.log   ONLINE   ACTIVE     52428800
     1 /opt/oracle/oradata/oraddim/redo01.log   ONLINE   INACTIVE   52428800

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