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