Connect to Oracle 11g using PL-SQL developer and SQL PLus

[amire...@gmail.com]

I have just installed Oracle 11g Personal Edition on my PC today. When I open PL-SqL developer, it asks me for four things:-

1. Username (oracle never asked for this in the installation)
2. Password (xyz, that I myself set)
3. Database ( Oracle created a starter database with the name of db_2 and global db with the name "orcl")
4. Connect as: Normal

What do I enter in the 4 required fields. The password field is obvious and I would probably enter db_2 in the database name. But what should i enter in the username? Oracle 11g never asked for the username during the installation.

The same issue persist with SQL PLus. When I open it, a command line window opens and it asks a username and password. I tried a lot of combinations but none of them worked.

Is there a default combination of username and password for SQL plus?

Please, help me since I am installing Oracle for the the first time. Also, tell me any good tutorial on how to create/set the tnsnames.ora file that I have heard about and setting the environment variables.

Which has to be done first? The tnsnames.ora file setting and env variable setting or do I connect first?

Regards,

[Amir Diwan]

Upon trying the username and password on SQL PLUS it gives, an error

ORA-12560: TNS Adapter error

[joel garry]

On Monday, October 14, 2013 6:52:42 AM UTC-7, Amir Diwan wrote:
> Upon trying the username and password on SQL PLUS it gives, an error
>
>
>
> ORA-12560: TNS Adapter error

OK, there are several possible problems in your previous post and this one. I don't know what the defaults are for personal edition and sql*developer, so I'll tell you what I would look for.

First, does sqlplus work for you? As the owner of the Oracle software, get to a command line and try this:

sqlplus / as sysdba

It should log you in as sys user and give a sql prompt. At the prompt, enter:

select username, account_status from dba_users;

It should give a list of usernames and whether the account is locked. If the names SCOTT or HR are in there, we can proceed with unlocking and/or attaching with sql*developer, if not, you need to google for how to add demo accounts - there is perhaps a script you need to run.

Show us the output of those commands with cut and paste.

Also, you can google for tutorials, but I think they generally assume the demo accounts are ready to go.

jg
--
@home.com is bogus.
http://gigaom.com/2013/10/14/oracles-hardware-business-may-be-worse-than-we-thought/

[ddf]

Normally one creates users other than SYS and SYSTEM so that you and others can connect to the database without the relative super powers of the administrative accounts (SYS and SYSTEM are created by default for every Oracle database). Creating user accounts is outlined here:

http://docs.oracle.com/cd/E11882_01/network.112/e36292/users.htm#BABGIFFE

Any users you would create using these examples would connect as Normal users. Of course you will need to connect as either SYS or SYSTEM to create additional user accounts and each new account will have its own password.

The database shouldn't need to be specified if the SQL Developer application is installed on the database server -- since this database is probably running on your laptop or personal PC I expect this is the case. If the database is not local then you would use the TNS alias to connect to it. Information regarding the TNS configuration can be found here:

http://docs.oracle.com/cd/E11882_01/network.112/e41945/naming.htm#i479588

Again since you are connecting locally the tnsnames.ora file can wait to be configured.

There is no 'default combination of username and password for SQL plus'. Basically on your own personal database you should be able to connect to it using the following command and parameters:

sqlplus / as sysdba

This connects you as SYS AS SYSDBA, the most powerful user in an Oracle database. This user account is NOT to be used lightly or regularly, as other non-SYSDBA user accounts should be created. See the link I provided for creating users to see how this is done.

The online documentation, available here:

http://tahiti.oracle.com

should be a ready reference and bookmarked in your browser favorites. It is the first place you should go to find answers to questions such as this.


David Fitzjarrell

[Amir Diwan]

@jg: Upon trying, sqlplus / as sysdba..

1) It again gives TNS adapter error and gives further 2 more attempts. Then i make lame attempts. Then it fails to connect. I will try to attach the snapshot..

Please, help me. The more deeper I dive into resolving the issue and the more confused I get.

2) There was no service available OracleServiceORCL in Windows Services. Do you know why? For that, I executed the command

Oradim -new -sid orcl

but that gives the error "DIM-000003: An argument is missing for the parameter".

3) Do you know where the files listener.ora, tnsnames.ora are found in the hard drive? Since, I have installed Oracle. Or do I have to create them on my own.

[joel garry]

On Monday, October 14, 2013 11:04:44 AM UTC-7, Amir Diwan wrote:
> @jg: Upon trying, sqlplus / as sysdba..
>
>
>
> 1) It again gives TNS adapter error and gives further 2 more attempts. Then i make lame attempts. Then it fails to connect. I will try to attach the snapshot..
>

Odd. Were you in a cmd window? Show us the output of a set command.

>
>
> Please, help me. The more deeper I dive into resolving the issue and the more confused I get.
>

See https://forums.oracle.com/thread/2592671

>
>
> 2) There was no service available OracleServiceORCL in Windows Services. Do you know why? For that, I executed the command
>
>
>
> Oradim -new -sid orcl
>
>
>
> but that gives the error "DIM-000003: An argument is missing for the parameter".
>

I avoid windows, but a google turns up many things like https://forums.oracle.com/thread/2278768

>
>
> 3) Do you know where the files listener.ora, tnsnames.ora are found in the hard drive? Since, I have installed Oracle. Or do I have to create them on my own.

You do not need a listener.ora. However, if you have one, it has to be correct. There should be a sample tnsnames.ora, you need to make one if you haven't made one with netca or some installation thingee. In my XE they wound up in C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN (with the sample directory there too).

jg
--
@home.com is bogus.

http://www.theregister.co.uk/2013/10/14/ballmer_successors/

