使用dbms_backup_restore包恢复数据库学习
来源：互联网 发布：铝塑板开槽尺寸算法 编辑：程序博客网 时间：2024/06/07 02:29
作者 阿九【转载时请务必以超链接形式标明文章原始出处和作者信息】
使用dbms_backup_restore包恢复数据库学习
在数据库的极端恢复下，比如，没有spfile、控制文件、数据文件，且catalog库也不可用，也不知道DBID的情况下，只剩下备份集文件的时候，我们可以通过使用dbms_backup_restore包来恢复数据库，这个包在nomount下也能执行。
1、目的：
学习使用dbms_backup_restore包
2、试验设计：
2.1、先创建一张测试表，test_restore
QL> DESC test_restore;
Name Null? Type
----------------------------------------- -------- ----------------------------
T_NUM NUMBER
T_DATE VARCHAR2(17)
SQL>
2.2、插入数据
SQL> insert into test_restore values(1,to_char(sysdate,'yyyymmdd hh24:mi:ss'));
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_restore;
T_NUM T_DATE
---------- -----------------
1 20120414 22:49:12
SQL>
2.3、备份数据库并清空 spfile文件、控制文件，数据文件后（数据库文件使用raw设备），使用dbms_backup_restore包来恢复数据库，检查表是否存在，数据是否存在。
3、试验过程
3.1、备份数据库
[oracle@clone_sigle oracle]$ rman target /
[uniread] Loaded history (91 lines)
Recovery Manager: Release 10.2.0.4.0 - Production on 星期六 4月 14 22:57:42 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCLAUX (DBID=2639214075)
RMAN> run{
2> allocate channel disk1 device type disk format='/oracle/backup/backdb<oracleaux_%s_%T.bak';
3> backup as compressed backupset database include current controlfile;
4> release channel disk1;
5> }
using target database control file instead of recovery catalog
allocated channel: disk1
channel disk1: sid=1624 devtype=DISK
Starting backup at 14-4月 -12
channel disk1: starting compressed full datafile backupset
channel disk1: specifying datafile(s) in backupset
input datafile fno=00001 name=/dev/raw/raw1
input datafile fno=00002 name=/dev/raw/raw3
input datafile fno=00003 name=/dev/raw/raw2
input datafile fno=00004 name=/dev/raw/raw4
channel disk1: starting piece 1 at 14-4月 -12
channel disk1: finished piece 1 at 14-4月 -12
piece handle=/oracle/backup/backdb<oracleaux_4_20120414.bak tag=TAG20120414T232447 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:45
channel disk1: starting compressed full datafile backupset
channel disk1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel disk1: starting piece 1 at 14-4月 -12
channel disk1: finished piece 1 at 14-4月 -12
piece handle=/oracle/backup/backdb<oracleaux_5_20120414.bak tag=TAG20120414T232447 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:03
Finished backup at 14-4月 -12
released channel: disk1
RMAN>
此备份包含了控制文件和spfile文件的自动备份。
3.2、关闭数据库并清空相关数据库文件
使用dd 命令清空raw设备上的数据
[oracle@clone_sigle oracle]$ dd if=/dev/zero of=/dev/raw/raw1 bs=8192 count=12800
12800+0 records in
12800+0 records out
其他raw参照执行
3.3、执行恢复
由于spfile文件已经被清理，可以使用rman来启动一个默认的数据库到nomount状态来执行恢复。
[oracle@clone_sigle oracle]$ rman target /
[uniread] Loaded history (130 lines)
Recovery Manager: Release 10.2.0.4.0 - Production on 星期六 4月 14 23:31:54 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/db10g/dbs/initorclaux.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 1266320 bytes
Variable Size 58723696 bytes
Database Buffers 92274688 bytes
Redo Buffers 7118848 bytes
RMAN>
使用sqlplus连接到数据库
[oracle@clone_sigle oracle]$ sq
[uniread] Loaded history (241 lines)
SQL*Plus: Release 10.2.0.4.0 - Production on 星期六 4月 14 23:34:01 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
SQL>
使用dbms_backup_restore包来恢复spfile文件、和控制文件、数据文件
恢复spfile文件：
[oracle@clone_sigle oracle]$ sq
[uniread] Loaded history (267 lines)
SQL*Plus: Release 10.2.0.4.0 - Production on 星期六 4月 14 23:39:24 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
#如果为磁带,这里type=>'sbt_tape'
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreSpfileTo(sfname=>'/oracle/db10g/dbs/spfileorclaux.ora');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/oracle/backup/backdb<oracleaux_5_20120414.bak', params=>null);
#如果为磁带，params不变
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
恢复控制文件：
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlFileTo(cfname=>'/dev/raw/raw6');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/oracle/backup/backdb<oracleaux_5_20120414.bak', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlFileTo(cfname=>'/dev/raw/raw7');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/oracle/backup/backdb<oracleaux_5_20120414.bak', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlFileTo(cfname=>'/dev/raw/raw8');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/oracle/backup/backdb<oracleaux_5_20120414.bak', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
恢复数据文件：
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6
7 BEGIN
8 sys.dbms_backup_restore.restoreSetDatafile;
9 sys.dbms_backup_restore.restoreDataFileTo(dfnumber=>1,toname=>'/dev/raw/raw1');
10 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/oracle/backup/backdb<oracleaux_4_20120414.bak', params=>null);
11 END;
12
13 BEGIN
14 sys.dbms_backup_restore.restoreSetDatafile;
15 sys.dbms_backup_restore.restoreDataFileTo(dfnumber=>2,toname=>'/dev/raw/raw3');
16 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/oracle/backup/backdb<oracleaux_4_20120414.bak', params=>null);
17 END;
18
19 BEGIN
20 sys.dbms_backup_restore.restoreSetDatafile;
21 sys.dbms_backup_restore.restoreDataFileTo(dfnumber=>3,toname=>'/dev/raw/raw2');
22 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/oracle/backup/backdb<oracleaux_4_20120414.bak', params=>null);
23 END;
24
25 BEGIN
26 sys.dbms_backup_restore.restoreSetDatafile;
27 sys.dbms_backup_restore.restoreDataFileTo(dfnumber=>4,toname=>'/dev/raw/raw4');
28 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/oracle/backup/backdb<oracleaux_4_20120414.bak', params=>null);
29 END;
30
31 sys.dbms_backup_restore.deviceDeallocate;
32 END;
33 /
PL/SQL procedure successfully completed.
SQL>
3.4、重启数据库并验证恢复
这里缺失临时表空间的恢复，在数据库打开的时候会自动创建。
由于日志也被dd清理，因此数据库打开是时候也会自动创建。
在打开数据库之前需要使用rman先恢复数据库。
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 486539264 bytes
Fixed Size 1268196 bytes
Variable Size 138413596 bytes
Database Buffers 339738624 bytes
Redo Buffers 7118848 bytes
SQL> alter database mount;
Database altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options
[uniread] Saved history (422 lines)
[oracle@clone_sigle oracle]$ rman target /
[uniread] Loaded history (132 lines)
Recovery Manager: Release 10.2.0.4.0 - Production on 星期六 4月 14 23:59:12 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCLAUX (DBID=2639214075, not open)
RMAN> recover database;
Starting recover at 14-4月 -12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1641 devtype=DISK
starting media recovery
unable to find archive log
archive log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/14/2012 23:59:22
RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 579708
#这里报错是因为联机日志被dd命令清空了
RMAN> alter database open resetlogs;
database opened
RMAN>
同时在alert日志中能看到如下内容
Sat Apr 14 23:59:20 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clear
Sat Apr 14 23:59:20 2012
alter database recover datafile list
1 , 2 , 3 , 4
Completed: alter database recover datafile list
1 , 2 , 3 , 4
Sat Apr 14 23:59:20 2012
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
parallel recovery started with 2 processes
ORA-279 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
Sat Apr 14 23:59:20 2012
alter database recover cancel
Sat Apr 14 23:59:22 2012
Media Recovery Canceled
Completed: alter database recover cancel
Sat Apr 14 23:59:30 2012
alter database open resetlogs
Sat Apr 14 23:59:30 2012
Errors in file /oracle/admin/orclaux/udump/orclaux_ora_10992.trc:
ORA-00367: checksum error in log file header
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/dev/raw/raw10'
Sat Apr 14 23:59:30 2012
Errors in file /oracle/admin/orclaux/udump/orclaux_ora_10992.trc:
ORA-00367: checksum error in log file header
ORA-00316: log 2 of thread 1, type 0 in header is not log file
ORA-00312: online log 2 thread 1: '/dev/raw/raw12'
RESETLOGS after incomplete recovery UNTIL CHANGE 579708
Resetting resetlogs activation ID 2640878604 (0x9d689c0c)
Sat Apr 14 23:59:32 2012
Setting recovery target incarnation to 4
Sat Apr 14 23:59:32 2012
Hex dump of (file 201, block 1) in trace file /oracle/admin/orclaux/bdump/orclaux_dbw0_10789.trc
Corrupt block relative dba: 0x00400001 (file 201, block 1)
Completely zero block found during data file header read
Reread of rdba: 0x00400001 (file 201, block 1) found valid data
Sat Apr 14 23:59:32 2012
Errors in file /oracle/admin/orclaux/bdump/orclaux_dbw0_10789.trc:
ORA-01186: Message 1186 not found; No message file for product=RDBMS, facility=ORA; arguments: [201]
ORA-01122: Message 1122 not found; No message file for product=RDBMS, facility=ORA; arguments: [201]
ORA-01110: Message 1110 not found; No message file for product=RDBMS, facility=ORA; arguments: [201] [/dev/raw/raw5]
ORA-01205: Message 1205 not found; No message file for product=RDBMS, facility=ORA; arguments: [0]
File 201 not verified due to error ORA-01122
Sat Apr 14 23:59:32 2012
Assigning activation ID 2640864264 (0x9d686408)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=20, OS id=10997
Sat Apr 14 23:59:32 2012
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=21, OS id=10999
Sat Apr 14 23:59:33 2012
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /dev/raw/raw10
Current log# 1 seq# 1 mem# 1: /dev/raw/raw11
Successful open of redo thread 1
Sat Apr 14 23:59:33 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Apr 14 23:59:33 2012
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Sat Apr 14 23:59:33 2012
ARC0: Becoming the heartbeat ARCH
Sat Apr 14 23:59:33 2012
SMON: enabling cache recovery
Sat Apr 14 23:59:34 2012
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Sat Apr 14 23:59:34 2012
Errors in file /oracle/admin/orclaux/bdump/orclaux_dbw0_10789.trc:
ORA-01186: Message 1186 not found; No message file for product=RDBMS, facility=ORA; arguments: [201]
ORA-01122: Message 1122 not found; No message file for product=RDBMS, facility=ORA; arguments: [201]
ORA-01110: Message 1110 not found; No message file for product=RDBMS, facility=ORA; arguments: [201] [/dev/raw/raw5]
ORA-01205: Message 1205 not found; No message file for product=RDBMS, facility=ORA; arguments: [0]
Sat Apr 14 23:59:34 2012
File 201 not verified due to error ORA-01122
Sat Apr 14 23:59:34 2012
Dictionary check complete
Sat Apr 14 23:59:34 2012
SMON: enabling tx recovery
Sat Apr 14 23:59:34 2012
Cannot re-create tempfile /dev/raw/raw5, the same name file exists
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 2
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=22, OS id=11001
Sat Apr 14 23:59:37 2012
LOGSTDBY: Validating controlfile with logical metadata
Sat Apr 14 23:59:37 2012
LOGSTDBY: Validation complete
Completed: alter database open resetlogs
验证恢复
[oracle@clone_sigle oracle]$ sq
[uniread] Loaded history (422 lines)
SQL*Plus: Release 10.2.0.4.0 - Production on 星期日 4月 15 00:03:28 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
SQL> select * from test_restore;
T_NUM T_DATE
---------- -----------------
1 20120414 22:49:12
SQL>
- 使用dbms_backup_restore包恢复数据库学习
- 使用dbms_backup_restore异地恢复数据库
- dbms_backup_restore恢复数据库
- Oracle dbms_backup_restore恢复数据库
- 利用dbms_backup_restore恢复数据库
- dbms_backup_restore包的使用
- RMAN dbms_backup_restore 包的使用
- 利用dbms_backup_restore包手工进行恢复
- oracle sys.dbms_backup_restore包恢复数据
- dbms_backup_restore恢复测试
- 应对RMAN-06026错误，使用dbms_backup_restore进行恢复
- 使用dbms_backup_restore修改DBID
- 使用dbms_backup_restore修改DBID
- 使用dbms_backup_restore包修改dbname及dbid
- 使用日志恢复数据库
- 使用RMAN恢复数据库
- Oracle9使用oradata恢复数据库
- 使用数据泵备分恢复数据库
- socket编程原理
- 仿QQ]\MSN弹出框WEB
- 深入了解C#系列：谈谈C#中垃圾回收与内存管理机制
- Android textview字体颜色显示和图片显示
- 页面静态化的实现实例分享
- 使用dbms_backup_restore包恢复数据库学习
- javascript 获取鼠标点击的元素
- jQuery Ajax(load,post,get,ajax) 实例 全解析
- Bullshit Bingo
- javascript操作radio
- Jquery.Validate AJAX验证
- 关于C++虚表和虚指针
- [CyanogenMOD编译]Android源码总体结构
- 关于Android APK反编译的探索
