Monday, July 18, 2016

ORA-39710 and ORA-00704

I tried to use dbua to upgrade a database from Oracle 11.2.0.4 to Oracle 12.1.0.2. Part way through, my PC lost connection with the UNIX server hosting the database. I tried to connect to the database but got an ORA-39710 so I forced the database to close with shutdown abort:

NLFINUT1 /export/home/oracle > sqlplus /

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 18 14:03:08 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE mode

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL>


A colleague restored the database for me. dbua had updated /var/opt/oracle/oratab to show the new database version so I changed it back to 11.2.0.4:

NLFINUT1:/oracle/app/oracle/product/11.2.0.4:N

However, I forgot to rerun . oraenv to pick up the old Oracle software so when I tried to open the database, I got an ORA-00704:

NLFINUT1 /export/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 18 14:13:38 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  650117120 bytes
Fixed Size                  2917400 bytes
Variable Size             230693864 bytes
Database Buffers          411041792 bytes
Redo Buffers                5464064 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
Process ID: 21064
Session ID: 212 Serial number: 60960

SQL>


I reran . oraenv then I was able to open the database:

NLFINUT1 /export/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 18 14:52:08 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  622338048 bytes
Fixed Size                  2184112 bytes
Variable Size             327158864 bytes
Database Buffers          285212672 bytes
Redo Buffers                7782400 bytes
Database is aangekoppeld.
Database is geopend.
SQL>


(It is used in the Netherlands so some of the messages are in Dutch.)