ORACLE 11G RAC 增加日志组及增大日志文件

最新推荐文章于 2022-04-29 21:50:06 发布
最新推荐文章于 2022-04-29 21:50:06 发布
阅读量1.5k 收藏 1
点赞数
分类专栏： oracle
原文链接：https://blog.csdn.net/cuishan5183/article/details/100403333?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.noneca
版权

1、查看目前日志组和日志文件情况

SQL> select * from v$logfile order by 1;

GROUP# STATUS  TYPE    MEMBER                                             IS_

     1         ONLINE  +FRA/stone/onlinelog/group_1.257.884819615         YES
     1         ONLINE  +DATA/stone/onlinelog/group_1.261.884819613        NO
     2         ONLINE  +FRA/stone/onlinelog/group_2.258.884819623         YES
     2         ONLINE  +DATA/stone/onlinelog/group_2.262.884819619        NO
     3         ONLINE  +DATA/stone/onlinelog/group_3.266.884820745        NO
     3         ONLINE  +FRA/stone/onlinelog/group_3.259.884820749         YES
     4         ONLINE  +DATA/stone/onlinelog/group_4.267.884820755        NO
     4         ONLINE  +FRA/stone/onlinelog/group_4.260.884820759         YES

SQL> select group#,thread#,bytes/1024/1024,archived,status,members from v$log;

GROUP#    THREAD# BYTES/1024/1024 ARC STATUS              MEMBERS

     1          1              50 NO  CURRENT                   2
     2          1              50 YES INACTIVE                  2
     3          2              50 YES INACTIVE                  2
     4          2              50 NO  CURRENT                   2

可以看到目前是2个节点，每个节点2组日志，每组日志2个日志文件，大小均为50M，分别位于+DATA和+FRA磁盘组。

2、查看日志组切换频率和归档日志生成量

SQL> select b.SEQUENCE#, b.FIRST_TIME,a.SEQUENCE#,a.FIRST_TIME,round(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) from v l o g h i s t o r y a , v log_history a, v logh​istorya,vlog_history b where a.SEQUENCE#=b.SEQUENCE#+1 and b.THREAD#=1 order by a.SEQUENCE# desc;

SEQUENCE# FIRST_TIM SEQUENCE# FIRST_TIM ROUND(((A.FIRST_TIME-B.FIRST_TIME)*24)*60,2)

    41 15-SEP-15         42 15-SEP-15                                          .43
    40 15-SEP-15         41 15-SEP-15                                          .42
    39 15-SEP-15         40 15-SEP-15                                          .53
    38 15-SEP-15         39 15-SEP-15                                          .22
    37 15-SEP-15         38 15-SEP-15                                          .18
    36 15-SEP-15         37 15-SEP-15                                        29.93
    35 22-JUL-15         36 15-SEP-15                                     79165.62

SQL> select trunc(completion_time),sum(mb)/1024 day_gb from(select name,completion_time,blocks*block_size /1024 /1024 mb from v$archived_log) group by trunc(completion_time) order by (trunc(completion_time));

