Oracle数据库备份恢复篇（二）
对于Oracle数据库来说，如果是实例恢复，需要确定检查点和检查点之后所有的日志的有效性，Oracle会自己完成恢复。如果是介质恢复，数据库运行在归档模式下，Oracle需要有一个备份，作为恢复的起点，需要从这个备份时间开始之后所有的归档日志和联机日志文件。当然，如果从严格意义上来讲，对于普通数据文件而言，即使没有备份也是可以恢复的。我们来看下面的例子：
在这个例子当中，我创建了一个表空间userdata，并在表空间上创建表T，并插入数据
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db01/system01.dbf
/u01/app/oracle/oradata/db01/undotbs01.dbf
/u01/app/oracle/oradata/db01/sysaux01.dbf
/u01/app/oracle/oradata/db01/users01.dbf
/u01/app/oracle/oradata/db01/example01.dbf
SQL> create tablespace userdata
2datafile '/u01/app/oracle/oradata/db01/userdata01.dbf'> Tablespace created.
SQL> create table t (x number) tablespace userdata;
Table created.
SQL> select group#,sequence#,status from v$log;
GROUP#SEQUENCE# STATUS
---------- ---------- --------------------------------
1 2 CURRENT
2 0 UNUSED
3 1 INACTIVE
SQL> insert into t values(2);
1 row created.
SQL> commit;
Commit complete.
使用alter sytem switch logfile 切换日志，模拟由于业务操作比较多，联机日志文件写满切换，导致刚才插入的日志2中的操作被覆盖
SQL>>
System> SQL> /
System> SQL> /
System> SQL> select group#,sequence#,status from v$log;
GROUP#SEQUENCE# STATUS
---------- ---------- --------------------------------
1 5 CURRENT
2 3 INACTIVE
3 4 ACTIVE
再往表中插入一条数据，由于提交了，此次操作会写入到联机日志文件5号中
SQL> insert into t values(5);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
X
----------
2
5
退出sqlplus，模拟文件丢失，再次进入sqlplus，当我们要建表t1时报错，到目录下检查文件，文件已经丢失
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition> With the Partitioning, OLAP and Data Mining options
$ rm /u01/app/oracle/oradata/db01/userdata01.dbf
$
$ sqlplus / as sysdba
SQL*Plus:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition> With the Partitioning, OLAP and Data Mining options
SQL> create table t1(x number) tablespace userdata;
create table t1(x number) tablespace userdata
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/db01/userdata01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition> With the Partitioning, OLAP and Data Mining options
$ ls /u01/app/oracle/oradata/db01/
a.sql control02.ctlexample01.dbfredo02.logsysaux01.dbftemp01.dbf users01.dbf
control01.ctlcontrol03.ctlredo01.log redo03.logsystem01.dbfundotbs01.dbf
$
使用RMAN恢复文件，即使没有备份Oracle也可以把文件修复回来，只要从表空间创建开始的归档日志和联机日志文件全部存在就可以，更直白一点，只要表空间创建之后的所有操作都存在就可以。
$ rman target /
Recovery Manager:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
connected to target database: DB01 (DBID=1414786454)
使用list backup命令确认，没有对表空间做过备份
RMAN> list backup of tablespace userdata;
using target database control file instead of recovery catalog
使用RMAN命令修复表空间，注意红色部分，由于没有备份，所以Oracle并没有执行restore动作，而是创建了一个新的数据文件，当然这个文件是空的，
没有内容的，所以后面Oracle会开始recover的动作，应用日志，把文件修复到当前的时间点。
RMAN>run{
2> allocate channel c1 type disk;
3> sql 'alter tablespace userdata offline immediate';
4> restore datafile 6;
5> recover datafile 6;
6> sql 'alter tablespace userdata online';
7> }
allocated channel: c1
channel c1: sid=141 devtype=DISK
sql statement:> Starting restore at 2012-05-17 14:18:06
creating datafile fno=6 name=/u01/app/oracle/oradata/db01/userdata01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 2012-05-17 14:18:07
Starting recover at 2012-05-17 14:18:07
starting media recovery
archive log thread 1 sequence 2 is already on disk as file
/u01/app/oracle/flash_recovery_area/DB01/archivelog/2012_05_17/o1_mf_1_2_7v95dv07_.arc
archive log thread 1 sequence 3 is already on disk as file
/u01/app/oracle/flash_recovery_area/DB01/archivelog/2012_05_17/o1_mf_1_3_7v95dzfd_.arc
archive log thread 1 sequence 4 is already on disk as file
/u01/app/oracle/flash_recovery_area/DB01/archivelog/2012_05_17/o1_mf_1_4_7v95f3bo_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/DB01/archivelog/2012_05_17/o1_mf_1_2_7v95dv07_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:02
Finished recover at 2012-05-17 14:18:10
sql statement:> released channel: c1
RMAN> exit
Recovery Manager complete.
$
修复完成后，在sqlplus下检验文件是否可以使用
$ sqlplus / as sysdba
SQL*Plus:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition> With the Partitioning, OLAP and Data Mining options
SQL> select * from t;
X
----------
2
5
SQL>
SQL> create table t2(x number) tablespace userdata;
Table created.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition> With the Partitioning, OLAP and Data Mining options
检查文件所在目录，文件也已经存在
$ ls /u01/app/oracle/oradata/db01/ -l
total 1063336
-rw-r--r--1 oracle oinstall 551 May 13 16:54 a.sql
-rw-r-----1 oracle oinstall 7061504 May 17 14:21 control01.ctl
-rw-r-----1 oracle oinstall 7061504 May 17 14:21 control02.ctl
-rw-r-----1 oracle oinstall 7061504 May 17 14:21 control03.ctl
-rw-r-----1 oracle oinstall 104865792 May 17 14:07 example01.dbf
-rw-r-----1 oracle oinstall52429312 May 17 14:21 redo01.log
-rw-r-----1 oracle oinstall52429312 May 17 14:07 redo02.log
-rw-r-----1 oracle oinstall52429312 May 17 14:07 redo03.log
-rw-r-----1 oracle oinstall 241180672 May 17 14:20 sysaux01.dbf
-rw-r-----1 oracle oinstall 503324672 May 17 14:18 system01.dbf
-rw-r-----1 oracle oinstall20979712 May 17 12:01 temp01.dbf
-rw-r-----1 oracle oinstall26222592 May 17 14:20 undotbs01.dbf
-rw-r-----1 oracle oinstall10493952 May 17 14:18 userdata01.dbf
-rw-r-----1 oracle oinstall 5251072 May 17 14:07 users01.dbf
以上方法并不适用于所有的表空间，比如system表空间。
也许有人会说，既然对于数据文件来说，不用备份就可以修复，那是不是以后备份的工作我们就不做了呢？显然不是的，我们这样的恢复是有严格的前提的，就是所有的归档日志和联机日志文件都要存在，一点不丢，那就意味着，为了保证数据库不做备份就可以恢复，我们需要非常大的存储来保留日志，还要保证日志不被意外损坏，这个难度是非常大的。当然还有一点是非常重要的，日志越多，恢复的速度是越慢的，这个也是很多公司所不能承受的。
在这里我们只是介绍Oracle的技术，具体的备份恢复策略需要根据实际的应用和业务需求来定。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]