精华内容
下载资源
问答
  • 查看Oracle的redo日志切换频率 两条SQL,原理是一样的,第二个用到了统计函数 时间单位:分钟 方法一、 select * from vKaTeX parse error: Expected 'EOF', got '#' at position 21: … where a.THREAD#̲ = 1 ; ...

    DB: 11.2.0.3.0

    查看Oracle的redo日志切换频率

    两条SQL,原理是一样的,第二个用到了统计函数

    时间单位:分钟

    方法一、
    select * from vKaTeX parse error: Expected 'EOF', got '#' at position 21: … where a.THREAD#̲ = 1 ; set line…log_history a, v$log_history b where a.SEQUENCE#=b.SEQUENCE#+1 and b.THREAD#=1 order by a.SEQUENCE# desc;

    方法二、

    set line 200
    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;

    根据oracle官方给出的,一般日志切换在一个小时7次,日志组建议5个组,如果切换还是很频繁建议加大日志组的大小。

    法二

    最近发现日志服务器切换redo log太频繁了,大概一分钟一次,如此过于
    频繁也不是个办法,
    于是加大日志大小和增加日志组的数量来解决。

    另外除了日志切换频繁以外,还有Thread 1 cannot allocate new log、
    Checkpoint not complete错误提示
    获知关于"Thread 1 cannot allocate new log"警告提示的解释:

    一旦发生"Thread 1 cannot allocate new log",表明系统的checkpoint
    没有来得及完成,也就是说 buffer cache 中的dirty data还没有完全写
    到数据文件,就已经有大量的日志需要写入到系统。而系统只能通知应用:
    checkpoint 还没有完成,你只能等待。这个时候,系统就基本处于hang
    状态了 When the database waits on checkpoints,redo generation is
    stopped until the log switch is done
    下面我们来看看日志的实时刷新信息

    [root@radius ~]# tail -f ORACLEBASE/admin/ORACLE_BASE/admin/ORACLE_SID/bdump/
    alert_$ORACLE_SID.log

    Thu Jan 6 22:26:35 2011
    Thread 1 advanced to log sequence 151078 (LGWR switch)
    Current log# 3 seq# 151078 mem# 0: /ora/oradata/radius/redo03.log
    Thu Jan 6 22:27:08 2011
    Thread 1 cannot allocate new log, sequence 151079
    Checkpoint not complete
    Current log# 3 seq# 151078 mem# 0: /ora/oradata/radius/redo03.log
    Thu Jan 6 22:27:12 2011
    Thread 1 advanced to log sequence 151079 (LGWR switch)
    Current log# 1 seq# 151079 mem# 0: /ora/oradata/radius/redo01.log
    Thu Jan 6 22:27:45 2011
    Thread 1 cannot allocate new log, sequence 151080
    Checkpoint not complete
    Current log# 1 seq# 151079 mem# 0: /ora/oradata/radius/redo01.log
    可以看出切换日志间隔不到一分钟,增加日志组容量和数据刻不容缓呐

    1.查询下当前redo log 情况
    SQL> select group#,status,archived,bytes/1024/1024 from v$log;
    GROUP# STATUS ARC BYTES/1024/1024


    1 INACTIVE YES 50
    2 CURRENT NO 50
    3 UNUSED YES 50
    可以看出有3组50M redo log

    2.根据目前的切换日志的频率,决定将日志组增加至5组,每组200M.
    SQL> alter database add logfile group 4 ‘/ora/oradata/radius/
    redo04.log’ size 200m;
    Database altered.
    SQL> alter database add logfile group 5 ‘/ora/oradata/radius/
    redo05.log’ size 200m;
    Database altered.
    SQL> alter database add logfile group 6 ‘/ora/oradata/radius/
    redo06.log’ size 200m;
    Database altered.
    SQL> alter database add logfile group 7 ‘/ora/oradata/radius/
    redo07.log’ size 200m;
    Database altered.
    SQL> alter database add logfile group 8 ‘/ora/oradata/radius/
    redo08.log’ size 200m;
    Database altered.
    查询现在redo log状态

    SQL> select group#,status,archived,bytes/1024/1024 from v$log;
    GROUP# STATUS ARC BYTES/1024/1024


    1 INACTIVE YES 50
    2 ACTIVE YES 50
    3 CURRENT NO 50
    4 UNUSED YES 200
    5 UNUSED YES 200
    6 UNUSED YES 200
    7 UNUSED YES 200
    8 UNUSED YES 200
    3.删除之前的三个50M的redo log组
    由于当前日志还在group# 3 redo log上,所以需要切换日志到其他的日志组中去.

    SQL> alter system switch logfile;
    System altered.
    SQL> select group#,status,archived,bytes/1024/1024 from v$log;
    GROUP# STATUS ARC BYTES/1024/1024


    1 INACTIVE YES 50
    2 ACTIVE YES 50
    3 ACTIVE YES 50
    4 CURRENT NO 200
    5 UNUSED YES 200
    6 UNUSED YES 200
    7 UNUSED YES 200
    8 UNUSED YES 200

    从上面的status字段可以看出group# 2,3 redo log 的状态均是ACTIVE,
    也就是内存中的脏数据还没有写到数据文件中,这时oracle是不允许你删除的,
    如果你硬要删除会出现下面这个提示

    SQL> alter database drop logfile group 2;
    alter database drop logfile group 2
    *
    ERROR at line 1:
    ORA-01624: log 2 needed for crash recovery of instance radius (thread 1)
    ORA-00312: online log 2 thread 1: ‘/ora/oradata/radius/redo02.log’

    如果你需要马上删除这个redo log ,你可以使用checkpoint来将脏数据写
    进数据文件(磁盘)中,之后再将group# 1,2,3的redo log一一删除。

    SQL> alter system checkpoint;
    System altered.
    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.
    查看当前redo log状态

    SQL> select group#,status,archived,bytes/1024/1024 from v$log;
    GROUP# STATUS ARC BYTES/1024/1024


    4 CURRENT NO 200
    5 INACTIVE YES 200
    6 INACTIVE YES 200
    7 INACTIVE YES 200
    8 ACTIVE YES 200
    4.删除不可用的redo log
    使用Linux命令删除之。
    5.每天日志switch数量的查询方法

    select to_char(first_time,‘yyyy-mm-dd’) day1,count(*)
    from v$log_history where first_time>=
    to_date(‘2011-01-11’,‘yyyy-mm-dd’)
    group by to_char(first_time,‘yyyy-mm-dd’);

    展开全文
  • 查看Oracle的redo日志切换频率: 单位:分钟 第一种: select * from v$log a where a.THREAD# =...

    查看Oracle的redo日志切换频率:

    单位:分钟

    第一种:

    select * from v$log a where a.THREAD# = 1 ;

    set line 200

    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;


    第二种:

    set line 200

    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;


    看你的redo日志大小。
    Oracle给的建议是一般15-20分钟左右,要根据数据量来判断,最长时间(单位时间内数据量最少)维持在25-30分钟,最好不要超过30分钟。最短时间(单位时间内数据量最大),保持在10分钟以上。



    SELECT  trunc(first_time) "Date",

            to_char(first_time, 'Dy') "Day",

            count(1) "Total",

            SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",

            SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",

            SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",

            SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",

            SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",

            SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",

            SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",

            SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",

            SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",

            SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",

            SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",

            SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",

            SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",

            SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",

            SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",

            SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",

            SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",

            SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",

            SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",

            SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",

            SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",

            SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",

            SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",

            SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"

    FROM    V$log_history

    group by trunc(first_time), to_char(first_time, 'Dy')

    Order by 1

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

    转载于:http://blog.itpub.net/22996654/viewspace-2158307/

    展开全文
  • ORACLE UNDO REDO查看

    2013-08-17 23:32:00
    REDO日志分析: 注意:必须将所有脚本在同一窗口执行。 1. 创建路径: mkdir /usr/local/dbs chown oracle:oinstall /usr/local/dbs 2. 修改数据库参数: alter system set utl_file_di...

    UNDO表空间:

    select * from flashback_transaction_query;

    REDO日志分析:

    注意:必须将所有的脚本在同一窗口执行。

    1. 创建路径:

    mkdir /usr/local/dbs chown oracle:oinstall /usr/local/dbs

    2. 修改数据库参数: alter system set utl_file_dir='/usr/local/dbs' scope=spfile;

    3. 重启数据库

    4. 创建logmnr文件: BEGIN

    dbms_logmnr_d.build(

    dictionary_filename => 'logminer_dict.dat',

    dictionary_location => '/usr/local/dbs');

    END;

    /

    5. 添加日志文件:

    execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,logfilename =>'/u01/app/oracle/oradata/orcl11g/redo01.log');

    execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename =>'/u01/app/oracle/oradata/orcl11g/redo02.log');

    execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename =>'/u01/app/oracle/oradata/orcl11g/redo03.log');

    6. 启动logmnr:

    a. 无条件: BEGIN

    dbms_logmnr.start_logmnr(

    dictfilename => '/usr/local/dbs/logminer_dict.dat');

    END;

    /

    b. 有时间条件:

    begin

    dbms_logmnr.start_logmnr(

    dictFileName => '/usr/local/dbs/logminer_dict.dat',

    StartTime => to_date('2011-8-9 13:00:00','YYYY-MM-DD HH24:MI:SS'),

    EndTime => to_date('2011-8-9 13:22:00','YYYY-MM-DD HH24:MI:SS'));

    end;

    /

    c. 有SCN条件:

    begin

    dbms_logmnr.start_logmnr(
    DictFileName => '/usr/local/dbs/logminer_dict.dat',
    StartScn => 20,
    EndScn => 50)

    end;

    /
    7. 查询结果: select sql_redo FROM v$logmnr_contents;

    转载于:https://www.cnblogs.com/weixun/p/3265488.html

    展开全文
  • 如下: 先登录oracle sql plus 用户@ip 关闭redolog: alter database noarchivelog 开启: alter database archivelog 查看archive状态 archive log list

    如下:
    先登录oracle sql plus 用户@ip

    关闭redolog: alter database noarchivelog

    开启: alter database archivelog

    查看archive状态 archive log list

    展开全文
  • OracleREDO日志

    2016-06-30 15:45:57
    Oracle的数据库日志称为redo log,所有数据改变都记录redo log,可以用于修复受损的数据库。Redo日志是分组的,,默认是三组。Redo日志是轮流使用的,一个redo log满了,LGWR会切换到...1.查看系统的redo log信息: ...
  • Oracle数据库中,redo文件block大小(blocksize)是跟平台相关,因此redo文件block size是无法改变 至于如何查看redo文件blocksize,有两种方法: 1.直接查询v$lo...
  • ORACLERedo日志文件丢失恢复 目录: 当前Redo日志丢失恢复 非当前Redo日志丢失恢复 一、当前Redo日志丢失恢复 数据库open状态下: 常规方法,只能通过热备份所有数据文件来恢复,因为数据库已经不一致。 ...
  • oracle-redo日志分析

    2020-08-18 17:13:09
    添加要查看的日志文件 exec dbms_logmnr.add_logfile(logfilename=>'/opt/oracle/app/oradata/orcl/redo01.log',options=>dbms_logmnr.new); exec dbms_logmnr.add_logfile(logfilename=>'/opt/oracle/app...
  • Oracle增大redo log file方法

    千次阅读 2012-04-29 22:22:32
    Oracle 10g,RAC环境,AIX操作系统,原来系统的redo文件为,每个节点5组,每组1个成员,每组的文件大小为250M。 在系统繁忙时间,因为redo日志切换过于频繁,11秒到35秒左右切换一次,alert会有Can not allocate ...
  • Oracleredo分析

    2012-05-22 22:20:44
    作为一名开发人员,应该能够测量你的操作生成了多少redo,...任何Oracle实例都只有一个LGWR,最终所有事务都会归于LGWR,要求这个进程管理它们的redo,并BOMMIT其事务,LGWR要做的越多,系统就会越慢。通过查看一个...
  • DB: 11.2.0.3.0 查看Oracle的redo日志切换频率 两条SQL,原理是一样的,第二个用到了统计函数 时间单位:分钟 方法一、 select * from v$log ...
  • oracle——redo日志

    2017-11-30 11:29:27
    查看重做日志按组排序:select * from v$logfile order by GROUP#; 1.创建重做日志及成员 alter database database_name add logfile group n filename size m 参数意义:  database_name //需要修改数据库名,...
  • 目的:修改当前在线日志从默认50M增加至512M。...1.查看当前日志组状态 SQL> select group#,members,bytes/1024/1024,status from v$log; GROUP# MEMBERS BYTES/1024/1024 STATUS ---------- ---------- -...
  • Oracle REDO损坏

    2017-05-14 14:55:44
    Oracle REDO损坏 一:通过警告日志alert_PROD2.log定位损坏的redolog名称和所属日志组; 二:通过v$log查看损坏redolog状态; ...
  • oracle 查看操作生成redo、undo

    千次阅读 2013-08-05 22:54:53
    查看redo 可以 通过两种方式查看redo 1、统计信息 set autotrace traceonly statistics; --只能查看部分操作产生的redo大小 2、通过自定义一个函数
  • 最近发现数据库存在日志频繁切换,查看了才发现redo log日志太小,进行在线调整。 1.查看当前日志组成员 SQL> select member from v$logfile; MEMBER -----...
  • Oracle 11g redolog默认大小为50M, 在一些业务场景下会出现报错"Checkpoint not complete"。可以观察redo log file切换时间,考虑增加文件大小,如果 redo log文件太大也可能会造成数据丢失风险。 查看redo log ...
  • 查看redolog状态 $ sqlplus / as sysdba SQL> select GROUP#, STATUS from v$log; GROUP# STATUS ---------- ---------------- 1 CLEARING 3 INACTIVE 2 CURRENT 在redolog都丢失情况下,使用...

空空如也

空空如也

1 2 3 4 5 ... 15
收藏数 285
精华内容 114
关键字:

查看oracle的redo