RAC – 第3页 – Oracle and Linux

详细记录一则RAC数据库恢复到异机单实例的案例

8月 27,2013 有8条评论 Experience, RAC

背景描述：来源于一套客户的生产系统的恢复案例，Linux平台的10.2.0.5.0的双节点RAC数据库，由于项目组的应用程序BUG导致，在7月30号下午4点30分左右执行了误操作，将生产系统中一张重要表的所有记录全部update一遍。

处理流程，接到电话之后，让项目组同事紧急使用闪回表尝试找回数据，由于发现错误时已是7月30号晚上，再加上undo_retention设置的偏低，导致报错ORA-01555的错误，未果。不得已让项目组同事参照以前的一则使用LogMiner来恢复数据，依然不幸，找不回数据。

不得已考虑使用之前的备份+归档来恢复到异机单实例环境，执行不完全恢复尝试恢复数据，本文用以记录和还原当时的详细步骤。

1 找到在误操作执行之前的最近一次的有效备份，以及从备份以来到误操作之前的所有归档日志。因为库小，该RAC数据库目前配置在每天晚上23:00开始执行全备份，且包括归档日志的备份。于是，找到29号晚上的全备的备份集。

发现，该库备份的结果是写到+FLASH磁盘组的，10g情况下貌似还不可以直接将数据从ASM磁盘组直接copy到文件系统上来存放。于是，通过RMAN工具将29号23点的全备的备份集重新备份一份到文件系统上来。

找29号晚上的全备结果：

list backup tag=TAG20130729T230017;

根据当时在RMAN中返回的备份集信息，重新备份29号晚上的备份集到文件系统：

 backup backupset 1822 format '/home/oracle/1822.bak';
 backup backupset 1823 format '/home/oracle/1823.bak';
 backup backupset 1824 format '/home/oracle/1824.bak';
 backup backupset 1825 format '/home/oracle/1825.bak';
 backup backupset 1826 format '/home/oracle/1826.bak';
 backup backupset 1827 format '/home/oracle/1827.bak';

找到从备份以来到误操作之前的所有归档日志，并备份这些归档日志到文件系统：

select name,FIRST_TIME from v$archived_log where first_time  between to_date('2013/07/29 22:00:00','yyyy/mm/dd hh24:mi:ss') and to_date('2013/07/30 16:20:00','yyyy/mm/dd hh24:mi:ss');

并且，将新的备份集copy或者FTP至新的单实例环境的机器上。这里存放在单实例环境下的/home/oracle/zl路径下。

2 获取原RAC数据库的参数文件。
利用RAC数据库的SPFILE生成新的PFILE命令：

SQL>CREATE PFILE='/home/oracle/pfile.txt' FROM SPFILE;

插曲1：原参数文件内容如下：

zldb2.__db_cache_size=398458880
zldb1.__db_cache_size=406847488
zldb1.__java_pool_size=4194304
zldb2.__java_pool_size=8388608
zldb1.__large_pool_size=4194304
zldb2.__large_pool_size=4194304
zldb2.__shared_pool_size=629145600
zldb1.__shared_pool_size=624951296
zldb1.__streams_pool_size=4194304
zldb2.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/zldb/adump'
*.background_dump_dest='/u01/app/oracle/admin/zldb/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.5.0'
*.control_files='+DATA/zldb/controlfile/current.256.793214967','+FLASH/zldb/controlfile/current.256.793214967'
*.core_dump_dest='/u01/app/oracle/admin/zldb/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='zldb'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=107374182400
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zldbXDB)'
zldb1.instance_number=1
zldb2.instance_number=2
*.job_queue_processes=10
Node1.local_listener='(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.33.31)(PORT = 22))'
Node2.local_listener='(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.33.32)(PORT = 22))'
*.open_cursors=300
*.pga_aggregate_target=3357540352
*.processes=150
*.remote_listener='LISTENERS_ZLDB'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
zldb2.thread=2
zldb1.thread=1
*.undo_management='AUTO'
*.undo_retention=86400
zldb1.undo_tablespace='UNDOTBS1'
zldb2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/u01/app/oracle/admin/zldb/udump'

插曲2：从上可以看到，该库的listeners参数肯定是被修改过，因为从alert日志文件内容看到下述信息：

ALTER SYSTEM SET local_listener='(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.33.31)(PORT = 22))' SCOPE=BOTH SID='Node1';
Fri Nov 23 11:36:19 CST 2012
ALTER SYSTEM SET local_listener='(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.33.32)(PORT = 22))' SCOPE=BOTH SID='Node2';

3 修改RAC数据库的参数文件，修改为一个可以为单实例数据库使用的初始化参数文件。修改之后的内容如下：

*.__db_cache_size=465567744
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__shared_pool_size=541065216
*.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/admin/zldb/adump'
*.background_dump_dest='/u01/app/admin/zldb/bdump'
#*.cluster_database_instances=1
*.cluster_database=false
*.compatible='10.2.0.5.0'
*.control_files='/u01/app/oradata/zldb/control01.ctl'
*.core_dump_dest='/u01/app/admin/zldb/cdump'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oradata/zldb'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='zldb'
*.db_recovery_file_dest='/u01/app/oradata/zldb'
*.db_recovery_file_dest_size=107374182
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zldbXDB)'
*.instance_number=1
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=3357540352
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
*.thread=1
*.undo_management='AUTO'
*.undo_retention=86400
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/admin/zldb/udump'

4 创建对应的目录。在一个新的已经安装好10.2.0.5.0软件的环境下，创建下述目录：

/u01/app/oradata/zldb
/u01/app/admin/zldb/adump
/u01/app/admin/zldb/bdump
/u01/app/admin/zldb/cdump
/u01/app/admin/zldb/udump

5 启动单实例库到NOMOUNT状态。

[oracle@localhost ~]$ export ORACLE_SID=zldb
[oracle@localhost ~]$ env|grep ORA
ORACLE_SID=zldb
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/10.2.5/db_1
[oracle@localhost zl]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 21 17:42:57 2013

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

Connected to an idle instance.

SQL> startup nomout pfile='/home/oracle/pfile.txt';
SP2-0714: invalid combination of STARTUP options
SQL> startup nomount pfile='/home/oracle/pfile.txt';
ORACLE instance started.

Total System Global Area 1056964608 bytes
Fixed Size                  2101608 bytes
Variable Size             260050584 bytes
Database Buffers          788529152 bytes
Redo Buffers                6283264 bytes
SQL>

6 在新环境下使用RMAN工具，还原控制文件。

[oracle@localhost ~]$ export ORACLE_SID=zldb
[oracle@localhost ~]$ env|grep ORA
ORACLE_SID=zldb
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/10.2.5/db_1
[oracle@localhost zl]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Aug 21 17:53:09 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: zldb (not mounted)

RMAN> restore controlfile from '/home/oracle/zl/1823.bak';

Starting restore at 2013/08/21 17:55:49
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oradata/zldb/control01.ctl
Finished restore at 2013/08/21 17:55:55

RMAN>

7 MOUNT 数据库(可直接在RMAN中执行。也可以通过SQL*PluS完成，这里直接在RMAN下完成。)，然后注册备份信息到控制文件中：

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with '/home/oracle/zl';

Starting implicit crosscheck backup at 2013/08/21 17:56:53
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 186 objects
Finished implicit crosscheck backup at 2013/08/21 17:56:56

Starting implicit crosscheck copy at 2013/08/21 17:56:56
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2013/08/21 17:56:56

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /home/oracle/zl

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/zl/1824.bak
File Name: /home/oracle/zl/arc1.arc
File Name: /home/oracle/zl/arc2.arc
File Name: /home/oracle/zl/1826.bak
File Name: /home/oracle/zl/1827.bak
File Name: /home/oracle/zl/1822.bak
File Name: /home/oracle/zl/1825.bak
File Name: /home/oracle/zl/1823.bak

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/zl/1824.bak
File Name: /home/oracle/zl/arc1.arc
File Name: /home/oracle/zl/arc2.arc
File Name: /home/oracle/zl/1826.bak
File Name: /home/oracle/zl/1827.bak
File Name: /home/oracle/zl/1822.bak
File Name: /home/oracle/zl/1825.bak
File Name: /home/oracle/zl/1823.bak

RMAN>

8 然后restore database。由于之前的数据文件是存放在ASM磁盘组上的，而这里新的单实例环境是使用文件系统来存放数据库的，所以，在RMAN工具中需要使用set newname来重命名数据文件，并将该信息重新写入到控制文件。

RMAN> run
2> {
3> allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
4> 5> 6> allocate channel c4 type disk;
7> set newname for datafile 1 to '/u01/app/oradata/zldb/1.dbf';
8> set newname for datafile 2 to '/u01/app/oradata/zldb/2.dbf';
9> set newname for datafile 3 to '/u01/app/oradata/zldb/3.dbf';
10> set newname for datafile 4 to '/u01/app/oradata/zldb/4.dbf';
11> set newname for datafile 5 to '/u01/app/oradata/zldb/5.dbf';
12> set newname for datafile 6 to '/u01/app/oradata/zldb/6.dbf';
13> set newname for datafile 7 to '/u01/app/oradata/zldb/7.dbf';
14> set newname for datafile 8 to '/u01/app/oradata/zldb/8.dbf';
15> set newname for datafile 9 to '/u01/app/oradata/zldb/9.dbf';
16> set newname for datafile 10 to '/u01/app/oradata/zldb/10.dbf';
17> set newname for datafile 11 to '/u01/app/oradata/zldb/11.dbf';
18> set newname for datafile 12 to '/u01/app/oradata/zldb/12.dbf';
19> set newname for datafile 13 to '/u01/app/oradata/zldb/13.dbf';
20> set newname for datafile 14 to '/u01/app/oradata/zldb/14.dbf';
21> set newname for datafile 15 to '/u01/app/oradata/zldb/15.dbf';
22> set newname for datafile 16 to '/u01/app/oradata/zldb/16.dbf';
23> set newname for datafile 17 to '/u01/app/oradata/zldb/17.dbf';
24> set newname for datafile 18 to '/u01/app/oradata/zldb/18.dbf';
25> set newname for datafile 19 to '/u01/app/oradata/zldb/19.dbf';
26> set newname for datafile 20 to '/u01/app/oradata/zldb/20.dbf';
27> set newname for datafile 21 to '/u01/app/oradata/zldb/21.dbf';
28> set newname for datafile 22 to '/u01/app/oradata/zldb/22.dbf';
29> set newname for datafile 23 to '/u01/app/oradata/zldb/23.dbf';
30> set newname for datafile 24 to '/u01/app/oradata/zldb/24.dbf';
31> set newname for datafile 25 to '/u01/app/oradata/zldb/25.dbf';
32> set newname for datafile 26 to '/u01/app/oradata/zldb/26.dbf';
33> restore database;
34> switch datafile all;
35> }

released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=156 devtype=DISK

allocated channel: c2
channel c2: sid=154 devtype=DISK

allocated channel: c3
channel c3: sid=153 devtype=DISK

