ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 丢失current状态日志文件

丢失current状态日志文件

原创 Oracle 作者：zzh_ds 时间：2017-05-07 16:58:50 0 删除 编辑
查看当前日志组状态，数据库处于归档模式
  1. SQL> select group#,members,archived,status from v$log;

  3.     GROUP# MEMBERS ARC STATUS
  4. ---------- ---------- --- ----------------
  5.          1 1 NO CURRENT
  6.          2 1 YES UNUSED
  7.          3 1 YES INACTIVE

  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;

  3. Table created.

  5. SQL> insert into scott.t select * from dba_objects;

  7. 86966 rows created.

  9. SQL> commit;

  11. Commit complete.

  13. SQL> select count(*) from scott.t;

  15.   COUNT(*)
  16. ----------
  17.      86966
  18.      
  19. SQL> select group#,members,archived,status from v$log;

  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

  5. SQL> !rm /u01/app/oracle/oradata/HU/redo01.log

  7. SQL> !ls /u01/app/oracle/oradata/HU/*.log
  8. /u01/app/oracle/oradata/HU/redo02.log /u01/app/oracle/oradata/HU/redo03.log

  10. SQL> 


查看日志组状态 ，然后手工切换下
  1. SQL> select group#,members,archived,status from v$log;

  3.     GROUP# MEMBERS ARC STATUS
  4. ---------- ---------- --- ----------------
  5.          1 1 NO CURRENT
  6.          2 1 YES UNUSED
  7.          3 1 YES INACTIVE

  9. SQL> alter system switch logfile;

  11. System altered.

  13. SQL> select group#,members,archived,status from v$log;

  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.

  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;

  16. Database altered.
  17. SQL> alter database drop logfile group 1;

  19. Database altered.

  21. SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/HU/redo01.log') size 50m;

  23. Database altered.


  26. SQL> alter database archivelog;

  28. Database altered.

  30. SQL> alter database open;

  32. Database altered.

  34. SQL>

  36. SQL> select count(*) from scott.t;

  38.   COUNT(*)
  39. ----------
  40.      86966

  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.

  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;

  19. Database altered.

  21. SQL> select group#,members,archived,status from v$log;

  23.     GROUP# MEMBERS ARC STATUS
  24. ---------- ---------- --- ----------------
  25.          1 1 NO CURRENT
  26.          2 1 YES UNUSED
  27.          3 1 YES UNUSED

  29. SQL> select count(*) from scott.t;

  31.   COUNT(*)
  32. ----------
  33.      86966

  35. SQL>






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

0
0
上一篇： ORA-01184: logfile group 1 already exists
下一篇： drop user scott 和drop user scott cascade 的区别
请登录后发表评论 登录
全部评论
zzh_ds

注册时间：2016-09-29

  • 博文量
    61
  • 访问量
    47811

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593