oracle實驗記錄（logfile基礎操作2）

fufuh2o發表於2009-08-25
Oracle

關於刪除logfile group& member

情況一：add 新member 與現有member在同一位置 or 不同位置
SQL> select group#,members,status from v$log;

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

SQL> select group#,member,status from v$logfile;

    GROUP# MEMBER                                   STATUS
---------- ---------------------------------------- -------
         3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO03.LOG

         2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO02.LOG

         1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO01.LOG
SQL> alter database add logfile member 'd:\redo02_2.log' to group 2;

資料庫已更改。
SQL> alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0

\ORADATA\XHTEST\
REDO03_2.LOG' to group 3;

資料庫已更改。
SQL> select group#,member,status from v$logfile;

    GROUP# MEMBER                                   STATUS
---------- ---------------------------------------- -------
         3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO03.LOG

         2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO02.LOG

         1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO01.LOG

         2 D:\REDO02_2.LOG                          INVALID
         3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\ INVALID

    GROUP# MEMBER                                   STATUS
---------- ---------------------------------------- -------
           REDO03_2.LOG


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

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


SQL> alter database drop logfile member 'F:\ORACLE\PRODUCT\10.2.0

\ORADATA\XHTEST~~~~~~~~~~刪除~
\REDO03_2.LOG';

資料庫已更改。

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

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

SQL> select member,status from v$logfile;

MEMBER                                   STATUS
---------------------------------------- -------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO03.LOG

F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO02.LOG

F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO01.LOG

D:\REDO02_2.LOG                          INVALID

SQL> alter database drop logfile member 'd:\redo02_2.log';~~~~~~~~~~~刪

除~

資料庫已更改。

SQL> select member,status from v$logfile;

MEMBER                                   STATUS
---------------------------------------- -------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO03.LOG

F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO02.LOG

F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO01.LOG


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

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

SQL> alter system switch logfile
  2  ;

系統已更改。

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

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

SQL> alter system checkpoint;

系統已更改。

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

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

SQL> alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0

\ORADATA\XHTEST\~~~~~~~~~~~~~~~~新add
REDO01_2.LOG' to group 1;

資料庫已更改。

SQL> select member,status from v$logfile;

MEMBER                                   STATUS
---------------------------------------- -------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO03.LOG

F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO02.LOG

F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO01.LOG

F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\ INVALID
REDO01_2.LOG

MEMBER                                   STATUS
---------------------------------------- -------


SQL> alter database drop logfile member 'F:\ORACLE\PRODUCT\10.2.0

\ORADATA\XHTEST~~~~~~~~~~~~~~~~~~~~~可以正常刪除
\REDO01_2.LOG';

資料庫已更改。


情況2 移動現有group中的member
SQL> select group#,member,status from v$logfile;

    GROUP# MEMBER                                   STATUS
---------- ---------------------------------------- -------
         3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO03.LOG

         2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO02.LOG

         1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO01.LOG

         2 D:\REDO02_2.LOG                          INVALID

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

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

SQL> host copy D:\REDO02_2.LOG  E:\REDO02_2.LOG
已複製         1 個檔案。

SQL> alter database rename file 'D:\REDO02_2.LOG' to 'E:\REDO02_2.LOG';

資料庫已更改。

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

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

SQL> select group#,member,status from v$logfile;

    GROUP# MEMBER                                   STATUS
---------- ---------------------------------------- -------
         3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO03.LOG

         2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO02.LOG

         1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO01.LOG

         2 E:\REDO02_2.LOG                          INVALID~~~~~~~~~~~移動ok


SQL> alter database drop logfile member 'E:\redo02_2.log';

資料庫已更改。


SQL> alter database drop logfile group 2;

資料庫已更改。

SQL> select group#,member,status from v$logfile;

    GROUP# MEMBER                                   STATUS
---------- ---------------------------------------- -------
         3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO03.LOG

         1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO01.LOG

oracle DBA 寶典中說，如果用這種方式(rename)
移動redo將無法刪除（member,group） 經過實驗 是可以刪除的

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>

SQL> alter database add logfile group 2 ( 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTE
ST\REDO02.LOG') size 10m;

資料庫已更改。

 

SQL> alter database add logfile member 'd:\redo02_2.log' to group 2;

資料庫已更改。

SQL> select group#,member,status from v$logfile;

    GROUP# MEMBER                                   STATUS
---------- ---------------------------------------- -------
         3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO03.LOG

         2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO02.LOG

         1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO01.LOG

         2 D:\REDO02_2.LOG                          INVALID

SQL> host copy D:\REDO02_2.LOG  E:\REDO02_2.LOG
已複製         1 個檔案。

SQL> alter database rename file 'D:\REDO02_2.LOG' to 'E:\REDO02_2.LOG';

資料庫已更改。

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

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

SQL> select group#,member,status from v$logfile;

    GROUP# MEMBER                                   STATUS
---------- ---------------------------------------- -------
         3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO03.LOG

         2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO02.LOG

         1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO01.LOG

         2 E:\REDO02_2.LOG                          INVALID

SQL> alter database drop logfile group 2;

資料庫已更改。

SQL> alter database drop logfile member 'E:\redo02_2.log';
alter database drop logfile member 'E:\redo02_2.log'
*
第 1 行出現錯誤:
ORA-00360: 非日誌檔案成員: E:\redo02_2.log

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-613239/，如需轉載，請註明出處，否則將追究法律責任。

相關文章