[Amir Diwan]

@jg: This group does not have a image attachment option. Please, give me your email address. I will mail you the snapshot. Or else, tell me where the snapshot is.

[Amir Diwan]

@jg: I mean where is the snapshot attachment option in this group?

Your posts are helpful. All the .ora files are in the dbhome_2\network\admin\ folders.

Thanks.

[ddf]

Have you set the ORACLE_SID environment variable to the name of your local database? You need to:

C:\>set ORACLE_SID=

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 15 08:45:04 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:

C:\>set ORACLE_SID=smedley

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 15 08:45:57 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


David Fitzjarrell

[Amir Diwan]

@David: Probably, you are right. I havent set my SID which is why this is occuring.

How do i know the Oracle_SID for my PC? How do I create an SID in Oracle 11g?

Its not in tnsnames.ora file. The three .ora files tnsnames.ora, sqlnet.ora and listener.ora are in the folder \db_2\network\admin\sample\ . But, they are present in the generalized format. And, if I try to modify them, Windows denies access to that, despite, they are not even READ-ONLY files.

[joel garry]

On Tuesday, October 15, 2013 1:16:58 AM UTC-7, Amir Diwan wrote:
> @jg: This group does not have a image attachment option. Please, give me your email address. I will mail you the snapshot. Or else, tell me where the snapshot is.

This is a text based volunteer usenet group. My email is fake, I only respond in group when I have time.

I have a real email in http://dbaoracle.net/readme-cdos.htm but really, don't send me stuff, I won't give personal help without pay and hardly check that email anyways. But helping here potentially helps everyone, and that makes me feel good.

Your question ought to be in a sql*developer faq, but I'm not sure where one is. The TNS error is explained on Ed Steven's blog, as well as many places including the oracle forums. David's answer is one perfectly good one, though I'm wondering whether there should be some more general answer for those tools on that OS.

jg
--
@home.com is bogus.

http://it.tmcnet.com/news/2013/10/15/7478454.htm

[ddf]

Since this is probably Windows you should check Services for an OracleServicexxxxx entry; the xxxxx part is the SID you should be using. On a UNIX/Linux platform a ps -ef | grep pmon command, from the shell prompt, will give you output similar to this:

$ ps -ef | grep pmon
oracle 18346 1 0 Oct06 ? 00:03:57 ora_pmon_dbm1

The text after the second underscore is the SID.

You would then need to set the ORACLE_SID on Windows as follows:

set ORACLE_SID=xxxxx

where xxxxx is replaced with your actual SID. On UNIX/Linux it's a bit different:

$export ORACLE_SID=xxxxx

again where xxxxx is replaced with the actual SID.


David Fitzjarrell

[Amir Diwan]

@David: After Run-> Services.msc -> Office Source Engine, Office Software Protection Platform and Offline files are the only entries I have with the letter O.

I dont have any entry with OracleServiceXXXX.

How do I create an entry for OracleServiceXXXX on Windows 7?

Thanks, for your help and seems like I am approaching near my goal.

[ddf]

Normally you don't, DBCA does that for you. Do you NOT have a running Oracle database on your PC?


David Fitzjarrell

[Amir Diwan]

@David: I used the dbca utility from the command line, and created a new database, following a tutorial, but now it gives new error:-

-------------------------------------------------------------------------
An unexpected error has been detected by Hotspot Virtual Machine:

EXCEPTION_ACCESS_VIOLATION:

--------------------------------------------------------------------------

On googling, the website suggested me to the install the latest version of Java. I did so. I created the db again with the dbca utility but i am getting the same error again.

Prior, to my Java installation, the command line did not recognize Java as a command but after installation, it does.

Please, tell me the fix for this error.

[ddf]

I wish I could but I have never used Hotspot Virtual Machine.


David Fitzjarrell

[Mladen Gogala]

There are 3 steps:

1) Use oradim.exe to create services. There are several.
2) Download the latest version of CentOS, currently CentOS 6.4 and put the
ISO on a USB stick.
3) Boot from the USB stick and select "format drive" option.
4) Install CentOS 6.4, turn off securelinux and firewall, adjust the
/etc/sysctl.conf file according to the instructions in the Oracle
installation manual and install Oracle. You can use OpenSuSE to
install Oracle 12c but not Fedora 19. Oracle 11.2.0.4 works well on
F19 but not Oracle 12c.
5) Make sure that you set the instance you just created to autostart by
putting "Y" in the appropriate field of /etc/oratab
6) Create autostart/stop script in /etc/init.d, you can call it "oracle"
the script should contain something like the following:
export ORACLE_HOME=/app/oracle/product/11.2.0/dbhome_1
su - oracle -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
7) Execute "chkconfig --add oracle" and then use the chkconfig to enable
it for the boot level 3 or 5. Boot level 3 doesn't start X11, which is
OK for a DB server.

Voila, Oracle is installed on the server and will start automatically.
Even more, the virus from Redmond, WA is thoroughly wiped out and will no
longer bother you with virus problems.



--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

[Amir Diwan]

@Mladen Gogala: With reference to instruction 1, please tell me the exact command for oradim to create a service or refer me to an excellent tutorial. I have tried oradim previously but it gives me the error that "Missing an argument".

With reference to instruction 2-7, they are probably for linux OS. I am using Windows 7.

Please, guide me with respect to Windows 7 and refer me to a tried and tested tutorial of yours of oradim that I can execute successfully.

[Mladen Gogala]

I don't really like Windows and I believe that the only proper way to
deal with Windows is to remove them and install something else in their
place. The proper command is "fdisk". On the other hand, I maybe a little
biased. Just a little, though.