Oracle备份与恢复案例(9)【转帖】
4.4.2 RMAN备份下的基于改变的恢复
以上用OS备份说明了一个基于时间的恢复,现在用RMAN说明一个基于改变的恢复
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
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>
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;
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.ARC
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)等。
TAG: