• 【ORACLE】oracle 日志文件管理


    修改Oracle重做日志文件大小

    创建新的日志组1
    
    删除旧的日志组0(旧的日志组状态需要是INACTIVE)
    
    创建新的日志组2,组名为旧的日志组0的组名

    删除日志组1

      

    ----------------------------------------------

    具体操作步骤:

    ----------------------------------------------

    1. 创建3个新的日志组

    ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo06.log') SIZE 500M;
    
    ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') SIZE 500M;
    
    ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') SIZE 500M; 
    

      

    查看日志组

    select * from v$logfile;
    

      

    2.切换当前日志到新的日志组

    alter system switch logfile;
    
    alter system switch logfile;
    
    alter system switch logfile; 
    

      

    切到要删除为INCACTIVE才行 

    select group#,sequence#,bytes,members,status from v$log;
    

      

    GROUP#  SEQUENCE#    BYTES    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ----------------
      1  139 52428800  1 INACTIVE
      2  137 52428800  1 INACTIVE
      3  138 52428800  1 INACTIVE
      4  140  524288000  1 ACTIVE
      5  141  524288000  1 CURRENT
      6  136  524288000  1 INACTIVE

    3. 删除旧的日志组

    alter database drop logfile group 1;
    

      

    Database altered.

    4. 查看是否删除了日志组

    select group#,sequence#,bytes,members,status from v$log;
    

      

    GROUP#  SEQUENCE#    BYTES    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ----------------
      4  140  524288000  1 INACTIVE
      5  141  524288000  1 CURRENT
      6  136  524288000  1 INACTIVE

    5. 操作系统删除原日志组1、2、3中的文件 

    [oracle@oracle122 log]$ cd /u01/app/oracle/oradata/orcl
    [oracle@oracle122 orcl]$ pwd
    /u01/app/oracle/oradata/orcl
    [oracle@oracle122 orcl]$ ls
    control01.ctl  example01.dbf  redo01.log  redo02.log  redo03.log  redo04.log  redo05.log  redo06.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
    [oracle@oracle122 orcl]$ rm -rf redo01.log 
    [oracle@oracle122 orcl]$ rm -rf redo02.log 
    [oracle@oracle122 orcl]$ rm -rf redo03.log 
    

      

    6. 重建日志组 1、2、3

    ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 500M;
    
    ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 500M;
    
    ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 500M; 
    

      

    7.  删除中间过渡用的日志组4、5、6 ,在删除时查询select group#,sequence#,bytes,members,status from v$log; 删除组是否为INACTIVE,如果不是INACTIVE 就要执行alter system switch logfile;

    查询看一下

    select group#,sequence#,bytes,members,status from v$log;
    

      

    GROUP#  SEQUENCE#    BYTES    MEMBERS STATUS

    ---------- ---------- ---------- ---------- ----------------
      1  148  524288000  1 CURRENT
      2  143  524288000  1 INACTIVE
      3  144  524288000  1 INACTIVE
      4  146  524288000  1 INACTIVE
      5  147  524288000  1 INACTIVE
      6  145  524288000  1 INACTIVE 

    SQL> alter database drop logfile group 4;
    
    Database altered.
    
    SQL> alter database drop logfile group 5;
    
    Database altered.
    
    SQL> alter database drop logfile group 6;
    
    Database altered.
    

      

    8. 到操作系统删除组4、5、6

    [oracle@oracle122 orcl]$ rm -rf redo04.log 
    [oracle@oracle122 orcl]$ rm -rf redo05.log 
    [oracle@oracle122 orcl]$ rm -rf redo06.log 
    

      

    9. 备份当前最新的控制文件

    alter database backup controlfile to trace resetlogs  ;
    

      

  • 相关阅读:
    学习笔记—Node之读取流的使用与实现
    macbook双系统用U盘离线安装 win10
    CF1634E Fair Share
    CF1632E Distance Tree
    CF1630D Flipping Range
    K8S部署之VMWare网络拓扑踩坑
    ArrayList中的遍历删除
    postgresql提权
    Sqlite数据库的Update知多少?
    C#屏蔽系统热键Ctrl+Alt+Delete的代码尝试。(已修改bug)
  • 原文地址:https://www.cnblogs.com/DBA-Ivan/p/9962805.html
Copyright © 2020-2023  润新知