Unable to open database using startup command in sql (Oracle)

[Former Member]

Hi

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 15 10:1

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 1358954496 bytes

Fixed Size 2163904 bytes

Variable Size 701397824 bytes

Database Buffers 654311424 bytes

Redo Buffers 1081344 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

thanks

AS

[audunlea_hansen]

Hi!

See if You can start the database in mount and list the datafiles.

Seems like spfile are ok since the database go to mount and starts the open phase.

Notmally errors like this are connected to wrong parameter-settings. Look in your alert.log and check all paths for e.g. datafiles, trace areas, utl_file_dir

The easyest way to do this are to dump the spfile to a pfile and check those parameters.

Regards

Audun

DBA

[Former Member]

hi

As per my analysis , i found this 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1' to make online and also i tried this but no use.

The result is: as follows.....

C:\Documents and Settings\dejadm>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 15 14:13:50 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> starup mount;

SP2-0734: unknown command beginning "starup mou..." - rest of line ignored.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1358954496 bytes

Fixed Size 2163904 bytes

Variable Size 701397824 bytes

Database Buffers 654311424 bytes

Redo Buffers 1081344 bytes

Database mounted.

SQL> alter database recover datafile 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

alter database recover datafile 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

*

ERROR at line 1:

ORA-00279: change 10310337 generated at 03/09/2011 09:52:58 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\DEJ\ORAARCH\DEJARCHARC00012_0744738937.001

ORA-00280: change 10310337 for thread 1 is in sequence #12

SQL>

Is there any other way to open database ?

Thanks

AS

[sivakumar_kilari3]

I found these errors.

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

Read this forum.

