Category Backup and Recovery

  • 首页   /  Oracle
  • 分类归档： "Backup and Recovery"

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

详细记录一则RAC数据库恢复到异机单实例的案例有8条评论 ,

背景描述：来源于一套客户的生产系统的恢复案例，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 数据库的备份很重要，如果该库没做备份的话，估计恢复数据就是难上加难的事儿了；

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

解决一则ORA-00600[kcratr_nab_less_than_odr]的故障有2条评论 , ,

       临近中午，收到某客户生产库的故障请求：数据库出现了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数据库恢复

留下评论 ,

           案例背景：一套跑在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服务配置为自动启动。

使用Log Miner恢复数据的案例一则

使用Log Miner恢复数据的案例一则有6条评论 , , ,

        上周五（9月21日上午11点左右），收到项目组的一封紧急邮件：

生产数据库中，FIN_CASH_MOVEMENT和FIN_CASH_DETAIL这两张表的数据9月1号到9月19号的数据都被删除了。

烦请提供下技术支持，恢复这两张表的数据。待回复。谢谢！

经过沟通，初步了解到系统的信息是：这是一套运行在IBM P750的小机上的64位的11gR2的单实例数据库。其实，这套环境也是之前的一篇文章里[记录一次在IBM P750小机上给Oracle动态扩展存储]提到的系统。

进一步了解，确认数据库中FIN_CASH_MOVEMENT和FIN_CASH_DETAIL这两张表的数据在9月20号下午3点左右被误删除了，且这两张表是主子表的关系。

我首先想到的方法是，尝试使用事务的闪回查询，看看能否找回数据？结果很不幸，由于是生产数据库，事务繁忙，且误操作离当前时间较长（差不错相差20个小时），UNDO表空间中的回滚数据被覆盖了，遇到了ORA-01555回滚过旧的错误。显然，这条路是走不通了。

接下来，看看系统中是否有之前的有效的逻辑备份？如果有的话，可以用逻辑恢复的方式来尝试找回数据，再次不幸，该系统中采用的RMAN备份，并无逻辑备份。显然，该方法同样不凑效。

那么，我能想到的方法就是对全库做基于时间点的不完全恢复或者使用Oracle 自带的Log Miner工具来挖掘数据了。而该生产库的数据量很大，如果使用基于时间的不完全恢复的话，又有种种弊端和风险。比如，肯定得在一套独立的测试库上执行基于时间的不完全恢复，还有就是rman备份文件很大，这个显然是下下策了。

最后，选择Log Miner工具来尝试找回数据了。下面，记录一下这次的主要过程：

1  首先，找出系统中涵盖误操作时间段的归档日志，这里找出9月20日15点到17点之间的归档：

select name,FIRST_TIME from v$archived_log where first_time between to_date('2012/09/20 14:50:00','yyyy/mm/dd hh24:mi:ss') and to_date('2012/09/20 17:00:00','yyyy/mm/dd hh24:mi:ss');
NAME                                                                   FIRST_TIME
---------------------------------------------------------------------- -------------------
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123956_85ohgybj_.arc    2012/09/20 14:52:41
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123957_85ohn6qh_.arc    2012/09/20 14:55:26
...
...
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123984_85olkh0t_.arc    2012/09/20 15:45:49
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123985_85olo3vb_.arc    2012/09/20 15:47:59
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123986_85olqg4r_.arc    2012/09/20 15:49:55
...
/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123992_85om9wo7_.arc    2012/09/20 15:59:07

2  调用dbms_logmnr系统包，添加归档日志：

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123956_85ohgybj_.arc',options=>dbms_logmnr.new);

3  调用dbms_logmnr系统包，启动Log Miner开始挖掘日志：

SQL>exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

4  从v$logmnr_contents系统表中，查看是否包含FIN_CASH_DETAIL表的SQL语句：

SQL>select timestamp,table_name,sql_redo,sql_undo,operation from v$logmnr_contents where table_name='FIN_CASH_DETAIL';

5  调用dbms_logmnr系统包，停止Log Miner：

exec dbms_logmnr.end_logmnr;

6  重复上述2~5步骤的动作，只是每次需要添加的归档日志不同而已。终于，在/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123985_85olo3vb_.arc这份归档日志中，均找到有FIN_CASH_MOVEMENT和FIN_CASH_DETAIL这两张表的操作。也就是误操作的时间应该是在2012/09/20 15:47:59到2012/09/20 15:49:55之间。

