try to get snap shot --- error out

[Deanna Tran_1]

I have installed the statpack on my system. However, when i tried to run a snap shot, I got the following errors?
the database has been running for 1 day
$ sqlplus perfstat/perfstat
SQL> execute statspack.snap
BEGIN statspack.snap; END;

*
ERROR at line 1:
ORA-03114: not connected to ORACLE

[Jeff Schussele]

Hi Deanna,

I don't see the DB name on the sqlplus command line - just the username/PW.

Rgds,
Jeff

[Deanna Tran_1]

according to the spdoc.txt
that is all i need to do? perhaps, there is something i didn't know? Can you show me how to do so?

[Jeff Schussele]

Hi (again) Deanna,

Well, first off I've never used statpack.
But a normal sqlplus command needs 3 args
username/PW ORACLE_SID (aka DB name)
with that syntax.

Rgds,
Jeff

[Deanna Tran_1]

hi, I tried you suggestion but was unsucssful
SQL> connect perfstat/perfstat daisy
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where ::= [/][@] | /

[Jeff Schussele]

Hi Deanna,

Sorry I'm an idiot.
Syntax should be
username/PW@ORACLE_SID

You need that @.

Sorry,
Jeff

[Deanna Tran_1]

i have problem when starting the listner...
SQL> connect perfstat/perfstat @daisy
ERROR:
ORA-12541: TNS:no listener

i then try to understand why ?
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
HPUX Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=princess.tucson.ibm.com)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
HPUX Error: 239: Connection refused
Starting /u01/product/9.0.1/bin/tnslsnr: please wait...

TNSLSNR for HPUX: Version 9.0.1.0.0 - Production
System parameter file is /u01/product/9.0.1/network/admin/listener.ora
Log messages written to /u01/product/9.0.1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=princess.tucson.ibm.com)(PORT=1521)))
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00305: the specified path name does not exist

Listener failed to start. See the error message(s) above...

[Deanna Tran_1]

after fixing the lsnterner.ora and tns.ora
i was able to start up the listener again..however...i still can't execute
SQL> connect perfstat/perfstat @daisy
Connected.
SQL> execute statspack.snap
BEGIN statspack.snap; END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

[Printaporn_1]

HI,
just make sure that you correctly connect to the database.

before

SQL> execute statspack.snap

do

SQL> select name from v$database

does it success ?

[Deanna Tran_1]

Answer to your question :

SQL> connect perfstat/perfstat @daisy;
Connected.
SQL> select name from v$database;

NAME
---------
DAISY

SQL> execute statspack.snap
BEGIN statspack.snap; END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

[Yogeeraj_1]

hi,

This could be related to bug 2175112--code change needed in spcpkg.sql

Which version of oracle are you running?

It's fine for me on Oracle 8.1.7.4.

Regards
Yogeeraj

[Yogeeraj_1]

hi again,

Also consider installing the StatsPack.

Connect as internal.

Remove the old installation by running spdrop.sql (8.1.7 and up) or statsdrp.sql (8.1.6 and before).

Then, simply run statscre.sql in 8.1.6 or spcreate.sql in 8.1.7 which are also found in [ORACLE_HOME]\rdbms\admin when connected as internal via SQLP*PLUS.

NB. You'll need to know three pieces of information before running the statscre.sql script. They are:
- The default tablespace for the user PERFSTAT that will be created
- The temporary tablespace for that user
- The name of the tablespace you would like the Statspack objects created in. You will not be prompted for this in 8.1.7, only in 8.1.6. This tablespace needs to have sufficient space for about 60 extents (so the size you need depends on you default initial extent size)

The StatsPack installation will create three .lis file (the names will be displayed to you during the installation). You should review these for any possible errors that might have occurred.

Good luck.

Best Regards
Yogeeraj