Search This Blog

Monday, February 19, 2007

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

ORA-12528: TNS:listener: all appropriate instances are blocking new connections
It is known issue with ORACLE 10G.

Suggestion: Don't Use "@(tns_entry string)" with userid/pwd.
just export or set ORACLE_SID and connect WITHOUT "@"tns_entry.




SQL> grant SYSDBA to system;

Grant succeeded.

SQL> conn system/oracle@oracle as sysdba
Connected.

SQL> STARTUP FORCE
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Feb 19 17:13:20 2007

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

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> conn system as sysdba
Enter password:
Connected.
SQL> SHOW PARAMETER DB_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORACLE
SQL> STARTUP FORCE
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL>


hope this helps
Taj

1 comment:

Anonymous said...

EXCELLENT...THANK YOU.

SRIRAM