不完全恢复案例
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)等。
no comment untill now