1. 准备工作
1.1. 查看日志组基本情况
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 29 19:21:59 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 1 INACTIVE 50
2 1 1 INACTIVE 50
3 2 1 INACTIVE 50
4 2 1 INACTIVE 50
5 1 1 CURRENT 50
6 1 1 INACTIVE 50
7 2 1 CURRENT 50
8 2 1 INACTIVE 50
8 rows selected.
SYS@RACDB1>col member for a45;
SYS@RACDB1>select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATA/racdb/onlinelog/group_1.267.926536571
2 +DATA/racdb/onlinelog/group_2.262.926536585
3 +DATA/racdb/onlinelog/group_3.266.926536603
4 +DATA/racdb/onlinelog/group_4.261.926536613
5 +DATA/racdb/onlinelog/group_5.272.926535655
6 +DATA/racdb/onlinelog/group_6.269.926535221
7 +DATA/racdb/onlinelog/group_7.270.926535251
8 +DATA/racdb/onlinelog/group_8.271.926535323
8 rows selected.
2. 增加日志组成员
——查看日志位置:
[grid@rac1 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCR/
ASMCMD> cd data
ASMCMD> ls
RACDB/
ASMCMD> cd racdb
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileRACDB.ora
ASMCMD> cd onlinelog
ASMCMD> ls
group_1.267.926536571
group_2.262.926536585
group_3.266.926536603
group_4.261.926536613
group_5.272.926535655
group_6.269.926535221
group_7.270.926535251
group_8.271.926535323
ASMCMD>
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo01_a.log' to group 1;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo02_a.log' to group 2;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo03_a.log' to group 3;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo04_a.log' to group 4;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo05_a.log' to group 5;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo06_a.log' to group 6;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo07_a.log' to group 7;
Database altered.
SYS@RACDB1>alter database add logfile member '+data/racdb/onlinelog/redo08_a.log' to group 8;
Database altered.
SYS@RACDB1>alter database add logfile member '+fra/racdb/onlinelog/redo01_b.log' to group 1;
alter database add logfile member '+fra/racdb/onlinelog/redo02_b.log' to group 2;
alter database add logfile member '+fra/racdb/onlinelog/redo03_b.log' to group 3;
alter database add logfile member '+fra/racdb/onlinelog/redo04_b.log' to group 4;
alter database add logfile member '+fra/racdb/onlinelog/redo05_b.log' to group 5;
alter database add logfile member '+fra/racdb/onlinelog/redo06_b.log' to group 6;
alter database add logfile member '+fra/racdb/onlinelog/redo07_b.log' to group 7;
alter database add logfile member '+fra/racdb/onlinelog/redo08_b.log' to group 8;
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
——查看日志组成员:
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 3 INACTIVE 50
2 1 3 INACTIVE 50
3 2 3 INACTIVE 50
4 2 3 INACTIVE 50
5 1 3 CURRENT 50
6 1 3 INACTIVE 50
7 2 3 CURRENT 50
8 2 3 INACTIVE 50
8 rows selected.
SYS@RACDB1>select GROUP#,MEMBER,STATUS from v$logfile;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATA/racdb/onlinelog/group_1.267.926536571
2 +DATA/racdb/onlinelog/group_2.262.926536585
3 +DATA/racdb/onlinelog/group_3.266.926536603
4 +DATA/racdb/onlinelog/group_4.261.926536613
1 +DATA/racdb/onlinelog/redo01_a.log INVALID
2 +DATA/racdb/onlinelog/redo02_a.log INVALID
3 +DATA/racdb/onlinelog/redo03_a.log INVALID
4 +DATA/racdb/onlinelog/redo04_a.log INVALID
5 +DATA/racdb/onlinelog/group_5.272.926535655
6 +DATA/racdb/onlinelog/group_6.269.926535221
7 +DATA/racdb/onlinelog/group_7.270.926535251
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
8 +DATA/racdb/onlinelog/group_8.271.926535323
5 +DATA/racdb/onlinelog/redo05_a.log INVALID
6 +DATA/racdb/onlinelog/redo06_a.log INVALID
7 +DATA/racdb/onlinelog/redo07_a.log INVALID
8 +DATA/racdb/onlinelog/redo08_a.log INVALID
1 +FRA/racdb/onlinelog/redo01_b.log INVALID
2 +FRA/racdb/onlinelog/redo02_b.log INVALID
3 +FRA/racdb/onlinelog/redo03_b.log INVALID
4 +FRA/racdb/onlinelog/redo04_b.log INVALID
5 +FRA/racdb/onlinelog/redo05_b.log INVALID
6 +FRA/racdb/onlinelog/redo06_b.log INVALID
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
7 +FRA/racdb/onlinelog/redo07_b.log INVALID
8 +FRA/racdb/onlinelog/redo08_b.log INVALID
24 rows selected.
3. 删除日志组成员
3.1. 什么情况无法删除日志成员
有以下两种情况无法删除:
1) 状态为 CURRENT 的日志组成员;
2) 日志组成员的状态为 INVALID 的。
3.2. 查看日志组成员状态
SYS@RACDB1>select GROUP#,MEMBER,STATUS from v$logfile order by 2;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATA/racdb/onlinelog/group_1.267.926536571
2 +DATA/racdb/onlinelog/group_2.262.926536585
3 +DATA/racdb/onlinelog/group_3.266.926536603
4 +DATA/racdb/onlinelog/group_4.261.926536613
5 +DATA/racdb/onlinelog/group_5.272.926535655
6 +DATA/racdb/onlinelog/group_6.269.926535221
7 +DATA/racdb/onlinelog/group_7.270.926535251
8 +DATA/racdb/onlinelog/group_8.271.926535323
1 +DATA/racdb/onlinelog/redo01_a.log INVALID
2 +DATA/racdb/onlinelog/redo02_a.log INVALID
3 +DATA/racdb/onlinelog/redo03_a.log INVALID
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
4 +DATA/racdb/onlinelog/redo04_a.log INVALID
5 +DATA/racdb/onlinelog/redo05_a.log INVALID
6 +DATA/racdb/onlinelog/redo06_a.log INVALID
7 +DATA/racdb/onlinelog/redo07_a.log INVALID
8 +DATA/racdb/onlinelog/redo08_a.log INVALID
1 +FRA/racdb/onlinelog/redo01_b.log INVALID
2 +FRA/racdb/onlinelog/redo02_b.log INVALID
3 +FRA/racdb/onlinelog/redo03_b.log INVALID
4 +FRA/racdb/onlinelog/redo04_b.log INVALID
5 +FRA/racdb/onlinelog/redo05_b.log INVALID
6 +FRA/racdb/onlinelog/redo06_b.log INVALID
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
7 +FRA/racdb/onlinelog/redo07_b.log INVALID
8 +FRA/racdb/onlinelog/redo08_b.log INVALID
24 rows selected.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 3 INACTIVE 50
2 1 3 INACTIVE 50
3 2 3 INACTIVE 50
4 2 3 INACTIVE 50
5 1 3 CURRENT 50
6 1 3 INACTIVE 50
7 2 3 CURRENT 50
8 2 3 INACTIVE 50
8 rows selected.
3.3. 切换日志组
通过切换日志组使日志成员的状态为空(也就是有效),每个节点有几组日志, 就切换几次即可
1) 一节点切换 4 次
SYS@RACDB1>alter system switch logfile;
System altered.
SYS@RACDB1>/
System altered.
SYS@RACDB1>/
System altered.
SYS@RACDB1>/
System altered.
2) 二节点切换 4 次
SYS@RACDB2>alter system switch logfile;
System altered.
SYS@RACDB2>/
System altered.
SYS@RACDB2>/
System altered.
SYS@RACDB2>/
System altered.
3) 查看切换后日志组成员的状态
SYS@RACDB1>select GROUP#,MEMBER,STATUS from v$logfile order by 2;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATA/racdb/onlinelog/group_1.267.926536571
2 +DATA/racdb/onlinelog/group_2.262.926536585
3 +DATA/racdb/onlinelog/group_3.266.926536603
4 +DATA/racdb/onlinelog/group_4.261.926536613
5 +DATA/racdb/onlinelog/group_5.272.926535655
6 +DATA/racdb/onlinelog/group_6.269.926535221
7 +DATA/racdb/onlinelog/group_7.270.926535251
8 +DATA/racdb/onlinelog/group_8.271.926535323
1 +DATA/racdb/onlinelog/redo01_a.log
2 +DATA/racdb/onlinelog/redo02_a.log
3 +DATA/racdb/onlinelog/redo03_a.log
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
4 +DATA/racdb/onlinelog/redo04_a.log
5 +DATA/racdb/onlinelog/redo05_a.log
6 +DATA/racdb/onlinelog/redo06_a.log
7 +DATA/racdb/onlinelog/redo07_a.log
8 +DATA/racdb/onlinelog/redo08_a.log
1 +FRA/racdb/onlinelog/redo01_b.log
2 +FRA/racdb/onlinelog/redo02_b.log
3 +FRA/racdb/onlinelog/redo03_b.log
4 +FRA/racdb/onlinelog/redo04_b.log
5 +FRA/racdb/onlinelog/redo05_b.log
6 +FRA/racdb/onlinelog/redo06_b.log
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
7 +FRA/racdb/onlinelog/redo07_b.log
8 +FRA/racdb/onlinelog/redo08_b.log
24 rows selected.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 3 INACTIVE 50
2 1 3 INACTIVE 50
3 2 3 INACTIVE 50
4 2 3 INACTIVE 50
5 1 3 CURRENT 50
6 1 3 INACTIVE 50
7 2 3 CURRENT 50
8 2 3 INACTIVE 50
8 rows selected.
3.4. 删除非 CURRENT 状态的日志组成员
1) 通过前面的查询我们知道,日志组 5 和 7 的状态为 CURRENT
SYS@RACDB1> alter database drop logfile member '+DATA/racdb/onlinelog/group_1.267.926536571';
alter database drop logfile member '+DATA/racdb/onlinelog/group_2.262.926536585';
alter database drop logfile member '+DATA/racdb/onlinelog/group_3.266.926536603';
Database altered.
SYS@RACDB1> alter database drop logfile member '+DATA/racdb/onlinelog/group_4.261.926536613';
alter database drop logfile member '+DATA/racdb/onlinelog/group_6.269.926535221';
Database altered.
SYS@RACDB1> alter database drop logfile member '+DATA/racdb/onlinelog/group_8.271.926535323';
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
SYS@RACDB1>
Database altered.
——查看日志状态:
SYS@RACDB1>select GROUP#,MEMBER,STATUS from v$logfile order by 2;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
5 +DATA/racdb/onlinelog/group_5.272.926535655
7 +DATA/racdb/onlinelog/group_7.270.926535251
1 +DATA/racdb/onlinelog/redo01_a.log
2 +DATA/racdb/onlinelog/redo02_a.log
3 +DATA/racdb/onlinelog/redo03_a.log
4 +DATA/racdb/onlinelog/redo04_a.log
5 +DATA/racdb/onlinelog/redo05_a.log
6 +DATA/racdb/onlinelog/redo06_a.log
7 +DATA/racdb/onlinelog/redo07_a.log
8 +DATA/racdb/onlinelog/redo08_a.log
1 +FRA/racdb/onlinelog/redo01_b.log
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
2 +FRA/racdb/onlinelog/redo02_b.log
3 +FRA/racdb/onlinelog/redo03_b.log
4 +FRA/racdb/onlinelog/redo04_b.log
5 +FRA/racdb/onlinelog/redo05_b.log
6 +FRA/racdb/onlinelog/redo06_b.log
7 +FRA/racdb/onlinelog/redo07_b.log
8 +FRA/racdb/onlinelog/redo08_b.log
18 rows selected.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 INACTIVE 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 INACTIVE 50
5 1 3 CURRENT 50
6 1 2 INACTIVE 50
7 2 3 CURRENT 50
8 2 2 INACTIVE 50
8 rows selected.
2) 切换日志组
——节点一切换 1 次
SYS@RACDB1>alter system switch logfile;
System altered.
——节点二切换 1 次
SYS@RACDB2>alter system switch logfile;
System altered.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 INACTIVE 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 INACTIVE 50
5 1 3 ACTIVE 50
6 1 2 CURRENT 50
7 2 3 INACTIVE 50
8 2 2 CURRENT 50
8 rows selected.
——节点一进行归档:
SYS@RACDB1>alter system checkpoint;
System altered.
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 INACTIVE 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 INACTIVE 50
5 1 3 INACTIVE 50
6 1 2 CURRENT 50
7 2 3 INACTIVE 50
8 2 2 CURRENT 50
8 rows selected.
3)最后删除 5号日志组和7号日志组的成员
SYS@RACDB1> alter database drop logfile member '+DATA/racdb/onlinelog/group_5.272.926535655';
alter database drop logfile member '+DATA/racdb/onlinelog/group_7.270.926535251';
Database altered.
SYS@RACDB1>
Database altered.
4) 验证
SYS@RACDB1>select group#,thread#,members,status,bytes/1024/1024 size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 2 INACTIVE 50
2 1 2 INACTIVE 50
3 2 2 INACTIVE 50
4 2 2 INACTIVE 50
5 1 2 INACTIVE 50
6 1 2 CURRENT 50
7 2 2 INACTIVE 50
8 2 2 CURRENT 50
8 rows selected.
SYS@RACDB1>select GROUP#,MEMBER,STATUS from v$logfile order by 2;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATA/racdb/onlinelog/redo01_a.log
2 +DATA/racdb/onlinelog/redo02_a.log
3 +DATA/racdb/onlinelog/redo03_a.log
4 +DATA/racdb/onlinelog/redo04_a.log
5 +DATA/racdb/onlinelog/redo05_a.log
6 +DATA/racdb/onlinelog/redo06_a.log
7 +DATA/racdb/onlinelog/redo07_a.log
8 +DATA/racdb/onlinelog/redo08_a.log
1 +FRA/racdb/onlinelog/redo01_b.log
2 +FRA/racdb/onlinelog/redo02_b.log
3 +FRA/racdb/onlinelog/redo03_b.log
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
4 +FRA/racdb/onlinelog/redo04_b.log
5 +FRA/racdb/onlinelog/redo05_b.log
6 +FRA/racdb/onlinelog/redo06_b.log
7 +FRA/racdb/onlinelog/redo07_b.log
8 +FRA/racdb/onlinelog/redo08_b.log
16 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31397003/viewspace-2128193/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2128193/