allocated channel: c4
channel c4: sid=152 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2013/08/21 17:57:23

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /u01/app/oradata/zldb/6.dbf
restoring datafile 00023 to /u01/app/oradata/zldb/23.dbf
restoring datafile 00024 to /u01/app/oradata/zldb/24.dbf
restoring datafile 00025 to /u01/app/oradata/zldb/25.dbf
restoring datafile 00026 to /u01/app/oradata/zldb/26.dbf
channel c1: reading from backup piece /home/oracle/zl/1822.bak
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u01/app/oradata/zldb/5.dbf
restoring datafile 00016 to /u01/app/oradata/zldb/16.dbf
restoring datafile 00017 to /u01/app/oradata/zldb/17.dbf
restoring datafile 00019 to /u01/app/oradata/zldb/19.dbf
restoring datafile 00020 to /u01/app/oradata/zldb/20.dbf
restoring datafile 00021 to /u01/app/oradata/zldb/21.dbf
restoring datafile 00022 to /u01/app/oradata/zldb/22.dbf
channel c2: reading from backup piece /home/oracle/zl/1826.bak
channel c3: starting datafile backupset restore
channel c3: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u01/app/oradata/zldb/3.dbf
restoring datafile 00004 to /u01/app/oradata/zldb/4.dbf
restoring datafile 00007 to /u01/app/oradata/zldb/7.dbf
restoring datafile 00009 to /u01/app/oradata/zldb/9.dbf
restoring datafile 00011 to /u01/app/oradata/zldb/11.dbf
restoring datafile 00014 to /u01/app/oradata/zldb/14.dbf
restoring datafile 00018 to /u01/app/oradata/zldb/18.dbf
channel c3: reading from backup piece /home/oracle/zl/1827.bak
channel c4: starting datafile backupset restore
channel c4: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oradata/zldb/1.dbf
restoring datafile 00002 to /u01/app/oradata/zldb/2.dbf
restoring datafile 00008 to /u01/app/oradata/zldb/8.dbf
restoring datafile 00010 to /u01/app/oradata/zldb/10.dbf
restoring datafile 00012 to /u01/app/oradata/zldb/12.dbf
restoring datafile 00013 to /u01/app/oradata/zldb/13.dbf
restoring datafile 00015 to /u01/app/oradata/zldb/15.dbf
channel c4: reading from backup piece /home/oracle/zl/1825.bak
channel c1: restored backup piece 1
piece handle=/home/oracle/zl/1822.bak tag=TAG20130729T230017
channel c1: restore complete, elapsed time: 00:05:41

channel c3: restored backup piece 1
piece handle=/home/oracle/zl/1827.bak tag=TAG20130729T230017
channel c3: restore complete, elapsed time: 00:11:29
channel c4: restored backup piece 1
piece handle=/home/oracle/zl/1825.bak tag=TAG20130729T230017
channel c4: restore complete, elapsed time: 00:11:30
channel c2: restored backup piece 1
piece handle=/home/oracle/zl/1826.bak tag=TAG20130729T230017
channel c2: restore complete, elapsed time: 00:11:37
Finished restore at 2013/08/21 18:09:01

datafile 1 switched to datafile copy
input datafile copy recid=27 stamp=824062143 filename=/u01/app/oradata/zldb/1.dbf
datafile 2 switched to datafile copy
input datafile copy recid=28 stamp=824062143 filename=/u01/app/oradata/zldb/2.dbf
datafile 3 switched to datafile copy
input datafile copy recid=29 stamp=824062143 filename=/u01/app/oradata/zldb/3.dbf
datafile 4 switched to datafile copy
input datafile copy recid=30 stamp=824062143 filename=/u01/app/oradata/zldb/4.dbf
datafile 5 switched to datafile copy
input datafile copy recid=31 stamp=824062143 filename=/u01/app/oradata/zldb/5.dbf
datafile 6 switched to datafile copy
input datafile copy recid=32 stamp=824062143 filename=/u01/app/oradata/zldb/6.dbf
datafile 7 switched to datafile copy
input datafile copy recid=33 stamp=824062143 filename=/u01/app/oradata/zldb/7.dbf
datafile 8 switched to datafile copy
input datafile copy recid=34 stamp=824062143 filename=/u01/app/oradata/zldb/8.dbf
datafile 9 switched to datafile copy
input datafile copy recid=35 stamp=824062144 filename=/u01/app/oradata/zldb/9.dbf
datafile 10 switched to datafile copy
input datafile copy recid=36 stamp=824062144 filename=/u01/app/oradata/zldb/10.dbf
datafile 11 switched to datafile copy
input datafile copy recid=37 stamp=824062144 filename=/u01/app/oradata/zldb/11.dbf
datafile 12 switched to datafile copy
input datafile copy recid=38 stamp=824062144 filename=/u01/app/oradata/zldb/12.dbf
datafile 13 switched to datafile copy
input datafile copy recid=39 stamp=824062144 filename=/u01/app/oradata/zldb/13.dbf
datafile 14 switched to datafile copy
input datafile copy recid=40 stamp=824062144 filename=/u01/app/oradata/zldb/14.dbf
datafile 15 switched to datafile copy
input datafile copy recid=41 stamp=824062144 filename=/u01/app/oradata/zldb/15.dbf
datafile 16 switched to datafile copy
input datafile copy recid=42 stamp=824062144 filename=/u01/app/oradata/zldb/16.dbf
datafile 17 switched to datafile copy
input datafile copy recid=43 stamp=824062144 filename=/u01/app/oradata/zldb/17.dbf
datafile 18 switched to datafile copy
input datafile copy recid=44 stamp=824062144 filename=/u01/app/oradata/zldb/18.dbf
datafile 19 switched to datafile copy
input datafile copy recid=45 stamp=824062144 filename=/u01/app/oradata/zldb/19.dbf
datafile 20 switched to datafile copy
input datafile copy recid=46 stamp=824062144 filename=/u01/app/oradata/zldb/20.dbf
datafile 21 switched to datafile copy
input datafile copy recid=47 stamp=824062144 filename=/u01/app/oradata/zldb/21.dbf
datafile 22 switched to datafile copy
input datafile copy recid=48 stamp=824062144 filename=/u01/app/oradata/zldb/22.dbf
datafile 23 switched to datafile copy
input datafile copy recid=49 stamp=824062144 filename=/u01/app/oradata/zldb/23.dbf
datafile 24 switched to datafile copy
input datafile copy recid=50 stamp=824062144 filename=/u01/app/oradata/zldb/24.dbf
datafile 25 switched to datafile copy
input datafile copy recid=51 stamp=824062144 filename=/u01/app/oradata/zldb/25.dbf
datafile 26 switched to datafile copy
input datafile copy recid=52 stamp=824062144 filename=/u01/app/oradata/zldb/26.dbf
released channel: c1
released channel: c2
released channel: c3
released channel: c4

RMAN>

9 重命名联机日志文件。
重命名之前：

SQL> col status for a10
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------- ------------- -------------------
         1          1       1373   52428800          2 YES       INACTIVE       187740095 2013/07/29 16:58:43
         2          1       1374   52428800          2 NO        CURRENT        188086667 2013/07/29 22:00:53
         3          2       1989   52428800          2 NO        CURRENT        188149113 2013/07/29 23:00:04
         4          2       1988   52428800          2 YES       INACTIVE       188096691 2013/07/29 22:01:50

SQL> col member for a50
SQL> select * from v$logfile;

    GROUP# STATUS     TYPE                  MEMBER                                             IS_RECOVE
---------- ---------- --------------------- -------------------------------------------------- ---------
         1            ONLINE                +DATA/zldb/onlinelog/group_1.257.793214967         NO
         1            ONLINE                +FLASH/zldb/onlinelog/group_1.257.793214969        YES
         2            ONLINE                +DATA/zldb/onlinelog/group_2.258.793214969         NO
         2            ONLINE                +FLASH/zldb/onlinelog/group_2.258.793214969        YES
         3            ONLINE                +DATA/zldb/onlinelog/group_3.265.793215729         NO
         3            ONLINE                +FLASH/zldb/onlinelog/group_3.259.793215729        YES
         4            ONLINE                +DATA/zldb/onlinelog/group_4.266.793215731         NO
         4            ONLINE                +FLASH/zldb/onlinelog/group_4.260.793215731        YES

8 rows selected.

SQL>

开始重命名：

SQL> alter database rename file '+DATA/zldb/onlinelog/group_1.257.793214967' to '/u01/app/oradata/zldb/redo01a.log';

Database altered.

SQL> alter database rename file '+FLASH/zldb/onlinelog/group_1.257.793214969' to '/u01/app/oradata/zldb/redo01b.log';

Database altered.

SQL> alter database rename file '+DATA/zldb/onlinelog/group_2.258.793214969' to '/u01/app/oradata/zldb/redo02a.log';

Database altered.

SQL> alter database rename file '+FLASH/zldb/onlinelog/group_2.258.793214969' to '/u01/app/oradata/zldb/redo02b.log';

Database altered.

SQL> alter database rename file '+DATA/zldb/onlinelog/group_3.265.793215729' to '/u01/app/oradata/zldb/redo03a.log';

Database altered.

SQL> alter database rename file '+FLASH/zldb/onlinelog/group_3.259.793215729' to '/u01/app/oradata/zldb/redo03b.log';

Database altered.

SQL> alter database rename file '+DATA/zldb/onlinelog/group_4.266.793215731' to '/u01/app/oradata/zldb/redo04a.log';

Database altered.

SQL> alter database rename file '+FLASH/zldb/onlinelog/group_4.260.793215731' to '/u01/app/oradata/zldb/redo04b.log';

Database altered.

SQL>

重命名之后：

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------- ------------- -------------------
         1          1       1373   52428800          2 YES       INACTIVE       187740095 2013/07/29 16:58:43
         2          1       1374   52428800          2 NO        CURRENT        188086667 2013/07/29 22:00:53
         3          2       1989   52428800          2 NO        CURRENT        188149113 2013/07/29 23:00:04
         4          2       1988   52428800          2 YES       INACTIVE       188096691 2013/07/29 22:01:50

SQL> select * from v$logfile;

    GROUP# STATUS     TYPE                  MEMBER                                             IS_RECOVE
---------- ---------- --------------------- -------------------------------------------------- ---------
         1            ONLINE                /u01/app/oradata/zldb/redo01a.log                  NO
         1            ONLINE                /u01/app/oradata/zldb/redo01b.log                  NO
         2            ONLINE                /u01/app/oradata/zldb/redo02a.log                  NO
         2            ONLINE                /u01/app/oradata/zldb/redo02b.log                  NO
         3            ONLINE                /u01/app/oradata/zldb/redo03a.log                  NO
         3            ONLINE                /u01/app/oradata/zldb/redo03b.log                  NO
         4            ONLINE                /u01/app/oradata/zldb/redo04a.log                  NO
         4            ONLINE                /u01/app/oradata/zldb/redo04b.log                  NO

8 rows selected.

SQL>

10 RECOVER database。
这里，从第7步骤执行后的结果，我们可以从RMAN中看到，当前环境下，控制文件里记录的新的归档日志文件备份信息如下，其中已经包含RAC双线程下从7月29号22:00到7月30号16:20分之间的所有归档日志的备份信息：

