ORA-01092: ORACLE instance terminated. Disconnection forced

Version imprimable

13/07/2006, 18h40
fahenry

ORA-01092: ORACLE instance terminated. Disconnection forced

Bonjour à tous,

je rencontre un probleme pour moi insoluble (mes connaissances sont limitées en debug )
La machine sur laquelle tourne mon instance Oracle (10g sur une RedHat AS3.3) a reboot froidement pendant un arret de l'instance .
Apres ce reboot. Oracle m a mis le message (titre du sujet)

je vous mets Alert_log de cette instance. Si qqun pouvait me guider vers un debut de solution ...
Merci par avance
Fa
Edit: j'ai un Backup de la DB (130Go) au cas ou, mais j' aimerais connaitre la maniere de resoudre ce prob

Citation:

Thu Jul 13 17:04:49 2006
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
KCCDEBUG_LEVEL = 0
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
Dynamic strands is set to TRUE
Running with 2 shared and 30 private strand(s). Zero-copy redo is FALSE
IMODE=BR
ILAT =30
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 10.1.0.2.0.
System parameters with non-default values:
processes = 250
__shared_pool_size = 138412032
__large_pool_size = 4194304
__java_pool_size = 8388608
nls_language = AMERICAN
sga_target = 285212672
control_files = /u02/oradata/cgnsdata/control01.ctl, /u02/oradata/cgnsdata/control02.ctl, /u02/oradata/cgnsdata/control03.ctl
db_block_size = 8192
__db_cache_size = 130023424
compatible = 10.1.0.2.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size= 39147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=cgnsdataXDB)
job_queue_processes = 10
background_dump_dest = /u01/app/oracle/admin/cgnsdata/bdump
user_dump_dest = /u01/app/oracle/admin/cgnsdata/udump
core_dump_dest = /u01/app/oracle/admin/cgnsdata/cdump
sort_area_size = 65536
db_name = cgnsdata
open_cursors = 300
pga_aggregate_target = 94371840
PMON started with pid=2, OS id=4778
MMAN started with pid=3, OS id=4780
DBW0 started with pid=4, OS id=4782
LGWR started with pid=5, OS id=4784
CKPT started with pid=6, OS id=4786
SMON started with pid=7, OS id=4788
RECO started with pid=8, OS id=4790
Thu Jul 13 17:04:49 2006
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
CJQ0 started with pid=9, OS id=4792
Thu Jul 13 17:04:49 2006
starting up 1 shared server(s) ...
Thu Jul 13 17:04:49 2006
ALTER DATABASE MOUNT
Thu Jul 13 17:04:49 2006
Controlfile identified with block size 16384
Thu Jul 13 17:04:53 2006
Setting recovery target incarnation to 2
Thu Jul 13 17:04:53 2006
Successful mount of redo thread 1, with mount id 1502107377
Thu Jul 13 17:04:53 2006
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Thu Jul 13 17:04:53 2006
ALTER DATABASE OPEN
Thu Jul 13 17:04:53 2006
Beginning crash recovery of 1 threads
attempting to start a parallel recovery with 3 processes
parallel recovery started with 3 processes
Thu Jul 13 17:04:54 2006
Started first pass scan
Thu Jul 13 17:04:54 2006
Completed first pass scan
1 redo blocks read, 0 data blocks need recovery
Thu Jul 13 17:04:54 2006
Started redo application at
Thread 1: logseq 191289, block 2, scn 0.805770165
Recovery of Online Redo Log: Thread 1 Group 2 Seq 191289 Reading mem 0
Mem# 0 errs 0: /u02/oradata/cgnsdata/redo02.log
Thu Jul 13 17:04:54 2006
Completed redo application
Thu Jul 13 17:04:54 2006
Completed crash recovery at
Thread 1: logseq 191289, block 3, scn 0.805790167
0 data blocks read, 0 data blocks written, 1 redo blocks read
Thu Jul 13 17:04:54 2006
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=4810
ARC0: Archival started
Thu Jul 13 17:04:54 2006
LGWR: STARTING ARCH PROCESSES COMPLETE
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thu Jul 13 17:04:54 2006
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1 started with pid=17, OS id=4812
ARC1: Archival started
ARC1: Becoming the heartbeat ARCH
Thu Jul 13 17:04:54 2006
Thread 1 advanced to log sequence 191290
Maximum redo generation record size = 120832 bytes
Maximum redo generation change vector size = 116476 bytes
Private_strands 7 at log switch
Thread 1 opened at log sequence 191290
Current log# 1 seq# 191290 mem# 0: /u02/oradata/cgnsdata/redo01.log
Successful open of redo thread 1
Thu Jul 13 17:04:54 2006
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jul 13 17:04:54 2006
ARC1: Evaluating archive log 2 thread 1 sequence 191289
Thu Jul 13 17:04:54 2006
ARC0: Evaluating archive log 2 thread 1 sequence 191289
Thu Jul 13 17:04:54 2006
SMON: enabling cache recovery
Thu Jul 13 17:04:54 2006
cgnsdata; ARC1: Beginning to archive log 2 thread 1 sequence 191289 (0.805770164:0.805790168)
Thu Jul 13 17:04:54 2006
ARC0: Unable to archive log 2 thread 1 sequence 191289
Thu Jul 13 17:04:54 2006
ARCH: Connecting to console port...
Thu Jul 13 17:04:54 2006
Log actively being archived by another process
Thu Jul 13 17:04:54 2006
cgnsdata; ARC1: Creating local archive destination LOG_ARCHIVE_DEST_10: '/u01/app/oracle/flash_recovery_area/CGNSDATA/archivelog/2006_07_13/o1_mf_1_191289_%u_.arc' (thread 1 sequence 191289)
Thu Jul 13 17:04:54 2006
ARCH: Connecting to console port...
db_recovery_file_dest_size of 37333 MB is 2.45% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Jul 13 17:04:54 2006
cgnsdata; ARC1: Closing local archive destination LOG_ARCHIVE_DEST_10: '/u01/app/oracle/flash_recovery_area/CGNSDATA/archivelog/2006_07_13/o1_mf_1_191289_2cdr8pf5_.arc'
ARCH: Connecting to console port...
Committing creation of archivelog '/u01/app/oracle/flash_recovery_area/CGNSDATA/archivelog/2006_07_13/o1_mf_1_191289_2cdr8pf5_.arc'
Created Oracle managed file /u01/app/oracle/flash_recovery_area/CGNSDATA/archivelog/2006_07_13/o1_mf_1_191289_2cdr8pf5_.arc
Thu Jul 13 17:04:54 2006
Errors in file /u01/app/oracle/admin/cgnsdata/udump/cgnsdata_ora_4802.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
Thu Jul 13 17:04:54 2006
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 4802
ORA-1092 signalled during: ALTER DATABASE OPEN...
13/07/2006, 20h48
pifor

La conjonction des 2 erreurs 1502 et 704 est inconnue de Metalink.

C'est peut-être un bug Oracle. Si vous avez un contrat de support Oracle, je vous conseille d'ouvrir l'incident sur Metalink:

Citation:

ORA-00704: bootstrap process failure
Cause: Failure in processing bootstrap data. Refer to the accompanying messages for more information about the cause of the problem.
Action: Correct the problems mentioned in the other messages. If the problem persists, contact Oracle Support Services.

Sinon, comme il s'agit d'un index dans le tablespace SYSTEM, je ne vois pas d'autre solution que de restaurer. Ce qui est dommage pour un index inutilisable mais comme l'index est dans le tablespace SYSTEM et que l'instance ne peut pas démarrer sans le tablespace SYSTEM ...:cry:

Je me demande aussi à quoi correspond le processus d'arrière plan 'USER': c'est lui qui détecte l'erreur et il ne semble pas documenté ...

Pouvez-vous poster le contenu du fichier de trace

Citation:

/u01/app/oracle/admin/cgnsdata/udump/cgnsdata_ora_4802.trc

?
14/07/2006, 08h29
fahenry

Hello ,

Merci en tout cas de te pencher sur mon probleme, je mets le trc que tu m' a demande

j' ai ouvert un case sur Metalink mais toujours pas reponse. si je recois une reponse de leur part je la partagerai sur le forum, Cela pourra toujours servir pour un autre malchanceux ;)

Citation:

/u01/app/oracle/admin/cgnsdata/udump/cgnsdata_ora_4802.trc
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1
System name: Linux
Node name: #####################
Release: 2.4.21-20.ELsmp
Version: #1 SMP Wed Aug 18 20:46:40 EDT 2004
Machine: i686
Instance name: cgnsdata
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 4802, image: ################# (TNS V1-V3)

