丢失undo，丢失部分归档日志，进行不完全恢复

今天在itpub上看到yssingle的一个恢复测试，挺不错的，转下来了，呵呵。

丢失undo，丢失部分归档日志，是完全可以进行不完全恢复的。我的测试如下：

SQL> select file_name,file_id,tablespace_name,status from dba_data_files;

FILE_NAME                                       FILE_ID TABLESPACE_NAME        STATUS
----------------------------------------------- ------- -------------------- ---------
/u01/app/oracle/oradata/yssingle/system01.dbf     1     SYSTEM                 AVAILABLE
/u01/app/oracle/oradata/yssingle/undotbs01.dbf    2     UNDOTBS1               AVAILABLE
......

8 rows selected

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS         FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         1          1         49  209715200          1 YES      ACTIVE              868530 2011-2-18 上
         2          1         50  209715200          1 NO       CURRENT             869023 2011-2-18 上
         3          1         48  209715200          1 YES      INACTIVE            757330 2011-1-13 上

做个RMAN全备份
[oracle@yssingle1 rmanbak]$ ./rman_backup.sh
[oracle@yssingle1 rmanbak]$ ll /archive/
total 0

SQL> alter system archive log current;

System altered

SQL> alter system archive log current;

System altered

SQL> alter system archive log current;

System altered

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS         FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         1          1         55  209715200          1 NO       CURRENT           869244 2011-2-18 上
         2          1         53  209715200          1 YES      INACTIVE          869235 2011-2-18 上
         3          1         54  209715200          1 YES      INACTIVE          869240 2011-2-18 上

SQL>

虚拟机断电后重启，模拟断电后丢失undo文件、归档日志
[oracle@yssingle1 ~]$ cd /u01/app/oracle/oradata/yssingle/

[oracle@yssingle1 yssingle]$ mv undotbs01.dbf undotbs01.dbf.bak ――模拟断电后丢失undo文件[oracle@yssingle1 yssingle]$ ll
total 1721184
-rw-r-----  1 oracle oinstall   7553024 Feb 18 10:39 control01.ctl
......
-rw-r-----  1 oracle oinstall 104865792 Feb 18 10:39 test01.dbf
-rw-r-----  1 oracle oinstall 209723392 Feb 18 10:39 undotbs01.dbf.bak
......

[oracle@yssingle1 rmanbak]$ ll /archive/
total 44
-rw-r-----  1 oracle oinstall 35840 Feb 18 10:37 1_52_723812272.dbf
-rw-r-----  1 oracle oinstall  2048 Feb 18 10:37 1_53_723812272.dbf
-rw-r-----  1 oracle oinstall  1536 Feb 18 10:37 1_54_723812272.dbf

[oracle@yssingle1 archive]$ mv 1_52_723812272.dbf 1_52_723812272.dbf.bak ――模拟丢失归档日志[oracle@yssingle1 archive]$ ll /archive/
total 44
-rw-r-----  1 oracle oinstall 35840 Feb 18 10:37 1_52_723812272.dbf.bak
-rw-r-----  1 oracle oinstall  2048 Feb 18 10:37 1_53_723812272.dbf
-rw-r-----  1 oracle oinstall  1536 Feb 18 10:37 1_54_723812272.dbf

SQL> startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1260936 bytes
Variable Size             192938616 bytes
Database Buffers           12582912 bytes
Redo Buffers                2932736 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/yssingle/undotbs01.dbf'
SQL> select open_mode from v$database;

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

RMAN> restore archivelog sequence 51; ――恢复归档日志51

Starting restore at 18-FEB-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=51
channel ORA_DISK_1: reading from backup piece /rmanbak/arc_t20110218_s43_p1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanbak/arc_t20110218_s43_p1.bak tag=TAG20110218T103312
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 18-FEB-11

RMAN> exit

Recovery Manager complete.
[oracle@yssingle1 bdump]$ ll /archive
total 60
-rw-r-----  1 oracle oinstall 15360 Feb 18 11:14 1_51_723812272.dbf
-rw-r-----  1 oracle oinstall 35840 Feb 18 10:37 1_52_723812272.dbf.bak
-rw-r-----  1 oracle oinstall  2048 Feb 18 10:37 1_53_723812272.dbf
-rw-r-----  1 oracle oinstall  1536 Feb 18 10:37 1_54_723812272.dbf

[oracle@yssingle1 yssingle]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Fri Feb 18 11:07:37 2011

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

connected to target database: YSSINGLE (DBID=1772505200, not open)

RMAN> restore database;
Starting restore at 18-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=44 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/yssingle/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/yssingle/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/yssingle/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/yssingle/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/yssingle/goldengate01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/yssingle/test01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/yssingle/director01.dbf
restoring datafile 00008 to /u01/app/oracle/oradata/yssingle/veridata01.dbf
channel ORA_DISK_1: reading from backup piece /rmanbak/full_t20110218_s41_p.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanbak/full_t20110218_s41_p.bak tag=TAG20110218T103233
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 18-FEB-11
RMAN> recover database until sequence 52;
Starting recover at 18-FEB-11
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 51 is already on disk as file /archive/1_51_723812272.dbf
archive log filename=/archive/1_51_723812272.dbf thread=1 sequence=51
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-FEB-11
RMAN> sql "ALTER DATABASE OPEN";

sql statement: ALTER DATABASE OPEN
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 02/18/2011 11:16:58
RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE OPENORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> ^[[A
user interrupt received

RMAN> sql "ALTER DATABASE OPEN RESETLOGS";sql statement: ALTER DATABASE OPEN RESETLOGS

RMAN> exit

Recovery Manager complete.

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1260936 bytes
Variable Size             192938616 bytes
Database Buffers           12582912 bytes
Redo Buffers                2932736 bytes
Database mounted.
Database opened.
SQL> exit
重启数据库正常。