Home » RDBMS Server » Backup & Recovery » Emergency! Help! Full rman backup restore problem
Emergency! Help! Full rman backup restore problem [message #60814] Mon, 08 March 2004 18:27 Go to next message
Roy
Messages: 30
Registered: May 2002
Member
Hello,

  DB Info:

This is for Oracle 9.2.0.4.0 database in archivelog mode backed up using RMAN using Oracle Enterprise Manager and restored using the same.  This is a production database and should be up tomorrow morning.  Help!

Description:

   We were planning to make some changes in the datafile size and user change.    So  I did a full backup of the database (including controlfile , logs etc.,).  The backup completed about 6PM.    I tried restoring it once (full restore) to check and see if everything was in place and the backup is fine.  Everything was fine.  The database was restored and came back up online. 

   Then I went ahead and made the following changes. All these changes were made after 7PM the same day.   Tried Deleting  the 'USER' tablespace.  As the user tablespace had materialized views,  it wouldn't let me delete.  So I deleted the default database owner and that way it removed all the objects and cleared up the tablespace.   Then I deleted the 'USER' tablespace.   I recreated the user itself again.  The user tablespace had 5 data files of size 8GB each.  But the data in each file is only about 2 - 3GB.  So I recreated the 'USER' tablespace with 5 files of 4 GIG each with the same name. 

    Thats' about the only change I did.  So now I ran a point in time restore and getting the following error:

Name : Recover0347
Target Name : XDB.aznet.com
Target Type : Database
Node Name : dbssrv01
Status : FAILED
Timestamp : Mar 08 2004  07:57:12 PM

Job Output:

Recovery Manager: Release 9.2.0.4.0 - 64bit Production

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

RMAN>
connected to target database: XDB (DBID=2478653290)

RMAN>
connected to recovery catalog database

RMAN>
RMAN> run {
2> allocate channel Channel1 type disk format
2> '/opt/dbsdta32/rmanbackups/XDB/b_%u_%p_%c';
3> set until time 'Mar 8 2004 18:0:4';
4> restore ( database);
5> set until time 'Mar 8 2004 18:0:4';
6> recover  database ;
7>
8> }
starting full resync of recovery catalog
full resync complete
allocated channel: Channel1
channel Channel1: sid=14 devtype=DISK

executing command: SET until clause

Starting restore at MAR 08 2004 18:59:53

datafile 5 not processed because file is offline
channel Channel1: starting datafile backupset restore
channel Channel1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /opt/dbsdta02/XDB/system01.dbf restoring datafile 00002 to /opt/dbsdta02/XDB/undotbs01.dbf restoring datafile 00003 to /opt/dbsdta02/XDB/cwmlite01.dbf restoring datafile 00004 to /opt/dbsdta02/XDB/drsys01.dbf restoring datafile 00006 to /opt/dbsdta02/XDB/XDBIDX1.dbf restoring datafile 00007 to /opt/dbsdta02/XDB/odm01.dbf restoring datafile 00008 to /opt/dbsdta12/XDB/tools01.dbf restoring datafile 00009 to /opt/dbsdta02/XDB/USERS.dbf restoring datafile 00010 to /opt/dbsdta02/XDB/xdb01.dbf restoring datafile 00011 to /opt/dbsdta22/XDB/XDBUser1.dbf restoring datafile 00012 to /opt/dbsdta02/XDB/XDBIDX2.dbf restoring datafile 00017 to /opt/dbsdta02/XDB/XDBIDX3.dbf channel Channel1: restored backup piece 1 piece handle=/opt/dbsdta32/rmanbackups/XDB/b_1vfg5dcg_1_1 tag=TAG20040308T154727 params=NULL channel Channel1: restore complete channel Channel1: starting datafile backupset restore channel Channel1: specifying datafile(s) to restore from backup set restoring datafile 00013 to /opt/dbsdta22/XDB/XDBUser2.dbf channel Channel1: restored backup piece 1 piece handle=/opt/dbsdta32/rmanbackups/XDB/b_20fg5enq_1_1 tag=TAG20040308T154727 params=NULL channel Channel1: restore complete channel Channel1: starting datafile backupset restore channel Channel1: specifying datafile(s) to restore from backup set restoring datafile 00014 to /opt/dbsdta22/XDB/XDBUser3.dbf channel Channel1: restored backup piece 1 piece handle=/opt/dbsdta32/rmanbackups/XDB/b_21fg5f7t_1_1 tag=TAG20040308T154727 params=NULL channel Channel1: restore complete channel Channel1: starting datafile backupset restore channel Channel1: specifying datafile(s) to restore from backup set restoring datafile 00015 to /opt/dbsdta22/XDB/XDBUser4.dbf channel Channel1: restored backup piece 1 piece handle=/opt/dbsdta32/rmanbackups/XDB/b_22fg5fo0_1_1 tag=TAG20040308T154727 params=NULL channel Channel1: restore complete channel Channel1: starting datafile backupset restore channel Channel1: specifying datafile(s) to restore from backup set restoring datafile 00016 to /opt/dbsdta22/XDB/XDBUser5.dbf channel Channel1: restored backup piece 1 piece handle=/opt/dbsdta32/rmanbackups/XDB/b_23fg5g84_1_1 tag=TAG20040308T154727 params=NULL channel Channel1: restore complete

Finished restore at MAR 08 2004 19:57:09

executing command: SET until clause

Starting recover at MAR 08 2004 19:57:09
datafile 5 not processed because file is offline

datafile 11 must be restored
a backup controlfile must be restored to recover datafile 11 datafile 13 must be restored a backup controlfile must be restored to recover datafile 13 datafile 14 must be restored a backup controlfile must be restored to recover datafile 14 datafile 15 must be restored a backup controlfile must be restored to recover datafile 15 datafile 16 must be restored a backup controlfile must be restored to recover datafile 16 released channel: Channel1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/08/2004 19:57:10
RMAN-06068: recovery aborted because of missing datafiles
 
RMAN>
RMAN> **end-of-file**

RMAN>

Recovery Manager complete.

Thanks.
More Info. [message #60817 is a reply to message #60814] Mon, 08 March 2004 20:14 Go to previous messageGo to next message
Roy
Messages: 30
Registered: May 2002
Member
Don't worry about the datafile # 5 error message, because the file was already offline even before the backup (not used). It belongs to the example database.

However, I think I'm having a timing problem here on the point-in-time. Could you please identify.

Full Backup Started at: Mar 08 2004. 3:46 PM.
Full Backup completed at: Mar 08 2004. 4:45 PM
Test Recovery (full database recovery) started at: Mar 08 2004 4:54pm
Test Recovery (full databaase recovery) completed on: Mar 08 2004 5:54pm.

I tried giving a point in time recovery for Mar 08 2004. 3.00pm and it failed.
Tried a point in time recovery for 5:00pm, 6:00pm, 7:00pm - all failed.

I'm currently trying a point in time recovery for 4:48pm and hopefully will work because its the time between the full backup completion and the first recovery test.

BTW, Is there any way that we can create a new database and make this backup work???
sorry everything failed.help please! [message #60818 is a reply to message #60817] Mon, 08 March 2004 20:42 Go to previous messageGo to next message
Roy
Messages: 30
Registered: May 2002
Member
Even the 4:48pm recovery I tried failed.

I'm not sure what the problem is.
Re: sorry everything failed.help please! [message #60832 is a reply to message #60818] Tue, 09 March 2004 06:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
you have a backup at Time t1. 
You did a restore at time t2.
did u ever reset the catalog or re-register the catalog user to catalog?
if so, YOUR database incarnation is changed...
first check whether your incarnation is changed.
and also check your resetlogs time..

 > select * from rc_database_incarnation;

    DB_KEY       DBID  DBINC_KEY NAME     RESETLOGS_CHANGE# RESETLOGS CUR PARENT_DBINC_KEY
---------- ---------- ---------- -------- ----------------- --------- --- ----------------
      9944  907585623       9945 LAWP                     1 13-DEC-03 YES

-----------------------------------------------------------------------------------------------

How did u take your backup?

did u make 

>>backup database plus archivelog

Re: sorry everything failed.help please! [message #60833 is a reply to message #60832] Tue, 09 March 2004 08:32 Go to previous messageGo to next message
Roy
Messages: 30
Registered: May 2002
Member
Thanks. The database incarnation is not changed.
I did not reset the catalog or re-register the catlog user to catalog.
Here is the seelect * from rc_database_incarnation;

DB Key Inc Key DB Name DB ID CUR ResetSCN Reset Time
------- ------- -------- ---------------- --- ---------
1653 1654 XDB 2478653290 YES 162043 04-SEP-03

I used OEM Backup with RMAN/REcovery catalog database to do the backup.

The backup was a fullbackup (offline) with archivelogs.

What happend was after the backup, I did a immediate restore. It worked. Major changes (changes to the XDB default data tablespace filesize etc., they were removed and recreated) were made to the database structure. Things didn't work. So I wanted to go back to the backup. But it doesn't let me do so.
I know after the full restore, there was no backup done again (all in less than 10 mins).

I am even ready to restore it to a new database. I have older control files available too.
Re: sorry everything failed.help please! [message #60835 is a reply to message #60833] Tue, 09 March 2004 09:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
# I dont know about RMAN backups with OEM.
# In command line, you need to mention RESTORE CONTROLFILE as shown
# IF there is any option to do the same in OEM please try that
# in command line, i would do like this

run {
set until time "to_date('$dat','MON DD YYYY HH24:MI:SS')";
restore controlfile;
restore database;
sql "alter database mount";
recover database;
sql "alter database open resetlogs"; ###if you do this step, incarnation will be changed.
}
Re: sorry everything failed.help please! [message #60841 is a reply to message #60835] Tue, 09 March 2004 12:36 Go to previous message
Roy
Messages: 30
Registered: May 2002
Member
Thanks. I got some support from Oracle, and finally we recovered it. Ultimately we used the controlfile from the backup (initially we tried using it and it didn't work, but latet it did!!!). However the restore for some reason wouldn't recover the datafiles properly. So we had to try ove rand over again, and finally deleted the files and then restored them.
All in all, we had so much confusion,and still can't figure out most of the causes.

I will have to try to practice restoring on a different database/host and see what goes on.

Thanks much
Previous Topic: Backup and Recovery
Next Topic: Delete objects in RMAN
Goto Forum:
  


Current Time: Sat Nov 23 19:34:07 CST 2024