RMAN> list backup of archivelog all;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
1824    3.50K      DISK        00:00:00     2013/07/31 16:34:06
        BP Key: 1824   Status: AVAILABLE  Compressed: NO  Tag: TAG20130731T163406
        Piece Name: /home/oracle/zl/arc1.arc

  List of Archived Logs in backup set 1824
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    1374    188086667  2013/07/29 22:00:53 188173593  2013/07/29 23:25:01
  1    1375    188173593  2013/07/29 23:25:01 188569236  2013/07/30 07:00:24
  1    1376    188569236  2013/07/30 07:00:24 188847801  2013/07/30 11:48:32
  1    1377    188847801  2013/07/30 11:48:32 189110687  2013/07/30 16:21:18

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
1825    515.04M    DISK        00:00:00     2013/07/31 16:32:30
        BP Key: 1825   Status: AVAILABLE  Compressed: NO  Tag: TAG20130731T163230
        Piece Name: /home/oracle/zl/arc2.arc

  List of Archived Logs in backup set 1825
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  2    1987    188086615  2013/07/29 22:00:52 188096691  2013/07/29 22:01:50
  2    1988    188096691  2013/07/29 22:01:50 188149113  2013/07/29 23:00:04
  2    1989    188149113  2013/07/29 23:00:04 188173580  2013/07/29 23:25:01
  2    1990    188173580  2013/07/29 23:25:01 188279567  2013/07/30 01:21:06
  2    1991    188279567  2013/07/30 01:21:06 188423782  2013/07/30 04:09:46
  2    1992    188423782  2013/07/30 04:09:46 188569192  2013/07/30 07:00:24
  2    1993    188569192  2013/07/30 07:00:24 188672390  2013/07/30 08:56:37
  2    1994    188672390  2013/07/30 08:56:37 188756632  2013/07/30 10:16:49
  2    1995    188756632  2013/07/30 10:16:49 188847773  2013/07/30 11:48:31
  2    1996    188847773  2013/07/30 11:48:31 188937795  2013/07/30 13:26:24
  2    1997    188937795  2013/07/30 13:26:24 189020185  2013/07/30 14:49:20
  2    1998    189020185  2013/07/30 14:49:20 189110668  2013/07/30 16:21:17

RMAN>

开始RECOVER：

RMAN> recover database;

Starting recover at 2013/08/26 16:54:48
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1374
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1375
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1376
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1377
channel ORA_DISK_1: reading from backup piece /home/oracle/zl/arc1.arc
Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup 
ORA-01110: data file 1: '/u01/app/oradata/zldb/1.dbf'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/26/2013 16:54:52
ORA-19870: error reading backup piece /home/oracle/zl/arc1.arc
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 512 bytes disk space from 107374182 limit

RMAN>

插曲3：RECOVER报错，开始处理报错：

[oracle@localhost zldb]$ oerr ora 19870
19870, 00000, "error reading backup piece %s"
// *Cause: This error should be followed by other errors indicating
//         the cause of the problem.
// *Action: See other errors actions.
//          
// Do not use message 19871; it is used by RMAN client for testing previous
// resync time when using backup/standby control file.
[oracle@localhost zldb]$ oerr ora 19809
19809, 00000, "limit exceeded for recovery files"
//*Cause: The limit for recovery files specified by the
//        DB_RECOVERY_FILE_DEST_SIZE was exceeded.
//*Action:The error is accompanied by 19804. See message 19804 for further
//        details.
[oracle@localhost zldb]$ oerr ora 19804
19804, 00000, "cannot reclaim %s bytes disk space from %s limit"
// *Cause: Oracle cannot reclaim disk space of specified bytes from the
//         DB_RECOVERY_FILE_DEST_SIZE limit.
// *Action: There are five possible solutions:
//          1) Take frequent backup of recovery area using RMAN.
//          2) Consider changing RMAN retention policy.
//          3) Consider changing RMAN archivelog deletion policy.
//          4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
//          5) Delete files from recovery area using RMAN.
[oracle@localhost zldb]$

从报错信息来看，应该是DB_RECOVERY_FILE_DEST_SIZE参数限制了数据恢复区的大小，导致RECOVER失败：

SQL> show parameter db_recover

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest                string                            /u01/app/oradata/zldb
db_recovery_file_dest_size           big integer                       107374182
SQL> alter system set db_recovery_file_dest_size=40G;

System altered.

SQL> show parameter db_recover

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest                string                            /u01/app/oradata/zldb
db_recovery_file_dest_size           big integer                       40G
SQL>

重新执行RECOVER：

RMAN> recover database;

Starting recover at 2013/08/26 16:59:28
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1374
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1375
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1376
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1377
channel ORA_DISK_1: reading from backup piece /home/oracle/zl/arc1.arc
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/zl/arc1.arc tag=TAG20130731T163406
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1989
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1990
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1991
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1992
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1993
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1994
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1995
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1996
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1997
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1998
channel ORA_DISK_1: reading from backup piece /home/oracle/zl/arc2.arc
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/zl/arc2.arc tag=TAG20130731T163230
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1989_91p67mxo_.arc thread=2 sequence=1989
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_1_1374_91p67kqh_.arc thread=1 sequence=1374
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1989_91p67mxo_.arc recid=3334 stamp=824489982
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1990_91p67my5_.arc thread=2 sequence=1990
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_1_1374_91p67kqh_.arc recid=3330 stamp=824489969
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_1_1375_91p67kqz_.arc thread=1 sequence=1375
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1990_91p67my5_.arc recid=3335 stamp=824489982
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1991_91p67myc_.arc thread=2 sequence=1991
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1991_91p67myc_.arc recid=3336 stamp=824489982
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1992_91p67myq_.arc thread=2 sequence=1992
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1992_91p67myq_.arc recid=3337 stamp=824489982
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1993_91p67yp3_.arc thread=2 sequence=1993
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_1_1375_91p67kqz_.arc recid=3331 stamp=824489969
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_1_1376_91p67kr9_.arc thread=1 sequence=1376
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1993_91p67yp3_.arc recid=3342 stamp=824489987
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1994_91p67ysz_.arc thread=2 sequence=1994
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1994_91p67ysz_.arc recid=3343 stamp=824489987
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1995_91p67n06_.arc thread=2 sequence=1995
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1995_91p67n06_.arc recid=3338 stamp=824489982
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1996_91p67n0f_.arc thread=2 sequence=1996
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_1_1376_91p67kr9_.arc recid=3332 stamp=824489969
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_1_1377_91p67krh_.arc thread=1 sequence=1377
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1996_91p67n0f_.arc recid=3339 stamp=824489982
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1997_91p67n0o_.arc thread=2 sequence=1997
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1997_91p67n0o_.arc recid=3340 stamp=824489982
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1998_91p67ylw_.arc thread=2 sequence=1998
channel default: deleting archive log(s)
archive log filename=/u01/app/oradata/zldb/ZLDB/archivelog/2013_08_26/o1_mf_2_1998_91p67ylw_.arc recid=3341 stamp=824489987
unable to find archive log
archive log thread=2 sequence=1999
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/26/2013 17:01:32
RMAN-06054: media recovery requesting unknown log: thread 2 seq 1999 lowscn 189110668

RMAN>

这次，从RECOVER报错信息看到RMAN识别不了 thread 2 seq 1999的归档日志。的确，这也是我们预期的，因为thread 2 seq 1999的归档日志从上述的归档日志备份信息是不存在的，所以，RMAN不识别，再者也不是我们需要的归档日志。恢复到此阶段，直接OK。
11 重新以resetlogs方式来启动新的单实例数据库，并确认恢复之后的结果。

SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> select count(*) from CUL_GMAR.OPM_BL_HEADER;

  COUNT(*)
----------
      7791

SQL>

经和项目组确认，已经是最大限度的找回了误操作的数据。

12 删除RAC的多余UNDO表空间和线程。
处理原RAC下的第2个线程：

SQL> select THREAD#, STATUS, ENABLED from v$thread;

   THREAD# STATUS     ENABLED
---------- ---------- ------------------------
         1 OPEN       PUBLIC
         2 CLOSED     PUBLIC

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------- ------------- -------------------
         1          1         19   52428800          2 YES       INACTIVE       189209640 2013/08/26 22:00:54
         2          1         20   52428800          2 NO        CURRENT        189295930 2013/08/27 02:00:13
         3          2          7   52428800          2 NO        CURRENT        189180639 2013/08/26 17:51:27
         4          2          6   52428800          2 YES       INACTIVE       189169179 2013/08/26 17:50:16

SQL>

删除第2个线程，及删除线程2的联机重做日志文件：

SQL> alter database disable thread 2;

Database altered.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance zldb2 (thread 2) needs to be archived
ORA-00312: online log 3 thread 2: '/u01/app/oradata/zldb/redo03a.log'
ORA-00312: online log 3 thread 2: '/u01/app/oradata/zldb/redo03b.log'

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select THREAD#, STATUS, ENABLED from v$thread;

   THREAD# STATUS     ENABLED
---------- ---------- ------------------------
         1 OPEN       PUBLIC

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------- ------------- -------------------
         1          1         19   52428800          2 YES       INACTIVE       189209640 2013/08/26 22:00:54
         2          1         20   52428800          2 NO        CURRENT        189295930 2013/08/27 02:00:13

SQL>

删除多余的UNDO表空间：

SQL> select tablespace_name,contents from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME                                                                            CONTENTS
------------------------------------------------------------------------------------------ ---------------------------
UNDOTBS1                                                                                   UNDO
UNDOTBS2                                                                                   UNDO

SQL> show parameter undo

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
undo_management                      string                            AUTO
undo_retention                       integer                           86400
undo_tablespace                      string                            UNDOTBS1
SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,contents from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME                                                                            CONTENTS
------------------------------------------------------------------------------------------ ---------------------------
UNDOTBS1                                                                                   UNDO

SQL> show parameter undo

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
undo_management                      string                            AUTO
undo_retention                       integer                           86400
undo_tablespace                      string                            UNDOTBS1
SQL>

临时表空间的处理，由于在第11步骤中，RESETLOGS方式启库的时候，从告警日志文件中看到下述信息，数据库自动重新创建临时文件，并且从数据库中可以验证到，故不需重新创建临时表空间：

Mon Aug 26 17:16:42 CST 2013
File 201 not verified due to error ORA-01157
Mon Aug 26 17:16:42 CST 2013
Dictionary check complete
Mon Aug 26 17:16:42 CST 2013
SMON: enabling tx recovery
Mon Aug 26 17:16:43 CST 2013
Re-creating tempfile +DATA/zldb/tempfile/temp.262.793214975 as /u01/app/oradata/zldb/ZLDB/datafile/o1_mf_temp_91p77vly_.tmp
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan

SQL> select name from v$tempfile;

NAME
------------------------------------------------------------
/u01/app/oradata/zldb/ZLDB/datafile/o1_mf_temp_91p77vly_.tmp

SQL>

小结：

1 数据库上的各种操作要谨慎，尤其尤其是生产库。如果不是项目组的那个误操作，也不会促成这篇文字；当然，插曲1中提到的那个listener参数被误修改就是另外一种误修改；

2 数据库的备份很重要，如果该库没做备份的话，估计恢复数据就是难上加难的事儿了；

声讨淘宝上及其他各类非法销售本人Oracle视频作品的卖家和网站

8月 1,2013 有12条评论 Dataguard, oracle 11g, RAC

被侵权了，被盗版了，虽然这已经不是一天两天的事儿了，并且还在继续…

同时，深知这是一个相当不重视知识产权，不够尊重个人劳动价值的国度，反正就是各种不靠谱，不多说。

并且，也明白，码出的这些文字几乎是瞬间就能被淹没在网络中，消失的无声无息。但，还是忍不住声讨那些在淘宝上在未经本人任何许可的情况下销售本人的Oracle数据库教学视频的卖家。在此，我很无奈的被问候你们家的某些人！

声明：

1 截止目前我所有的oracle视频均为原创，未经本人许可，不得以任何形式在互联网上兜售、获得商业利益的行为;

2 截止目前我所有的oracle视频唯一指定的销售平台只有播布客;

3 其他一切渠道，包括淘宝、美和在线等等等各类三教九流之辈均为盗版。是的，你们侵权了，侵了我的知识产权。

附：给出部分非法淘宝的黑心卖家列表及其他非法销售本人Oracle视频的卖家链接地址，这篇文章我会不定期的更新。你们这帮混蛋行为的链接地址在这里呢：

坏蛋 01：http://item.taobao.com/item.htm?spm=a230r.1.14.1.iaFvu9&id=19972027533&_u=v1fov9jd9105

坏蛋 02：http://item.taobao.com/item.htm?spm=a230r.1.14.10.iaFvu9&id=19225714645&_u=v1fov9jd3d58

坏蛋 03：http://item.taobao.com/item.htm?spm=a230r.1.14.18.iaFvu9&id=17768833172&_u=v1fov9jdb5fa

坏蛋 04：http://item.taobao.com/item.htm?spm=a230r.1.14.28.PQJZdM&id=25385320741&_u=11fov9jd0cf7

坏蛋 05：http://item.taobao.com/item.htm?spm=a230r.1.14.18.PQJZdM&id=17768833172&_u=11fov9jd2efd

坏蛋太多了，点击这里可以戳出一坨一坨的坏蛋，这也有一坨坏蛋呢！

你们这帮混蛋，下面给出你们的不要脸的脸面贴图：

这篇短文字写于2013年7月31日23点43分，于给上海一客户恢复数据的空当时间内。

最后，大家看到这篇文字的时候，如果有更好的办法可以制止这帮坏蛋的行为或者建议，请回复这篇文章，多谢！当然，如果你们当中有需要这些Oracle视频资料的话，更是可以直接联系我。你们的支持，是我前进的动力。

不要轻易修改你的主机名

6月 12,2013 留下评论 Dataguard, RAC

这篇文章不是标题党，是在实际工作中真切的案例。

场景：这是一套Windows Server 2008 R2 X64的系统，跑了一套10.2.0.5.0的oracle物理备库，运行一切正常。在客户的要求下，需要调整该服务器的机器名。

步骤：整理好调整的思路后，开始执行操作[包括停备库，ASM实例，修改hosts文件、tnsnames.ora文件等]，在客户IT人员修改完机器名并重启服务器之后，发现悲剧的一幕，机器无法正常启动，不过客户端倒是可以ping通服务器，但是无法通过远程桌面连接。

怎么办呢？经过分析和定位，感觉极有可能出问题的地方就是OracleCSService这个服务，而且该服务的启动类型是自动启动。也就是说该服务项会加载到windows系统的启动项里，随着操作系统的启动而启动，而该服务又是hard-coded,应该是同机器名进行“捆绑”的，由于修改了机器名，导致OracleCSService服务项不能正常启动，进而导致操作系统无法正常启动。

找到解决问题的思路之后，可以尝试重启服务器，进入安全模式，禁用该服务，然后重启机器，结果该机器已经无法再次进入安全模式，之前进去过，原因未知，客户IT硬件人员操作。

于是，一边尝试可以进入安全模式的方法，一边估计下下策的重装Windows系统，重建Dataguard的方案。结果，更为不可思议的是，服务器特么自己能够正常启动了，大家什么都没操作。接下来，就登录上去，果断重建了OracleCSService服务：

删除该服务：

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Administrator>C:\oracle\product\10.2.0\db_1\BIN\localconfig.bat
usage:  crssetup 
                  config   - configure and startup the cluster on nodes
                  add      - add specified nodes to the cluster
                  del      - delete the specified nodes from the cluster
                  deconfig - wipe out all cluster configuration information
                  ldel     - local css delete from oracle home
                  lres     - local css home reset to new oracle home
                  ladd     - local css add to oracle home
                  shutdown - shutdown the selected nodes
                  upgrade  - upgrade the specified nodes
                  help     - print out this information

C:\Users\Administrator>C:\oracle\product\10.2.0\db_1\BIN\localconfig.bat deconfig
GetConfiguredClusterNodes:  failed to initialize subsystem, rc(21)
failed to determine remaining nodes in the cluster
failed during critical configuration information
  please supply <-force> option to continue

C:\Users\Administrator>C:\oracle\product\10.2.0\db_1\BIN\localconfig.bat deconfig -force
GetConfiguredClusterNodes:  failed to initialize subsystem, rc(21)
failed to determine remaining nodes in the cluster
failed during critical configuration information
  <-force> option specified, continuing
Step 1:  shutting down node apps
failed executing check for CRS resources
  [ 2 ] The system cannot find the file specified.
failed executing check for CRS resources

failure determining CRS resources state, continuing due to FORCE option
  DEBRESTDDB            Removing node apps
PRKC-1056 : Failed to get the hostname for node DEBRESTDDB
PRKH-1010 : Unable to communicate with CRS services.
  [Communications Error(Native: prsr_initCLSS:[3])]
  DEBRESTDDB            Removing ONS configuration
failed to remove ONS configuration
  [ 2 ] The system cannot find the file specified.
  DEBRESTDDB            failed to execute removal of ONS configuration
failuring during delete of node apps, continuing
Step 2:  shutting down local CRS stack
  DEBRESTDDB            failed to located service OracleEVMService, err(1060)
failed to stop CRS stack on all nodes to be removed, continuing
Step 3:  removing CRS stack from requested nodes
Step 4:  stopping extra CRS services
Step 5:  cleanup up registry keys
Step 6:  perform cleanup of the OCR repository C:\oracle\product\10.2.0\db_1\cdata\localhost\local.ocr
successful deconfiguration of the cluster

C:\Users\Administrator>

重建该服务：

C:\Users\Administrator>C:\oracle\product\10.2.0\db_1\BIN\localconfig.bat add
Step 1:  creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'administrator', privgrp ''..
Operation successful.
Step 2:  creating new CSS service
successfully created local CSS service
successfully added CSS to home

C:\Users\Administrator>

最后，启动ASM实例，启动物理备库，打开同主库的同步，完成同步。

值得记住的地方：

① 不要轻易修改机器名，除非必要。修改之前，一定一定要理清楚checklist，不可像本例中遗漏了OracleCSService服务项的重建；

② 对于生产环境的各种操作，真的要三思而后行；

③ 写这篇记录小文的时候，发现Metalink上有该案例的详细操作说明哇：How to change the Hostname when Oracle 10G and ASM are used [ID 422729.1]

如何正确手工启动Windows下的Oracle RAC数据库？

12月 11,2012 有4条评论 Experience, Oracle, RAC

这是一则来自于某德国客户生产环境的RAC数据库启动出现故障的案例，记录下来一是用于对自己的警醒，二是可以同广大网友分享。

操作系统环境：Windows Server 2008 R2 Enterprise version 6.1(Build 7601:Service Pack 1)

数据库环境：10gR2 10.2.0.5.0的64位RAC双节点数据库；

上周末，在顺利地对RAC数据库的几张分区表做调整之后，正常关闭RAC数据库，并重新启动2台Windows 2008 R2的操作系统之后，尝试启动Oracle CRS时，发现报错：

1 在任何一个节点的服务项里，启动OracleCRService服务时，报错，其中OracleCSService的状态一直停留在Starting状态，其它服务项无任何变化；

2 重启Windows服务器后，使用$CRS_HOME\bin\crsctl start crs在命令行尝试启动CRS时，依然报错;

3 接下去，开始检查CRS的错误日志：在C:\oracle\product\10.2.0\crs\log\dehamora002\crsd\crsd.log日志文件中看到下述报错信息：

2012-12-08 11:52:52.606: [  OCRMAS][3876]th_master:13: I AM THE NEW OCR MASTER at incar 2. Node Number 2
2012-12-08 11:52:52.606: [  OCROSD][3876]utgdv:11:could not read reg value ocrmirrorconfig_loc os error= The system could not find the environment option that was entered.

2012-12-08 11:52:52.621: [  OCROSD][3876]utgdv:11:could not read reg value ocrmirrorconfig_loc os error= The system could not find the environment option that was entered.

2012-12-08 11:52:52.637: [  OCRRAW][3876]proprioo: for disk 0 (\\.\ocrcfg), id match (1), my id set (1381592635,1028247821) total id sets (1), 1st set (1381592635,1028247821), 2nd set (0,0) my votes (2), total votes (2)
2012-12-08 11:52:52.715: [  OCRMAS][3876]th_master: Deleted ver keys from cache (master)

从上可以看出，问题应该是出现在服务器访问共享存储时出现的。果然，在远程联系德国汉堡客户IT人员检查后，发现是服务器同存储间出现了问题，协调并解决该错误。

4 再次重启Windows，并尝试启动CRS时，C:\oracle\product\10.2.0\crs\log\dehamora002\cssd\cssdOUT.log日志文件中看到下述报错信息：

Oracle Database 10g CSS Release 10.2.0.5.0 Production Copyright 1996, 2004, Oracle.  All rights reserved.
12/08/12 12:02:06  ssmain_run_css:  launching boot check 1 with c:\oracle\product\10.2.0\crs\bin\crsctl.exe check boot
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [The system cannot find the file specified.

] [2]
12/08/12 12:02:06  ssmain_run_css:  boot check returned 8, looping
12/08/12 12:02:07  ssmain_run_css:  launching boot check 2 with c:\oracle\product\10.2.0\crs\bin\crsctl.exe check boot
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [The system cannot find the file specified.

] [2]
12/08/12 12:02:07  ssmain_run_css:  boot check returned 8, looping
12/08/12 12:02:08  ssmain_run_css:  launching boot check 3 with c:\oracle\product\10.2.0\crs\bin\crsctl.exe check boot
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [The system cannot find the file specified.

] [2]

通过查询Metalink：

Can not Start CRS on Windows Cluster [ID 1115153.1]

How to Start (or stop) 10gR2 or 11gR1 Oracle Clusterware Services Manually in Windows [ID 729512.1]

OracleCSService does not start – PROC-26 error possible [ID 305093.1]

找到产生问题的原因：原来这套RAC环境下的所有Oracle服务都是手工启动的方式，正常情况下，手工启动OracleCRService服务时，会自动启动依赖的相关服务。而该环境下，oracle并没有如我们期待的那样去启动与OracleCRService相关的服务。

准确定位到原因后，解决问题的办法其实也很简单，就是如Metalink文档上说明的方案，手工依次启动. OracleObjectService 、OracleClusterVolumeService 、OracleCSService、OracleEVMService、OracleCRService 很快，RAC数据库重新正常启动！

启示：

1 对于Windows环境下的RAC，最好是将OracleObjectService的启动类型置为自动启动；

2 如果上述服务是手工启动的，那么正确手工启动Windows下的Oracle RAC数据库的顺序依次是：OracleObjectService 、OracleClusterVolumeService(if using OCFS) 、OracleCSService、OracleEVMService、OracleCRService

解决一则enq: TX – row lock contention的性能故障

11月 26,2012 有3条评论 Experience, Oracle, Performance Tuning

上周二早上，收到项目组的一封邮件：

早上联代以下时间点用户有反馈EDI导入“假死”，我们跟踪了EDI导入服务，服务是正常在跑，可能是处理的慢所以用户感觉是“假死”了，请帮忙从数据库中检查跟踪以下时间点是否有“异常”操作，多谢！

2012-11-20 9:10:10~~~~9:55:13,这个时间点内一共反馈了3次，大概是10~20分钟“假死”一次，请帮忙跟踪检查，多谢！

这是一套Windows RAC的环境，也是之前处理解决一则row cache lock引起的性能故障那套环境。下面记录一下处理的经过：

1 对这一个小时进行AWR的收集和分析，首先，从报告头中看到DB Time达到近500分钟，(DB Time)/Elapsed=8，这个比值偏高：

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	15142	20-11月-12 09:00:05	62	5.8
End Snap:	15143	20-11月-12 10:00:56	74	8.3
Elapsed:		60.85 (mins)
DB Time:		492.88 (mins)

2 再看TOP 5事件：

看到排在第一位的是enq: TX – row lock contention事件，也就是说系统中在这一个小时里产生了较为严重的行级锁等待事件。

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
CPU time		21,215		71.7
enq: TX – row lock contention	12,232	6,013	492	20.3	Application
gc cr multi block request	14,696,067	1,675	0	5.7	Cluster
gc buffer busy	441,472	719	2	2.4	Cluster
db file sequential read	4,191	25	6	.1	User I/O

