重庆思庄Oracle、Redhat认证学习论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 1270|回复: 0
打印 上一主题 下一主题

[原创] ORACLE 19c 使用内联外部表看报警日志内容

[复制链接]
跳转到指定楼层
楼主
发表于 2023-9-23 23:50:08 | 只看该作者 回帖奖励 |正序浏览 |阅读模式
本帖最后由 郑全 于 2023-9-23 23:52 编辑

比如想看PROD4库的报警文件,

1.建立PROD4实例的报警文件目录
  create directory trace_dir as '/u01/app/oracle/diag/rdbms/prod4/PROD4/trace';

2.使用内联外部表读出报警文件内容最近50条记录,最新的在最前面
  SYS@PROD4> WITH alert_tab AS (
  2    SELECT rownum rn,message
  3    FROM   EXTERNAL (
  4             (
  5              message varchar2(500)
  6             )
  7           TYPE ORACLE_LOADER
  8           DEFAULT DIRECTORY trace_dir
  9           ACCESS PARAMETERS
10           (
11                  FIELDS TERMINATED BY ','
12                  MISSING FIELD VALUES ARE NULL
13                  (message
14                   )
15           )
16           LOCATION ('alert_PROD4.log')
17           REJECT LIMIT UNLIMITED
18        )
19    order by rn desc
20  )
21  select message from alert_tab where rn <50;

MESSAGE
----------------------------------------------------------------------------------------------------
Capability Type : Engineered Systems
capabilities requested : 400000FF detected : 40000000 Simulated : 0
Capability Type : Runtime Environment
capabilities requested : 7 detected : 0 Simulated : 0
Capability Type : Network
Shared memory segment for instance monitoring created
Number of processor sockets in the system is 2
Number of processor cores in the system is 4
Initial number of CPU is 4
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_SESSION = 0

MESSAGE
----------------------------------------------------------------------------------------------------
**********************************************************************
2023-06-26T16:28:45.749527+08:00
to lock 100% of SHARED GLOBAL AREA (SGA) pages into physical memory
2. Increase per process memlock (soft) limit to at least 1202MB
2023-06-26T16:28:45.749455+08:00
instance restart operation.
of pages for every supported system pagesize prior to the next
1. For optimal performance
2023-06-26T16:28:45.745390+08:00
RECOMMENDATION:
2023-06-26T16:28:45.745357+08:00

MESSAGE
----------------------------------------------------------------------------------------------------
     2048K                0             601               0        NONE
2023-06-26T16:28:45.745322+08:00
        4K       Configured               3          307203        NONE
2023-06-26T16:28:45.745230+08:00
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2023-06-26T16:28:45.745190+08:00
Supported system pagesize(s):
2023-06-26T16:28:45.745156+08:00
  4K
Available system pagesizes:
2023-06-26T16:28:45.745088+08:00

MESSAGE
----------------------------------------------------------------------------------------------------
instance MAX SHARED GLOBAL AREA (SGA) into memory: 1202M
Expected per process system memlock (soft) limit to lock
2023-06-26T16:28:45.745012+08:00
Per process system memlock (soft) limit = 64K
2023-06-26T16:28:45.744956+08:00

Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
2023-06-26T16:28:45.744873+08:00
**********************************************************************
2023-06-26T16:28:45.744835+08:00
****************************************************

MESSAGE
----------------------------------------------------------------------------------------------------
Sys-V shared memory will be used for creating SGA
****************************************************
2023-06-26T16:28:45.744202+08:00
Starting ORACLE instance (normal) (OS id: 4314)
2023-06-26T16:28:45.739883+08:00

49 rows selected.

SYS@PROD4>

3.看最近1000条报警文件中,有ORA-开头的内容
   SYS@PROD4> WITH alert_tab AS (
  2    SELECT rownum rn,message
  3    FROM   EXTERNAL (
  4             (
  5              message varchar2(500)
  6             )
  7           TYPE ORACLE_LOADER
  8           DEFAULT DIRECTORY trace_dir
  9           ACCESS PARAMETERS
10           (
11                  FIELDS TERMINATED BY ','
12                  MISSING FIELD VALUES ARE NULL
13                  (message
14                   )
15           )
16           LOCATION ('alert_PROD4.log')
17           REJECT LIMIT UNLIMITED
18        )
19    order by rn desc
20  )
21  select message from alert_tab where rn <10000 and upper(message) like 'ORA-%';

