SQL> set ORACLE_SID=ORCL
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 "ORCL" has 2 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:59
LOCAL SERVER
Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "ORCL_XPT" has 1 instance(s).
Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Want Solution Do the following :
you can add the new TNS connect string (UR = A) to the tnsnames entry.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
(UR = A)
)
)
if the above Solution not working Make sure your listener look like :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\oraserver\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\oraserver\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME=orcl)
(ORACLE_HOME=D:\app\oraserver\product\11.2.0\dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
Thank you
Osama Mustafa
Like this:
Like Loading...
Related
Published by Osama Mustafa
Osama considered as one of the leaders in Cloud technology, DevOps and database in the Middle-East. I have more than ten years of experience within the industry. moreover, certfied 4x AWS , 4x Azure and 6x OCI, have also obtained database certifications for multiple providers.
In addition to having experience with Oracle database and Oracle products, such as middle-ware, OID, OAM and OIM, I have gained substantial knowledge with different databases.
Currently, I am architecting and implementing Cloud and DevOps. On top of that, I'm providing solutions for companies that allow them to implement the solutions and to follow the best practices.
View all posts by Osama Mustafa
no luck buddy… still the same…
LikeLike
Can you please post the alert log if you want i will help you with this problem since listener problem have more than one solution
LikeLike
Hi Osama,I tried the proposed solution. but it dint worked out. I am getting following errors at command prompt:ORA-12528: TNS:listener: all appropriate instances are blocking new connections.ORA-24327: need explicit attach before authenticating a user.Thanks for your time,
LikeLike
are you have change the ORACLE_SID in Listener.ora and tnsnames.oraPlease restart your listener to fix the issue.Also Check sqlnet.ora if its located $ORACLE_HOME/network/admin
LikeLike
thank you Osama, your tip helped me !!!
LikeLike
Welcome
LikeLike
Hello Osama,I tried your suggestion but still I am getting the error:LSNRCTL> statusConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))STATUS of the LISTENER————————Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 – ProductionStart Date 07-NOV-2012 17:36:15Uptime 0 days 0 hr. 5 min. 19 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /opt/oracle/112/network/admin/listener.oraListener Log File /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xmlListening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))Services Summary…Service “ORCL” has 1 instance(s). Instance “ORCL”, status BLOCKED, has 1 handler(s) for this service…The command completed successfullyThis is blocking me to configure Oracle Identity Management. The installation failed at the endwith the following error. I think this because of the Blocking of listener.Component Schema=SOA InfrastructureDriver=oracle.jdbc.xa.client.OracleXADataSourceURL=jdbc:oracle:thin:@localhost.localdomain:1521/orclUser=DEV_SOAINFRAPassword=***********SQL Test=select 1 from schema_version_registry where owner=(select user from dual) and mr_type='SOAINFRA' and version='11.1.1.5.0'Listener refused the connection with the following error:ORA-12528, TNS:listener: all appropriate instances are blocking new connections CFGFWK-60850: Test Failed!I am installing Oracle Identity Management 11.1.1.5 on RedHat Liux 5.Please help me and let me know what data you need.Thanks,-arshad
LikeLike
Hi Arshad IqbalCould you please post :show parameter aq_tm_processes
LikeLike
SQL> show parameter aq_tm_processes;NAME TYPE VALUE———————————— ———– ——————————aq_tm_processes integer 0Thanks,-Arshad
LikeLike
ALTER SYSTEM SET AQ_TM_PROCESSES=1;try after change it . if its not working back original value for this parameter .
LikeLike
Hi Osama,I did that and now I am getting a different error when starting the db. Also getting the same error when doing checking the “status” from “lsnrctl”SQL> startupORACLE instance started.LSNRCTL> statusConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))STATUS of the LISTENER————————Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 – ProductionStart Date 12-NOV-2012 10:26:46Uptime 0 days 0 hr. 1 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /opt/oracle/112/network/admin/listener.oraListener Log File /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xmlListening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))Services Summary…Service “ORCL” has 1 instance(s). Instance “ORCL”, status BLOCKED, has 1 handler(s) for this service…Service “orcl.localdomain” has 1 instance(s). Instance “orcl”, status READY, has 1 handler(s) for this service…Service “orclXDB.localdomain” has 1 instance(s). Instance “orcl”, status READY, has 1 handler(s) for this service…The command completed successfullyTotal System Global Area 267227136 bytesFixed Size 2212496 bytesVariable Size 205524336 bytesDatabase Buffers 54525952 bytesRedo Buffers 4964352 bytesORA-01102: cannot mount database in EXCLUSIVE mode
LikeLike
Sorry my last comment is mixed up:SQL> startupORACLE instance started.Total System Global Area 267227136 bytesFixed Size 2212496 bytesVariable Size 205524336 bytesDatabase Buffers 54525952 bytesRedo Buffers 4964352 bytesORA-01102: cannot mount database in EXCLUSIVE mode
LikeLike
First seems the above solution is working with you since lsnrctl status appear that database is register with your listener.
LikeLike
Please post the following : ps -ef|grep pmon Post the last 50 lines in alertlog please
LikeLike
[oracle@localhost trace]$ ps -ef | grep pmon oracle 9037 1 0 10:27 ? 00:00:00 ora_pmon_ORCLoracle 12325 1 0 Nov09 ? 00:00:06 ora_pmon_orcloracle 14653 7613 0 11:37 pts/1 00:00:00 grep pmon[root@localhost Desktop]# cd /opt/oracle/diag/rdbms/orcl/ORCL/trace[root@localhost trace]# tail -50 alert_ORCL.log remote_login_passwordfile= “EXCLUSIVE” db_domain = “” dispatchers = “(PROTOCOL=TCP) (SERVICE=ORCLXDB)” audit_file_dest = “/opt/oracle/admin/orcl/adump” audit_trail = “DB” db_name = “ORCL” open_cursors = 300 diagnostic_dest = “/opt/oracle”Mon Nov 12 10:27:12 2012PMON started with pid=2, OS id=9037 Mon Nov 12 10:27:12 2012VKTM started with pid=3, OS id=9041 at elevated priorityVKTM running at (10)millisec precision with DBRM quantum (100)msMon Nov 12 10:27:12 2012GEN0 started with pid=4, OS id=9047 Mon Nov 12 10:27:12 2012DIAG started with pid=5, OS id=9051 Mon Nov 12 10:27:12 2012DBRM started with pid=6, OS id=9055 Mon Nov 12 10:27:13 2012PSP0 started with pid=7, OS id=9059 Mon Nov 12 10:27:13 2012DIA0 started with pid=8, OS id=9063 Mon Nov 12 10:27:13 2012MMAN started with pid=9, OS id=9067 Mon Nov 12 10:27:13 2012DBW0 started with pid=10, OS id=9071 Mon Nov 12 10:27:13 2012LGWR started with pid=11, OS id=9075 Mon Nov 12 10:27:13 2012CKPT started with pid=12, OS id=9079 Mon Nov 12 10:27:13 2012SMON started with pid=13, OS id=9083 Mon Nov 12 10:27:13 2012RECO started with pid=14, OS id=9087 Mon Nov 12 10:27:13 2012MMON started with pid=15, OS id=9091 Mon Nov 12 10:27:13 2012MMNL started with pid=16, OS id=9095 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'…starting up 1 shared server(s) …ORACLE_BASE from environment = /opt/oracleMon Nov 12 10:27:14 2012ALTER DATABASE MOUNTsculkget: failed to lock /opt/oracle/112/dbs/lkORCL exclusivesculkget: lock held by PID: 12359ORA-09968: unable to lock fileLinux-x86_64 Error: 11: Resource temporarily unavailableAdditional information: 12359ORA-1102 signalled during: ALTER DATABASE MOUNT…
LikeLike
Like i expected Do the following :kill -9 9037 kill -9 12325 then 1-After that fire export ORACLE_SID= (watch case sensitive ) 2-sqlplus / as sysdba3-startup ;
LikeLike
Thanks a lot Osama, it worked and I have successfully installed Oracle Identity Management.Much appreciated.Thanks,-Arshad
LikeLike
Pleasure i could help 🙂
LikeLike
Hello Osama,I need your help again as I am getting the same issue when I restarted my Linux machine.I am not sure why there are two instances of Oracle SI, one “ORCL” and another “orcl”.Please let me know how to resolve this issue permanently.When I logged in to the Oracle Enterprise Manager console through “https://localhost:1158/em/” I can see that Oracle SID is “orcl”(lower case).But /home/oracle/.bash_profile file still shows “ORCL”, although did “export ORACLE_SID=orcl” already.[root@localhost ~]# cat /home/oracle/.bash_profile file # .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then . ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/binexport PATHORACLE_BASE=/opt/oracleORACLE_HOME=$ORACLE_BASE/112ORACLE_SID=ORCLLD_LIBRARY_PATH=$ORACLE_HOME/libPATH=$PATH:$ORACLE_HOME/binexport ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH [root@localhost ~]# ps -ef | grep pmon oracle 8383 1 0 12:12 ? 00:00:00 ora_pmon_ORCLroot 9866 5181 0 12:33 pts/1 00:00:00 grep pmonLSNRCTL> statusServices Summary…Service “ORCL” has 1 instance(s). Instance “ORCL”, status BLOCKED, has 1 handler(s) for this service…SQL> startupORACLE instance started.Total System Global Area 267227136 bytesFixed Size 2212496 bytesVariable Size 205524336 bytesDatabase Buffers 54525952 bytesRedo Buffers 4964352 bytesORA-00205: error in identifying control file, check alert log for more infoThanks for your help,-Arshad
LikeLike
Hello Osama,I tried the same steps that you mentioned in your last comment and now database is up and running.It is not blocking the database instance but I can't connect to Oracle Enterprise Manager using thehttps://localhost:1158/em. Firefox displayed the error message. Also I changed the ORACLE_SID to “orcl”(lower case) in /home/oracle/.bash_profile. I think thats the reason for DB console failure. I am also getting error when installing OIM.When I tried to start it from CLI:[oracle@localhost bin]$ emctl start dbconsoleOC4J Configuration issue. /opt/oracle/112/oc4j/j2ee/OC4J_DBConsole_localhost.localdomain_orcl not foundPlease let me know what should I do so that both Database is running and DB Console should be up too.As as last resort I am thinking to re-install Database, run RCU, install Weblogic server and the finally install the Oracle Identity Management.Thanks,-Arshad
LikeLike
For this ORA-00205: error in identifying control file, check alert log for more info As you the error is clear you need to check the alert log , and seems you have miss configuration Check your ORACLE_SID and fix it in the bash_profile.
LikeLike
As I told you before you need to check The ORACLE_SID , After that try to use emctl start dbconsole , if its not working or same error appear to you Use :-emca -deconfig dbcontrol db -repos drop-emctl -config dbcontrol db -repos create
LikeLike
Hello Osama,I am now able to login to DB console and also “lsnrtcl” donot show that it is blocking an instance.I can connect to database using “sqlplus” successfully and no errors.But when I login to the DB console using “https://lcoalhosy:1158/em”, I got the following message:” Enterprise Manager is not able to connect to the database instance. The state of the components are listed below.”.Although “Database Instance”, “Listener” are UP, and also “Agent Connection to Instance” shows “Succeeded”.Please let me know what should I do next to resolve this issue.Thanks for your help,-Arshad
LikeLike
check DBSNMP, SYSMAN, and SYS not locked and password not been changed.
LikeLike
Could you please tell me how can I check that? SQL queries?Thanks,-Arshad
LikeLike
I guess its time to read some Oracle Documentation and start over .SQL > Select username , account_status from dba_users
LikeLike
hiii osama, I am facing the problem ORA-12528, TNS:listener: all appropriate instances are blocking new connections The Connection descriptor used by the client was: 192.168.6.98:1521:ORCL. though i am not using this IP i am using 192.168.6.115:1521:ORCL, and the earlier IP is no where not even in my tnsnames.ora. please do the needful
LikeLike
if the above solution not working with you could be able to Create a static entry for the SID to the SID_LIST in the listener.ora
LikeLike
hii osama, Thanks for the time and solution but thats also not working, my problem is that ip address which i am not using anywhere in my machine can you plz tell me how to resolve that ? ThanksVipin
LikeLike
you're welcome , but could you please post more information
LikeLike
Could not create connection; – nested throwable: (java.sql.SQLException: Listener refused the connection with the following error: ORA-12528, TNS:listener: all appropriate instances are blocking new connections The Connection descriptor used by the client was: 192.168.6.98:1521:ORCL ); – nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; – nested throwable: (java.sql.SQLException: Listener refused the connection with the following error: ORA-12528, TNS:listener: all appropriate instances are blocking new connections The Connection descriptor used by the client was: 192.168.6.98:1521:ORCL ))
LikeLike
seems you are using windows ?if yes please do the following :This is performed on Microsoft Windows > Administrative Tools > Services by doing a right-click > Properties to open up the properties window then go to the Dependencies tab to indicate on which the windows service (for example the database startup service) is dependent.
LikeLike
Hii Osama, Yes i am using the windows but will u please kindly eloborate more about that dependency tab in services?
LikeLike
Check the below link which is describe the dependency tab https://svcdependencyviewer.codeplex.com/ also you can check Microsoft site about it.
LikeLike
HI Osama, Could please help me to solve the oracle issueAgent Connection to Instance:Status Failed Details Failed to connect to database instance: ORA-12541: TNS:no listener (DBD ERROR: OCIServerAttach.Listener:Status Up Host BELL-ID Port 1521 Name LISTENER Oracle Home C:\app\bellid\product\11.2.0\dbhome_1 Location C:\app\bellid\product\11.2.0\dbhome_1\network\admin Database Instance:Down
LikeLike
# tnsnames.ora ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = BELL-ID)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL) (UR = A) ) ) # listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = BELL-ID)(PORT = 1521)) ) )ADR_BASE_LISTENER = C:\app\bellid
LikeLike
My pleasure delete the listener.ora and without create start it and see what happening
LikeLike
Thanks, Below is error message we have:Status Metric Collection Error This database is not configured Status Pending Since May 21, 2013 2:00:00 PM CAT Last Known State
LikeLike
Still the same issue anothing are change
LikeLike
I removed the listener but still the same:Agent Connection to Instance Status Failed Details Failed to connect to database instance: ORA-12541: TNS:no listener (DBD ERROR: OCIServerAttach).
LikeLike
Enterprise manager error as i see , did you check from the user select account_status from dba_users where username = 'DBSNMP';if it's locked –> alter user dbsnmp identified by password account unlock;Check http://www.oracledistilled.com/grid-control/metric-collection-error-after-fresh-install-of-oracle-enterprise-manager-11gr1-grid-control/
LikeLike
Check your tnsnames.ora and recreate your listener using netca
LikeLike
Hi great Osama, I will so happy if you can help me on this.am trying to build Datagurad and got to the stage of RMAN. Am having error when I ran this command. connect auxiliary sys/oracle@DR..LOOK AT THE ERROR output.RMAN-04006: error from auxiliary database: ORA insufficient privileges……..I went to check my lsnrctl status I discover that the instance has a BLOCKED STATUS. I try to use some of your solution but it's seems not to be working. any help from your end will be appreciated.
LikeLike
From where you run duplicate ? it's recommended to run it from auxiliary
LikeLike
Hello Osama,Can i request for your guidance for my below issue: Instance “GDEV”, status BLOCKED, has 1 handler(s) for this service…I have tried few of the things but wasn't successful. Basically, i have scrapped the previous oracle_home and installed afresh. Now wanted to refresh from backup thru rman.But, I'm getting status BLOCKED, which is not getting rid ofThank you
LikeLike
Hi, I am working on Upgrade Manager, while installing it I got an issue which shows “ORA-12528 tns listener all appropriate instances are blocking new connections “I don't know database stuff, so can any one help me on this issue. Please suggest me step by step procedure to solve this issue.Thank,Bhaskar.
LikeLike
Slam 3lykom Osama,It solves the issue and I am able to login RMAN using Auxiliary instanceMy problem now .. after issuing the Duplicate database … when RMAN shutdown the Auxiliary, it cannot start it up again ..I get the Error:RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
LikeLike
Issue been solved following the Below:set ORACLE_SID=rman target sys/oracle@orcl auxiliary /Before I was writingrman target sys/oracle@orcl auxiliary sys/oracle@TESTDBThanks any way 🙂
LikeLike
Sorry mohammed I just see your message 🙂 Glad that you solve it let me know if you need anything else.
LikeLike
Unable to Solve if Oracle Listener Stops Working? Contact to Remote DBA Services Investigate your Oracle audience issue with Cognegic's Database Administration for Oracle or Online Oracle DB Support. Here we have most experienced and expert specialized specialists who give best answer for take care of your Oracle audience issue. Aside from this audience issue our specialists can illuminate some other specialized hiccups like recuperation issue, establishment issue, setup issue et cetera. Simply contact to our specialized specialists through dialing this underneath sans toll number and investigate the worthwhile help. For More Info: https://cognegicsystems.com/ Contact Number: 1-800-450-8670Email Address- info@cognegicsystems.com Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
LikeLike