麻袋爸爸

不完全恢复案例

1. OS备份下的基于时间的恢复
不完全恢复可以分为基于时间的恢复，基于改变的恢复与基于撤消的恢复，这里已基于时间的恢复为例子来说明不完全恢复过程。
基于时间的恢复可以不完全恢复到现在时间之前的某一个时间，对于某些误操作，如删除了一个数据表，可以在备用恢复环境上恢复到表的删除时间之前，然后把该表导出到正式环境，避免一个人为的错误。

(1) 连接数据库，创建测试表并插入记录

SQL> connect internal/password as sysdba;
Connected.
SQL> create table test(a int);
Table created
SQL> insert into test values(1);
1 row inserted
SQL> commit;
Commit complete

(2) 备份数据库，这里最好备份所有的数据文件，包括临时数据文件

SQL> @hotbak.sql 或在DOS下 svrmgrl @hotbak.sql
或冷备份也可以

(3) 删除测试表，假定删除前的时间为T1，在删除之前，便于测试，继续插入数据并应用到归档。

SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
                         A
---------------------------------------
                         1
                         2
SQL> alter system switch logfile;
Statement processed.
SQL> alter system switch logfile;
Statement processed.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2003-05-21 14:43:01
SQL> drop table test;
Table dropped.

(4) 准备恢复到时间点T1，找回删除的表，先关闭数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

(5) 拷贝刚才备份的所有数据文件回来

C:\>copy D:\DATABAK\*.DBF D:\ORACLE\ORADATA\TEST\

(6) 启动到mount下

SQL> startup mount;
ORACLE instance started.
Total System Global Area  102020364 bytes
Fixed Size                    70924 bytes
Variable Size              85487616 bytes
Database Buffers           16384000 bytes
Redo Buffers                  77824 bytes
Database mounted.

(7) 开始不完全恢复数据库到T1时间

SQL> recover database until time '2003-05-21:14:43:01';
ORA-00279: change 30944 generated at 05/21/2003 14:40:06 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00191.ARC
ORA-00280: change 30944 for thread 1 is in sequence #191

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

(8) 打开数据库，检查数据

SQL> alter database open resetlogs;

Database altered.
SQL> select * from test;
                         A
---------------------------------------
                         1
                         2

说明：
1、不完全恢复最好备份所有的数据，冷备份亦可，因为恢复过程是从备份点往后恢复的，如果因为其中一个数据文件的时间戳(SCN)大于要恢复的时间点，那么恢复都是不可能成功的。
2、不完全恢复有三种方式，过程都一样，仅仅是recover命令有所不一样，这里用基于时间的恢复作为示例。
3、不完全恢复之后，都必须用resetlogs的方式打开数据库，建议马上再做一次全备份，因为resetlogs之后再用以前的备份恢复是很难了。
4、以上是在删除之前获得时间，但是实际应用中，很难知道删除之前的实际时间，但可以采用大致时间即可，或可以采用分析日志文件(logmnr)，取得精确的需要恢复的时间。
5、一般都是在测试机后备用机器上采用这种不完全恢复，恢复之后导出/导入被误删的表回生产系统

2. RMAN备份下的基于改变的恢复
以上用OS备份说明了一个基于时间的恢复，现在用RMAN说明一个基于改变的恢复

(1) 连接数据库，创建测试表并插入记录

SQL> connect internal/password as sysdba;
Connected.
SQL> create table test(a int);
Table created
SQL> insert into test values(1);
1 row inserted
SQL> commit;
Commit complete

(2) 备份数据库

C:\>rman
Recovery Manager: Release 8.1.6.0.0 - Production
RMAN> connect rcvcat rman/rman@back
RMAN-06008: connected to recovery catalog database
RMAN> connect target internal/virpure
RMAN-06005: connected to target database: TEST (DBID=874705288)

RMAN> run{
2> allocate channel c1 type disk;
3> backup full tag 'dbfull' format 'd:\backup\full%u_%s_%p' database
4> include current controlfile;
5> sql 'alter system archive log current';
6> release channel c1;
7> }

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: c1
RMAN-08500: channel c1: sid=12 devtype=DISK
RMAN-03022: compiling command: backup
RMAN-03023: executing command: backup
RMAN-08008: channel c1: starting full datafile backupset
RMAN-08502: set_count=1 set_stamp=494607834 creation_time=21-MAY-03
RMAN-08010: channel c1: specifying datafile(s) in backupset
RMAN-08522: input datafile fno=00001 name=D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF
RMAN-08011: including current controlfile in backupset
RMAN-08522: input datafile fno=00002 name=D:\ORACLE\ORADATA\TEST\RBS01.DBF
RMAN-08522: input datafile fno=00003 name=D:\ORACLE\ORADATA\TEST\USERS01.DBF
RMAN-08522: input datafile fno=00004 name=D:\ORACLE\ORADATA\TEST\TEMP01.DBF
RMAN-08522: input datafile fno=00005 name=D:\ORACLE\ORADATA\TEST\TOOLS01.DBF
RMAN-08522: input datafile fno=00006 name=D:\ORACLE\ORADATA\TEST\INDX01.DBF
RMAN-08013: channel c1: piece 1 created
RMAN-08503: piece handle=D:\BACKUP\FULL01ENM7EQ_1_1 comment=NONE
RMAN-08525: backup set complete, elapsed time: 00:00:16
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter system archive log current
RMAN-03023: executing command: sql

RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: c1

RMAN>

(3) 删除测试表，在删除之前，便于测试，继续插入数据并应用到归档，并获取删除前的scn号。

SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
                         A
---------------------------------------
                         1
                         2
SQL> alter system switch logfile;
Statement processed.
SQL> alter system switch logfile;
Statement processed.

SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) scn from x$ktuxe;
   SCN
----------
  31014
SQL> drop table test;
Table dropped.

(4) 准备恢复到SCN 31014，先关闭数据库，然后启动到mount下

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area                         53126412 bytes
Fixed Size                                          70924 bytes
Variable Size                                    26763264 bytes
Database Buffers                                 26214400 bytes
Redo Buffers                                        77824 bytes
Database mounted.

(5) 开始恢复到改变点SCN 31014

RMAN> run{
2>      allocate channel c1 type disk;
3>      restore database;
4>      recover database until scn 31014;
5>      sql 'ALTER DATABASE OPEN RESETLOGS';
6>      release channel c1;
7> }

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: c1
RMAN-08500: channel c1: sid=10 devtype=DISK
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel c1: starting datafile backupset restore
RMAN-08502: set_count=1 set_stamp=494613682 creation_time=21-MAY-03
RMAN-08089: channel c1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF
RMAN-08523: restoring datafile 00002 to D:\ORACLE\ORADATA\TEST\RBS01.DBF
RMAN-08523: restoring datafile 00003 to D:\ORACLE\ORADATA\TEST\USERS01.DBF
RMAN-08523: restoring datafile 00004 to D:\ORACLE\ORADATA\TEST\TEMP01.DBF
RMAN-08523: restoring datafile 00005 to D:\ORACLE\ORADATA\TEST\TOOLS01.DBF
RMAN-08523: restoring datafile 00006 to D:\ORACLE\ORADATA\TEST\INDX01.DBF
RMAN-08023: channel c1: restored backup piece 1
RMAN-08511: piece handle=D:\BACKUP\FULL01ENMD5I_1_1 tag=DBFULL params=NULL
RMAN-08024: channel c1: restore complete
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03022: compiling command: recover
RMAN-03022: compiling command: recover(1)
RMAN-03022: compiling command: recover(2)
RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-03022: compiling command: recover(4)
RMAN-06050: archivelog thread 1 sequence 191 is already on disk as file D:\ORACL
E\ORADATA\TEST\ARCHIVE\TESTT001S00191.ARC
RMAN-06050: archivelog thread 1 sequence 192 is already on disk as file D:\ORACL
E\ORADATA\TEST\ARCHIVE\TESTT001S00192.ARC
RMAN-03023: executing command: recover(4)
RMAN-08515: archivelog filename=D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00191.AR
C thread=1 sequence=191
RMAN-08515: archivelog filename=D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00192.AR
C thread=1 sequence=192
RMAN-08055: media recovery complete
RMAN-03022: compiling command: sql
RMAN-06162: sql statement: ALTER DATABASE OPEN RESETLOGS
RMAN-03023: executing command: sql
RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: c1

(6) 检查数据

Database altered.
SQL> select * from test;
                         A
---------------------------------------
                         1
                         2
可以看到，表依然存在

说明：
1、RMAN也可以实现不完全恢复，方法比OS备份恢复的方法更简单可靠
2、RMAN可以基于时间，基于改变与基于日志序列的不完全恢复，基于日志序列的恢复可以指定恢复到哪个日志序列，如

run {  
     allocate channel ch1 type disk;  
     allocate channel ch2 type 'sbt_tape'; 
     set until logseq 1234 thread 1; 
     restore controlfile to '$ORACLE_HOME/dbs/cf1.f' ;  
     replicate controlfile from '$ORACLE_HOME/dbs/cf1.f'; 
     alter database mount;  
     restore database;  
     recover database;  
     sql "ALTER DATABASE OPEN RESETLOGS";
}

3、与所有的不完全恢复一样，必须在mount下，restore所有备份数据文件，需要resetlogs
4、基于改变的恢复比基于时间的恢复更可靠，但是可能也更复杂，需要知道需要恢复到哪一个改变号(SCN)，在正常生产中，获取SCN的办法其实也有很多，如查询数据库字典表(V$archived_log or v$log_history)，或分析归档与联机日志(logmnr)等。