【ORACLE RAC】日志组管理
************************************************************************ ****原文： blog.csdn.net/clark_xu 徐长亮的专栏 ************************************************************************ rac需要至少需要四个日志组，分配到每个线程上各
************************************************************************ ****原文：blog.csdn.net/clark_xu 徐长亮的专栏 ************************************************************************rac需要至少需要四个日志组，分配到每个线程上各两组，两个线程的日志组不共享。
一、查看各个节点的线程分配：
节点一
SQL> show parameters thread
NAME TYPE VALUE
----------------------------------------------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 1
节点2
SQL> show parameters thread
NAME TYPE VALUE
----------------------------------------------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 2
二、查看默认组对应的线程
SQL> select group#,thread#,members from v$log;
GROUP# THREAD# MEMBERS
---------- ---------- ----------
1 1 2
2 1 2
3 2 2
4 2 2
三、常见的日志组规划：每个线程6组，每个组的两个成员放在不同的位置
| redo 日志组 | 线程 | 成员 | 位置 | 大小 |
| Group 1 | thread 1 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB | ||
| Group 2 | thread 1 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB | ||
| Group 3 | thread 2 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB | ||
| Group 4 | thread 2 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB | ||
| Group 5 | thread 1 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB | ||
| Group 6 | thread 1 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB | ||
| Group 7 | thread 1 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB | ||
| Group 8 | thread 1 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB | ||
| Group 9 | thread 2 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB | ||
| Group 10 | thread 2 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB | ||
| Group 11 | thread 2 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB | ||
| Group 12 | thread 2 | member 1 | +DGDATA | 1GB |
| member 2 | +DGFRA | 1GB |
四、添加日志组
SQL> ALTER DATABASE ADD LOGFILE THREAD 1GROUP 5 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1GROUP 6 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1GROUP 7 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1GROUP 8 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2GROUP 9 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2GROUP 10 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2GROUP 11 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2GROUP 12 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
五、修改当前日志组，为了能删除默认的四个日志组
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
Systemaltered.
SQL> alter system checkpoint;
System altered.
六、删除和新建日志组
SQL> alter database drop logfile group1;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
七、添加日志组
SQL> ALTER DATABASE ADD LOGFILE THREAD 1GROUP 1 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1GROUP 2 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2GROUP 3 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2GROUP 4 ('+DGDATA','+DGFRA') SIZE 1024M;
Database altered.
八、查看，确认
SQL> select group#,thread#,members fromv$log;
GROUP# THREAD# MEMBERS
---------- ---------- ----------
1 1 2
2 1 2
3 2 2
4 2 2
5 1 2
6 1 2
7 1 2
8 1 2
9 2 2
10 2 2
11 2 2
GROUP# THREAD# MEMBERS
---------- ---------- ----------
12 2 2
12 rows selected