TRUNC(COM DAY_GB

11-JUL-15 .048826694
12-JUL-15 .170285702
13-JUL-15 .141009331
14-JUL-15 .135242462
15-JUL-15 .153475761
16-JUL-15 .09009409
17-JUL-15 .031218529
18-JUL-15 .084494114
20-JUL-15 .088860512
21-JUL-15 .011713982
22-JUL-15 .106176376

TRUNC(COM DAY_GB

15-SEP-15 .37887764

12 rows selected.

根据以上结果进行分析，系统需要的重做日志组数量和重做日志文件大小。也可以简单通过一天的归档日志量平均到每小时，如每小时是400M的归档日志量，如果15分钟切换一次的话，那么就设置日志文件大小为100M。
可以通过告警日志查看是否出现LGWR等待，如果有，则需要增加重做日志组。

3、增加日志组及增大日志文件
（1）备份参数文件和控制文件
[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Sep 15 15:44:54 2015

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: STONE (DBID=2995045972)

RMAN> backup current controlfile;

Starting backup at 15-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 instance=stone1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 15-SEP-15
channel ORA_DISK_1: finished piece 1 at 15-SEP-15
piece handle=+FRA/stone/backupset/2015_09_15/ncnnf0_tag20150915t154735_0.330.890495259 tag=TAG20150915T154735 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-SEP-15

RMAN> backup spfile;

Starting backup at 15-SEP-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 15-SEP-15
channel ORA_DISK_1: finished piece 1 at 15-SEP-15
piece handle=+FRA/stone/backupset/2015_09_15/nnsnf0_tag20150915t154800_0.331.890495281 tag=TAG20150915T154800 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-SEP-15

或者：
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 15 15:48:48 2015

Copyright © 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

SQL> alter database backup controlfile to ‘/home/oracle/control.ctl’;

Database altered.

SQL> alter database backup controlfile to trace as ‘/home/oracle/control.trc’;

Database altered.

SQL> create pfile=’/home/oracle/pfile.ora’ from spfile;

File created.

（2）增加日志组和日志文件

SQL> alter database add logfile thread 1 group 5 size 100M;

Database altered.

SQL> alter database add logfile thread 1 group 6 size 100M;

Database altered.

SQL> alter database add logfile thread 1 group 7 size 100M;

Database altered.

SQL> alter database add logfile thread 2 group 8 size 100M;

Database altered.

SQL> alter database add logfile thread 2 group 9 size 100M;

Database altered.

SQL> alter database add logfile thread 2 group 10 size 100M;

Database altered.

SQL> select group#,thread#,bytes/1024/1024,archived,status,members from v$log;

GROUP#    THREAD# BYTES/1024/1024 ARC STATUS              MEMBERS

     1          1              50 NO  CURRENT                   2
     2          1              50 YES INACTIVE                  2
     3          2              50 YES INACTIVE                  2
     4          2              50 NO  CURRENT                   2
     5          1             100 YES UNUSED                    2
     6          1             100 YES UNUSED                    2
     7          1             100 YES UNUSED                    2
     8          2             100 YES UNUSED                    2
     9          2             100 YES UNUSED                    2
    10          2             100 YES UNUSED                    2

10 rows selected.

SQL> select * from v$logfile order by 1;

GROUP# STATUS  TYPE    MEMBER                                             IS_

     1         ONLINE  +DATA/stone/onlinelog/group_1.261.884819613        NO
     1         ONLINE  +FRA/stone/onlinelog/group_1.257.884819615         YES
     2         ONLINE  +DATA/stone/onlinelog/group_2.262.884819619        NO
     2         ONLINE  +FRA/stone/onlinelog/group_2.258.884819623         YES
     3         ONLINE  +DATA/stone/onlinelog/group_3.266.884820745        NO
     3         ONLINE  +FRA/stone/onlinelog/group_3.259.884820749         YES
     4         ONLINE  +DATA/stone/onlinelog/group_4.267.884820755        NO
     4         ONLINE  +FRA/stone/onlinelog/group_4.260.884820759         YES
     5         ONLINE  +DATA/stone/onlinelog/group_5.270.890496635        NO
     5         ONLINE  +FRA/stone/onlinelog/group_5.333.890496637         YES
     6         ONLINE  +DATA/stone/onlinelog/group_6.269.890496649        NO

GROUP# STATUS  TYPE    MEMBER                                             IS_

     6         ONLINE  +FRA/stone/onlinelog/group_6.332.890496651         YES
     7         ONLINE  +DATA/stone/onlinelog/group_7.271.890496663        NO
     7         ONLINE  +FRA/stone/onlinelog/group_7.334.890496665         YES
     8         ONLINE  +DATA/stone/onlinelog/group_8.272.890496679        NO
     8         ONLINE  +FRA/stone/onlinelog/group_8.335.890496683         YES
     9         ONLINE  +DATA/stone/onlinelog/group_9.273.890496703        NO
     9         ONLINE  +FRA/stone/onlinelog/group_9.336.890496705         YES
    10         ONLINE  +DATA/stone/onlinelog/group_10.274.890496717       NO
    10         ONLINE  +FRA/stone/onlinelog/group_10.337.890496719        YES

20 rows selected.

（3）删除原有日志组
切换日志组，使原有日志组处于INACTIVE状态。

SQL> alter system switch logfile;

System altered.

SQL> select group#,thread#,bytes/1024/1024,archived,status,members from v$log;

GROUP#    THREAD# BYTES/1024/1024 ARC STATUS              MEMBERS

     1          1              50 YES INACTIVE                  2
     2          1              50 YES INACTIVE                  2
     3          2              50 YES INACTIVE                  2
     4          2              50 YES INACTIVE                  2
     5          1             100 YES INACTIVE                  2
     6          1             100 NO  CURRENT                   2
     7          1             100 YES INACTIVE                  2
     8          2             100 YES INACTIVE                  2
     9          2             100 YES INACTIVE                  2
    10          2             100 NO  CURRENT                   2

10 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select group#,thread#,bytes/1024/1024,archived,status,members from v$log;

GROUP#    THREAD# BYTES/1024/1024 ARC STATUS              MEMBERS

     5          1             100 YES INACTIVE                  2
     6          1             100 NO  CURRENT                   2
     7          1             100 YES INACTIVE                  2
     8          2             100 YES INACTIVE                  2
     9          2             100 YES INACTIVE                  2
    10          2             100 NO  CURRENT                   2

6 rows selected.

（4）重新增加日志组

SQL> alter database add logfile thread 1 group 1 size 100M;

Database altered.

SQL> alter database add logfile thread 1 group 2 size 100M;

Database altered.

SQL> alter database add logfile thread 2 group 3 size 100M;

Database altered.

SQL> alter database add logfile thread 2 group 4 size 100M;

Database altered.

SQL> select group#,thread#,bytes/1024/1024,archived,status,members from v$log;

GROUP#    THREAD# BYTES/1024/1024 ARC STATUS              MEMBERS

     1          1             100 YES UNUSED                    2
     2          1             100 YES UNUSED                    2
     3          2             100 YES UNUSED                    2
     4          2             100 YES UNUSED                    2
     5          1             100 YES INACTIVE                  2
     6          1             100 NO  CURRENT                   2
     7          1             100 YES INACTIVE                  2
     8          2             100 YES INACTIVE                  2
     9          2             100 YES INACTIVE                  2
    10          2             100 NO  CURRENT                   2

10 rows selected.

SQL> select * from v$logfile order by 1;

GROUP# STATUS  TYPE    MEMBER                                             IS_

     1         ONLINE  +DATA/stone/onlinelog/group_1.267.890498177        NO
     1         ONLINE  +FRA/stone/onlinelog/group_1.260.890498179         YES
     2         ONLINE  +DATA/stone/onlinelog/group_2.266.890498187        NO
     2         ONLINE  +FRA/stone/onlinelog/group_2.259.890498191         YES
     3         ONLINE  +DATA/stone/onlinelog/group_3.262.890498205        NO
     3         ONLINE  +FRA/stone/onlinelog/group_3.258.890498207         YES
     4         ONLINE  +DATA/stone/onlinelog/group_4.261.890498225        NO
     4         ONLINE  +FRA/stone/onlinelog/group_4.257.890498229         YES
     5         ONLINE  +DATA/stone/onlinelog/group_5.270.890496635        NO
     5         ONLINE  +FRA/stone/onlinelog/group_5.333.890496637         YES
     6         ONLINE  +DATA/stone/onlinelog/group_6.269.890496649        NO

GROUP# STATUS  TYPE    MEMBER                                             IS_

     6         ONLINE  +FRA/stone/onlinelog/group_6.332.890496651         YES
     7         ONLINE  +DATA/stone/onlinelog/group_7.271.890496663        NO
     7         ONLINE  +FRA/stone/onlinelog/group_7.334.890496665         YES
     8         ONLINE  +DATA/stone/onlinelog/group_8.272.890496679        NO
     8         ONLINE  +FRA/stone/onlinelog/group_8.335.890496683         YES
     9         ONLINE  +DATA/stone/onlinelog/group_9.273.890496703        NO
     9         ONLINE  +FRA/stone/onlinelog/group_9.336.890496705         YES
    10         ONLINE  +DATA/stone/onlinelog/group_10.274.890496717       NO
    10         ONLINE  +FRA/stone/onlinelog/group_10.337.890496719        YES

20 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> select group#,thread#,bytes/1024/1024,archived,status,members from v$log;

GROUP#    THREAD# BYTES/1024/1024 ARC STATUS              MEMBERS

     1          1             100 YES ACTIVE                    2
     2          1             100 NO  CURRENT                   2
     3          2             100 NO  CURRENT                   2
     4          2             100 YES UNUSED                    2
     5          1             100 YES INACTIVE                  2
     6          1             100 YES ACTIVE                    2
     7          1             100 YES INACTIVE                  2
     8          2             100 YES INACTIVE                  2
     9          2             100 YES INACTIVE                  2
    10          2             100 YES ACTIVE                    2

10 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28536251/viewspace-1800592/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28536251/viewspace-1800592/

董小姐yyds
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle归档日志写满(ora-00257)了怎么办
12-16
### Oracle归档日志写满(ora-00257)解决办法 #### 问题背景 在Oracle数据库管理过程中，经常会遇到ORA-00257错误：“archiver error. Connect internal only, until freed”。这通常是因为归档日志空间不足导致的，...
RAC 添加和删除日志文件组
小楼一夜听春雨，深巷明朝卖杏花
08-16 1203
首先oracle 日志文件组有六种状态： 日志文件组的状态一般有INACTIVE、ACTIVE、CURRENT、UNUSED、CLEARING、CLEARING_CURRNT等六种状态： v$log 保存控制文件中的日志文件信息。 SQL> SELECT STATUS FROM V$LOG; UNUSED ： 表示该联机重做日志文件组对应的文件还从未被写入过数据，通常是刚刚创建的联机...
Oracle RAC 与 ADG 如何重建 Redo 日志组？
JiekeXu的博客
04-29 2874
作者 | JiekeXu来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）大家好，我是JiekeXu,很高兴又和大家见面了,今天和大家一起来看看Oracle RAC 与 ADG 如何重建 Redo 日志组？欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！随着业务数据量增长原来设置的 300M 大小 redo 日志组已经出现各种小问题，“...
oracle rac添加日志组,【rac】实验四：增加日志组
weixin_39587029的博客
04-04 750
1.准备工作1.1.查看日志组基本情况[oracle@rac1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production onSat Oct 29 18:46:28 2016Copyright (c) 1982, 2013, Oracle.All rights reserved.Connected to:Oracle Databa...
Oracle添加新的日志文件
weixin_43798842的博客
11-15 594
alter database add logfile member ‘C:database\oradata\orcl\redo4c.log’ to group 4;
kafka日志清理配置
wangzhicheng2013的专栏
09-14 938
server.properties log.cleanup.policy=delete #清理日志 log.retention.hours=1 #日志保留1h，1h后删除日志 log.segment.bytes=104857600 #日志的segment保留100M log.retention.check.interval.ms=30000 #5分钟检测日志情况
Oracle RAC安装图解
04-20
此外，需要修改内核参数以支持Oracle RAC，比如增大`kernel.shmall`、`kernel.shmmax`等，以及调整系统文件描述符限制和网络参数。 5. **用户和权限**：为了运行Oracle RAC，需要创建并配置`oracle`用户，包括修改`...
Oracle GoldenGate同步服务归档空间维护【推荐】
12-16
- 使用RMAN（Recovery Manager）删除不再需要的归档日志文件。在命令行输入RMAN并连接到目标数据库，执行`crosscheck backup`来检查备份集的完整性，然后删除过期的归档日志。`delete noprompt obsolete`命令会删除...
oracle 常见等待事件及处理方法
12-15
5. **log buffer space**：当redo log buffer的空间不足时，会发生此等待，需要检查日志写入速度是否跟上事务提交的速度，可能需要增大日志缓冲区大小或优化事务提交策略。 6. **log file switch**：数据库在切换...
Oracle 11g rac asm 添加删除日志组（修改日志组大小）
viviliving的专栏
01-19 585
Oracle 11g RAC 添加删除日志组（修改日志组大小） 1.查看日志文件位置 col status for a10 col type for a10 col member for a50 select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_REC ---------- ---------- ------...
oracle_dataguard下扩日志文件
weixin_47745154的博客
09-01 335
背景 dataguard下的将日志文件从50M 扩大到60M 主库上联机日志操作 主库增加过渡日志 2 组，大小1G左右 alter database add logfile thread 1 group 201 ('+DATA/orcl/onlinelog/redo201.log','+ARCH/orcl/onlinelog/redo201.log') size 50M; alter database add logfile thread 1 group 202 ('+DATA/o.
file 关闭_ORACLE开启/关闭归档模式
weixin_39818521的博客
11-27 103
归档模式所有的数据库操作都会以日志形式写入日志文件组，但当日志文件组写满后，会覆盖最早的日志文件组以写入新的日志文件，这会导致日志文件丢失。开启归档模式后，所有在覆盖最早的日志文件组时，会将其日志文件数据读取后，写入日志文件，以达到归档所有操作日志文件的作用。开启归档模式# 进入SQLPLUS模式 ...
【rac】实验三：增加日志组成员
congjiu2607的博客
11-09 344
1. 准备工作 1.1. 查看日志组基本情况 [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 P...
oracle rac 添加日志,【ORACLE RAC】日志组管理
weixin_35098081的博客
04-06 649
************************************************************************ ****原文： blog.csdn.net/clark_xu 徐长亮的专栏 ************************************************************************ rac需要至少需要四个日志组，分...
oracle 增加日志组和日志文件
大道至简 分而治之
12-07 6899
--查看日志文件组 select * from v$log; --查看日志文件 select * from v$logfile; --添加日志文件组 alter database add logfile group 4 ('D:\app\Administrator\oradata\orcl\REDO04.LOG') size 512m; alter database add logfile g
Oracle重建控制文件
zongzizz的博客
01-11 4333
重建控制文件前言一、重建原因二、重建步骤 前言 以下只是记录一次重建控制文件的过程 一、重建原因 在rman恢复数据库的过程中，碰到恢复结束时，做不完全恢复后，无法resetlogs打开数据库的情况，报错如下： 这种情况不能rename日志文件，也不能添加新的redo后再把旧的redo删除，所以只能选择重建控制文件了。 二、重建步骤 在mount状态下备份控制文件到/u01/control12.trc alter database backup controlfile to trace as '/u
Oracle集群管理 -重做日志管理
oradbm的博客
11-19 168
1在本实例添加日志组 alter database add logfile group 5 '+data01' size 1G; 2 在实例1为实例2添加重做日志组 alter database add logfile instance 'orcl2' group 6'+data01' size 1G; 3 切换日志 alter system switch logfile; alter system switch logfile; alter system archive lo...
参数切换thread测试
oradbm的博客
09-05 462
参数切换thread测试 ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO04.LOG' SIZE 100M; ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 'D:\APP\ADMINISTRATOR\ORADATA\O...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值