{over}oracle 恢复的凝问

numenhuang

举例：
BACKUP information:

RMAN>show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/test3/%F.bck';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/
snapcf_test3.f'; # default

RMAN> RUN {
2>      backup
3>      incremental level 0
4>      format '/u01/app/oracle/backup/test3/%d_%s_%p'
5>      (database include current controlfile);
6>    }

Starting backup at 01-OCT-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/test3/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/test3/sysaux01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/test3/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/test3/users01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/test3/idx01.dbf
channel ORA_DISK_1: starting piece 1 at 01-OCT-06
channel ORA_DISK_1: finished piece 1 at 01-OCT-06
piece handle=/u01/app/oracle/backup/test3/TEST3_1_1 tag=TAG20061001T051523 comme
nt=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting incremental level 0 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 01-OCT-06
channel ORA_DISK_1: finished piece 1 at 01-OCT-06
piece handle=/u01/app/oracle/backup/test3/TEST3_2_1 tag=TAG20061001T051523 comme
nt=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 01-OCT-06

Starting Control File and SPFILE Autobackup at 01-OCT-06
piece handle=/u01/app/oracle/backup/test3/c-683700542-20061001-00.bck comment=NO
NE
Finished Control File and SPFILE Autobackup at 01-OCT-06

RMAN> list backup;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Incr 0  265.30M    DISK        00:00:24     01-OCT-06
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20061001T051523
        Piece Name: /u01/app/oracle/backup/test3/TEST3_1_1
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 399597     01-OCT-06 /u01/app/oracle/oradata/test3/system01.dbf
  2    0  Incr 399597     01-OCT-06 /u01/app/oracle/oradata/test3/undotbs01.dbf
  3    0  Incr 399597     01-OCT-06 /u01/app/oracle/oradata/test3/sysaux01.dbf
  4    0  Incr 399597     01-OCT-06 /u01/app/oracle/oradata/test3/users01.dbf
  5    0  Incr 399597     01-OCT-06 /u01/app/oracle/oradata/test3/idx01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Incr 0  7.08M      DISK        00:00:01     01-OCT-06
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20061001T051523
        Piece Name: /u01/app/oracle/backup/test3/TEST3_2_1
  Control File Included: Ckp SCN: 399597       Ckp time: 01-OCT-06

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    7.11M      DISK        00:00:01     01-OCT-06
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20061001T051551
        Piece Name: /u01/app/oracle/backup/test3/c-683700542-20061001-00.bck
  Control File Included: Ckp SCN: 399597       Ckp time: 01-OCT-06
  SPFILE Included: Modification time: 01-OCT-06


RECOVERY information

RMAN>set dbid 683700542;

RMAN> startup nomount;

Oracle instance started

Total System Global Area     104857600 bytes

Fixed Size                     2018872 bytes
Variable Size                 75497928 bytes
Database Buffers              25165824 bytes
Redo Buffers                   2174976 bytes

RMAN> RUN {
2>     SET CONTROLFILE AUTOBACKUP FORMAT
3>         FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/test3/%F.bck';
4>     RESTORE CONTROLFILE FROM AUTOBACKUP;
5>    }
这里也可以通过下面方式来恢复controlfile：
RMAN> restore controlfile from '/u01/app/oracle/backup/test3/TEST3_2_1';
RMAN> restore controlfile from '/u01/app/oracle/backup/test3/c-683700542-20061001-00.bck';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 01-OCT-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: TEST3
channel ORA_DISK_1: no autobackups found in the recovery area --------->pay attention to this point（oracle默认recovery搜索目录是flash_recovery_area）
channel ORA_DISK_1: looking for autobackup on day: 20061001
channel ORA_DISK_1: autobackup found: /u01/app/oracle/backup/test3/c-683700542-2
0061001-00.bck
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/app/oracle/oradata/test3/control01.ctl
output filename=/u01/app/oracle/oradata/test3/control02.ctl
output filename=/u01/app/oracle/oradata/test3/control03.ctl
Finished restore at 01-OCT-06

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 01-OCT-06
Starting implicit crosscheck backup at 01-OCT-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 01-OCT-06