通常，产生enq: TX – row lock contention事件的原因有以下几种可能：

不同的session更新或删除同一条记录；
唯一索引有重复索引；
位图索引同时被更新或同时并发的向位图索引字段上插入相同字段值；
并发的对同一个数据块上的数据进行update操作；
等待索引块完成分裂；

同时，从段的统计信息章节中，也看到下面的信息：

Segments by Row Lock Waits

% of Capture shows % of row lock waits for each top segment compared
with total row lock waits for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Obj. Type	Row Lock Waits	% of Capture
SUNISCO	SUNISCO_DATA1	BIND_PROCESS_LOG_REFNO	INDEX	159	67.66
SUNISCO	FDN_EDI_I01	IDX_EDI_WORK_QUEUE_1	INDEX	29	12.34
SUNISCO	SUNISCO_DATA1	IND_EDI_CUSTOMER_TYPE_CODE	INDEX	15	6.38
SUNISCO	SUNISCO_DATA1	IDX_EDI_MESSAGE_1	INDEX	14	5.96
SUNISCO	FDN_BASE_T01	BSE_NUM_LIST	TABLE	6	2.55

看到row lock waits发生在一个索引上。

3 那么，究竟是什么操作导致了这个enq: TX – row lock contention等待事件呢？查看系统中，当前有哪些会话产生了enq: TX – row lock contention等待事件？

SQL> select event,sid,p1,p2,p3 from v$session_wait where event='enq: TX - row lock contention';
 
EVENT                                                                   SID         P1         P2         P3
---------------------------------------------------------------- ---------- ---------- ---------- ----------
enq: TX - row lock contention                                           224 1415053316    1441815     144197
enq: TX - row lock contention                                           238 1415053316    1441815     144197
enq: TX - row lock contention                                           247 1415053316    1441815     144197
enq: TX - row lock contention                                           248 1415053316    1441815     144197
enq: TX - row lock contention                                           253 1415053316    1441815     144197
SQL>

看到SID为224,238,247,248,253的会话产生enq: TX – row lock contention等待事件。

4 查看系统中的当前会话，是在哪个对象上产生了产生了enq: TX – row lock contention等待事件？

SQL> select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention';
 
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
       369195              0               0             0
       369195              0               0             0
       369195              0               0             0
       369195              0               0             0
       369195              0               0             0
       369195              0               0             0
 
6 rows selected
SQL>

5 那么这个数据库对象为369195的对象究竟是什么呢？

SQL> select object_name,object_id from dba_objects where object_id=369195;
 
OBJECT_NAME                          OBJECT_ID
----------------------------------- ----------
BIND_PROCESS_LOG_REFNO                  369195
SQL> select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID, OBJECT_TYPE from dba_objects where object_name='BIND_PROCESS_LOG_REFNO';
 
OWNER                          OBJECT_NAME                    OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ----------------------------- ---------- -------------- -------------------
SUNISCO                        BIND_PROCESS_LOG_REFNO            369195         369195 INDEX
SQL>

可以看到，定位到的结果同上述AWR报告中段统计信息吻合，是SUNISCO这个用户下的一个索引。

6 接下来，继续看看SID为224,238,247,248,253的会话到底在执行哪些操作导致enq: TX – row lock contention等待事件？

SQL> select sid,sql_text from v$session a,v$sql b where sid in(224,238,247,248,253) and (b.sql_id=a.sql_id or b.sql_id=a.prev_sql_id);
 
       SID SQL_TEXT
