Re: RMAN restore of full backup fails completely

On Fri, 21 Nov 2008 11:09:46 -0600, "Andreas Zimmermann" <Souldiver_at_nurfuerspam.de> wrote:

>Hi,
>
>after searching for a hint to solve this problem for quite some time, I
>thought I might try it here to get help - I am a RMAN novice, so please
>excuse me seeming uninformed or plain dumb, but since this is a production
>problem posting instead of reading to even more material than I already did
>and trying around more is not really an option.
>
>Situation:
>
>We have servers (DELL 2650, 6 disks, 12 GB RAM) both running CentOS 5
>(Redhat) & Oracle 10gR2. Both servers are set up the same way (and running 4
>instances each - 2 production, 1 development, 1 reporting) with one being
>the production server (the 2 production instances up & running in archivelog
>mode connected to our web application) and one the development / reporting
>server. Overnight the production data is automatically restored to the 'warm
>backup' server (via RMAN full / cumulative backups & archivelogs) to have a
>somewhat actual failover in case of emergency and for reporting. After a
>disk failure on the production server we actually had to switch over and
>everything worked fine up to that point: restored the last cumulative backup
>and recovered to the last archived log ... so far so good. Now that the
>other server has new disks and is back in business I wanted to set it up as
>'warm backup' again but unfortunately I ran into problems that I neither
>understand nor can solve (which is a result of not understanding what's
>happening obviously). Here is what I try to do:
>
>1. Take a full (online) backup of the production instance(s) via RMAN using
>one of the following scripts (i tried both full backup & cumulative lvl 0)
>
>-- script 1
>run{
>backup database;
>sql 'alter system archive log current';
>backup archivelog all;
>}
>exit;
>
>-- script 2
>run{
>backup incremental level 0 cumulative device type disk tag
>'daily_incr_backup_lvl_0' database include current controlfile;
>sql 'alter system archive log current';
>backup device type disk tag 'archivelog_backup' archivelog all not backed up
>delete all input;
>crosscheck backupset;
>crosscheck archivelog all;
>delete noprompt obsolete device type disk;
>}
>exit;
>
>2. Copy all the files generated over to the apropriate path in the Flash
>Recovery Area of the warm backup server (via sync)
>
>3. Try to restore & recover those backups on the repaired warm backup server
>using our original restore script which worked fine (e.g. for the emergency
>switch / restore / recover) - resulting in the following error msg or a
>similar one (here is the logfile that is generated by my script):
>
>[oracle_at_wd-db-05 log]$ more 20081118-234000-RESTOREANDRECOVER-IBS.log
>
>Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 18 23:40:00
>2008
>
>Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
>connected to target database: IBS (DBID=507845374, not open)
>
>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 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 268435604 bytes
>Database Buffers 1862270976 bytes
>Redo Buffers 15556608 bytes
>
>Starting restore at 18-NOV-08
>allocated channel: ORA_DISK_1
>channel ORA_DISK_1: sid=322 devtype=DISK
>
>recovery area destination: /var/log/oracle/Flash_Recovery_Area/IBS
>database name (or database unique name) used for search: IBS
>channel ORA_DISK_1: autobackup found in the recovery area
>channel ORA_DISK_1: autobackup found:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/autobackup/2008_11_18/o1_mf_s_671153040_4l78qkb3_.
>bkp
>channel ORA_DISK_1: control file restore from autobackup complete
>output filename=/local/oracle/IBS/ibs/control01.ctl
>output filename=/local/oracle/IBS/ibs/control02.ctl
>output filename=/local/oracle/IBS/ibs/control03.ctl
>Finished restore at 18-NOV-08
>
>database mounted
>released channel: ORA_DISK_1
>
>Starting restore at 18-NOV-08
>Starting implicit crosscheck backup at 18-NOV-08
>allocated channel: ORA_DISK_1
>channel ORA_DISK_1: sid=322 devtype=DISK
>Crosschecked 46 objects
>Finished implicit crosscheck backup at 18-NOV-08
>
>Starting implicit crosscheck copy at 18-NOV-08
>using channel ORA_DISK_1
>Finished implicit crosscheck copy at 18-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/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15696_4l4q0zt0_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15697_4l4qx49f_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15698_4l4rsg0c_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15699_4l4solol_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15700_4l4tkq54_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15701_4l4vg14f_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15702_4l4wb5qw_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15703_4l4x69wt_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15704_4l4y2n7s_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15705_4l4yyxwm_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15706_4l4zv2mp_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15707_4l50q7ds_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15708_4l51mhsb_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15709_4l52hn1x_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15710_4l53ctjs_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15711_4l54847d_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15712_4l554fxt_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15713_4l560lnt_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15714_4l56wqcd_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15715_4l57rwbk_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15716_4l58o16s_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15717_4l59k603_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15718_4l5bflcb_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15719_4l5c9y03_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15720_4l5d61hp_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15721_4l5f2799_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15722_4l5fyjy6_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15723_4l5gtood_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15724_4l5hptbh_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15725_4l5jm41c_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15726_4l5kh8pf_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15727_4l5lcfy9_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15728_4l5m7g99_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15729_4l5n3lhg_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15730_4l5nzqlw_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15731_4l5ow3jz_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15732_4l5prcmo_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15733_4l5qnkjt_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15734_4l5rjpnw_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15735_4l5sdvgw_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15736_4l5t9540_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15737_4l5v59sx_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15738_4l5w1gmn_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15739_4l5wxm96_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15740_4l5xsx6n_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15741_4l5yp3wx_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15742_4l5zl8mq_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15743_4l60gf7r_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15744_4l61bpx8_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15745_4l626x94_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15746_4l6332xr_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15747_4l63z7j9_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15748_4l64vkb6_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15749_4l65qox7_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15750_4l66mtly_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15751_4l67hz7w_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/archivelog/2008_11_18/o1_mf_1_15528_4l69ljd1_.arc
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/backupset/2008_11_18/o1_mf_ncnn0_DAILY_FULL_BACKUP_LV_4l4pwljw_.bkp
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/backupset/2008_11_18/o1_mf_annnn_ARCHIVELOG_BACKUP_4l4px0rb_.bkp
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/backupset/2008_11_18/o1_mf_annnn_ARCHIVELOG_BACKUP_4l4pxld9_.bkp
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/backupset/2008_11_18/o1_mf_annnn_ARCHIVELOG_BACKUP_4l4py39t_.bkp
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/autobackup/2008_11_18/o1_mf_s_671069235_4l4pwmt6_.bkp
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/autobackup/2008_11_18/o1_mf_s_671069298_4l4pylw5_.bkp
>File Name:
>/var/log/oracle/Flash_Recovery_Area/IBS/IBS/autobackup/2008_11_18/o1_mf_s_671153040_4l78qkb3_.bkp
>
>using channel ORA_DISK_1
>
>RMAN-00571: ===========================================================
>RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
>RMAN-00571: ===========================================================
>RMAN-03002: failure of restore command at 11/18/2008 23:40:28
>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.
>
>Searching for a solution I found out that I can make the DB work rebuilding
>the controlfile from trace but that will not recover the data to the point
>in time it should (afaik), so that does not really help me.
>
>I don't really know why this is not working, since I take a full backup and
>just try to restore / recover it to get a 'baseline' on the backup server -
>is there anything I am missing or doing wrong or is there another constraint
>that I just miss out on ?! What can I do to finally establish RMAN backups
>again ?!
>
>Any idea, tip or help would be greatly appreciated - thanks in advance !
>
>Kind regards
>
>Andy
>

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