Starting implicit crosscheck copy at 01-OCT-06
using channel ORA_DISK_1
Finished implicit crosscheck copy at 01-OCT-06

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/test3/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test3/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test3/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test3/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test3/idx01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/test3/TEST3
_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/test3/TEST3_1_1 tag=TAG20061001T051523
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 01-OCT-06

[ 本帖最后由 numenhuang 于 2006-10-4 01:04 编辑 ]

numenhuang

如果没有catalog，restore controlfile要考虑以下的情况：
1. 是否在backup时候有没有configure controlfile autobackup format for device type xxx to '%Fxxx';
    如果设置了，在restore的时候可以从两种方式来恢复：
    a.  restore controlfile from ‘backupset_controlfile_full_path_name';
    b.  run {
             set controlfile autobackup format for device type xxx to '%Fxxx';
             restore controlfile from autobackup;
         }

2. 如果没有设置controlfile autobackup format，则oracle会在flash_recovery_area/SID/xxxx/目录来寻找autobackup file。

[ 本帖最后由 numenhuang 于 2006-10-4 01:00 编辑 ]

ghbspecial

前面的备份情况基本和你的一样，我就没有贴上来了，之后我将系统和oracle重装了一遍，开始用备份出来数据库全备和控制文件进行恢复，所有的路径和原来的一样，控制文件已经可以恢复，可是在restore database 却报了下面的错误，list backup也可以看备份信息，而且文件也在，真是不解，谢谢您的回复，祝您中秋节快乐。
RMAN> startup nomount;

Oracle instance started

Total System Global Area     236000356 bytes

Fixed Size                      451684 bytes
Variable Size                201326592 bytes
Database Buffers              33554432 bytes
Redo Buffers                    667648 bytes

RMAN> set DBID=2347671489;

executing command: SET DBID

RMAN> restore controlfile from '/opt/ora9/product/9.2.0.4/dbs/c-2347671489-20060630-01';

Starting restore at 29-JUN-06

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/opt/ora9/oradata/oradb/control01.ctl
output filename=/opt/ora9/oradata/oradb/control02.ctl
output filename=/opt/ora9/oradata/oradb/control03.ctl
Finished restore at 29-JUN-06

RMAN> alter database mount;

database mounted

RMAN> restore database;

Starting restore at 29-JUN-06

using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/29/2006 23:29:48
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-06023: no backup or copy of datafile 9 found to restore
RMAN-06023: no backup or copy of datafile 8 found to restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to 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 Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    761M       DISK        00:02:32     30-JUN-06      
        BP Key: 1   Status: AVAILABLE   Tag: DBALL
        Piece Name: /tmp/dball01hmum1k_1_1_20060630
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 292554     30-JUN-06 /opt/ora9/oradata/oradb/system01.dbf
  2       Full 292554     30-JUN-06 /opt/ora9/oradata/oradb/undotbs01.dbf
  3       Full 292554     30-JUN-06 /opt/ora9/oradata/oradb/cwmlite01.dbf
  4       Full 292554     30-JUN-06 /opt/ora9/oradata/oradb/drsys01.dbf
  5       Full 292554     30-JUN-06 /opt/ora9/oradata/oradb/example01.dbf
  6       Full 292554     30-JUN-06 /opt/ora9/oradata/oradb/indx01.dbf
  7       Full 292554     30-JUN-06 /opt/ora9/oradata/oradb/odm01.dbf
  8       Full 292554     30-JUN-06 /opt/ora9/oradata/oradb/tools01.dbf
  9       Full 292554     30-JUN-06 /opt/ora9/oradata/oradb/users01.dbf
  10      Full 292554     30-JUN-06 /opt/ora9/oradata/oradb/xdb01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    761M       DISK        00:02:30     30-JUN-06      
        BP Key: 2   Status: AVAILABLE   Tag: DBALL
        Piece Name: /tmp/dball03hmumf9_3_1_20060630
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 293648     30-JUN-06 /opt/ora9/oradata/oradb/system01.dbf
  2       Full 293648     30-JUN-06 /opt/ora9/oradata/oradb/undotbs01.dbf
  3       Full 293648     30-JUN-06 /opt/ora9/oradata/oradb/cwmlite01.dbf
  4       Full 293648     30-JUN-06 /opt/ora9/oradata/oradb/drsys01.dbf
  5       Full 293648     30-JUN-06 /opt/ora9/oradata/oradb/example01.dbf
  6       Full 293648     30-JUN-06 /opt/ora9/oradata/oradb/indx01.dbf
  7       Full 293648     30-JUN-06 /opt/ora9/oradata/oradb/odm01.dbf
  8       Full 293648     30-JUN-06 /opt/ora9/oradata/oradb/tools01.dbf
  9       Full 293648     30-JUN-06 /opt/ora9/oradata/oradb/users01.dbf
  10      Full 293648     30-JUN-06 /opt/ora9/oradata/oradb/xdb01.dbf

