View original (French)

Problème dans Oracle 10G

Solved
bilanabl -  
Hello,
I am trying to connect to my database but I keep getting this error
ERROR: ORA-01033: ORACLE initialization or shutdown in progress
I can't solve the problem
Configuration: Windows XP Internet Explorer 6.0

15 answers

  1. bilanabl Author
    I can connect with sys

    I executed shutdown immediate and startup open

    C:\>set ORACLE_SID=teste

    C:\>sqlplus /nolog

    SQL*Plus: Release 10.1.0.4.2 - Production on Fri Aug 21 22:33:17 2009

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

    SQL> connect SYS/soprep AS SYSDBA
    Connected.
    SQL> shutdown immediate
    ORA-01109: database not open

    Database dismounted.
    ORACLE instance stopped.
    SQL> startup open
    ORACLE instance started.

    Total System Global Area 566231040 bytes
    Fixed Size 789876 bytes
    Variable Size 166720140 bytes
    Database Buffers 398458880 bytes
    Redo Buffers 262144 bytes
    Database mounted.
    ORA-16038: log 2 sequence# 249 cannot be archived
    ORA-19809: limit exceeded for recovery files
    ORA-00312: online log 2 thread 1:
    'C:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTE\REDO02.LOG'

    SQL>

    But when I connect to sqlplus, it always shows me the same error
    ERROR: ORA-01033: ORACLE initialization or shutdown in progress
    6
    1. HostOfSeraphim Contributor
      There is no more space for the recovery files.

      Once connected, copy and paste the result of this command here:

      SELECT * FROM v$recovery_file_dest;

      --
      Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
      Internet connection: ADSL Orange 8M (Livebox)
      0
  2. bilanabl Author
    SQL> SELECT * FROM v$recovery_file_dest;

    NAME
    --------------------------------------------------------------------------------

    SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
    ----------- ---------- ----------------- ---------------
    C:\oracle\product\10.1.0\flash_recovery_area
    2147483648 2141157888 0 244
    2
    1. HostOfSeraphim Contributor
      OK, the limit is 2 GB and you are at 1.99 GB.

      Run the following command:

      ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=3221225472 SCOPE=BOTH

      then SHUTDOWN ABORT and STARTUP.

      --
      Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
      Internet Connection: ADSL Orange 8M (Livebox)
      0
  3. bilanabl Author
    Ok, my comic book is working very well thanks to you. I thank you for helping me and I'm sorry because I took a bit of your time.

    Please, can you explain to me what the problem was?

    Because I didn't quite understand these commands that you gave me.
    1
    1. HostOfSeraphim Contributor
      Glad to have been able to help you.

      The recovery files are histories of the modifications made to the database. Oracle relies on the parameter DB_RECOVERY_FILE_DEST_SIZE to limit the maximum size of these files: in your case, the write action exceeded the maximum file size and could not be completed, which caused the crash. The corrective query simply raised the max value to 3 GB instead of 2 GB, which resolved the issue.

      The query "SELECT * FROM v$recovery_file_dest;" allows you to check the current limit and current usage. In the last line, the first value corresponds to the limit, and the second to the current usage (values expressed in bytes).

      --
      Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
      Internet Connection: ADSL Orange 8M (Livebox)
      0
  4. HostOfSeraphim Contributor
    Connect as SYSDBA and perform an immediate shutdown, or an abort shutdown if necessary.

    --
    Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
    Internet Connection: ADSL Orange 8M (Livebox)
    0
    2. bilanabl Author
      I did
      SQL> connect/ as SySDBA

      ERROR:
      ORA-01031: insufficient privileges
      and yet I am the administrator of my PC.
      0
      1. HostOfSeraphim Contributor


        sqlplus /nolog
        connect / as SYSDBA

        --
        Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
        Internet Connection: ADSL Orange 8M (Livebox)
        0
    3. bilanabl Author
      I did exactly the same thing

      C:\>sqlplus/nolog

      SQL*Plus: Release 10.1.0.4.2 - Production on Fri Aug 21 21:53:20 2009

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

      SQL> connect / as SYSDBA
      ERROR:
      ORA-01031: insufficient privileges
      0
      1. HostOfSeraphim Contributor
        OK.

        Do you have the SYS password, or not?

        --
        Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
        Internet connection: ADSL Orange 8M (Livebox)
        0
    4. bilanabl Author
      The BD field needs to have the name of my database
      and it’s not working

      however, I have another database and it works very well.
      0
      1. HostOfSeraphim Contributor
        I didn't understand everything...

        --
        Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
        Internet connection: ADSL Orange 8M (Livebox)
        0
    5. bilanabl Author
      I want to say that I have two comics, one works well and the other has this problem.
      0
      1. HostOfSeraphim Contributor
        Do you make sure to specify the correct ORACLE_SID before connecting?

        --
        Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
        Internet Connection: ADSL Orange 8M (Livebox)
        0
    6. bilanabl Author
      No, I don't believe it

      but how to do it
      0
      1. HostOfSeraphim Contributor
        What are the names of your Oracle instances?

        --
        Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
        Internet Connection: ADSL Orange 8M (Livebox)
        0
    7. bilanabl Author
      TEST that does not work and EVENT that works well
      0
      1. HostOfSeraphim Contributor
        Is it a Windows or Unix machine?

        --
        Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
        Internet connection: ADSL Orange 8M (Livebox)
        0
    8. bilanabl Author
      fenêtres
      0
      1. HostOfSeraphim Contributor
        OK.

        So in your Windows console:

        set ORACLE_SID=XXXX
        sqlplus /nolog
        connect / as SYSDBA

        (XXXX being the instance you want to connect to)

        Then, if you want to connect to another instance:

        set ORACLE_SID=YYYY
        sqlplus /nolog
        connect ...

        --
        Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
        Internet connection: ADSL Orange 8M (Livebox)
        0
    9. bilanabl Author
      the same problem

      C:\>set ORACLE_SID=test

      C:\>sqlplus /nolog

      SQL*Plus: Release 10.1.0.4.2 - Production on Fri Aug 21 22:23:11 2009

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

      SQL> connect / as SYSDBA
      ERROR:
      ORA-01031: insufficient privileges

      SQL>
      0
      1. HostOfSeraphim Contributor
        I don't have a machine with Oracle to test, too bad.

        Otherwise, do you not have the SYS password?

        --
        Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
        Internet Connection: ADSL Orange 8M (Livebox)
        0
    10. bilanabl Author
      the password I gave to sys during the installation of my DB
      yes I do
      0
      1. HostOfSeraphim Contributor
        In this case:

        set ORACLE_SID=XXXX
        sqlplus /nolog
        connect SYS/MOTDEPASSE AS SYSDBA

        --
        Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
        Internet connection: ADSL Orange 8M (Livebox)
        0
    12. labiko445
      Stop tiring yourselves, the error is very clear that he does not have the privileges to connect as Sys, so all you have to do is assign him the resources and then connect with grant
      But it's still a bit weird, normally Sys has all the rights by default.
      0
      1. HostOfSeraphim Contributor
        You know, in the 5 years since this issue was addressed, we've had time to rest...

        And if you reread the thread of the conversation, you'll see that no, it wasn't just a simple issue of privileges.
        0