Working case – 第 18 页

一次ORA-3136的处理

2011-03-04 10条评论

最近收到一个告警，用户说数据库无法连接，但是从监控上看，oracle的后台进程已经侦听进程还是在的，没有任何的alert。登录数据库，已经恢复正常，但是在数据库的alertlog中发现大量的ora-3136的报错：

Thu Feb 17 09:07:31 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:31 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:31 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

Thu Feb 17 09:07:31 2011

WARNING: inbound connection timed out (ORA-3136)

Thu Feb 17 09:07:31 2011

WARNING: inbound connection timed out (ORA-3136)

Thu Feb 17 09:07:31 2011

WARNING: inbound connection timed out (ORA-3136)

Thu Feb 17 09:07:32 2011

WARNING: inbound connection timed out (ORA-3136)

Thu Feb 17 09:07:32 2011

WARNING: inbound connection timed out (ORA-3136)

Thu Feb 17 09:07:32 2011

WARNING: inbound connection timed out (ORA-3136)

Thu Feb 17 09:07:32 2011

WARNING: inbound connection timed out (ORA-3136)

Thu Feb 17 09:07:32 2011

WARNING: inbound connection timed out (ORA-3136)

Thu Feb 17 09:07:32 2011

WARNING: inbound connection timed out (ORA-3136)

时间大约是在9点 […]

..experience, Working case

遭遇ora-600[19004]

2011-01-23 没有评论

今天本来是休假半天，正在外面办事的时候，突然收到pushmail的告警，oracle数据库中有500多个ora-的报错，心里咯噔了一下，怎么会有那么多的报错！但是又没收到数据库down的pushmail，难道是程序遇到bug了？赶紧打了电话给同事，请他们帮忙看看。同时也顾不上吃午饭了，匆匆赶到了公 […]

..experience, Working case

在9i的数据库中排除某些表做owner级导出

2011-01-07 5条评论

在做exp的时候，我们可以做owner级的导出，导出该用户下的所有对象。owner级的导出，会导出所有的表，不太容易排除单独的一些表；而如果做table级的导出，就必须列出所有的table，且当数据库有增加新table的时候，也得把新增的table加到exp的列表中。今天收到一个客户的要求，做ow […]

..experience, Working case

ORA-03232老库的老问题

2011-01-03 一条评论

今天接到客户的一个问题，应用报错：ORA-03232 从报错上看，是属于表空间3的，进一步查select TS#,NAME from v$tablespace; 3号表空间为temp。然后进一步看ORA-03232的报错信息，查对应版本的error message: [crayon-678c338 […]

..experience, Working case

AIX上启动xmanager界面

2010-11-17 6条评论

在aix上安装oracle，用图形界面进行安装，首先的一点是要让oracle用户能启动xwindow。由于之前一直在HP平台下，对AIX还不是很熟悉，此文章是以为记。我们假定root可以启动xclock：我们用xstart通过root登录之后，我们查看通道： [crayon-678c338f83 […]

..experience, Working case

又见bug，动态调整SGA参数导致数据库crash

2010-11-13 一条评论

最近遇到这样一个事情，在动态的调整sga的一个参数的时候，数据库就down了：

SQL> alter system set DB_KEEP_CACHE_SIZE=1g;
alter system set DB_KEEP_CACHE_SIZE=1g
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL>

1

2

3

4

5

6

7

8

SQL> alter system set DB_KEEP_CACHE_SIZE=1g;

alter system set DB_KEEP_CACHE_SIZE=1g

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

SQL>

对应的，在alertlog中的报错为：

Sat Nov 13 03:56:00 2010
Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mman_3264732.trc:
ORA-00600: internal error code, arguments: [kmgs_update_target_size_1], [0], [], [], [], [], [], []
Sat Nov 13 03:56:01 2010
Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mman_3264732.trc:
ORA-00600: internal error code, arguments: [kmgs_update_target_size_1], [0], [], [], [], [], [], []
Sat Nov 13 03:56:01 2010
MMAN: terminating instance due to error 822
Sat Nov 13 03:56:01 2010
Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mmon_2969868.trc:
ORA-00600: internal error code, arguments: [kmgs_perform_parameter_updates_1], [0], [], [], [], [], [], []
Sat Nov 13 03:56:03 2010
Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mmon_2969868.trc:
ORA-00600: internal error code, arguments: [kmgs_parameter_update_timeout_1], [600], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kmgs_perform_parameter_updates_1], [0], [], [], [], [], [], []
Instance terminated by MMAN, pid = 3264732

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

Sat Nov 13 03:56:00 2010

Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mman_3264732.trc:

ORA-00600: internal error code, arguments: [kmgs_update_target_size_1], [0], [], [], [], [], [], []

Sat Nov 13 03:56:01 2010

Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mman_3264732.trc:

