Oracle DB备份恢复篇之丢失数据文件

Oracle DB备份恢复篇之丢失数据文件

一、实验目的

本篇主要模拟数据库处于OPEN状态时，丢失数据文件，如何根据实际情况恢复数据库，才能尽可能不丢失数据。

二、实验说明

保证数据不丢失无疑是DBA的首要职责，因此，数据库的定时备份对于DBA来说无疑是工作的重中之重，本实验是在数据库有归档下的完备而模拟的，试想如果没有备份，在模拟的过程中数据库肯定会宕掉。也许有人会说，只要我有原始数据，而且归档日志也一直保留，当数据库挂掉时，照样可以恢复，话是这么说，但假如有一个归档日志丢了，那么归档日志就出现了断点，那你还能恢复数据库到宕机前的状态吗？所以我们不冒险，数据就是DBA的生命。在此特别声明，希望与君共勉之。

三、实验模拟种类

1   ARCHIVELOG模式下

2）丢失了非关键数据文件（该文件不属于 SYSTEM 或 UNDO 表空间）

2）丢失了系统关键数据文件（该文件属于SYSTEM或UNDO表空间）

2在NOARCHIVELOG模式下

如果在 NOARCHIVELOG 模式下丢失了数据库中的任何数据文件，只可恢复到上一次备份时的状态，因此数据库会丢失数据。

四、实验环境

1）Linux系统环境

[oracle@DG1 ~]$ lsb_release -a

LSB Version: :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch

Distributor ID: RedHatEnterpriseServer

Description:    Red Hat Enterprise Linux Server release 5.4 (Tikanga)

Release:        5.4

Codename:       Tikanga

2）Oracle数据库版本信息

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

3）查看数据库是否归档

[oracle@DG1 ~]$sqlplus / as sysdba

SQL> archive logfile list;

SP2-0734: unknown command beginning "archive lo..." - rest of line ignored.

SQL> archive list;

SP2-0734: unknown command beginning "archive li..." - rest of line ignored.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

五、实验过程

1 ARCHIVELOG模式下

1）丢失了非关键数据文件（该文件不属于 SYSTEM 或 UNDO 表空间）

只会影响缺失文件中的对象，数据库不会挂掉，用户仍可使用数据库的其余部分继续工作。由于数据库处于ARCHIVELOG模式，因此可恢复到上次提交的时间，数据库不会丢失任何数据，其恢复过程与下面情况相同，在此不再赘述。

2）丢失了系统关键数据文件（该文件属于SYSTEM或UNDO表空间）

丢失属于 SYSTEM 表空间或包含 UNDO 数据的数据文件，就需要从 MOUNT 状态还原数据库（与可在数据库打开时还原其它数据文件不同）。为了更真实的模拟此种情况，我在自己的机器上开了数据库的压力测试工具，让其不断往数据库中读写数据，在此种情况下，删除掉SYSTEM表空间，然后再来恢复。

 

      图1 压力测试

        图2 EM

                             图3 实时监控全备份、归档日志

 

在压力测试下，由于我设置同时连接20个用户，因此我的CPU和I/O都是高负荷运行。在正式演示这个过程开始之前，我再次对自己的数据库做一个归档下的全备。

 

[oracle@DG1 ~]$ rman target sys/oracle@DG1

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jun 16 09:05:47 2012

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

connected to target database: DG1 (DBID=1762320829)

 

当前已备份的数据库文件、控制文件、参数文件、归档日志文件

 

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

39      Incr 0  3.40G      DISK        00:07:52     14-JUN-12

        BP Key: 39   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_DG1_000023_061412090004

        Piece Name: /home/oracle/DiskBackupLocation/1endhsr1_1_1

  List of Datafiles in backup set 39

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  1    0  Incr 782243     14-JUN-12 /u01/app/oracle/oradata/DG1/system01.dbf

  2    0  Incr 782243     14-JUN-12 /u01/app/oracle/oradata/DG1/undotbs01.dbf

  3    0  Incr 782243     14-JUN-12 /u01/app/oracle/oradata/DG1/sysaux01.dbf

  4    0  Incr 782243     14-JUN-12 /u01/app/oracle/oradata/DG1/users01.dbf

  5    0  Incr 782243     14-JUN-12 /u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

41      16.00M     DISK        00:00:02     14-JUN-12

        BP Key: 41   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_DG1_000023_061412090004

        Piece Name: /home/oracle/DiskBackupLocation/1gndhtag_1_1

  List of Archived Logs in backup set 41

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    79      770550     14-JUN-12 783149     14-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

