SEARCH YOUR SOLUTION HERE  

Tuening issues

Hi Folks,,,,

please help me,,,,,,

OS - Win NT
DB - Oracle 8.1.5 Prod.

I get the following error often whenever i run the batch process,,,
and other users also getting the same error in the client side.

" User requested cancel of current operation"

I think this is the tuening issue of DB.

Memory - 512.

Can anyone give some solution for this,, bcoz every user is struggling to access with the db.

Thanx in advance.

Posted On: Thursday 15th of November 2012 09:06:54 PM Total Views:  331
View Complete with Replies




Related Messages:

Transportable tablespace issues   (477 Views)
I am doing TTS from TRU64 (Bid endian format)to HPUX (little endian format).... having issues during impdp steps at source: --------------- begin sys.dbms_tts.transport_set_check('TESTTBLSPC',TRUE); end; / ....repeated for two tablespaces alter tablespace TESTTBLSPC read only; - did for both tablespaces expdp userid=userid/pwd DIRECTORY=DATA_PUMP_DIR DUMPFILE=TTS.dmp LOGFILE=TTS.log TRANSPORT_TABLESPACES=TESTTBLSPC,TESTTBLSPCS TRANSPORT_FULL_CHECK=y steps at target: -------------- ftped the dumpfile and the two datafiles to the respective location /dbfiles/oradata/DBNAME and the target database is up and running The export contains two tablespaces with each one datafile... i am trying to import one tablespace impdp userid=ramesh/ramesh DIRECTORY=DATA_PUMP_DIR DUMPFILE=TTS.dmp LOGFILE=TTSimp.log TRANSPORT_DATAFILES=/dbfiles/oradata/DBNAME/testtblspc.dbf KEEP_MASTER=y error: ORA-39123: Data Pump transportable tablespace job aborted ORA-01565: error in identifying file '/dbfiles/oradata/DBNAME/testtblspc.dbf' ORA-27037: unable to obtain file status HPUX-ia64 Error: 2: No such file or directory Additional information: 3 Job "USERID"."SYS_IMPORT_TRANSPORTABLE_02" stopped due to fatal error at 16:43:24 1.DOES THE MOUNT POINT NAME ON THE TARGET HAS TO BE THE SAME AS SOURCE 2. INSTEAD OF DOING ONE DATAFILE AT A TIME FOR EACH TABLESPACE.. CAN I DO MULITPLE TABLESPACES USING TRANSPORTABLE_TABLESPACES = TABLESPACENAME.dbf, TABLESPACENAME2.dbf Many
Too many sessions - performance issues!   (168 Views)
I have a 10GB database with around 30 concurrent users. Using 8.1.6 on NT (Dont laugh!) With just 15 connected users, i find that there are 150 sessions and the performance has slowed down considerably. I just exp/imped from 7.3.4 to 8.1.6. How can I go about tuning this When it 7.3.4 I has an acceptable response time. Do you thing switching to MTS will help Nizar
system tablespace space issues   (164 Views)
TS_NAME TOTAL_MB USED_MB FREE_MB USED_PCT --------------- ----------- ----------- ----------- -------- SYSTEM 325.00 294.20 30.80 90.5 hi, is the above acceptable i got it from my database.
sys.dual any issues?   (126 Views)
Is there any known issues with selecting as sys.dual. Some of our application modules are coded with selections from above kind of query. We are getting calls from users that their sessions are hung. But when we see their sessions stats at database as inactive. The last query executed by them mostly the query with selection as above. An sample query is given below. Select userenv('sessionid') from sys.dual
Privilege issues   (108 Views)
I have a table name A and owned ny JOHN_DO, this table is granted to public. Another user named KATHY logged into the database and do a SELECT statement from table A. Here is the problem. KATHY have do this statement SELECT * from JOHN_DO.A I thought KAthy doesn't have to have JOHN_DO in front of the A table since JOHN_DO already granted table A to public. Any way, I want to be able to query A table by typing SELECT * FROM A how do I do that
Sql Trace to detect concurrency issues   (108 Views)
I have just started using oracle and currently working on the concurrency issues in it. For this I want to take the sql trace of various queries that are encountered by server along with transaction id (or session id) and time at which the query is executed. (The time and transaction id will help finding conflicts between transactions.) I tried to do it with the standard Sql_trace option, but the trace files generated are quite difficult to parse for above info. Instead what I would like is a trigger that dumps the sql text in a file/ database table along with session id and current time . I want to know 1) if this is a feasible approach (if yes, the the event that occurs for each query so that I can write a trigger on it.) 2) is there any other simpler approach to this problem
Performance issues!!!   (236 Views)
I have a questions. There are two databases and they are the same running on two seperate server, one on NT and one on Linux I think. The question is I have one query and I run on one machine take about 3 min and the other machine take more than 1 hour. In this situation, what could it be the reason What do you I need to look for to improve on the second server. Please help.
Several issues w/ Windows 2000   (136 Views)
Hi all, I just downloaded Oracle 8.17 from the otn website however I ran into some problems when I tried to install this on my windows 2000 pc. My first problem is that I don't know how to remove my previous version of ORacle completely.Here is what i did, first, I removed the whole file structure and clean out the whole drive but somehow Oracle Installer recognizes my old database and asks me to migrate it to the new version. Secondly, I have problem using OEM console. What do I have to enter for the following fields at the logon screen: Administrator :( can i put sys or sytem here) Password: Management Server: ( I have no idea what to put for this field.. ) If you know how to resolve the 2 problems above plse give me a hand THx in adv, newbie
RMAN incremental backup failures - TSM TDP issues   (198 Views)
I get the below errors every once in a while when doing a "level 1 incremental backup" with 2 channels. I have little idea why. It doesn't fail at any consistent point..the failure will sometimes occur at the beginning and other times half way through the backup. I'm at a loss here. Ideas anyone Below are the RMAN and TSM logs. Pay attention to "09/22/04 22:10:34 ANE4994S (Session: 41266, Node: DWDEV01_TDPORA) TDP Oracle SUN ANU0599 ANU2602E The object /dwdev//dwdev_incr1_datafiles_537573609_5630_1 was not found on the TSM Server(SESSION: 41266)" in the TSM log. The backup piece name being written is unique! What the heck is a "not found" supposed to mean! Beats me. RMAN logfile : Recovery Manager: Release 9.2.0.4.0 - 64bit Production Copyright (c) 1995, 2002, Oracle Corporation. rights reserved. connected to recovery catalog database 2> run 3> { 4> allocate channel t1 type 'sbt_tape' parms 5> 5> 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/DWDEV/tdpo.opt)'; 6> allocate channel t2 type 'sbt_tape' parms 7> 7> 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/DWDEV/tdpo.opt)'; 8> BACKUP INCREMENTAL LEVEL = 1 CUMULATIVE 9> tag='DWDEV LVL 1 CUM DB BKUP' 10> format 'dwdev_incr1_datafiles_%t_%s_%p' 11> filesperset 5 12> DATABASE; 13> sql 'alter system archive log current'; 14> backup 15> format 'dwdev_incr1_archlog_%t_%s_%p' 16> ARCHIVELOG ALL not backed up 2 times TAG='DWDEV Archivelog bkup'; 17> BACKUP format 'dwdev_ctrl_file_%t_%s_%p' 18> current controlfile 19> TAG='DWDEV curr controlfile bkup'; 20> release channel t1; 21> release channel t2; 22> } 23> connected to target database: DWDEV (DBID=294984608) allocated channel: t1 channel t1: sid=17 devtype=SBT_TAPE channel t1: Tivoli Data Protection for Oracle: version 5.2.0.0 allocated channel: t2 channel t2: sid=16 devtype=SBT_TAPE channel t2: Tivoli Data Protection for Oracle: version 5.2.0.0 Starting backup at 22-sep-2004 22:00:06 channel t1: starting incremental level 1 datafile backupset channel t1: specifying datafile(s) in backupset input datafile fno=00050 name=/u14/oradata/DWDEV/opeagg_dat1_03.dbf input datafile fno=00052 name=/u10/oradata/DWDEV/opeagg_dat1_01.dbf input datafile fno=00010 name=/u03/oradata/DWDEV/development01.dbf input datafile fno=00019 name=/u09/oradata/DWDEV/rbsbig1_01.dbf input datafile fno=00049 name=/u16/oradata/DWDEV/opeagg_dat1_04.dbf channel t1: starting piece 1 at 22-sep-2004 22:00:09 channel t2: starting incremental level 1 datafile backupset channel t2: specifying datafile(s) in backupset input datafile fno=00051 name=/u13/oradata/DWDEV/opeagg_dat1_02.dbf input datafile fno=00057 name=/u05/oradata/DWDEV/opeagg_idx1_01.dbf input datafile fno=00016 name=/u11/oradata/DWDEV/prodrbs1_01.dbf input datafile fno=00053 name=/u14/oradata/DWDEV/opeagg_dat2_02.dbf input datafile fno=00061 name=/u10/oradata/DWDEV/ope_dat1_03.dbf channel t2: starting piece 1 at 22-sep-2004 22:00:09 channel t2: finished piece 1 at 22-sep-2004 22:06:44 piece handle=dwdev_incr1_datafiles_537573609_5631_1 comment=API Version 2.0,MMS Version 5.2.0.0 channel t2: backup set complete, elapsed time: 00:06:35 channel t2: starting incremental level 1 datafile backupset channel t2: specifying datafile(s) in backupset input datafile fno=00055 name=/u02/oradata/DWDEV/opeagg_dat2_03.dbf input datafile fno=00031 name=/u15/oradata/DWDEV/category_dat1_01.dbf input datafile fno=00047 name=/u15/oradata/DWDEV/opeaggarch_dat1_01.dbf input datafile fno=00058 name=/u03/oradata/DWDEV/opeagg_idx1_03.dbf input datafile fno=00045 name=/u09/oradata/DWDEV/opeagg2004_dat1_01.dbf channel t2: starting piece 1 at 22-sep-2004 22:06:44 released channel: t1 released channel: t2 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on t1 channel at 09/22/2004 22:07:29 ORA-27206: requested file not found in media management catalog ORA-19502: write error on file "dwdev_incr1_datafiles_537573609_5630_1", blockno 513 (blocksize=512) ORA-27030: skgfwrt: sbtwrite2 returned error ORA-19511: Error received from media manager layer, error text: ANS1017E (RC-50) Session rejected: TCP/IP connection failure Recovery Manager complete. ---------------------- TSM logs 09/22/04 22:10:34 ANR0406I Session 41265 started for node DWDEV01_TDPORA (TDP Oracle SUN) (Tcp/Ip 172.16.0.25(35081)). (SESSION: 41265) 09/22/04 22:10:34 ANR0403I Session 41265 ended for node DWDEV01_TDPORA (TDP Oracle SUN). (SESSION: 41265) 09/22/04 22:10:34 ANR0406I Session 41266 started for node DWDEV01_TDPORA (TDP Oracle SUN) (Tcp/Ip 172.16.0.25(35082)). (SESSION: 41266) 09/22/04 22:10:34 ANE4994S (Session: 41266, Node: DWDEV01_TDPORA) TDP Oracle SUN ANU0599 ANU2602E The object /dwdev//dwdev_incr1_datafiles_537573609_5630_1 was not found on the TSM Server(SESSION: 41266) 09/22/04 22:10:34 ANR0403I Session 41266 ended for node DWDEV01_TDPORA (TDP Oracle SUN). (SESSION: 41266) 09/22/04 22:10:34 ANR0403I Session 41260 ended for node DWDEV01_TDPORA (TDP Oracle SUN). (SESSION: 41260)
Replication issues   (130 Views)
Couple of issues regarding replication: We have replicated two database at two different sites and are in the testing phase now. 1) We tried bring the dblinks down on both sides, do some transactions and bring the db up. When we bought the db up,but the data wasnt being pushed to the remote dbs and we had to push it manually. Do you know why this happened Also, when we checked the jobs, we saw that one of the jobs had been broken. Is there a way to bring the broken job to normal state automatically through any dbms_jobs. I'd really appreciate an example on this , if any. 2) If we check the deferror table, I saw a lot of errors mainly ORA-01403: no data found. I think this has to do with finding null values prior to doing an update. Is there a way to overcome this error 3) And also if we are setting it up for a failover scenario, how do I set the tnsnames.ora file.
Privilege issues   (127 Views)
I have a question regarding to privilege. I created an user and grant CONNECT, RESOURCE to him. Is it possible for me to lock him out so he can't see any of the public synonym and only grant him and privilege to see certain tables Please explain and help me with what I need to do.
performance issues   (121 Views)
Hi There, I have a slight issue with a new configuration I have. The database is an 8.1.7 running Sun accounts (Oracle version) on hp-ux 11.00 Currently the server has 12 disks which use mirrordisk-ux: Data is currently laid out as follows: 4 tablespaces: RBS SYSTEM TEMP USERS Note there is no tablespace for indexes (which you would expect) but Sun Accounts seem keen to not to have this. Indexes need adding. Tablespaces/Datafiles are as follows: RBS - Total size - 1gb - /u02/oradata/sunlive/rbs01.dbf SYSTEM - 100Mb - /u03/oradata/sunlive/system01.dbf TEMP - 500Mb - /u03/oradata/sunlive/temp01.dbf USERS - 8gb - /u04/oradata/sunlive/usr01.dbf /u04/oradata/sunlive/usr02.dbf /u04/oradata/sunlive/usr03.dbf /u04/oradata/sunlive/usr04.dbf There are currently 4 x 2gb datafiles all on the same disc. This is my biggest issue. I would prefer to spread the i/o over four discs and take it from there. What would else recommend Also is there any benefit to using large files i.e over 2gb
Performance issues   (107 Views)
I have a job which is performing very badly and following are the output of the tkprof done on the trace. Can someone please advice on what can be done to improve the performance. Kind
standby - rebuilding issues   (245 Views)
, If I failover to a standby and made it as primary database, can I bring it back again as standby without rebuilding it(Assuming that I have not done any database activities) We are using 8i. As per Oracle documentation, once you activate the standby, that becomes the primary and you can't return make it as standby again without rebuilding it. One of the DBA says that you can bring the standby (which is primary now) to read-only mode and bring it back to standby again... Any thoughts
Space issues   (122 Views)
Hi all, how can i shrink the space used by tablespaces, I have some table reaching to 99% used and want to shrink it. how can i make it automated job to shrink if the usage is more than 70% Please help and guide!!
Sequence issues   (249 Views)
Does anyone know a way to find the current value of a sequence with using currval or dba/all_sequences It seems that use to select sequence.currval one needs to use sequence.nextval first in the session. Heres a script that I used to prove this: SQL> create sequence myseq start with 22; Sequence created. SQL> select myseq.currval from dual; select myseq.currval from dual * ERROR at line 1: ORA-08002: sequence MYSEQ.CURRVAL is not yet defined in this session SQL> SQL> select myseq.nextval from dual; NEXTVAL ---------- 22 SQL> SQL> select myseq.currval from dual; CURRVAL ---------- 22 Weired eh. I'd love to know a way to get round this. Also one may think that dba_sequences.last_number would provide the current number however this only provides the Last sequence number written to disk. "If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used." Anyone know a way round this
performace issues   (143 Views)
Hi i have a perfromace oriented database. I ran toad utility and got the following : slave wait : 18000 chained fetch ration of .032 (is this significant) Buffer_hit_ratio : 99.89 % please give suggestion to imporve performace. ALSO IF THERE ARE SCRIPTS TO FIND QUERIES NOT UTILIZING INDEX PLEASE LET ME KNOW
MySQL 5.x and ASP.NET ADO 2.0 framework connection issues....   (197 Views)
I have tried every way possible to connect to the database with every example on the web and have no luck. I now have 9 projects in VS that cannot connect after trying to tweak each one. Even temping was purchasing the "Professional ADO.NET 2.0" book, but all the poor reviews has me wondering if the samples, which other readers say they cannot download, even work What I wish to do is use the 2.0 framework in VB.NET to create an ASP.NET page, pull data from a stored procedure, function, hard coded SQL - ANY connection to the DB will do. I can make the procs once I can connect but can never connect. NOT wanting to use ODBC is an issue it seems I want to use a native driver, not the ODBC nightmare many layer object model. So far nothing out there is even remotely useful that I have tried or seen. Unfortunately, I am stuck with MySQL 5, VB.NET, and cannot use ODBC connections....[:(] Can anyone please help this very frustrated Oracle guy
Oracle 9i / IIS 5 Application connection issues   (170 Views)
I have an application residing on a Windows 2000 Server with IIS 5. During peak usage, the app can have as many as 80 to 120 concurrent users. During this time a number of users will experience connection issues. Most commonly the following error appears on the screen: "System.InvalidOperationException: Connection must be open for this operation at Oracle.DataAccess.Client.OracleDataReader.GetString(Int32 i) ..... " We are connecting to an Oracle 9i database running on a Sun/Solaris system with the connection string: "PublicOracledb As String = "Data Source=(DESCRIPTION=" _ + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=servername)(PORT=portnumber)))" _ + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=servicename)));" _ + "User Id=user;Password=password;Connection Lifetime=120;" _ + "Connection Timeout=60; Incr Pool Size=5; Decr Pool Size=2;" We make connections as necessary and close them asap as follows (example): Try dsEU = New DataSet Dim var As String = "string" strSQL = "sqlstring" daEU = New OracleDataAdapter daEU.SelectCommand = New OracleCommand daEU.SelectCommand.Connection = conn daEU.SelectCommand.CommandText = strSQL If conn.State = ConnectionState.Open Then 'Do nothing Else conn.Open() End If daEU.Fill(dsEU, "tblname") If conn.State = ConnectionState.Open Then conn.Close() End If Catch ex As Exception lblError.Text = ex.ToString Finally If conn.State = ConnectionState.Open Then conn.Close() End If End Try Any ideas why we are getting so many connection errors The application is very heavy on database connections. Any ideas to resolve
Grouping issues !!!!   (134 Views)
i haven't had this issue before... i am grouping on invoice_no and trying to calculate a value for a commission based on the gm for an invoice, and sql returns the calculation per line number in the invoice; here is the code: select b.invoice_no as invoice, c.salespersoncode as salesid, c.name as salesperson, br.shortname as branch, sum(b.gm) as gm, (c.base*40) as wkly_base, sum(case when sum(b.gm) between 0 and 50 then sum(b.gm)*0 when sum(b.gm) between 51 and 60 then sum(b.gm)*.06 when sum(b.gm) between 61 and 70 then sum(b.gm)*.07 when sum(b.gm) between 71 and 80 then sum(b.gm)*.08 when sum(b.gm) between 81 and 100 then sum(b.gm)*.09 when sum(b.gm) between 101 and 125 then sum(b.gm)*.1 when sum(b.gm) between 126 and 160 then sum(b.gm)*.12 when sum(b.gm) between 161 and 200 then sum(b.gm)*.13 when sum(b.gm) between 201 and 250 then sum(b.gm)*.14 when sum(b.gm) between 251 and 1000 then sum(b.gm)*.15 when sum(b.gm) > 1000 then sum(b.gm)*.15 end) as commission from bfiles b inner join commissions c on c.salespersoncode = b.salesman inner join branch br on br.branchid = b.branch where b.year = @year and b.week between @startweek and @endweek group by br.shortname,c.salespersoncode,c.name,c.base,b.invoice_no,b.gm order by b.invoice_no any thoughts on how to fix this i really need it to add up the gm values and group it by invoice_no, then calculate the commission...