oracle日志组

1.alter system switch logfile 和alter system archive log current的区别
1）alter system switch logfile 是不等待归档完成就switch logfile。
如果database尚未开启archive log mode。那用这个切换是毋庸置疑了。另外，也是对单实例database和RAC模式下当前实例执行日志切换。

2）alter system archive log current则需要等待归档完成才switch logfile。会对中所有实例执行日志切换。

整体上说来，在自动归档的库里，两个命令的所产生的结果几乎一样。有区别的是alter system archive log current所用的时间会比alter system switch logfile 的长。

2.增加日志组成员：
SQL> alter database add logfile member '/oraback/logbackup/redo01_b.log' to group 1;
Database altered.
注意：alter 语句中不可设定redo01_b.log的大小。

SQL> alter database add logfile member '/oraback/logbackup/redo01_b.log' size 100M to group 1;
alter database add logfile member '/oraback/logbackup/redo01_b.log' size 100M to group 1
                                                                    *
ERROR at line 1:
ORA-00946: missing TO keyword。

Alter语句运行之后，生成的Redo01_b.log的大小与redo01.log的大小一致。
路径可以选择与默认路径不一致。

最后，得声明一下：可以同时增加几个日志组。也可以同时增加几个日志组成员。语句格式如下：
ALTER DATABASE ADD LOGFILE '/LOCATION_DUEST/REDO01.LOG' SIZE 10M，''/LOCATION_DUEST/REDO02.LOG'  SIZE 10M... ...;依次类推。


3.删除日志组及日志组成员：

原则：删除前必须遵守如下原则，每个实例必须至少有两个日志组；当一个组处于ACTIVE或者CURRENT的状态时不可删除；删除日志组的操作只对数据库进行更改，
操作系统的文件尚未删除；当删除时适用DROP LOGFILE GROUP N语句时，此时GROUP N内的所有成员都将被删除。

ALTER DATABASE DROP LOGFILE GROUP N；

删除日志成员的原则：当你删除一个是该组中最后一个成员的时候，你不能删除此成员；当组的转台处于current的状态时，
不能删除组成员；在归档模式下，必须得归档之后才能删除；
删除日志组成员的操作只对数据库进行更改，操作系统的文件尚未删除

ALTER DATABASE DROP LOGFILE MEMBER '/LOCATION_DUST/REDO0N_N.LOG';


4.实际操作：
SQL> alter database add logfile group 4 '+FRA_DG' size 500m;
Database altered
SQL> alter database add logfile member '+DATA_DG' size 500m to group 4;
alter database add logfile member '+DATA_DG' size 500m to group 4
ORA-00946: 缺失 TO 关键字—这里不用指定这个member的大小，这个会跟组里面的其他成员一样大小
SQL> alter database add logfile member '+DATA_DG' to group 4;
Database altered

alter database add logfile group 4 '+FRA_DG' size 500m;
alter database add logfile member '+DATA_DG' to group 4;
alter database add logfile group 5 '+FRA_DG' size 500m;
alter database add logfile member '+DATA_DG' to group 5 ;
alter database add logfile group 6 '+FRA_DG' size 500m;
alter database add logfile member '+DATA_DG' to group 6 ;
alter database add logfile group 7 '+FRA_DG' size 500m;
alter database add logfile member '+DATA_DG' to group 7 ;


5.添加6个组 每个组2个member 大小都是500m，然后将之前的日志组删掉。
SQL> select group#,thread#,members,archived,status from v$log;
    GROUP#    THREAD#    MEMBERS ARCHIVED STATUS
---------- ---------- ---------- -------- ----------------
         1          1          2 NO       INACTIVE
         2          1          2 NO       ACTIVE
         3          1          2 NO       INACTIVE
         4          1          2 NO       CURRENT
         5          1          2 YES      UNUSED
         6          1          2 YES      UNUSED
         7          1          2 YES      UNUSED
         8          1          2 YES      UNUSED
         9          1          2 YES      UNUSED

9 rows selected

SQL> alter database drop logfile group 1;
Database altered

SQL> alter database drop logfile group 3;
Database altered

alter system switch logfile;
当组2的状态变成inactive的时候，就可以将其删掉。
alter database drop logfile group 2;

SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
         4         ONLINE  +FRA_DG/scs/onlinelog/group_4.3472.830949475                                     NO
         4         ONLINE  +DATA_DG/scs/onlinelog/group_4.640.830949573                                     NO
         5         ONLINE  +FRA_DG/scs/onlinelog/group_5.580.830949687                                      NO
         5         ONLINE  +DATA_DG/scs/onlinelog/group_5.641.830949753                                     NO
         6         ONLINE  +FRA_DG/scs/onlinelog/group_6.2980.830949843                                     NO
         6 INVALID ONLINE  +DATA_DG/scs/onlinelog/group_6.642.830949845                                     NO
         7         ONLINE  +FRA_DG/scs/onlinelog/group_7.10760.830949845                                    NO
         7 INVALID ONLINE  +DATA_DG/scs/onlinelog/group_7.643.830949849                                     NO
         8         ONLINE  +FRA_DG/scs/onlinelog/group_8.2963.830949849                                     NO
         8 INVALID ONLINE  +DATA_DG/scs/onlinelog/group_8.644.830949851                                     NO
         9         ONLINE  +FRA_DG/scs/onlinelog/group_9.421.830949853                                      NO
         9 INVALID ONLINE  +DATA_DG/scs/onlinelog/group_9.645.830949855                                     NO

12 rows selected

SQL> select group#,thread#,members,status from v$log
    GROUP#    THREAD#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         4          1          2 ACTIVE
         5          1          2 CURRENT
         6          1          2 UNUSED
         7          1          2 UNUSED
         8          1          2 UNUSED
         9          1          2 UNUSED

6 rows selected

SQL> select group#,thread#,members,archived,status from v$log;
    GROUP#    THREAD#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         4          1          2 NO  CURRENT
         5          1          2 YES INACTIVE
         6          1          2 YES INACTIVE
         7          1          2 YES INACTIVE
         8          1          2 YES INACTIVE
         9          1          2 YES INACTIVE

6 rows selected.

SQL> alter database drop logfile member '+FRA_DG/scs/onlinelog/group_5.580.830949687';
Database altered.

备库也添加6组日志组
alter database add standby logfile  group 10 '+DATA_DG' size 500m;
alter database add standby logfile  group 11 '+DATA_DG' size 500m;
alter database add standby logfile  group 12 '+DATA_DG' size 500m;
alter database add standby logfile  group 13 '+DATA_DG' size 500m;
alter database add standby logfile  group 14 '+DATA_DG' size 500m;
alter database add standby logfile  group 15 '+DATA_DG' size 500m;


SQL> select group#,thread#,members,archived,status,bytes/1024/1024 from v$log;

    GROUP#    THREAD#    MEMBERS ARC STATUS           BYTES/1024/1024
---------- ---------- ---------- --- ---------------- ---------------
         1          1          1 NO  INACTIVE                      50
         2          1          1 NO  CURRENT                       50
         3          1          1 NO  INACTIVE                      50     一般初始化的库都是3个组  每个组一个成员，每个成员50m大小