eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle数据恢复:ORA-00600 6749与ORA-8102 | Blog首页 | 如何禁用Oracle AWR自动采样功能 »

如何删除日志组成员( DROP LOGFILE MEMBER )

整理自己测试数据库的日志.

第一个步骤,转移日志文件位置:
SQL> alter database mount;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
         1          1         16   52428800          2 NO  INACTIVE
       209996 04-MAR-12

         3          1         15   52428800          2 NO  INACTIVE
       209220 04-MAR-12

         2          1         17   52428800          2 NO  CURRENT
       230802 05-MAR-12


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_1_6Y993Y41_.LOG
C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_1_6Y993YBB_.LOG
C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LOG
C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG
C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_3_6Y993YRD_.LOG
C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_3_6Y993YX3_.LOG

6 rows selected.

SQL> alter database rename file 'C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_1_6Y993Y41_.LOG' to 'D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_1_6Y993Y41_.LO
G';

Database altered.

SQL> alter database rename file 'C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LOG' to 'D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LO
G';

Database altered.

SQL> alter database rename file 'C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_3_6Y993YRD_.LOG' to 'D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_3_6Y993YRD_.LO
G';

Database altered.
缺省的每组日志有两个成员:
SQL> alter database open;

Database altered.
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                        IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         1         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_1_6Y993Y41_.LOG                         NO
         1         ONLINE  C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_1_6Y993YBB_.LOG             YES
         2         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LOG                         NO
         2         ONLINE  C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG             YES
         3         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_3_6Y993YRD_.LOG                         NO
         3         ONLINE  C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_3_6Y993YX3_.LOG             YES

6 rows selected.
清除其中的一个日志组成员:
SQL> alter database drop logfile member 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_1_6Y993YBB_.LOG';

Database altered.

SQL> alter database drop logfile member 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG';
alter database drop logfile member 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: 'D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LOG'
ORA-00312: online log 2 thread 1: 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG'


SQL> alter database drop logfile member 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_3_6Y993YX3_.LOG';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile member 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG';

Database altered.
最后的日志情况:
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                        IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         1         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_1_6Y993Y41_.LOG                         NO
         2         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LOG                         NO
         3         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_3_6Y993YRD_.LOG                         NO
日志记录之。




历史上的今天...
    >> 2009-03-13文章:
    >> 2008-03-13文章:
           打点行囊 准备搬家
    >> 2007-03-13文章:
    >> 2006-03-13文章:
           自强不息 厚德载物

无觅

By eygle on 2012-03-13 08:44 | Comments (0) | FAQ | 2969 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com