ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录(logfile基础操作2)

oracle实验记录(logfile基础操作2)

原创 Linux操作系统 作者:fufuh2o 时间:2009-08-25 23:47:03 0 删除 编辑

关于删除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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426822