ORA-00600: internal error code, arguments: [kmgs_update_target_size_1], [0], [], [], [], [], [], []

Sat Nov 13 03:56:01 2010

MMAN: terminating instance due to error 822

Sat Nov 13 03:56:01 2010

Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mmon_2969868.trc:

ORA-00600: internal error code, arguments: [kmgs_perform_parameter_updates_1], [0], [], [], [], [], [], []

Sat Nov 13 03:56:03 2010

Errors in file /u01/app/oracle/product/10.2.0/admin/MICPRD/bdump/micprd_mmon_2969868.trc:

ORA-00600: internal error code, arguments: [kmgs_parameter_update_timeout_1], [600], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [kmgs_perform_parameter_updates_1], [0], [], [], [], [], [], []

Instance terminated by MMAN, pid = 3264732

对应的tace文件中的call stack为： […]

..experience, Working case

DG备库遭遇ORA-16146的bug

2010-11-05 一条评论

今天收到监控的一个告警：

ORA-16146: standby destination control file enqueue unavailable

1	ORA-16146: standby destination control file enqueue unavailable

登录主库和备库检查其报错信息：备库上的alertlog：

Media Recovery Waiting for thread 1 seq# 2506
Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2506.arc
Wed Oct 27 20:30:24 2010
Media Recovery Waiting for thread 1 seq# 2507
Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2507.arc
Thu Oct 28 00:13:35 2010
Media Recovery Waiting for thread 1 seq# 2508
Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2508.arc
Thu Oct 28 01:24:57 2010
RFS: controlfile enqueue unavailable
Possible invalid cross-instance archival configuration
Thu Oct 28 01:25:00 2010
Errors in file /oracle_log/JPJDE/admin/udump/jpjde_rfs_1331404.trc:
ORA-16146: standby destination control file enqueue unavailable
Thu Oct 28 01:44:42 2010
RFS: Error State mode '2'
Thu Oct 28 01:45:31 2010
Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2509.arc
Thu Oct 28 01:46:53 2010
RFS: Forced Shutdown due to RFS_ERROR state
Thu Oct 28 01:50:33 2010
Media Recovery Waiting for thread 1 seq# 2510 (in transit)
Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2510.arc
Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2511.arc
Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2512.arc
Thu Oct 28 02:11:34 2010
Media Recovery Waiting for thread 1 seq# 2513
Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2513.arc

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

Media Recovery Waiting for thread 1 seq# 2506

Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2506.arc

Wed Oct 27 20:30:24 2010

Media Recovery Waiting for thread 1 seq# 2507

Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2507.arc

Thu Oct 28 00:13:35 2010

Media Recovery Waiting for thread 1 seq# 2508

Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2508.arc

Thu Oct 28 01:24:57 2010

RFS: controlfile enqueue unavailable

Possible invalid cross-instance archival configuration

Thu Oct 28 01:25:00 2010

Errors in file /oracle_log/JPJDE/admin/udump/jpjde_rfs_1331404.trc:

ORA-16146: standby destination control file enqueue unavailable

Thu Oct 28 01:44:42 2010

RFS: Error State mode '2'

Thu Oct 28 01:45:31 2010

Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2509.arc

Thu Oct 28 01:46:53 2010

RFS: Forced Shutdown due to RFS_ERROR state

Thu Oct 28 01:50:33 2010

Media Recovery Waiting for thread 1 seq# 2510 (in transit)

Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2510.arc

Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2511.arc

Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2512.arc

Thu Oct 28 02:11:34 2010

Media Recovery Waiting for thread 1 seq# 2513

Media Recovery Log /oracle_log/JPJDE/arch_logs/stby_1_2513.arc

