—-查看当前日志组状态：1号日志组当前应用，未归档
SYS@PROD1>select group#,members,status from v$log;

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

2.做DML操作，生成日志
SYS@PROD1>create table scott.t tablespace users as select * from dba_objects where 1=2;

Table created.

SYS@PROD1>commit;

Commit complete.

SYS@PROD1>select count(*) from scott.t;

COUNT(*)
----------
86968

1号日志组当前应用，未归档
SYS@PROD1>select group#,members,archived,status from v$log;

GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO CURRENT
2 2 YES INACTIVE
3 2 YES INACTIVE

3.删除1号日志组（即current状态的日志文件）
SYS@PROD1>col member for a50;
SYS@PROD1>select group#,member from v$logfile order by 1;

GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD1/redo01.log
1 /u01/app/oracle/oradata/PROD1/redo01_a.log
2 /u01/app/oracle/oradata/PROD1/redo02_a.log
2 /u01/app/oracle/oradata/PROD1/redo02.log
3 /u01/app/oracle/oradata/PROD1/redo03_a.log
3 /u01/app/oracle/oradata/PROD1/redo03.log

6 rows selected.

SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo01*

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo*
/u01/app/oracle/oradata/PROD1/redo02_a.log /u01/app/oracle/oradata/PROD1/redo03_a.log
/u01/app/oracle/oradata/PROD1/redo02.log /u01/app/oracle/oradata/PROD1/redo03.log

4.查看日志组状态，手工切换一下日志，1号日志组为ACTIVE且未归档（那是一定的），此时数据库是可以正常对外提供服务的，但是，当再次切换到1号日志组的时候，将由于归档问题而hang住
——查看日志组，第一组状态为无归档，目前正在使用

手工切换日志
SYS@PROD1>alter system switch logfile;

System altered.

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

GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO ACTIVE
2 2 NO CURRENT
3 2 YES INACTIVE

再次查看日志组状态，发现第一组为活跃，但没有归档（正常，因为第一组日志物理层已经删除了）
SYS@PROD1>select group#,members,archived,status from v$log;

GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO ACTIVE
2 2 NO CURRENT
3 2 YES INACTIVE

再次切换当前使用的日志组到1号日志组：
SYS@PROD1>alter system switch logfile;

System altered.

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

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

SYS@PROD1>alter system switch logfile;

当再次切换到3号日志组的时候，将由于归档问题而hang住。

5.尝试在命令行drop掉1号日志组，显然是不行的，提示的很清楚，1号日志组需要归档先（另开一个窗口操作）
[oracle@host01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 17 11:35:34 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@PROD1>alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance PROD1 (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD1/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD1/redo01_a.log'

6.解决办法1

删除日志组的错误是由于归档所间接造成的，我们可以将数据库改为非归档模式，然后重建重做日志，如下：
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@PROD1>startup mount;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.

SYS@PROD1>alter database noarchivelog;

Database altered.

——删除第一组日志
SYS@PROD1>alter database drop logfile group 1;

Database altered.

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

GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
3 2 NO CURRENT
2 2 NO INACTIVE

——再次增加第一组日志
SYS@PROD1>alter database add logfile group 1 ('/u01/app/oracle/oradata/PROD1/redo01.log','/u01/app/oracle/oradata/PROD1/redo01_a.log') size 50m;

Database altered.

——查看状态及验证
SYS@PROD1>select group#,members,archived,status from v$log;

GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 YES UNUSED
3 2 NO CURRENT
2 2 NO INACTIVE

SYS@PROD1>col member for a50;
SYS@PROD1>select group#,member from v$logfile order by 1;

GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD1/redo01.log
1 /u01/app/oracle/oradata/PROD1/redo01_a.log
2 /u01/app/oracle/oradata/PROD1/redo02_a.log
2 /u01/app/oracle/oradata/PROD1/redo02.log
3 /u01/app/oracle/oradata/PROD1/redo03_a.log
3 /u01/app/oracle/oradata/PROD1/redo03.log

6 rows selected.

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo*
/u01/app/oracle/oradata/PROD1/redo01_a.log /u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo01.log /u01/app/oracle/oradata/PROD1/redo03_a.log
/u01/app/oracle/oradata/PROD1/redo02_a.log /u01/app/oracle/oradata/PROD1/redo03.log

SYS@PROD1>alter database open;

Database altered.

ITPub博客

【备份恢复】恢复丢失的current状态的日志文件方法(一) 改为非归档

最新文章

ITPub博客

【备份恢复】恢复丢失的current状态的日志文件 方法(一) 改为非归档

最新文章

【备份恢复】恢复丢失的current状态的日志文件方法(一) 改为非归档