Oracle数据恢复情景2-模拟redo log丢失(2)
相关链接：
Oracle恢复情景1-模拟数据文件损坏或丢失
http://www.2cto.com/database/201206/137602.html；
Oracle数据恢复情景2-模拟redo log丢失(1)
http://www.2cto.com/database/201207/138884.html
oracle redo log(当前或非当前日志)损坏之后的db恢复(2)
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
GROUP# MEMBER TYPE STATUS BYTES
07
------ ---------------------------------------- -------- -------- ----------
08
3 /opt/oracle/oradata/ora/redo03.log ONLINE CURRENT 52428800
09
2 /opt/oracle/oradata/ora/redo02.log ONLINE INACTIVE 52428800
10
1 /opt/oracle/oradata/ora/redo01.log ONLINE INACTIVE 52428800
1
$ cat del.sql
2
delete from DBA_temp1 where rownum<100;
3 www.2cto.com
commit;
1
SQL> @del.sql
2
SQL> select count(1) from uas.dba_temp1;
3
4
COUNT(1)
5
----------
6
72188
3.删除当前redo log
1
$ mv redo03.log ..
4.在删除当前redo log以后，继续删除数据
1
SQL> @del.sql
2
SQL> select count(1) from uas.dba_temp1;
3
4
COUNT(1)
5
----------
6
71297
7
8
SQL>
5.切换日志
01
SQL> alter system switch logfile;
02 www.2cto.com
03
System altered.
04
05
SQL> alter system switch logfile;
06
07
System altered.
08
09
SQL> alter system switch logfile;
10
hanging...
6.DB粗暴关闭
1
SQL> shutdown abort
2
ORACLE instance shut down.
7.启动DB
01
SQL> startup
02
ORACLE instance started.
03 www.2cto.com
04
Total System Global Area 6647513088 bytes
05
Fixed Size 2239032 bytes
06
Variable Size 5049943496 bytes
07
Database Buffers 1577058304 bytes
08
Redo Buffers 18272256 bytes
09
Database mounted.
10
ORA-03113: end-of-file on communication channel
11
Process ID: 24253
12
Session ID: 125 Serial number: 5
13
14
SQL>
15
16
SQL> alter database clear unarchived logfile group 3;
17
alter database clear unarchived logfile group 3
18 www.2cto.com
*
19
ERROR at line 1:
20
ORA-01034: ORACLE not available
21
Process ID: 0
22
Session ID: 0 Serial number: 0
23
24
SQL>
8.此时数据库已经不能正常工作，需进行修复
01
SQL> alter database mount;
02
03
Database altered.
04
05
SQL> alter database clear unarchived logfile group 3;
06
07
Database altered.
08
09
SQL> alter database open;
10 www.2cto.com
11
Database altered.
12
13
SQL>
14
15
SQL> select t1.GROUP#,t1.MEMBER,t1.TYPE,t2.STATUS,t2.bytes from v$logfile t1,v$log t2 where t1.GROUP#=t2.GROUP#;
16
17
GROUP# MEMBER TYPE STATUS BYTES
18
------ ---------------------------------------- -------- -------- ----------
19
3 /opt/oracle/oradata/ora/redo03.log ONLINE CURRENT 52428800
20
2 /opt/oracle/oradata/ora/redo02.log ONLINE INACTIVE 52428800
21