How to start Oracle DataBase10g that have stopped running properly?

Hello

I have Oracle DataBase 10G on Windows Server 2003

The database does not start properly anymore.

I cannot connect as sysdba, because i have the error

ORA-12560 - TNS unresolved

I have restart all windows services related with Oracle
There no error in restarting all service (OracleNAME_INSTANCE service works)

I have created ORACLE_SID and ORACLE_HOME

Patrice
patrocle_runAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Can you post the exact commands that you executed?  TNS shouldn't be involved, so I'm thinking that you missed something in one of the commands.
0
patrocle_runAuthor Commented:
sqlplus /nolog

SQL*Plus: Release 8.1.7.0.0 - Production on Me Jun 3 14:41:19 2015

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL>
SQL> connect / as sysdba
ERROR:
ORA-12560: TNS : erreur d'adaptateur de protocole


SQL> connect sys as sysdba
Entrez le mot de passe :
ERROR:
ORA-12560: TNS : erreur d'adaptateur de protocole


SQL> connect internal
ERROR:
ORA-24314: descripteur de service non initialisÚ


SQL>
0
Geert GOracle dbaCommented:
there should be a service OracleServicexxxx
xxxx is the oracle_sid

in a command box:
set oracle_sid=xxxx
sqlplus "/ as sysdba"
startup
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Geert GOracle dbaCommented:
oracle 8.1.7 ?????
this is not oracle 10.2 ...

show output from command box of this:

set ora
0
patrocle_runAuthor Commented:
set ora
ORACLE_HOME=E:\oracle\product\10.2.0\db_1
ORACLE_SID=BASESOM



On the Other part, i have restarted OracleServiceBASESOM (no errors is displayed)
and restart the computer.
0
patrocle_runAuthor Commented:
In fact, Oracle8.1.7 does not install in my server, and i reinstall oracle10g on it
0
patrocle_runAuthor Commented:
Note :
the database have worked since several years.

normally, when i restart the server, the database works.
0
johnsoneSenior Oracle DBACommented:
I think something isn't right in your environment.  You are running a 8.1.7 version of SQL*Plus, which says to me that your PATH is probably set incorrectly.
0
patrocle_runAuthor Commented:
The path environment;

C:\Program Files\Windows Resource Kits\Tools\;E:\oracle\product\10.2.0\db_1\bin;E:\oracle\product\10.2.0\db_1\Apache\Perl\5.00503\bin\mswin32-x86;C:\Program Files\Oracle\jre\1.1.7\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem
0
patrocle_runAuthor Commented:
when i typed:

SQL> connect /@BASESOM as sysdba
ERROR:
ORA-01031: insufficient privileges
0
slightwv (䄆 Netminder) Commented:
>>In fact, Oracle8.1.7 does not install in my server

You need to figure out why/where/how you are running 8.1.7 sqlplus.  My guess is there is an environment confusion somewhere.  You are trying to start a database with some things pointing to different versions of the software.

>> and i reinstall oracle10g on it

I have to ask:  
What happened to the software that you had to reinstall?
Was the database 10g?
Did you reinstall the exact same version (all 4 numbers)?
0
patrocle_runAuthor Commented:
last line in alert log

Wed Jun 03 14:59:18 2015
alter database open
ORA-1113 signalled during: alter database open...
0
slightwv (䄆 Netminder) Commented:
From:
http://docs.oracle.com/cd/B14117_01/server.101/b10734/ostroubl.htm

When you attempt to open the database, error ORA-1113 indicates that a datafile needs media recovery

This error commonly occurs because:
•You are performing incomplete recovery but failed to restore all needed datafile backups.
•Incomplete recovery stopped before datafiles reached a consistent SCN.
•You are recovering datafiles from an online backup, but not enough redo was applied to make the datafiles consistent.
•You are performing recovery with a backup control file, and did not specify the location of a needed online redo log.
•A datafile is undergoing media recovery when you attempt to open the database.
•Datafiles needing recovery were not brought online before executing RECOVER DATABASE, and so were not recovered.


There is also an older 8i reference that says there is one or more data files that are still in backup mode:
https://community.oracle.com/thread/720403
0
patrocle_runAuthor Commented:
when i search in google "ora-1113", there are answers

but i cannot access in sysdba in sqlplus

connect / as sysdba
ORA-12560: TNS:protocol adaptor error
0
patrocle_runAuthor Commented:
I never do a recovery or a backup, so why this ORA-1113
0
patrocle_runAuthor Commented:
lsnrctl status

