ORACLE 19c 使用内联外部表方便检查报警日志

ORACLE 数据库日常巡检中，难免会去看日志，但一般看日志，都需要去操作系统上看，能否使用SQL语句在远程看呢，答案是肯定的.

ORACLE 19C 的一个新功能，内联外部表，可以使用内联外部表，不需要事先创建外部表，把外部表当成一个SQL语句的一部分，直接使用。具体如下：

比如想看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上测试通过.