丢失current状态日志文件

查看当前日志组状态，数据库处于归档模式

1. SQL> select group#,members,archived,status from v$log;
   
2. 
   
3.     GROUP# MEMBERS ARC STATUS
   
4. ---------- ---------- --- ----------------
   
5.          1 1 NO CURRENT
   
6.          2 1 YES UNUSED
   
7.          3 1 YES INACTIVE
   
8. 
   
9. SQL> archive log list
   
10. Database log mode Archive Mode
    
11. Automatic archival Enabled
    
12. Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
    
13. Oldest online log sequence 12
    
14. Next log sequence to archive 15
    
15. Current log sequence 15
    
16. SQL>

做DML操作，生成日志

1. SQL> create table scott.t tablespace users as select * from dba_objects where 1=2;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> insert into scott.t select * from dba_objects;
   
6. 
   
7. 86966 rows created.
   
8. 
   
9. SQL> commit;
   
10. 
    
11. Commit complete.
    
12. 
    
13. SQL> select count(*) from scott.t;
    
14. 
    
15.   COUNT(*)
    
16. ----------
    
17.      86966
    
18.      
    
19. SQL> select group#,members,archived,status from v$log;
    
20. 
    
21.     GROUP# MEMBERS ARC STATUS
    
22. ---------- ---------- --- ----------------
    
23.          1 1 NO CURRENT
    
24.          2 1 YES UNUSED
    
25.          3 1 YES INACTIVE

删除一日志组（处于current状态）

1. SQL> !ls /u01/app/oracle/oradata/HU/*.log
   
2. /u01/app/oracle/oradata/HU/redo01.log /u01/app/oracle/oradata/HU/redo03.log
   
3. /u01/app/oracle/oradata/HU/redo02.log
   
4. 
   
5. SQL> !rm /u01/app/oracle/oradata/HU/redo01.log
   
6. 
   
7. SQL> !ls /u01/app/oracle/oradata/HU/*.log
   
8. /u01/app/oracle/oradata/HU/redo02.log /u01/app/oracle/oradata/HU/redo03.log
   
9. 
   
10. SQL> 

查看日志组状态 ，然后手工切换下

1. SQL> select group#,members,archived,status from v$log;
   
2. 
   
3.     GROUP# MEMBERS ARC STATUS
   
4. ---------- ---------- --- ----------------
   
5.          1 1 NO CURRENT
   
6.          2 1 YES UNUSED
   
7.          3 1 YES INACTIVE
   
8. 
   
9. SQL> alter system switch logfile;
   
10. 
    
11. System altered.
    
12. 
    
13. SQL> select group#,members,archived,status from v$log;
    
14. 
    
15.     GROUP# MEMBERS ARC STATUS
    
16. ---------- ---------- --- ----------------
    
17.          1 1 NO ACTIVE
    
18.          2 1 NO CURRENT
    
19.          3 1 YES INACTIVE

尝试在命令行 drop 掉 1号日志组 ，显然是不行的，提示的很清楚，  1号日志组需要归档先

1. SQL> alter database drop logfile group 1;
   
2. alter database drop logfile group 1
   
3. *
   
4. ERROR at line 1:
   
5. ORA-01624: log 1 needed for crash recovery of instance HU (thread 1)
   
6. ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/HU/redo01.log'

解决方法：
方法一：该归档

1. SQL> shutdown immediate
   
2. Database closed.
   
3. Database dismounted.
   
4. ORACLE instance shut down.
   
5. SQL> startup mount
   
6. ORACLE instance started.
   
7. 
   
8. Total System Global Area 830930944 bytes
   
9. Fixed Size 2257800 bytes
   
10. Variable Size 536874104 bytes
    
11. Database Buffers 289406976 bytes
    
12. Redo Buffers 2392064 bytes
    
13. Database mounted.
    
14. SQL> alter database noarchivelog;
    
15. 
    
16. Database altered.
    
17. SQL> alter database drop logfile group 1;
    
18. 
    
19. Database altered.
    
20. 
    
21. SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/HU/redo01.log') size 50m;
    
22. 
    
23. Database altered.
    
24. 
    
25. 
    
26. SQL> alter database archivelog;
    
27. 
    
28. Database altered.
    
29. 
    
30. SQL> alter database open;
    
31. 
    
32. Database altered.
    
33. 
    
34. SQL>
    
35. 
    
36. SQL> select count(*) from scott.t;
    
37. 
    
38.   COUNT(*)
    
39. ----------
    
40.      86966
    
41. 
    
42. SQL>

方法二：recover的方式恢复重做日志

1. SQL> shutdown immediate
   
2. Database closed.
   
3. Database dismounted.
   
4. ORACLE instance shut down.
   
5. SQL> startup mount
   
6. ORACLE instance started.
   
7. 
   
8. Total System Global Area 830930944 bytes
   
9. Fixed Size 2257800 bytes
   
10. Variable Size 536874104 bytes
    
11. Database Buffers 289406976 bytes
    
12. Redo Buffers 2392064 bytes
    
13. Database mounted.
    
14. SQL>
    
15. SQL> recover database until cancel;
    
16. Media recovery complete.
    
17. SQL> alter database open resetlogs;
    
18. 
    
19. Database altered.
    
20. 
    
21. SQL> select group#,members,archived,status from v$log;
    
22. 
    
23.     GROUP# MEMBERS ARC STATUS
    
24. ---------- ---------- --- ----------------
    
25.          1 1 NO CURRENT
    
26.          2 1 YES UNUSED
    
27.          3 1 YES UNUSED
    
28. 
    
29. SQL> select count(*) from scott.t;
    
30. 
    
31.   COUNT(*)
    
32. ----------
    
33.      86966
    
34. 
    
35. SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31399171/viewspace-2138628/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31399171/viewspace-2138628/