Oracle Life

January 29, 2012

2012 龙年新年记事

2012的新年在东北老家，第一次开车回东北。记录一下这个龙年的片段，祝大家新年快乐。

1.大年29开车回东北，高速上的车很少很少，非常顺利。高速路上，河北段全部免费，吉林段不仅免费，最后还有小的新年礼物赠送，喜庆且人性化，需要表扬。

2.路过唐山时，跟儿子说这里曾经大地震，那时候爸爸还没有出生。儿子说，爸爸你是不是害怕不敢生出来。

3.今年过年，老家（吉林-四平-公主岭）一点雪也没有下，空有严寒，未有雪意，遗憾，而今年的雨雪又转移到南方去下了。年后东北如果不降雪，则明年农作物将受到影响了。

4.看以前的照片，儿子总是问，你有没有带我去过？我们说没有，你还没有出生。问多了小家伙怒了，说:哼，你们总是说我没有出生。

5.春晚，没有了赵本山，东北乡亲普遍反映没有了期待，还好有李玉刚，还是正宗的公主岭老乡。

6.铁道部的"厨师修铁路"事件影响深远，间接的影响到了朋友的施工队伍，铁路建设项目错综复杂。

7.食品安全问题的重视已经到达老家，市里在组建新的食品安全监察大队，希望全国都能从生产基层开始重视食品安全问题。

8.回京途中，很多车遮挡号牌，超速驾驶，在辽宁亲眼见到一辆车在休息站被警察抓获，予以处罚，开车安全第一，还是不要超速的好。

9.暧暧远人村，依依墟里烟

10.儿子眼中的玉米大炮

Posted by eygle at 9:01 AM | Permalink | Comments (0) | Life (510)

January 19, 2012

《Oracle DBA手记》以及51CTO 年度图书作者

从出版社获得的消息，在51CTO的年度评选中，再次被评选为年度最受读者喜爱的作者奖。

感谢 51CTO 网站多年以来坚持不懈的图书评选工作，这一评选对于支持图书原创、图书写作来说，具有相当的影响力和支持作用。

感谢出版社，也感谢读者们的支持和厚爱，谢谢，祝大家龙年快乐！

Posted by eygle at 11:50 PM | Permalink | Comments (0) | Books (178)

SCSI读写错误导致文件系统只读的数据库恢复

假期马上来到，一个客户数据库出现问题。

两个实例异常终止，文件系统变成只读：

PCLERPDB2:[10g]:/DBMS/PCMK/admin/PCMK> sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 19 09:08:05 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-09925: Unable to create audit trail file
Linux Error: 30: Read-only file system
Additional information: 9925
ORA-01075: you are currently logged on

检查系统日志，发现早晨出现SCSI IO错误：

Jan 19 07:56:00 PCLERPDB2 kernel: SCSI error : <0 0 0 1> return code = 0x10000
Jan 19 07:56:00 PCLERPDB2 kernel: end_request: I/O error, dev sda, sector 26480696
Jan 19 07:56:00 PCLERPDB2 kernel: Buffer I/O error on device sda1, logical block 3310083
Jan 19 07:56:00 PCLERPDB2 kernel: lost page write due to I/O error on sda1
Jan 19 07:56:00 PCLERPDB2 kernel: SCSI error : <0 0 0 9> return code = 0x10000
Jan 19 07:56:00 PCLERPDB2 kernel: end_request: I/O error, dev sdh, sector 60052680
Jan 19 07:56:00 PCLERPDB2 kernel: SCSI error : <0 0 0 4> return code = 0x10000
Jan 19 07:56:00 PCLERPDB2 kernel: end_request: I/O error, dev sdc, sector 20042688
Jan 19 07:56:00 PCLERPDB2 kernel: SCSI error : <0 0 0 9> return code = 0x10000
Jan 19 07:56:00 PCLERPDB2 kernel: end_request: I/O error, dev sdh, sector 26747408
Jan 19 07:56:00 PCLERPDB2 kernel: Buffer I/O error on device sdh2, logical block 843074
Jan 19 07:56:00 PCLERPDB2 kernel: lost page write due to I/O error on sdh2
Jan 19 07:56:00 PCLERPDB2 kernel: SCSI error : <0 0 0 1> return code = 0x10000
Jan 19 07:56:00 PCLERPDB2 kernel: end_request: I/O error, dev sda, sector 32606944
Jan 19 07:56:00 PCLERPDB2 kernel: Buffer I/O error on device sda1, logical block 4075864

然后数据库崩溃.

安排用户重启数据库主机，检查是否硬件软故障。
很幸运，重启后数据库能够正常启动：

