troubleshooting Question

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Avatar of BAYEXPERTZ
BAYEXPERTZFlag for Canada asked on
Oracle Database
18 Comments1 Solution20279 ViewsLast Modified:
I am using Oracle 11g on Windows Xp Service Pack 2. After restarting my computer and then loading up the database control I get the following error under the Agent connection to Instance:
Status  Failed
Details  ORA-12528: TNS:listener: all appropriate instances are blocking new connections (DBD ERROR: OCIServerAttach)
I have tried to bounce the listener but it never worked using:
LSNRCTL> stop
LSNRCTL> start
Then restart your instance: sqlplus with sysdba privileges
SQL> startup
Also I have tried to do a shutdown immediate through RMAN and received the following:
Recovery Manager: Release 11.1.0.6.0 - Production on Wed Jan 7 08:42:05 2009

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

RMAN> SHUTDOWN IMMEDIATE

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 01/07/2009 08:45:15
RMAN-06171: not connected to target database

And then when I try to connect  using the SYS account I get the following:

RMAN> connect target SYS/PASSWORD@orcl

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

***I have tried restarting the computer many times without any luck, Contents of alert log below:

Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile C:\APP\TEST\PRODUCT\11.1.0\DB_1\DATABASE\SPFILEORCL.ORA
System parameters with non-default values:
  processes                = 200
  memory_target            = 392M
  control_files            = "C:\APP\TEST\ORADATA\ORCL\CONTROL01.CTL"
  control_files            = "C:\APP\TEST\ORADATA\ORCL\CONTROL02.CTL"
  control_files            = "C:\APP\TEST\ORADATA\ORCL\CONTROL03.CTL"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  log_archive_dest_1       = "LOCATION=C:\Flash_Recovery_Area MANDATORY REOPEN=300 QUOTA_SIZE=5000"
  log_archive_dest_10      = "LOCATION=USE_DB_RECOVERY_FILE_DEST OPTIONAL REOPEN=300"
  db_recovery_file_dest    = "C:\Flash_Recovery_Area"
  db_recovery_file_dest_size= 25G
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
  audit_file_dest          = "C:\APP\TEST\ADMIN\ORCL\ADUMP"
  audit_trail              = "DB"
  db_name                  = "orcl"
  open_cursors             = 300
  diagnostic_dest          = "C:\APP\TEST"
Wed Jan 07 09:01:13 2009
PMON started with pid=2, OS id=6512
Wed Jan 07 09:01:13 2009
VKTM started with pid=3, OS id=5200 at elevated priority
VKTM running at (20)ms precision
Wed Jan 07 09:01:13 2009
DIAG started with pid=4, OS id=7852
Wed Jan 07 09:01:13 2009
DBRM started with pid=5, OS id=7076
Wed Jan 07 09:01:13 2009
PSP0 started with pid=6, OS id=6932
Wed Jan 07 09:01:13 2009
DSKM started with pid=7, OS id=7728
Wed Jan 07 09:01:13 2009
DIA0 started with pid=8, OS id=6352
Wed Jan 07 09:01:13 2009
MMAN started with pid=7, OS id=6796
Wed Jan 07 09:01:13 2009
DBW0 started with pid=9, OS id=3192
Wed Jan 07 09:01:13 2009
LGWR started with pid=10, OS id=5824
Wed Jan 07 09:01:13 2009
CKPT started with pid=11, OS id=3792
Wed Jan 07 09:01:13 2009
SMON started with pid=12, OS id=6460
Wed Jan 07 09:01:13 2009
RECO started with pid=13, OS id=7588
Wed Jan 07 09:01:13 2009
MMON started with pid=14, OS id=3512
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Jan 07 09:01:13 2009
MMNL started with pid=15, OS id=7148
starting up 1 shared server(s) ...
Oracle Data Guard is not available in this edition of Oracle.
ORACLE_BASE from environment = C:\app\TEST
Wed Jan 07 09:01:13 2009
ALTER DATABASE   MOUNT
Setting recovery target incarnation to 2
Errors in file c:\appTEST\diag\rdbms\orcl\orcl\trace\orcl_ora_5764.trc:
ORA-01507: database not mounted
ORA-1507 signalled during: ALTER DATABASE   MOUNT...

Contents of orcl_ora_5764.trc:see below

Trace file c:\app\TEST\diag\rdbms\orcl\orcl\trace\orcl_ora_5764.trc
Oracle Database 11g Release 11.1.0.6.0 - Production
Windows XP Version V5.1 Service Pack 2
CPU                 : 2 - type 586
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:1123M/2004M, Ph+PgF:2229M/3314M, VA:1437M/2047M
Instance name: orcl
Redo thread mounted by this instance: 0 <none>
Oracle process number: 18
Windows thread id: 5764, image: ORACLE.EXE (SHAD)


*** 2009-01-07 09:01:19.611
*** SESSION ID:(225.3) 2009-01-07 09:01:19.611
*** CLIENT ID:() 2009-01-07 09:01:19.611
*** SERVICE NAME:() 2009-01-07 09:01:19.611
*** MODULE NAME:(sqlplus.exe) 2009-01-07 09:01:19.611
*** ACTION NAME:() 2009-01-07 09:01:19.611
 
ORA-01507: database not mounted



Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 18 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros