Re: RMAN restore of full backup fails completely

From: Andreas Zimmermann <Souldiver_at_nurfuerspam.de>
Date: Mon, 24 Nov 2008 16:02:53 -0600
Message-ID: <ggf8ad$837$1@aioe.org>

> Andy, I'm just guessing here, so here goes
>
> 1 You are not using a catalog database.
> 2 You might have autobackup controlfile on, but it is not obvious
> 3 Your autobackup has been made *prior* to the backup!
> Ie the autobackup is made when datafile 0 is included in the
> backupset, datafile 0 is the SYSTEM tablespace
> Using
> backup database plus archivelog
> the autobackup is made *after* the backup, so the backup is in the
> controlfile subsituting the catalog
> 5 Your controlfile is too old, and you are asking for *complete*
> recovery, implying your controlfile is current.
> 6 As your controlfile is not current the only thing you can do is
> *incomplete* recovery, and open the database with resetlogs.
>
> After you have gone through this, please correct your *first* backup
> script. The second is executing too many superfluous statements.
>
> Hth
> --
> Sybrand Bakker
> Senior Oracle DBA

Hi Sybrand,

thanks for the quick response ...

1. Yes, we don't use a catalog DB, just the controlfile
2. Yes, autobackup for controlfiles is on
3. I did a full online backup using your suggested command 'backup database plus archivelog', when i try a restore on the other server however, the result is not really different :( ... in addition I issued a 'list backupset' which shows that the files should be there since they're clearly in the backupset (by my understanding):

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Nov 22 13:19:27 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: USERDB (DBID=3794760173)

RMAN> run{shutdown immediate;

2> startup nomount;
3> restore controlfile from autobackup;
4> alter database mount;
5> restore database;
6> recover database;
7> alter database open resetlogs;
8> }
9> exit;

using target database control file instead of recovery catalog database closed
database dismounted
Oracle instance shut down

connected to target database (not started) Oracle instance started

Total System Global Area 2147483648 bytes

Fixed Size                     1220460 bytes
Variable Size                218103956 bytes
Database Buffers            1912602624 bytes
Redo Buffers                  15556608 bytes

Starting restore at 24-NOV-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=322 devtype=DISK

recovery area destination: /var/log/oracle/Flash_Recovery_Area/USERDB database name (or database unique name) used for search: USERDB channel ORA_DISK_1: autobackup found in the recovery area channel ORA_DISK_1: autobackup found:
/var/log/oracle/Flash_Recovery_Area/USERDB/USERDB/autobackup/2008_11_22/o1_mf_s_671457672_4ljl 79g8_.bkp
channel ORA_DISK_1: control file restore from autobackup complete

output filename=/local/oracle/UserDB/userdb/control01.ctl
output filename=/local/oracle/UserDB/userdb/control02.ctl
output filename=/local/oracle/UserDB/userdb/control03.ctl

Finished restore at 24-NOV-08

database mounted
released channel: ORA_DISK_1
For record type ARCHIVED LOG recids from 82 to 5946 are re-used before resync
For record type BACKUP PIECE recids from 5 to 5893 are re-used before resync For record type BACKUP SET recids from 5 to 5686 are re-used before resync

Starting restore at 24-NOV-08
Starting implicit crosscheck backup at 24-NOV-08 allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=322 devtype=DISK Crosschecked 52 objects
Finished implicit crosscheck backup at 24-NOV-08

Starting implicit crosscheck copy at 24-NOV-08 using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-NOV-08

searching for all files in the recovery area cataloging files...
cataloging done

List of Cataloged Files

---

File Name:
/var/log/oracle/Flash_Recovery_Area/USERDB/USERDB/autobackup/2008_11_22/o1_mf_s_671457672_4ljl79g8_.bkp

using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/24/2008 13:02:06
RMAN-06026: some targets not found - aborting 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

Recovery Manager complete.

RMAN> list backupset;

List of Backup Sets

---

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ---------------

6500    1.85G      DISK        00:11:18     22-NOV-08
        BP Key: 6688   Status: AVAILABLE  Compressed: YES  Tag:
TAG20081122T105607
        Piece Name:

/var/log/oracle/Flash_Recovery_Area/USERDB/USERDB/backupset/2008_11_22/o1_mf_annnn_TAG20081122T105607_4ljgfbjr_.bkp

  List of Archived Logs in backup set 6500   Thrd Seq Low SCN Low Time Next SCN Next Time

• ------- ---------- --------- ---------- --------- [...]

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ---------------

6501    1.82G      DISK        00:11:03     22-NOV-08
        BP Key: 6689   Status: AVAILABLE  Compressed: YES  Tag:
TAG20081122T105607
        Piece Name:

/var/log/oracle/Flash_Recovery_Area/USERDB/USERDB/backupset/2008_11_22/o1_mf_annnn_TAG20081122T105607_4ljh2w61_.bkp

  List of Archived Logs in backup set 6501   Thrd Seq Low SCN Low Time Next SCN Next Time

• ------- ---------- --------- ---------- --------- [...]

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 6502 Full 3.73G DISK 00:41:28 22-NOV-08   List of Datafiles in backup set 6502
  File LV Type Ckp SCN Ckp Time Name

• -- ---- ---------- --------- ---- 1 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/system01.dbf 2 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/undotbs01.dbf 3 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/sysaux01.dbf 4 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/users01.dbf 5 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/userdb_data_01.dbf 6 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/userdb_data_02.dbf 7 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/userdb_data_03.dbf 8 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/undotbs03.dbf 9 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/undotbs02.dbf 11 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/userdb_idx_01.dbf 12 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/userdb_idx_02.dbf 13 Full 240270500 22-NOV-08 /local/oracle/UserDB/userdb/userdb_idx_03.dbf

  Backup Set Copy #1 of backup set 6502
  Device Type Elapsed Time Completion Time Compressed Tag

• ------------ --------------- ---------- --- DISK 00:41:28 22-NOV-08 YES TAG20081122T111849

    List of Backup Pieces for backup set 6502 Copy #1     BP Key Pc# Status Piece Name

• --- ----------- ---------- 6690 1 AVAILABLE /var/log/oracle/Flash_Recovery_Area/USERDB/USERDB/backupset/2008_11_22/o1_mf_nnndf_TAG20081122T111849_4ljhqxfx_.bkp 6691 2 AVAILABLE /var/log/oracle/Flash_Recovery_Area/USERDB/USERDB/backupset/2008_11_22/o1_mf_nnndf_TAG20081122T111849_4ljk1k1j_.bkp

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ---------------

6503    85.08M     DISK        00:00:39     22-NOV-08
        BP Key: 6692   Status: AVAILABLE  Compressed: YES  Tag:
TAG20081122T120025
        Piece Name:

/var/log/oracle/Flash_Recovery_Area/USERDB/USERDB/backupset/2008_11_22/o1_mf_annnn_TAG20081122T120025_4ljl5tg5_.bkp

  List of Archived Logs in backup set 6503   Thrd Seq Low SCN Low Time Next SCN Next Time

• ------- ---------- --------- ---------- --------- 1 263 240218120 22-NOV-08 240272074 22-NOV-08 1 264 240272074 22-NOV-08 240328208 22-NOV-08 1 265 240328208 22-NOV-08 240351234 22-NOV-08

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ---------------

6504    Full    9.70M      DISK        00:00:00     22-NOV-08
        BP Key: 6693   Status: AVAILABLE  Compressed: NO  Tag:
TAG20081122T120112
        Piece Name:

/var/log/oracle/Flash_Recovery_Area/USERDB/USERDB/autobackup/2008_11_22/o1_mf_s_671457672_4ljl79g8_.bkp   Control File Included: Ckp SCN: 240352988 Ckp time: 22-NOV-08   SPFILE Included: Modification time: 22-NOV-08

4. The only way I can get the database 'back up' at that point is to recreate the controlfile from trace and do a 'recover database until cancel' with following alter database open resetlogs (which leaves me with totally not up to date data at all of course)

5. As I said, I am a novice to RMAN and I might lack knowledge of basics - unfortunately I do not really have the time to catch up right now (since this is production) ... I do not understand why i can't restore from this backup since it - according to the list - contains all needed data files but nevertheless tells me that some of them are missing. Any idea how I actually can make this backup / restore work again ?! Any way to tell RMAN to just use those files (a restore from that specific backupset using it's tag did not work eihter) ?!

Kind regards,

Andy Received on Mon Nov 24 2008 - 16:02:53 CST