Reputation: 315
I'm getting this error if i try to login as db user. If lsnrctl
status is run i get the below error.
DB was working fine all these years and stopped working suddenly.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ABC.LOCAL)(PORT=1521)
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 19-MAY-2014 12:18:17
Uptime 0 days 0 hr. 22 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\Oracle\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oracle\administrator\diag\tnslsnr\abc\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ABC.LOCAL)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "devdb" has 1 instance(s).
Instance "devdb", status BLOCKED, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
Instance "testdb", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
Upvotes: 8
Views: 186165
Reputation: 1
I had this error when trying to connect an API container to an oracle DB container which were both started by docker compose. The solution was to add "condition: service_healthy" in the docker compose so that the API would only start running when the DB oracle finished initializing. See my docker-compose below:
version: '3.9'
services:
api:
build:
context: .
depends_on:
db:
condition: service_healthy
environment:
- SPRING_DATASOURCE_URL=jdbc:oracle:thin:@//db:1521/xe
- SPRING_DATASOURCE_USERNAME=system
- SPRING_DATASOURCE_PASSWORD=oracle
ports:
- 8888:8080
networks:
spring-net:
aliases:
- spring-host
db:
image: oracleinanutshell/oracle-xe-11g:latest
healthcheck:
test: "exit 0"
ports:
- 49161:1521
- 5500:5500
environment:
- ORACLE_ALLOW_REMOTE=true
networks:
spring-net:
aliases:
- db-host
networks:
spring-net:
driver: bridge
ipam:
driver: default
Upvotes: 0
Reputation: 152
Thanks for the above. I had to do a combination of things. Here are my notes:
$ or c:\ > sqlplus "/as sysdba"
SQL> shutdown abort;
SQL> startup mount;
This error happened: ora-00214: more here: https://knowledge.exlibrisgroup.com/Aleph/Knowledge_Articles/Oracle_database_fails_to_start%2C_error_message%3A_ORA-00214%3A_control_file_control02.ctl_%5B...%5D_inconsistent_with_file_control01.ctl
Go to
C:\app\david\virtual\oradata\orcl
backup ctl files. Copy CONTROL01.CTL to CONTROL02.CTL
See if startup mount works; it does!
select instance_name, status, database_status from v$instance;
instance name; status; database_status
orcl; MOUNTED; ACTIVE
Listener Stop and Start in Windows Services : didn’t help
$ sqlplus nolog
SQL> connect / as sysdba
Connected.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> elect instance_name, status, database_status from v$instance;
instance name; status; database_status
orcl; MOUNTED; ACTIVE
Listener Stop and Start in Windows Services : now worked. (note that in both cases the listener showed as running).
$ tnsping orcle
Last line should say OK (0 msec)
Mine Dev Sandbox environment is localhost port 1521 service_name = orcl
Now I can connect from Toad, etc. firstly as SYSTEM
select * from davidl.dlfirst;
Upvotes: 0
Reputation: 9502
I had this problem on my developent environment with Visual Studio.
What helped me was to Clean Solution
in Visual Studio and then do a rebuild.
Upvotes: 3
Reputation: 3749
I had this error message with boot2docker on windows with the docker-oracle-xe-11g image (https://registry.hub.docker.com/u/wnameless/oracle-xe-11g/).
The reason was that the virtual box disk was full (check with boot2docker.exe ssh df
). Deleting old images and restarting the container solved the problem.
Upvotes: 4
Reputation: 138
If you are using 11G XE with Windows, along with tns listener restart, make sure Windows Event Log service is started.
Upvotes: 1
Reputation: 744
You have to know if the problem come from the listener or from the database.
So first, restart the listener, it could solve the problem.
Second, it could come from the db if it's not in open mode (nomount, mount, restrict). To check it, connect locally and do the following query:
sqlplus /nolog
connect / as sysdba
SQL> select instance_name, status, database_status from v$instance;
Upvotes: 11
Reputation: 16673
set ORACLE_SID=<YOUR_SID>
sqlplus "/as sysdba"
alter system disable restricted session;
or maybe
shutdown abort;
or maybe
lsnrctl stop
lsnrctl start
Upvotes: 18