Re: Recovery Scenarios - Help !!
- From: Peter Gram <peter.gram@xxxxxxxxxxxx>
- To: premj@xxxxxxxxxxxxxx
- Date: Mon, 29 Nov 2004 10:00:31 +0100
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: quoted-printable
Prem
Comment inline
Prem Khanna J wrote:
>Friends,
>I have got some questions regarding recovery.
>Let us assume i have full backup of the database
>with all archived logs.It's oracle 9.2.0.4/Aix5L.
>
>Case 1
>------
>I have lost my undo tablespace during a batch job=20
>which does lot of update.
> - What will be the status of my batch job now ?
> - How will my transaction be rolled back if=20
> the undo tablespace is lost ?
>
> =20
>
If you lose the undo tablespace the instance will crash, after the=20
recovery of the undo tablespace
(asuming that you hae a backup and can appliy archive and redologs) when =
the database starts it
will do instance recovery and rollback all uncommited transactions.
>Case 2
>------
>I have lost my temp tablespace during a batch job=20
>which does lot of sort/update.
> - What will be the status of my batch job and=20
> how do i recover by temp tablespace now ?
> - i can drop/create new temp tablespace, but which=20
> process takes care of rolling back the
> transaction and when it happens ?
> is it PMON that does the job ?
>
> =20
>
This depends on the way you have written the batch program :
1) If the batch program detects the problem and issues a rollback of =
the last uncommitted transaction and stops you don't
have a problem :-)
2) If the batch program just quits on error then Pmon will do the=20
rollback of the uncommitted transaction.
>Case 3
>------
>I have lost an un-archived online redo log file.The proper=20
>recovery procedure is :
> - restore all datafiles from backup
> - mount the database
> - recover until cancel
> - open resetlogs
>
>Without restoring datafiles from backup,Can i do this :=20
> - mount the database
> - recover until cancel
> - open resetlogs
> =20
>
This depends on if the redolog is the the current redolog file or not :
Current redolog )
In this case the instance will crash. You will have to restore all=20
datafiles from a backup and do incomplete recovery up to the missing
redolog. (cancel, time or SCN based) and do a open resetlogs. The=20
database will then do instance recovery when you open the database
and rollback all uncommitted transaction. If you have written your batch =
program correct it start from a point that makes sense :-)
Not the current redolog)
This is big problem there are several ways the fix this problem one is=20
the use the "alter database CLEAR LOGFILE .."
command to fix the problem
>I have posted all these questions after RTFM and=20
>referring some books.Can someone throw some light on this ?=20
>
>Thanks in advance.
>
>Prem.
>--
>//www.freelists.org/webpage/oracle-l
>
> =20
>
--=20
Best regards / Venlig hilsen
/*Peter Gram*/
Miracle A/S <http://www.miracleas.dk>
Kratvej 2
DK - 2760 M=E5l=F8v
Cell: (+45) 2527 7107
Phone: (+45) 4466 8855
Fax: (+45) 4466 8856
Home: (+45) 3874 5696
Skype: peter_gram
Email: peter.gram@xxxxxxxxxxxx
22-24 Nov 2004 RMAN-kursus med Tuomas Pystynen=20
17-19 Jan 2005 Miracle Master Class with Tim Gorman=20
28 feb - 2.mar 2005 Database Master Class with Chris Date=20
2-4 May 2005 SQL Server Master Class with Kimberly Tripp=20
--
//www.freelists.org/webpage/oracle-l
Other related posts:
