Auxiliary oracle db

Posted on 2009-04-22
Medium Priority
Last Modified: 2013-12-19
Hi all

I have a question in restore from user managed backups.
Recently a user made a wrong update to a prod table, the db does "user managed backups". So i am trying to do TSPITR manually to get teh table back following the steps provided in this link


we use oracle 9.2.06

When i try to do this part


I get the following error in sqlplus:

SQL> recover database until cancel using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01221: data file 1 is not the same file to a background process

alert log has this:
ORA-01186: file 1 failed verification tests
ORA-01221: data file 1 is not the same file to a background process

and the dbwr's trace has

ORA-01186: file 1 failed verification tests
ORA-01221: data file 1 is not the same file to a background process
ORA-01110: data file 1: 'orcl/aux/prod_system_01.dbf'
The path in V$datafile is the same place where the file exists. I have this specified in the init file using the db_filen_ame_convert.

Please let me know if you know how to get this working. Also if there are other ways to restore  from user managed bkups thats fine too.

Question by:san_jy
LVL 48

Accepted Solution

schwertner earned 1500 total points
ID: 24208459
But if you use user managed backup possibly it will be better
to use a normal backup (without RMAN, that adds additional level of complexity).



Do the 'oracle' user has access to the directory

ORA-01186: file string failed verification tests
Cause: The data file did not pass the checks to insure it is part of the database. See the accompanying error messages for the reason the verification failed.

Action: Make the correct file available to the database. Then, either open the database, or execute ALTER SYSTEM CHECK DATAFILES.
LVL 40

Expert Comment

ID: 24215200
What are the full paths? 'orcl/aux/' is a relative path, not a full path.

What does your db_filename_convert line look like?

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month15 days, 20 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question