ORA-12528: TNS:listener: all appropriate instances are blocking new connections
If this error message is still displayed a few minutes after trying to start the instance, check if there is no problem starting up the instance.
You might want to check for invalid database parameters, missing control files, database corruption, ...
The error is typically displayed when the database is in NOMOUNT or RESTRICTED state.
You can check the services of the listener to see if the service is in BLOCKED state:
SQL> set ORACLE_SID=STBY
SQL> startup nomount
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1332552 bytes
Variable Size 222300856 bytes
Database Buffers 33554432 bytes
Redo Buffers 6451200 bytes
C:\>lsnrctl services
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production
Service "STBY" has 2 instance(s).
Instance "STBY", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:59
LOCAL SERVER
Instance "stby", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "STBY_XPT" has 1 instance(s).
Instance "stby", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
In order to resolve this problem, you can add the new TNS connect string (UR = A) to the tnsnames entry.
STBY=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STBY)
(UR = A)
)
)
Welcome to our forum for Oracle error: ORA-12528 Add your own message
when i try to connect to the database, i received this error : ORA-12528: TNS:listener: all appropriate instances are blocking new connections. how can i clear it..
You have to startup your database. If this is unsuccessful, check for errors in the alert.log or oradim.log files
(2)ERROR:ORA-12528: TNS:listener: all appropriate instances are blocking new connections
(3)In isql*plus :ERROR - ORA-12560: TNS:protocol adapter error
(4)my enterprise manager is not working
i cannot give the shutdown command on sqlplus,it gives not responding and then hanging,when i
close and open it again then it issues the error-12528,and says all connections are blocked by
instances.
Did you already try with a shutdown abort?
If this does not work, than you will have to kill the smon process on the Unix operating system, or kill the correct oracle.exe process on Windows
I have drop tablespaces and delete ORA files from oracle/database folder. After that I could not connect database as a system/manager@sid.
When I try to Login "ORA-01033: ORACLE initialization or shutdown in progress message" comes
When i startup th databse,
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.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'E:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\SHARES.ORA'
message comes,
How did you drop the tablespace? Was it with the drop tablespace name including contents and datafiles; command?
Did the file mentioned ('E:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\SHARES.ORA') belongs to this tablespace?
ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [9761], [9760], [0x0], [], [], [], []
the database is mounted and i have tried to shutit down thru SQLPLUS (CMD mode) and startup mount the database still the same problem. and i have tried the above mentioned solution again it did not work, By adding the (UR=A) to the Tnsnames.ora file as mentioned. still the same.
Could any one help me solve this problem?
Thanks in advance.
This message indicates that there is a problem with (one of the) controlfiles.
Are these controlfiles multiplexed? If so, take a backup of all control files and copy over the correct one.
If not, you will need to recreate the controlfile, or restore from backup and perform recovery
firstly error ora-12528 was coming and i resolved it by giving UR=A in tnsnames.ora file. But when i tried starting application it gave error ORA-01033 and it continues even after restarting of the opracle databse again..Wht shud be done to resolve this?
http://www.dbmotive.com/oracle_error_codes.php?errcode=1033&type=ORA Your database is not able to startup, you need to check oradim.log (On Windows) and check the alert log as well. It will contain error messages as to why Oracle is unable to startup
Thanks for the help Sir. I am getting following error msg in oradim.log file in Windows. Please let me know what shud I do to resolve this?
Wed Jun 09 10:40:04 2010
E:\oracle\product\10.1.0\Db_2\bin\oradim.exe -startup -sid orcl -usrpwd * -log oradim.log -nocheck 0
ORA-00204: error in reading (block 3, # blocks 8) of controlfile
ORA-00202: controlfile: 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL'
ORA-27091: unable to queue I/O
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 23) Data error (cyclic redundancy check).
It looks like your control file is corrupt.
Go to directory E:\oracle\product\10.1.0\Db_2\database and check spfileorcl.ora
An entry will exist for control_files, which probably contain more than 1 control file.
You will need to make a backup of the failing controlfile (E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL) and copy over one of the other controlfile (I'm guessing E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL)
After that try to restart your database
But make sure you have a backup of the files
Sir,
By backup ..you mean back up of whole database or wht??
Waiting for your reply,
Make a backup of the control files you see in parameter control_files which is in the spfile
yes, there r more than 1 control files in spfile. how to make the back up of control files??
You can just do ctrl+c ctrl+v of the file in Windows explorer
and wht do u mean by copy over other file control file..shud i copy the content of control3.ctl to control2.ctl??
No, control3 is the file which is causing the error and is probably corrupt.
You need to copy the other file (control2.ctl?) over control3.ctl
do all control files contain same info?
They should yes. But you keep multiple copies so you can recover from a crash in case one became corrupt, as you are facing now with control3.ctl
I have done as you said..
Following error is still coming in oradim.log(Windows):
Wed Jun 09 16:04:32 2010
E:\oracle\product\10.1.0\Db_2\bin\oradim.exe -startup -sid orcl -usrpwd * -log oradim.log -nocheck 0
Wed Jun 09 16:06:05 2010
ORA-00205: error in identifying controlfile, check alert log for more info
What should I do now?
Waiting for your reply,
Thanks and Regards
Did you copy over the file control2.ctl and rename it to control3.ctl?
Are all files that are identified by the control_files spfile parameter available?
It looks like one of the files is either missing or have an incorrect name
No, I copy the content of control02.ctl and pasted it in control03.ctl. Yes, there are 3 control files mentioned in spfile and they r available in the folder orcl..
waiting for your reply
You should copy the file, not the content of the file.
Controlfiles are binary files, you cannot copy the content
ok..now if i copy the control02.ctl and rename it..will it work fine?
If control2.ctl was not corrupt as well (they are on a different filesystem,right? ) you should be able to start the OracleServiceORCL now.
different file system means?..They all r in same folder orcl..
Normally you put multiple copies of your controlfile (and redolog files) on different drives.
If one drive crashes, you can still get the backup of another drive.
This is clearly not the case in your setup.
Are you able to startup the database service?
ok..
no Sir, I m not able to start database services(Start up SQL) also. Its giving same error msg 12528..
What is inside the oradim.log?
Did you remove the (UR=A) parameter?
No SIr, I have not removed it...Shud I remove this line?
No, what does the oradim.log tell you?
Following error is still coming in oradim.log(Windows):
Wed Jun
09 16:04:32 2010
E:\oracle\product\10.1.0\Db_2\bin\oradim.exe
-startup -sid orcl -usrpwd * -log oradim.log -nocheck 0
Wed Jun 09
16:06:05 2010
ORA-00205: error in identifying controlfile, check
alert log for more info
What should I do now?
As the message says, you should check your alertORCL.log file.
At the end, it will contain error messages saying what is wrong.
Probably it complains about a missing file, check if that file exist
Sir, Checked alert_orcl.log file. Its giving following error at the end:
Wed Jun 09 16:12:56 2010
ORA-00202: controlfile: 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL'
ORA-27046: file size is not a multiple of logical block size
OSD-04000: logical block size mismatch (OS 512)
Wed Jun 09 16:12:56 2010
Controlfile identified with block size 16384
Wed Jun 09 16:12:59 2010
ORA-205 signalled during: alter database mount exclusive...
Please let me know the solution..
Thanks for the help so far ..
Regards,
Rashmi
Probably there was a problem copying the control file.
Can you open a dos box (command prompt) and do:
copy E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL
After that start your OracleServiceORCL.
If it still fails, check the oradim.log and alert.log again.
Thanks Sir for your valuable support and timely help. I am able to resolve the erroe and the Application is working very fine now.
Thanks once again..
Sir, Is there any way to increase the speed of the server (windows 2K) where oracle is installed. The machine is going very slow.
Looking forward to your reply,
Thanks and Regards,
Rashmi
Performance tuning is more than just a few tips I'm afraid.
You need to look at bad performing statements (missing indexes, incorrect statistics data, ...), memory sizing, datafile locations and distribution, ...
There are so many things to look at...
Under Your guidance I can take things one by one..if thats possible?
Looking forward to your reply,
Thanks and Regards,
Rashmi
I'm sorry, that's something we cannot do as it requires a lot of time
If you lack knowledge about performance tuning, you can hire someone to do a health and performance checkup
Ok..Thanks for the reply..
Hello,
I got a problem in my harddrive so I needed to format everything, but before doing this, I copied the directory "oradata" with all the files inside.
After installing again the OS, I installed Oracle and tried to override the directory "oradata" with my old directory, and i got this message: "All appropiate instances...".
Could you please help me on how to recover this database from the files?
I'm using the same path than the previous installation.
Thank you in advance
is this a Windows pc? If so, check the content of file %ORACLE_HOME%/database/oradim.log for errors.
Also check your alert.log file for errors
Probably your database could not be started due to an error, such as a missing file, ...
Your oradim.log or alert.log should tell you what's wrong
Add your message
If you need more information about this particular error message, you can leave a forum message.
We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.
Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.
In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.
If you do not enter the validation correctly, your message will not be recorded.