005.如何修改redo大小？

1.查询redolog信息

1.1日志状态信息

select group#,
       thread#,
       sequence#,
       bytes / 1024 / 1024 size_MB,
       members,
       archived,
       status,
       first_change#,
       to_char(FIRST_TIME, 'yyyy-mm-dd hh24:mi:ss') first_time
  from v$log;

     GROUP#    THREAD#  SEQUENCE#    SIZE_MB    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1         28        100          2 YES CLEARING_CURRENT       1961831 2021-02-25 11:05:38
         3          1         27        100          2 YES CLEARING               1841509 2021-02-25 11:03:34
         2          1         28        100          2 YES CLEARING               1961831 2021-02-25 11:03:38

1.2日志文件信息

set line 199 pagesize 99
col member for a80
select a.group#,
       a.thread#,
       b.member,
       a.bytes / 1024 / 1024 size_MB,
       a.members,
       a.archived,
       a.status
  from v$log a, v$logfile b
 where a.group# = b.group#;

    GROUP#    THREAD# MEMBER                                                SIZE_MB    MEMBERS ARC STATUS
---------- ---------- -------------------------------------------------- ---------- ---------- --- ----------------
         1          1 /u01/app/oracle/oradata/PROD/disk1/redo01.log             100          2 YES CLEARING_CURRENT
         1          1 /u01/app/oracle/oradata/PROD/disk1/redo04.log             100          2 YES CLEARING_CURRENT
         3          1 /u01/app/oracle/oradata/PROD/disk1/redo06.log             100          2 YES CLEARING
         2          1 /u01/app/oracle/oradata/PROD/disk1/redo05.log             100          2 YES CLEARING
         3          1 /u01/app/oracle/oradata/PROD/disk1/redo03.log             100          2 YES CLEARING
         2          1 /u01/app/oracle/oradata/PROD/disk1/redo02.log             100          2 YES CLEARING

2.添加日志组

2.1单实例添加

添加时确保文件系统空间满足要求

alter database add logfile group 4 ('/u01/app/oracle/oradata/redolog/redo04a.log','/u01/app/oracle/oradata/redolog/redo04b.log') size 20M;   

2.2集群添加

添加时确保asm磁盘空间满足要求

节点1

alter database add logfile thread 1 group 21 ('+DATA') size 2048m;

节点2

alter database add logfile thread 2 group 22 ('+DATA') size 2048m;

3.删除日志组

3.1检查日志组状态

检查日志组状态(status为INACTIVE或UNUSED的才可以删除)

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

    GROUP#    THREAD#  SEQUENCE#    SIZE_MB    MEMBERS ARC STATUS          
---------- ---------- ---------- ---------- ---------- --- ----------------
         1          1         31        100          2 YES INACTIVE        
         2          1         32        100          2 NO  CURRENT         
         3          1         30        100          2 YES INACTIVE        
         4          1          0         20          2 YES UNUSED          
         5          1          0         20          2 YES UNUSED          

3.2 删除日志组

alter database drop logfile group 1;

如果日志组都是CURRENT或ACTIVE的状态，做以下处理

可以切换日志组,让日志切换到下一组

alter system switch logfile;

或者对数据库做个checkpoint，提交所有日志，这样可以让active的日志组变为INACTIVE

alter system checkpoint;

然后再删除日志组

alter database drop logfile group 1;

set timing on
set time on
set lines 168 pages 200
set COLSEP '|'
col id for 99
col sid for a12
col PID for a8
col USR for a12
col program for a30
col ACTN for a9
col OPN for a3
col state for a18
col status for a9
col SQLID for a13
col event for a30
col wait for a4
col wait_time for 999999999
SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */ /*+ NO_MONITOR */
 s.sid||','||s.SERIAL# sid,
 p.spid pid,
 decode(p.BACKGROUND, 1, 'B/G', decode(s.username, NULL, 'F/G', s.username)) usr,
 substr(s.program,1,30) program,
 decode(COMMAND,3,'SEL',2,'INT',6,'UPD',7,'DEL','OTH') OPN,
 DECODE(s.PLSQL_SUBPROGRAM_ID, NULL, s.SQL_ID, NULL) SQLID,
 s.status,
 s.STATE,
 substr(s.wait_class,1,4) wait,
 case when s.state = 'WAITING' then SECONDS_IN_WAIT
      when s.state in ('WAITED SHORT TIME','WAIT UNKNOW TIME') then null
      when s.state = 'WAITING KNOWN TIME' then wait_time
 else s.SECONDS_IN_WAIT end wait_time ,
 substr(event,1,30) event
  from v$session s,v$process p
where p.addr = s.paddr;