Oracle DB备份恢复篇之丢失数据文件
一、实验目的
本篇主要模拟数据库处于OPEN状态时,丢失数据文件,如何根据实际情况恢复数据库,才能尽可能不丢失数据。
二、实验说明
保证数据不丢失无疑是DBA的首要职责,因此,数据库的定时备份对于DBA来说无疑是工作的重中之重,本实验是在数据库有归档下的完备而模拟的,试想如果没有备份,在模拟的过程中数据库肯定会宕掉。也许有人会说,只要我有原始数据,而且归档日志也一直保留,当数据库挂掉时,照样可以恢复,话是这么说,但假如有一个归档日志丢了,那么归档日志就出现了断点,那你还能恢复数据库到宕机前的状态吗?所以我们不冒险,数据就是DBA的生命。在此特别声明,希望与君共勉之。
三、实验模拟种类
1
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:
Release:
Codename:
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
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
Automatic archival
Archive destination
Oldest online log sequence
Next log sequence to archive
Current log sequence
五、实验过程
1 ARCHIVELOG模式下
1)丢失了非关键数据文件(该文件不属于 SYSTEM 或 UNDO 表空间)
只会影响缺失文件中的对象,数据库不会挂掉,用户仍可使用数据库的其余部分继续工作。由于数据库处于ARCHIVELOG模式,因此可恢复到上次提交的时间,数据库不会丢失任何数据,其恢复过程与下面情况相同,在此不再赘述。
2)丢失了系统关键数据文件(该文件属于SYSTEM或UNDO表空间)
丢失属于 SYSTEM 表空间或包含 UNDO 数据的数据文件,就需要从 MOUNT 状态还原数据库(与可在数据库打开时还原其它数据文件不同)。为了更真实的模拟此种情况,我在自己的机器上开了数据库的压力测试工具,让其不断往数据库中读写数据,在此种情况下,删除掉SYSTEM表空间,然后再来恢复。
在压力测试下,由于我设置同时连接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.
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
------- ---- -- ---------- ----------- ------------ ---------------
39
BS Key
------- ---------- ----------- ------------ ---------------
41
BS Key
------- ---- -- ---------- ----------- ------------ ---------------
43
Piece Name: /home/oracle/FlashRecovery/DG1/backupset/2012_06_15/o1_mf_nnnd1_TAG20120615T090016_7xo291fy_.bkp
BS Key
------- ---- -- ---------- ----------- ------------ ---------------
44
BS Key
------- ---- -- ---------- ----------- ------------ ---------------
45
BS Key
------- ---- -- ---------- ----------- ------------ ---------------
46
BS Key
------- ---------- ----------- ------------ ---------------
47
BS Key
------- ---- -- ---------- ----------- ------------ ---------------
48
RMAN>
再次再归档下对数据库做RMAN全备
RMAN>
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
-------------------- ------------------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
SOE
将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
Fixed Size
Variable Size
Database Buffers
Redo Buffers
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
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
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.
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
------- ---- -- ---------- ----------- ------------ ---------------
50
BS Key
------- ---------- ----------- ------------ ---------------
51
BS Key
------- ---- -- ---------- ----------- ------------ ---------------
52
此时数据库处于MOUNT状态,我们用RMAN恢复数据文件/u01/app/oracle/oradata/DG1/system01.dbf
RMAN> restore datafile 1;
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模式下
六、总结