昨天做一个实验,结果把数据库搞坏了,当试图进行恢复时居然报了RMAN-06026错误。 回想一下,原来在尝试恢复中使用了_allow_resetlogs_corruption参数,resetlogs之后,Oracle使用当前的控制文件不答应从这个历史备份集中进行恢复。
由于我没有使用catalog,所以尝试使用dbms_backup_restore进行恢复。
1.错误信息
我们看到虽然list backup可以显示备份集,但是无法进行恢复,错误为RMAN-06026,RMAN-06026。
[oracle@jumper oradata]$ rman target /
Recovery Manager: Release 9.2.0.4.0 - ProdUCtion
Copyright (c) 1995, 2002, Oracle Corporation.All rights reserved.
connected to target database: CONNER (DBID=3152029224)
RMAN restore database;
Starting restore at 11-JUN-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/11/2005 01:19:01
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN list backup;
List of Backup Sets
===================
BS KeyType LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13Full1G DISK00:03:20 09-JUN-05
BP Key: 13 Status: AVAILABLE Tag: TAG20050609T173346
Piece Name: /opt/oracle/product/9.2.0/dbs/0ggmiabq_1_1
SPFILE Included: Modification time: 08-JUN-05
List of Datafiles in backup set 13
File LV Type Ckp SCNCkp TimeName
---- -- ---- ---------- --------- ----
1 Full 24056026909-JUN-05 /opt/oracle/oradata/conner/system01.dbf
2 Full 24056026909-JUN-05 /opt/oracle/oradata/conner/undotbs01.dbf
3 Full 24056026909-JUN-05 /opt/oracle/oradata/conner/users01.dbf
RMAN exit
Recovery Manager complete.
2.使用dbms_backup_restore进行恢复
dbms_backup_restore是一个非常强大的package,可以在数据库nomount下使用,用于从备份集中读取各类文件。
本例使用如下脚本:
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate (type='',ident='t1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=01,toname='/opt/oracle/oradata/conner/system01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=02,toname='/opt/oracle/oradata/conner/undotbs01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=03,toname='/opt/oracle/oradata/conner/users01.dbf');
sys.dbms_backup_restore.restoreBackupPiece(done=done,handle='/opt/oracle/product/9.2.0/dbs/0ggmiabq_1_1', params=null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
3.执行恢复
[oracle@jumper conner]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Jun 11 01:24:34 2005
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
Connected to an idle instance.
SQL startup nomount;
ORACLE instance started.
Total System Global Area101782828 bytes
Fixed Size 451884 bytes
Variable Size37748736 bytes
Database Buffers 62914560 bytes
Redo Buffers 667648 bytes
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.restoreDatafileTo(dfnumber=01,toname='/opt/oracle/oradata/conner/system01.dbf');
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=02,toname='/opt/oracle/oradata/conner/undotbs01.dbf');
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=03,toname='/opt/oracle/oradata/conner/users01.dbf');
10 sys.dbms_backup_restore.restoreBackupPiece(done=done,handle='/opt/oracle/product/9.2.0/dbs/0ggmiabq_1_1',
params=null);
11 sys.dbms_backup_restore.deviceDeallocate;
12END;
13/
PL/SQL procedure successfully completed.
SQL
至此,从备份集中读取文件完毕。
4.恢复控制文件
由于大意,也没有备份控制文件,所以只好重建控制文件。
SQL alter database mount;
Database altered.
SQL alter database backup controlfile to trace;
Database altered.
找到trace文件,编辑、执行重建控制文件需要部分:
[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Jun 11 01:30:50 2005
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL startup nomount;
ORACLE instance started.
Total System Global Area101782828 bytes
Fixed Size 451884 bytes
Variable Size37748736 bytes
Database Buffers 62914560 bytes
Redo Buffers 667648 bytes
SQL set echo on
SQL @ctl
SQL
SQL CREATE CONTROLFILE REUSE DATABASE "CONNER" RESETLOGSARCHIVELOG
2--SET STANDBY TO MAXIMIZE PERFORMANCE
3MAXLOGFILES 5
4MAXLOGMEMBERS 3
5MAXDATAFILES 100
6MAXINSTANCES 1
7MAXLOGHISTORY 1361
8LOGFILE
9GROUP 1 '/opt/oracle/oradata/conner/redo01.log'SIZE 10M,
10GROUP 2 '/opt/oracle/oradata/conner/redo02.log'SIZE 10M,
11GROUP 3 '/opt/oracle/oradata/conner/redo03.log'SIZE 10M
12-- STANDBY LOGFILE
13DATAFILE
14'/opt/oracle/oradata/conner/system01.dbf',
15'/opt/oracle/oradata/conner/undotbs01.dbf',
16'/opt/oracle/oradata/conner/users01.dbf'
17CHARACTER SET ZHS16GBK
18;
Control file created.
5.执行恢复
SQL recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL recover database using backup controlfile until cancel;
ORA-00279: change 240560269 generated at 06/09/2005 17:33:48 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/conner/archive/1_7.dbf
ORA-00280: change 240560269 for thread 1 is in sequence #7
Specify log: {=suggested filename AUTO CANCEL}
auto
ORA-00279: change 240600632 generated at 06/10/2005 10:42:26 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/conner/archive/1_8.dbf
ORA-00280: change 240600632 for thread 1 is in sequence #8
ORA-00278: log file '/opt/oracle/oradata/conner/archive/1_7.dbf' no longer needed for this recovery
Specify log: {=suggested filename AUTO CANCEL}
auto
ORA-00279: change 240620884 generated at 06/10/2005 10:45:42 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/conner/archive/1_9.dbf
ORA-00280: change 240620884 for thread 1 is in sequence #9
ORA-00278: log file '/opt/oracle/oradata/conner/archive/1_8.dbf' no longer needed for this recovery
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [4242465], [1], [9], [314], [272], [], []
ORA-10567: Redo is inconsistent with data block (file# 1, block# 48161)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/opt/oracle/oradata/conner/system01.dbf'
ORA-10560: block type 'DATA SEGMENT HEADER - UNLIMITED'
ORA-01112: media recovery not started
SQL recover database using backup controlfile until cancel;
ORA-00279: change 240620949 generated at 06/10/2005 10:45:44 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/conner/archive/1_9.dbf
ORA-00280: change 240620949 for thread 1 is in sequence #9
Specify log: {=suggested filename AUTO CANCEL}
cancel
Media recovery cancelled.
SQL alter database open resetlogs;
Database altered.
SQL select name from v$datafile;
NAME
------------------------------------------------------------
/opt/oracle/oradata/conner/system01.dbf
/opt/oracle/oradata/conner/undotbs01.dbf
/opt/oracle/oradata/conner/users01.dbf
SQL
至此恢复完毕。