oracle 7.3.4.3 and bcv - recover steps?

[ramaral]

Hi there,

I hope someone has some insight into an issue I am having with replicating an Oracle 7.3.4.3 database using BCV on HP's XP-256 SAN. I seem to have some trouble recovering the database on the target machine (the source is fine)

To start off with, I am replicating a production system to another machien for reporting purposes. Both machines have HP-UX 11 and Oracle 7.3.4.3.0 (I have verified this).

1) on the target machine, if I have a database up and running I shutdown it down. I remove the control files.
2) on the source machine, I place the production database in backup mode
3) I resync the disks between the two machines
4) I split the pair
5) I fsck the target disk to make sure it's fine
6) I mount the disk/filesystem
7) I take the source database out of backup mode
8) I check the SCN number from the source database (select first_change#-1 from v$log where sequence#=(select max(sequence#) from v$log))
9) I do a switch logfile statement 3 times (alter system switch logfile)
10) I backup the controlfile to trace to get my create control file scripts and I copy this to the target machine and modify it
11) I copy the archive logs to the target machine
12) I startup the target database in nomount
13) I run the create controlfile script from step 10
15) Oracle needs recovery at this point and I issue a backup controlfile to trace statement
14) I am now into recovering the database and I recover the database from until change using backup controlfile
15) logs get applied and I cancel this recovery then I issue a 'recover automatic until cancel using backup controlfile' statement and immediately cancel this recovery
16) do an alter database open resetlogs statement
17) database fails to open - file 1 (system datafile) needs more recovery

Is there some step that I am missing or do I have something out of order? I read in a thread on Technet Michael Ault's suggestion about using the _allow_resetlogs_corruption=true parameter in the init file but I would like to use this if there is nothing wrong with my steps or I have no other choice (I do realize that if I use this then Oracle will not support this database which I think is fine because it would only be a reporting database that gets refreshed daily anyway - it's not a production system).

If anyone can shed some light or suggest an extra step that would be great.

NOTE: I use this procedure on two other systems (Oracle 8.1.6.2 on HP-UX 11 and it works fine which suggests that I may be missing something).

Thanks everyone

Rui Amaral

 

[karluk]

As far as I can see, your problem must be happening in steps 14 & 15 when you are trying to recover the target database. You say that "logs get applied", but I have a feeling that this never actually happens. Probably in step 14 you are recovering to the SCN determined in step 8. Is that right? If so, I don't see why you have to cancel this recovery. Oracle should end the recovery all by itself, after applying enough logs to get to the specified SCN.

One of step 14 or 15 seems unnecessary. You start a second recovery, and then immediately cancel it without applying any logs at all. The situation after the cancel is exactly the same as before the recovery started.

I would definitely not use _allow_reset_logs_corruption=true. Your database is out of sync, and you need to find out why and fix the problem. It's probably true that a reporting database is less critical than the original production database, but your customers aren't going to be too happy if their reports don't run or have errors. Your goal, as DBA, is to provide a stable, reliable database, and this goal seems to be easily in reach. Don't give up now!

 

[ramaral]

Hi Karluk,

you are correct that in step 14 the database does go the scn # derived in step 8. And oracle tells me that recovery is complete. At this step I do an "alter database open resetlogs" and that fails. Then I go and do the second recovery and that fails. (This two step recovery works for me in another environment with HP-UX 11 and Oracle 8.1.6.2 -although the one step recovery does not work at all in either environments. Other DBA's and the Oracle Analysts on Metalink do suggest this 2 step recovery process as well - seems it happens to others).

I am wondering though if the the order of the tablespace begin backups and end backups scripts should be changed. In the begin backup script the system tablespace is the first to go into backup mode and in the end backup script the system tablespace is the first one to go out of backup mode. I wondering if I should change the end backup script to have the system tablespace be the last tablespace to go out of backup mode. It's just a thought - I don't think it really matters much because the redo logs should take care of the synchronization during the recovery but I am running against a brick wall at the moment.

What do you think?

 

[karluk]

Ah, I think I see the problem. The calculation in step 8 gives you the last SCN of the redo log that was active right before the current log. Your recovery misses all the changes that are recorded in the currently active log.

You should be able to solve this by reversing steps 8 and 9. Doing the log switches first will ensure that the current redo log is empty.

If this works, you should be able to eliminate the second recovery in step 15.

 

[ramaral]

Thanks. I will give that a try.