使用dbms_backup_restore进行数据库恢复
今天看到ITPUB上有个网友提问,使用dbms_backup_restore包进行恢复,以前没有测试过,今天测试一下。我的测试环境是10204
想了一下,可能会用到这种恢复的情况,就是数据文件部分丢失,控制文件全部丢失,且备份只有数据文件的全备、全部归档日志和spfile,这时可以通过使用dbms_backup_restore来恢复数据文件,然后通过脚本重建controlfile,通过这种方式可以恢复数据库。
因此如果控制文件丢失,还原起来就比较麻烦,需要用dbms_backup_restore来进行恢复,其实想想,dbms_backup_restore其实也没那么常用了。因为:
1、控制文件如果有自动备份,那么可以从自动备份还原。
2、如果有单独备份控制文件,那么可以从这个单独备份的backupset处还原。
3、如果之前有做数据库全备,全备中就包含了控制文件和spfile的备份,可以从全备中还原。
4、从上面的四种方法都无法还原控制文件,那么只能从备份集restore数据文件以及arch,然后重建控制文件,再resetlogs打开数据库。
环境准备
对数据库进行全备
RMAN> backup database format '/ora10g/backup/database_full_%U.dbf';
Starting backup at 04-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=254 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/ora10g/oradata/ora10g/system02.dbf
input datafile fno=00007 name=/ora10g/oradata/ora10g/users02.dbf
input datafile fno=00001 name=/ora10g/oradata/ora10g/system01.dbf
input datafile fno=00003 name=/ora10g/oradata/ora10g/sysaux01.dbf
input datafile fno=00005 name=/ora10g/oradata/ora10g/example01.dbf
input datafile fno=00002 name=/ora10g/oradata/ora10g/undotbs01.dbf
input datafile fno=00004 name=/ora10g/oradata/ora10g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 04-DEC-12
channel ORA_DISK_1: finished piece 1 at 04-DEC-12
piece handle=/ora10g/backup/database_full_0qns0pih_1_1.dbf tag=TAG20121204T102417 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 04-DEC-12
Starting Control File and SPFILE Autobackup at 04-DEC-12
piece handle=/ora10g/backup/c-4115709917-20121204-00 comment=NONE
Finished Control File and SPFILE Autobackup at 04-DEC-12
l 准备dbms_backup_restore包脚本,以下脚本可以从网上获取,要根据自己的需求来修改。
--restore controlfile
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate(type => '',ident => 't1');
dbms_backup_restore.RestoresetdataFile;
dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control01.ctl');
dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);
dbms_backup_restore.RestoresetdataFile;
dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control02.ctl');
dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);
dbms_backup_restore.RestoresetdataFile;
dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control03.ctl');
dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);
dbms_backup_restore.DeviceDeallocate;
END;
/
红色部分为controlfile 的备份信息。
--restore datafile
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 't1');
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/ora10g/oradata/ora10g/system01.dbf');
dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => '/ora10g/oradata/ora10g/undotbs01.dbf');
dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => '/ora10g/oradata/ora10g/sysaux01.dbf');
dbms_backup_restore.RestoreDatafileTo(dfnumber => 4,toname => '/ora10g/oradata/ora10g/users01.dbf');
dbms_backup_restore.RestoreDatafileTo(dfnumber => 5,toname => '/ora10g/oradata/ora10g/example01.dbf');
dbms_backup_restore.RestoreDatafileTo(dfnumber => 6,toname => '/ora10g/oradata/ora10g/system02.dbf');
dbms_backup_restore.RestoreDatafileTo(dfnumber => 7,toname => '/ora10g/oradata/ora10g/users02.dbf');
dbms_backup_restore.RestoreBackupPiece(done => done,handle => ' /ora10g/backup/database_full_0qns0pih_1_1.dbf', params => null);
dbms_backup_restore.DeviceDeallocate;
END;
/
红色文件部分为数据文件的备份信息
**该脚本在本实验中没有使用,没有修改。
--restore archived redolog
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN');
dbms_backup_restore.RestoreSetArchivedLog(destination=>'D:\ORACLE_BASE\achive\');
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>1);
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>2);
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>3);
dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_LOG_BCK0DH1JGND_1_1', params => null);
dbms_backup_restore.DeviceDeallocate;
END;
/
*******************************************************************************
删除控制文件和部分数据文件
$ ll
total 6716518
-rw-r----- 1 ora10g dba 7061504 Dec 4 10:41 control01.ctl
-rw-r----- 1 ora10g dba 7061504 Dec 4 10:41 control02.ctl
-rw-r----- 1 ora10g dba 7061504 Dec 4 10:41 control03.ctl
-rw-r----- 1 ora10g dba 104865792 Nov 23 16:38 example01.dbf
-rw-r----- 1 ora10g dba 52429824 Dec 3 03:05 redo01.log
-rw-r----- 1 ora10g dba 52429824 Dec 3 22:00 redo02.log
-rw-r----- 1 ora10g dba 52429824 Dec 4 10:41 redo03.log
-rw-r----- 1 ora10g dba 387981312 Dec 4 10:41 sysaux01.dbf
-rw-r----- 1 ora10g dba 524296192 Dec 4 10:41 system01.dbf
-rw-r----- 1 ora10g dba 1073750016 Dec 4 10:24 system02.dbf
-rw-r----- 1 ora10g dba 20979712 Dec 3 22:00 temp01.dbf
-rw-r----- 1 ora10g dba 57679872 Dec 4 10:41 undotbs01.dbf
-rw-r----- 1 ora10g dba 17047552 Dec 4 10:24 users01.dbf
-rw-r----- 1 ora10g dba 1073750016 Dec 4 10:24 users02.dbf
$ mv control01.ctl control01.ctl.bak
$ mv control02.ctl control02.ctl.bak
$ mv control03.ctl control03.ctl.bak
$ mv users01.dbf users01.dbf.bak
$ mv users02.dbf users02.dbf.bak
$ mv system01.dbf system01.dbf.bak
$ ll
total 6716518
-rw-r----- 1 ora10g dba 7061504 Dec 4 10:46 control01.ctl.bak
-rw-r----- 1 ora10g dba 7061504 Dec 4 10:46 control02.ctl.bak
-rw-r----- 1 ora10g dba 7061504 Dec 4 10:46 control03.ctl.bak
-rw-r----- 1 ora10g dba 104865792 Nov 23 16:38 example01.dbf
-rw-r----- 1 ora10g dba 52429824 Dec 3 03:05 redo01.log
-rw-r----- 1 ora10g dba 52429824 Dec 3 22:00 redo02.log
-rw-r----- 1 ora10g dba 52429824 Dec 4 10:45 redo03.log
-rw-r----- 1 ora10g dba 387981312 Dec 4 10:42 sysaux01.dbf
-rw-r----- 1 ora10g dba 524296192 Dec 4 10:43 system01.dbf.bak
-rw-r----- 1 ora10g dba 1073750016 Dec 4 10:24 system02.dbf
-rw-r----- 1 ora10g dba 20979712 Dec 3 22:00 temp01.dbf
-rw-r----- 1 ora10g dba 57679872 Dec 4 10:45 undotbs01.dbf
-rw-r----- 1 ora10g dba 17047552 Dec 4 10:24 users01.dbf.bak
-rw-r----- 1 ora10g dba 1073750016 Dec 4 10:24 users02.dbf.bak
使用dbms_backup_restor包恢复数据库
将数据库启动到nomount状态
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2168928 bytes
Variable Size 496887712 bytes
Database Buffers 1644167168 bytes
Redo Buffers 4259840 bytes
SQL>
执行恢复controlfile 脚本,这个恢复也是需要有controlfile备份的!,如果没有controlfile的备份,这步可以跳过
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype := dbms_backup_restore.DeviceAllocate(type => '',ident => 't1');
6 dbms_backup_restore.RestoresetdataFile;
7 dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control01.ctl');
8 dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);
9 dbms_backup_restore.RestoresetdataFile;
10 dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control02.ctl');
11 dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);
12 dbms_backup_restore.RestoresetdataFile;
13 dbms_backup_restore.RestoreControlFileto('/ora10g/oradata/ora10g/control03.ctl');
14 dbms_backup_restore.RestoreBackupPiece('/ora10g/backup/c-4115709917-20121204-00',done => done);
15 dbms_backup_restore.DeviceDeallocate;
16 END;
17 /
PL/SQL procedure successfully completed.
执行成功后,将数据库启动到mount状态,并执行恢复数据文件脚本
SQL> alter database mount;
Database altered.
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 't1');
6 dbms_backup_restore.RestoreSetDatafile;
7 dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/ora10g/oradata/ora10g/system01.dbf');
8 dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => '/ora10g/oradata/ora10g/undotbs01.dbf');
9 dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => '/ora10g/oradata/ora10g/sysaux01.dbf');
10 dbms_backup_restore.RestoreDatafileTo(dfnumber => 4,toname => '/ora10g/oradata/ora10g/users01.dbf');
11 dbms_backup_restore.RestoreDatafileTo(dfnumber => 5,toname => '/ora10g/oradata/ora10g/example01.dbf');
12 dbms_backup_restore.RestoreDatafileTo(dfnumber => 6,toname => '/ora10g/oradata/ora10g/system02.dbf');
13 dbms_backup_restore.RestoreDatafileTo(dfnumber => 7,toname => '/ora10g/oradata/ora10g/users02.dbf');
14 dbms_backup_restore.RestoreBackupPiece(done => done,handle => '/ora10g/backup/database_full_0qns0pih_1_1.dbf', params => null);
15 dbms_backup_restore.DeviceDeallocate;
16 END;
17 /
PL/SQL procedure successfully completed.
执行成功,启动数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/ora10g/oradata/ora10g/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/ora10g/oradata/ora10g/system01.dbf'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/ora10g/oradata/ora10g/system01.dbf'
这时,我们无法将数据库打开,一直报ORA-01152错误,说明数据库的SCN号和数据文件的SCN号不一致了。这是因为控制文件我们是恢复回来的,要比数据文件的SCN号要小。通过v$database和v$datafile的checkpoint_change#列,可以确定。
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
3154971
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3185311
2 3185311
3 3185311
4 3185311
5 1300617
6 3185311
7 3185311
7 rows selected.
这个时候,就需要我们使用_allow_resetlogs_corruption的隐含参数来处理了。
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2168928 bytes
Variable Size 496887712 bytes
Database Buffers 1644167168 bytes
Redo Buffers 4259840 bytes
Database mounted.
重启数据库,使隐含参数生效。在次恢复数据库
SQL>
SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/ora10g/oradata/ora10g/system01.dbf'
使用resetlogs打开数据库。
SQL> alter database open resetlogs;
Database altered.
检查SCN
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
3185325
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3185325
2 3185325
3 3185325
4 3185325
5 3185325
6 3185325
7 3185325
7 rows selected.
检查数据文件和控制文件
$ cd /ora10g/oradata/ora10g/
$ ll
total 9912422
-rw-r----- 1 ora10g dba 7061504 Dec 4 11:10 control01.ctl
-rw-r----- 1 ora10g dba 7061504 Dec 4 10:47 control01.ctl.bak
-rw-r----- 1 ora10g dba 7061504 Dec 4 11:10 control02.ctl
-rw-r----- 1 ora10g dba 7061504 Dec 4 10:47 control02.ctl.bak
-rw-r----- 1 ora10g dba 7061504 Dec 4 11:10 control03.ctl
-rw-r----- 1 ora10g dba 7061504 Dec 4 10:47 control03.ctl.bak
-rw-r----- 1 ora10g dba 104865792 Dec 4 11:09 example01.dbf
-rw-r----- 1 ora10g dba 52429824 Dec 4 11:10 redo01.log
-rw-r----- 1 ora10g dba 52429824 Dec 4 11:09 redo02.log
-rw-r----- 1 ora10g dba 52429824 Dec 4 11:09 redo03.log
-rw-r----- 1 ora10g dba 387981312 Dec 4 11:09 sysaux01.dbf
-rw-r----- 1 ora10g dba 524296192 Dec 4 11:09 system01.dbf
-rw-r----- 1 ora10g dba 524296192 Dec 4 10:43 system01.dbf.bak
-rw-r----- 1 ora10g dba 1073750016 Dec 4 11:09 system02.dbf
-rw-r----- 1 ora10g dba 20979712 Dec 3 22:00 temp01.dbf
-rw-r----- 1 ora10g dba 57679872 Dec 4 11:09 undotbs01.dbf
-rw-r----- 1 ora10g dba 17047552 Dec 4 11:09 users01.dbf
-rw-r----- 1 ora10g dba 17047552 Dec 4 10:24 users01.dbf.bak
-rw-r----- 1 ora10g dba 1073750016 Dec 4 11:09 users02.dbf
-rw-r----- 1 ora10g dba 1073750016 Dec 4 10:24 users02.dbf.bak
后续处理,关闭隐含参数,重启数据库,对数据库进行全备。
SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2168928 bytes
Variable Size 496887712 bytes
Database Buffers 1644167168 bytes
Redo Buffers 4259840 bytes
Database mounted.
Database opened.
SQL>
检查业务数据
SQL> conn scott/scott
Connected.
SQL>
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
TEST_LOCK
DEPT_RILOCK
EMP_RILOCK
7 rows selected.
SQL>
后续思考:
使用dbms_backup_restor包使用存在局限性
恢复时需要了解备份片信息,说明备份信息很重要啊!!!
恢复数据库时需要指明file#与new_datafile_name,并且需要了解数据文件备份在哪个备份片。
Rman已经完全可以实现,在使用这个感觉有些鸡肋了。
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE
转载于:http://blog.itpub.net/11590946/viewspace-750511/