Issue: Unable to recover database from an online backup
We have a production Oracle 10 G database ; Solaris. An online backup is performed everyday (No offline backups as it a HA environment). We are trying to validate the online backup of the production database by restoring it onto another test oracle database.
- Alter database begin backup is issued in the production database
- The oracle database disks are split and a copy is mounted to another server. This is the location from where the backup is taken
- Alter database endbackup is issued in the production database. (As a split copy is used for the database backup, the time frame between 'begin backup and 'end backup' is only about 3 mins.)
- We wiped out the existing data from the test database. Basically, erased the datafiles from the filesystem.
- Restored the online backup of the production database to the test database
- Restored the control file
- Restored the archived logs that were generated from the time the online backup was started until the backup ended
- Take the database off from the backup mode (As the 'end backup' is issued only in the production database, the restored files are in the backup mode)
- Tried to recover the database using 'recover database until cancel' . The result is that we are continuously being asked to supply the redo logs and when we get an error that the database needs more recovery before it can be opened if we cancel the recovery process.
The SCN numbers from the V$datafile and V$datafile_header is higher than the SCN number in the V$database.
select checkpoint_change# from v$datafile;
select checkpoint_change# from v$datafile_header;
select checkpoint_change# from v$database;
The system SCN is lower to those in the v$datafile and v$datafile_header. Any ideas on where the problem could be? Thanks for your time
try using "recover database using backup controlfile until cancel;" pass cancel from where you dont have further archives , let me know if it ask for system file recovery or says "Media Recovery Complete" & provide o/p of below
select distinct checkpoint_change# from v$datafile_header;
select hxfil file_id, fhscn scn, fhthr thread, fhrba_seq sequence, fhsta status from x$kcvfh;
select distinct fhscn from x$kcvfh;
select hxfil file_id, fhtnm tablespace_name from x$kcvfh;
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set feedback on
set heading on
set pagesize 1000
set linesize 175
column checkpoint_change# format 999999999999999999999
select status, checkpoint_change#, fuzzy,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
group by status, checkpoint_change#, fuzzy, checkpoint_time
order by status, checkpoint_change#, fuzzy, checkpoint_time;
Thanks for the reply. We tried the recover using the backup controlfile but failed to open the database. My suspicion is largely on the system SCN. The test database had data in it before we wiped it off. So, I believe the test database had an SCN number at that point in time. We then restored the online backup from our production database and restored to the test database server. Now, these datafiles' SCN would be different to the SCN of the test database and hence the trouble in bringing the DB up. Pls correct me if I am wrong.
The output of the script is quiet lengthy. pls bear with me. Thanks Ajay.
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
The next archivelog sequence is 278471. We applied this log and we were prompted to enter the next sequence. We applied 278472.
select distinct status from v$backup;
We got the following error while we tried to recover using backup controlfile.
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/PR1/sapdata1/system_1/system.data1'
I am using sqlplus to recover the database. The test database had data in it but there was no activity in the database. The test database has an SCN #. This SCN #, lets call it as 100. Now, I restored the datafiles of a production database to the test database (after deleting all the datafiles in the test database). The SCN numbers of these datafiles, lets say is 500. As there is a mis-match between the database SCN (100) and the SCN of the datafiles (500), oracle concludes that the database requires a recovery to be consistent and therefore, prompts for the archive logs. We supplied all the archivelogs, even those that were generated way past the time the backup ended but unable to open the database. Is such a recovery ever possible?. I mean where the database SCN is lesser to that of the SCN of the datafiles?. Thanks much for your time.
We supplied the archive logs that were generated after the backup was completed and even those generated way past the time the backup ended but that doesn't help to open the database. Oracle continues to prompt for more archive logs and upon canceling the recovery, it warns that the database cannot be opened.
Seeing at ur previous post it seems the database is not using the fresh controlfile.
On ur production database issue the command
alter database backup controlfile to 'any location u want to give'
Now save this controlfile in ur clone db location as mentioned in control_files parameter in ur pfile. If there are two or three entries of control file entry then make the neccesary copies and save with the same name as in pfile. make a spfile from this pfile.
Now bounce the database.
Then startup mount
recover database using backup controlfile.
It will recover upto the archives which u have saved in the archive destination. And then it will ask for next sequence which is not present.
The control file which is being used is of the previous test database while the datafiles are belonging to other database. Also as per the error the the datafiles u copied were not in the begin backup mode while copying
Instead of breaking ur head on this i suggest u to take a fresh hot backup.
Instead of putting ur database in begin backup mode go for tablespace begin backup mode and copy the datafiles belonging to that tablespace. Once finished end backup mode of the tablespace.
Do this for all tablespaces.
Also take a binary backup of ur control file and if u want take a copy of ur production pfile to the clone site.