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
After
SQL> startup upgrade
, run thecatupgrd.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
thenSQL>startup
then runSQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
to recompile invalid database objects.