对应同一时间，主库上的信息： [crayon-678c338f8513126286 […]

..experience, Working case

移动DG数据库的数据文件

2010-10-26 6条评论

今天遇到个问题，由于执行一个TSM脚本的时候，没注意到脚本中的语句会新建一个tablespace，而且新建的语句是

create tablespace tsm datafile 'tsm.dbf' size 100m autoextend off;

1	create tablespace tsm datafile 'tsm.dbf' size 100m autoextend off;

由于没有指定数据文件的绝对路径，所以默认就放在了$ORACLE_HOME/dbs路径下。数据文件路径不统一，风险可大 […]

..experience, Working case

由于TSM引起的数据库宕机

2010-10-19 2条评论

今天接到一个电话，一个应用的同事说的登录不上数据库，该数据库是在一台windows服务器上的，登录主机后，发现在系统的服务里面，oracle是启动的，但是登录数据库发现数据库已经宕机。检查数据库的alertlog发现：

Tue Oct 19 03:22:38 2010
Thread 1 advanced to log sequence 40786
  Current log# 3 seq# 40786 mem# 0: D:\ORACLE\ORADATA\JPJDE2\REDO03.LOG
Tue Oct 19 03:25:44 2010
Thread 1 advanced to log sequence 40787
  Current log# 1 seq# 40787 mem# 0: D:\ORACLE\ORADATA\JPJDE2\REDO01.LOG
Tue Oct 19 03:29:48 2010
Errors in file d:\oracle\admin\jpjde2\bdump\jpjde2_lgwr_2532.trc:
ORA-00321: log 2 of thread 1, cannot update log file header
ORA-00312: online log 2 thread 1: 'D:\ORACLE\ORADATA\JPJDE2\REDO02.LOG'
ORA-27091: skgfqio: unable to queue I/O
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.

Tue Oct 19 03:29:48 2010
Errors in file d:\oracle\admin\jpjde2\bdump\jpjde2_lgwr_2532.trc:
ORA-00321: log 2 of thread 1, cannot update log file header

LGWR: terminating instance due to error 321
Instance terminated by LGWR, pid = 2532

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

Tue Oct 19 03:22:38 2010

Thread 1 advanced to log sequence 40786

Current log# 3 seq# 40786 mem# 0: D:\ORACLE\ORADATA\JPJDE2\REDO03.LOG

Tue Oct 19 03:25:44 2010

Thread 1 advanced to log sequence 40787

Current log# 1 seq# 40787 mem# 0: D:\ORACLE\ORADATA\JPJDE2\REDO01.LOG

Tue Oct 19 03:29:48 2010

Errors in file d:\oracle\admin\jpjde2\bdump\jpjde2_lgwr_2532.trc:

ORA-00321: log 2 of thread 1, cannot update log file header

ORA-00312: online log 2 thread 1: 'D:\ORACLE\ORADATA\JPJDE2\REDO02.LOG'

ORA-27091: skgfqio: unable to queue I/O

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.

Tue Oct 19 03:29:48 2010

Errors in file d:\oracle\admin\jpjde2\bdump\jpjde2_lgwr_2532.trc:

ORA-00321: log 2 of thread 1, cannot update log file header

LGWR: terminating instance due to error 321

Instance terminated by LGWR, pid = 2532

看来昨天晚上3 […]

..oracle service, Working case

OOW 2010中两场Larry Ellison的keynote

2010-10-16 一条评论

在网上看到2场OOW（Oracle Open World）2010中Larry Ellison主讲的keynote，一场是2010年9月19日的，主要讲的是云计算和oracle新推出的Exalogic；另一场是在2010年的9月22日，讲的内容比较多，主要讲了oracle相关的所有内容做的集成。我 […]

..experience, Working case

crs只能启动一个asm实例

2010-10-09 3条评论

今天接到一个朋友的电话，说他有个客户rac安装的时候总是有问题。cluster软件已经装上，但是没法装数据库。由于网络环境比较差，无法远程，只能通过QQ来了解情况和诊断了。一开始，先让对方运行crs_stat -t看看各个资源的状况：

[oracle@rac01 bin]$ ./crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    rac01       
ora....01.lsnr application    ONLINE    ONLINE    rac01       
ora.rac01.gsd  application    ONLINE    ONLINE    rac01       
ora.rac01.ons  application    ONLINE    ONLINE    rac01       
ora.rac01.vip  application    ONLINE    ONLINE    rac01       
ora....SM2.asm application    ONLINE    OFFLINE               
ora....02.lsnr application    ONLINE    ONLINE    rac02       
ora.rac02.gsd  application    ONLINE    ONLINE    rac02       
ora.rac02.ons  application    ONLINE    ONLINE    rac02       
ora.rac02.vip  application    ONLINE    ONLINE    rac02

1

2

3

4

5

6

7

8

9

10

11

12

13

[oracle@rac01 bin]$ ./crs_stat -t

Name Type Target State Host

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

ora....SM1.asm application ONLINE ONLINE rac01

ora....01.lsnr application ONLINE ONLINE rac01

ora.rac01.gsd application ONLINE ONLINE rac01

ora.rac01.ons application ONLINE ONLINE rac01

ora.rac01.vip application ONLINE ONLINE rac01

ora....SM2.asm application ONLINE OFFLINE

ora....02.lsnr application ONLINE ONLINE rac02

ora.rac02.gsd application ONLINE ONLINE rac02

ora.rac02.ons application ONLINE ONLINE rac02

ora.rac02.vip application ONLINE ONLINE rac02

Working case

为AIX中配置sendmail relay到smtp server

2010-08-24 2条评论

今天写了一个数据库的监控脚本，在测试脚本能否正常告警时，发现邮件发不出去。这个系统的环境是这样的，在整个系统中，大部分的机器放在192.168.1网段，为trust area，数据库主机也在此网段；另外有几台机器在192.168.3网段，为DMZ area。发送邮件不能直接在数据库主机上直接发送， […]