Best way to move the modified data after upgrade
Title: Best way to move the modified data after upgrade Kindly read the whole scenario and let me know your views. (1) What we have ? Three db instances on the same machine. Two of the instances are on 8.1.7.4 One of the instance is on 8.1.6.0 Oracle as well as O/S is running on 32-bit. Database is running in archivelog mode. The current box is Sun SPARK E6500. Use BCV backup solutions with Veritas NetBackup for tape integration. The production server (E6500) and the backup server both are physically located in Ohio. The total db size is 1.5 TB. (2) What we are going to do ? Migrate to a new E12K server. Migrate all three instances to 9.2.0.4 Install Oracle on top of 64-bit Sun Solaris 2.9 version. Install 64-bit Oracle software on top of it. The new server is supposed to be located in Arizona. (3) How we are going to do ? Shut down all three instances on the production server. Take the backup on the backup server. Copy the data on to tape. Ship the tapes over to Arizona where the new E12K box resides. Copy all the data from tape on to the new E12K. Upgrade all three instances on the new E12K as mentioned above on DAY1. Back up all the data locally. Now it takes around 2 days to ship all the tapes are shipped to Arizona and then copy the data over to new E12K. During this time the users are still accessing the production server in Mason. Also the etl jobs are updating/inserting/deleting data from this server in those two days. (4) Whether this is possible ? Now lets say we want to apply just the changed data from DAY1 till DAY3 is there any way I can do it ? The only other option we have at this moment is to run all the jobs against the newly configured E12K. (I guess the answer is NO, but just wanted to confirm with u all) -- Thanks, Chetan
Shared Pool Wait while using SQL Loader
Gurus , We run a Oracle 8.1.7.4.0 dw on Sun Solaris 2.8 version and its a data warehouse environment. There are lots of SQL Loader jobs which run on a day-to-day basis. Normally it takes 15-20 mins to finish the loading but today it is taking more than an hour and nothing seems to happen. This is a range partitioned table. Here are the results of the queries on v$ views. SID SEQ# EVENT -- -- P1TEXT P2TEXT P3TEXTWAIT_TIME SECONDS_IN_WAIT STATE -- --- --- 1 26767 pmon timer duration 0 4750 WAITING 113 9126 db file sequential read file#block# blocks -1 0 WAITED SHORT TIME 86 2404 db file scattered read file#block# blocks2 0 WAITED KNOWN TIME 8 7539 smon timer sleep time failed 0 232 WAITING 70 36 SQL*Net message to client driver id#bytes -1 0 WAITED SHORT TIMEhandle address 3830864540 E4565A9C lock address 4121106472 F5A31828 10*mode+namespace31 001F 0 677 WAITING 70 32 SQL*Net message to client driver id1650815232 62657100 #bytes1 0001 0 00 -1 0 WAITED SHORT TIME Please advise ... - Chetan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chindarkar, Chetan (CONS FIN , Contractor) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Shared Pool Wait while using SQL Loader
Here is the information for relevant SID. SID SEQ# EVENT -- -- P1TEXT P1 P1RAW -- P2TEXT P2 P2RAW -- P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE -- -- --- --- 156 43997 library cache lock handle address 3830864540 E4565A9C lock address 4121103412 F5A30C34 10*mode+namespace31 001F 0 101 WAITING And here is the info from v$lock : SELECT * FROM v$lock WHERE sid = 156 ; ADDR KADDR SID TYID1ID2 LMODEREQUEST CTIME BLOCK -- -- -- -- -- -- -- -- 01A83FD4 01A840A0156 TX 65635 213866 6 0 5011 0 F58941AC F58941C0156 TM1548379 0 3 0 5005 0 F5893B94 F5893BA8156 TM 2832 0 3 0 5011 0 F58939B4 F58939C8156 TM 870072 0 3 0 5005 0 F58938C4 F58938D8156 TM1548381 0 3 0 4495 0 F58913BC F58913D0156 TM1548382 0 3 0 4170 0 F5891074 F5891088156 TM1548380 0 3 0 4715 0 7 rows selected. And here is the info from v$locked_object SELECT b.name, a.* FROM v$locked_object a, sys.obj$ b WHERE a.object_id = b.obj# and session_id = 156 ; NAME XIDUSNXIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME -- -- -- -- -- -- -- OS_USER_NAME PROCESS LOCKED_MODE -- - --- COMMENTS1 99 2138661548380156 CDW collprod 85233 COMMENTS1 99 213866 870072156 CDW collprod 85233 COMMENTS1 99 2138661548379156 CDW collprod 85233 COMMENTS1 99 2138661548382156 CDW collprod 85233 COMMENTS1 99 2138661548381156 CDW collprod 85233 PRODUCTION_LOG_DTL 1 99 213866 2832156 CDW collprod 85233 6 rows selected. Please advise .. Thanks - Chetan -Original Message- Sent: Tuesday, July 08, 2003 9:49 PM To: Multiple recipients of list ORACLE-L You queried everything from v$session_wait and you needed to query only the information relevant to the SID that is waiting. Other then that, the only session in WAITING status is waiting for a lock. Your chase is about to continue. Now that you have lock address, go on your way to v$lock table, grasshopper. The legend continues. On 2003.07.08 21:29, Chindarkar, Chetan (CONS FIN , Contractor) wrote: Gurus , We run a Oracle 8.1.7.4.0 dw on Sun Solaris 2.8 version and its a data warehouse environment. There are lots of SQL Loader jobs which run on a day-to-day basis. Normally it takes 15-20 mins to finish the loading but today it is taking more than an hour and nothing seems to happen. This is a range partitioned table. Here are the results of the queries on v$ views. SID SEQ# EVENT -- -- P1TEXT P2TEXT P3TEXTWAIT_TIME SECONDS_IN_WAIT STATE -- --- --- 1 26767 pmon timer duration
RE: Shared Pool Wait while using SQL Loader
Thanks for the script Mladen. I think I figured out what is the problem. There was a long running query which was creating a materialized view which had that table in the where clause. So the update and the SQL Loader jobs were hanging. - Chetan -Original Message- Sent: Wednesday, July 09, 2003 1:09 AM To: Multiple recipients of list ORACLE-L Oh, it's a library cache lock. Here is the script, contributed about 2 days ago by Andy Rivenes: -- FILE: libcache_lock.sql -- -- AUTHOR: Andy Rivenes -- -- DATE: 01/22/2003 -- -- DESCRIPTION: -- Query to display library cache lock/pin blockers and waiters -- Source: Note: 122793.1, HOW TO FIND THE SESSION HOLDING -- A LIBRARY CACHE LOCK -- -- The address of the object should allow access through -- v$open_cursor, v$sql views. -- -- REQUIREMENTS: -- Access to x$ tables (connect as sys or sysdba). -- -- MODIFICATIONS: -- -- SET LINESIZE 132; SET PAGESIZE 60; SET TRIMSPOOL off; -- COLUMN sid HEADING 'SID' FORMAT ; COLUMN objtyp HEADING 'Object|Type' FORMAT A25; COLUMN lktyp HEADING 'Lock|Type' FORMAT A4; COLUMN lkmod HEADING 'Mode|Held' FORMAT A10; COLUMN lkreq HEADING 'Mode|Request'FORMAT A10; COLUMN objaddr HEADING 'Address' FORMAT A10; COLUMN objdef HEADING 'Object' FORMAT A30 WORD_WRAPPED; -- BREAK ON lk.kgllkhdl SKIP 1; -- SELECT s.sid, DECODE(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure/Type', 2, 'Body', 3, 'trigger', 4, 'Index', 5, 'Cluster', 13, 'Java Source', 14, 'Java Resource', 32, 'Java Data', TO_CHAR(ob.kglhdnsp)) objtyp, lk.kgllktype lktyp, DECODE(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', TO_CHAR(lk.kgllkmod)) lkmod, DECODE(lk.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', TO_CHAR(lk.kgllkreq)) lkreq, RAWTOHEX(lk.kgllkhdl) objaddr, DECODE(ob.kglnaown, NULL, '', ob.kglnaown || '.') || ob.kglnaobj || DECODE(ob.kglnadlk, NULL, '', '@' || ob.kglnadlk) objdef FROM v$session s, x$kglob ob, -- dba_kgllock lk ( SELECT kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype FROM x$kgllk UNION ALL SELECT kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin' kgllktype FROM x$kglpn ) lk WHERE lk.kgllkhdl = ob.kglhdadr AND lk.kgllkuse = s.saddr AND lk.kgllkhdl IN ( SELECT DISTINCT kgllkhdl FROM ( SELECT kgllkhdl, kgllkreq FROM x$kgllk UNION ALL SELECT kglpnhdl, kglpnreq FROM x$kglpn ) WHERE kgllkreq 0 ) ORDER BY lk.kgllkhdl, lk.kgllkreq ASC, lk.kgllkmod DESC / That's the best thing I can do to help you. On 2003.07.08 23:39, Chindarkar, Chetan (CONS FIN , Contractor) wrote: Here is the information for relevant SID. SID SEQ# EVENT -- -- P1TEXT P1 P1RAW -- P2TEXT P2 P2RAW -- P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE -- -- --- --- 156 43997 library cache lock handle address 3830864540 E4565A9C lock address 4121103412 F5A30C34 10*mode+namespace31 001F 0 101 WAITING And here is the info from v$lock : SELECT * FROM v$lock WHERE sid = 156 ; ADDR KADDR SID TYID1ID2 LMODEREQUEST CTIME BLOCK -- -- -- -- -- -- -- -- 01A83FD4 01A840A0156 TX 65635 213866 6 0 5011 0 F58941AC F58941C0156 TM1548379 0 3 0 5005 0 F5893B94 F5893BA8156 TM 2832 0 3 0 5011 0 F58939B4 F58939C8156 TM 870072 0 3 0 5005 0 F58938C4 F58938D8156 TM1548381 0 3 0 4495 0 F58913BC F58913D0156 TM1548382 0 3 0 4170 0
RE: SYSDBA privilege with ORAPWD utility
Tom , I set REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE, gave the same name to the pw file as suggested by you (though it was not mentioned anyewhere) and tried; but still it's not working. I can't figure out what can be the problem. Pls help. Chetan -- From: Mercadante, Thomas F[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Wednesday, September 11, 2002 6:58 PM To: Multiple recipients of list ORACLE-L Subject: RE: SYSDBA privilege with ORAPWD utility Chetan, The NAME of the password file should be: PWD{sidname}.ORA. For example, if your Oracle Sid name is SB815, your password file should be named PWDSB815.ORA On NT boxes, upper/lower case of the file name does not matter. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, September 11, 2002 8:38 AM To: Multiple recipients of list ORACLE-L Hi List , I have Oracle 8.1.5 server on Win 2K machine. When I try to connect as SYSDBA using SYS account from a remote machine it says ORA-01017: invalid username/password; logon denied. Then I created a password file using ORAPWD utility and granted SYS and WIP (a newly created schema) the SYSDBA privilege. I also ensured that REMOTE_LOGIN_PASSWORDFILE is set to TRUE, but still I can not connect as SYSDBA. Following are the steps which I took. Step 1. Created a password file as followed : D:\Oracle\Ora815\BINcd ..\dbs D:\Oracle\Ora815\DBSorapwd file=orapwsb815 password=oracle8i entries=30 Step 2. Grant SYS user the SYSDBA privilege by the following method D:\Oracle\Ora815\DBSsvrmgrl Oracle Server Manager Release 3.1.5.0.0 - Production (c) Copyright 1997, Oracle Corporation. All Rights Reserved. Oracle8i Release 8.1.5.0.0 - Production With the Java option PL/SQL Release 8.1.5.0.0 - Production SVRMGR connect internal Password: Connected. SVRMGR grant SYSDBA to SYS; Statement processed. SVRMGR select * from v$pwfile_users ; USERNAME SYSDB SYSOP -- - - INTERNAL TRUE TRUE SYSTRUE TRUE 2 rows selected. Pls help me if possible. Thanks in advance. Chetan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chindarkar,Chetan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chindarkar,Chetan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SYSDBA privilege with ORAPWD utility
Hey , Im able to connect as SYSDBA from all other users (for e.g. WIP, SYSTEM) except SYS. Can anyone pls advise on this. Thanks, Chetan -- From: Yechiel Adar[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 6:53 PM To: Multiple recipients of list ORACLE-L Subject: Re: SYSDBA privilege with ORAPWD utility Hello Chetan I think that you need to have administrator permission on the NT domain to be able to logon as sysdba. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 11, 2002 2:38 PM Hi List , I have Oracle 8.1.5 server on Win 2K machine. When I try to connect as SYSDBA using SYS account from a remote machine it says ORA-01017: invalid username/password; logon denied. Then I created a password file using ORAPWD utility and granted SYS and WIP (a newly created schema) the SYSDBA privilege. I also ensured that REMOTE_LOGIN_PASSWORDFILE is set to TRUE, but still I can not connect as SYSDBA. Following are the steps which I took. Step 1. Created a password file as followed : D:\Oracle\Ora815\BINcd ..\dbs D:\Oracle\Ora815\DBSorapwd file=orapwsb815 password=oracle8i entries=30 Step 2. Grant SYS user the SYSDBA privilege by the following method D:\Oracle\Ora815\DBSsvrmgrl Oracle Server Manager Release 3.1.5.0.0 - Production (c) Copyright 1997, Oracle Corporation. All Rights Reserved. Oracle8i Release 8.1.5.0.0 - Production With the Java option PL/SQL Release 8.1.5.0.0 - Production SVRMGR connect internal Password: Connected. SVRMGR grant SYSDBA to SYS; Statement processed. SVRMGR select * from v$pwfile_users ; USERNAME SYSDB SYSOP -- - - INTERNAL TRUE TRUE SYSTRUE TRUE 2 rows selected. Pls help me if possible. Thanks in advance. Chetan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chindarkar,Chetan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chindarkar,Chetan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SYSDBA privilege with ORAPWD utility
Yechiel , I have administrator permission on the NT domain; but its still not working. What can be possible cause of this ? Thx, Chetan -- From: Yechiel Adar[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 6:53 PM To: Multiple recipients of list ORACLE-L Subject: Re: SYSDBA privilege with ORAPWD utility Hello Chetan I think that you need to have administrator permission on the NT domain to be able to logon as sysdba. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 11, 2002 2:38 PM Hi List , I have Oracle 8.1.5 server on Win 2K machine. When I try to connect as SYSDBA using SYS account from a remote machine it says ORA-01017: invalid username/password; logon denied. Then I created a password file using ORAPWD utility and granted SYS and WIP (a newly created schema) the SYSDBA privilege. I also ensured that REMOTE_LOGIN_PASSWORDFILE is set to TRUE, but still I can not connect as SYSDBA. Following are the steps which I took. Step 1. Created a password file as followed : D:\Oracle\Ora815\BINcd ..\dbs D:\Oracle\Ora815\DBSorapwd file=orapwsb815 password=oracle8i entries=30 Step 2. Grant SYS user the SYSDBA privilege by the following method D:\Oracle\Ora815\DBSsvrmgrl Oracle Server Manager Release 3.1.5.0.0 - Production (c) Copyright 1997, Oracle Corporation. All Rights Reserved. Oracle8i Release 8.1.5.0.0 - Production With the Java option PL/SQL Release 8.1.5.0.0 - Production SVRMGR connect internal Password: Connected. SVRMGR grant SYSDBA to SYS; Statement processed. SVRMGR select * from v$pwfile_users ; USERNAME SYSDB SYSOP -- - - INTERNAL TRUE TRUE SYSTRUE TRUE 2 rows selected. Pls help me if possible. Thanks in advance. Chetan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chindarkar,Chetan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chindarkar,Chetan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SYSDBA privilege with ORAPWD utility
Hi List , I have Oracle 8.1.5 server on Win 2K machine. When I try to connect as SYSDBA using SYS account from a remote machine it says ORA-01017: invalid username/password; logon denied. Then I created a password file using ORAPWD utility and granted SYS and WIP (a newly created schema) the SYSDBA privilege. I also ensured that REMOTE_LOGIN_PASSWORDFILE is set to TRUE, but still I can not connect as SYSDBA. Following are the steps which I took. Step 1. Created a password file as followed : D:\Oracle\Ora815\BINcd ..\dbs D:\Oracle\Ora815\DBSorapwd file=orapwsb815 password=oracle8i entries=30 Step 2. Grant SYS user the SYSDBA privilege by the following method D:\Oracle\Ora815\DBSsvrmgrl Oracle Server Manager Release 3.1.5.0.0 - Production (c) Copyright 1997, Oracle Corporation. All Rights Reserved. Oracle8i Release 8.1.5.0.0 - Production With the Java option PL/SQL Release 8.1.5.0.0 - Production SVRMGR connect internal Password: Connected. SVRMGR grant SYSDBA to SYS; Statement processed. SVRMGR select * from v$pwfile_users ; USERNAME SYSDB SYSOP -- - - INTERNAL TRUE TRUE SYSTRUE TRUE 2 rows selected. Pls help me if possible. Thanks in advance. Chetan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chindarkar,Chetan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Performance Tuning steps
Thnx Mike , U saved at least 50-60 bucks of mine. Nywayscan anyone tell me few linkswhere I can find good documentation on Oracle Performance Tuning ? (Or books if they are worth byuing .) Thnx in advance -Chetan "Johnson, Michael " <[EMAIL PROTECTED]>wrote: I have seen no other books thatdeal withOraclewaits the way Gaja and Kirti's book does.After all, the waits are what you are trying todiagnose in a slow system. There are several web sites floating around that have some good technical papers on diagnosing slow systems. IMHO, I find that Oracle Books are like Stock Trading books. There are only a few good ones floating around, but alot of them offer advice that isdown right dangerous, so choose wisely. FWIW. Mike -Original Message-From: Chetan [mailto:[EMAIL PROTECTED]]Sent: Friday, June 28, 2002 4:13 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Oracle Performance Tuning steps Thanks , I looked into the database. There r some waits happening on the undo blocks (non-system) but could not figure out whether this could possibly cause such a slowdown of the system. Also there were some indexes newly created on some of the tables which are causing problems. What's the best approch anyways to hunt down the problem in a situation like this ? - Chetan BigP <[EMAIL PROTECTED]>wrote: cheten , find processid of the application , look into database waits , that will give u some hint .Also look into db buffers to find if there are full table scans flushing db buffer . btw Did u ran statistics ? -Bigp - Original Message - From: Chetan To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 26, 2002 3:38 AM Subject: Oracle Performance Tuning steps Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan Chindarkar Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World Cup Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World CupDo You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup
Re: Oracle Performance Tuning steps
Thanks , I looked into the database. There r some waits happening on the undo blocks (non-system) but could not figure out whether this could possibly cause such a slowdown of the system. Also there were some indexes newly created on some of the tables which are causing problems. What's the best approch anyways to hunt down the problem in a situation like this ? - Chetan BigP <[EMAIL PROTECTED]>wrote: cheten , find processid of the application , look into database waits , that will give u some hint .Also look into db buffers to find if there are full table scans flushing db buffer . btw Did u ran statistics ? -Bigp - Original Message - From: Chetan To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 26, 2002 3:38 AM Subject: Oracle Performance Tuning steps Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan Chindarkar Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World CupDo You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup
Re:Oracle Performance Tuning steps
Dick , Thanks a ton for ur feedback. I ran UTLBSTAT/UTLESTAT and found out that the problem was with memory. Then I went and queried V$SGASTAT, V$SQLAREA, V$SQLTEXT. Can u tell me if there can be any other problems ? I found out later that there were some indexes added afterwards. Thanks nyways. Chetan [EMAIL PROTECTED] wrote: Chetan,First questions, What changed? Did someone add/delete an index? Was therean oversized data load done? When were the statistics, if present, lastcomputed? Has a new program/application been added to the server? Did a memorybank go offline? Did a disk interface card go offline?I use HP's HP-UX boxes as well, except most of my DB's are 10 times largerthen yours. Just about everytime something like that happens, especially all ofa sudden, one of those questions is the cause. The last item I'd go lookingfor, have only had one instance of it so far, is a unrestricted user whoreleased "the query from HELL" into the database as a "one time requirement". If you can find him/her I've a used gallows rope is still here, one usage only.Dick GouletReply Separator____Author: Chetan <ORACHETAN@YAH! ! OO.COM>Date: 6/26/2002 2:18 AMHi guys ,Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX.The application was running fine uptil yesterday. Suddenly a part of the applnis running extremely slow. I can not figure what might be the problem. Wanted totrack this down asap.Here is some information about the db.Database size - 20GBOptimizer - CHOOSEDisk Structure - RAID 1+0No. of processors - 4Block Size - 8KArchivelog mode : ARCHIVELOGPlease tell me what should be the ideal way I should try to trace the problem. Ithought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run thatpart of the appln. Has anybody worked with STATSPACK before ?Can anybody tell me what should accurate and fastest way to hunt down theproblem ? I think its something to do with indexes or changes in the queries.Also can someone tell me the ide! ! al backup strategy for this database consideringthe fact that it's a 24x7 system.Thanks in advance .Chetan Chindarkar-Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World Cup Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX.The application was running fine uptil yesterday. Suddenly a part of the applnis running extremely slow. I can not figure what might be the problem. Wanted totrack this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace theproblem. I thought of running UTLBSTAT/UTLESTAT orSTATSPACK and asked the user to run that part of the appln. Hasanybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt downthe problem ? I think its something to do with indexes or changes in thequeries. Also can someone tell me the ideal backup strategy for thisdatabaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan Chindarkar Do You Yahoo!?<Ahref="http://rd.yahoo.com/welcome/*http://fifaworldcup.yahoo.com/fc/en/spl"Sign-up for Video Highlights of 2002 FIFA World Cup-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).Do You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup
Oracle Performance Tuning steps
Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan ChindarkarDo You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup
Oracle Performance Tuning steps
Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan ChindarkarDo You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup