How to Solve ORA-39710 Error in Oracle 10g

oracleoracle-10g

I've got an Oracle 10.2 database that I need to get up and running. I've googled but now I've run into a problem I don't know how to solve.
I've been told the database is a bit FUBAR, so expect many strange things…

Here is what I've done so far.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Apr 7
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  914358272 bytes
Fixed Size                  2100520 bytes
Variable Size             230687448 bytes
Database Buffers          675282944 bytes
Redo Buffers                6287360 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

Then I check the alert.log

Successful open of redo thread 1
Fri Apr 07 01:53:24 EDT 2017
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Apr 07 01:53:24 EDT 2017
Errors in file /ora/product/10.2.0/db_1/admin/ora10/udump/ora10_ora_3031.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 3031
ORA-1092 signalled during: ALTER DATABASE OPEN...

And in the mentioned error file:

*** ACTION NAME:() 2017-04-07 01:53:24.252
*** MODULE NAME:(sqlplus@rh59 (TNS V1-V3)) 2017-04-07 01:53:24.252
*** SERVICE NAME:() 2017-04-07 01:53:24.252
*** SESSION ID:(159.3) 2017-04-07 01:53:24.251
Thread 1 checkpoint: logseq 29, block 2, scn 1119103
start recovery at logseq 29, block 2, scn 1119103
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 0Kb in 0.14s => 0.00 Mb/sec
Total physical reads: 4096Kb
Longest record: 0Kb, moves: 0/1 (0%)
Longest LWN: 0Kb, moves: 0/1 (0%), moved: 0Mb
Last redo scn: 0x0000.00111380 (1119104)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
*** 2017-04-07 01:53:24.398
KCRA: start recovery claims for 0 data blocks
*** 2017-04-07 01:53:24.398
KCRA: blocks processed = 0/0, claimed = 0, eliminated = 0
*** 2017-04-07 01:53:24.398
Recovery of Online Redo Log: Thread 1 Group 1 Seq 29 Reading mem 0
*** 2017-04-07 01:53:24.398
Completed redo application
*** 2017-04-07 01:53:24.398
Completed recovery checkpoint
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option

So I try UPGRADE

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  914358272 bytes
Fixed Size                  2100520 bytes
Variable Size             230687448 bytes
Database Buffers          675282944 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.

Fine! Lets create a user
SQL> CREATE USER foo IDENTIFIED BY "bar";

User created.

SQL> grant create session to foo;
Grant succeeded.

And then
$ sqlplus foo/bar

ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Google tells me to do
$ sqlplus / as sysdba
SQL> alter system disable restricted session;

System altered.

And then
$ sqlplus foo/bar

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

Now what?

I tried to do

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  914358272 bytes
Fixed Size                  2100520 bytes
Variable Size             230687448 bytes
Database Buffers          675282944 bytes
Redo Buffers                6287360 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

I check the alert log and the referenced file tell me (again)

ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option

Now what? shutdown/startup was not the solution. And I guess startup upgrade will just give me another 39710 error? How can I fix this?

Best Answer

39700, 00000, "database must be opened with UPGRADE option"

*Cause: A normal database open was attempted, but the database has not been upgraded to the current server version.

*Action: Use the UPGRADE option when opening the database to run catupgrd.sql (for database upgrade), or to run catalog.sql and catproc.sql (after initial database creation).

After SQL> startup upgrade, run the catupgrd.sql(if you were upgrading the database). The file can be found under $ORACLE_HOME/rdbms/admin directory.

After the completion it's execution SQL>shut immediate then SQL>startup then run SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sqlto recompile invalid database objects.