How to find SID 1

[lydiattc]

Hi,

I'm working on a server, on which an Oracle instance was already installed without a tnsname.ora file or a listener.ora file. I copied these two files from one of my development machines to this server. And of course, they cannot work without giving them the correct SID and Global_DBName.

How do I find information about the SID and Gloabel_DBname?

Thanks,
Lydia

 

[carp]

Try
SELECT instance_name FROM v$instance;
for the SID.

The database name (NOT necessarily the global database name):
SELECT name FROM v$database;

 

[SantaMufasa]

Lydia,

I presume that the database is at least up and running, correct? If so, my favorite query for SID and information is:

col sid Heading "Instance|Name" format a8
col host Heading "Host|Name" format a25
col ver Heading "Oracle|Version" format a12
col up Heading "Startup|Time/Date" format a30
select instance_name sid
, host_name host
, version ver
, TO_char(startup_time,'hh24:mi:ss "on" Dy, dd-MON-yy') up
from v$instance;

Instance Host Oracle Startup
Name Name Version Time/Date
-------- ------------------------- ------------ --------------------------
FOS foster 8.1.6.0.0 05:02:49 on Sat, 19-JUL-03

If the instance is not up and running, then I would look next for the parameter file, whose default location is $ORACLE_HOME/dbs. The parameter file name is usually &quot;init<SID>.ora&quot;.

 

[lydiattc]

I checked the list of all services. Oracle agent was running, but no listener was running. I tried to start the listener by using Net Configuration Assistant and it got the listener started. However, I still cannot connect due to incorrect SERVICE_NAME.

I cannot connect to any database, so I cannot run the SQL scripts you provided. How do I run your scripts when the listener is not working and the connection is not up? How do I find the SERVICE_NAME?

Thanks,
Lydia

 

[carp]

Well, if you were able to get the listener started, I think I would be tempted to see what service the listener is listening for!

From the command line on your server, try:

lsnrctl status

This will give you the service names.

And if you have Net Configurartion assistant, you might want to create a tnsnames.ora file (assuming you still don't have one).

Finally, if you have a user session running on your database server (e.g., you've telneted in to the server) and know a username/password for a valid user on your database, try starting a sqlplus session.

 

[SantaMufasa]

And as an addendum to Carp's excellent response, remember these items:

1) You should be able to identify the Oracle SID
a) on Windows, by confirming the Oracle Service name: Start..Settings..Control Panel..Administrative Tools/Services..OracleService<SID>
b) on Unix (if the Oracle instance is running), with the command (for example): &quot;ps -ef | grep pmon&quot;, which responds with &quot;...ora_pmon_<SID>&quot;.

2) Technically, you need neither &quot;tnsnames.ora&quot; nor a running Listener to be running the Oracle database. The &quot;tnsnames.ora&quot; facilitates connectivity to Oracle instances when you use a &quot;service name&quot;; the Listener facilitates connections from EXTERNAL sources to an instance on &quot;this&quot; machine. If you are logged onto the same machine as the running target Oracle server (and on Unix, your $ORACLE_HOME and $ORACLE_SID point to your target Oracle instance), then you should be able to connect to SQL*Plus with the command line, &quot;sqlplus <username>/<pw>&quot;.

3) You should be able to use the working tnsnames.ora file copied from one of your other clients or servers.

4) Resolving Listener problems may require a bit more intuition. I'm certain that several of us on the forum are willing to help if you provide any error messages or diagnostic information you can provide.

 

[lydiattc]

Thank you all for replying. Here's some detailed information.

1. This is a new server shipped to me from another site. Oracle 9.2.0.3 was installed without any database created. No tnsname.ora file. No listener.ora file. Listener was not running and OracleService<SID> was not running.

2. I copied tnsnames.ora and listener.ora files from one of our development boxes, and tried to create a new database by using the OEM. In the process of creation, I was able to choose one of the database names listed in the tnsnames.ora file and successfully added it to the tree.

3. The new database is listed under the tree in OEM, but I was NOT able to connect to it by either OEM or SQL*PLUS. The error message was TNS:NO Listener.

4. From initdw.ora, I found that SID was &quot;dw&quot;

5. I went to Oracle Net Configuration Assistant and added a listener and started it. When I run lsnerctl status, it returns the following:
...
Service &quot;dw.jxsrv26&quot; has one instance(s)
Instance &quot;dw&quot;, status UNKNOWN, has one handler(s) for this services ...
...

5. I ran &quot;net start OracleServicedw.jxsrv26&quot;. It returned error &quot;The service name is invalid&quot;

6. I ran &quot;net start OracleServicedw&quot;. It returned the same error.

7. After I started listener, I tried to connect to Oracle through OEM and SQL*PLUS, and both gave me an error &quot;TNS:Listener could not resolve service_name given in connect descriptor&quot;

Questions:
1. How to start OracleService<SID>? In this case, I don't know what the SID is. How do I find out?
2. How to get the service name?

Hope this explains the problem better.

Thanks,
Lydia

 

[SantaMufasa]

Lydia, With the information you provided, I believe now we can make more progress. In Item #1, you mention that you received the server with Oracle 9.2.0.3 installed, but no database. That's where we should begin.

From your assertion that &quot;...OracleService<SID> was not running,&quot; I infer that this installation is running on a Windows server, correct? Could you please confirm/correct my inference?

Since the database do not yet exist, then you have the luxury of choosing both the instance name and database name (which, of course, can be the same name). Please specify what you want for those appellations.

Do you have any other working 9i database/instances? If so, let's plagiarize the init<SID>.ora file, the listener.ora file, the sqlnet.ora, and tnsnames.ora. If not, let's still plagiarize, but from an 8i instance...We'll at least be able to get the instance and database up and running, then make tweaks for 9i.

Please specify the pathnames of where you want your Database data files, on-line redo log files, and control files to reside.

With the above preparations, I can post tailored scripts for you to create your database.

Cheers,

Dave

 

[appi]

Lydia,

in accordiance to dave's Post. I agree with him that you're using Windows (... net start ...)
So you can check if there is an Instance running if you'd an service called OracleService<SID> in the services tab of your server.
If not I agree that there is no installed Instance, only the Software is installed.
After the first initialization Oracle 9i will use in internal Server-Parameter File (SP-File) which builds oracle from the first init<SID>.ora it founds during first start. Every change in init<SID>.ora will take no effect until you read out the SP-File, chage the values there and reads it back with &quot;CREATE PFILE ...... from SPFILE&quot; and &quot;CREATE SPFILE from PFILE ......&quot;
regards
Uwe

 

[lydiattc]

Hi Dave and Uwe,

Yes, you were both right. I'm sorry that I didn't tell you earlier that I was working on Windows2000 Server running Terminal Services.

I'm sorry that I'm afraid that I cannot tell you the real names I need to give to the db, because they has a very special format with sensitive information. Let's just pretend that the names I want are &quot;oradb.server2&quot; and &quot;oradb&quot;.

Like I said ealier, I do have other Oracle9i databases running on other servers. I have copied tnsnames.ora and listener.ora to the new server. As you suggested, I will copy init<SID>.ora and sqlnet.ora over as we speak.

I think I'd like data files, log files, and control file to reside under h:\Oracle\Oradata\ora92. Where would you suggest?

Let me know if you need more information, and I'll let you know if copying more files over helps.

Thanks,
Lydia

 

[lydiattc]

Hi,

It's all fixed now! Thank you all for your time and concern.

Finally, I used the Database Configuration Asistant to successfully create an instance and a new database. It's now working fine. I didn't have to copy all the files over. Those files were created by the D.C.A.

Thanks,
Lydia