select sql_redo from v$logmnr_contents where table_name='FIN_CASH_MOVEMENT' and operation='DELETE';
SQL_REDO
---------------------------------------------------------------------------------------------------
delete from "SITCLINE"."FIN_CASH_MOVEMENT" where "CASH_MOVEMENT_ID" = '2c2881d63987424d01398b77fa2f6345' and "RP_ID" = 'R' and "OFFICE_ID" = 'SITTP' and "STATE_IND" = '0' 
and "MOVEMENT_TYPE" = 'CHECK' and "MOVEMENT_NO" = 'CR201209030138' and "BANK_MOVEMENT_NO" = '010060936' and "MOVEMENT_DATE" 
= TO_DATE('03-9月 -12', 'DD-MON-RR') and "LEDGER_PARTNER_CODE" = '80273312' and "LEDGER_PARTNER_NAME" IS NULL 
and "EXTERNAL_BANK_NAME" IS NULL and "EXTERNAL_BANK_ACCOUNT" = '056637' and "EXTERAL_BANK_ACCOUN
T_NAME" IS NULL and "INTERNAL_BANK_ID" = '2c2881d63978294201397a864fe30c1e' and "INTERNAL_BANK_NAME" = '花旗台灣' and "INTERNAL_BACNT_ID" = '2c2881d63978294201397a8c72be0c6c' and "INTERNAL_BANK_ACCOUNT_CODE" = '5049328003' 
and "INTERNAL_BANK_ACCOUNT_NAME" = 'SITC STEAMSHIPS CO LTD TAIWAN BRANCH' and "PRIME_CURRENCY_CODE" = 'NTD' and "PRIME_CURRENCY_VALUE" = '10799' and "BASE_CURRENCY_CODE" IS NULL and "BASE_CURRENCY_VALUE" IS NULL and "REMARK" IS NULL and "REALRP" = '1' and "REALRP_DATE" 
IS NULL and
 "REALRP_PERSON" IS NULL and "REALRP_PERSON_NAME" IS NULL and "DISCOUNT_VALUE" IS NULL and "DISCOUNT_REMARK" 
IS NULL and "RATE_BASE" IS NULL and "ALLOCATION_EVENT_ID" IS NULL and "DEPOSIT_DATE" = TO_DATE('03-9月 -12', 'DD-MON-RR') and "INVOICE_INFO" IS NULL and "CREATED_BY_USER" = 'FIN_TWPEI05' and "CREATED_OFFICE" = 'TP_FIN_DP' and "CREATED_DTM_LOC" = TO_DATE('03-9月 -12', 'DD-MON-RR') and "CREATED_TIME_ZONE" 
IS NULL and "UPDATED_BY_USER" = 'FIN_TWPEI05' and "UPDATED_OFFICE" = 'TP_FIN_DP' and "UP
DATED_DTM_LOC" = TO_DATE('03-9月 -12', 'DD-MON-RR') and "UPDATED_TIME_ZONE" IS NULL and "RECORD_VERSION" = '0' and "PRINCIPAL_GROUP_CODE" = 'SIT' 
and "CHECK_NO" = '6822983' and "PRINTED_PERSON" IS NULL and "IS_PRINTED" = '0' and "PRINTED_PERSON_NAME" IS NULL and "PRINTED_DATE" IS NULL and "BANK_EXCHANGE_NO" 
IS NULL and "INVOICE_AMOUNT" IS NULL and "SHORT_OVER_AMOUNT" = '0' and "SAP_STATUS" = '0' and "ARP_ID" IS NULL and ROWID = 'AAATyIAAUAAAMQ7AAH';

...

7  发现对于主、子表FIN_CASH_MOVEMENT、FIN_CASH_DETAIL的误操作分别删除了1390和1911条数据。生成下述的反向SQL，并把SQL脚本交给项目组确认，数据是否正确？

