Oracle数据恢复情景2-模拟redo log丢失(1)
相关链接：
Oracle恢复情景1-模拟数据文件损坏或丢失
http://www.2cto.com/database/201206/137602.html
oracle redo log(当前或非当前日志)损坏之后的db恢复(1)
(1)
数据库没有异常关闭
DB Version:Release 11.2.0.2.0
1.查看当前日志情况
01 www.2cto.com
SQL> col GROUP# format 9;
02
SQL> col TYPE format a8;
03
SQL> col MEMBER format a40;
04
SQL> col STATUS format a8;
05
SQL> select t1.GROUP#,t1.MEMBER,t1.TYPE,t2.STATUS,t2.bytes from v$logfile t1,v$log t2 where t1.GROUP#=t2.GROUP#;
06
07
GROUP# MEMBER TYPE STATUS BYTES
08
------ ---------------------------------------- -------- -------- ----------
09
3 /opt/oracle/oradata/oraddim/redo03.log ONLINE INACTIVE 52428800
10
2 /opt/oracle/oradata/oraddim/redo02.log ONLINE CURRENT 52428800
11
1 /opt/oracle/oradata/oraddim/redo01.log ONLINE INACTIVE 52428800
www.2cto.com
2.模拟数据库活动--删除数据
1
$ cat del.sql
2
delete from DBA_temp1 where rownum<100;
3
commit;
1
SQL> @del.sql
2
3
SQL> select count(*) from DBA_temp1;
4
COUNT(*)
5
----------
6
72683
3.模拟删除current redo log
1 www.2cto.com
$ mv redo03.log ..
01
SQL> @del.sql
02
99 rows deleted.
03
Commit complete.
04
05
SQL> select count(*) from uas.DBA_temp1;
06
COUNT(*)
07
----------
08
72584
09
10
SQL>
4.模拟切换日志
1
SQL> alter system switch logfile;
2
System altered.
3
4
SQL> alter system switch logfile;
5 www.2cto.com
System altered.
6
7
SQL> alter system switch logfile; --切换一个循环(再次到redo03)后，hang住了
5.查看alter.log
1
Linux-x86_64 Error: 2: No such file or directory
2
Additional information: 3
3
Wed Jul 04 18:10:22 2012
4
Errors in file /opt/oracle/diag/rdbms/oraddim/oraddim/trace/oraddim_arc0_10985.trc:
5
ORA-00313: open failed for members of log group 2 of thread 1
6
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/oraddim/redo02.log'
7
ORA-27037: unable to obtain file status
6.取消日志切换
1
SQL> alter system switch logfile;
2
alter system switch logfile
3
*
4
ERROR at line 1:
5
ORA-01013: user requested cancel of current operation
6 www.2cto.com
7
SQL>
7.清除未归档的日志组3
01
SQL> alter database clear unarchived logfile group 3;
02
Database altered.
03
04
SQL> select t1.GROUP#,t1.MEMBER,t1.TYPE,t2.STATUS,t2.bytes from v$logfile t1,v$log t2 where t1.GROUP#=t2.GROUP#;
05
GROUP# MEMBER TYPE STATUS BYTES
06
------ ---------------------------------------- -------- -------- ----------
07
3 /opt/oracle/oradata/oraddim/redo03.log ONLINE UNUSED 52428800
08
2 /opt/oracle/oradata/oraddim/redo02.log ONLINE CURRENT 52428800
09
1 /opt/oracle/oradata/oraddim/redo01.log ONLINE INACTIVE 52428800
10
11
SQL> select count(*) from uas.DBA_temp1;
12
COUNT(*)
13
----------
14 www.2cto.com
72584
8.切换日志
01
SQL> alter system switch logfile;
02
System altered.
03
04
SQL> alter system switch logfile;
05
System altered.
06
07
SQL> select t1.GROUP#,t1.MEMBER,t1.TYP