Oracle – 第5页 – 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]

关于AWR报告中几个命中率指标的初步解释

3月 18,2013 有2条评论 Oracle, Performance Tuning

从Oracle 10g开始，Oracle给广大DBA提供了一个性能优化的利器，那便是Automatic Workload Repository性能报告。

在拿到一份AWR性能报告后，通过分析AWR报告来定位数据库性能问题时，在AWR报告的报告头中，我们会看到类似如下的一些命中率指标：

Instance Efficiency Percentages [Target 100%]

Buffer Nowait %:	99.87	Redo NoWait %:	99.95
Buffer Hit %:	95.89	In-memory Sort %:	100.00
Library Hit %:	86.87	Soft Parse %:	99.26
Execute to Parse %:	91.37	Latch Hit %:	99.73
Parse CPU to Parse Elapsd %:	53.78	% Non-Parse CPU:	98.18

那么，这些关于Oracle内存的几个关键指标以及Instance效率的几个指标又该如何理解呢？

1 这几个指标重要，但是通过这些命中率指标并非就可以定位到问题的关键所在。如上，我们看到各项指标基本都很高，除Parse CPU to Parse Elapsd %:只有53.78%之外，但是，该统计数据是来自于一则生产环境下出现严重性能问题的一个小时采样数据。

2 分别对上述表格中各项指标作一初步解释：

① Buffer Nowait %:表示会话向Database Buffer Cache【数据高速缓冲区】申请1个缓存时不等待的比例；

② Buffer Hit %:表示数据高速缓冲区的命中率，也叫Cache Hit Ratio。该指标要分实际业务系统类型来分析，如OLAP系统，该值可能为20%就算合理，而对于OLTP系统来讲，理想值应该在90%以上。当然，并非该值达到100%就没问题了，系统中可能依然难以避免物理读等待。计算脚本：

SELECT (1 - (phys.value / (db.value + cons.value))) * 100 AS "Buffer Cache Hit Ratio"                                            
FROM   v$sysstat phys,                                    
       v$sysstat db,                                      
       v$sysstat cons                                     
WHERE  phys.name  = 'physical reads'                      
AND    db.name    = 'db block gets'                       
AND    cons.name  = 'consistent gets';

③ Library Hit %:Library Cache Hit Ration【库高速缓冲区命中率】，表示向共享池的Library Cache中申请1个Library Cache Object对象时，其已经在Library Cache中存在的比例。该指标的一个合理值应该达到95%以上。计算脚本：

SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100 AS "Library Cache Hit Ratio"                                              
FROM   v$librarycache;

