oracle数据库正常关闭情况下当前日志损坏的恢复(转)

环境说明:
oracle 10.1.0.3.0
linux as4 2.6.9-34.EL
db创建在vg_oracle卷上



如果非当前日志损坏,clear日志组即可解决问题


以下为当前日志损坏的模拟情况,也包括了非当前日志的损坏
[root&＃64;test raw]# dd if&＃61;/dev/raw/raw5 of&＃61;/data/raw5
106496&＃43;0 records in
106496&＃43;0 records out
[root&＃64;test raw]# dd if&＃61;/dev/raw/raw6 of&＃61;/data/raw6
106496&＃43;0 records in
106496&＃43;0 records out
[root&＃64;test raw]# dd if&＃61;/dev/raw/raw4 of&＃61;/data/raw4
106496&＃43;0 records in
106496&＃43;0 records out

--------------------------------------------删除redo的lv
[root&＃64;test raw]# lvremove /dev/vg_oracle/lv_redo*
Do you really want to remove active logical volume "lv_redo1"? [y/n]: y
Logical volume "lv_redo1" successfully removed
Do you really want to remove active logical volume "lv_redo2"? [y/n]: y
Logical volume "lv_redo2" successfully removed
Do you really want to remove active logical volume "lv_redo3"? [y/n]: y
Logical volume "lv_redo3" successfully removed



SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 778212 bytes
Variable Size 70262812 bytes
Database Buffers 96468992 bytes
Redo Buffers 262144 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: &＃39;/dev/raw/raw4&＃39;

-----------------------------------------------------查看alert日志,
SQL> !
[oracle&＃64;test ~]$ vi /opt/oracle/app/admin/test/bdump/alert_test.log


Fri Jun 29 14:17:33 2007
Errors in file /opt/oracle/app/admin/test/bdump/test_lgwr_25394.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: &＃39;/dev/raw/raw4&＃39;
ORA-27041: unable to open file
Linux Error: 6: No such device or address
Additional information: 2
Fri Jun 29 14:17:33 2007
Errors in file /opt/oracle/app/admin/test/bdump/test_lgwr_25394.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: &＃39;/dev/raw/raw4&＃39;
ORA-27041: unable to open file
Linux Error: 6: No such device or address
Additional information: 2
Fri Jun 29 14:17:33 2007
ORA-313 signalled during: ALTER DATABASE OPEN...

------------------------------------------------------------clear日志文件
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00344: unable to re-create online log &＃39;/dev/raw/raw4&＃39;
ORA-27041: unable to open file
Linux Error: 6: No such device or address
Additional information: 1






------------------------------------以上提示找不到对应设备,因此创建对应设备
[root&＃64;test data]# lvcreate -L 50M -n lv_redo1 vg_oracle
lvcreate -L 50M -n lv_redo2 vg_oracle
lvcreate -L 50M -n lv_redo3 vg_oracle
Rounding up size to full physical extent 52.00 MB
Logical volume "lv_redo1" created
[root&＃64;test data]# lvcreate -L 50M -n lv_redo2 vg_oracle
Rounding up size to full physical extent 52.00 MB
Logical volume "lv_redo2" created
[root&＃64;test data]# lvcreate -L 50M -n lv_redo3 vg_oracle
Rounding up size to full physical extent 52.00 MB
Logical volume "lv_redo3" created
[root&＃64;test data]# raw /dev/raw/raw4 /dev/vg_oracle/lv_redo1
raw /dev/raw/raw5 /dev/vg_oracle/lv_redo2
raw /dev/raw/raw6 /dev/vg_oracle/lv_redo3
/dev/raw/raw4: bound to major 253, minor 3
[root&＃64;test data]# raw /dev/raw/raw5 /dev/vg_oracle/lv_redo2
/dev/raw/raw5: bound to major 253, minor 4
[root&＃64;test data]# raw /dev/raw/raw6 /dev/vg_oracle/lv_redo3
/dev/raw/raw6: bound to major 253, minor 5
[root&＃64;test data]# chown oracle:dba /dev/raw/raw4
[root&＃64;test data]# chown oracle:dba /dev/raw/raw5
[root&＃64;test data]# chown oracle:dba /dev/raw/raw6




SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance test (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: &＃39;/dev/raw/raw5&＃39;


---------------------------------------------------------group 2为当前日志

SQL> set linesize 1000
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 51380224 1 YES UNUSED 375809 29-JUN-07
2 1 3 51380224 1 NO CURRENT 379739 29-JUN-07
3 1 1 51380224 1 YES INACTIVE 366852 29-JUN-07

SQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-00316: log 2 of thread 1, type 0 in header is not log file
ORA-00312: online log 2 thread 1: &＃39;/dev/raw/raw5&＃39;


SQL> alter database clear logfile group 3;

Database altered.

SQL> recover database until cancel;
Media recovery complete.



SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 51380224 1 YES UNUSED 0
2 1 1 51380224 1 NO CURRENT 380833 29-JUN-07
3 1 0 51380224 1 YES UNUSED 0

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 51380224 1 YES INACTIVE 381270 29-JUN-07
2 1 4 51380224 1 NO CURRENT 381274 29-JUN-07
3 1 3 51380224 1 YES INACTIVE 381272 29-JUN-07


接下来对数据库做一个全备份[&＃64;more&＃64;]

来自 “ ITPUB博客 ” &＃xff0c;链接&＃xff1a;http://blog.itpub.net/9650775/viewspace-923234/&＃xff0c;如需转载&＃xff0c;请注明出处&＃xff0c;否则将追究法律责任。