1.查询当前的日志组及状态:
10:24:22 SYS@ORA11GR2>select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
3 /u01/app/oracle/disk/redo03b.log
2 /u01/app/oracle/disk/redo02b.log
1 /u01/app/oracle/disk/redo01b.log
6 rows selected.
10:24:37 SYS@ORA11GR2>select group#,members,bytes/1024/1024 M,status from v$log;
GROUP# MEMBERS M STATUS
---------- ---------- ---------- ----------------
1 2 50 INACTIVE
2 2 50 INACTIVE
3 2 50 CURRENT
2.关库并删除一个非当前日志组:
10:25:05 SYS@ORA11GR2>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@host03 ~]$ rm /u01/app/oracle/disk/redo01b.log
[oracle@host03 ~]$ rm /u01/app/oracle/oradata/ORA11GR2/redo01.log
3.起库时报错:
10:28:00 SYS@ORA11GR2>startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 289406976 bytes
Redo Buffers 2392064 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4200
Session ID: 1 Serial number: 5
查看alert日志(提示找不到redo01b.log和redo01.log):
Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_4200.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/disk/redo01b.log'
4.将数据库启动到mount状态:
查看报错日志组是否为当前日志组:
10:36:54 SYS@ORA11GR2>select group#,members,bytes/1024/1024 M,status from v$log;
GROUP# MEMBERS M STATUS
---------- ---------- ---------- ----------------
1 2 50 INACTIVE[是非当前日志组]
3 2 50 CURRENT
2 2 50 INACTIVE
5.clear 日志组:
10:37:05 SYS@ORA11GR2>alter database clear logfile group 1;
Database altered.
6.起库:
10:39:37 SYS@ORA11GR2>alter database open;
Database altered.