select sql_undo from v$logmnr_contents where table_name='FIN_CASH_MOVEMENT' and operation='DELETE';
SQL_UNDO
---------------------------------------------------------------------------------------------------
insert into "SITCLINE"."FIN_CASH_MOVEMENT"("CASH_MOVEMENT_ID","RP_ID","OFFICE_ID","
STATE_IND","MOVEMENT_TYPE","MOVEMENT_NO","
BANK_MOVEMENT_NO","MOVEMENT_DATE","LEDGER_PARTNER_CODE","
LEDGER_PARTNER_NAME","EXTERNAL_BANK_NAME","
EXTERNAL_BANK_ACCOUNT","EXTERAL_BANK_ACCOUNT_NAME","
INTERNAL_BANK_ID","INTERNAL_BANK_NAME","INTERNAL_BACNT_ID","
INTERNAL_BANK_ACCOUNT_CODE","INTERNAL_BANK_ACCOUNT_NAME","
PRIME_CURRENCY_CODE","PRIME_CURRENCY_VALUE","BASE_CURRENCY_CODE","
BASE_CURRENCY_VALUE","REMARK","REALRP","REALRP_DATE","
REALRP_PERSON","REALRP_PERSON_NAME","DISCOUNT_VALUE","
DISCOUNT_REMARK","RATE_BASE","A
LLOCATION_EVENT_ID","DEPOSIT_DATE","INVOICE_INFO","
CREATED_BY_USER","CREATED_OFFICE","CREATED_DTM_LOC","
CREATED_TIME_ZONE","UPDATED_BY_USER","UPDATED_OFFICE","
UPDATED_DTM_LOC","UPDATED_TIME_ZONE","RECORD_VERSION","
PRINCIPAL_GROUP_CODE","CHECK_NO","PRINTED_PERSON","
IS_PRINTED","PRINTED_PERSON_NAME","PRINTED_DATE","
BANK_EXCHANGE_NO","INVOICE_AMOUNT","SHORT_OVER_AMOUNT","
SAP_STATUS","ARP_ID") values ('2c2881d63987424d01398b77fa2f6345','R','SITTP','0','CHECK','CR201209030138','010060936',TO_DATE('03-9月 -12', 'DD-MON-RR'),'80273312',NULL,NULL,'056637',NULL,'2c2881d63978294201397a864fe30c1e','花旗台灣',
'2c2881d63978294201397a8c72be0c6c','5049328003','SITC STEAMSHIPS CO LTD TAIWAN BRANCH','NTD','10799',NULL,NULL,NULL,'1',
NULL,NULL,NULL,NULL,NULL,NULL,NULL,TO_DATE('03-9月 -12', 'DD-MON-RR'),NULL,'FIN_TWPEI05','TP_FIN_DP',TO_DATE('03-9月 -12', 'DD-MON-RR'),NULL,'FIN_TWPEI05','TP_FIN_DP',TO_DATE('03-9月 -12', 'DD-MON-RR'),NULL,'0','SIT','6822983',
NULL,'0',NULL,NULL,NULL,NULL,'0','0',NULL);

...
select sql_undo from v$logmnr_contents where table_name='FIN_CASH_DETAIL' and operation='DELETE';
SQL_UNDO
---------------------------------------------------------------------------------------------------
insert into "SITCLINE"."FIN_CASH_DETAIL"("CASH_DETAIL_ID","CASH_MOVEMENT_ID","INVOICE_NO","
VESSEL_CODE","VOYAGE_NO","VOYAGE_LEG","BL_NO","
AMOUNT","CURRENCY","RATE","INVOICE_DOC_ID","
FREIGHT_ITEM_ID","CREATED_BY_USER","CREATED_OFFICE","
CREATED_DTM_LOC","CREATED_TIME_ZONE","UPDATED_BY_USER","
UPDATED_OFFICE","UPDATED_DTM_LOC","UPDATED_TIME_ZONE","
RECORD_VERSION","PRINCIPAL_GROUP_CODE") values ('2c2881d63987424d01398b77fa2f6346','2c2881d63987424d01398b77fa2f6345',
'EZ03404580','STKE','1236','N','SITGKESH002049','
10799',NULL,NULL,'2c2881d63987424d01398a8999e22caa',NULL,'FIN_TWPEI05','
TP_FIN_DP',TO_D
ATE('03-9月 -12', 'DD-MON-RR'),NULL,'FIN_TWPEI05','TP_FIN_DP',TO_DATE('03-9月 -12', 'DD-MON-RR'),NULL,'0','SIT');
...

8  最后，项目组确认之后，重新执行反向的SQL脚本，并发邮件过来，确认数据全部找回。

后记：项目组发布出来，确认引起该错误的原因是程序bug，已经修复。从这次的恢复数据过程中，我们说在生产系统上的程序也好，人为操作数据库也好，一定要谨慎。同样，数据库的备份也不容忽视！