LSNRCTL for 32-bit Windows: Version 8.1.7.0.0 - Production on 03-JUN-2015 16:27:
12

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Connexion Ó (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUT du LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 8.1.7.0.0 - Produc
tion
Date de dÚpart                       03-JUN-2015 14:58:58
DurÚe d'activitÚ                    0 jours 1 heures 28 min. 15 sec
Niveau de trace           off
SÚcuritÚ                  OFF
SNMP                      OFF
Fichier de paramÞtres du listener     E:\oracle\product\10.2.0\db_1\network\ADMI
N\listener.ora
Fichier journal listener             E:\oracle\product\10.2.0\db_1\network\log\l
istener.log
RÚcapitulatif services...
  BASESOM       \ 1 gestionnaires de services
  BASESOM       \ 1 gestionnaires de services
  PLSExtProc    \ 1 gestionnaires de services
La commande a rÚussi
0
slightwv (䄆 Netminder) Commented:
>>ORA-12560: TNS:protocol adaptor error

99% of the time this is because the Windows Service for the SID you are trying to connect to hasn't started.

>>I never do a recovery or a backup, so why this ORA-1113

Then go back to the doc link:  
ORA-1113 indicates that a datafile needs media recovery
0
slightwv (䄆 Netminder) Commented:
>>lsnrctl status

Thanks but that doesn't do anything for this question.  The database won't start so the status of the listener isn't related to anything.
0
patrocle_runAuthor Commented:
tnsping BASESOM

TNS Ping Utility for 32-bit Windows: Version 8.1.7.0.0 - Production on 03-JUN-20
15 16:29:13

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.1)(PORT=1521))
OK (130 msec)
0
johnsoneSenior Oracle DBACommented:
Again, something isn't right in your environment.  Look at your output from lsnrctl:

Version                   TNSLSNR for 32-bit Windows: Version 8.1.7.0.0 - Produc
Fichier de paramÞtres du listener     E:\oracle\product\10.2.0\db_1\network\ADMI

One of these things is not like the other.

You need to figure out why you are getting 8.1.7 versions of the software.
0
patrocle_runAuthor Commented:
but i try to resolve the fact that i cannot log on sysdba

i need a connection to recover data files

When i try to connect, i have ORA-12560: TNS:protocol adaptor error
0
slightwv (䄆 Netminder) Commented:
>>tnsping BASESOM


Again, thanks for this but it doesn't help.  tnsping only hits the listener.  It doesn't touch the database.


And the version issue pointed out above.


You keep posting the output of commands that don't help us and are not answering the questions we have asked that will help.
0
Mark GeerlingsDatabase AdministratorCommented:
It is possible to have SQL*Plus 8.1.7 on a client machine and an Oracle10 database on a Windows server.  We used that combination for a number of years.  I never tried using SQL*Plus on a client machine though to start or stop the database.  I always used SQL*Plus on the server to start or stop the database.  That version of SQL*Plus should be the same version as the database.
0
slightwv (䄆 Netminder) Commented:
>>It is possible to have SQL*Plus 8.1.7 on a client machine and an Oracle10 database on a Windows server.

Agreed but lsnrctl doesn't come with the client.

And you would need to add a tns alias with:  conn / as sysdba that was posted above.

All this tells me they are on the database server.

Based on what was posted, my guess is the database was 8.1.7 and something happened and 10g was installed for some reason and not the environment is all confused.
0
johnsoneSenior Oracle DBACommented:
Also the fact that connect internal actually seemed to do something would say to me they are connected to the database server.

To me, it seems like there is at least a partial install of 8 on the database server (or just a 8 client) and the database is 10.  Something in the environment is messed up so the 8 executables are being used.
0
johnsoneSenior Oracle DBACommented:
Scratch that.  Cannot be 8 client.  lsnrctl wouldn't be there.  Since lsnrctl seems to have found information, it would seem that an 8 listener is running as well.
0
Mark GeerlingsDatabase AdministratorCommented:
I think johnsone spotted the problem:  
LSNRCTL ... for 32-bit Windows: Version 8.1.7.0.0
...
 Fichier de paramÞtres du listener     E:\oracle\product\10.2.0\db_1\network\ADMI

It looks like you have a version 8 database and listener (not a 10g database) but your path is to Oracle version 10 software.

Normally, the Oracle database version and the path point to the same Oracle version.  Do you have Oracle8 software installed on the server somewhere?
0
patrocle_runAuthor Commented:
problem resolved with upwork

step 1: add administrator and my name account to ORA_DBA

Step 2 : Use RECOVER DATABASE command
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
if the freelancer had access to the server it would be a piece of cake
just as it would be for any of us
0
patrocle_runAuthor Commented:
the solution was found by a freelancer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.