• 利用外部表查询alert日志中的ora错误


    SQL> show parameter dump

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    background_core_dump                 string      partial
    background_dump_dest                 string      /oracle/oracle/diag/rdbms/orcldb/orcldb1/trace
    core_dump_dest                       string      /oracle/oracle/diag/rdbms/orcldb/orcldb1/cdump
    max_dump_file_size                   string      unlimited
    shadow_core_dump                     string      partial
    user_dump_dest                       string      /oracle/oracle/diag/rdbms/orcldb/orcldb1/trace

    SQL> create directory alert as'/oracle/oracle/diag/rdbms/orcldb/orcldb1/trace';
    SQL> grant read,write on directory alert to public;
    Grant succeeded.

    SQL> desc dba_directories
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- ------------------------------------
     OWNER                                                 NOT NULL VARCHAR2(30)
     DIRECTORY_NAME                                        NOT NULL VARCHAR2(30)
     DIRECTORY_PATH                                                 VARCHAR2(4000)

    SQL> col OWNER for a30
    SQL> col DIRECTORY_NAME for a30
    SQL> col DIRECTORY_PATH for a100
    SQL> select * from dba_directories where DIRECTORY_NAME='ALERT';

    OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH

    SYS                            ALERT                          /oracle/oracle/diag/rdbms/orcldb/orcldb1/trace

    SQL> !
    [oracle@linux01 ~]$ cd /oracle/oracle/diag/rdbms/orcldb/orcldb1/trace
    [oracle@linux01 trace]$ ls
    alert_orcldb1.log             orcldb1_gen0_4646.trc   orcldb1_lms0_4701.trm  orcldb1_p000_4882.trc  orcldb1_rsmn_4802.trm
    orcldb1_asmb_4737.trc         orcldb1_gen0_4646.trm   orcldb1_lms1_4666.trc  orcldb1_p000_4882.trm  orcldb1_vkrm_10819.trc
    orcldb1_asmb_4737.trm         orcldb1_j000_22025.trc  orcldb1_lms1_4666.trm  orcldb1_p001_4884.trc  orcldb1_vkrm_10819.trm
    orcldb1_cjq0_4949.trc         orcldb1_j000_22025.trm  orcldb1_lms1_4705.trc  orcldb1_p001_4884.trm  orcldb1_vkrm_18720.trc
    orcldb1_cjq0_4949.trm         orcldb1_j005_4968.trc   orcldb1_lms1_4705.trm  orcldb1_p002_4886.trc  orcldb1_vkrm_18720.trm
    orcldb1_dbrm_4650.trc         orcldb1_j005_4968.trm   orcldb1_mark_4747.trc  orcldb1_p002_4886.trm  orcldb1_vkrm_18893.trc
    orcldb1_dbrm_4650.trm         orcldb1_lck0_4787.trc   orcldb1_mark_4747.trm  orcldb1_ping_4685.trc  orcldb1_vkrm_18893.trm
    orcldb1_dbrm_4683.trc         orcldb1_lck0_4787.trm   orcldb1_mman_4715.trc  orcldb1_ping_4685.trm  orcldb1_vktm_4675.trc
    orcldb1_dbrm_4683.trm         orcldb1_lmd0_4695.trc   orcldb1_mman_4715.trm  orcldb1_q001_4912.trc  orcldb1_vktm_4675.trm
    orcldb1_dbw0_4717.trc         orcldb1_lmd0_4695.trm   orcldb1_mmon_4690.trc  orcldb1_q001_4912.trm  orcldb1_w000_17178.trc
    orcldb1_dbw0_4717.trm         orcldb1_lmhb_4711.trc   orcldb1_mmon_4690.trm  orcldb1_qmnc_4908.trc  orcldb1_w000_17178.trm
    orcldb1_dia0_4691_base_1.trc  orcldb1_lmhb_4711.trm   orcldb1_mmon_4739.trc  orcldb1_qmnc_4908.trm  orcldb1_w001_20637.trc
    orcldb1_dia0_4691_base_1.trm  orcldb1_lmon_4693.trc   orcldb1_mmon_4739.trm  orcldb1_rcbg_4906.trc  orcldb1_w001_20637.trm
    orcldb1_dia0_4691.trc         orcldb1_lmon_4693.trm   orcldb1_ora_4610.trc   orcldb1_rcbg_4906.trm
    orcldb1_dia0_4691.trm         orcldb1_lms0_4662.trc   orcldb1_ora_4610.trm   orcldb1_rms0_4709.trc
    orcldb1_diag_4681.trc         orcldb1_lms0_4662.trm   orcldb1_ora_4804.trc   orcldb1_rms0_4709.trm
    orcldb1_diag_4681.trm         orcldb1_lms0_4701.trc   orcldb1_ora_4804.trm   orcldb1_rsmn_4802.trc

    [oracle@linux01 trace]$ exit
    SQL> create table db_alert
      2  (log varchar2(4000))
      3  organization external
      4  (type oracle_loader
      5   default directory alert
      6  access parameters
      7  (records delimited by newline
      8  nobadfile
      9  nodiscardfile
     10  nologfile
     11  )
     12  location ('alert_orcldb1.log'))
     13   reject limit unlimited;

    Table created.
    SQL>  select * from db_alert where log like '%ORA-%';

    LOG

    ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
    ORA-00313: open failed for members of log group 1 of thread 1
    ORA-00313: open failed for members of log group 1 of thread 1
    ORA-00313: open failed for members of log group 1 of thread 1
    ORA-00313: open failed for members of log group 2 of thread 1
    ORA-00313: open failed for members of log group 2 of thread 1
    ORA-00313: open failed for members of log group 2 of thread 1
    Non critical error ORA-48113 caught while writing to trace file "/oracle/oracle/diag/rdbms/orcldb/orcldb1/trace/orcldb1_dbrm_7968.trc"
    Non critical error ORA-48181 caught while writing to trace file "/oracle/oracle/diag/rdbms/orcldb/orcldb1/trace/orcldb1_ora_32461.trc"
    Non critical error ORA-48181 caught while writing to trace file "/oracle/oracle/diag/rdbms/orcldb/orclMon Mar 21 21:59:57 2016

    10 rows selected.

  • 相关阅读:
    keil 提示"running with code size limit 32k"
    关于C语言编译出现give arg types警告问题
    windows10添加设备管理器的快捷方式到桌面
    deepin20社区版 安装 STM32CubeIDE 小记
    STM32开发 printf和scanf函数的重定向——修改HAL标准库用printf函数发送数据直接输出
    ardupilot环境配置之eclipse指定jdk版本启动,解决“Version XXXX of the JVM is not ......"报错的问题
    jdk9,10,11,12没有jre安装方法
    C++ 类构造函数 & 析构函数
    STM32 Keil中关于stlink的调试 下载设置
    STM32 SWD下载出现no target connect解决方法
  • 原文地址:https://www.cnblogs.com/datalife/p/5649320.html
Copyright © 2020-2023  润新知