Re: Work with SQL*Plus through command prompt windows

From: <>
Date: Thu, 8 May 2008 11:13:45 -0700 (PDT)
Message-ID: <>

Comments embedded.
On May 8, 11:58 am, wrote:
> On May 8, 11:34 am, "" <> wrote:
> > On May 8, 11:17 am, wrote:
> > > Dear All,
> > > I have big problems trying to access a database using SQL*Plus through
> > > command prompt. I am a beginner DBA, and my first challenge is to make
> > > a backup and recovery of an Oracle9i database.
> > > I tried to access through SQL*Plus windows but it showed me "TSNames
> > > No listener"...

And the command you attempted which produced that output was ...

To verify if you're using a correct TNS alias you would type, at the command prompt:

tnsping <whatever alias you want to use>

and see what tnsping reports. Some sample successful output follows:

c:\sql>tnsping smudgepot

TNS Ping Utility for 32-bit Windows: Version - Production on 08-MAY-2008 13:06:39

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = ZINGBOT.COM)(Port = 1529))) (CONNECT_DATA = (SID = SMUDGEPOT)))
OK (10 msec)

As you can see a successful ping returns a plethora of information; an unsuccessful ping produces:

c:\sql>tnsping smort

TNS Ping Utility for 32-bit Windows: Version - Production on 08-MAY-2008 13:09:55

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:

TNS-03505: Failed to resolve name

which should be telling you the alias isn't defined in the tnsnames.ora file. This informative bit of text:

Used parameter files:

provides the networked location of a common tnsnames.ora file (the sqlnet.ora and tnsnames.ora files are located in the same directory). One would then investigate the tnsnames.ora file with any common text editor and find the proper alias for the database in question.

> > > and accessing using SQL*Plus through command prompt I
> > > could, but now...I can do anything else, I can't use RMAN, V$ .... I
> > > got completely lost..and I will appreciate so much any help.
> > > Thanks in advance for your time.
> > > Bets regards,
> > > BeginnerDBA
> > Since you won't supply the command you attempted to use to access an
> > Oracle database through SQL*Plus tell us why we should take our time
> > to try and solve an issue where no real information is available.
> > Post what you did and what error messages you received.
> > David Fitzjarrell
> Ok, I apologize....this is my first day as a real DBA after I finished
> college, no more practice...nothing ...this is real. Also, this is the
> first time using this tool to try to contact someone who could help
> me.


> Well, after I launch SQLPlus in the command prompt I tried to see the
> blocks that I got corrupted after a hot backup, and I ran v
> $backup_corruption...and the only message I got was "unknown
> command".....

You cannot simply 'launch' SQL*Plus and expect to be able to do anything worthwhile as you have yet to connect to a database. One presumes this is a client machine, and that your database of interest is NOT local:

c:\sql>sqlplus /nolog

SQL*Plus: Release - Production on Thu May 8 12:57:22 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect ####/*********@smudgepot
SQL> At this point you are successfully connected to a database; one wonders how you passed your courses without knowing HOW to connect to an Oracle database.

Queries can now be executed from the SQL> prompt:

SQL> select * from v$backup_corruption;

no rows selected

SQL> I am concerned that you are convinced that a database backup can be taken from SQL*Plus; nothing could be further from the truth. RMAN, a separate utility, is used for such tasks; it would be a wise person who bookmarks the following link:

and uses it religiously to learn the various aspects of Oracle and its management.
> the thing is that if you could help me giving me some
> help what should I do...or may be some link where I can read ...I will
> do it....I just want to know which is the best way to make a backup
> and recovery by using the SQLPlus through command prompt
> window.........

The best way is to not make such an attempt; RMAN is the supplied tool of choice for backups.

> Thanks- Hide quoted text -
> - Show quoted text -

David Fitzjarrell Received on Thu May 08 2008 - 13:13:45 CDT

Original text of this message