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

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

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

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

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

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

找29号晚上的全备结果:

list backup tag=TAG20130729T230017;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Connected to an idle instance.

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

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

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

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

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

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

connected to target database: zldb (not mounted)

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

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

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

RMAN>

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

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

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

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

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

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

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

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

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

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

RMAN>

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

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

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

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

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

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

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

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

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

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

RMAN>

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

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

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

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

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

8 rows selected.

SQL>

开始重命名:

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

SQL>

重命名之后:

SQL> select * from v$log;

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

SQL> select * from v$logfile;

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

8 rows selected.

SQL>

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

RMAN> list backup of archivelog all;

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

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

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

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

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

RMAN>

开始RECOVER:

RMAN> recover database;

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

starting media recovery

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

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

RMAN>

插曲3:RECOVER报错,开始处理报错:

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

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

SQL> show parameter db_recover

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

System altered.

SQL> show parameter db_recover

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

重新执行RECOVER:

RMAN> recover database;

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

starting media recovery

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

RMAN>

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

SQL> alter database open resetlogs;

Database altered.

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

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

SQL>

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

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

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

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

SQL> select * from v$log;

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

SQL>

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

SQL> alter database disable thread 2;

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

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

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

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

SQL> select * from v$log;

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

SQL>

删除多余的UNDO表空间:

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

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

SQL> show parameter undo

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

Tablespace dropped.

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

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

SQL> show parameter undo

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

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

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

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

SQL>

小结:

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

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

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

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

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

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

声明:

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

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

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

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

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

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

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

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

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

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

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

淘宝坏蛋1淘宝坏蛋2

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

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