Friday, August 3, 2018

DBUA 12.2 failed with ORA-01157 at preupgrade tool execution

DBUA got following errors at 'Prerequisite Check' (perupgrade tool execution) while upgrading Oracle database to
Error in preupgrade tool execution. ERROR - ERROR - Unable to run sqlplus due to: ORA-06512: at "SYS.DBMS_PREUP",
line 4386 ORA-06512: at "SYS.DBMS_PREUP", line 9108 ORA-06512: at line 8 declare * ERROR at line 1: ORA-01157:
cannot identify/lock data file 202 - see DBWR trace file ORA-01110: data file 202: '+DATA' ORA-06512: at line 56
if you are executing the preupgrade tool from a standby database make sure the preupgrade tool has been executed
in the primary database If you are executing the tool from Windows, please make sure you have given the appropriate
permissions to the Oracle binaries owner. (more details)

The preupgrade tool (running preupgrade_driver.sql) cannot identify data file named '+DATA', find out the file as following,
SQL> select  FILE#,TS#,NAME from v$datafile;
     FILE#        TS# NAME
---------- ---------- --------------------------------------------------
         1          0 +DATA/db01/datafile/system.996.983124867
         2          1 +DATA/db01/datafile/sysaux.992.983124655
         3          2 +DATA/db01/datafile/undotbs1.993.983124731
         4          4 +DATA/db01/datafile/users.995.983124831

SQL> select FILE#,TS#,NAME from v$tempfile;
     FILE#        TS# NAME
---------- ---------- --------------------------------------------------
         1          3 +DATA/db01/tempfile/temp.1009.983125197
         2          3 +DATA
SQL> select * from v$tablespace;
       TS# NAME                                               INC BIG FLA ENC
---------- -------------------------------------------------- --- --- --- ---
         0 SYSTEM                                             YES NO  YES
         3 TEMP                                               NO  NO  YES
         1 SYSAUX                                             YES NO  YES
         2 UNDOTBS1                                           YES NO  YES
         4 USERS                                              YES NO  YES
The temporary file (file# 2) is missing (file name +DATA is dummy) which belongs to tablespace TEMP. Since it is temporary file, we can drop it safely,
SQL> alter tablespace temp drop tempfile 2;
Tablespace altered.
SQL> select FILE#,TS#,NAME from v$tempfile;
     FILE#        TS# NAME
---------- ---------- --------------------------------------------------
         1          3 +DATA/apextmp/tempfile/temp.1009.983125197
Click 'Check Again' in DBUA, the error is gone.

No comments: