Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Recover database using cold backup
mandrake
post Dec 7 2007, 01:47 PM
Post #1


Member
**

Group: Members
Posts: 10
Joined: 7-December 07
Member No.: 14,864



Hello Everyone,
I have an oracle 8i recovery scenario.

I have database cold backups of MONDAY and WEDNESDAY evenings. backup contains controlfiles, data files, redofiles

Database runs in Archive log mode and all archives of the month are available.

Hard disk crashed on SATURDAY

all archive logs are still available at different location

Controlfiles are not backed up to trace or filename or automatically means no backup control file (except ones in cold backups described above)

restoring all files from cold backup of either MONDAY or WEDNESDAY (I recovered to wednesday) leaves nothing to recover. Querying V$RECOVER_FILE returns nothing.

all log sequences higher to the number nnn displayed as result of ARCHIVE LOG LIST are present at correct location in correct format

My question is how do i ROLL Forward the Database from WEDNESDAY to SATURDAY in the above scenario.

Urgent help is sought and quick response in steps to recover will be highly appreciated
thanks
Go to the top of the page
 
+Quote Post
Starikovsky
post Dec 7 2007, 02:17 PM
Post #2


Advanced Member
***

Group: Members
Posts: 119
Joined: 13-November 07
Member No.: 13,939



You can check MetaLink Note#271916.1

Basically:
Restore all datafiles, controlfiles and archivelogs to expected locations.
startup mount
recover database using backup controlfile until cancel;
Apply your redologs and then CANCEL
alter database open resetlogs;
shutdown immediate;
startup
Go to the top of the page
 
+Quote Post
mandrake
post Dec 7 2007, 02:42 PM
Post #3


Member
**

Group: Members
Posts: 10
Joined: 7-December 07
Member No.: 14,864



Thanks That was quick but when you say
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

