************************************************************************
****原文: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