【ORACLE RAC】日志组管理

来源：互联网发布：淘宝新品上架怎么刷编辑：程序博客网时间：2024/06/07 17:06

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

0 0