RE: Restore problem

From: Robert Freeman <robertgfreeman_at_yahoo.com>
Date: Sun, 6 May 2007 19:35:50 -0600
Message-ID: <KEEDIPJOJLCHPPAIDPDOIEPFDOAA.robertgfreeman@yahoo.com>

I don't have the first email of this thread, but it sounds like you want to restore a datafile or a tablespace on Windows. Also, it does not mention which version of the database is being used. I assume the datafile is still in place, because of the locking error.

That being said, I tried a tablespace recover on XP using Oracle 10gR2, here are the commands and the result was successful as seen here:

RMAN> sql "alter tablespace test offline"; sql statement: alter tablespace test offline

RMAN> restore tablespace 'TEST';

Starting restore at 06-MAY-07
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00006 to D:\ORACLE\ORADATA\ROB10GR2\TEST.DBF channel ORA_DISK_1: reading from backup piece C:\ORACLE\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ROB10GR2\BACKUPSET\2007_ 05_06\O1_MF_NNNDF_TAG20070506T191404_33WZBYMJ_.BKP channel ORA_DISK_1: restored backup piece 1 piece
handle=C:\ORACLE\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ROB10GR2\BACKUPSE T\2007_05_06\O1_MF_NNNDF_TAG20070506T191404_33WZBYMJ_.BKP tag=TAG20070506T191404
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36 Finished restore at 06-MAY-07

RMAN> recover tablespace 'TEST';

Starting recover at 06-MAY-07
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:11

Finished recover at 06-MAY-07

RMAN> sql 'alter tablespace test online';

sql statement: alter tablespace test online

RMAN> Robert G. Freeman
Oracle Consultant/DBA/Author
Principal Engineer/Team Manager
The Church of Jesus Christ of Latter-Day Saints Father of Five, Husband of One,
Author of various geeky computer titles
from Osborne/McGraw Hill (Oracle Press)
Sig V1.1

  -----Original Message-----
  From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jared Still   Sent: Sunday, May 06, 2007 3:11 PM
  To: wbfergus_at_gmail.com
  Cc: oracle-l_at_freelists.org
  Subject: Re: Restore problem

On 5/4/07, Bill Ferguson <wbfergus_at_gmail.com> wrote: Hi all,

      channel ORA_DISK_1: restored backup piece 14
      piece handle=E:\BACKUP\BACKED UP DATA\AJIGLMEM_14_1.BAK
tag=TAG20070501T220157
      channel ORA_DISK_1: reading from backup piece E:\BACKUP\BACKED UP
DATA\AJIGLMEM_15_1.BAK
      ORA-19870: error reading backup piece E:\BACKUP\BACKED UP

DATA\AJIGLMEM_15_1.BAK ORA-19504: failed to create file
"E:\ORACLE\DATAFILES\NGDB_DATA_06.DBF"

      ORA-27086: unable to lock file - already in use
      OSD-00002: additional error information
      O/S-Error: (OS 32) The process cannot access the file because it is
being used b
      y another process.
      failover to previous backup

Hi Bill,

  Taking a tablespace offline on windows does not seem to remove the lock that Oracle has on it.
  When a file is open, the process has a lock (don't know the windows technical term for the
  type of lock)

Just tested this on my laptop: took 10g tablespace EXAMPLE offline, and the handle utility
(from Sys Internals toolkit) shows that the EXAMPLE files are still open by Oracle:

oracle.exe pid: 2848 NT AUTHORITY\SYSTEM

      c: File          C:\WINDOWS\SYSTEM32
     d8: Section       \BaseNamedObjects\*oraspawn_buffer_ts50*
     f4: Section       \BaseNamedObjects\ShimSharedMemory


  ...

    624: File          C:\oracle\product\10.1.0\oradata\ts50\USERS01.DBF
    628: File          C:\oracle\product\10.1.0\oradata\ts50\EXAMPLE01.DBF
    62c: File          C:\oracle\product\10.1.0\oradata\ts50\EXAMPLE01.DBF


  ...

    664: File          C:\oracle\product\10.1.0\oradata\ts50\SYSTEM01.DBF
    668: File          C:\oracle\product\10.1.0\oradata\ts50\SYSTEM01.DBF
    66c: File          C:\oracle\product\10.1.0\oradata\ts50\UNDOTBS01.DBF

...

I've never restored a tablespace on Windows, but it appears that your procedure may need to be modified a bit.

Have you tried restoring the tablespace with Oracle in mount mode?

Our resident RMAN expert (Robert Freeman) may know the answer to this.

  --
  Jared Still
  Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Sun May 06 2007 - 20:35:50 CDT