RE: Bad header found during backing up datafile
Drop the index, create it in another tbs/disk; check if the error(s) appear during the next RMAN bkp run. If it doesn't then it would confirm that your problem is real. Roger I have never received this error (or didn't read the log closely enough). I think I heard somewhere that is how RMAN handles blocks that are updated while it is reading them. I do remember that RMAN's method of handling this is different from hot backups. One thing you might consider is exporting the table. If you can't export to disk, you could export to /dev/null. Several people on this list have mentioned that RMAN doesn't catch all data corruption problems, but I think (experts correct me if I'm wrong) that export will catch all forms of data corruption. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 28, 2003 3:59 PM To: Multiple recipients of list ORACLE-L In our development box, when RMAN is running, I see the following messages only in the alert.log quite often. And it seems to me always happens in the index tablespace. I ran dbverify and validate structure of the whole database with no corruption reported. Any comments? *** Corrupt block relative dba: 0x13c2c44c (file 79, block 181324) Bad header found during backing up datafile Data in bad block - type: 48 format: 1 rdba: 0x3939390a last change scn: 0x3537.31333031 seq: 0x34 flg: 0x38 consistency value in tail: 0x791b0602 check value in block header: 0x3431, block checksum disabled spare1: 0x4, spare2: 0x31, spare3: 0x330 *** Reread of blocknum=181324, file=/oracle/DV2/sapdata3/btabi_15/btabi.data15. found valid data Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Corniche Park 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: Inactive Sessions/Java
and :-:-using connection pooling to share them between your :-:-processes, somewhat :-:-reducing the overhead to create drop a connection for :-:-every interaction :-:-with database. This is generally a good thing. :-:- :-:-Tanel. :-:-- Original Message - :-:-To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] :-:-Sent: Thursday, August 28, 2003 8:29 PM :-:- :-:- :-:- :-:- :-:- Hi All, :-:- :-:- I have a problem with inactive sessions (despite having set the :-:- sqlnet.expire parameter) :-:- :-:- Some of the users connect to the database via a java :-:-process - when I :-:-stop :-:- this process from running the number of sessions just :-:-shots right down. :-:- :-:- Short of scheduling a start and restart of this process :-:-can anybody else :-:- come up with a solution?? :-:- :-:- I am reluctant to touch the java process as this is part :-:-of the production :-:- box. (plus my knowledge of java is hovering over zero percent) :-:- :-:- Any ideas appreciated, :-:- :-:- thanks, :-:- :-:- N. :-:- :-:- :-:- :-:--- :-:-Please see the official ORACLE-L FAQ: http://www.orafaq.net :-:--- :-:-Author: Tanel Poder :-:- 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). :- :- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuala Cullen 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuala Cullen 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Corniche Park 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
Re: How to keep root out?
Put the following code snippet if [ $LOGNAME = root ]; then init 0 fi; in your oraenv. I guarantee you that the SA will no longer be connecting as SYSDBA. May be it will happen once. A smart SA will suppress it next time. OR he/she can always create another OS account with id = 0,gid (root) and then use that subsequently while trying to use oracle OR log in as 'x' which is a non root account and then su root, followed by cd $ORACLE_HOME, source .profile/oraenv, get going. GovindanK MessageBetter yet, put the following lines echo ORA-600 [kgfdjjks] [scdcsc] [dssdcdcsdc] [45] [999] Unauthorized root access then print some garbage into a file named like the regular trace files in user_dump_dest directory. Open up a iTAR and show this trace file to your SA's manager, along with the TAR number. Let the fun begin. - Original Message - From: Mladen Gogala To: Multiple recipients of list ORACLE-L Sent: Thursday, August 28, 2003 1:04 PM Subject: RE: How to keep root out? Put the following code snippet if [ $LOGNAME = root ]; then init 0 fi; in your oraenv. I guarantee you that the SA will no longer be connecting as SYSDBA. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Walter K Sent: Thursday, August 28, 2003 11:34 AM To: Multiple recipients of list ORACLE-L Subject: How to keep root out? Just for grins, I'll ask this question... Is there any way to keep the Unix root user from logging into the database (i.e. connect internal or / as sysdba)? Currently using 8.1.7.4 on Solaris 8 here. We have a couple people in our Unix admin group that feel the need to help by writing their own DB monitoring scripts. Of course, they don't know what they're talking about. They do not have formal logins for the database, but since they are root users they are connecting via connect internal. This is not only counterproductive but actually a potential security issue--just because someone has root doesn't necessarily entitle them to see the data in the database. What if it is a payroll database? So, I'm curious, is there any way to prevent access via connect internal or / as sysdba? Thanks in advance. W -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Corniche Park 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: get sid (session id) and serial#?
But the user need select access to v$mystat connect as sysdba and grant select on v_$mystat to user; HTH GovindanK Thanks all for the input. the script is select sid,serial# from v$session where sid=(select sid from v$mystat where rownum=1) But the user need select access to v$mystat Chuan -Original Message- Sent: Friday, 29 August 2003 15:19 To: Multiple recipients of list ORACLE-L Hi Chuan, can v$session help you this view has information like machine, osuser, username, sid, program, and others SQL desc v$session SQL select columns,... from v$session where username = 'SCOTT' and machine = 'YOUR_HOSTNAME' if you do telnet you will get 2 rows (if scoot is only use by you) is not Sinardy -Original Message- Sent: 29 August 2003 12:34 To: Multiple recipients of list ORACLE-L DABs, Is there any way in my connection to get the sid and serial# for my own connection? Suppose I connect to Oracle db by sqlplus scott/[EMAIL PROTECTED] In this connection, SQL What shoud I input to get this sid and serial#? TIA Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing 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). Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Corniche Park 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: UNIX and Oracle
You may take a look at the book Oracle for Linux DBA's. Design and Implementation of the [UNIX || 4.x BSD] Operating System by [Bach || McCusic et all] Should both have excellent overviews of shared memory, semaphores, and why they're useful. If you want even more nitty gritty, pick up a decent book on pthreads programming and you'll also learn about things like mutex locks, etc. -- Rich Holland(913) 645-1950SAP Technical Consultant print unpack(u,92G5S\=\!A;F]T:5R(\'!EFP\@:%C:V5R\[EMAIL PROTECTED]); -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Veeraraju_Mareddi Sent: Friday, August 22, 2003 10:19 AM To: Multiple recipients of list ORACLE-L Subject: UNIX and Oracle Dear Friends, Any good article to explain the above subject, SHARED Memory Sema phores on SUN / LINUX for Oracle. I just know what is shared memory , sema phores are. But never involved practically much. Please also send me some typical configurations , with explanation(if possible ) Please send me details about ur UNIX production Oracle systems, and lot of examples. Any good we site with pictorial information. NOTE: This is just for information only. Thanks a lot. Regards Rajuveera ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rich Holland 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Corniche Park 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).
Shared Pool Utilization
(actual): 201326592bytes (192MB) (B) Percentage Utilized: 247% PL/SQL procedure successfully completed. I understand F2 and F are tallying. But how can one say that there exists some free memory in Shared Pool when the Utilization seems to have exceed the actual allocation. Where is the mismatch? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Corniche Park 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 Utilization
I ran the query given, but that doesnot answer my question.
I am asking what is wrong with the script(s)?
I don't trust scripts I didn't write myself, and not even some of those.
;)
What does this reveal?
select a.value + ( b.value * c.value )
from
( select sum(value) value from v$parameter
where name like '%pool%'
) a
, v$parameter b
, v$parameter c
where b.name = 'db_block_buffers'
and c.name = 'db_block_size'
As long as you're not using some of the newer 9i memory allocation
parameters, this
should be pretty close to the amount of memory you've allocated in
init.ora.
Now see how much is in the SGA:
select sum(value) from v$sga;
Should be fairly close to the first number.
Jared
Corniche Park [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
08/27/2003 12:44 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Shared Pool Utilization
I have two databases, one in which the shared_pool utilization is not
exceeded (development) and the other one in which it appears that the
shared_pool utilization is exceeded.
The queries i am using are
1. COL Free_Mem FORMAT 99.99 heading 'Free|Mem|(Mb)'
SELECT pool, name, bytes/(1024*1024) Free_Mem FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool'
/
2. Metalink note
SGA/Shared Pool Utilization - Metalink Note : 105004.1 (modified for
bytes-- Mb and nvl clauses)
set serveroutput on size 100;
DECLARE
object_mem NUMBER;
shared_sql NUMBER;
cursor_mem NUMBER;
mts_memNUMBER;
used_pool_size NUMBER;
free_mem NUMBER;
pool_size VARCHAR2(512); -- same AS V$PARAMETER.VALUE
BEGIN
-- Stored objects (PACKAGEs, views)
--V$DB_OBJECT_CACHE
--This view displays database objects that are cached in the
library cache. Objects include
--tables, indexes, clusters, synonym definitions, PL/SQL
procedures and packages, and triggers.
--http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch346.htm#1113348
SELECT SUM(NVL(sharable_mem,0)) INTO object_mem FROM
v$db_object_cache;
--
--
-- User Cursor Usage -- run this during peak usage.
-- assumes 250 bytes per open cursor, FOR each concurrent user.
-- V$SQLAREA lists statistics on shared SQL area and contains one
row per SQL string.
-- It provides statistics on SQL statements that are in memory,
parsed, and ready for execution.
SELECT SUM(250*users_opening) INTO cursor_mem FROM v$sqlarea;
-- For a test system -- get usage FOR one user, multiply by # users
-- SELECT (250 * value) bytes_per_user
-- FROM v$sesstat s, v$statname n
-- WHERE s.statistic# = n.statistic#
-- AND n.name = 'opened cursors current'
-- AND s.sid = 25; -- WHERE 25 is the sid of the process
-- MTS memory needed to hold session inFORmation FOR shared server
users
-- This query computes a total FOR all currently logged on users (run
-- during peak period). Alternatively calculate FOR a single user and
-- multiply by # users.
SELECT SUM(NVL(value,0)) INTO mts_mem FROM v$sesstat s, v$statname n
WHERE s.statistic#=n.statistic#
AND n.name='session uga memory max';
-- Free (unused) memory IN the SGA: gives an indication of how much
memory
-- is being wasted OUT of the total allocated.
SELECT NVL(bytes,0) INTO free_mem FROM v$sgastat
WHERE name = 'free memory' AND pool='shared pool';
-- For non-MTS add up object, shared sql, cursors AND 30% overhead.
--used_pool_size := ROUND(1.3*(object_mem+cursor_mem));
-- For MTS mts contribution needs to be included (comment OUT previous
line)
used_pool_size :=
ROUND(1.3*(object_mem+NVL(shared_sql,0)+cursor_mem+mts_mem));
SELECT NVL(value,0) INTO pool_size FROM v$parameter WHERE
name='shared_pool_size';
-- Display results
DBMS_OUTPUT.PUT_LINE ('Object mem :
'||TO_CHAR(ROUND(object_mem/(1024*1024),2)) || ' Mb');
DBMS_OUTPUT.PUT_LINE ('Cursors:
'||TO_CHAR(ROUND(cursor_mem/(1024*1024),2)) || ' Mb');
DBMS_OUTPUT.PUT_LINE ('MTS session:
'||TO_CHAR(ROUND(mts_mem/(1024*1024),2)) || ' Mb');
DBMS_OUTPUT.PUT_LINE ('Free memory:
'||TO_CHAR(ROUND(free_mem/(1024*1024),2)) || ' Mb ' ||
'('|| TO_CHAR(ROUND(free_mem/1024/1024,2)) || 'MB)');
DBMS_OUTPUT.PUT_LINE ('Shared pool utilization (total): '||
TO_CHAR(used_pool_size) || ' bytes ' || '(' ||
TO_CHAR(ROUND(used_pool_size/1024/1024,2)) || 'MB)');
DBMS_OUTPUT.PUT_LINE ('Shared pool allocation (actual): '|| pool_size
||'bytes ' || '(' || TO_CHAR(ROUND(pool_size/1024/1024,2)) || 'MB)');
DBMS_OUTPUT.PUT_LINE ('Percentage Utilized: '||TO_CHAR
(ROUND(used_pool_size/pool_size*100)) || '%');
END;
/
The dev instance shows properly (Free Mem seems to be tallying)
(F) Approx = (B
Re: Shared Pool Utilization
(used_pool_size) || ' bytes ' || '(' ||
TO_CHAR(ROUND(used_pool_size/1024/1024,2)) || 'MB)');
DBMS_OUTPUT.PUT_LINE ('Shared pool allocation (actual): '|| pool_size
||'bytes ' || '(' || TO_CHAR(ROUND(pool_size/1024/1024,2)) || 'MB)');
DBMS_OUTPUT.PUT_LINE ('Percentage Utilized: '||TO_CHAR
(ROUND(used_pool_size/pool_size*100)) || '%');
END;
/
The dev instance shows properly (Free Mem seems to be tallying)
(F) Approx = (B) - (A)
Free
Mem
POOLNAME (Mb)
--- -- --
shared pool free memory 18.27 (F)
and
Object mem : 10.5 Mb
Cursors: .07 Mb
MTS session: 9.47 Mb
Free memory: 18.29 Mb (18.29MB) (F2) for Shared Pool
Shared pool utilization (total): 27316566 bytes (26.05MB) (A)
Shared pool allocation (actual): 50331648bytes (48MB) (B)
Percentage Utilized: 54%
BUT in Prodn it does not seem to be the case.
Free
Mem
POOLNAME (Mb)
--- -- --
shared pool free memory120.17 (F)
Object mem : 59.43 Mb
Cursors: 2.14 Mb
MTS session: 302.59 Mb
Free memory: 120.13 Mb (120.13MB) (F2) for Shared Pool
Shared pool utilization (total): 496410073 bytes (473.41MB) (A)
Shared pool allocation (actual): 201326592bytes (192MB) (B)
Percentage Utilized: 247%
PL/SQL procedure successfully completed.
I understand F2 and F are tallying. But how can one say that
there exists some free memory in Shared Pool when the Utilization
seems to have exceed the actual allocation.
Where is the mismatch?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Corniche Park
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 Utilization
Tanel did. Thanks for the input and time.
Ah, I see. What you have to keep in mind is that these messages need to
be simplified
for us poor overworked DBA's. We have short attention spans. :)
That script is too complex for what you're trying to do, which is track
memory usages.
Throw it away and write one more to your liking, and is also
understandable.
Really.
I wouldn't troubleshoot it for my own use, so I'm not too likely to do it
for someone else. :)
Maybe someone else here will take a go at it.
Jared
Corniche Park [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
08/27/2003 03:04 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: Shared Pool Utilization
I ran the query given, but that doesnot answer my question.
I am asking what is wrong with the script(s)?
I don't trust scripts I didn't write myself, and not even some of those.
;)
What does this reveal?
select a.value + ( b.value * c.value )
from
( select sum(value) value from v$parameter
where name like '%pool%'
) a
, v$parameter b
, v$parameter c
where b.name = 'db_block_buffers'
and c.name = 'db_block_size'
As long as you're not using some of the newer 9i memory allocation
parameters, this
should be pretty close to the amount of memory you've allocated in
init.ora.
Now see how much is in the SGA:
select sum(value) from v$sga;
Should be fairly close to the first number.
Jared
Corniche Park [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
08/27/2003 12:44 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Shared Pool Utilization
I have two databases, one in which the shared_pool utilization is not
exceeded (development) and the other one in which it appears that the
shared_pool utilization is exceeded.
The queries i am using are
1. COL Free_Mem FORMAT 99.99 heading 'Free|Mem|(Mb)'
SELECT pool, name, bytes/(1024*1024) Free_Mem FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool'
/
2. Metalink note
SGA/Shared Pool Utilization - Metalink Note : 105004.1 (modified for
bytes-- Mb and nvl clauses)
set serveroutput on size 100;
DECLARE
object_mem NUMBER;
shared_sql NUMBER;
cursor_mem NUMBER;
mts_memNUMBER;
used_pool_size NUMBER;
free_mem NUMBER;
pool_size VARCHAR2(512); -- same AS V$PARAMETER.VALUE
BEGIN
-- Stored objects (PACKAGEs, views)
--V$DB_OBJECT_CACHE
--This view displays database objects that are cached in the
library cache. Objects include
--tables, indexes, clusters, synonym definitions, PL/SQL
procedures and packages, and triggers.
--http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch346.htm#1113348
SELECT SUM(NVL(sharable_mem,0)) INTO object_mem FROM
v$db_object_cache;
--
--
-- User Cursor Usage -- run this during peak usage.
-- assumes 250 bytes per open cursor, FOR each concurrent user.
-- V$SQLAREA lists statistics on shared SQL area and contains
one
row per SQL string.
-- It provides statistics on SQL statements that are in memory,
parsed, and ready for execution.
SELECT SUM(250*users_opening) INTO cursor_mem FROM v$sqlarea;
-- For a test system -- get usage FOR one user, multiply by # users
-- SELECT (250 * value) bytes_per_user
-- FROM v$sesstat s, v$statname n
-- WHERE s.statistic# = n.statistic#
-- AND n.name = 'opened cursors current'
-- AND s.sid = 25; -- WHERE 25 is the sid of the process
-- MTS memory needed to hold session inFORmation FOR shared server
users
-- This query computes a total FOR all currently logged on users
(run
-- during peak period). Alternatively calculate FOR a single user
and
-- multiply by # users.
SELECT SUM(NVL(value,0)) INTO mts_mem FROM v$sesstat s, v$statname n
WHERE s.statistic#=n.statistic#
AND n.name='session uga memory max';
-- Free (unused) memory IN the SGA: gives an indication of how much
memory
-- is being wasted OUT of the total allocated.
SELECT NVL(bytes,0) INTO free_mem FROM v$sgastat
WHERE name = 'free memory' AND pool='shared pool';
-- For non-MTS add up object, shared sql, cursors AND 30% overhead.
--used_pool_size := ROUND(1.3*(object_mem+cursor_mem));
-- For MTS mts contribution needs to be included (comment OUT
previous
line)
used_pool_size :=
ROUND(1.3*(object_mem+NVL(shared_sql,0)+cursor_mem+mts_mem));
SELECT NVL(value,0) INTO pool_size FROM v$parameter WHERE
name='shared_pool_size';
-- Display results
DBMS_OUTPUT.PUT_LINE ('Object mem :
'||TO_CHAR(ROUND(object_mem/(1024*1024),2)) || ' Mb');
DBMS_OUTPUT.PUT_LINE ('Cursors:
'||TO_CHAR(ROUND
Re: buffer_size parameter for import utility
use buffer size = 1045760 (10Mb with commit = Y). If there of columns with LOB then it is going to be slow. HTH Hello, I don't understand very well the buffer_size parameter and how to use it. with imp tool. If I read the help: BUFFER Default: operating system-dependent The integer specified for BUFFER is the size, in bytes, of the buffer through which data rows are transferred. BUFFER determines the number of rows in the array inserted by Import. The following formula gives an approximation of the buffer size that inserts a given array of rows: buffer_size * rows_in_array * maximum_row_size I have a database with 3 big tables (each containing between 9 and 13 billions rows). The others 60 tables contains about 5 and 2 rows. The biggest table is: SQL describe gesten3.grandeur_mesure; Nom NULL ? Type - ID_GRANDEUR NOT NULL NUMBER(15) DATE_AQUISITION NOT NULL DATE VALEUR FLOAT(126) VALIDITE NUMBER(1) ID_TYPE_ACQUISITIONNUMBER(15) UTILISATIONNUMBER(15) ID_COMPTEURNUMBER(15) ID_TYPE_GRANDEUR NUMBER(15) ID_UNITE NUMBER(15) ID_HISTORISATION NOT NULL NUMBER(15) SQL Small tables, of course are imported very rapidly, but this table take several hour to finish import. How to improve that ? This database is running 8.1.7.2 under Solaris 8. Thanks for your help and have a nice day. Jean Berthold __ Jean Berthold EOS - energie ouest suisse Chemin de Mornex 10 , CP 570 CH-1001 Lausanne , Switzerland Tel. : +41 (0)21 341 24 58 Fax : +41 (0)21 341 20 49 E-Mail : [EMAIL PROTECTED] - UNIX is user friendly. It's just selective about who its friends are. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: BERTHOLD Jean 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Corniche Park 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).
OID - Help Reqd(Integration Server/Change Logs)
Not sure of the terms. I have been approached to troubleshoot the following scenario. Using OID. Intergration server is used besides 3rd party tools from Sun , IBM. The change logs need to be picked up by the 3rd party tools. How do i do it. TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Corniche Park 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).
