SYS@rhel6> select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 INACTIVE
3 1 CURRENT

1，丢失inactive日志文件，数据库没有重启
查看日志组状态
SYS@rhel6> select group#,member from v$logfile;

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/rhel6/redo01.log

3
/u01/app/oracle/oradata/rhel6/redo03.log

2
/u01/app/oracle/oradata/rhel6/redo02.log

SYS@rhel6> !ls /u01/app/oracle/oradata/rhel6/redo*
/u01/app/oracle/oradata/rhel6/redo01.log
/u01/app/oracle/oradata/rhel6/redo02.log
/u01/app/oracle/oradata/rhel6/redo03.log

SYS@rhel6> alter database drop logfile group 1;

Database altered.

SYS@rhel6> select group#,status,bytes/1024/1024 as m from v$log;

GROUP# STATUS M
---------- ---------------- ----------
2 INACTIVE 50
3 CURRENT 50

SYS@rhel6> alter database add logfile group 1('/u01/app/oracle/oradata/rhel6/redo01.log') size 50m;（既然删了就加一个吧）

Database altered.

SYS@rhel6> select group#,status,bytes/1024/1024 as m from v$log;

GROUP# STATUS M
---------- ---------------- ----------
1 UNUSED 50
2 INACTIVE 50
3 CURRENT 50

SYS@rhel6> alter system switch logfile;

System altered.

SYS@rhel6> select group#,status,bytes/1024/1024 as m from v$log;

GROUP# STATUS M
---------- ---------------- ----------
1 CURRENT 50
2 INACTIVE 50
3 ACTIVE 50

2，删除incative 日志组重启数据库
SYS@rhel6> select group#,status,members from v$log;

GROUP# STATUS MEMBERS
---------- ---------------- ----------
1 CURRENT 1
2 INACTIVE 1
3 ACTIVE 1

SYS@rhel6> !ls /u01/app/oracle/oradata/rhel6/redo*
/u01/app/oracle/oradata/rhel6/redo01.log
/u01/app/oracle/oradata/rhel6/redo02.log
/u01/app/oracle/oradata/rhel6/redo03.log

SYS@rhel6> select group#,status,members from v$log;

GROUP# STATUS MEMBERS
---------- ---------------- ----------
1 CURRENT 1
2 INACTIVE 1
3 ACTIVE 1

SYS@rhel6> !rm /u01/app/oracle/oradata/rhel6/redo02.log

SYS@rhel6> !ls /u01/app/oracle/oradata/rhel6/redo*
/u01/app/oracle/oradata/rhel6/redo01.log
/u01/app/oracle/oradata/rhel6/redo03.log

重启数据库
SYS@rhel6> startup
ORACLE instance started.

Total System Global Area 830930944 bytes
Fixed Size 2232920 bytes
Variable Size 633343400 bytes
Database Buffers 188743680 bytes
Redo Buffers 6610944 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 21440
Session ID: 191 Serial number: 3

查看alert日志
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/rhel6/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

对2号日志组进行clear操作open数据库
SYS@rhel6> startup mount;（其实数据库是shutdown状态的）
ORACLE instance started.

Total System Global Area 830930944 bytes
Fixed Size 2232920 bytes
Variable Size 633343400 bytes
Database Buffers 188743680 bytes
Redo Buffers 6610944 bytes
Database mounted.

SYS@rhel6> alter database clear logfile group 2;

Database altered.

SYS@rhel6> alter database clear logfile group 2;

Database altered.

SYS@rhel6> alter database open;

Database altered.

SYS@rhel6> select group#,status,members from v$log;

GROUP# STATUS MEMBERS
---------- ---------------- ----------
1 CURRENT 1
2 UNUSED 1
3 INACTIVE 1

ITPub博客

恢复重做日志incative

最新文章