oracle查找alert,没事看看Oracle的AlertSID.log做一上分析

没事看看Oracle的AlertSID.log做一下分析

后台进程trace file:

alert log 文件:

查看问题

1.描述内存错误和block损坏错误

2.监控数据操作

3.查看非默认初始化参数

查看dump存储

show parameter dump;

backgroup_dump_dest:后台进程的dump

user_dump_dest:

alert.log

调优的查看信息:

1. 检查点开始和结束时间查看.

局部检查点的查看.

2.归档相应的时间.

3.实例恢复开始结束的时间.

4.死锁和超时的时间.

如果想记录检查点信息记录alert中必须开启参数:

log_checkpoints_to_alert参数值为true:

alter system set log_checkpoints_to_alert=true scope=both;

测试:

alter　system checkpoint;

分析alert文件.

1.数据库启动方式,归档目录,UNDO retention 是否开启,系统审计是否开启.

数据库启动和关闭的过程中数据的各种状态变化,和进程的变化:

2.系统客户端参数文件pfile,spfile以及非默认的参数信息,字符集信息,以及控制文件的信息.

数据库创建的脚本的详细信息.(如果没有创建没有设置特定临时表空间,默认警告)

3.后台进程以及其他进程,启动顺序的时间(PMON,DBWn,LGWR,CKPT,SMON,ARCn).

其他后台进程MMNL,CJQ0,QMNC,MMNL,MMON,VKTM,MMAN,DBRM,DIAG,RECO,DIAn,FBDA,SMCO等的启停在数据库

和操作系统的进程pid或者id.MTTR进程记录(FAST_START_MTTR_TARGET).

4.在系统联系各种恢复功能产生大量的日志信息:

如临时文件被删除,后台进程中的trc文件删除,会报错的同时给出的建议.

5.如果使用RMAN启动,备份数据,显示,备份目录的使用情况.

db_recovery_file_dest_size各种数据文件恢复的时间.

REDO恢复过程:

6.数据库默认Oracle net为专用服务器模式,当使用共享服务器模式,

记录share server以及dispatcher进程启动个数.

以及一些系统的表中过期字段(不推荐使用的)信息.

6.一些数据库系统状态和系统参数操作的记录:

如修改数据库以及表空间以及数据文件的状态变化以及过程中各进程的变化:

ALTER TABLESPACE example READ WRITE.

ALTER SYSTEM SWTICH LOGFILE;

alter database character set INTERNAL_CONVERT ZHS16GBK

create tablespace 表空间的创建语句.

ALTER DATABASE MOUNT/OPEN

alter database datafile 6 offline;

ALTER DATABASE RECOVER datafile 6

alter tablespace system begin/end backup

alter database rename file:

ALTER DATABASE RECOVER

ALTER DATABASE RECOVER CANCEL

ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;

以及数据库内部数据库以及表空间以及数据文件的执行的SQL语句

ALTER DATABASE CLOSE NORMAL

ALTER DATABASE DISMOUNT

alter database mount exclusive

7.数据导出导入Job记录

8.运行过程的错误ORA异常:

ORA-1089 : opidrv aborting process J000 ospid (860_75056)

ORA-00312: online log 3 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\TICKET\REDO03.LOG'

ORA-01565: ???? 'D:\app\Administrator\oradata\ticket\TEMP01.DBF' ???

ORA-01517: ????: 'D:\app\Administrator\oradata\ticket\REDO01.LOG'

ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ...

ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...

ORA-01110: data file 6: 'D:\APP\ADMINISTRATOR\ORADATA\TICKET\TBS_DATA.DBF'

以下数据库数据文件和数据库恢复日志记录

ALTER DATABASE RECOVER恢复过程:

ALTER DATABASE MOUNT

Setting recovery target incarnation to 2

Successful mount of redo thread 1, with mount id 408133357

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Fri Aug 05 20:00:02 2011

ALTER DATABASE RECOVER datafile 'D:\app\Administrator\oradata\ticket\SYSTEM01.DBF'

ORA-1179 signalled during: ALTER DATABASE RECOVER datafile 'D:\app\Administrator\oradata\ticket\SYSTEM01.DBF' ...

Fri Aug 05 20:02:08 2011

alter database rename file 'D:\app\Administrator\backup\SYSTEM01.DBF' to 'D:\app\Administrator\oradata\ticket\SYSTEM01.DBF'

Completed: alter database rename file 'D:\app\Administrator\backup\SYSTEM01.DBF' to 'D:\app\Administrator\oradata\ticket\SYSTEM01.DBF'

Fri Aug 05 20:02:23 2011

ALTER DATABASE RECOVER database

Media Recovery Start

Fast Parallel Media Recovery enabled

WARNING! Recovering data file 1 from a fuzzy backup. It might be an online

backup taken without entering the begin backup command.

parallel recovery started with 3 processes

Recovery of Online Redo Log: Thread 1 Group 3 Seq 42 Reading mem 0

Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\TICKET\REDO03.LOG

Completed: ALTER DATABASE RECOVER database

Fri Aug 05 20:02:36 2011

alter database open

ALTER DATABASE RECOVER CANCEL 恢复过程:

ALTER DATABASE RECOVER database using backup controlfile

Media Recovery Start

Fast Parallel Media Recovery enabled

parallel recovery started with 3 processes

ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile ...

Sun Aug 07 09:56:26 2011

ALTER DATABASE RECOVER CONTINUE DEFAULT

Media Recovery Log D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TICKET\ARCHIVELOG\2011_08_07\O1_MF_1_52_%U_.ARC

Errors with log D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TICKET\ARCHIVELOG\2011_08_07\O1_MF_1_52_%U_.ARC

ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...

ALTER DATABASE RECOVER CONTINUE DEFAULT

Media Recovery Log D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TICKET\ARCHIVELOG\2011_08_07\O1_MF_1_52_%U_.ARC

Errors with log D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TICKET\ARCHIVELOG\2011_08_07\O1_MF_1_52_%U_.ARC

ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...

ALTER DATABASE RECOVER CANCEL

Media Recovery Canceled

Completed: ALTER DATABASE RECOVER CANCEL