问题描述
今天有人提起丢失current online redolog,如何打开数据库,我也模拟一把
专家解答
环境 oracle 10g r2
archivelog model
数据库打开的情况下os rm logfile
sys@ORCL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- 1 1 61 52428800 2 YES ACTIVE 1339107572 2011-06-28 17:41:09 2 1 62 52428800 2 YES ACTIVE 1339107576 2011-06-28 17:41:16 3 1 63 52428800 2 NO CURRENT 1339107587 2011-06-28 17:41:25 5 1 60 52428800 2 YES INACTIVE 1339107550 2011-06-28 17:40:14 sys@ORCL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------------------------------------- --- 5 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/redo5.log NO 5 ONLINE /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/redo5.log NO 3 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_5y4dgorl_.log NO 3 ONLINE /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5y4dgq04_.log NO 2 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_70ljvbqf_.log NO 2 ONLINE /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/redo2_2.log NO 1 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_5y4dgjvk_.log NO 1 ONLINE /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5y4dgl2s_.log NO 8 rows selected. sys@ORCL> alter system checkpoint; System altered. sys@ORCL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- 1 1 61 52428800 2 YES INACTIVE 1339107572 2011-06-28 17:41:09 2 1 62 52428800 2 YES INACTIVE 1339107576 2011-06-28 17:41:16 3 1 63 52428800 2 NO CURRENT 1339107587 2011-06-28 17:41:25 5 1 60 52428800 2 YES INACTIVE 1339107550 2011-06-28 17:40:14 sys@ORCL> ! rm /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_5y4dgorl_.log sys@ORCL> ! rm /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5y4dgq04_.log sys@ORCL> alter system switch logfile; System altered. sys@ORCL> alter system switch logfile; System altered. sys@ORCL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- 1 1 65 52428800 2 NO CURRENT 1339107758 2011-06-28 17:48:43 2 1 62 52428800 2 YES INACTIVE 1339107576 2011-06-28 17:41:16 3 1 63 52428800 2 NO ACTIVE 1339107587 2011-06-28 17:41:25 5 1 64 52428800 2 NO ACTIVE 1339107755 2011-06-28 17:48:36 sys@ORCL> alter system checkpoint; System altered. sys@ORCL> alter system switch logfile; System altered. sys@ORCL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- 1 1 65 52428800 2 NO ACTIVE 1339107758 2011-06-28 17:48:43 2 1 66 52428800 2 NO CURRENT 1339107774 2011-06-28 17:49:11 3 1 63 52428800 2 NO INACTIVE 1339107587 2011-06-28 17:41:25 5 1 64 52428800 2 NO INACTIVE 1339107755 2011-06-28 17:48:36 sys@ORCL> alter system switch logfile; --没反应 alter system switch logfile * ERROR at line 1: ORA-01013: user requested cancel of current operation sys@ORCL> sys@ORCL> sys@ORCL> sys@ORCL> sys@ORCL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. sys@ORCL> startup ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 1219184 bytes Variable Size 385877392 bytes Database Buffers 855638016 bytes Redo Buffers 15556608 bytes Database mounted. ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_5y4dgorl_.log' ORA-00312: online log 3 thread 1: '/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5y4dgq04_.log' sys@ORCL> recover database until cancel; Media recovery complete. sys@ORCL> alter database resetlogs; alter database resetlogs * ERROR at line 1: ORA-02231: missing or invalid option to ALTER DATABASE sys@ORCL> alter database open resetlogs; Database altered. sys@ORCL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- 1 1 2 52428800 2 NO CURRENT 1339107896 2011-06-28 17:54:23 2 1 1 52428800 2 YES INACTIVE 1339107895 2011-06-28 17:54:13 3 1 0 52428800 1 YES UNUSED 0 5 1 0 52428800 2 YES UNUSED 0 sys@ORCL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------------------------------------- --- 5 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/redo5.log NO 5 ONLINE /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/redo5.log NO 3 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_70m96bz0_.log NO 1 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_5y4dgjvk_.log NO 2 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_70ljvbqf_.log NO 2 ONLINE /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/redo2_2.log NO 1 ONLINE /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5y4dgl2s_.log NO 7 rows selected. sys@ORCL> !ls /u01/app/oracle/oradata/ORCL/onlinelog o1_mf_1_5y4dgjvk_.log o1_mf_2_70ljvbqf_.log o1_mf_3_70m96bz0_.log redo5.log sys@ORCL> alter database add logfile member '/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/redo3_2.log' to group 3; Database altered. sys@ORCL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- 1 1 2 52428800 2 NO CURRENT 1339107896 2011-06-28 17:54:23 2 1 1 52428800 2 YES INACTIVE 1339107895 2011-06-28 17:54:13 3 1 0 52428800 2 YES UNUSED 0 5 1 0 52428800 2 YES UNUSED 0 sys@ORCL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------------------------------------- --- 5 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/redo5.log NO 5 ONLINE /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/redo5.log NO 3 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_70m96bz0_.log NO 1 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_5y4dgjvk_.log NO 2 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_70ljvbqf_.log NO 2 ONLINE /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/redo2_2.log NO 1 ONLINE /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5y4dgl2s_.log NO 3 INVALID ONLINE /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/redo3_2.log NO 8 rows selected. sys@ORCL> startup force ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 1219184 bytes Variable Size 385877392 bytes Database Buffers 855638016 bytes Redo Buffers 15556608 bytes Database mounted. Database opened.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
