Re: Database recovery from user error

The error message you are getting is a classic. It indicates that not all the data files are consistent one with another, not that there is anything intrinsically wrong with the SYSTEM datafile. It typically happens when you fail to restore all datafiles prior to an incomplete recovery.

It's hard to tell, but I think it's your methodology that's the problem here. I'm not sure what you mean by saying you have a 'script' for the recovery. You mean like a batch file? That's an extremely dodgy route to take if so.

For a start, your script says 'shutdown immediate' -and then appears to immediately begin the restoration of the data files. If this is automated, what's the check you perform to make sure that the shutdown immediate has completed successfully before you start tying to restore the files? If the shutdown hasn't completed, then quite possibly your script will attempt to copy a file over the top of a file that is in use... and that will fail, so the net result is that you haven't restored that file. Therefore the error message you get makes sense. I would use a shutdown abort instead.

I also don't see how the script can possibly work in an automated way when your line reads 'recover database until time xxx'... that will prompt you to apply redo, yet I don't see any of that happening, merely that the script then goes on to request an open resetlogs. If you are truly trying to automate this process (and automating recoveries is a distinctly daft thing to do), then you need the 'recover automatic database until time xxx' syntax.

The other thing that's wrong (though not related to your specific problem) is that I see no indication anywhere that you have taken a cold backup of the existing control files and online redo logs before attempting the incomplete recovery. That's serious, because it's only by copying those files somewhere safe (not your regular backup directory) that you can repeat an incomplete recovery if it, for some reason, fails to work, or fails to result in the recovery of the table(s) you are trying to get back.

But I repeat: the major problem here is attempting to automate a recovery. Recovery scenarios need to be diagnosed carefully, and handled carefully. Particularly since no two recovery scenarios are the same. A batch script just doesn't cut it.

Regards
HJR <rsusanto_at_atlas-sp.com> wrote in message news:adpqj2$hnn$1_at_news.netmar.com...
> Hi all,
>
> I'm trying to perform recovering the tables after deleting them. Before i
> deleting them, i perform hot backup, after deleting them i try to recover
but
> failed. Sometime i can do it successfully and sometime is not, i don't
know
> the actually what is going on.
>
> This is my script when i perfom hot backup:
> alter tablespace appdata begin backup;
> host copy g:\data\v817\disk1\appdata01.dbf y:\data\v817-rs\hotbackup-1\
> host copy g:\data\v817\disk2\appdata02.dbf y:\data\v817-rs\hotbackup-1\
> alter tablespace appdata end backup;
> alter tablespace indx begin backup;
> host copy d:\oracle\oradata\v817\indx01.dbf y:\data\v817-rs\hotbackup-1\
> alter tablespace indx end backup;
> alter tablespace rbs begin backup;
> host copy d:\oracle\oradata\v817\rbs01.dbf y:\data\v817-rs\hotbackup-1\
> alter tablespace rbs end backup;
> alter tablespace system begin backup;
> host copy d:\oracle\oradata\v817\system01.dbf y:\data\v817-rs\hotbackup-1\
> alter tablespace system end backup;
> alter tablespace temp begin backup;
> host copy d:\oracle\oradata\v817\temp01.dbf y:\data\v817-rs\hotbackup-1\
> alter tablespace temp end backup;
> alter tablespace tools begin backup;
> host copy d:\oracle\oradata\v817\tools01.dbf y:\data\v817-rs\hotbackup-1\
> alter tablespace tools end backup;
> alter tablespace users begin backup;
> host copy d:\oracle\oradata\v817\users01.dbf y:\data\v817-rs\hotbackup-1\
> alter tablespace users end backup;
> alter system switch logfile;
> alter database backup controlfile to 'y:\data\v817-rs\hotbackup-1\';
> host copy d:\oracle\admin\v817\pfile\initv817.ora
> y:\data\v817-rs\hotbackup-1\
> host copy d:\oracle\ora81\database\pwdv817.ora
y:\data\v817-rs\hotbackup-1\
> exit
>
> This is my script to recover the database:
> shutdown immediate
> host copy y:\data\v817-rs\hotbackup-1\APPDATA01.DBF G:\DATA\V817\DISK1\
> host copy y:\data\v817-rs\hotbackup-1\APPDATA02.DBF G:\DATA\V817\DISK2\
> host copy y:\data\v817-rs\hotbackup-1\INDX01.DBF D:\ORACLE\ORADATA\V817\
> host copy y:\data\v817-rs\hotbackup-1\RBS01.DBF D:\ORACLE\ORADATA\V817\
> host copy y:\data\v817-rs\hotbackup-1\SYSTEM01.DBF D:\ORACLE\ORADATA\V817\
> host copy y:\data\v817-rs\hotbackup-1\TEMP01.DBF D:\ORACLE\ORADATA\V817\
> host copy y:\data\v817-rs\hotbackup-1\TOOLS01.DBF D:\ORACLE\ORADATA\V817\
> host copy y:\data\v817-rs\hotbackup-1\USERS01.DBF D:\ORACLE\ORADATA\V817\
> startup mount
> recover database until time '07-jun-2002 09:30:00'
> alter database open resetlogs;
> exit
>
> i got error message when i tried to run 'recover database until time' and
the
> error message is
> ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error
> below
> ORA-01195: online backup of file 1 needs more recovery to be consistent
> ORA-01110: data file 1: 'D:\ORACLE\ORADATA\V817\SYSTEM01.DBF'
>
> Although i try to recover all of the datafiles that got error message when
i
> try to perform 'ALTER DATABASE OPEN RESETLOGS' like
> RECOVER DATAFILE 'D:\ORACLE\ORADATA\V817\SYSTEM01.DBF',
> RECOVER DATAFILE 'D:\ORACLE\ORADATA\V817\TOOLS01.DBF', and so on.
> But I still can't get the tables back.
>
> Please help me.
>
> Thanks in advance,
> Rudy Susanto
>
> ----- Posted via NewsOne.Net: Free (anonymous) Usenet News via the
eb -----
> http://newsone.net/ -- Free reading and anonymous posting to 60,000+
groups
> NewsOne.Net prohibits users from posting spam. If this or other posts
> made through NewsOne.Net violate posting guidelines, email
abuse_at_newsone.net Received on Fri Jun 07 2002 - 17:08:21 CDT