IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Oracle Discussion :

ORA-01092: ORACLE instance terminated. Disconnection forced


Sujet :

Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Juillet 2006
    Messages
    6
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Juillet 2006
    Messages : 6
    Par défaut 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

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

  2. #2
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    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:

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

    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
    /u01/app/oracle/admin/cgnsdata/udump/cgnsdata_ora_4802.trc
    ?

  3. #3
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Juillet 2006
    Messages
    6
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Juillet 2006
    Messages : 6
    Par défaut
    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 ;)


    /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

  4. #4
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Juillet 2006
    Messages
    6
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Juillet 2006
    Messages : 6
    Par défaut
    Reponse du support Metalink

    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

  5. #5
    Expert éminent
    Avatar de orafrance
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 48
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 15 967
    Par défaut
    et il n'y a pas moyen de l'ouvrir et rebuilder l'index ? Genre en lançant le catalog.sql ?

  6. #6
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Juillet 2006
    Messages
    6
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Juillet 2006
    Messages : 6
    Par défaut
    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

    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:

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

Discussions similaires

  1. ORA-01092 lors du lancement du instance
    Par Eric BERNARD dans le forum Oracle
    Réponses: 4
    Dernier message: 18/03/2009, 15h11
  2. [ORA-01092] : Instance oracle terminée. Déconnexion forcée.
    Par bencot dans le forum Connexions aux bases de données
    Réponses: 1
    Dernier message: 06/11/2008, 16h41
  3. ORA-01092:instance ORACLE interrompue. DÚconnexion imposÚe
    Par bestsuan dans le forum Administration
    Réponses: 9
    Dernier message: 09/09/2008, 11h48
  4. Oracle 9.2 Windows 2003 instance terminated
    Par ducho dans le forum Administration
    Réponses: 0
    Dernier message: 02/04/2008, 14h50
  5. Réponses: 4
    Dernier message: 11/02/2006, 17h55

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo