Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

try to get snap shot --- error out

Deanna Tran_1
Frequent Advisor

try to get snap shot --- error out

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
11 REPLIES
Jeff Schussele
Honored Contributor

Re: try to get snap shot --- error out

Hi Deanna,

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

Rgds,
Jeff
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Deanna Tran_1
Frequent Advisor

Re: try to get snap shot --- error out

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
Honored Contributor

Re: try to get snap shot --- error out

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
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Deanna Tran_1
Frequent Advisor

Re: try to get snap shot --- error out

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
Honored Contributor

Re: try to get snap shot --- error out

Hi Deanna,

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

You need that @.

Sorry,
Jeff
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Deanna Tran_1
Frequent Advisor

Re: try to get snap shot --- error out

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
Frequent Advisor

Re: try to get snap shot --- error out

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
Esteemed Contributor

Re: try to get snap shot --- error out

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 ?

enjoy any little thing in my life
Deanna Tran_1
Frequent Advisor

Re: try to get snap shot --- error out

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
Honored Contributor

Re: try to get snap shot --- error out

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: try to get snap shot --- error out

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)