oracle日志组

[size=small]
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大小[/size]