I REPEAT i have never used
ALTER DATABASE CREATE BACKUP CONTROLFILE TO TRACE or to filename :-(
so which backup file will be used for recovery
Go to the top of the page
 
+Quote Post
Starikovsky
post Dec 7 2007, 02:51 PM
Post #4


Advanced Member
***

Group: Members
Posts: 119
Joined: 13-November 07
Member No.: 13,939



the only one you have, the one in your Cold Backup.
Go to the top of the page
 
+Quote Post
mandrake
post Dec 7 2007, 02:52 PM
Post #5


Member
**

Group: Members
Posts: 10
Joined: 7-December 07
Member No.: 14,864



Can i do this

1. Restore database files from cold backup of Monday.
2. MOUNT DATABASE
3. Run ALTER DATABASE CREATE BACKUP CONTROLFILE "/backup/backupctlfile.ctl" and save this file seperately.
4. Dismount database and delete all datafiles (all).
5. Restore database files from cold backup of Wednesday.
6. Copy backupctlfile.ctl to directory where other controlfiles (of wednesday) are located .
7. Now that i have a backup controlfile from Monday while my datafiles are of Wednesday i will run
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL like you advised
8. Is my approach correct
Go to the top of the page
 
+Quote Post
Starikovsky
post Dec 7 2007, 02:57 PM
Post #6


Advanced Member
***

Group: Members
Posts: 119
Joined: 13-November 07
Member No.: 13,939



as far as I can see your approach would force you to restore your database twice and apply two extra days of archive logs.
Go to the top of the page
 
+Quote Post
mandrake
post Dec 7 2007, 03:07 PM
Post #7


Member
**

Group: Members
Posts: 10
Joined: 7-December 07
Member No.: 14,864



i proposed this approach because did the following
1. Restore database files from wednesday backup.

2. RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL.

3. The archive file which oracle asked me to provide for recovery is of july and i dont have such old archives

4. this forces me to believe that there is some backup controlfile existing (althought i couldnot locate it)
which deserves recovery from july which i dont have.

5. therefore the deliberately created backup file although i wonder that it may again take me at para 3.
will do it with crossed fingers
6. whats your take on the the create controlfile option and rolling forward from wednesday backup

P.S. I am grateful to you for the the promptness you have shown and the time you are giving to me http://dba.ipbhost.com/style_images/1/fold...icons/icon1.gif
Go to the top of the page
 
+Quote Post
mandrake
post Dec 7 2007, 03:37 PM
Post #8


Member
**

Group: Members
Posts: 10
Joined: 7-December 07
Member No.: 14,864



Still waiting for your reply to why i am being asked for july archive when my controlfile are of monday. Which backup controlfile is my database reading from and how can i change it...
even as given in the metalink note you suggested the first file i am being asked irchive file of july. How is it when my controlfile is of this MONDAY
Go to the top of the page
 
+Quote Post
jgarry
post Dec 7 2007, 06:01 PM
Post #9


Member
**

Group: Members
Posts: 10
Joined: 28-November 07
From: Just outside of beautiful Vista, CA
Member No.: 14,553



QUOTE (mandrake @ Dec 7 2007, 12:38 PM) *
Still waiting for your reply to why i am being asked for july archive when my controlfile are of monday. Which backup controlfile is my database reading from and how can i change it...
even as given in the metalink note you suggested the first file i am being asked irchive file of july. How is it when my controlfile is of this MONDAY


This can happen if you have a read only tablespace or sometimes if you have an offlined data file. desc v$datafile to see what information you can get.

If this is the case, be sure you have a copy of the offending data files, startup mount, then alter database datafile 'whatever' offline drop

This happens because using the backup controlfile syntax, the recovery ignores what the control file says the current SCN's in each data file should be, in favor of what each data file says it should be. So when it sees that it is trying to bring everything up to what the control file says should be current, the offline or read-only datafile says the SCN is very old, and the recovery asks for logs that will bring it up to be current. If you actually have all of those logs, you would probably run through them all, just so the recovery can mark the datafile with a current SCN.

Or something like that.

So, if you have a read only tablespace you can get it back from when you originally backed it up, right?


--------------------
See your DBA? Larry sez we don't need those any more!
Go to the top of the page
 
+Quote Post
mandrake
post Dec 8 2007, 11:04 PM
Post #10


Member
**

Group: Members
Posts: 10
Joined: 7-December 07
Member No.: 14,864



thank you Starikovsky and jgarry for your responses.

Starikovsky i could make progress and was able to roll my database forward. but i have another prob now
i get in the following in the middle of recovery.

ORA-27046
file size is not a multiple of logical block size

i run 8i on Aix 4.3 and have copied (ftp) all my archives from the backup machine to recovered machine. The problem is that archive file in backup location show correct size i.e. 524800 which is a multiple of 512 but when ftpd to recovered machine the filesize becomes 524799. this happens only for one file all others are copied in correct sizes.

Can you tell me what the problem may be and the solution .

thanks in advance
Go to the top of the page
 
+Quote Post
Starikovsky
post Dec 9 2007, 08:32 AM
Post #11


Advanced Member
***

Group: Members
Posts: 119
Joined: 13-November 07
Member No.: 13,939



Glad you are making progress.

May be FTP failed during process;
have you tried to FTP it again?
have you tried to copy the offending file on the same box? does the size stays at 524800?
Go to the top of the page
 
+Quote Post
mandrake
post Dec 9 2007, 11:34 AM
Post #12


Member
**

Group: Members
Posts: 10
Joined: 7-December 07
Member No.: 14,864



Copy again yes i tried that....
on the same box will try that too
but i experinced another thing which i would like to share
i had two backups as i told you of Monday and wednes day
wednesday db when recovered i did first (that was before your guidance came in)

SQL>RECOVER DATABASE
Media recovery complete.

Now on your advise i did
SQL>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

the first archive log i am prompted for was of july (sequence was 1) which i didnt have

so i had to use the older backup of Monday to proceed ........ in which i made progress and got stuck in middle with file size issue.
anyway will start fresh tomorrow and tell you the result
Go to the top of the page
 
+Quote Post
dalu
post Sep 5 2011, 05:21 AM
Post #13


Newbie
*

Group: Members
Posts: 9
Joined: 5-January 11
Member No.: 44,475



hi all ,
it is too late but hope this will help..

issue the command to start the recovery and apply all the archivelog files that are newer than any of the control or datafiles that we restored.


SQL> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ORA-00279: change 216252 generated at 04/09/99 16:39:56 needed for thread 1
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC85.1
ORA-00280: change 216252 for thread 1 is in sequence #85

SQL> alter database recover continue default;
alter database recover continue default

after applying all archive till present time .
Now we stop the recovery and open the database:


SQL> alter database recover cancel;
alter database recover cancel
Statement processed.

SQL> alter database open resetlogs;
Statement processed.



Go to the top of the page
 
+Quote Post
dalu
post Sep 5 2011, 05:22 AM
Post #14


Newbie
*

Group: Members
Posts: 9
Joined: 5-January 11
Member No.: 44,475



QUOTE (Starikovsky @ Dec 8 2007, 12:47 AM) *
You can check MetaLink Note#271916.1

Basically:
Restore all datafiles, controlfiles and archivelogs to expected locations.
startup mount
recover database using backup controlfile until cancel;
Apply your redologs and then CANCEL
alter database open resetlogs;
shutdown immediate;
startup

Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 17th April 2014 - 02:05 AM