7 Replies Latest reply: Feb 15, 2013 7:18 PM by 991440 RSS

    Error 12528, instance BLOCKED, and ORA-01033 error

    927049
      I'm a student, just getting started with Oracle, and my teacher is not very helpful with the install/running problems that I'm having with Oracle.

      I installed Oracle 11g Express a week ago, and upon installing it, the software worked just fine. I was able to create connections and write basic select statements to get data from a database provided by the manufacturer of my textbook.

      Now, when I try to connect to the database, I get an error that says "ORA_12528, TNS:listener: all appropriate instances are blocking new connections."

      I did a little research on how to fix this, and got into the listener to see its status. Here's what it says:

      LSNRCTL> status
      Connection to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
      STATUS of the LISTENER
      ------------------------
      Alias LISTENER
      Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
      Start Date 28-MAR_2012 09:45:21
      Uptime 0 days 0 hr. 21 min. 19 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Default Service XE
      Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
      Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\PC325862970629\listener\alert\log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PC325862970629)(PORT=1521)
      Services Summary...
      Service "CLRExtProc" has 1 instance(s).
      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Service "xe" has 1 instance(s).
      Instance"xe", status BLOCKED, has 1 handler(s) for this service...
      This command completed successfully
      LSNRCTL>

      I also looked at the Listener Log File (although most of what I saw makes no sense to me, and it was pretty long)... here's what appears to be the last chunk of the log file:
      <msg time='2012-03-28T09:45:23.468-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>Listener completed notification to CRS on start
      </txt>
      </msg>
      <msg time='2012-03-28T09:45:23.484-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>
      TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
      </txt>
      </msg>
      <msg time='2012-03-28T09:46:01.203-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PC325862970629)(PORT=1521)))
      </txt>
      </msg>
      <msg time='2012-03-28T09:46:01.218-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>28-MAR-2012 09:46:01 * service_register * xe * 0
      </txt>
      </msg>
      <msg time='2012-03-28T09:46:02.796-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>28-MAR-2012 09:46:02 * service_update * xe * 0
      </txt>
      </msg>
      <msg time='2012-03-28T09:49:38.250-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>28-MAR-2012 09:49:38 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=Admin))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1047)) * establish * xe * 12528
      </txt>
      </msg>
      <msg time='2012-03-28T09:49:38.750-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>TNS-12528: TNS:listener: all appropriate instances are blocking new connections
      </txt>
      </msg>
      <msg time='2012-03-28T09:56:04.218-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>28-MAR-2012 09:56:04 * service_update * xe * 0
      </txt>
      </msg>
      <msg time='2012-03-28T10:05:35.343-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>28-MAR-2012 10:05:35 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=Admin))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1147)) * establish * xe * 12528
      </txt>
      </msg>
      <msg time='2012-03-28T10:05:35.812-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>TNS-12528: TNS:listener: all appropriate instances are blocking new connections
      </txt>
      </msg>
      <msg time='2012-03-28T10:06:40.453-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>28-MAR-2012 10:06:40 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Admin))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0
      </txt>
      </msg>
      <msg time='2012-03-28T10:26:03.890-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>28-MAR-2012 10:26:03 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Admin))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0
      </txt>
      </msg>
      <msg time='2012-03-28T10:26:14.968-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>28-MAR-2012 10:26:14 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=Admin))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1164)) * establish * xe * 12528
      </txt>
      </msg>
      <msg time='2012-03-28T10:26:15.062-07:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='PC325862970629'
      host_addr='10.10.10.10'>
      <txt>TNS-12528: TNS:listener: all appropriate instances are blocking new connections
      </txt>
      </msg>


      After doing more googling, I found someone suggested that logging on to SQL*Plus as sysdba and then shutting down and restarting the database might do something to fix this, but when I try to log in it doesn't work... here's what I see in SQL*Plus:

      SQL> connect / as sysdba
      ERROR:
      ORA-28056: Writing audit records to Windows Event Log failed
      OSD-196787312: Message 196787312 not found; product=RDBMS; facility=SOSD
      O/S-Error: (OS 1502) The event log file is full.
      ORA-01075: you are currently logged on

      SQL> connect
      Enter User0name: sysdba
      Enter password:
      ERROR:
      ORA-01033: ORACLE initialization or shutdown in progress
      Process ID: 0
      Session ID: 0 Serial number: 0

      SQL>

      I have no idea what's going on here, and unfortunately, since I'm a student and have just started with Oracle, I can't seem to make heads or tails of most of what I'm finding on the forums here (and most of the threads that seem pertinent are 6 or more years old).

      If anyone out there can help me, I'd greatly appreciate it! Thanks in advance!

      --Kate                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
        • 1. Re: Error 12528, instance BLOCKED, and ORA-01033 error
          sb92075
          open up Command Window & type line for line as below

          sqlplus
          / as sysdba
          select status from v$instance;
          exit


          COPY command & results then PASTE all back here
          • 2. Re: Error 12528, instance BLOCKED, and ORA-01033 error
            Srini Chavali-Oracle
            The issue seems to be

            >
            ...
            SQL> connect / as sysdba
            ERROR:
            ORA-28056: Writing audit records to Windows Event Log failed
            OSD-196787312: Message 196787312 not found; product=RDBMS; facility=SOSD
            O/S-Error: (OS 1502) The event log file is full.
            ORA-01075: you are currently logged on
            ...
            >

            You will need to make changes to the event log settings/properties (delete the log, and/or make it bigger in size, and/or allow for overwrites etc)

            unable o connect database

            HTH
            Srini
            • 3. Re: Error 12528, instance BLOCKED, and ORA-01033 error
              927049
              sb92075 -

              I started the DB before doing what you suggested. According to my computer, after entering "net start OracleServiceXE" the service was started successfully. Then I tried what you suggested and here's what I got:

              Microsoft(R) Windows DOS
              (C)Copyright Microsoft Corp 1990-2001.

              C:\DOCUME~1\ADMIN>sqlplus

              SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 28 17:41:26 2012

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

              Enter user-name: / as sysdba

              Connected to:
              Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

              SQL> select status from v$instance;

              STATUS
              ------------
              OPEN

              SQL> exit
              Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Produ
              ction

              C:\DOCUME~1\ADMIN>

              I have no idea what this tells me, so hopefully it means something to you... ideas?

              Edited by: 924046 on Mar 28, 2012 5:56 PM
              • 4. Re: Error 12528, instance BLOCKED, and ORA-01033 error
                927049
                Srini -

                I did take a look at the link you posted and I tried to do what it said, but I don't really know what to make of what I found. When I run services.msc I find the following are running:
                OracleJobSchedulerXE, OracleMTSRecoveryService, OracleServiceXE, OraclexEClrAgent, and OracleXETNSLisnener
                So it appears that the Oracle Service is running... probably not the problem.

                With regards to the logs, I don't know how to do what you suggested (make the log bigger, allow for overwrites, etc.).

                I tried to follow the suggestions from the link you posted and get into the event viewer, but when I enter "eventviewer" in the run window my machine can't find it. I tried other methods I found online to get into the event viewer, but it appears as though my computer doesn't offer me access to it (or simply doesn't have that capability). Are there other ways to clear old logs? Can I just open it as a text file and delete the contents? I don't want to make a mess out of things, and I really don't know what I'm doing so any guidance would be appreciated.

                Thanks!

                --Kate                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                • 5. Re: Error 12528, instance BLOCKED, and ORA-01033 error
                  Srini Chavali-Oracle
                  Please identify your exact OS version. On my Windows XP machine, I use Start > Control Panel > Administrative Tools > Event Viewer, then single left-click the Application (or System) log to select it, then single right-click and select Properties, and can adjust the properties and size on the resulting window.

                  If you are now able to connect "/ as sysdba" as you demonstrated, the issue may have been resolved (or resolved itself). Are you now able to use the database/application ?

                  HTH
                  Srini
                  • 6. Re: Error 12528, instance BLOCKED, and ORA-01033 error
                    927049
                    That is weird. I don't feel like I changed anything (never changed anything with the log files), but it seems to be working now. Thanks to you both for your help...

                    Does this mean that every time I want to connect to a DB in Oracle I need to go to the command prompt and log in as sysdba to make it work?
                    • 7. Re: Error 12528, instance BLOCKED, and ORA-01033 error
                      991440
                      I have been searching for this fix for almost a day, simply I had XE running on my WinXP computer for days with no issue. I was under the impression the issue was because my IP changed and spent alot of time in the tns area. All I did was clear the windows event logs stopped all services and restarted and I was ready to throw my computer thru the window, why the heck do they make things so difficult, thanks for taking the time for this post@