[;

Regards

Siva

[audunlea_hansen]

Hi!

It is possible to temporary create an other undo tablespace, point the undo_tablespace parameter to this new undo-tablespace.

Then drop the undo-tbs that doesn't work and recreate it with exact same values as it have today.

Set the undo_tablespace parameter back and bounce the database again.

Drot the temporary undo-tablespace

Regards

Audun

DBA

[Former Member]

Hi All,

Thanks a lot for your solutions, above mentioned problem was solved forget to update.

i jus tried recover database using backup controlfile and i was able to start the DB with mount and opened.

thanks

AS

[sivakumar_kilari3]

Hi,

Can you paste your alert logs here.

Regards

Siva

[Former Member]

Hi

Please find the below alert log

ksdpec: called for event 13740 prior to event group initialization

Starting up ORACLE RDBMS Version: 10.2.0.4.0.

System parameters with non-default values:

processes = 80

sessions = 96

event = 10191 trace name context forever, level 1

sga_max_size = 1358954496

shared_pool_size = 654311424

shared_pool_reserved_size= 64345866

filesystemio_options = setall

control_files = D:\ORACLE\DEJ\ORIGLOGA\CNTRL\CNTRLDEJ.DBF, D:\ORACLE\DEJ\ORIGLOGB\CNTRL\CNTRLDEJ.DBF, D:\ORACLE\DEJ\SAPDATA1\CNTRL\CNTRLDEJ.DBF

control_file_record_keep_time= 30

db_block_size = 8192

db_cache_size = 654311424

compatible = 10.2.0

log_archive_dest = D:\oracle\DEJ\oraarch\DEJarch

log_buffer = 1048576

log_checkpoint_interval = 0

db_files = 254

log_checkpoints_to_alert = TRUE

dml_locks = 4000

replication_dependency_tracking= FALSE

undo_management = AUTO

undo_tablespace = PSAPUNDO

inmemory_undo = FALSE

undo_retention = 43200

recyclebin = off

remote_os_authent = TRUE

remote_login_passwordfile= EXCLUSIVE

job_queue_processes = 1

p

Tue N

Thread 1 advanced to log sequence 20 (LGWR switch)

Current log# 4 seq# 20 mem# 0: D:\ORACLE\DEJ\ORIGLOGB\LOG_G14M1.DBF

Current log# 4 seq# 20 mem# 1: D:\ORACLE\DEJ\MIRRLOGB\LOG_G14M2.DBF

Tue Nov 09 18:39:53 2010

Completed checkpoint up to RBA [0x12.2.10], SCN: 191192

Tue Nov 09 18:39:57 2010

Beginning log switch checkpoint up to RBA [0x15.2.10], SCN: 193113

Tue Nov 09 18:39:57 2010

Thread 1 advanced to log sequence 21 (LGWR switch)

Current log# 1 seq# 21 mem# 0: D:\ORACLE\DEJ\ORIGLOGA\LOG_G11M1.DBF

Current log# 1 seq# 21 mem# 1: D:\ORACLE\DEJ\MIRRLOGA\LOG_G11M2.DBF

Tue Nov 09 18:40:15 2010

Completed checkpoint up to RBA [0x13.2.10], SCN: 192035

Tue Nov 09 18:40:17 2010

Beginning log switch checkpoint up to RBA [0x16.2.10], SCN: 193870

Tue Nov 09 18:40:17 2010

Thread 1 advanced to log sequence 22 (LGWR switch)

Current log# 2 seq# 22 mem# 0: D:\ORACLE\DEJ\ORIGLOGB\LOG_G12M1.DBF

Current log# 2 seq# 22 mem# 1: D:\ORACLE\DEJ\MIRRLOGB\LOG_G12M2.DBF

Tue Nov 09 18:40:21 2010

Completed checkpoint up to RBA [0x14.2.10], SCN: 192570

Tue Nov 09 18:40:22 2010

Beginning log switch checkpoint up to RBA [0x17.2.10], SCN: 194272

Tue Nov 09 18:40:22 2010

Thread 1 advanced to log sequence 23 (LGWR switch)

Current log# 3 seq# 23 mem# 0: D:\ORACLE\DEJ\ORIGLOGA\LOG_G13M1.DBF

Current log# 3 seq# 23 mem# 1: D:\ORACLE\DEJ\MIRRLOGA\LOG_G13M2.DBF

Tue Nov 09 18:40:26 2010

Completed checkpoint up to RBA [0x15.2.10], SCN: 193113

Tue Nov 09 18:40:32 2010

Beginning log switch checkpoint up to RBA [0x18.2.10], SCN: 194764

Tue Nov 09 18:40:32 2010

Thread 1 advanced to log sequence 24 (LGWR switch)

Current log# 4 seq# 24 mem# 0: D:\ORACLE\DEJ\ORIGLOGB\LOG_G14M1.DBF

Current log# 4 seq# 24 mem# 1: D:\ORACLE\DEJ\MIRRLOGB\LOG_G14M2.DBF

Tue Nov 09 18:40:39 2010

Completed checkpoint up to RBA [0x16.2.10], SCN: 193870

Tue Nov 09 18:40:41 2010

Beginning log switch checkpoint up to RBA [0x19.2.10], SCN: 195264

Tue Nov 09 18:40:41 2010

Thread 1 advanced to log sequence 25 (LGWR switch)

Current log# 1 seq# 25 mem# 0: D:\ORACLE\DEJ\ORIGLOGA\LOG_G11M1.DBF

Current log# 1 seq# 25 mem# 1: D:\ORACLE\DEJ\MIRRLOGA\LOG_G11M2.DBF

Tue Nov 09 18:40:52 2010

Completed checkpoint up to RBA [0x17.2.10], SCN: 194272

Tue Nov 09 18:40:54 2010

Beginning log switch checkpoint up to RBA [0x1a.2.10], SCN: 195839

Tue Nov 09 18:40:54 2010

Thread 1 advanced to log sequence 26 (LGWR switch)

Current log# 2 seq# 26 mem# 0: D:\ORACLE\DEJ\ORIGLOGB\LOG_G12M1.DBF

Current log# 2 seq# 26 mem# 1: D:\ORACLE\DEJ\MIRRLOGB\LOG_G12M2.DBF

Tue Nov 09 18:41:03 2010

Completed checkpoint up to RBA [0x18.2.10], SCN: 194764

Tue Nov 09 18:41:04 2010

Beginning log switch checkpoint up to RBA [0x1b.2.10], SCN: 196341

Tue Nov 09 18:41:04 2010

Thread 1 advanced to log sequence 27 (LGWR switch)

Current log# 3 seq# 27 mem# 0: D:\ORACLE\DEJ\ORIGLOGA\LOG_G13M1.DBF

Current log# 3 seq# 27 mem# 1: D:\ORACLE\DEJ\MIRRLOGA\LOG_G13M2.DBF

Tue Nov 09 18:41:17 2010

Completed checkpoint up to RBA [0x19.2.10], SCN: 195264

Tue Nov 09 18:41:19 2010

Beginning log switch checkpoint up to RBA [0x1c.2.10], SCN: 196945

Tue Nov 09 18:41:19 2010

Thread 1 advanced to log sequence 28 (LGWR switch)

Current log# 4 seq# 28 mem# 0: D:\ORACLE\DEJ\ORIGLOGB\LOG_G14M1.DBF

Current log# 4 seq# 28 mem# 1: D:\ORACLE\DEJ\MIRRLOGB\LOG_G14M2.DBF

Tue Nov 09 18:41:25 2010

Completed checkpoint up to RBA [0x1a.2.10], SCN: 195839

Tue Nov 09 18:41:30 2010

Beginning log switch checkpoint up to RBA [0x1d.2.10], SCN: 197479

Tue Nov 09 18:41:30 2010

Thread 1 advanced to log sequence 29 (LGWR switch)

Current log# 1 seq# 29 mem# 0: D:\ORACLE\DEJ\ORIGLOGA\LOG_G11M1.DBF

Current log# 1 seq# 29 mem# 1: D:\ORACLE\DEJ\MIRRLOGA\LOG_G11M2.DBF

Tue Nov 09 18:41:39 2010

Completed checkpoint up to RBA [0x1b.2.10], SCN: 196341

Tue Nov 09 18:41:41 2010

Beginning log switch checkpoint up to RBA [0x1e.2.10], SCN: 197966

Tue Nov 09 18:41:41 2010

Thread 1 advanced to log sequence 30 (LGWR switch)

Current log# 2 seq# 30 mem# 0: D:\ORACLE\DEJ\ORIGLOGB\LOG_G12M1.DBF

Current log# 2 seq# 30 mem# 1: D:\ORACLE\DEJ\MIRRLOGB\LOG_G12M2.DBF

Tue Nov 09 18:41:49 2010

Completed checkpoint up to RBA [0x1c.2.10], SCN: 196945

Tue Nov 09 18:41:53 2010

Beginning log switch checkpoint up to RBA [0x1f.2.10], SCN: 198692

Tue Nov 09 18:41:53 2010

Thread 1 advanced to log sequence 31 (LGWR switch)

Current log# 3 seq# 31 mem# 0: D:\ORACLE\DEJ\ORIGLOGA\LOG_G13M1.DBF

Current log# 3 seq# 31 mem# 1: D:\ORACLE\DEJ\MIRRLOGA\LOG_G13M2.DBF

Tue Nov 09 18:43:13 2010

Shutting down instance: further logons disabled

Tue Nov 09 18:43:13 2010

Completed checkpoint up to RBA [0x1d.2.10], SCN: 197479

Completed checkpoint up to RBA [0x1f.2.10], SCN: 198692

Completed checkpoint up to RBA [0x1e.2.10], SCN: 197966

Tue Nov 09 18:43:13 2010

Stopping background process CJQ0

Tue Nov 09 18:43:14 2010

Stopping background process QMNC

Tue Nov 09 18:43:14 2010

Stopping background process MMNL

Tue Nov 09 18:43:15 2010

Background process MMNL not dead after 10 seconds

Tue Nov 09 18:43:15 2010

Stopping background process MMON

Tue Nov 09 18:43:16 2010

Background process MMON not dead after 30 seconds

Tue Nov 09 18:43:16 2010

Shutting down instance (immediate)

License high water mark = 33

Tue Nov 09 18:43:16 2010

Stopping Job queue slave processes, flags = 7

Tue Nov 09 18:43:16 2010

Job queue slave processes stopped

Tue Nov 09 18:43:16 2010

ALTER DATABASE CLOSE NORMAL

Tue Nov 09 18:43:17 2010

SMON: disabling tx recovery

SMON: disabling cache recovery

Tue Nov 09 18:43:18 2010

Shutting down archive processes

Archiving is disabled

Archive process shutdown avoided: 0 active

Thread 1 closed at log sequence 31

Successful close of redo thread 1

Tue Nov 09 18:43:18 2010

Completed: ALTER DATABASE CLOSE NORMAL

Tue Nov 09 18:43:18 2010

ALTER DATABASE DISMOUNT

Completed: ALTER DATABASE DISMOUNT

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Archive process shutdown avoided: 0 active

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Archive process shutdown avoided: 0 active

Tue Nov 09 18:43:26 2010

Adjusting the default value of parameter parallel_max_servers

from 160 to 65 due to the value of parameter processes (80)

Tue Nov 09 18:43:26 2010

Starting ORACLE instance (normal)

Tue Nov 09 18:43:26 2010

Specified value of sga_max_size is too small, bumping to 1358954496

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

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.4.0.

System parameters with non-default values:

processes = 80

sessions = 96

event = 10191 trace name context forever, level 1

sga_max_size = 1358954496

shared_pool_size = 654311424

shared_pool_reserved_size= 64345866

filesystemio_options = setall

control_files = D:\ORACLE\DEJ\ORIGLOGA\CNTRL\CNTRLDEJ.DBF, D:\ORACLE\DEJ\ORIGLOGB\CNTRL\CNTRLDEJ.DBF, D:\ORACLE\DEJ\SAPDATA1\CNTRL\CNTRLDEJ.DBF

control_file_record_keep_time= 30

db_block_size = 8192

db_cache_size = 654311424

compatible = 10.2.0

log_archive_dest = D:\oracle\DEJ\oraarch\DEJarch

log_buffer = 1048576

log_checkpoint_interval = 0

db_files = 254

log_checkpoints_to_alert = TRUE

dml_locks = 4000

replication_dependency_tracking= FALSE

undo_management = AUTO

undo_tablespace = PSAPUNDO

inmemory_undo = FALSE

undo_retention = 43200

recyclebin = off

remote_os_authent = TRUE

remote_login_passwordfile= EXCLUSIVE

job_queue_processes = 1

parallel_execution_message_size= 16384

tablelookup_prefetch_size= 0

background_dump_dest = D:\ORACLE\DEJ\SAPTRACE\BACKGROUND

user_dump_dest = D:\ORACLE\DEJ\SAPTRACE\USERTRACE

core_dump_dest = D:\ORACLE\DEJ\SAPTRACE\BACKGROUND

sort_area_size = 2097152

sort_area_retained_size = 0

db_name = DEJ

open_cursors = 800

sortelimination_cost_ratio= 10

btree_bitmap_plans = FALSE

star_transformation_enabled= true

query_rewrite_enabled = false

indexjoin_enabled = FALSE

optimpeek_user_binds = FALSE

pga_aggregate_target = 629145600

optimizermjc_enabled = FALSE

PMON started with pid=2, OS id=7804

PSP0 started with pid=4, OS id=7128

MMAN started with pid=6, OS id=7256

DBW0 started with pid=8, OS id=7756

DBW1 started with pid=10, OS id=6588

LGWR started with pid=12, OS id=1424

CKPT started with pid=14, OS id=3764

SMON started with pid=16, OS id=7648

RECO started with pid=18, OS id=3440

CJQ0 started with pid=20, OS id=6552

MMON started with pid=22, OS id=6996

MMNL started with pid=24, OS id=6884

Tue Nov 09 18:43:29 2010

ALTER DATABASE MOUNT

Tue Nov 09 18:43:33 2010

Setting recovery target incarnation to 1

Tue Nov 09 18:43:33 2010

Successful mount of redo thread 1, with mount id 1663674961

Tue Nov 09 18:43:33 2010

Database mounted in Exclusive Mode

Completed: ALTER DATABASE MOUNT

Tue Nov 09 18:43:34 2010

ALTER DATABASE OPEN

Tue Nov 09 18:43:34 2010

Thread 1 opened at log sequence 31

Current log# 3 seq# 31 mem# 0: D:\ORACLE\DEJ\ORIGLOGA\LOG_G13M1.DBF

Current log# 3 seq# 31 mem# 1: D:\ORACLE\DEJ\MIRRLOGA\LOG_G13M2.DBF

Successful open of redo thread 1

Tue Nov 09 18:43:34 2010

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Tue Nov 09 18:43:34 2010

SMON: enabling cache recovery

Tue Nov 09 18:43:35 2010

Successfully onlined Undo Tablespace 1.

Tue Nov 09 18:43:35 2010

Sun Dec 26 04:41:14 2010

Errors in file d:\oracle\dej\saptrace\background\dej_smon_8144.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

Sun Dec 26 04:41:24 2010

Errors in file d:\oracle\dej\saptrace\background\dej_pmon_8108.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

Sun Dec 26 04:41:24 2010

Errors in file d:\oracle\dej\saptrace\background\dej_pmon_8108.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

Sun Dec 26 04:41:24 2010

Errors in file d:\oracle\dej\saptrace\background\dej_pmon_8108.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

Sun Dec 26 04:41:24 2010

Errors in file d:\oracle\dej\saptrace\background\dej_pmon_8108.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

Sun Dec 26 04:41:24 2010

Errors in file d:\oracle\dej\saptrace\background\dej_smon_8144.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

Sun Dec 26 04:41:25 2010

Errors in file d:\oracle\dej\saptrace\background\dej_pmon_8108.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

Sun Dec 26 04:41:25 2010

Errors in file d:\oracle\dej\saptrace\background\dej_pmon_8108.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

Sun Dec 26 04:41:25 2010

Errors in file d:\oracle\dej\saptrace\background\dej_pmon_8108.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

Tue Mar 15 00:46:10 2011

Errors in file d:\oracle\dej\saptrace\usertrace\dej_ora_6088.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1'

Error 604 happened during db open, shutting down database

USER: terminating instance due to error 604

Tue Mar 15 00:46:10 2011

Errors in file d:\oracle\dej\saptrace\background\dej_pmon_9476.trc:

ORA-00604: error occurred at recursive SQL level

Tue Mar 15 00:46:11 2011

Errors in file d:\oracle\dej\saptrace\background\dej_reco_10540.trc:

ORA-00604: error occurred at recursive SQL level

Tue Mar 15 00:46:12 2011

Errors in file d:\oracle\dej\saptrace\background\dej_ckpt_10024.trc:

ORA-00604: error occurred at recursive SQL level

Tue Mar 15 00:46:13 2011

Errors in file d:\oracle\dej\saptrace\background\dej_psp0_8240.trc:

ORA-00604: error occurred at recursive SQL level

Thanks

AS

[Former Member]

Some of your datafiles seem to have 'disappeared'.

ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: 'D:\ORACLE\DEJ\SAPDATA2\UNDO_1\UNDO.DATA1' 

Check if the files are there and you have access to them. If not what has happened?

If they have been deleted or a disk is gone, then you might need to restore the files from a backup.

Cheers Michael