Home » RDBMS Server » Server Administration » ORA-39700 on newly created database
ORA-39700 on newly created database [message #301366] Wed, 20 February 2008 04:50 Go to next message
bofh
Messages: 7
Registered: February 2008
Junior Member
Hello,

I have installed Oracle 10g on SUN V240 running Solaris 10, and built two databases.
One of them is running fine, but the other one refused to start after I did an import from an export generated with an older release (Oracle 8.1.7).
After the import, I got ORA-39700 (database must be started in upgrade mode), so, I started it with startup upgrade and tried catupgrd.sql to fix this error.
Catupgrd.sql also refused to "upgrade" my DB, because of a version mismatch.
So, I decided to start again from scratch, dropped the entire database, cleaned up all Oracle directories, and re-created the DB. This worked, so, I did a shutdown immediate to ensure that everything's well, and now, I'm again unable to start the DB because of ORA-39700.
So it seems that there's something leftover somewhere from the old installation?!

Here's what I get when I try to start the DB:

sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mi Feb 20 11:43:36 2008

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

Bei einer nicht hochgefahrenen Instance angemeldet.


> startup
ORACLE-Instance hochgefahren.

Total System Global Area 343932928 bytes
Fixed Size 1978560 bytes
Variable Size 272633664 bytes
Database Buffers 67108864 bytes
Redo Buffers 2211840 bytes
Datenbank mit MOUNT angeschlossen.
ORA-01092: ORACLE-Instance beendet. Verbindungsabbruch erzwungen.


>

And here's the appr. part of the alert.log:

====================/snaip/=========================
Wed Feb 20 11:43:41 2008
Starting ORACLE instance (normal)
Wed Feb 20 11:43:41 2008
Specified value of sga_max_size is too small, bumping to 343932928
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =97
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
processes = 800
timed_statistics = FALSE
sga_max_size = 343932928
shared_pool_size = 134217728
large_pool_size = 134217728
java_pool_size = 4194304
resource_manager_plan = system_plan
control_files = /oracle/oradata/TESTANNY/control01.ctl, /oracle/ora
index/TESTANNY/control02.ctl, /oracle/orabackup/TESTANNY/control03.ctl
db_block_size = 8192
db_cache_size = 67108864
compatible = 10.1.0.2.0
log_archive_dest = /oracle/orabackup/TESTANNY/arch/
log_archive_format = TESTANNY_log%t_%s_%r.arc
log_buffer = 2144256
db_files = 200
db_file_multiblock_read_count= 8
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = undo
O7_DICTIONARY_ACCESSIBILITY= FALSE
remote_login_passwordfile= EXCLUSIVE
instance_name = TESTANNY
utl_file_dir = *
job_queue_processes = 10
background_dump_dest = /opt/app/oracle/admin/TESTANNY/bdump
user_dump_dest = /opt/app/oracle/admin/TESTANNY/udump
core_dump_dest = /opt/app/oracle/admin/TESTANNY/cdump
optimizer_features_enable= 10.1.0
db_name = TESTANNY
open_cursors = 1000
os_authent_prefix = ops$
star_transformation_enabled= false
pga_aggregate_target = 104857600
PMON started with pid=2, OS id=1831
PSP0 started with pid=3, OS id=1833
MMAN started with pid=4, OS id=1835
DBW0 started with pid=5, OS id=1837
LGWR started with pid=6, OS id=1839
CKPT started with pid=7, OS id=1841
SMON started with pid=8, OS id=1843
RECO started with pid=9, OS id=1845
CJQ0 started with pid=10, OS id=1847
MMON started with pid=11, OS id=1849
MMNL started with pid=12, OS id=1851
Wed Feb 20 11:43:43 2008
ALTER DATABASE MOUNT
Wed Feb 20 11:43:47 2008
Setting recovery target incarnation to 1
Wed Feb 20 11:43:47 2008
Successful mount of redo thread 1, with mount id 1241369775
Wed Feb 20 11:43:47 2008
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Wed Feb 20 11:43:47 2008
ALTER DATABASE OPEN
Wed Feb 20 11:43:47 2008
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Wed Feb 20 11:43:47 2008
Started redo scan
Wed Feb 20 11:43:48 2008
Completed redo scan
0 redo blocks read, 0 data blocks need recovery
Wed Feb 20 11:43:48 2008
Started redo application at
Thread 1: logseq 14, block 3, scn 368303
Wed Feb 20 11:43:48 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 14 Reading mem 0
Mem# 0 errs 0: /oracle/oradata/TESTANNY/log_2a.rdo
Mem# 1 errs 0: /oracle/orabackup/TESTANNY/log_2b.rdo
Wed Feb 20 11:43:48 2008
Completed redo application
Wed Feb 20 11:43:48 2008
Completed crash recovery at
Thread 1: logseq 14, block 3, scn 388304
0 data blocks read, 0 data blocks written, 0 redo blocks read
Wed Feb 20 11:43:48 2008
Thread 1 advanced to log sequence 15
Thread 1 opened at log sequence 15
Current log# 3 seq# 15 mem# 0: /oracle/oraindex/TESTANNY/log_3a.rdo
Current log# 3 seq# 15 mem# 1: /oracle/orabackup/TESTANNY/log_3b.rdo
Successful open of redo thread 1
Wed Feb 20 11:43:48 2008
SMON: enabling cache recovery
Wed Feb 20 11:43:48 2008
Errors in file /opt/app/oracle/admin/TESTANNY/udump/testanny_ora_1853.trc:
ORA-00704: Bootstrap-Prozess nicht erfolgreich
ORA-39700: Datenbank muss mit UPGRADE-Option geöffnet werden
Wed Feb 20 11:43:48 2008
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Wed Feb 20 11:43:48 2008
Errors in file /opt/app/oracle/admin/TESTANNY/bdump/testanny_mman_1835.trc:
ORA-00704: bootstrap process failure
Instance terminated by USER, pid = 1853
ORA-1092 signalled during: ALTER DATABASE OPEN...
=================/snap/===================================

Any ideas?

Thanks and br,

Harald
Re: ORA-39700 on newly created database [message #301383 is a reply to message #301366] Wed, 20 February 2008 06:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> I did an import from an export generated with an older release (Oracle 8.1.7).
Could you post the exact commands you used, first/last 20 lines from logfile?
>>Catupgrd.sql also refused to "upgrade" my DB, because of a version mismatch.
Post the exact output.
Re: ORA-39700 on newly created database [message #301390 is a reply to message #301366] Wed, 20 February 2008 07:23 Go to previous messageGo to next message
bofh
Messages: 7
Registered: February 2008
Junior Member
Hi,

thanks for the fast reply! I used exp/imp in interactive mode for the export, and when I faced the starting problems afterwards, I cleaned up my system and deleted the import log, so I can't post it, sorry...
But I tried catupgrd.sql again, and here's what I got:

> startup upgrade
ORACLE-Instance hochgefahren.

Total System Global Area 343932928 bytes
Fixed Size 1978560 bytes
Variable Size 272633664 bytes
Database Buffers 67108864 bytes
Redo Buffers 2211840 bytes
Datenbank mit MOUNT angeschlossen.
Datenbank geöffnet.
> @/opt/app/oracle/product/10.2.0/rdbms/admin/catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

<...lots of stuff here...>

SELECT version_script AS file_name FROM DUAL
*
FEHLER in Zeile 1:
ORA-20000: Upgrade not supported from version
ORA-06512: in "SYS.VERSION_SCRIPT", Zeile 50


Verbindung zu Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options beendet
oracle10@tantalus:~
[TESTANNY]$

And that's it. I also tried catalog.sql and catproc.sql, catalog.sql runs fine, but catproc.sql stops with the following error:

BEGIN
*
FEHLER in Zeile 1:
ORA-39706: Schema 'DBSNMP' nicht gefunden
ORA-06512: in "SYS.DBMS_SYS_ERROR", Zeile 86
ORA-06512: in "SYS.DBMS_REGISTRY", Zeile 71
ORA-06512: in "SYS.DBMS_REGISTRY", Zeile 494
ORA-06512: in Zeile 2

(and of course, the DB still refuses to start with ORA-39700).

Harald
Re: ORA-39700 on newly created database [message #301392 is a reply to message #301390] Wed, 20 February 2008 07:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you do a full import?
Usually, you are supposed to import only the custom schemas (users/schemas you created and not sys/system/dbsnmp etc).
And just for exp/imp, catupgrd.sql does not need to be run.
I would recomend to startover.
Import only the custom schema.
and by these last two statements
>>Datenbank mit MOUNT angeschlossen.
>>Datenbank geöffnet.
I reckon the database is mounted and opened.

[Updated on: Wed, 20 February 2008 07:32]

Report message to a moderator

Re: ORA-39700 on newly created database [message #301417 is a reply to message #301366] Wed, 20 February 2008 09:34 Go to previous messageGo to next message
bofh
Messages: 7
Registered: February 2008
Junior Member
Hi Mahesh,

that's a good point, the next time, I will only import the schemas, and not the system stuff.
But, the problem at the moment is that I can't start over: I created the database again from scratch without importing anything, and now I get these errors - with "startup upgrade" I get the DB mounted and opened, but I can't start this newly created DB in normal mode. So I guess there's still something wrong, and maybe there's something lyin' around from the old setup, although I cleaned up oradata etc.

Harald
Re: ORA-39700 on newly created database [message #301421 is a reply to message #301417] Wed, 20 February 2008 09:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> I created the database again from scratch without importing anything, and now I get these errors - with "startup upgrade"

If it is a new 10g database, why are issuing startup upgrade?

It is supposed to be issued only after doing "some kind of upgrade".
Re: ORA-39700 on newly created database [message #301422 is a reply to message #301366] Wed, 20 February 2008 09:49 Go to previous messageGo to next message
bofh
Messages: 7
Registered: February 2008
Junior Member
Hi,

yes, that's clear: I was also wondering why I should upgrade a newly created database, but the DB refuses to start in normal mode, I always get ORA-39700 - and using startup upgrade was the only suggestion I've found, so I decided to give it a try.

Harald
Re: ORA-39700 on newly created database [message #301429 is a reply to message #301422] Wed, 20 February 2008 10:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
post your environment. Values of
ORACLE_HOME, ORACLE_BASE and PATH, contents of oratab file, your login.sql or glogin.sql .
I assume you do not have both versions in same Host.
Did you copy the oracle users profile (environment) from old server to new server?
Did you use DBCA to create database or manual options?
Try these logged into sql*plus( after startup upgrade, as there is no other option).
If you find something like this, that is a possible issue.
c > show com
compatibility version NATIVE
c > show sqlpluscompat
sqlpluscompatibility 8.1.7

you are supposed to see something like this
SQL> show com
compatibility version NATIVE
SQL> show sqlpluscompat
sqlpluscompatibility 10.2.0

Re: ORA-39700 on newly created database [message #301432 is a reply to message #301429] Wed, 20 February 2008 10:38 Go to previous messageGo to next message
bofh
Messages: 7
Registered: February 2008
Junior Member
Hi,

I have both versions on the same host, but with different users. And, I have one 10g and one 8i database running fine on it...
Here's my environment for the oracle10 user:

env | grep -i ora
USER=oracle10
LD_LIBRARY_PATH=:/opt/app/oracle/product/10.2.0/lib
ORACLE_SID=TESTANNY
ORACLE_BASE=/opt/app/oracle
TNS_ADMIN=/var/opt/oracle
MAIL=/var/mail//oracle10
PATH=/usr/local/bin:/usr/bin:/opt/app/oracle/product/10.2.0/bin
PWD=/opt/app/oracle/product/10.2.0
[$ORACLE_SID]$
SQLPATH=/opt/oracle10/tools:/opt/app/oracle/product/10.2.0/rdbms/admin
HOME=/opt/oracle10
LOGNAME=oracle10
ORA_NLS33=/opt/app/oracle/product/10.2.0/ocommon/nls/admin/data
ORACLE_HOME=/opt/app/oracle/product/10.2.0
PRECOMPPUBLIC=/opt/app/oracle/product/10.2.0/precomp/public
oracle10@tantalus:/opt/app/oracle/product/10.2.0

oratab-file:
TESTD2:/opt/app/oracle/product/8.1.7:Y
TESTPMW:/opt/app/oracle/product/10.2.0:Y
TESTANNY:/opt/app/oracle/product/10.2.0:Y

glogin.sql:
SET PAGESIZE 14
SET SQLPLUSCOMPATIBILITY 10.2.0

-- Used by Trusted Oracle
COLUMN ROWLABEL FORMAT A15

-- Used for the SHOW ERRORS command
COLUMN LINE/COL FORMAT A8
COLUMN ERROR FORMAT A65 WORD_WRAPPED

-- Used for the SHOW SGA command
COLUMN name_col_plus_show_sga FORMAT a24

-- Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for SET AUTOTRACE EXPLAIN report
COLUMN id_plus_exp FORMAT 990 HEADING i
COLUMN parent_id_plus_exp FORMAT 990 HEADING p
COLUMN plan_plus_exp FORMAT a60
COLUMN object_node_plus_exp FORMAT a8
COLUMN other_tag_plus_exp FORMAT a29
COLUMN other_plus_exp FORMAT a44

-- Defaults for sqlprompt, _editor, editfile, serveroutput
set termout off
col dbname new_value prompt_dbname
select global_name dbname from global_name;
set sqlprompt "&&prompt_dbname> "
set termout on

define _editor='vi'
set editfile $HOME/sql.buffer
set serveroutput on

(One word to glogin.sql: When I checked sqlpluscompat, the first time I got 8.1.7, so I edited glogin.sql and set this to 10.2.0. Now, I get 10.2.0 in sqlplus as supposed, but I still can't start the DB...)

Harald
Re: ORA-39700 on newly created database [message #301434 is a reply to message #301432] Wed, 20 February 2008 10:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The damage is already done Smile
Try recreating the database.
This is something very basic and I am running out of ideas.
If it does not work, please open a ticket with Oracle Support.
Re: ORA-39700 on newly created database [message #301435 is a reply to message #301366] Wed, 20 February 2008 11:03 Go to previous messageGo to next message
bofh
Messages: 7
Registered: February 2008
Junior Member
Hi Mahesh,

thanks for your support and the fast answers!
As a last try, I will drop the database again and recreate it with the new glogin.sql.
If this fails, I'm also out of ideas...

Harald
Re: ORA-39700 on newly created database [message #301664 is a reply to message #301435] Thu, 21 February 2008 05:04 Go to previous messageGo to next message
bofh
Messages: 7
Registered: February 2008
Junior Member
Hi Mahesh,

I've dropped the database, re-created it with sqlpluscompat 10.2.0, and voila, it's working, Smile.
I'd like to know what happened, but, I think that was something weird, and I will never figure it out.
Nevertheless, everything's fine now, Smile.
Thanks again for your support!

Harald
Re: ORA-39700 on newly created database [message #301666 is a reply to message #301664] Thu, 21 February 2008 05:10 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Good to know. Thanks for feedback.
Previous Topic: Charater sets usage in oracle
Next Topic: Bug No. 6376915
Goto Forum:
  


Current Time: Thu Dec 12 04:12:54 CST 2024