RAMN备份与恢复（不完全恢复）

昨天做了有关于rman的完全恢复，今天主要操作不完全恢复。不完全恢复是指将数据库恢复到备份点与失败点之间的某个时间点。不完全恢复无论是对rman还是用户自定义来说都要求数据库必须在归档模式下，归档模式好处多多，所以只要数据量有变化的都应该跑在归档模式下。同时，不完全恢复只能在mount状态下恢复，因为不完全恢复都是针对整个数据库级别的，而且恢复完毕后必须用resetlogs命令来打开，会复位日志序列号，此时应该重新备份数据库。

Rman的不完全恢复，饼干将会从以下四点来进行试验。基于时间、基于scn、基于归档日志、基于备份控制文件恢复。

基于时间恢复

基于时间恢复一般用于单用户出现误操作，例如误删表、截断表啊什么的，然后将数据库恢复到误操作前的时间点。

1. 查看操作系统时间

SQL> select to_char(sysdate , 'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH2

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

2010-11-20 10:13:48

2. 截断表students

SQL> truncate table students ;

Table truncated

3. 执行不完全恢复数据库

在这里肯定会因为时间格式不对报错

A、设置时间格式，在OS上做

Export NLS_DATE_FORMAT="YYYY.MM.DD HH24:MI:SS"

Echo $ NLS_DATE_FORMAT

查看是否生效

B、在rman中数据库mount状态下设置恢复时间

Run{

set until time='2010.11.20 10:13:48';

}

报错：

RMAN> run

2> {set until time='2010-11-20:10:13:48';}

executing command: SET until clause

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of set command at 11/20/2010 11:25:49

ORA-01861: literal does not match format string

RMAN> eixt

C、修改时间

sql "alter session set nls_date_format=''yyyy.mm.dd hh24:mi:ss'' ";

这里一定要注意单引号和双引号，work上看不出来，在UltraEdit可以看出来，至于为什么我目前也不知道

RMAN> sql "alter session set nls_date_format=''yyyy.mm.dd hh24:mi:ss'' ";

sql statement: alter session set nls_date_format=''yyyy.mm.dd hh24:mi:ss''

D、转储数据库

RMAN> run

2> {set until time='2010.11.20 10:13:48';

3> restore database;

4> recover database;

5> }

executing command: SET until clause

Starting restore at 2010.11.20 12:11:49

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 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 /opt/ora10g/oradata/inomc/system01.dbf

restoring datafile 00002 to /opt/ora10g/oradata/inomc/undotbs01.dbf

restoring datafile 00003 to /opt/ora10g/oradata/inomc/sysaux01.dbf

restoring datafile 00004 to /opt/ora10g/oradata/inomc/users01.dbf

restoring datafile 00005 to /opt/ora10g/oradata/inomc/text01-1.dbf

channel ORA_DISK_1: reading from backup piece /opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_nnndf_TAG20101120T055037_6gfw9gm5_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_nnndf_TAG20101120T055037_6gfw9gm5_.bkp tag=TAG20101120T055037

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

Finished restore at 2010.11.20 12:13:05

Starting recover at 2010.11.20 12:13:05

using channel ORA_DISK_1

starting media recovery

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 11/20/2010 12:13:06

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed

start until time 'NOV 20 2010 10:13:48'

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

我们看到报media recovery failed错误，仔细查看应该还是因为时间格式问题

目前没有办法继续下去了

基于SCN的恢复

1. 查看数据库的当前scn

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

452117

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

452640

不知道这两种查询的方式有什么区别

2. 删除表

SQL> drop table students;

Table dropped

3. 恢复数据库至scn=452117

RMAN> run

2> {

3> set until scn=452117;

4> restore database;

5> recover database;}

executing command: SET until clause

Starting restore at 2010-11-20 20:59:31

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 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 /opt/ora10g/oradata/inomc/system01.dbf

restoring datafile 00002 to /opt/ora10g/oradata/inomc/undotbs01.dbf

restoring datafile 00003 to /opt/ora10g/oradata/inomc/sysaux01.dbf

restoring datafile 00004 to /opt/ora10g/oradata/inomc/users01.dbf

restoring datafile 00005 to /opt/ora10g/oradata/inomc/text01-1.dbf

channel ORA_DISK_1: reading from backup piece /opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_nnndf_TAG20101120T055037_6gfw9gm5_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_nnndf_TAG20101120T055037_6gfw9gm5_.bkp tag=TAG20101120T055037

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

Finished restore at 2010-11-20 21:01:20

Starting recover at 2010-11-20 21:01:21

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 31 is already on disk as file /opt/ora10g/flash_recovery_area/INOMC/archivelog/2010_11_20/o1_mf_1_31_6gfwdqy8_.arc

archive log thread 1 sequence 32 is already on disk as file /opt/ora10g/flash_recovery_area/INOMC/archivelog/2010_11_20/o1_mf_1_32_6gg4x8x4_.arc

archive log thread 1 sequence 33 is already on disk as file /opt/ora10g/flash_recovery_area/INOMC/archivelog/2010_11_20/o1_mf_1_33_6gg61c97_.arc

archive log filename=/opt/ora10g/flash_recovery_area/INOMC/archivelog/2010_11_20/o1_mf_1_31_6gfwdqy8_.arc thread=1 sequence=31

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

Finished recover at 2010-11-20 21:01:39

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

4. 检查删除的表是否已经恢复

SQL> select * from students;

SNO SNAME SEX AGE BPLACE

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

95029 赵新男 20 新疆

95030 赵新1 男 20 新疆

95031 赵新2 男 20 新疆

95032 赵新3 男 20 新疆

95034 赵新4 男 20 新疆

95035 赵新5 男 20 新疆

95036 赵新6 男 20 新疆

95037 赵新7 男 20 新疆

95038 赵新8 男 20 新疆

数据已经恢复。

其实基于时间和基于SCN是一个道理，因为SCN就是数据库里的时间，我们还可以查看scn对应的各个时间点

Look

SQL> select scn , to_char(time_dp ,'yyyy-mm-dd hh24:mi:ss') from sys.smon_scn_time;

SCN TO_CHAR(TIME_DP,'YYYY-MM-DDHH2

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

4 2010-10-14 01:53:14

7770 2010-10-14 01:54:20

80244 2010-10-14 01:59:35

85444 2010-10-14 01:59:53

124651 2010-10-14 02:05:14

134896 2010-10-14 02:07:40

170131 2010-10-14 02:12:55

170331 2010-10-14 02:12:58

211917 2010-10-14 02:18:08

362366 2010-11-19 17:16:17

基于备份控制文件恢复

基于备份控制文件恢复是指使用备份控制文件恢复数据库的过程。当误删除了表空间，或者数据库所有控制文件全部损坏时，可以采用这种恢复方法。

1. 查看当前数据库scn

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

452631

2. 删除表空间text01

SQL> drop tablespace text01 including contents;

Tablespace dropped

3. 使用控制文件恢复数据库

connected to target database: INOMC (DBID=1039069245)

a．在nomount模式下打开数据库

RMAN> startup nomount

connected to target database (not started)

Oracle instance started

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 75499764 bytes

Database Buffers 88080384 bytes

Redo Buffers 2973696 bytes

b．设置数据库DBID

RMAN> set dbid=1039069245;

executing command: SET DBID

c．转储控制文件

首先用list bakcup of controlfile查看

RMAN> list backup of controlfile;

List of Backup Sets

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