43      Incr 1  244.74M    DISK        00:05:11     15-JUN-12

        BP Key: 43   Status: AVAILABLE  Compressed: NO  Tag: TAG20120615T090016

Piece Name: /home/oracle/FlashRecovery/DG1/backupset/2012_06_15/o1_mf_nnnd1_TAG20120615T090016_7xo291fy_.bkp

  List of Datafiles in backup set 43

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  1    1  Incr 789474     15-JUN-12 /u01/app/oracle/oradata/DG1/system01.dbf

  2    1  Incr 789474     15-JUN-12 /u01/app/oracle/oradata/DG1/undotbs01.dbf

  3    1  Incr 789474     15-JUN-12 /u01/app/oracle/oradata/DG1/sysaux01.dbf

  4    1  Incr 789474     15-JUN-12 /u01/app/oracle/oradata/DG1/users01.dbf

  5    1  Incr 789474     15-JUN-12 /u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

44      Full    7.08M      DISK        00:00:02     15-JUN-12

        BP Key: 44   Status: AVAILABLE  Compressed: NO  Tag: TAG20120615T090533

        Piece Name: /home/oracle/DiskBackupLocation/c-1762320829-20120615-00

  Control File Included: Ckp SCN: 790171       Ckp time: 15-JUN-12

  SPFILE Included: Modification time: 15-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

45      Incr 0  3.40G      DISK        00:12:26     15-JUN-12

        BP Key: 45   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_DG1_000023_061512091429

        Piece Name: /home/oracle/DiskBackupLocation/1kndki26_1_1

  List of Datafiles in backup set 45

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  1    0  Incr 812330     15-JUN-12 /u01/app/oracle/oradata/DG1/system01.dbf

  2    0  Incr 812330     15-JUN-12 /u01/app/oracle/oradata/DG1/undotbs01.dbf

  3    0  Incr 812330     15-JUN-12 /u01/app/oracle/oradata/DG1/sysaux01.dbf

  4    0  Incr 812330     15-JUN-12 /u01/app/oracle/oradata/DG1/users01.dbf

  5    0  Incr 812330     15-JUN-12 /u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

46      Full    7.08M      DISK        00:00:06     15-JUN-12

        BP Key: 46   Status: AVAILABLE  Compressed: NO  Tag: TAG20120615T212714

        Piece Name: /home/oracle/DiskBackupLocation/c-1762320829-20120615-01

  Control File Included: Ckp SCN: 813369       Ckp time: 15-JUN-12

  SPFILE Included: Modification time: 15-JUN-12

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

47      50.24M     DISK        00:00:07     15-JUN-12

        BP Key: 47   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_DG1_000023_061512091429

        Piece Name: /home/oracle/DiskBackupLocation/1mndkiq1_1_1

  List of Archived Logs in backup set 47

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    80      783149     14-JUN-12 812283     15-JUN-12

  1    81      812283     15-JUN-12 813398     15-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

48      Full    7.08M      DISK        00:00:01     15-JUN-12

        BP Key: 48   Status: AVAILABLE  Compressed: NO  Tag: TAG20120615T212738

        Piece Name: /home/oracle/DiskBackupLocation/c-1762320829-20120615-02

  Control File Included: Ckp SCN: 813436       Ckp time: 15-JUN-12

  SPFILE Included: Modification time: 15-JUN-12

RMAN>

 

再次再归档下对数据库做RMAN全备

 

RMAN>  backup database plus archivelog delete all input;

Starting backup at 16-JUN-12

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=103 devtype=DISK

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=82 recid=155 stamp=786099319

input archive log thread=1 sequence=83 recid=156 stamp=786099873

input archive log thread=1 sequence=84 recid=157 stamp=786099880

input archive log thread=1 sequence=85 recid=158 stamp=786099898

input archive log thread=1 sequence=86 recid=159 stamp=786100953

input archive log thread=1 sequence=87 recid=160 stamp=786101238

input archive log thread=1 sequence=88 recid=161 stamp=786101323

input archive log thread=1 sequence=89 recid=162 stamp=786101482

input archive log thread=1 sequence=90 recid=163 stamp=786102563

input archive log thread=1 sequence=91 recid=164 stamp=786102594

channel ORA_DISK_1: starting piece 1 at 16-JUN-12

channel ORA_DISK_1: finished piece 1 at 16-JUN-12

piece handle=/home/oracle/DiskBackupLocation/1ondlua8_1_1 tag=TAG20120616T094958 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:28

channel ORA_DISK_1: deleting archive log(s)

archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_16/o1_mf_1_82_7xqpcon4_.arc recid=155 stamp=786099319

archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_16/o1_mf_1_83_7xqpwz5b_.arc recid=156 stamp=786099873

archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_16/o1_mf_1_84_7xqpx85g_.arc recid=157 stamp=786099880

archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_16/o1_mf_1_85_7xqpxtw0_.arc recid=158 stamp=786099898

archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_16/o1_mf_1_86_7xqqyr3g_.arc recid=159 stamp=786100953

archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_16/o1_mf_1_87_7xqr7o9r_.arc recid=160 stamp=786101238

archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_16/o1_mf_1_88_7xqrbbbz_.arc recid=161 stamp=786101323

archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_16/o1_mf_1_89_7xqrh8ko_.arc recid=162 stamp=786101482

archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_16/o1_mf_1_90_7xqsjr1k_.arc recid=163 stamp=786102563

archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_16/o1_mf_1_91_7xqsl1fs_.arc recid=164 stamp=786102594

Finished backup at 16-JUN-12

Starting backup at 16-JUN-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00005 name=/u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf

input datafile fno=00001 name=/u01/app/oracle/oradata/DG1/system01.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/DG1/sysaux01.dbf

input datafile fno=00002 name=/u01/app/oracle/oradata/DG1/undotbs01.dbf

input datafile fno=00004 name=/u01/app/oracle/oradata/DG1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 16-JUN-12

channel ORA_DISK_1: finished piece 1 at 16-JUN-12

piece handle=/home/oracle/DiskBackupLocation/1pndlub8_1_1 tag=TAG20120616T095032 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:29:18

Finished backup at 16-JUN-12

Starting backup at 16-JUN-12

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=92 recid=165 stamp=786104406

channel ORA_DISK_1: starting piece 1 at 16-JUN-12

channel ORA_DISK_1: finished piece 1 at 16-JUN-12

piece handle=/home/oracle/DiskBackupLocation/1qndm02r_1_1 tag=TAG20120616T102008 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

channel ORA_DISK_1: deleting archive log(s)

archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_16/o1_mf_1_92_7xqvbb25_.arc recid=165 stamp=786104406

Finished backup at 16-JUN-12

Starting Control File and SPFILE Autobackup at 16-JUN-12

piece handle=/home/oracle/DiskBackupLocation/c-1762320829-20120616-00 comment=NONE

Finished Control File and SPFILE Autobackup at 16-JUN-12

RMAN>                                         全备成功

 

删除掉早期的全备份，为以后的全备腾出磁盘空间。

 

RMAN>list backup;

RMAN>crosscheck backup;

RMAN>delete obsolete;

 

现在我们删除system表空间数据文件，再进行恢复。

 

SQL>select t.name tname,d.name dname from v$datafile d,v$tablespace t where t.ts#=d.ts#;

TNAME                DNAME

-------------------- ------------------------------------------

SYSTEM               /u01/app/oracle/oradata/DG1/system01.dbf

UNDOTBS1             /u01/app/oracle/oradata/DG1/undotbs01.dbf

SYSAUX               /u01/app/oracle/oradata/DG1/sysaux01.dbf

USERS                /u01/app/oracle/oradata/DG1/users01.dbf

SOE                  /u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf

 

将system表空间数据文件删除

 

SQL> !rm /u01/app/oracle/oradata/DG1/system01.dbf

SQL> !ls /u01/app/oracle/oradata/DG1/system01.dbf

ls: /u01/app/oracle/oradata/DG1/system01.dbf: No such file or directory             成功删除

 

在删除system表空间数据文件之后，数据库可能会直接挂掉，也可能还处于运行状态，但由于我的实验数据库在压力测试工具之下运行，因此按道理说会直接挂掉的，结果这次还没挂掉，我表示很好奇，一般我们希望数据库能永远正常运行，不要出现任何非正常情况，可这次好嘛，想让它宕掉，却依然还坚挺，无语啊。。。所以现在手动将其关闭再启动。

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              75499088 bytes

Database Buffers          205520896 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/DG1/system01.dbf'

 

alter_log信息

 

Sat Jun 16 10:37:20 2012

ALTER DATABASE   MOUNT

Sat Jun 16 10:37:24 2012

Setting recovery target incarnation to 3

Sat Jun 16 10:37:24 2012

Successful mount of redo thread 1, with mount id 1767484832

Sat Jun 16 10:37:24 2012

Database mounted in Exclusive Mode

Completed: ALTER DATABASE   MOUNT

