Restore Oracle READ-ONLY tablespace

[William_Potter]

Backup Exec 11.d rev 6235

Windows 2003 SP2

Oracle 10g v 10.2.0.2

I am testing the disaster recovery of an Oracle database which includes a read-only tablespace.

I have taken an offline backup of the database and when examining the logfile, I can see that all tablespaces have been backed up.

I have then taken an online backup of the database and when examining the logfile, I can again see that all tablespaces have been backed up.

I have then removed all the Oracle datafiles from the datafile folder.

I have prepared a restore job to restore the complete database from the latest control files and in the Oracle options I have selected "Restore read_only files if they are not current". When I run this job, it fails with the error message in the RMAN log "no backup or copy of datafile xy found to recover".

If I restore the database without checking the option "Restore read_only files if they are not current", then the database is restored without error but also without the read-only tablespace. I can then copy in the missing datafile and perform the alter database open resetlogs command.

Are there any special steps I need to take when backing up the read-only tablespace to ensure I can restore it from the backup?

[Patty_McGowan]

William,

I will look into this, but I have some questions for you.

BE 6235, what HotFixes do you have?

Also are you using encryption or software compression?

Thanks

Patty

[William_Potter]

Prior to starting the testing on 19th April, I performed a successful live update so I'm assuming that I will have all hot-fixes that there are to have to that date. If you need a specific list of hot-fixes, I would appreciate some instruction on how to obtain this.

I am not using any encryption or software compression. Just trying to keep it plain vanilla until I succeed with a full restore.

On re-reading my post, I see that I did not make myself clear that the message no backup or copy of datafile xy found to recover refers to the read-only tablespace's datafile. I think you may have assumed this but I wanted to make it 100% clear.

Thanks for looking at this problem.

William Potter

[Patty_McGowan]

William,

If you go to help and about in Backup Exec and then installed updates.

It will give you a list of what is installed.

Thanks.

Patty

[Patty_McGowan]

William,

What tables are you restoring that are read-only?

Patty

[William_Potter]

Installed are

hotfix6

hotfix9

hotfix10

hotfix11

hotfix13

hotfix15

hotfix16

hotfix19

hotfix20

Thanks,

William Potter

[William_Potter]

Patty,

I'm sorry but your question has not used the correct jargon for the context therefore I am assuming that you mean, "What tablespaces are you restoring that are read-only?" Even then I have to confess I don't quite see the relevance of the question. RMAN, which is now the motor behind BUExec, only deals in databses and tablespaces.

I have a database with the usual Oracle tablespaces, SYSTEM; TEMPSEG; UNDOTBS01. In addition there are about 16 other tablespaces which hold application data. One of these is a small read-only tablespace with about 25Mb of data. This tablespace only has one datafile and it is the last in the list of datafiles. I have only performed complete database backups, and I wish to make a complete database restore.

Here is the RMAN script extracted from the online backup log

RUN {
ALLOCATE CHANNEL ch0 TYPE 'SBT_TAPE';
SEND 'BSA_SERVICE_HOST=172.28.97.188,NBBSA_TOTAL_STREAMS=1,NBBSA_JOB_COOKIE={33E4AB6E-7FB6-4DDD-BCD7-34C39AB33DF7},NBBSA_DB_DEVICE_NAME=Oracle-Win::\\hostname\databasename';
BACKUP INCREMENTAL LEVEL=0 FORMAT 'BE_U'
    DATABASE FORCE PLUS ARCHIVELOG FORCE DELETE INPUT;
BACKUP CURRENT CONTROLFILE FORMAT 'BE_U';
RELEASE CHANNEL ch0;
}

Here is the RMAN script extracted from the restore log

RUN {
ALLOCATE CHANNEL ch0 TYPE 'SBT_TAPE';
SEND 'BSA_SERVICE_HOST=172.28.97.188,NBBSA_TOTAL_STREAMS=1,NBBSA_JOB_COOKIE={0594FE13-3011-4187-812E-E7C3347E920C},NBBSA_DB_DEVICE_NAME=Oracle-Win::\\hostname\databasename';

RESTORE CONTROLFILE FROM 'BE_09ifivne_1_1';
ALTER DATABASE MOUNT;
RESTORE DATABASE CHECK READONLY;
RECOVER DATABASE;
RELEASE CHANNEL ch0;
alter database open resetlogs;
}

And last but not least, the error message from the restore job

database mounted
Starting restore at 20-APR-07
released channel: ch0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/20/2007 14:04:14
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 37 found to restore

I hope this makes the issue a bit clearer.

Thanks,

William POtter

[Patty_McGowan]

Yes,

Thank you very much William.

Let me do some research and I will post when I have some information for you.

Thanks

Patty

[William_Potter]

Is their any update on this?

I am beeing pressed to come to a conclusion on whether we can continue to recommend Backup Exec to our customers or whether I should evaluate another product. To give you a perspective, my company is indirectly responsible for about 250 sales of BackupExec with Oracle Agent per year. We are currently still recommending the use of BUExec 10d but that is becoming increasingly difficult to obtain.

[Patty_McGowan]

William,

At this time I do not know what is causing this issue.  I have done research and have not found any similar cases or anyone who has seen this before. 

I would need to gather logs and analyze the environment, this can be done by opening a support case.

It is very possible that a TSANet call may need to be opened with Oracle. 

This is not something we can do on the forum.

I strongly recommend opening up a support case.

Patty