RMAN>

numenhuang

原帖由 ghbspecial 于 2006-10-5 22:45 发表
前面的备份情况基本和你的一样，我就没有贴上来了，之后我将系统和oracle重装了一遍，开始用备份出来数据库全备和控制文件进行恢复，所有的路径和原来的一样，控制文件已经可以恢复，可是在restore database 却报 ...

你执行restore controlfile的时间，和你的backup set时间好像不大符合。数据库有没有作过alter database open resetlogs？（在这两个backupset之后）。如果是作的controlfile autobackup，那要注意，如果open resetlogs，backup中控制文件会被自动更新的。如果要恢复，则要set until time。执行一下：
RMAN>list incarnation of database;
结果？

ghbspecial

原帖由 numenhuang 于 2006-10-6 11:39 发表


你执行restore controlfile的时间，和你的backup set时间好像不大符合。数据库有没有作过alter database open resetlogs？（在这两个backupset之后）。如果是作的controlfile autobackup，那要注意，如果open ...

真心的感谢numenhuang您的指点，同时也感谢指点我的兄弟们，中秋之夜特此祝您工作顺利，幸福快乐。  （有点老土了。。）

  看到您的提醒执行restore controlfile时间，看到时间这两个字，我突然明白了，问题就是出现在时间上了，因为我在执行备份前和将系统还原后的时间很不统一（我在vmware做的实验，没太注意时间。）所以才出现了上面的错误尽写控制文件可以恢复了，list backup也可看到备份见，但就是不能正常恢复原因。我又重新做了一次实验，在备份前系统时间确认好与当前时间一致，在vmware还原完系统，我又把时间与当前时间一致，就可以正确恢复了，问题解决了。
   在做这个实验之前，凭借简单理解我觉得rman应该支持如果有rman全备和控制文件，就算重做系统后也应该可以恢复的，就像前bluestone前面说的那样，不过，我就是想在实验中，实验成功后才放心，所以一直实验一直没有成功，今天找到原因了，原来是时间不一致的原因。虽然错误很低级，但是在排错的过程中，还是让我学到了很多东西，谢谢你们的支持，也希望兄弟们以此为鉴。

pq777

好文章，顶一下。大家辛苦了。

ghbspecial

我在上面实验成功的基础上，又做了一下面的试验，我的想法是这样的，假设前面的备份已经有了，并且成功恢复了，这时我创建了一个表，并插入一条数据，执行提交commit ,日志切换，alter system switch logfile 生成归档日志，我再将归档日志备份出来了。现在假定系统再次出现故障，我还是使用前面的全备份文件和控制文件，还有刚才生成归档日志，希望数据库恢复完毕后，将前面创建前创建的那个表也成功恢复。
可是现在我没能将上面的创建表成功恢复,下面我的操作贴上来，请大家指点一下，该如何操作，还是我哪里错了。

现在开始恢复：
[oracle@ns ~]$ rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 236000356 bytes

Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

RMAN> set DBID=2347671489

executing command: SET DBID


RMAN> restore controlfile from '/opt/ora9/product/9.2.0.4/dbs/c-2347671489-20061006-00';

Starting restore at 08-OCT-06