MESSAGE
----------------------------------------------------------------------------------------------------
ORA-959 signalled during: alter tablespace trans_tbs read write...
ORA-27037: unable to obtain file status
ORA-01565: error in identifying file '/u01/app/oracle/oradata/PROD4/system01.dbf'
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD4/system01.dbf'
ORA-01122: database file 1 failed verification check
ORA-27037: unable to obtain file status
ORA-01565: error in identifying file '/u01/app/oracle/oradata/PROD4/system01.dbf'
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD4/system01.dbf'
ORA-27037: unable to obtain file status
ORA-00202: control file: '/u01/app/oracle/oradata/PROD4/control02.ctl'
ORA-205 signalled during: ALTER DATABASE   MOUNT...

MESSAGE
----------------------------------------------------------------------------------------------------
ORA-27037: unable to obtain file status
ORA-00202: control file: '/u01/app/oracle/oradata/PROD4/control02.ctl'
ORA-00210: cannot open the specified control file
ORA-01031: insufficient privileges
ORA-27037: unable to obtain file status
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD4/redo03.log'
ORA-00313: open failed for members of log group 3 of thread 1
ORA-27037: unable to obtain file status
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD4/redo03.log'
ORA-00313: open failed for members of log group 3 of thread 1
ORA-27037: unable to obtain file status

MESSAGE
----------------------------------------------------------------------------------------------------
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD4/redo02.log'
ORA-00313: open failed for members of log group 2 of thread 1
ORA-27037: unable to obtain file status
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD4/redo02.log'
ORA-00313: open failed for members of log group 2 of thread 1
ORA-27037: unable to obtain file status
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD4/redo01.log'
ORA-00313: open failed for members of log group 1 of thread 1
ORA-27037: unable to obtain file status
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD4/redo01.log'
ORA-00313: open failed for members of log group 1 of thread 1

MESSAGE
----------------------------------------------------------------------------------------------------
ORA-27037: unable to obtain file status
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD4/redo03.log'
ORA-00313: open failed for members of log group 3 of thread 1
ORA-27037: unable to obtain file status
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD4/redo03.log'
ORA-00313: open failed for members of log group 3 of thread 1
ORA-27037: unable to obtain file status
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD4/redo02.log'
ORA-00313: open failed for members of log group 2 of thread 1
ORA-27037: unable to obtain file status
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD4/redo02.log'

MESSAGE
----------------------------------------------------------------------------------------------------
ORA-00313: open failed for members of log group 2 of thread 1
ORA-27037: unable to obtain file status
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD4/redo01.log'
ORA-00313: open failed for members of log group 1 of thread 1
ORA-27037: unable to obtain file status
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD4/redo01.log'
ORA-00313: open failed for members of log group 1 of thread 1
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

52 rows selected.

4.按最近10000条记录报错最多的显示
SYS@PROD4> WITH alert_tab AS (
  2    SELECT rownum rn,message
  3    FROM   EXTERNAL (
  4             (
  5              message varchar2(500)
  6             )
  7           TYPE ORACLE_LOADER
  8           DEFAULT DIRECTORY trace_dir
  9           ACCESS PARAMETERS
10           (
11                  FIELDS TERMINATED BY ','
12                  MISSING FIELD VALUES ARE NULL
13                  (message
14                   )
15           )
16           LOCATION ('alert_PROD4.log')
17           REJECT LIMIT UNLIMITED
18        )
19    order by rn desc
20  )
21  select message,count(*) from alert_tab  where rn <100000   and upper(message) like 'ORA-%' group by message order by 2 desc;

MESSAGE                                                                                                COUNT(*)
---------------------------------------------------------------------------------------------------- ----------
ORA-27037: unable to obtain file status                                                                      16
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD4/redo03.log'                                  4
ORA-00313: open failed for members of log group 3 of thread 1                                                 4
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD4/redo02.log'                                  4
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD4/redo01.log'                                  4
ORA-00313: open failed for members of log group 2 of thread 1                                                 4
ORA-00313: open failed for members of log group 1 of thread 1                                                 4
ORA-00202: control file: '/u01/app/oracle/oradata/PROD4/control02.ctl'                                        2
ORA-01565: error in identifying file '/u01/app/oracle/oradata/PROD4/system01.dbf'                             2
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD4/system01.dbf'                                          2
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...                                                     1

MESSAGE                                                                                                COUNT(*)
---------------------------------------------------------------------------------------------------- ----------
ORA-205 signalled during: ALTER DATABASE   MOUNT...                                                           1
ORA-959 signalled during: alter tablespace trans_tbs read write...                                            1
ORA-01031: insufficient privileges                                                                            1
ORA-01122: database file 1 failed verification check                                                          1
ORA-00210: cannot open the specified control file                                                             1

16 rows selected.



SYS@PROD4> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0




以上在19.3上测试通过.


分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏 支持支持 反对反对
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2024-11-25 10:35 , Processed in 0.109344 second(s), 21 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表