如何查看数据库alert日志文件

2024-11-15 01:08:04

推荐回答（3个）

回答1：

1、查看操作系统版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>

2、查看预警日志文件（alert_sid.log）的位置
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/oracle/admin/bdump
core_dump_dest string /u01/oracle/admin/cdump
max_dump_file_size string 10240
shadow_core_dump string partial
user_dump_dest string /u01/oracle/admin/udump

3、创建目录alert
注意：directory不是实体，只是一个指向，指向os中一个路径
SQL> create or replace directory alert as '/u01/oracle/admin/bdump';
Directory created.
SQL>

4、创建外部表alert
SQL> create table alert
1 (log varchar2(1000))
2 organization external
3 (type oracle_loader
4 default directory alert
5 access parameters
6 (records delimited by newline)
7 location ('alert_PROD.log'))
8 reject limit unlimited;
Table created.

5、查看alert中的内容
SQL> select * from alert where rownum<20;
LOG
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Wed Feb 27 11:05:11 2013
Starting ORACLE instance (normal)
Cannot determine all dependent dynamic libraries for /proc/self/exe
Unable to find dynamic library libocr10.so in search paths
RPATH = /ade/aime1_build2101/oracle/has/lib/:/ade/aime1_build2101/oracle/lib/:/ade/aime1_build2101/oracle/has/lib/:
LD_LIBRARY_PATH is not set!
The default library directories are /lib and /usr/lib
Unable to find dynamic library libocrb10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
19 rows selected.
SQL>

6、看看数据库有哪些可爱的ORA- 错误吧
SQL> select * from alert where log like '%ORA-%';
LOG
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-959 signalled during: alter database default tablespace users...
ORA-959 signalled during: drop tablespace uses...
ORA-12919 signalled during: drop tablespace users...
ORA-1549 signalled during: drop tablespace users...
ORA-1505 signalled during: alter database add logfile group 1
ORA-1184 signalled during: alter database add logfile group 1
ORA-1013 signalled during: alter tablespace tts read only...
ORA-1013 signalled during: alter tablespace tts read only...
ORA-1013 signalled during: alter tablespace users read only...
ORA-1539 signalled during: alter tablespace users read only...

回答2：

1、查看操作系统版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>

2、查看预警日志文件（alert_sid.log）的位置
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/oracle/admin/bdump
core_dump_dest string /u01/oracle/admin/cdump
max_dump_file_size string 10240
shadow_core_dump string partial
user_dump_dest string /u01/oracle/admin/udump

3、创建目录alert
注意：directory不是实体，只是一个指向，指向os中一个路径
SQL> create or replace directory alert as '/u01/oracle/admin/bdump';
Directory created.
SQL>

4、创建外部表alert
SQL> create table alert
1 (log varchar2(1000))
2 organization external
3 (type oracle_loader
4 default directory alert
5 access parameters
6 (records delimited by newline)
7 location ('alert_PROD.log'))
8 reject limit unlimited;
Table created.

5、查看alert中的内容
SQL> select * from alert where rownum<20;
LOG
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Wed Feb 27 11:05:11 2013
Starting ORACLE instance (normal)
Cannot determine all dependent dynamic libraries for /proc/self/exe
Unable to find dynamic library libocr10.so in search paths
RPATH = /ade/aime1_build2101/oracle/has/lib/:/ade/aime1_build2101/oracle/lib/:/ade/aime1_build2101/oracle/has/lib/:
LD_LIBRARY_PATH is not set!
The default library directories are /lib and /usr/lib
Unable to find dynamic library libocrb10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
19 rows selected.
SQL>

6、看看数据库有哪些错误
SQL> select * from alert where log like '%QMZ-%';
LOG
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-959 signalled during: alter database default tablespace users...
ORA-959 signalled during: drop tablespace uses...
ORA-12919 signalled during: drop tablespace users...
ORA-1549 signalled during: drop tablespace users...
ORA-1505 signalled during: alter database add logfile group 1
ORA-1184 signalled during: alter database add logfile group 1
ORA-1013 signalled during: alter tablespace tts read only...
ORA-1013 signalled during: alter tablespace tts read only...
ORA-1013 signalled during: alter tablespace users read only...
ORA-1539 signalled during: alter tablespace users read only...

回答3：

1、查看操作系统版本 
SQL> select * from v$version; 
BANNER 

---------------------------------------------------------------- 
Oracle 
Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 
PL/SQL Release 
10.2.0.1.0 - Production 
CORE 10.2.0.1.0 Production 
TNS for Linux: 
Version 10.2.0.1.0 - Production 
NLSRTL Version 10.2.0.1.0 - Production 

SQL> 

2、查看预警日志文件（alert_sid.log）的位置 
SQL> 
show parameter dump 
NAME TYPE VALUE 
------------------------------------ 
----------- ------------------------------ 
background_core_dump string 
partial 
background_dump_dest string /u01/oracle/admin/bdump 

core_dump_dest string /u01/oracle/admin/cdump 
max_dump_file_size string 
10240 
shadow_core_dump string partial 
user_dump_dest string 
/u01/oracle/admin/udump 

3、创建目录alert 
注意：directory不是实体，只是一个指向，指向os中一个路径 
SQL> create or replace directory 
alert as '/u01/oracle/admin/bdump'; 
Directory created. 
SQL> 


4、创建外部表alert 
SQL> create table alert 
1 (log 
varchar2(1000)) 
2 organization external 
3 (type oracle_loader 
4 
default directory alert 
5 access parameters 
6 (records delimited by 
newline) 
7 location ('alert_PROD.log')) 
8 reject limit unlimited; 

Table created. 

5、查看alert中的内容 
SQL> select * 
from alert where rownum<20; 
LOG 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

Wed Feb 27 11:05:11 2013 
Starting ORACLE instance (normal) 
Cannot 
determine all dependent dynamic libraries for /proc/self/exe 
Unable to find 
dynamic library libocr10.so in search paths 
RPATH = 
/ade/aime1_build2101/oracle/has/lib/:/ade/aime1_build2101/oracle/lib/:/ade/aime1_build2101/oracle/has/lib/: 

LD_LIBRARY_PATH is not set! 
The default library directories are /lib and 
/usr/lib 
Unable to find dynamic library libocrb10.so in search paths 

Unable to find dynamic library libocrutl10.so in search paths 
Unable to 
find dynamic library libocrutl10.so in search paths 
LICENSE_MAX_SESSION = 0 

LICENSE_SESSIONS_WARNING = 0 
Picked latch-free SCN scheme 2 
Using 
LOG_ARCHIVE_DEST_1 parameter default value as 
/u01/oracle/product/10.2.0/db_1/dbs/arch 
Autotune of undo retention is 
turned on. 
IMODE=BR 
ILAT =10 
LICENSE_MAX_USERS = 0 
SYS auditing 
is disabled 
19 rows selected. 
SQL> 

6、看看数据库有哪些 可爱的ORA- 
错误吧 
SQL> select * from alert where log like '%ORA-%'; 
LOG 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

ORA-959 signalled during: alter database default tablespace users... 

ORA-959 signalled during: drop tablespace uses... 
ORA-12919 signalled 
during: drop tablespace users... 
ORA-1549 signalled during: drop tablespace 
users... 
ORA-1505 signalled during: alter database add logfile group 1 

ORA-1184 signalled during: alter database add logfile group 1 
ORA-1013 
signalled during: alter tablespace tts read only... 
ORA-1013 signalled 
during: alter tablespace tts read only... 
ORA-1013 signalled during: alter 
tablespace users read only... 
ORA-1539 signalled during: alter tablespace 
users read only...