using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/opt/ora9/oradata/oradb/control01.ctl
output filename=/opt/ora9/oradata/oradb/control02.ctl
output filename=/opt/ora9/oradata/oradb/control03.ctl
Finished restore at 08-OCT-06

RMAN> startup mount;

database is already started
database mounted

RMAN> list backup
2> ;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 761M DISK 00:02:59 06-OCT-06
BP Key: 1 Status: AVAILABLE Tag: DBALL
Piece Name: /tmp/dball01hv6ll4_1_1_20061006
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 196538 06-OCT-06 /opt/ora9/oradata/oradb/system01.dbf
2 Full 196538 06-OCT-06 /opt/ora9/oradata/oradb/undotbs01.dbf
3 Full 196538 06-OCT-06 /opt/ora9/oradata/oradb/cwmlite01.dbf
4 Full 196538 06-OCT-06 /opt/ora9/oradata/oradb/drsys01.dbf
5 Full 196538 06-OCT-06 /opt/ora9/oradata/oradb/example01.dbf
6 Full 196538 06-OCT-06 /opt/ora9/oradata/oradb/indx01.dbf
7 Full 196538 06-OCT-06 /opt/ora9/oradata/oradb/odm01.dbf
8 Full 196538 06-OCT-06 /opt/ora9/oradata/oradb/tools01.dbf
9 Full 196538 06-OCT-06 /opt/ora9/oradata/oradb/users01.dbf
10 Full 196538 06-OCT-06 /opt/ora9/oradata/oradb/xdb01.dbf

RMAN> restore database;

Starting restore at 08-OCT-06

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/ora9/oradata/oradb/system01.dbf
restoring datafile 00002 to /opt/ora9/oradata/oradb/undotbs01.dbf
restoring datafile 00003 to /opt/ora9/oradata/oradb/cwmlite01.dbf
restoring datafile 00004 to /opt/ora9/oradata/oradb/drsys01.dbf
restoring datafile 00005 to /opt/ora9/oradata/oradb/example01.dbf
restoring datafile 00006 to /opt/ora9/oradata/oradb/indx01.dbf
restoring datafile 00007 to /opt/ora9/oradata/oradb/odm01.dbf
restoring datafile 00008 to /opt/ora9/oradata/oradb/tools01.dbf
restoring datafile 00009 to /opt/ora9/oradata/oradb/users01.dbf
restoring datafile 00010 to /opt/ora9/oradata/oradb/xdb01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/dball01hv6ll4_1_1_20061006 tag=DBALL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 08-OCT-06

RMAN> recover database;

Starting recover at 08-OCT-06
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 10 is already on disk as file /opt/ora9/oradata/oradb/redo01.log
archive log filename=/opt/ora9/oradata/oradb/redo01.log thread=1 sequence=10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/08/2006 16:23:49
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/opt/ora9/oradata/oradb/redo01.log'
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [kcrfr_resize2], [2151608832], [2151677952], [33542144], [], [], [], []RMAN> alter database open resetlogs;

database opened

RMAN> exit


[oracle@ns ~]$ sqlplus " / as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 8 16:29:02 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select * from test2;
select * from test2
*
ERROR at line 1:
ORA-00942: table or view does not exist
数据库虽然恢复了，可是创建的那个表并没有被恢复。

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1


ll /opt/arch
-rw-r----- 1 oracle oinstall 556032 Oct 8 16:36 1_1.dbf

不知为什么没有成功应用归档日志

ghbspecial

顶一下。。。。

numenhuang

你现在的数据库备份是最近一次resetlogs以前的备份。要恢复的话，也只能恢复到resetlogs以前的状态。

ghbspecial

谢谢你来了，，呵呵。。
我的备份确实是resetlogs以前的备份，恢复完毕也是回到了reselogs以前的状态，我想知道为什么不能应用归档日志。
因为我是这样想的，我每次做完一个全备或是差异备份后，我都想将归档日志删除，如果某一天出现故障，我将对应的最近备份恢复后，再应用当时归档日志这样就可以恢复到更近的时刻。可是我现在用的备份文件恢复后不能应用归档日志，所以很是不解，谢谢再次指点。。。。。