Thu Jan 19 09:55:09 2012
Completed redo application
Thu Jan 19 09:55:09 2012
Completed crash recovery at
Thread 1: logseq 18735, block 5214, scn 5965501404211
59 data blocks read, 59 data blocks written, 609 redo blocks read
Thu Jan 19 09:55:09 2012
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=23, OS id=14599
Thu Jan 19 09:55:09 2012
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=24, OS id=14601
Thu Jan 19 09:55:09 2012
Thread 1 advanced to log sequence 18736
Thread 1 opened at log sequence 18736
Current log# 3 seq# 18736 mem# 0: /DBMS/DCERP/dcerpdata/log03a.dbf
Current log# 3 seq# 18736 mem# 1: /DBMS/DCERP/dcerpdata/log03b.dbf
Successful open of redo thread 1
Thu Jan 19 09:55:09 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jan 19 09:55:09 2012
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Thu Jan 19 09:55:09 2012
ARC1: Becoming the heartbeat ARCH
Thu Jan 19 09:55:09 2012
SMON: enabling cache recovery
Thu Jan 19 09:55:11 2012
Successfully onlined Undo Tablespace 368.
Thu Jan 19 09:55:11 2012
SMON: enabling tx recovery
Thu Jan 19 09:55:11 2012
Database Characterset is UTF8
Thu Jan 19 09:55:11 2012
Incremental checkpoint up to RBA [0x4930.3.0], current log tail at RBA [0x4930.43.0]
Thu Jan 19 09:55:11 2012
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=25, OS id=14626
Thu Jan 19 09:55:25 2012
Completed: ALTER DATABASE OPEN
Thu Jan 19 10:15:13 2012
Incremental checkpoint up to RBA [0x4930.100d.0], current log tail at RBA [0x4930.107d.0]
Thu Jan 19 10:35:16 2012
Incremental checkpoint up to RBA [0x4930.150f.0], current log tail at RBA [0x4930.155b.0]
Thu Jan 19 10:55:17 2012
Incremental checkpoint up to RBA [0x4930.1724.0], current log tail at RBA [0x4930.175a.0]
Thu Jan 19 11:15:18 2012
Incremental checkpoint up to RBA [0x4930.1edf.0], current log tail at RBA [0x4930.1f13.0]

估计硬件的生命周期达到，需要更新了。

Posted by eygle at 9:18 AM | Permalink | Comments (0) | Case (145)

January 18, 2012

ACOUG 2012年2月 Ask Tom and Eygle - 上海

ACOUG在2012年的首次活动将来到上海，重量级的嘉宾是Thomas Kyte，ASKTOM 网站背后的老大，Oracle的副总裁。

在这个活动中，我还有一个主题演讲，大家有兴趣的请尽快报名：
http://www.acoug.org/events/239.html

具体信息请关注 ACOUG 微博：
http://weibo.com/acoug

Posted by eygle at 9:47 AM | Permalink | Comments (2) | Activity (83)

January 12, 2012

TNS-00583 错误解决案例一则

今天删除了本机安装的Oracle 11g数据库，使用drop database命令：

SQL> startup restrict;
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size                  1384752 bytes
Variable Size             268439248 bytes
Database Buffers          260046848 bytes
Redo Buffers                5791744 bytes
Database mounted.
Database opened.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL> alter database close;

Database altered.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\ORACLE\11.2.0\DATABASE\INITORA11G.ORA'
SQL> exit
Disconnected

启动数据库出现了TNS-00583错误：

E:\>lsnrctl start

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 12-JAN-2012 17:07:56

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
System parameter file is D:\Oracle\11.2.0\network\admin\listener.ora
Log messages written to D:\Oracle\diag\tnslsnr\oracle-vm\listener\alert\log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
TNS-00583: Valid node checking: unable to parse configuration parameters

Listener failed to start. See the error message(s) above...

检查发现，是SQLNET.ORA 文件中的Wallet参数配置影响，删除该参数后监听器恢复正常：

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=D:\Oracle\11.2.0\NETWORK\ADMIN\encryption_wallet\)))

监听程序启动正常：

E:\>lsnrctl start

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 12-JAN-2012 17:09:27

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
System parameter file is D:\Oracle\11.2.0\network\admin\listener.ora
Log messages written to D:\Oracle\diag\tnslsnr\oracle-vm\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
Start Date                12-JAN-2012 17:09:30
Uptime                    0 days 0 hr. 0 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\Oracle\11.2.0\network\admin\listener.ora
Listener Log File         D:\Oracle\diag\tnslsnr\oracle-vm\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "ora11g" has 1 instance(s).
Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully