当同组的redolog文件,丢失部分成员时,可以使用clear logfile语句进行重建
select GROUP#,MEMBERS,STATUS from v$log;
col member for a45
select GROUP#,MEMBER from v$logfile order by 1,2;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 INACTIVE
3 2 CURRENT
4 2 INACTIVE
5 2 INACTIVE
GROUP# MEMBER
---------- ---------------------------------------------
1 /u01/oradata/PROD/Disk1/redo11.log
1 /u01/oradata/PROD/Disk2/redo12.log
2 /u01/oradata/PROD/Disk1/redo21.log
2 /u01/oradata/PROD/Disk2/redo22.log
3 /u01/oradata/PROD/Disk1/redo31.log
3 /u01/oradata/PROD/Disk2/redo32.log
4 /u01/oradata/PROD/Disk1/redo41.log
4 /u01/oradata/PROD/Disk2/redo42.log
5 /u01/oradata/PROD/Disk1/redo51.log
5 /u01/oradata/PROD/Disk2/redo52.log
删除Disk1下的所有redolog
[oracle@dbsv Disk1]$ rm -rf redo*
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance PROD (thread 1)
ORA-00312: online log 3 thread 1: '/u01/oradata/PROD/Disk1/redo31.log'
ORA-00312: online log 3 thread 1: '/u01/oradata/PROD/Disk2/redo32.log'
SQL> alter database clear logfile group 4;
Database altered.
SQL> alter database clear logfile group 5;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,MEMBERS,STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 CURRENT
2 2 UNUSED
3 2 INACTIVE
4 2 UNUSED
5 2 UNUSED
SQL> alter database clear logfile group 3;
Database altered.
当前redolog组,需要切成非当前组,再clear
[oracle@dbsv Disk1]$ ll
total 1681348
-rw-r----- 1 oracle oinstall 6078464 Jul 29 12:36 control01.ctl
-rw-r----- 1 oracle oinstall 419438592 Jul 29 12:35 example01.dbf
-rw-r----- 1 oracle oinstall 28516352 Jul 29 12:35 example201.dbf
-rw-r----- 1 oracle oinstall 41951232 Jul 29 12:35 indx01.dbf
-rw-r----- 1 oracle oinstall 50339840 Jul 29 12:35 oltp01.dbf
-rw-r----- 1 oracle oinstall 104858112 Jul 29 12:36 redo11.log
-rw-r----- 1 oracle oinstall 104858112 Jul 29 12:35 redo21.log
-rw-r----- 1 oracle oinstall 104858112 Jul 29 12:36 redo31.log
-rw-r----- 1 oracle oinstall 104858112 Jul 29 12:35 redo41.log
-rw-r----- 1 oracle oinstall 104858112 Jul 29 12:35 redo51.log
-rw-r----- 1 oracle oinstall 94380032 Jul 29 12:35 registration01.dbf
-rw-r----- 1 oracle oinstall 125837312 Jul 29 12:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 314580992 Jul 29 12:35 system01.dbf
-rw-r----- 1 oracle oinstall 52436992 Jul 25 15:10 temp01.dbf
-rw-r----- 1 oracle oinstall 52436992 Jul 25 14:43 temp02.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 29 12:35 tools01.dbf
-rw-r----- 1 oracle oinstall 52436992 Jul 29 12:35 undotbs01.dbf
-rw-r----- 1 oracle oinstall 50339840 Jul 29 12:35 users01.dbf
删掉的redolog文件又回来了。