Cannot start or mount database for Recovery

[ceecil]

Hello people,

I am new to Oracle 9i. I just installed it on Win2k Server and created the database successfully. It worked well for about 10 days. Suddenly today, I was not able to start the database through Enterprise Manager.

1. When I try to connect as sys and click oK, it seems fine but when I clcik on instance, it shows that the DB status is not started. Basically, it shows RED - shutdown.

2. When I try to Start UNMOUNTED, it says 'starting database' and it goes on and on for a long time.

This database was in archive mode as I had turned it on. However, I am not able to start the database in unmount mode or mount mode in order to open it. Could somebody tell me how to oever come this problem.

Thanks

[davey23uk]

can you start it from sqlplus - mght get a better message then, also check the alert log

[ceecil]

Thank you for your response. I did try to start the DB from SQLPLUS. It does the same thing - just hangs as though it is processing. Maybe it is, but I closed the window and went back to EM and tried to -> start unmounted there. It's been going on for 1/2 an hour now. I cannot even cancel that because it say ' Operation is no cancellable'.

I think the problem may be because I edited some script which may have attempted to change the DB character set to UTF8. I am not sure but I cannot afford to lose this database because it was created during a complex Oracle Clinical 4.5 installation.

here is the alert log that I found. I have posted only the last part which was a challenge in itself. So much for Oracle9i !!.

Wed Jan 11 00:15:24 2006
ORACLE V9.2.0.3.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Wed Jan 11 00:15:24 2006
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.3.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 117440512
large_pool_size = 8388608
java_pool_size = 117440512
enqueue_resources = 2000
nls_language = AMERICAN
nls_date_language = AMERICAN
nls_date_format = DD-MON-RRRR
nls_numeric_characters = .,
control_files = E:\oracle\ora92\octrn\control01.ctl, E:\oracle\ora92\octrn\control02.ctl, E:\oracle\ora92\octrn\control03.ctl
db_block_size = 16384
db_cache_size = 83886080
compatible = 9.2.0.3.0
db_files = 100
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
rdbms_server_dn = cn=octrn
max_enabled_roles = 128
remote_os_authent = TRUE
remote_login_passwordfile= EXCLUSIVE
db_domain = techobs.com
instance_name = octrn
dispatchers = (protocol=TCP)
utl_file_dir = *
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = E:\oracle\admin\octrn\bdump
user_dump_dest = E:\oracle\admin\octrn\udump
core_dump_dest = E:\oracle\admin\octrn\cdump
sort_area_size = 3000000
db_name = octrn
open_cursors = 300
os_authent_prefix = OPS$
optimizer_mode = CHOOSE
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 25165824
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Wed Jan 11 00:15:33 2006
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Jan 11 00:15:35 2006
alter database mount exclusive
Wed Jan 11 00:15:42 2006
Successful mount of redo thread 1, with mount id 2708703784.
Wed Jan 11 00:15:42 2006
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Wed Jan 11 00:15:42 2006
alter database open
Wed Jan 11 00:15:43 2006
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 35
Current log# 2 seq# 35 mem# 0: E:\ORACLE\ORA92\OCTRN\REDO02.LOG
Successful open of redo thread 1.
Wed Jan 11 00:15:45 2006
SMON: enabling cache recovery
Wed Jan 11 00:15:46 2006
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Wed Jan 11 00:15:46 2006
SMON: enabling tx recovery
Wed Jan 11 00:15:46 2006
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
Wed Jan 11 05:24:23 2006
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 5
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Wed Jan 11 05:24:35 2006
ALTER DATABASE CLOSE NORMAL
Wed Jan 11 05:24:35 2006
SMON: disabling tx recovery
SMON: disabling cache recovery
Wed Jan 11 05:24:35 2006
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 35
Successful close of redo thread 1.
Wed Jan 11 05:24:38 2006
Completed: ALTER DATABASE CLOSE NORMAL
Wed Jan 11 05:24:38 2006
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Wed Jan 11 05:24:43 2006
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.3.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 117440512
large_pool_size = 8388608
java_pool_size = 117440512
enqueue_resources = 2000
nls_language = AMERICAN
nls_date_language = AMERICAN
nls_date_format = DD-MON-RRRR
nls_numeric_characters = .,
control_files = E:\oracle\ora92\octrn\control01.ctl, E:\oracle\ora92\octrn\control02.ctl, E:\oracle\ora92\octrn\control03.ctl
db_block_size = 16384
db_cache_size = 83886080
compatible = 9.2.0.3.0
db_files = 100
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
rdbms_server_dn = cn=octrn
max_enabled_roles = 128
remote_os_authent = TRUE
remote_login_passwordfile= EXCLUSIVE
db_domain = techobs.com
instance_name = octrn
dispatchers = (protocol=TCP)
local_listener = LISTENER_OCTRN
utl_file_dir = *
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = E:\oracle\admin\octrn\bdump
user_dump_dest = E:\oracle\admin\octrn\udump
core_dump_dest = E:\oracle\admin\octrn\cdump
sort_area_size = 3000000
db_name = octrn
open_cursors = 300
os_authent_prefix = OPS$
optimizer_mode = CHOOSE
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 25165824
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Wed Jan 11 05:24:46 2006
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Jan 11 05:24:47 2006
ALTER DATABASE MOUNT
Wed Jan 11 05:24:52 2006
Successful mount of redo thread 1, with mount id 2708740511.
Wed Jan 11 05:24:52 2006
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Wed Jan 11 05:24:52 2006
ALTER DATABASE OPEN
Wed Jan 11 05:24:53 2006
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 35
Current log# 2 seq# 35 mem# 0: E:\ORACLE\ORA92\OCTRN\REDO02.LOG
Successful open of redo thread 1.
Wed Jan 11 05:24:55 2006
SMON: enabling cache recovery
Wed Jan 11 05:24:56 2006
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Wed Jan 11 05:24:56 2006
SMON: enabling tx recovery
Wed Jan 11 05:24:56 2006
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Thu Jan 12 01:29:44 2006
Thread 1 cannot allocate new log, sequence 36
All online logs needed archiving
Current log# 2 seq# 35 mem# 0: E:\ORACLE\ORA92\OCTRN\REDO02.LOG
Thu Jan 12 22:31:05 2006
Shutting down instance: further logons disabled

[davey23uk]

that says the database is fine

what are you doing in sqlplus - show us some output

[ceecil]

This is what I got from my SQLPLUS: I see an error in the listener which is surprising. I have listed the listener.ora file below.

SQL> startup pfile=e:\oracle\ora92\database\initoctrn.ora;
ORA-00119: invalid specification for system parameter local_listener
ORA-00132: syntax error or unresolved network name 'LISTENER_OCTRN'
ORA-01078: failure in processing system parameters
SQL> spool off



# LISTENER.ORA Network Configuration File: E:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = w2kocsvr)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = octrn)
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = octrn)
)
(SID_DESC =
(GLOBAL_DBNAME = OEMREP.techobs.com)
(ORACLE_HOME = E:\oracle\ora92)
(SID_NAME = OEMREP)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\ora92)
(PROGRAM = extproc)
)
)

TRACE_LEVEL_LISTENER = ADMIN
==================================================
note: I have deleted the listener and recreated it. The listing is above. The name of the listener is LISTENER. Yet I get the same error in SQLPLUS. I am working on windows2K. Is it because the service is on?. I had stopped the service when I recreated the listener. I even rebooted the machine and tried the sqlplus.

[sambavan]

check your initoctrn.ora file for any lisener entry for the MTS. If you remove it and try starting it.

Thanx,
Sam

[ceecil]

No such listener entry in initoctrn.ora .Another thing is that I see this in the file initoctrn.ora -

local_listener= LISTENER_OCTRN. That is incorrect. In my quest to make this work, I had created the Pfile from the Spfile. The SPFILE also has this entry. Both must be changed to just LISTENER as seen in the LISTENER.ora above. But if I edit these files, would they still work?.

[tomcat]

You use one or the other - in your example, you started it manually with a pfile=, so it used an init.ora and not an spfile for that one startup.

If you just started the win service, it will default to the spfile - if it finds one. Same as doing starup at sqlplus prompt w/o the pfile= clause.

You also don't directly edit the spfile - alter system set .... scope=spfile;

[ceecil]

Thank you guys for your insights. I have resolved my issue. First I could not use the Alter system command as Oracle was not available - it said.
However, it was a good piece of information for editing. TY.

I had kept a copy of my spfile. So I copied it back and found thtat it did not have that stupid parameter local_listener. God knows how it came into the new one. Maybe when I recreated the listener. What a joke Oracle is!!.

Once I copied the spfile, I recreated the pfile from it. Then I just made sure that all the Oracle services were on. And Bingo, the DB started.
Thank you all.