Sat Jun 16 10:37:24 2012

ALTER DATABASE OPEN

Sat Jun 16 10:37:25 2012

Errors in file /u01/app/oracle/admin/DG1/bdump/dg1_dbw0_303.trc:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/DG1/system01.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-1157 signalled during: ALTER DATABASE OPEN...

 

在有mount状态启动到open状态是会出现如上错误，此时查看alert日志会看到如下信息，我们可以看到错误信息提示我们system01.dbf文件找不到。

 

由于数据库有归档下的全备，所以我们可以使用RMAN来恢复。

 

[oracle@DG1 ~]$ rman target sys/oracle@DG1

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jun 16 10:43:22 2012

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

connected to target database: DG1 (DBID=1762320829, not open)

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

50      Full    3.41G      DISK        00:29:06     16-JUN-12

        BP Key: 50   Status: AVAILABLE  Compressed: NO  Tag: TAG20120616T095032

        Piece Name: /home/oracle/DiskBackupLocation/1pndlub8_1_1

  List of Datafiles in backup set 50

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  1       Full 905281     16-JUN-12 /u01/app/oracle/oradata/DG1/system01.dbf

  2       Full 905281     16-JUN-12 /u01/app/oracle/oradata/DG1/undotbs01.dbf

  3       Full 905281     16-JUN-12 /u01/app/oracle/oradata/DG1/sysaux01.dbf

  4       Full 905281     16-JUN-12 /u01/app/oracle/oradata/DG1/users01.dbf

  5       Full 905281     16-JUN-12 /u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

51      46.71M     DISK        00:00:06     16-JUN-12

        BP Key: 51   Status: AVAILABLE  Compressed: NO  Tag: TAG20120616T102008

        Piece Name: /home/oracle/DiskBackupLocation/1qndm02r_1_1

  List of Archived Logs in backup set 51

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    92      904604     16-JUN-12 934213     16-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

52      Full    7.08M      DISK        00:00:07     16-JUN-12

        BP Key: 52   Status: AVAILABLE  Compressed: NO  Tag: TAG20120616T102020

        Piece Name: /home/oracle/DiskBackupLocation/c-1762320829-20120616-00

  Control File Included: Ckp SCN: 934634       Ckp time: 16-JUN-12

  SPFILE Included: Modification time: 16-JUN-12     可以看到我的全备份信息如上

 

此时数据库处于MOUNT状态，我们用RMAN恢复数据文件/u01/app/oracle/oradata/DG1/system01.dbf

 

RMAN> restore datafile 1;          ------这儿我用的是system01.dbf文件号

Starting restore at 16-JUN-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 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/DG1/system01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/DiskBackupLocation/1pndlub8_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/home/oracle/DiskBackupLocation/1pndlub8_1_1 tag=TAG20120616T095032

channel ORA_DISK_1: restore complete, elapsed time: 00:01:06

Finished restore at 16-JUN-12        数据文件成功恢复到上次归档下的全备状态

 

执行RMAN恢复数据库的操作，将数据库恢复到挂掉之前的状态

 

RMAN> recover database;

Starting recover at 16-JUN-12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:07

Finished recover at 16-JUN-12                   恢复成功

 

将数据库启动

 

RMAN> alter database open;

database opened                      数据库成功恢复到挂掉之前的状态

 

数据库恢复成功之后，赶紧对数据库做一次归档下的RMAN全备，以防再次挂掉恢复的麻烦。

 

RMAN> backup database plus archivelog delete all input;

 

2在NOARCHIVELOG模式下

    由上面的实验过程，我们容易得出，在非归档模式下，数据库丢失数据文件只能恢复到上次全备时的状态，丢失数据是难免的，具体实验过程与上相同，在此不再赘述。

六、总结

    正如本篇开始时我们就强调说，数据库备份对于DBA来说无疑是工作的重中之重，试想如果没有备份，数据库挂掉了，你怎么恢复？还有归档日志也很重要，比如即便你有归档下的全备，但是全备之后的归档日志丢失或损坏，甚至被你误删，即便只是其中的一个，那么，恢复数据库也只能到归档日志的断点处或是全备时的状态。所以，在此建议最好使用RMAN来删除已经备份的归档日志和旧的全备份集，这样无论是从数据库数据字典级别还是操作系统物理文件级别都可以保持二者一致性，一般不会出任何问题。最后在强掉一点，每次数据库恢复成功之后的第一件事就是，在归档下做一次RMAN的全备，以防在没有备份下数据库再次挂掉恢复的麻烦。