背景描述：来源于一套客户的生产系统的恢复案例，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 数据库的备份很重要，如果该库没做备份的话，估计恢复数据就是难上加难的事儿了；
评论 (8)
gjw1987b| 2013年8月28日
非常详细
支持！
admin| 2013年8月28日
过奖过奖。只是把当时的场景给还原出来，供大家分享。
kommy| 2013年8月28日
插曲1中LOCAL_LISTENER在10G中必须修改的吧，这个是10G RAC下监听的一个BUG
admin| 2013年8月28日
是的。这个要修改。不过从alert日志里看到的信息，Node1.local_listener='(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.33.31)(PORT = 22))’应该是人为修改的。
zeyuan| 2013年9月6日
如果单机环境是否可以如下2种操作方法：
1.使用误操作执行之前的最近一次的有效备份，以及从备份以来到误操作之前的所有归档日志：
alter database mount;
recovery database until cancel
alter database open read only
exp table
shutdown immediate;
使用全部归档+redo
alter database mount;
recovery database until cancel
alter database open resetlogs
2.如果DB启用了闪回功能，且误操作的日志未被覆盖,使用flashback log:
alter database mount
flashback database to timestamp…
alter database open read only
exp table
recovery database
admin| 2013年9月9日
@zeyuan
你说的第2种方法可行。
第1种思路有问题。首先，recovery database until cancel之后，alter database open read only肯定行不通。其次，接下来的recovery database until cancel，再alter database open resetlogs的话，不是要丢失数据么？
zeyuan| 2013年9月10日
@adim我思路确实错了，
1.until cancel,后打开数据库必须用resetlogs,
recovery database until cancel 后Resetlogs打开DB，前提DB正常关闭，归档都在，也属于完全恢复吧？
2.我想在原库上操作，把原库恢复到误操作前状态,导出表后，在把库恢复到最新状态，用什么方法呢？resetlogs做恢复只能用一次。
admin| 2013年9月10日
@zeyuan
1 只要是recover 之后，使用resetlogs方式启库的话，都属于不完全恢复；
2 你说的这种情况，前提应该是数据库有启用FLASHBACK功能。可以整库闪回到误操作之前的某个时间点，然后read only方式启库，exp/expdp导出，然后重新将库启动到当前状态，最后imp/impdp导入数据即可。