Nid revert

NID-00111: Oracle error reported from target database while executing

begin dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged, :nmchged); end;

ORA-01116: error in opening database file /gp_utyeb05/d001/temp01.dbf

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6185

ORA-06512: at line 1

Change of database ID failed.

Must finish change or REVERT changes before attempting any database operation.

DBNEWID - Completed with errors.

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7
This problem can occur on any platform.

Symptoms

NID failing due to missing tempfiles:
ORA-01116: error in opening database file /ora/u10/oradata/eu0289p/temp01.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6164
ORA-06512: at line 1

Cause

Bug 5861994

Issue reported matches those of bug 5861994, thus justifies cause. This bug is fixed in Oracle 11.2.

Solution

To implement the solution, please execute the following steps:

1. While database is mounted, drop the tempfiles:
SQL> alter database tempfile 1 drop;

NOTE: this workaround may give error ora-19951 "cannot modify control file until DBNEWID is completed"

2. Recreate the controlfile:
- SQL> alter database backup controlfile to trace resetlogs;

NOTE: Depending on how much NID had accomplished before failing, it may be required to recreate the controlfile with "set database" to new database name.

- SQL> startup nomount;
- SQL> create controlfile.....
- SQL> alter database open resetlogs;

OR

You can also request the backport patch for this bug to prevent this error again.

nid TARGET=SYS/oracle REVERT=YES LOGFILE=$HOME/nid.log