Oracle备份恢复之利用dbms_backup_restore恢复数据库

进行测试之前先将数据库做全备：
[quote]RMAN> run {
2> allocate channel ch00 device type disk;
3> backup database include current controlfile format '/backup/full%t' tag='FULLDB';
4> sql 'alter system archive log current';
5> backup archivelog all format '/backup/arch%t' tag='ARCHIVELOG';
6> release channel ch00;
7> }

allocated channel: ch00
channel ch00: sid=17 devtype=DISK

Starting backup at 20-JAN-10
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00001 name=/app/oracle/oradata/ora9i/system01.dbf
input datafile fno=00002 name=/app/oracle/oradata/ora9i/undotbs01.dbf
input datafile fno=00005 name=/app/oracle/oradata/ora9i/example01.dbf
input datafile fno=00011 name=/app/oracle/oradata/ora9i/STREAM01.dbf
input datafile fno=00010 name=/app/oracle/oradata/ora9i/xdb01.dbf
input datafile fno=00006 name=/app/oracle/oradata/ora9i/indx01.dbf
input datafile fno=00009 name=/app/oracle/oradata/ora9i/users01.dbf
input datafile fno=00003 name=/app/oracle/oradata/ora9i/cwmlite01.dbf
input datafile fno=00004 name=/app/oracle/oradata/ora9i/drsys01.dbf
input datafile fno=00007 name=/app/oracle/oradata/ora9i/odm01.dbf
input datafile fno=00008 name=/app/oracle/oradata/ora9i/tools01.dbf
channel ch00: starting piece 1 at 20-JAN-10
channel ch00: finished piece 1 at 20-JAN-10
piece handle=/backup/full708756233 comment=NONE
channel ch00: backup set complete, elapsed time: 00:02:26
Finished backup at 20-JAN-10

Starting Control File and SPFILE Autobackup at 20-JAN-10
piece handle=/app/oracle/product/9.0.2/dbs/c-2494723682-20100120-00 comment=NONE
Finished Control File and SPFILE Autobackup at 20-JAN-10

sql statement: alter system archive log current

Starting backup at 20-JAN-10
current log archived
channel ch00: starting archive log backupset
channel ch00: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=254 stamp=708756150
input archive log thread=1 sequence=2 recid=255 stamp=708756383
input archive log thread=1 sequence=3 recid=256 stamp=708756383
channel ch00: starting piece 1 at 20-JAN-10
channel ch00: finished piece 1 at 20-JAN-10
piece handle=/backup/arch708756383 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:02
Finished backup at 20-JAN-10

Starting Control File and SPFILE Autobackup at 20-JAN-10
piece handle=/app/oracle/product/9.0.2/dbs/c-2494723682-20100120-01 comment=NONE
Finished Control File and SPFILE Autobackup at 20-JAN-10

released channel: ch00[/quote]
假设现在数据库异常宕机
[quote]SQL> shutdown abort
ORACLE instance shut down[/quote]
启动数据库至nomount状态
[quote]SQL> startup nomount
ORACLE instance started.

Total System Global Area 1125193868 bytes
Fixed Size 452748 bytes
Variable Size 335544320 bytes
Database Buffers 788529152 bytes
Redo Buffers 667648 bytes[/quote]
1、利用dbms_backup_restore恢复控制文件
[quote]
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype := dbms_backup_restore.DeviceAllocate(type => '',ident => 'testctl');
6 dbms_backup_restore.RestoresetdataFile;
7 dbms_backup_restore.RestoreControlFileto('/app/oracle/oradata/ora9i/control01.ctl');
8 dbms_backup_restore.RestoreBackupPiece('/backup/full708756233',done => done);
9 dbms_backup_restore.RestoresetdataFile;
10 dbms_backup_restore.RestoreControlFileto('/app/oracle/oradata/ora9i/control02.ctl');
11 dbms_backup_restore.RestoreBackupPiece('/backup/full708756233',done => done);
12 dbms_backup_restore.RestoresetdataFile;
13 dbms_backup_restore.RestoreControlFileto('/app/oracle/oradata/ora9i/control03.ctl');
14 dbms_backup_restore.RestoreBackupPiece('/backup/full708756233',done => done);
15 dbms_backup_restore.DeviceDeallocate;
16 END;
17 /

PL/SQL procedure successfully completed.[/quote]
当然也已可用rman进行控制文件恢复
[quote]RMAN> restore controlfile from '/app/oracle/product/9.0.2/dbs/c-2494723682-20100120-00';

Starting restore at 20-JAN-10

using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/app/oracle/oradata/ora9i/control01.ctl
output filename=/app/oracle/oradata/ora9i/control02.ctl
output filename=/app/oracle/oradata/ora9i/control03.ctl
Finished restore at 20-JAN-10[/quote]

2、利用dbms_backup_restore恢复数据文件
[quote]SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'testdatafile');
6 dbms_backup_restore.RestoreSetDatafile;
7 dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/app/oracle/oradata/ora9i/system01.dbf');
8 dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => '/app/oracle/oradata/ora9i/undotbs01.dbf');
9 dbms_backup_restore.RestoreBackupPiece(done => done,handle => '/backup/full708756233', params => null);
10 dbms_backup_restore.DeviceDeallocate;
11 END;
12 /[/quote]


PL/SQL procedure successfully completed.

3、利用dbms_backup_restore恢复归档日志
[quote]SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'testarchlog');
6 dbms_backup_restore.RestoreSetArchivedLog(destination=>'/app/oracle/product/9.0.2/dbs/arch');
7 dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>1);
8 dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>2);
9 dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>3);
10 dbms_backup_restore.RestoreBackupPiece(done => done,handle => '/backup/arch708756383', params => null);
11 dbms_backup_restore.DeviceDeallocate;
12 END;
13 /

PL/SQL procedure successfully completed.[/quote]

4、不完全恢复打开数据库
[quote]SQL> alter database mount;

Database altered.
SQL> recover database using backup controlfile;
ORA-00279: change 17329956 generated at 01/20/2010 04:43:54 needed for thread 1
ORA-00289: suggestion : /app/oracle/product/9.0.2/dbs/arch/1_2.dbf
ORA-00280: change 17329956 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 17330517 generated at 01/20/2010 04:46:23 needed for thread 1
ORA-00289: suggestion : /app/oracle/product/9.0.2/dbs/arch/1_3.dbf
ORA-00280: change 17330517 for thread 1 is in sequence #3
ORA-00278: log file '/app/oracle/product/9.0.2/dbs/arch/1_2.dbf' no longer
needed for this recovery


ORA-00279: change 17330520 generated at 01/20/2010 04:46:23 needed for thread 1
ORA-00289: suggestion : /app/oracle/product/9.0.2/dbs/arch/1_4.dbf
ORA-00280: change 17330520 for thread 1 is in sequence #4
ORA-00278: log file '/app/oracle/product/9.0.2/dbs/arch/1_3.dbf' no longer
needed for this recovery


ORA-00308: cannot open archived log
'/app/oracle/product/9.0.2/dbs/arch/1_4.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 17330520 generated at 01/20/2010 04:46:23 needed for thread 1
ORA-00289: suggestion : /app/oracle/product/9.0.2/dbs/arch/1_4.dbf
ORA-00280: change 17330520 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/oradata/ora9i/redo06.log
Log applied.
Media recovery complete.[/quote]