④ Execute to Parse %:表示执行解析比，目标是希望一次解析多次执行，计算公式=[1-(parse count (total)/(execute count)]%=[1-1257816/14576118]%=91.37%,其中parse count (total)来源于V$SYSSTAT中的parse count (total)字段值，execute count则取值于execute count的字段值。同时在同一份AWR报告中，parse count (total)和execute count的值可以从AWR报告的Instance Activity Stats章节中获取，如下摘录：

Instance Activity Stats

Ordered by statistic name

Statistic	Total	per Second	per Trans
Batched IO (bound) vector count	560,211	157.69	28.15
CPU used by this session	1,434,831	403.88	72.10
。。。。。	。。。	。。。	。。。
execute count	14,576,118	4,102.96	732.43
。。。。。	。。。	。。。	。。。
parse count (describe)	9	0.00	0.00
parse count (failures)	28	0.01	0.00
parse count (hard)	9,364	2.64	0.47
parse count (total)	1,257,816	354.06	63.20
parse time cpu	26,723	7.52	1.34
parse time elapsed	49,687	13.99	2.50
redo entries	7,072,485	1,990.80	355.38
redo log space requests	3,665	1.03	0.18
。。。。。	。。。	。。。	。。。
sorts (disk)	7	0.00	0.00
sorts (memory)	22,108,325	6,223.16	1,110.92
。。。。。	。。。	。。。	。。。
。。。。。	。。。	。。。	。。。
write clones created in foreground	2,243	0.63	0.11

⑤ Parse CPU to Parse Elapsd %:该指标表示解析消耗的CPU时间与解析消耗的总时间的比值，目标同样是100%。我们当然希望解析的过程中，时间都消耗在CPU上，而不希望在解析的过程中，出现其他等待事件而拉长解析消耗的总时间。如果该指标偏低的话，说明在解析的过程中，除了消耗CPU资源外，还有其它等待事件，如等待共享池对象、闩锁。计算公式=[parse time cpu/parse time elapsed]%,parse time cpu和parse time elapsed同样来自于V$SYSSTAT，也可以参照AWR报告中Instance Activity Stats章节中的数据，如：Parse CPU to Parse Elapsd %:=[26723/49687]%=53.78%。

⑥ Redo NoWait %:表示会话写Redo Entry时不等待的比例。计算公式=[1-redo log space requests/redo entries]%，同样该两项指标来自于V$SYSSTAT字典表，也可以参照AWR报告中Instance Activity Stats章节中的数据，如Redo NoWait %:=[1-3665/7072485]%=[1-0.0005]%=99.95%。

⑦ In-memory Sort %:表示在内存中排序的比例。计算公式=[1-sorts (disk)/sorts (memory)]%，同样该两项指标来自于V$SYSSTAT字典表，也可以参照AWR报告中Instance Activity Stats章节中的数据，如In-memory Sort %:=[1-7/22108325]%=99.9999%。

⑧ Soft Parse %:表示软解析比例。计算公式=【1-parse count (hard)/parse count (total)】,同样该两项指标来自于V$SYSSTAT字典表，也可以参照AWR报告中Instance Activity Stats章节中的数据，如Soft Parse %:=[1-9364/1257816]%=99.26%。

⑨ Latch Hit %:表示以 willing-to-wait 方式去获取内存栓锁的命中率指标，通常这个指标要求至少在99%以上，否则，很有可能意味着大量栓锁等待，影响性能。该值来源于V$LATCH字典表中的GETS和MISSES字段值计算脚本：

SELECT (1 - (Sum(misses) / Sum(gets))) * 100 AS "Latch Hit Ratio"                               
FROM   v$latch;

⑩ % Non-Parse CPU:表示除解析之外CPU的使用率，计算公式=【1-(parse time cpu)/(CPU used by this session)】%。同样该两项指标来自于V$SYSSTAT字典表，也可以参照AWR报告中Instance Activity Stats章节中的数据，如% Non-Parse CPU:=[1-26723/1434831]%=98.18%。

同时遭遇row cache lock和enq: US – contention的等待事件

3月 5,2013 留下评论 Experience, Oracle, Performance Tuning, RAC

上周五，接到项目组同事电话通知，说某客户应用系统无法登陆。我在应用服务器端用PL/SQL Developer尝试连接数据库服务器时，报错“ORA-00018:maximum number of sessions exceeded”，显然又是连接数不够用了。

就电话回复同事说，赶紧检查一下各应用服务器的连接情况，原因是数据库连接数又不够用了。结果，同事接完电话之后，直接关闭了其中的一台IIS应用服务器，然后再启动这台IIS应用服务器。结果是，应用系统恢复了使用，大约20分钟后，却带了整个数据库的性能急剧下降，数据库Hung住，几乎不可用的状态。

这是一套Windows 2003+10.2.0.5 X64的双节点RAC系统，接下来，就迅速抓取AWR报告，进行问题的定位：

节点1的报告头：

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	6981	01-3月 -13 14:00:17	64	14.9
End Snap:	6982	01-3月 -13 15:00:13	186	17.2
Elapsed:		59.94 (mins)
DB Time:		2,215.01 (mins)

节点2的报告头：

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	6981	01-3月 -13 14:00:14	65	14.2
End Snap:	6982	01-3月 -13 15:14:21	178	25.0
Elapsed:		74.12 (mins)
DB Time:		2,991.16 (mins)

从上可以看到，在每个节点上，这一时段的数据库负载都很高，至少要比正常业务期间负载高出很多。同时，也看到，数据库连接数出现较为不太正常的连接。

节点1的Top 5事件：

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
db file sequential read	210,093	48,731	232	36.7	User I/O
enq: US – contention	73,040	36,420	499	27.4	Other
log file sync	146,401	14,330	98	10.8	Commit
row cache lock	11,636	13,801	1,186	10.4	Concurrency
CPU time		9,314		7.0

节点2的Top 5事件：

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
row cache lock	33,305	49,524	1,487	27.6	Concurrency
enq: US – contention	94,368	46,710	495	26.0	Other
db file sequential read	450,346	38,795	86	21.6	User I/O
CPU time		16,797		9.4
direct path write temp	18,587	13,857	746	7.7	User I/O

看到，在2个节点上均出现了row cache lock和enq: US – contention的等待事件，尤其是第2个节点上更为严重。对于row cache lock等待事件，之前曾遇到过相关案例，原因同样是由于高并发的RAC环境下，sequence没有CACHE，迅速定位并解决了这个问题。

那么，这个enq: US – contention等待事件究竟是什么呢？Google之，找到了类似的案例：异常终止会话导致系统被Hung，以及ITPUB上的一篇帖子：row cache lock+us contention=宕机
原来，导致enq: US – contention等待事件的原因是Undo表空间不够导致的。结合上述案例的提示，原来是因为同事直接停止IIS应用服务器，导致Oracle需要回滚之间的事务，这样，如果之前的事务比较大的话，那么整个回滚的时间也将越长。同时，还有一种可能就是，当初的ACTIVE事务因为停止IIS导致了被强制终止，这样一来，该事务占有的回滚段资源没有释放出来。等到IIS重启之后，新连接上来的会话因为事务操作，需要分配新的UNDO表空间，结果导致了enq: US – contention等待事件。

参照上述的两则案例，找出紧急解决办法，由于是RAC，这里交叉重启了2个节点，最后问题得到解决。

如何正确手工启动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还是蛮重要的。

Oracle索引组织表学习系列三(完)

11月 23,2012 留下评论 Oracle, oracle 11g

5 索引组织表(IOT Index-Organized Table)的键压缩

在创建IOT表时，有一个存储选项NOCOMPRESS，这个选项的意思是不压缩，即对IOT表的索引条目不压缩。该选项不单对IOT表有效，同样对B-Tree索引也适用。

与NOCOMPRESS选项对应的就是COMPRESS N，这个N只能取值整数，表示要压缩的列数，在数据块儿级别提取"公因子"，用以避免存储重复值。如IOT表的主键是(col1,col2,col3)的联合主键的话，那么COMPRESS 2就可以表示，如果在表中重复出现多个col1,col2的重复值的话，那么Oracle就只存储col1,col2 1次，对于重复的记录不再存储。所以，达到压缩的目的。

接下来，看一个示例：

1 创建一个NOCOMPRESS的IOT表：

[oracle@oracle11g ~]$ sqlplus 

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 23 14:21:43 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> conn hr/hr
Connected.
SQL> create table iot
  2  ( owner, object_type, object_name,
  3  constraint iot_pk primary key(owner,object_type,object_name)
  4  )
  5  organization index
  6  NOCOMPRESS
  7  as
  8  select distinct owner, object_type, object_name
  9  from all_objects;

Table created.

SQL> select count(*) from iot;

  COUNT(*)
----------
     55646

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

no rows selected

SQL>

看到表里有55646条记录。

2 用ANALYZE INDEX VALID STRUCTURE来分析这个IOT表的主键：

SQL> analyze index iot_pk validate structure;

Index analyzed.

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

   LF_BLKS    BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
       347          3    2493786              2               33

SQL>

看到这个IOT表的索引使用了347个叶子块，3个分枝块，占用空间大小是2493786字节，约2.4M。

3 使用COMPRESS 1选项来重建IOT表：

SQL> alter table iot move compress 1;

Table altered.

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

no rows selected

SQL> analyze index iot_pk validate structure;

Index analyzed.

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

   LF_BLKS    BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
       304          3    2178229              2               23

SQL>

然后，看到这个IOT表的索引现在使用了304个叶子块，3个分枝块，占用空间大小是2178229字节，约2.1M。

4 使用COMPRESS 2继续压缩这个IOT表：

SQL> alter table iot move compress 2;

Table altered.

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

no rows selected

SQL> analyze index iot_pk validate structure;

Index analyzed.

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

   LF_BLKS    BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
       233          1    1670153              2                0

SQL>

最终，看到这个IOT表的索引现在使用了233个叶子块，1个分枝块，占用空间大小是1670153字节，约1.6M。

说明：

从上可以看出，使用不同的压缩级别，索引的占用的空间大小逐渐变小，从最初的2.4M变成1.6M。叶子块从最初的347变成233个，分枝块最终只有1个。

对于上述分析索引之后的OPT_CMPR_COUNT字段表示最优压缩数，即对于该索引而言如果使用COMPRESS 2来压缩的话，可以得到最优的压缩效果。

同时OPT_CMPR_PCTSAVE字段值则表示最优压缩节省的空间百分比，针对第一次分析之后的结果，可以看到OPT_CMPR_PCTSAVE为33，即可以节省大约33%的空间。如下，压缩后1670153大约是压缩前2493786的2/3，即节约了1/3的空间：

SQL> select 1670153/2493786 from dual;

1670153/2493786
---------------
     .669725871

SQL>

6 索引组织表(IOT Index-Organized Table)的INCLUDING和PCTTHRESHOLD参数选择

对于IOT表而言，如果在建表的语句中同时使用了INCLUDING和PCTTHRESHOLD参数的话，那么PCTTHRESHOLD参数的级别较高。

PCTTHRESHOLD：行中的数据量超过数据块的这个百分比时，行中其它的列则存放到OVERFLOW段中；

INCLUDING：行中从第一列直到INCLUDING子句中指定的列(包括该列)都存放在索引块中，其它的列存放到OVERFLOW段中。

即，对于大小为8K的数据块而言，有下面的IOT表：

SQL> create table iot1
  2  (id number,
  3  name char(2000),
  4  addr char(2000),
  5  salary number,
  6  constraint iot1_pk primary key(id,name)
  7  )
  8  organization index
  9  pctthreshold 50
 10  overflow
 11  including addr;

Table created.

SQL>

那么，如果PCTTHRESHOLD参数指定为50的话，那么索引块中至多会使用4K的空间来存放主键字段和非主键字段，其它字段则存放到OVERFLOW段中，而会忽略此时的INCLUDING选项，并非将addr字段随主键字段一起存放，而是将其存放到溢出段中。

关于IOT表的学习，先到这个地方，以后有新的关于IOT表的知识，再补充在这里！

解决一则ORA-00600[kcratr_nab_less_than_odr]的故障

11月 22,2012 有2条评论 Experience, Oracle, oracle 11g

临近中午，收到某客户生产库的故障请求：数据库出现了ORA-00600的错误！现在数据库启不来，影响业务的运行。

故障场景描述：这是我之前服务过的一家客户，是一套跑在Linux机器上的11gR2单实例数据库，由于服务器异常断电导致的故障！印象较为深刻的是就在2011年2月14日情人节那天，这个库也曾出现过另外一则ORA-00600的错误。较为不幸的是，就在今天又遭遇到这个ORA-600的错误！

下面记录一下这次故障的处理过程：

1 启库时遭遇ORA-600的错误：

[oracle@os5 ~]$ uname -rm
2.6.18-128.el5PAE i686
[oracle@os5 ~]$ ps -ef|grep asm_
oracle    9099  9068  0 10:54 pts/8    00:00:00 grep asm_
[oracle@os5 ~]$ ps -ef|grep ora_
oracle    4797     1  0 10:52 ?        00:00:00 ora_pmon_database
oracle    4799     1  0 10:52 ?        00:00:00 ora_vktm_database
oracle    4803     1  0 10:52 ?        00:00:00 ora_gen0_database
oracle    4805     1  0 10:52 ?        00:00:00 ora_diag_database
oracle    4807     1  0 10:52 ?        00:00:00 ora_dbrm_database
oracle    4809     1  0 10:52 ?        00:00:00 ora_psp0_database
oracle    4811     1  0 10:52 ?        00:00:01 ora_dia0_database
oracle    4813     1  0 10:52 ?        00:00:00 ora_mman_database
oracle    4815     1  0 10:52 ?        00:00:00 ora_dbw0_database
oracle    4817     1  0 10:52 ?        00:00:00 ora_lgwr_database
oracle    4819     1  0 10:52 ?        00:00:00 ora_ckpt_database
oracle    4821     1  0 10:52 ?        00:00:00 ora_smon_database
oracle    4823     1  0 10:52 ?        00:00:00 ora_reco_database
oracle    4825     1  0 10:52 ?        00:00:00 ora_mmon_database
oracle    4827     1  0 10:52 ?        00:00:00 ora_mmnl_database
oracle    4829     1  0 10:52 ?        00:00:00 ora_d000_database
oracle    4831     1  0 10:52 ?        00:00:00 ora_s000_database
oracle    9105  9068  0 11:55 pts/8    00:00:00 grep ora_ 
[oracle@os5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 21 10:54:16 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[3464], [12432], [12534], [], [], [], [], [], [], []


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL>

从上可以看到，当前数据库停留在MOUNT状态，没法OPEN，然后，就用RMAN重新做了个全备，并手工拷贝了联机日志文件，保留现场！

2 接下来，重新尝试以RESETLOGS方式也打不开，尝试RECOVER依然报错！

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [3464], [12432], [12534], [], [], [], [], [], [], []


SQL>

3 那么接下来，看看ALERT日志都记录哪些有用的信息：

Wed Nov 21 10:59:16 2012
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Started redo scan
Completed redo scan
 read 1642 KB redo, 0 data blocks need recovery
Errors in file /oracle/ora11gR2/diag/rdbms/database/database/trace/database_ora_4848.trc  (incident=129790):
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [3464], [12432], [12534], [], [], [], [], [], [], []
Incident details in: /oracle/ora11gR2/diag/rdbms/database/database/incident/incdir_129790/database_ora_4848_i129790.trc
Aborting crash recovery due to error 600
Errors in file /oracle/ora11gR2/diag/rdbms/database/database/trace/database_ora_4848.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [3464], [12432], [12534], [], [], [], [], [], [], []
Errors in file /oracle/ora11gR2/diag/rdbms/database/database/trace/database_ora_4848.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [3464], [12432], [12534], [], [], [], [], [], [], []
ORA-600 signalled during: alter database open...
Wed Nov 21 10:59:17 2012
Sweep [inc][129790]: completed
Sweep [inc2][129790]: completed
Wed Nov 21 10:59:17 2012
Trace dumping is performing id=[cdmp_20121121105917]
Wed Nov 21 11:07:50 2012
db_recovery_file_dest_size of 3852 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.

从alert里，一直看到ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [3464], [12432], [12534], [], [], [], [], [], [], []的错误，还有一些提示就是Errors in file /oracle/ora11gR2/diag/rdbms/database/database/trace/database_ora_4848.trc。

4 就去看看上述的TRACE文件：

*** 2012-11-21 10:59:16.123
Successfully allocated 2 recovery slaves
Using 66 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 3464, block 4945, scn 203336710
  cache-low rba: logseq 3464, block 9148
    on-disk rba: logseq 3464, block 12534, scn 203340512
  start recovery at logseq 3464, block 9148, scn 0

*** 2012-11-21 10:59:16.144
Started writing zeroblks thread 1 seq 3464 blocks 12432-12439

*** 2012-11-21 10:59:16.144
Completed writing zeroblks thread 1 seq 3464
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4096Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 1642Kb in 0.02s => 80.18 Mb/sec
Longest record: 14Kb, moves: 0/1953 (0%)
Change moves: 22/192 (11%), moved: 0Mb
Longest LWN: 445Kb, moves: 0/733 (0%), moved: 0Mb
Last redo scn: 0x0000.0c1eba6c (203340396)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
WARNING! Crash recovery of thread 1 seq 3464 is
ending at redo block 12432 but should not have ended before
redo block 12534
Incident 129790 created, dump file: /oracle/ora11gR2/diag/rdbms/database/database/incident/incdir_129790/database_ora_4848_i129790.trc
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [3464], [12432], [12534], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [3464], [12432], [12534], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [3464], [12432], [12534], [], [], [], [], [], [], []

5 结合ALERT里的错误ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [3464], [12432], [12534], 和TRACE里的错误提示WARNING! Crash recovery of thread 1 seq 3464 is ending at redo block 12432 but should not have ended before redo block 12534 以及查询MetaLink文档ID 1299564.1获取的指导性信息，应该是由于服务器异常断电，导致LGWR写联机日志文件时失败，下次重新启动数据库时，需要做实例级恢复，而又无法从联机日志文件里获取到这些redo信息，因为上次断电时，写日志失败了。

那么ORA-00600的错误里，那几个参数 [1], [3464], [12432], [12534]又表示什么呢？从EYGLE的网站上查询到类似的案例信息，结合本故障场景分析，原来是实例需要恢复日志序列号为3464的联机日志文件，需要恢复到编号为12534的日志块，而实际上只能恢复到第12432个日志块儿，所以库就启不来了。不过，从当前日志文件信息，可以看到，当前日志组的确是3464：

SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

Session altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1       3463   52428800        512          1 YES INACTIVE             203285629 2012/11/21 03:00:32    203335249 2012/11/21 08:59:46
         3          1       3462   52428800        512          1 YES INACTIVE             203238520 2012/11/20 21:00:52    203285629 2012/11/21 03:00:32
         2          1       3464   52428800        512          1 NO  CURRENT              203335249 2012/11/21 08:59:46   2.8147E+14

SQL>

6 参照MetaLink文档ID 1299564.1的方案来恢复数据库：

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 203360397 generated at 11/21/2012 09:21:51 needed for thread 1
ORA-00289: suggestion : /home/oracle/archive_no_delete/ARC_743097220_0000003464_1.arc
ORA-00280: change 203360397 for thread 1 is in sequence #3464


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/ora11gR2/oradata/database/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL>  alter database open resetlogs;

Database altered.

SQL>

至此，这个库是成功恢复，并且启动了。恢复之后，再次对数据库做了备份。看到Alert日志信息：

Wed Nov 21 12:43:44 2012
ALTER DATABASE RECOVER    LOGFILE '/oracle/ora11gR2/oradata/database/redo02.log'  
Media Recovery Log /oracle/ora11gR2/oradata/database/redo02.log
Wed Nov 21 12:43:44 2012
Incomplete recovery applied all redo ever generated.
Recovery completed through change 203360398 time 11/21/2012 09:21:51
Media Recovery Complete (database)
Completed: ALTER DATABASE RECOVER    LOGFILE '/oracle/ora11gR2/oradata/database/redo02.log'  
Wed Nov 21 12:43:55 2012
alter database open 
Errors in file /oracle/ora11gR2/diag/rdbms/database/database/trace/database_ora_10698.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: alter database open ...
Wed Nov 21 12:44:37 2012
 alter database open resetlogs
Archived Log entry 3382 added for thread 1 sequence 3463 ID 0xd9842c95 dest 1:
Archived Log entry 3383 added for thread 1 sequence 3464 ID 0xd9842c95 dest 1:
Archived Log entry 3384 added for thread 1 sequence 3462 ID 0xd9842c95 dest 1:
RESETLOGS after complete recovery through change 203360398
Resetting resetlogs activation ID 3649318037 (0xd9842c95)
Wed Nov 21 12:44:43 2012
Setting recovery target incarnation to 4
Wed Nov 21 12:44:43 2012
Assigning activation ID 3706166088 (0xdce79b48)
LGWR: STARTING ARCH PROCESSES
Wed Nov 21 12:44:43 2012
ARC0 started with pid=24, OS id=11236 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Wed Nov 21 12:44:44 2012
ARC1 started with pid=25, OS id=11238 
Wed Nov 21 12:44:44 2012
ARC2 started with pid=26, OS id=11240 
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /oracle/ora11gR2/oradata/database/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Nov 21 12:44:44 2012
SMON: enabling cache recovery
ARC1: Archival started
ARC2: Archival started
Wed Nov 21 12:44:44 2012
ARC3 started with pid=29, OS id=11242 
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed

从Metalink上看到，目前这个ORA-600的错误应该只有在Oracle 11g上才有的。

记录一则Windows操作系统重装后的Oracle数据库恢复

11月 20,2012 留下评论 Experience, Oracle

案例背景：一套跑在Windows 2K3 SP1 32位系统下的Oracle 10g 10.2.0.2.0的单实例数据库，数据库软件和数据库文件都放在D盘。遭遇到该服务器主板故障之后，更换主板，重新安装Windows 2K3 SP1 32位操作系统。成功配置好Windows操作系统之后，下面简单记录一下这个案例的恢复过程。

1 将之前的数据库安装目录重命名，将D:\oracle 重命名为D:\oracle_old；

2 重新安装Oracle软件，不建库，数据库版本同之前的版本一致，并且将安装目录指向D:\oracle；

3 再次将新安装的D:\oracle重命名为D:\oracle_new，重命名第1步骤中的D:\oracle_old为D:\oracle：

4 重新配置监听，设置ORACLE_SID环境变量，环境变量同之前的ORACLE_SID=cssdb，创建Oracle数据库服务：

C:\Documents and Settings\huangw>set oracle_sid=cssdb

C:\Documents and Settings\huangw>oradim
ORADIM:  [options].  Refer to manual.
Enter one of the following command:
Create an instance by specifying the following options:
     -NEW -SID sid | -SRVC srvc | -ASMSID sid | -ASMSRVC srvc [-SYSPWD pass]
 [-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]
 [-SHUTMODE normal|immediate|abort] [-TIMEOUT secs] [-RUNAS osusr/ospass]
Edit an instance by specifying the following options:
     -EDIT -SID sid | -ASMSID sid [-SYSPWD pass]
 [-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]
 [-SHUTMODE normal|immediate|abort] [-SHUTTYPE srvc|inst] [-RUNAS osusr/ospass]
Delete instances by specifying the following options:
     -DELETE -SID sid | -ASMSID sid | -SRVC srvc | -ASMSRVC srvc
Startup services and instance by specifying the following options:
     -STARTUP -SID sid | -ASMSID sid [-SYSPWD pass]
 [-STARTTYPE srvc|inst|srvc,inst] [-PFILE filename | -SPFILE]
Shutdown service and instance by specifying the following options:
     -SHUTDOWN -SID sid | -ASMSID sid [-SYSPWD pass]
 [-SHUTTYPE srvc|inst|srvc,inst] [-SHUTMODE normal|immediate|abort]
 Query for help by specifying the following parameters: -? | -h | -help

C:\Documents and Settings\huangw>oradim -new -sid cssdb
Instance created.

C:\Documents and Settings\huangw>

5 调用SQL*PLUS，发现权限不足的错误，重建口令文件，执行口令文件验证登录：

C:\Documents and Settings\huangw>oradim -new -sid cssdb
Instance created.

C:\Documents and Settings\huangw>sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Nov 19 18:21:39 2012

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

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> exit

C:\Documents and Settings\huangw>
C:\Documents and Settings\huangw>orapwd file=PWDCSSDB.ora password=dehamdb entri
es=10

C:\Documents and Settings\huangw>sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Nov 19 18:23:49 2012

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

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> conn sys/dehamdb as sysdba
Connected to an idle instance.
SQL>

6 直接启库，完成。

小结：该案例的幸运之处在于Oracle软件和数据库文件都在D盘，Windows系统崩溃之后，只是影响到C盘下的数据和文件，好在对D盘下的文件没有破坏。其实重命名D:\oracle文件，以及重新安装oracle数据库软件的目的无非就是重新将Oracle软件的注册信息，环境变量等信息重新写到Windows系统和注册表中而已。

最后，在系统的环境变量中重新添加ORACLE_SID和在系统服务项中将OracleServiceCSSDB服务配置为自动启动。

Category Oracle