---------- --------------------------------------------------------------------------------
       224 select count(1)  from EDI_MESSAGE_PROCESS_LOG where   (LOG_ID =  :P_0_0  )
       224 INSERT INTO EDI_MESSAGE_PROCESS_LOG(LOG_ID, SERVICE_TYPE, SERVICE_STATUS, INFO_C
       238 select count(1)  from EDI_MESSAGE_PROCESS_LOG where   (LOG_ID =  :P_0_0  )
       238 INSERT INTO EDI_MESSAGE_PROCESS_LOG(LOG_ID, SERVICE_TYPE, SERVICE_STATUS, INFO_C
       247 INSERT INTO EDI_MESSAGE_PROCESS_LOG(LOG_ID, SERVICE_TYPE, SERVICE_STATUS, REFNO,
       247 INSERT INTO EDI_MESSAGE_PROCESS_LOG(LOG_ID, SERVICE_TYPE, SERVICE_STATUS, REFNO,
       248 INSERT INTO EDI_MESSAGE_PROCESS_LOG (LOG_ID, SERVICE_TYPE, SERVICE_STATUS, REFNO
       248 INSERT INTO EDI_MESSAGE_PROCESS_LOG (LOG_ID, SERVICE_TYPE, SERVICE_STATUS, REFNO
       248 SELECT SEQ_NEWID.NEXTVAL FROM DUAL
       253 SELECT SEQ_NEWID.NEXTVAL FROM DUAL
       253 INSERT INTO EDI_MESSAGE_PROCESS_LOG (LOG_ID, SERVICE_TYPE, SERVICE_STATUS, REFNO
 
11 rows selected
 
SQL>

看到有SQL_ID不同的SQL在同时向EDI_MESSAGE_PROCESS_LOG这张表执行INSERT操作。

7 接下去看看EDI_MESSAGE_PROCESS_LOG这张表和索引BIND_PROCESS_LOG_REFNO之间有没有什么关系？

SQL> select index_name,table_name,index_type from user_indexes where table_name='EDI_MESSAGE_PROCESS_LOG';

INDEX_NAME                     TABLE_NAME                     INDEX_TYPE
------------------------------ ------------------------------ ----------
PK_EDI_MESSAGE_PROCESS_LOG     EDI_MESSAGE_PROCESS_LOG        NORMAL
ID_EDI_LOG_INPUT_DATE          EDI_MESSAGE_PROCESS_LOG        NORMAL
BIND_PROCESS_LOG_REFNO         EDI_MESSAGE_PROCESS_LOG        BITMAP

SQL> select index_name,table_name,column_name from user_ind_columns where table_name='EDI_MESSAGE_PROCESS_LOG';

INDEX_NAME                     TABLE_NAME                     COLUMN_NAM
------------------------------ ------------------------------ ----------
PK_EDI_MESSAGE_PROCESS_LOG     EDI_MESSAGE_PROCESS_LOG        LOG_ID
ID_EDI_LOG_INPUT_DATE          EDI_MESSAGE_PROCESS_LOG        INPUT_DATE
BIND_PROCESS_LOG_REFNO         EDI_MESSAGE_PROCESS_LOG        REFNO

SQL> select object_name,object_id,object_type,created from user_objects where object_name='BIND_PROCESS_LOG_REFNO';

OBJECT_NAME                     OBJECT_ID OBJECT_TYPE     CREATED
------------------------------ ---------- --------------- -------------------
BIND_PROCESS_LOG_REFNO             369195 INDEX           2012/11/05 10:18:28

SQL> select index_name,index_type from user_indexes where index_name='BIND_PROCESS_LOG_REFNO';

INDEX_NAME                      INDEX_TYPE
------------------------------- -----------
BIND_PROCESS_LOG_REFNO          BITMAP

SQL>

发现，这个索引BIND_PROCESS_LOG_REFNO是位于EDI_MESSAGE_PROCESS_LOG这张表的REFNO字段上的一个位图索引，而且是2012/11/05 10:18:28创建的，也就是说是近期才创建的1个位图索引。

问题定位到这一步基本比较清晰了，产生enq: TX – row lock contention事件的原因就是上述的第2个可能原因：位图索引同时被更新或同时并发的向位图索引字段上插入相同字段值。

8 那么，解决的办法也比较简单了，就是干掉这个位图索引，因为这个位图索引在这种应用场景下确实不太适合。事后，经过同客户方沟通确认，该索引是他们的一个DBA当初看到系统比较慢，而加上去的一个位图索引。

9 补充，从当时的ADDM报告中，也可以看到数据库给我们的建议：

FINDING 4: 20% impact (6013 seconds)
------------------------------------
发现 SQL 语句正处于行锁定等待。

   RECOMMENDATION 1: Application Analysis, 17% benefit (5131 seconds)
      ACTION: 在 INDEX "SUNISCO.BIND_PROCESS_LOG_REFNO" (对象 ID 为 369195)
         中检测到了严重的行争用。使用指定的阻塞 SQL 语句在应用程序逻辑中跟踪行争
用的起因。
         RELEVANT OBJECT: database object with id 369195
      RATIONALE: SQL_ID 为 "dr4uxu769tmmb" 的 SQL 语句在行锁上被阻塞。
         RELEVANT OBJECT: SQL statement with SQL_ID dr4uxu769tmmb
         INSERT INTO EDI_MESSAGE_PROCESS_LOG(LOG_ID, SERVICE_TYPE,
         SERVICE_STATUS, LOG_DATETIME, REFNO, REF_TYPE, MSG_ID, BL_NO, BL_ID,
         VOYAGE_ID, VESSEL_NAME, IMO_NO, VOYAGE_NO, FUNCTION_TYPE, INPUT_DATE,
         IN_STATUS, SYSTEM_TYPE, ERROR_LOG, FILE_NAME) VALUES ( :B1 , :B2 ,
         :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 ,
         :B14 , :B15 , :B16 , :B17 , :B18 , :B19 )
      RATIONALE: SQL_ID 为 "dxsbgubsb6r4n" 的 SQL 语句在行锁上被阻塞。
         RELEVANT OBJECT: SQL statement with SQL_ID dxsbgubsb6r4n
         INSERT INTO EDI_MESSAGE_PROCESS_LOG(LOG_ID, SERVICE_TYPE,
         SERVICE_STATUS, INFO_CODE, INFORMATION, INFO_LEVEL, LOG_DATETIME,
         REFNO, REF_TYPE, MSG_ID, BL_NO, VOYAGE_ID, VESSEL_NAME, IMO_NO,
         VOYAGE_NO, FUNCTION_TYPE, INPUT_DATE, IN_STATUS, SYSTEM_TYPE,
         ERROR_LOG, FILE_NAME) VALUES ( :B1 , :B2 , :B3 , :B4 , :B5 , :B6 ,
         :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 , :B14 , :B15 , :B16 ,
         :B17 , :B18 , :B19 , :B20 , :B21 )
      RATIONALE: SQL_ID 为 "b38qhyzvn5bdd" 的 SQL 语句在行锁上被阻塞。
         RELEVANT OBJECT: SQL statement with SQL_ID b38qhyzvn5bdd
         INSERT INTO EDI_MESSAGE_PROCESS_LOG(LOG_ID, SERVICE_TYPE,
         SERVICE_STATUS, LOG_DATETIME, REFNO, REF_TYPE, MSG_ID, BL_NO,
         VOYAGE_ID, VESSEL_NAME, IMO_NO, VOYAGE_NO, FUNCTION_TYPE, INPUT_DATE,
         IN_STATUS, SYSTEM_TYPE, ERROR_LOG, FILE_NAME) VALUES ( :B1 , :B2 ,
         :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 ,
         :B14 , :B15 , :B16 , :B17 , :B18 )
      RATIONALE: SQL_ID 为 "36k2xpx3c6wr5" 的 SQL 语句在行锁上被阻塞。
         RELEVANT OBJECT: SQL statement with SQL_ID 36k2xpx3c6wr5
         INSERT INTO EDI_MESSAGE_PROCESS_LOG(LOG_ID, SERVICE_TYPE,
         SERVICE_STATUS, REFNO, REF_TYPE, MSG_ID, BL_NO, VOYAGE_ID,
         VESSEL_NAME, IMO_NO, VOYAGE_NO, FUNCTION_TYPE, INPUT_DATE, IN_STATUS,
         SYSTEM_TYPE, ERROR_LOG, FILE_NAME) VALUES ( :B1 , :B2 , :B3 , :B4 ,
         :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 , :B14 , :B15
         , :B16 , :B17 )
      RATIONALE: 具有 ID "268", 用户 ID "31", 程序 "FC.EdiService.Import.exe" 和
模块
         "FC.EdiService.Import.exe" 的会话是构成此建议案中的优化建议的 51% 的阻
塞会话。
      RATIONALE: 具有 ID "307", 用户 ID "31", 程序 "FC.EdiService.Import.exe" 和
模块
         "FC.EdiService.Import.exe" 的会话是构成此建议案中的优化建议的 11% 的阻
塞会话。
      RATIONALE: 具有 ID "227", 用户 ID "31", 程序 "FC.EdiService.Import.exe" 和
模块
         "FC.EdiService.Import.exe" 的会话是构成此建议案中的优化建议的 11% 的阻
塞会话。
      RATIONALE: 具有 ID "273", 用户 ID "31", 程序 "FC.EdiService.Import.exe" 和
模块
         "FC.EdiService.Import.exe" 的会话是构成此建议案中的优化建议的 9% 的阻塞
会话。

10 最后，从本案例中，可以看到在日常的数据库维护中，添加或修改一些对象信息时，务必要经过严格的测试，尤其是在生产系统上做调整更应如此。同样，可以看出，数据库的一些理论基础知识对于DBA还是蛮重要的。

8月工作小记二三事

9月 3,2012 留下评论 Experience, Oracle, RAC

上个月，应该算是一个较为忙碌的工作月。其中：

1 公事的一DBA同事离职，所有的数据库这块全部压到我1个人的身上，要看的库多了，任务重了，有些库也只能做到粗犷式管理了；

2 出差2次，月初1次到上海，月末1次同样到上海；2次差旅虽说都是现场实施Oracle数据库环境的搭建，但都不是很顺利，究其原因：①项目前期同客户需求、准备工作都很有问题。如去之前，问项目组人员是实施RAC的数据库还是配置单实例的库，告知于我是单实例的库，结果一到客户现场方知是实施RAC的数据库；②个人的技术经验匮乏的短板也暴露的很明显了，尤其是存储方面的知识；

3 快速记录这2次出差上海解决问题的方法：

① 月初去上海某客户遇到的问题，客户环境采用Dell R910单台服务器，存储采用Dell MD 3200。在阵列的管理界面将存储映射到主机时，发现找不到主机，原因：犯的是低级错误，Dell MD 3200 支持级联扩展存储，误将主机的SAS卡插到阵列上的SAS OUT扩展口上，而应该将其连接到阵列上的SAS IN口。其次，在做好阵列之后，重启主机、阵列之后，发现主机依然无法找到对应的LUN，解决方法：注释掉/etc/udev/rules.d/90-dm.rules配置文件。

②第2次去上海另1客户实施RAC时，同样碰到存储的问题。只是这次的主机是2台IBM X3650M3，存储是IBM System Storage DS3500磁盘阵列柜。在阵列上做好LUN并映射到主机后，还是不能正常发现存储设备，解决办法是安装IBM提供的RDAC补丁包。下述，记录安装RDAC软件包的过程，该软件包需要到IBM官方网站下载对应的操作系统版本和阵列型号的补丁。

–

[root@node1 ~]# cd linuxrdac-09.03.0C05.0638/
[root@node1 linuxrdac-09.03.0C05.0638]# ll
total 1780
-r-xr-xr-x 1 root root     73 Apr 18 04:31 bootMpp26p.sh
drwxr-xr-x 2 root root   4096 Apr 18 04:31 dracutsetup
-r-xr-xr-x 1 root root   3808 Apr 18 04:31 genfileattributes
-r--r--r-- 1 root root   2603 Apr 18 04:31 genuniqueid.c
-r--r--r-- 1 root root   2603 Apr 18 04:31 hbaCheck
-r--r--r-- 1 root root  15136 Apr 18 04:31 License.txt
-r-xr-xr-x 1 root root   6592 Apr 18 04:31 lsvdev
-r--r--r-- 1 root root  22991 Apr 18 04:31 Makefile
-r--r--r-- 1 root root 150075 Apr 18 04:31 mppCmn_s2tos3.c
-r--r--r-- 1 root root  21321 Apr 18 04:31 mppCmn_SysInterface.c
-r--r--r-- 1 root root    814 Apr 18 04:31 mpp.conf
-r--r--r-- 1 root root 520094 Apr 18 04:31 MPP_hba.c
-r-xr-xr-x 1 root root   5155 Apr 18 04:31 mppiscsi_umountall
drwxr-xr-x 2 root root   4096 Apr 18 04:31 mpp_linux_headers
drwxr-xr-x 2 root root   4096 Apr 18 04:31 mpp_linux_sys_headers
-r--r--r-- 1 root root  57111 Apr 18 04:31 mppLnx26p_sysdep.c
-r--r--r-- 1 root root 175028 Apr 18 04:31 mppLnx26p_upper.c
-r--r--r-- 1 root root 206555 Apr 18 04:31 mppLnx26p_vhba.c
-r--r--r-- 1 root root 185100 Apr 18 04:31 mppLnx26p_vhbaio.c
-r--r--r-- 1 root root  95675 Apr 18 04:31 mppLnx26p_vhbalib.c
-r--r--r-- 1 root root  21610 Apr 18 04:31 mppLnx26p_vhbamisc.c
-r--r--r-- 1 root root  33709 Apr 18 04:31 mppLnx26p_vhbaproc.c
-r--r--r-- 1 root root 120597 Apr 18 04:31 mppLnx26p_vhbatask.c
-r--r--r-- 1 root root   1805 Apr 18 04:31 mpp_rcscript.REDHAT
-r--r--r-- 1 root root   1903 Apr 18 04:31 mpp_rcscript.SUSE
-r-xr-xr-x 1 root root  28522 Apr 18 04:31 mppSupport
-r--r--r-- 1 root root  24245 Apr 18 04:31 Readme.txt
-rw-r--r-- 1 root root   5559 Apr 18 04:31 setupDriver.REDHAT
-r--r--r-- 1 root root   8984 Apr 18 04:31 setupDriver.SUSE
-r--r--r-- 1 root root    261 Apr 18 04:31 setupMpp26p.sh
drwxr-xr-x 2 root root   4096 Apr 18 04:31 utility
[root@node1 linuxrdac-09.03.0C05.0638]# make
make[1]: Entering directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
  CC [M]  /root/linuxrdac-09.03.0C05.0638/MPP_hba.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_upper.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_sysdep.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppCmn_s2tos3.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppCmn_SysInterface.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbamisc.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbatask.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhba.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbaproc.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbalib.o
  CC [M]  /root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbaio.o
/root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbaio.c: In function a€?__mppLnx_scsi_donea€?:
/root/linuxrdac-09.03.0C05.0638/mppLnx26p_vhbaio.c:3691: warning: label a€?donea€? defined but not used
  LD [M]  /root/linuxrdac-09.03.0C05.0638/mppUpper.o
  LD [M]  /root/linuxrdac-09.03.0C05.0638/mppVhba.o
  Building modules, stage 2.
  MODPOST
  CC      /root/linuxrdac-09.03.0C05.0638/mppUpper.mod.o
  LD [M]  /root/linuxrdac-09.03.0C05.0638/mppUpper.ko
  CC      /root/linuxrdac-09.03.0C05.0638/mppVhba.mod.o
  LD [M]  /root/linuxrdac-09.03.0C05.0638/mppVhba.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
make[1]: Entering directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
  Building modules, stage 2.
  MODPOST
make[1]: Leaving directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
gcc  -I/root/linuxrdac-09.03.0C05.0638 -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_headers/ -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_sys_headers/ -c ./utility/mppUtil.c  -o mppUtil.o
/bin/bash ./genfileattributes bld
gcc  -I/root/linuxrdac-09.03.0C05.0638 -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_headers/ -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_sys_headers/ -c ./utility/mppUtil26p_sysdep.c -o mppUtilSysdep.o
gcc mppUtil.o mppUtilSysdep.o -o mppUtil
gcc -o genuniqueid genuniqueid.c
[root@node1 linuxrdac-09.03.0C05.0638]# make install
make[1]: Entering directory `/root/linuxrdac-09.03.0C05.0638'
make[2]: Entering directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
  Building modules, stage 2.
  MODPOST
make[2]: Leaving directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
make[2]: Entering directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
  Building modules, stage 2.
  MODPOST
make[2]: Leaving directory `/usr/src/kernels/2.6.18-194.el5-x86_64'
/bin/bash ./genfileattributes bld
gcc  -I/root/linuxrdac-09.03.0C05.0638 -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_headers/ -I/root/linuxrdac-09.03.0C05.0638/mpp_linux_sys_headers/ -c ./utility/mppUtil26p_sysdep.c -o mppUtilSysdep.o
gcc mppUtil.o mppUtilSysdep.o -o mppUtil
Checking Host Adapter Configuration...
Detected 1 Emulex Host Adapter Port(s) on the system
Please wait while we modify the system configuration files.
Your kernel version is 2.6.18-194.el5
Preparing to install MPP driver against this kernel version...
Generating module dependencies...
Creating new MPP initrd image...
        You must now edit your boot loader configuration file, /boot/grub/menu.lst, to 
        add a new boot menu, which uses mpp-2.6.18-194.el5.img as the initrd image.
        Now Reboot the system for MPP to take effect.
        The new boot menu entry should look something like this (note that it may 
        vary with different system configuration):

        ...

                title Red Hat Linux (2.6.18-194.el5) with MPP support
                root (hd0,5)
                kernel /vmlinuz-2.6.18-194.el5 ro root=LABEL=RH9
                initrd /mpp-2.6.18-194.el5.img
        ...
MPP driver package has been successfully installed on your system.
make[1]: Leaving directory `/root/linuxrdac-09.03.0C05.0638'
[root@node1 linuxrdac-09.03.0C05.0638]#

4 问题得以解决，也深知自己存在的问题！送给自己一句话以自勉：革命尚未成功，同志仍需努力！

遇到ORA-600 [kmgs_parameter_update_timeout_1] [1565]的错误续

7月 27,2012 留下评论 Experience, Oracle, RAC

刚发完上篇文章之后，又从老杨的一篇文章“http://blog.itpub.net/post/468/450451?SelectActiveLayout=a”中看到一些有所帮助的信息，而这个案例中在alert里看到，在这个600的错误抛出之前一条错误信息中看到：

Wed Jul 25 09:56:46  2012
Thread 1 advanced to log sequence 580 (LGWR switch)
  Current log# 1 seq# 580 mem# 0: +DATA/zhfr8db/onlinelog/group_1.271.783424599
  Current log# 1 seq# 580 mem# 1: +FLASH/zhfr8db/onlinelog/group_1.256.783424601
Wed Jul 25 10:49:39  2012
Unexpected communication failure with ASM instance:
 error 21561 (ORA-21561: 生成 OID 失败
)

的错误信息。

从后续的alert日志里，尝试关闭数据库服务器的时候抛出的错误中也有类似错误：

Wed Jul 25 10:55:03  2012
Trace dumping is performing id=[cdmp_20120725105503]
Wed Jul 25 10:56:06  2012
Restarting dead background process MMON
MMON started with pid=33, OS id=6756
Wed Jul 25 12:46:20  2012
Unexpected communication failure with ASM instance:
 error 21561 (ORA-21561: 生成 OID 失败
)
NOTE: ASMB process state dumped to trace file c:\oracle\product\10.2.0\admin\zhfr8db\udump\zhfr8db1_ora_5552.trc
Wed Jul 25 12:47:03  2012
Unexpected communication failure with ASM instance:
 error 21561 (ORA-21561: 生成 OID 失败
)
NOTE: ASMB process state dumped to trace file c:\oracle\product\10.2.0\admin\zhfr8db\udump\zhfr8db1_ora_2008.trc
Wed Jul 25 12:50:01  2012
Unexpected communication failure with ASM instance:
 error 21561 (ORA-21561: 生成 OID 失败
)

再从c:\oracle\product\10.2.0\admin\zhfr8db\udump\zhfr8db1_ora_5552.trc文件跟踪看到：

*** 2012-07-25 12:46:20.268
*** CLIENT ID:() 2012-07-25 12:46:20.268
      ----------------------------------------
      SO: 000000047111DEF0, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
      (process) Oracle pid=31, calls cur/top: 000000047E15DC20/000000047E15DC20, flag: (6) SYSTEM
                int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 33
              last post received-location: ksrpublish
              last process to post me: 7e11e6f8 1 6
              last post sent: 849 0 4
              last post sent-location: kslpsr
              last process posted by me: 7312aef8 1 6
        (latch info) wait_event=0 bits=0
        Process Group: DEFAULT, pseudo proc: 00000004731384B8
        O/S info: user: SYSTEM, term: DATACENTER01, ospid: 4828 
        OSD pid info: Windows thread id: 4828, image: ORACLE.EXE (ASMB)
        Short stack dump: 
ksdxfstk+42<-ksdxcb+1630<-ssthreadsrgruncallback+589<-OracleOradebugThreadStart+975<-0000000077D6B71A
<-0000000077EF047A<-0000000077DA79F3<-0000000008653328<-000000000865190C<-0000000005F564A9
<-0000000005F0CF64<-0000000005EE5D88<-0000000005EE57F9<-0000000005EA5ECB<-ttcdrv+14881
<-0000000005EAAA6D<-xupirtrc+1335<-xupirtr+216<-upirtr+23<-kpurcs+45
<-OCIKDispatch+32<-kfnOpExecute+146<-kfnbRun+1062<-ksbrdp+988<-opirip+700
<-opidrv+856<-sou2o+52<-opimai_real+268<-opimai+96<-BackgroundThreadStart+637<-0000000077D6B71A
        ----------------------------------------
        SO: 000000047114E1A0, type: 4, owner: 000000047111DEF0, flag: INIT/-/-/0x00
        (session) sid: 189 trans: 0000000000000000, creator: 000000047111DEF0, flag: (51) USR/- BSY/-/-/-/-/-
                  DID: 0001-001F-00000003, short-term DID: 0000-0000-00000000
                  txn branch: 0000000000000000
                  oct: 0, prv: 0, sql: 0000000000000000, psql: 0000000000000000, user: 0/SYS
        service name: SYS$BACKGROUND
        waiting for 'ASM background timer' wait_time=0, seconds since wait started=420165
                    =0, =0, =0
                    blocking sess=0x0000000000000000 seq=31
        Dumping Session Wait History
         for 'ASM background timer' count=1 wait_time=4.999949 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999893 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=5.000022 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999948 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999924 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=5.000012 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999948 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999858 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999991 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=5.000000 sec
                    =0, =0, =0
        Sampled Session History of session 189 serial 1
        ---------------------------------------------------
        The sampled session history is constructed by sampling
        the target session every 1 second. The sampling process
        captures at each sample if the session is in a non-idle wait,
        an idle wait, or not in a wait. If the session is in a
        non-idle wait then one interval is shown for all the samples
        the session was in the same non-idle wait. If the
        session is in an idle wait or not in a wait for
        consecutive samples then one interval is shown for all
        the consecutive samples. Though we display these consecutive
        samples  in a single interval the session may NOT be continuously
        idle or not in a wait (the sampling process does not know).
 
        The history is displayed in reverse chronological order.
 
        sample interval: 1 sec, max history 120 sec
        ---------------------------------------------------
          [121 samples,                                    12:44:20 - 12:46:20]
            idle wait at each sample
        temporary object counter: 0
          KTU Session Commit Cache Dump for IDLs: 
          KTU Session Commit Cache Dump for Non-IDLs: 
          ----------------------------------------
          UOL used : 0 locks(used=0, free=0)
          KGX Atomic Operation Log 000000047AECC840
           Mutex 0000000000000000(0, 0) idn 0 oper NONE
           Cursor Pin uid 189 efd 3 whr 11 slp 0
          KGX Atomic Operation Log 000000047AECC888
           Mutex 0000000000000000(0, 0) idn 0 oper NONE
           Library Cache uid 189 efd 0 whr 0 slp 0
          KGX Atomic Operation Log 000000047AECC8D0
           Mutex 0000000000000000(0, 0) idn 0 oper NONE
           Library Cache uid 189 efd 0 whr 0 slp 0
          ----------------------------------------
          SO: 000000045A233D80, type: 41, owner: 000000047114E1A0, flag: INIT/-/-/0x00
          (dummy) nxc=0, nlb=0   
        ----------------------------------------
        SO: 0000000472172A40, type: 11, owner: 000000047111DEF0, flag: INIT/-/-/0x00
        (broadcast handle) flag: (2) ACTIVE SUBSCRIBER, owner: 000000047111DEF0,
                           event: 31, last message event: 31,
                           last message waited event: 31,                            next message: 0000000476225BC8(0), messages read: 0
                           channel: (00000004711640E0) system events broadcast channel
                                    scope: 2, event: 30690, last mesage event: 7413,
                                    publishers/subscribers: 1/45,
                                    messages published: 3
                                    oldest msg (?): 0000000476225BB8 id: 1 pub: 000000047E11F768
                                    heuristic msg queue length: 3
        ----------------------------------------
        SO: 0000000472270FA0, type: 19, owner: 000000047111DEF0, flag: INIT/-/-/0x00
         GES MSG BUFFERS: st=emp chunk=0x0000000000000000 hdr=0x0000000000000000 lnk=0x0000000000000000 flags=0x0 inc=0
          outq=0 sndq=0 opid=0 prmb=0x0 
          mbg[i]=(0 0) mbg[b]=(0 0) mbg[r]=(0 0)
          fmq[i]=(0 0) fmq[b]=(0 0) fmq[r]=(0 0)
          mop[s]=0 mop[q]=0 pendq=0 zmbq=0
          nonksxp_recvs=0
        ------------process 0x0000000472270FA0--------------------
        proc version      : 0
        Local node        : 0
        pid               : 4828
        lkp_node          : 0
        svr_mode          : 0
        proc state        : KJP_FROZEN
        Last drm hb acked : 0
        Total accesses    : 3
        Imm.  accesses    : 0
        Locks on ASTQ     : 0
        Locks Pending AST : 0
        Granted locks     : 0
        AST_Q: 
        PENDING_Q: 
        GRANTED_Q: 
        ----------------------------------------
        SO: 000000047E15DC20, type: 3, owner: 000000047111DEF0, flag: INIT/-/-/0x00
        (call) sess: cur 7114e1a0, rec 0, usr 7114e1a0; depth: 0
          ----------------------------------------
          SO: 000000045ED23770, type: 84, owner: 000000047E15DC20, flag: INIT/-/-/0x00
          (kfgso) flags: 00000000 clt: 3 err: 0 hint: 0
          (kfgpn) rpi: 1 itrn:0000000000000000 gst:0000000000000000 usrp:0000000000000000
          busy: 0 rep: 0 grp: 5d60b840 check: 0/0 glink: 5d60b888 5d60b888
            kfgrp:  number: 0/0 type: 0 compat: 0.0.0.0.0 dbcompat:0.0.0.0.0
            timestamp: 0 state: 0 flags: 2 gpnlist: 5ed237f0 5ed237f0
            KFGPN at 5ed23770 in dependent chain
        ----------------------------------------
        SO: 000000045AEEDD48, type: 16, owner: 000000047111DEF0, flag: INIT/-/-/0x00
        (osp req holder)
PSO child state object changes :
Dump of memory from 0x0000000474167DC0 to 0x0000000474167FC8
474167DC0 00000005 00000000 5AEEDD48 00000004  [........H..Z....]
474167DD0 00000010 000313F4 7E15DC20 00000004  [........ ..~....]
474167DE0 00000003 000313F4 72270FA0 00000004  [..........'r....]
474167DF0 00000013 000312CB 72172A40 00000004  [........@*.r....]
474167E00 0000000B 000313F4 7114E1A0 00000004  [...........q....]
474167E10 00000004 000312CB 00000000 00000000  [................]
474167E20 00000000 00000000 00000000 00000000  [................]
        Repeat 25 times
474167FC0 00000000 00000000                    [........]        
*** 2012-07-25 12:46:37.393
*** CLIENT ID:() 2012-07-25 12:46:37.393
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 320 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 288 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 224 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1 
*** 2012-07-25 12:47:20.314
*** CLIENT ID:() 2012-07-25 12:47:20.314
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1

难道问题是：

WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 320 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 288 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 224 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1

导致的，这些WARNING又说明什么呢？

遇到ORA-600 [kmgs_parameter_update_timeout_1] [1565]的错误

7月 27,2012 留下评论 Experience, Oracle, RAC

今天上午，在一套Windows 2003 64位的双节点10.2.0.5.0的64位RAC数据库上，遇到一则600的错误，ORA-00600: 内部错误代码, 参数: [kmgs_parameter_update_timeout_1], [1565], [], [], [], [], [], []。从(节点1的)alert日志里获取的具体错误信息如下：

Wed Jul 25 10:49:39  2012
Unexpected communication failure with ASM instance:
 error 21561 (ORA-21561: 生成 OID 失败
)
NOTE: ASMB process state dumped to trace file c:\oracle\product\10.2.0\admin\zhfr8db\bdump\zhfr8db1_mmon_4624.trc
System State dumped to trace file c:\oracle\product\10.2.0\admin\zhfr8db\bdump\zhfr8db1_mmon_4624.trc
Wed Jul 25 10:55:02  2012
Errors in file c:\oracle\product\10.2.0\admin\zhfr8db\bdump\zhfr8db1_mmon_4624.trc:
ORA-00600: 内部错误代码, 参数: [kmgs_parameter_update_timeout_1], [1565], [], [], [], [], [], []
ORA-01565: 标识文件 '+DATA/zhfr8db/spfilezhfr8db.ora' 时出错
ORA-17503: ksfdopn: 2 未能打开文件 +DATA/zhfr8db/spfilezhfr8db.ora
ORA-21561: 生成 OID 失败

Wed Jul 25 10:55:03  2012
Trace dumping is performing id=[cdmp_20120725105503]
Wed Jul 25 10:56:06  2012
Restarting dead background process MMON
MMON started with pid=33, OS id=6756

当时的表现情况是，客户端无法通过应用系统访问数据库，客户端通过tnsping service_name的返回结果也是，有时通，返回10毫秒，而有时挂死了。

登录到其中的一个节点服务器(节点1)上，执行lsnrctl status，查看监听状态也挂死，而到另外一个节点(节点2)上，执行lsnrctl status一切正常。并且，分别在两个节点上，通过SQL*PLUS连接数据库均正常，执行crs_stat -t返回的结果也都正常。

接下来，分别在两个节点上作了一个AWR报告，分析了AWR发现并没有发现数据库的异常现象。

从告警日志里看到跟SPFILE相关的错误，于是在SQL*PLUS里尝试create pfile from spfile,以及使用RMAN工具backup spfile时，都报错了，当时匆忙，具体错误号没有记录下来。

没辙了，就到节点1上，尝试通过srvctl stop database -d db_unique_name关闭数据库时，挂死了，丝毫没有反应；通过SQL*PLUS在节点1上，尝试shutdown immediate来手工关闭实例，也未果。

当时，从网络上找到老杨以及惜分飞的文章，不过，貌似都和我遇到的这个情况不太类似。

然后，在没有更好的办法的前提下，直接重启节点1的Windows服务器，毕竟当时应用已经不可用了，再者是RAC架构，最起码还留着一个实例。重启之后，数据库一切正常了。

最后，从MetaLink上找到这样的一篇文章：ORA-600 [kmgs_parameter_update_timeout_1], [1565] While Accessing Spfile Managed By ASM [ID 553915.1]该文档描述，该错误影响到10.2及以上版本的数据库，原因是：

This is due to unpublished bug 5399699 where ORA-600 [kmgs_parameter_update_timeout_1] or similar errors can occur in MMON when ASM is being used.

In 10g MMON manages all memory re-size activity by modifying related parameters. If MMON is not running DBW0 will handle this task. The parameter update activity is triggered by a timeout. Basically this error indicates that the MMON process is not able to write to the SPFILE to store some settings required for dynamic SGA parameter adjustments.

也就是说：这个错误是oracle还未发布的一个bug,bug号是5399699。这个错误是说在10g的数据库里，MMON进程动态的管理内存，MMON（Memory Monitor）是10g数据库的新进程。从10g开始，数据库支持自动调整SGA内存，当需要调整（动态增大或减小）的时候，MMON进程会自动完成，MMON会把这个改变的信息，写入到SPFILE里。

结合最开始的alert日志文件的内容，是由于当时MMON进程无法把这个信息写入到SPFILE里导致的，导致后来MMON后台进程僵死了，在Wed Jul 25 10:56:06 2012的时候，MMON进程又被重启了。然后数据库一直处于“假死”的状态。

Oracle Metalink上这篇文章给的解决方案：

Solution

1. Upgrade to the 10.2.0.4.4 PSU Patch:9352164 or higher where this bug is fixed.

2. Check if Patch:5399699 is available for your RDBMS release and platform.

3. Use one of the following workarounds:

Relocate the spfile either to some other diskgroup on which the archive logs are not being managed.
Move the spfile to the file system

显然，第1个与当前环境下的数据库版本不一致；而第2个方案中，又没有查到Windows 2003 X64平台下的补丁；第3个方案，我当前是RAC的库，如果把SPFILE迁移到文件系统上的话，又不太合适，除非给每个实例单独配置PFILE。

最后，这个问题，对于我这个Oracle菜鸟而言，依然头痛，没有更好的解决方案，如果大家有遇到过类似的案例的话，请不吝赐教！

一步一步在Linux上安装Oracle 11gR2 RAC (8–完结)

6月 21,2012 有16条评论 oracle 11g, RAC

7 脚本附录

7.1 preusers.sh脚本

#!/bin/bash
#Purpose:Create 6 groups named 'oinstall','dba','asmadmin','asmdba','asmoper','oper', plus 2 users named 'oracle','grid'.
#Also setting the Environment
#variable for oracle user.
#variable for grid user.
#Usage:Log on as the superuser('root'),and then execute the command:#./1preusers.sh
#Author:Asher Huang

echo "Now create 6 groups named 'oinstall','dba','asmadmin','asmdba','asmoper','oper'"
echo "Plus 2 users named 'oracle','grid',Also setting the Environment"


groupadd -g 1000 oinstall 
groupadd -g 1200 asmadmin 
groupadd -g 1201 asmdba 
groupadd -g 1202 asmoper 
useradd -u 1100 -g oinstall -G asmadmin,asmdba,asmoper -d /home/grid -s /bin/bash -c "grid Infrastructure Owner" grid 
echo "grid" | passwd --stdin grid

echo 'export PS1="`/bin/hostname -s`-> "'>> /home/grid/.bash_profile 
echo "export TMP=/tmp">> /home/grid/.bash_profile  
echo 'export TMPDIR=$TMP'>>/home/grid/.bash_profile 
echo "export ORACLE_SID=+ASM1">> /home/grid/.bash_profile 
echo "export ORACLE_BASE=/u01/app/grid">> /home/grid/.bash_profile
echo "export ORACLE_HOME=/u01/app/11.2.0/grid">> /home/grid/.bash_profile
echo "export ORACLE_TERM=xterm">> /home/grid/.bash_profile
echo "export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'" >> /home/grid/.bash_profile
echo 'export TNS_ADMIN=$ORACLE_HOME/network/admin'  >> /home/grid/.bash_profile
echo 'export PATH=/usr/sbin:$PATH'>> /home/grid/.bash_profile
echo 'export PATH=$ORACLE_HOME/bin:$PATH'>> /home/grid/.bash_profile
echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib'>> /home/grid/.bash_profile
echo 'export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib'>> /home/grid/.bash_profile
echo "export EDITOR=vi" >> /home/grid/.bash_profile
echo "export LANG=en_US" >> /home/grid/.bash_profile
echo "export NLS_LANG=american_america.AL32UTF8" >> /home/grid/.bash_profile
echo "umask 022">> /home/grid/.bash_profile

groupadd -g 1300 dba 
groupadd -g 1301 oper 
useradd -u 1101 -g oinstall -G dba,oper,asmdba -d /home/oracle -s /bin/bash -c "Oracle Software Owner" oracle 
echo "oracle" | passwd --stdin oracle

echo 'export PS1="`/bin/hostname -s`-> "'>> /home/oracle/.bash_profile 
echo "export TMP=/tmp">> /home/oracle/.bash_profile  
echo 'export TMPDIR=$TMP'>>/home/oracle/.bash_profile 
echo "export ORACLE_HOSTNAME=node1.localdomain">> /home/oracle/.bash_profile 
echo "export ORACLE_SID=devdb1">> /home/oracle/.bash_profile 
echo "export ORACLE_BASE=/u01/app/oracle">> /home/oracle/.bash_profile
echo 'export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1'>> /home/oracle/.bash_profile
echo "export ORACLE_UNQNAME=devdb">> /home/oracle/.bash_profile
echo 'export TNS_ADMIN=$ORACLE_HOME/network/admin'  >> /home/oracle/.bash_profile
echo "export ORACLE_TERM=xterm">> /home/oracle/.bash_profile
echo 'export PATH=/usr/sbin:$PATH'>> /home/oracle/.bash_profile
echo 'export PATH=$ORACLE_HOME/bin:$PATH'>> /home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib'>> /home/oracle/.bash_profile
echo 'export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib'>> /home/oracle/.bash_profile
echo "export EDITOR=vi" >> /home/oracle/.bash_profile
echo "export LANG=en_US" >> /home/oracle/.bash_profile
echo "export NLS_LANG=american_america.AL32UTF8" >> /home/oracle/.bash_profile
echo "export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'" >> /home/oracle/.bash_profile
echo "umask 022">> /home/oracle/.bash_profile

echo "The Groups and users has been created"
echo "The Environment for grid,oracle also has been set successfully"

7.2 predir.sh脚本

#!/bin/bash
#Purpose:Create the necessary directory for oracle,grid users and change the authention to oracle,grid users.
#Usage:Log on as the superuser('root'),and then execute the command:#./2predir.sh
#Author:Asher Huang

echo "Now create the necessary directory for oracle,grid users and change the authention to oracle,grid users..."
mkdir -p /u01/app/grid 
mkdir -p /u01/app/11.2.0/grid 
mkdir -p /u01/app/oracle 
chown -R oracle:oinstall /u01
chown -R grid:oinstall /u01/app/grid 
chown -R grid:oinstall /u01/app/11.2.0
chmod -R 775 /u01
echo "The necessary directory for oracle,grid users and change the authention to oracle,grid users has been finished"

7.3 prelimits.sh脚本

#!/bin/bash
#Purpose:Change the /etc/security/limits.conf.
#Usage:Log on as the superuser('root'),and then execute the command:#./3prelimits.sh
#Author:Asher Huang

echo "Now modify the /etc/security/limits.conf,but backup it named /etc/security/limits.conf.bak before"
cp /etc/security/limits.conf /etc/security/limits.conf.bak
echo "oracle soft nproc 2047" >>/etc/security/limits.conf
echo "oracle hard nproc 16384" >>/etc/security/limits.conf
echo "oracle soft nofile 1024" >>/etc/security/limits.conf
echo "oracle hard nofile 65536" >>/etc/security/limits.conf
echo "grid soft nproc 2047" >>/etc/security/limits.conf
echo "grid hard nproc 16384" >>/etc/security/limits.conf
echo "grid soft nofile 1024" >>/etc/security/limits.conf
echo "grid hard nofile 65536" >>/etc/security/limits.conf
echo "Modifing the /etc/security/limits.conf has been succeed."

7.4 prelogin.sh脚本

#!/bin/bash
#Purpose:Modify the /etc/pam.d/login.
#Usage:Log on as the superuser('root'),and then execute the command:#./4prelimits.sh
#Author:Asher Huang

echo "Now modify the /etc/pam.d/login,but with a backup named /etc/pam.d/login.bak"
cp /etc/pam.d/login /etc/pam.d/login.bak

echo "session required /lib/security/pam_limits.so" >>/etc/pam.d/login
echo "session required pam_limits.so" >>/etc/pam.d/login

echo "Modifing the /etc/pam.d/login has been succeed."

7.5 preprofile.sh脚本

#!/bin/bash
#Purpose:Modify the /etc/profile.
#Usage:Log on as the superuser('root'),and then execute the command:#./5preprofile.sh
#Author:Asher Huang

echo "Now modify the  /etc/profile,but with a backup named  /etc/profile.bak"
cp /etc/profile /etc/profile.bak
echo 'if [ $USER = "oracle" ]||[ $USER = "grid" ]; then' >>  /etc/profile
echo 'if [ $SHELL = "/bin/ksh" ]; then' >> /etc/profile
echo 'ulimit -p 16384' >> /etc/profile
echo 'ulimit -n 65536' >> /etc/profile
echo 'else' >> /etc/profile
echo 'ulimit -u 16384 -n 65536' >> /etc/profile
echo 'fi' >> /etc/profile
echo 'fi' >> /etc/profile
echo "Modifing the /etc/profile has been succeed."

7.6 presysctl.sh脚本

#!/bin/bash
#Purpose:Modify the /etc/sysctl.conf.
#Usage:Log on as the superuser('root'),and then execute the command:#./6presysctl.sh
#Author:Asher Huang

echo "Now modify the /etc/sysctl.conf,but with a backup named /etc/sysctl.bak"
cp /etc/sysctl.conf /etc/sysctl.conf.bak

echo "fs.aio-max-nr = 1048576" >> /etc/sysctl.conf
echo "fs.file-max = 6815744" >> /etc/sysctl.conf
echo "kernel.shmall = 2097152" >> /etc/sysctl.conf
echo "kernel.shmmax = 1054472192" >> /etc/sysctl.conf
echo "kernel.shmmni = 4096" >> /etc/sysctl.conf
echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf
echo "net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.conf
echo "net.core.rmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.rmem_max = 4194304" >> /etc/sysctl.conf
echo "net.core.wmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.wmem_max = 1048586" >> /etc/sysctl.conf
echo "net.ipv4.tcp_wmem = 262144 262144 262144" >> /etc/sysctl.conf
echo "net.ipv4.tcp_rmem = 4194304 4194304 4194304" >> /etc/sysctl.conf

echo "Modifing the /etc/sysctl.conf has been succeed."
echo "Now make the changes take effect....."
sysctl -p