OracleBlog – 第 28 页 – 笑看数据库江湖的那些事儿……

一次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点 […]

Study note

在windows上的两种建立ASM实例的方式

2011-02-13 3条评论

我们可以在windows上建立ASM实例。oracle给我们提供了一个很贴心的工具，来实现在windows上安装asm，这个工具就是asmtool。该工具可以在安装介质的asmtool目录中找到，也可以在安装数据库软件后，在$ORACLE_HOME/bin下找到。下面，我们就用asmtool来在wi […]

..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-6782afc […]

It's my life

有史以来IBM GDC招聘最高级别的DBA

2010-12-11 13条评论

IBM GDC ITD SSO，也就是我所在的部门，近期接到澳洲电讯（Telstra）的数据库维护项目，需要招聘高级DBA 6～7人。澳洲电讯是澳大利亚最大的电信运营商。据相关消息透露，项目涉及1000多台server，其中有12个节点的RAC。该项目原来由IBM印度的团队进行维护，预计明年年初转 […]

..experience, Working case

AIX上启动xmanager界面

2010-11-17 6条评论

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

..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-6782afc2ceac063116 […]

..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相关的所有内容做的集成。我 […]