You encounter below error while connecting to Oracle DB instance, which was working before
—————————
ODBC 32Bit Test Program
—————————
SQLSTATE: S1000
Native Error Code:12514
Driver Message:[Oracle][ODBC][Ora]ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
Need Any suggestions to avoid error ?
—————————
Yes No
—————————
Troubleshooting:
TNS Listener shows its status as running:
[root@ProdDB01 ~]# lsnrctl status
LSNRCTL for Linux: Version 8.1.6.0.0 – Production on 20-MAR-2014 10:48:58
(c) Copyright 1998, 1999, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.21.201.217)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 8.1.6.0.0 – Production
Start Date 20-MAR-2014 10:45:55
Uptime 0 days 0 hr. 3 min. 3 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /opt/oracle/product/8.1.6/network/admin/listener.ora
Listener Log File /opt/oracle/product/8.1.6/network/log/listener.log
Services Summary…
PLSExtProc has 1 service handler(s)
inst1 has 1 service handler(s)
inst3 has 1 service handler(s)
The command completed successfully
[root@ProdDB01 ~]#
But there will not be any Oracle DB service running:
[root@ProdDB01 ~]# ps -ef | grep pmon
root 11001 23846 0 11:50 pts/4 00:00:00 grep pmon
[root@ProdDB01 ~]#
Expected is:
[root@ProdDB01 ~]# ps -ef | grep pmon
oracle 2142 1 0 2009 ? 00:00:10 ora_pmon_ProdDB01
root 11001 23846 0 11:50 pts/4 00:00:00 grep pmon
[root@ProdDB01 ~]#
Starting up Oracle Database will fail with errors:
[root@ProdDB01 ~]# su – oracle
-bash-3.1$ lsnrctl start
LSNRCTL for Linux: Version 8.1.6.0.0 – Production on 20-MAR-2014 12:12:07
(c) Copyright 1998, 1999, Oracle Corporation. All rights reserved.
TNS-01106: Listener using listener name LISTENER has already been started
-bash-3.1$ dbstart
SQL*Plus: Release 8.1.6.0.0 – Production on Thu Mar 20 12:12:15 2014
(c) Copyright 1999 Oracle Corporation. All rights reserved.
SQL> Connected to an idle instance.
SQL> ORACLE instance started.
Total System Global Area 122736624 bytes
Fixed Size 69616 bytes
Variable Size 91774976 bytes
Database Buffers 30720000 bytes
Redo Buffers 172032 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/opt/dbfiles/oradata/inst3/system01inst3.dbf’
SQL> Disconnected
Database "inst3" warm started.
-bash-3.1$
At this point the ODBC connection will result in below error message:
—————————
ODBC 32Bit Test Program
—————————
SQLSTATE: 08004
Native Error Code:1033
Driver Message:[Oracle][ODBC][Ora]ORA-01033: ORACLE initialization or shutdown in progress
Need Any suggestions to avoid error ?
—————————
Yes No
—————————
From Oracle DB log file:
[root@ProdDB01 ~]# tail -15 /opt/dbfiles/inst3/bdump/alert_inst3.log
Thu Mar 20 12:12:15 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0
Starting up ORACLE RDBMS Version: 8.1.6.1.0.
System parameters with non-default values:
processes = 800
shared_pool_size = 60000000
control_files = /opt/dbfiles/oradata/inst3/ctrl1inst3.ctl, /opt/dbfiles/oradata/inst3/ctrl2inst3.ctl, /opt/dbfiles/oradata/inst3/ctrl3inst3.ctl
db_block_buffers = 15000
compatible = 8.1.5
log_archive_dest = /opt/dbfiles/inst3/archive
log_buffer = 163840
log_checkpoint_interval = 10000
db_files = 20
db_file_multiblock_read_count= 8
dml_locks = 200
rollback_segments = r01, r02, r03, r04, r05, r06
remote_login_passwordfile= EXCLUSIVE
sort_area_size = 2097152
db_name = inst3
ifile = /opt/dbfiles/admin/inst3/pfile/configinst3.ora
background_dump_dest = /opt/dbfiles/inst3/bdump
user_dump_dest = /opt/dbfiles/inst3/udump
max_dump_file_size
= 10240
core_dump_dest = /opt/dbfiles/inst3/cdump
PMON started with pid=2
Load Indicator not supported by OS !
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Thu Mar 20 12:12:16 2014
ALTER DATABASE MOUNT
Thu Mar 20 12:12:20 2014
Successful mount of redo thread 1, with mount id 2581528420.
Thu Mar 20 12:12:20 2014
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Thu Mar 20 12:12:26 2014
ALTER DATABASE OPEN
ORA-1113 signalled during: ALTER DATABASE OPEN…
Thu Mar 20 12:45:43 2014
Starting ORACLE instance (normal)
[root@ProdDB01 ~]#
Manually verifying DB file mount and attempt to make DB mode to open/write:
-bash-3.1$ env | grep ORA
ORACLE_OEM_JAVARUNTIME=/opt/java/jre
ORACLE_OWNER=oracle
ORACLE_SID=inst3
ORACLE_BASE=/opt/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/opt/oracle/product/8.1.6
-bash-3.1$ sqlplus ‘/ as sysdba’
SQL*Plus: Release 8.1.6.0.0 – Production on Thu Mar 20 12:45:02 2014
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 – Production
With the Partitioning option
JServer Release 8.1.6.0.0 – Production
SQL> startup
ORA-01081: cannot start already-running ORACLE – shut it down first
SQL> select value from v$parameter where name = ‘background_dump_dest’;
VALUE
——————————————————————————–
/opt/dbfiles/inst3/bdump
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.6.1.0 – Production
With the Partitioning option
JServer Release 8.1.6.0.0 – Production
-bash-3.1$ less /opt/dbfiles/inst3/bdump
SQL> select status, database_status from v$instance;
STATUS DATABASE_STATUS
——- —————–
MOUNTED ACTIVE
SQL> select open_mode from v$database;
OPEN_MODE
———-
MOUNTED
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/opt/dbfiles/oradata/inst3/system01inst3.dbf’
SQL>
The underlying problem is that DB is not open for writes, thus not starting up. You need to get the reported .dbf file recovered.
References:
4.2.7 Start Services
You must start the services in the following order:
- Establish IP address on the active node
- Start the TNS listener
- Start the database
- Start dbconsole
- Test functionality
In the event that services do not start, do the following:
- Establish IP on failover box
- Start TNS listener
3. lsnrctl start
4.
- Start the database
6. dbstart
7.
- Start Database Control
9. emctl start dbconsole
10.
- Test functionality
To manually stop or shutdown a service, follow these steps:
- Stop the application.
- Stop Database Control
3. emctl stop dbconsole
4.
- Stop TNS listener
6. lsnrctl stop
7.
- Stop the database
9. dbshut
10.
- Stop IP
Source: 4.2.7 Start Services