*** SERVICE NAME:() 2006-07-13 17:04:53.977
*** SESSION ID:(272.3) 2006-07-13 17:04:53.977
attempting to start a parallel recovery with 3 processes
parallel recovery started with 3 processes
KCRP: using 357 overflow buffers per slave
Thread 1 chkpt rba 0x02eb39.00000002.0010 scn 0x0000.300713b5
Cache-low rba 0x02eb35.000041d2.0000
On-disk rba 0x02eb35.000041d2.0000 scn 0x0000.3005cc15
Thread 1 start rba 0x02eb39.00000002.0010 scn 0x0000.300713b5
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 0Kb in 0.04s => 0.00 Mb/sec
Longest record: 0Kb, moves: 0/1 (0%)
Longest LWN: 0Kb, moves: 0/1 (0%), moved: 0Mb
----------------------------------------------
----- 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
----------------------------------------------
*** 2006-07-13 17:04:54.149
KCRA: start recovery claims for 0 data blocks
*** 2006-07-13 17:04:54.149
KCRA: blocks processed = 0/0, claimed = 0, eliminated = 0
----- 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-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
14/07/2006, 15h46
fahenry

Reponse du support Metalink

Citation:

DATA COLLECTED
===============

The following are the files uploaded by customer for analyzing

ALERT LOG
-----------
File Name : alert_cgnsdata[1].log

ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
Thu Jul 13 17:04:54 2006
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 4802
ORA-1092 signalled during: ALTER DATABASE OPEN............

TRACE FILE
------------
File Name : /u01/app/oracle/admin/cgnsdata/udump/cgnsdata_ora_4802.trc

----- 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-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
.
ISSUE CLARIFICATION
====================
Customer getting the following error , during the database starup. (

ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable sta
te
.............

This leads to shutting down of database and the following error occurred.....

ORA-1092 signalled during: ALTER DATABASE OPEN.......

.
ISSUE VERIFICATION
===================
Verified the above issue by the alert log file (alert_cgnsdata[1].log)
Verified the above issue by the trace files (cgnsdata_ora_4802.trc)
.
CAUSE DETERMINATION
====================
This is a general error reported at startup when there is some problem during processing of bootstrap in
formation.

CAUSE JUSTIFICATION
====================
It appears that an index that is either corrupted or in an unusable state:

-->ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
.
POTENTIAL SOLUTION(S)
======================
- restore system datafile from cold backup and run a recovery
- rebuild the database and run an import

POTENTIAL SOLUTION JUSTIFICATION(S)
====================================
By analyzing Alert log and Trace file it indicates that it is a Data Dictionary problem ,with an unusable index on the
DEPENDENCY$ table.
Hence the above solution is justified.
.
SOLUTION / ACTION PLAN
=======================

There are two ways where you can solve this issue:

1.Restore system datafile from cold backup and run a recovery

2.Rebuild the database and run an import...

Donc ta 1ere analyse etait correcte restore de la db
17/07/2006, 14h17
orafrance

et il n'y a pas moyen de l'ouvrir et rebuilder l'index ? Genre en lançant le catalog.sql ?
19/07/2006, 10h25
fahenry

J'avoue que je n' ai pas essayé...

De plus apres un restore qui me dit succeed j' ai un message d' erreur après recover

Citation:

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u02/oradata/cgnsdata/system01.dbf'

J' ai 2 questions qui me viennent à l' esprit, lorsque l' on spécifie par exemple:

Citation:

...recover database until time"to_date('07/09/06 21:41:56','MM/DD/YY HH24:MI:SS')";
6> sql 'alter database open noresetlogs';
7> release channel'dev_0';
8> }

1. Y a t il une règle pour le time until ? si oui qu' elle est elle ? le timestamp du debut de backup ?

2. Puis je specifier ds ma commande de recovery quel Archivelog je prend ?

dsl pour ces questions mais je debute en dba (au depart je suis linux system... ) et je viens enfin de decider mon boss à me payer les formations idoines . Parce que l experience par l erreur... C' est bien mais je risque vite d' être vieux avant l' âge ;)
19/07/2006, 10h41
orafrance

pour restorer dans le passer, il faut que la date de sauvegarde des datafiles soient antérieures.

donc pour restaurer la base au 07/09/06 à 21:41:56, tu dois restaurer les fichiers sauvegardés avant cette heure.