oracle 日志优化

 

SELECT * FROM v$archived_log ;

SELECT * FROM v$log ;

SELECT * FROM v$logfile ;

 

查询redolong的切换频率：

方法1：

select b.SEQUENCE#, b.FIRST_TIME,a.SEQUENCE#,a.FIRST_TIME,round(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) from v$log_history a, v$log_history b where a.SEQUENCE#=b.SEQUENCE#+1 and b.THREAD#=1 order by a.SEQUENCE# desc;

 

方法2：

select sequence#,first_time,nexttime,round(((first_time-nexttime)*24)*60,2) diff from (select sequence#,first_time,lag(first_time) over(order by sequence#) nexttime from v$log_history where thread#=1) order by sequence# desc;

 

如果切换频率过高（建议15~20分钟切换一次），要扩大logfile的大小：

一、执行 SELECT * FROM v$logfile ;查看现有的logfile信息

 
  

二、新建根据现有的group#的最大值，继续创建你需要创建的新的日志组文件

 

ALTER DATABASE ADD logfile GROUP 12 '/opt/oracle/oradata/orcl/redo12.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 13 '/opt/oracle/oradata/orcl/redo13.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 14 '/opt/oracle/oradata/orcl/redo14.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 15 '/opt/oracle/oradata/orcl/redo15.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 16 '/opt/oracle/oradata/orcl/redo16.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 17 '/opt/oracle/oradata/orcl/redo17.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 18 '/opt/oracle/oradata/orcl/redo18.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 19 '/opt/oracle/oradata/orcl/redo19.log' SIZE 5120m ;

 ALTER DATABASE ADD logfile GROUP 20 '/opt/oracle/oradata/orcl/redo20.log' SIZE 5120m ;

执行 SELECT * FROM v$logfile ; 确认是否新建成功：

 

 

 三、删除当前的logfile

删除前执行 SELECT * FROM v$log ;确认日志状态；是INACTIVE。

 

ALTER database DROP logfile group 8;

ALTER database DROP logfile group 9;

ALTER database DROP logfile group 10;

ALTER database DROP logfile group 11;

 

 四、切换当前日志

ALTER system switch logfile ;

alter system checkpoint;