peru_edaite_enti Posted April 24, 2013 Report Posted April 24, 2013 [quote name='STFU' timestamp='1366840051' post='1303659136'] [color=#FFFFFF][font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=3][background=rgb(20, 20, 20)]select log_date,status from dba_scheduler_job_run_details where job_name=’BSLN_MAINTAIN_STATS_JOB’[/background][/size][/font][/color] [color=#FFFFFF][font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=3][background=rgb(20, 20, 20)]order by log_date desc;[/background][/size][/font][/color] [url="http://osamamustafa.blogspot.com/2012/09/bslnmaintainstatsjob.html"]http://osamamustafa....instatsjob.html[/url] [/quote] I see the last runs; LOG_DATE --------------------------------------------------------------------------- STATUS -------------------------------------------------------------------------------- 21-APR-13 12.00.29.777654 AM -07:00 SUCCEEDED 14-APR-13 12.00.39.898760 AM -07:00 SUCCEEDED 07-APR-13 12.00.15.184020 AM -07:00 SUCCEEDED LOG_DATE --------------------------------------------------------------------------- STATUS -------------------------------------------------------------------------------- 31-MAR-13 12.00.14.975177 AM -07:00 SUCCEEDED succeeded, but, there was a table, which was stale, where stats were not gathered in a schema, which resulted in a batch job to have perf inssue, resulted in a sev-2 So my question, is why did this job didn't run on that table, which resulted in the sev-2 ani
KOTC Posted April 24, 2013 Author Report Posted April 24, 2013 [quote name='CBZ' timestamp='1366840644' post='1303659204'] I see the last runs; LOG_DATE --------------------------------------------------------------------------- STATUS -------------------------------------------------------------------------------- 21-APR-13 12.00.29.777654 AM -07:00 SUCCEEDED 14-APR-13 12.00.39.898760 AM -07:00 SUCCEEDED 07-APR-13 12.00.15.184020 AM -07:00 SUCCEEDED LOG_DATE --------------------------------------------------------------------------- STATUS -------------------------------------------------------------------------------- 31-MAR-13 12.00.14.975177 AM -07:00 SUCCEEDED succeeded, but, there was a table, which was stale, where stats were not gathered in a schema, which resulted in a batch job to have perf inssue, resulted in a sev-2 So my question, is why did this job didn't run on that table, which resulted in the sev-2 ani [/quote] check this out https://forums.oracle.com/forums/thread.jspa?threadID=1086625
Gachibowli_Diwakar Posted April 24, 2013 Report Posted April 24, 2013 [quote name='CBZ' timestamp='1366840644' post='1303659204'] I see the last runs; LOG_DATE --------------------------------------------------------------------------- STATUS -------------------------------------------------------------------------------- 21-APR-13 12.00.29.777654 AM -07:00 SUCCEEDED 14-APR-13 12.00.39.898760 AM -07:00 SUCCEEDED 07-APR-13 12.00.15.184020 AM -07:00 SUCCEEDED LOG_DATE --------------------------------------------------------------------------- STATUS -------------------------------------------------------------------------------- 31-MAR-13 12.00.14.975177 AM -07:00 SUCCEEDED succeeded, but, there was a table, which was stale, where stats were not gathered in a schema, which resulted in a batch job to have perf inssue, resulted in a sev-2 So my question, is why did this job didn't run on that table, which resulted in the sev-2 ani [/quote] [size=3][color=#000000][font=tahoma, verdana, sans-serif]dba_tab_modifications view lo chudu .. aah table ki enni inserts,updates,deletes ayyaayo with timestamp .. CBO run avvaka mundhe heavy dml operations jarigaayemo .. adhi kuda reason kadhu ante .. we will see wat next ani[/font][/color][/size]
peru_edaite_enti Posted April 24, 2013 Report Posted April 24, 2013 [quote name='STFU' timestamp='1366841006' post='1303659255'] check this out [url="https://forums.oracle.com/forums/thread.jspa?threadID=1086625"]https://forums.oracl...hreadID=1086625[/url] [/quote] good notes..
peru_edaite_enti Posted April 24, 2013 Report Posted April 24, 2013 [quote name='Silence..Please' timestamp='1366841082' post='1303659263'] [size=3][color=#000000][font=tahoma, verdana, sans-serif]dba_tab_modifications view lo chudu .. aah table ki enni inserts,updates,deletes ayyaayo with timestamp .. CBO run avvaka mundhe heavy dml operations jarigaayemo .. adhi kuda reason kadhu ante .. we will see wat next ani[/font][/color][/size] [/quote] ya.. for now, nenu aa auto "[color=#000000][background=rgb(238, 238, 238)]BSLN_MAINTAIN_STATS_JOB"[/background][/color][color=#000000][background=rgb(238, 238, 238)]ni enable chesi, bayata cron nundi complete [/background][/color][color=#000000][background=rgb(238, 238, 238)]schema ki stats gather schedule chestunna, to prevent this in future,..[/background][/color]
Gachibowli_Diwakar Posted April 24, 2013 Report Posted April 24, 2013 [quote name='CBZ' timestamp='1366841634' post='1303659309'] ya.. for now, nenu aa auto "[color=#000000][background=rgb(238, 238, 238)]BSLN_MAINTAIN_STATS_JOB"[/background][/color][color=#000000][background=rgb(238, 238, 238)]ni enable chesi, bayata cron nundi complete [/background][/color][color=#000000][background=rgb(238, 238, 238)]schema ki stats gather schedule chestunna, to prevent this in future,..[/background][/color] [/quote] 11g aithe ni db version .. oracle must gather the stats .. nuvu cheyyakkarledu .. open an SR with Oracle .. may be its a bug emo and they will guide towards right path .. ivaala dheeniki aindhi.. repu inkoka db ki avvadhu ani guarantee enti ...
KOTC Posted April 24, 2013 Author Report Posted April 24, 2013 [quote name='CBZ' timestamp='1366841634' post='1303659309'] ya.. for now, nenu aa auto "[color=#000000][background=rgb(238, 238, 238)]BSLN_MAINTAIN_STATS_JOB"[/background][/color][color=#000000][background=rgb(238, 238, 238)]ni enable chesi, bayata cron nundi complete [/background][/color][color=#000000][background=rgb(238, 238, 238)]schema ki stats gather schedule chestunna, to prevent this in future,..[/background][/color] [/quote] recent ga upgrade em ayina chesara??
peru_edaite_enti Posted April 24, 2013 Report Posted April 24, 2013 [quote name='STFU' timestamp='1366841958' post='1303659328'] recent ga upgrade em ayina chesara?? [/quote] Nope.. from 11, its the same version 11.2.0.1
KOTC Posted April 25, 2013 Author Report Posted April 25, 2013 [quote name='CBZ' timestamp='1366842337' post='1303659352'] Nope.. from 11, its the same version 11.2.0.1 [/quote] asalu error em ani vachindi ?
peru_edaite_enti Posted April 25, 2013 Report Posted April 25, 2013 [quote name='STFU' timestamp='1366904163' post='1303661914'] asalu error em ani vachindi ? [/quote] error ani kaadu... in database, that auto stats job is enabled, to run weekly... But there were some tables which were not analyzed by oracle with that automated stats default jobs, comes in oracle 11g.... unfortunately, aa table stats collect avvani dani meda monna sev-2 ochindi.. enta ani chuste, stats were stale.. inspite of auto stats gather job is ON.. reason teliledu... as of now, I am disabling that auto stats job, and enabling hard core/manual compute stats job from cron weekly. so that aa schema lo unna anni tables/indices gets analyzed without fail ani
KOTC Posted April 25, 2013 Author Report Posted April 25, 2013 [quote name='CBZ' timestamp='1366910359' post='1303662747'] error ani kaadu... in database, that auto stats job is enabled, to run weekly... But there were some tables which were not analyzed by oracle with that automated stats default jobs, comes in oracle 11g.... unfortunately, aa table stats collect avvani dani meda monna sev-2 ochindi.. enta ani chuste, stats were stale.. inspite of auto stats gather job is ON.. reason teliledu... as of now, I am disabling that auto stats job, and enabling hard core/manual compute stats job from cron weekly. so that aa schema lo unna anni tables/indices gets analyzed without fail ani [/quote] no idea bhayya,silence please chepinatu SR open cheste better anukunta
KOTC Posted April 25, 2013 Author Report Posted April 25, 2013 [b]Is your disk group balanced[/b] [color=#000000]Run the following query in your ASM instance to get the report on the disk group imbalance.[/color] [color=#000000][font=Consolas,] SQL> column "Diskgroup" format A30 SQL> column "Imbalance" format 99.9 Heading "Percent|Imbalance" SQL> column "Variance" format 99.9 Heading "Percent|Disk Size|Variance" SQL> column "MinFree" format 99.9 Heading "Minimum|Percent|Free" SQL> column "DiskCnt" format 9999 Heading "Disk|Count" SQL> column "Type" format A10 Heading "Diskgroup|Redundancy" SQL> SELECT g.name "Diskgroup", 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance", 100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance", 100*(min(d.free_mb/d.total_mb)) "MinFree", count(*) "DiskCnt", g.type "Type" FROM v$asm_disk d, v$asm_diskgroup g WHERE d.group_number = g.group_number and d.group_number <> 0 and d.state = 'NORMAL' and d.mount_status = 'CACHED' GROUP BY g.name, g.type; Percent Minimum Percent Disk Size Percent Disk Diskgroup Diskgroup Imbalance Variance Free Count Redundancy ------------------------------ --------- --------- ------- ----- ---------- ACFS .0 .0 12.5 2 NORMAL DATA .0 .0 48.4 2 EXTERN PLAY 3.3 .0 98.1 3 NORMAL RECO .0 .0 82.9 2 EXTERN[/font][/color]
KOTC Posted April 25, 2013 Author Report Posted April 25, 2013 [b]Is your disk group balanced[/b] [color=#000000]Run the following query in your ASM instance to get the report on the disk group imbalance.[/color] [color=#000000][font=Consolas,] SQL> column "Diskgroup" format A30 SQL> column "Imbalance" format 99.9 Heading "Percent|Imbalance" SQL> column "Variance" format 99.9 Heading "Percent|Disk Size|Variance" SQL> column "MinFree" format 99.9 Heading "Minimum|Percent|Free" SQL> column "DiskCnt" format 9999 Heading "Disk|Count" SQL> column "Type" format A10 Heading "Diskgroup|Redundancy" SQL> SELECT g.name "Diskgroup", 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance", 100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance", 100*(min(d.free_mb/d.total_mb)) "MinFree", count(*) "DiskCnt", g.type "Type" FROM v$asm_disk d, v$asm_diskgroup g WHERE d.group_number = g.group_number and d.group_number <> 0 and d.state = 'NORMAL' and d.mount_status = 'CACHED' GROUP BY g.name, g.type; Percent Minimum Percent Disk Size Percent Disk Diskgroup Diskgroup Imbalance Variance Free Count Redundancy ------------------------------ --------- --------- ------- ----- ---------- ACFS .0 .0 12.5 2 NORMAL DATA .0 .0 48.4 2 EXTERN PLAY 3.3 .0 98.1 3 NORMAL RECO .0 .0 82.9 2 EXTERN[/font][/color]
KOTC Posted April 29, 2013 Author Report Posted April 29, 2013 database ki e e applications run avutunai ani ela telsundi? SELECT MACHINE, PROGRAM, COUNT(*) FROM V$SESSION GROUP BY MACHINE, PROGRAM; paina command use cheste telusundi kadha?
KOTC Posted April 29, 2013 Author Report Posted April 29, 2013 [b] [/b] http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so [b]How to log on even when SYSDBA can't do so?[/b] [color=#000000][font=Arial, Verdana, sans-serif][size=3] [size=3] [background=rgb(239, 244, 249)] [b]Contents[/b] [/background][/size][/size][/font][/color][list] [*][url="http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so#TOC-Background"][b]1 [/b]Background[/url] [*][url="http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so#TOC-Usage"][b]2 [/b]Usage[/url] [/list] [b] Background[/b] When you have a situation where let say the archiver process is stuck, regular users and even users with DBA role may not be able to log on. This usually because new sessions logging on generate audit records or (sometimes) update SEQ$ table to get new values from a sequence for populating AUDSID. If the archiver is stuck, then any DML will get blocked as well as they can't generate any new redo until the archiver stuck issue is fixed. In these cases the SYSDBA (and SYSOPER) privlege holders will still be able to log on and create their session. This is because SYSDBA connections do [i]not [/i]generated any audit records to the database tables (that's why there's the [i]audit_file_dest[/i] directory where SYSDBA audit files will be written) and SYSDBA connections also don't get their AUDSID from a sequence but use a hardcoded value (0xFFFFFFFF) instead. But there are cases when even SYSDBA privilege holders can't log on! One situation what I've seen was due a bug, where a process never released a shared pool latch it had taken (in fact it was stuck spinning in free memory searching code). That instance happened to have only one shared pool subpool in use, protected by a single latch. That meant that no-one else could allocate/free memory from shared pool, causing most sessions to eventually hang. So, when the DBA tried to log on using SYSDBA privilege, he got a surprise - even SYSDBA logon got hung! Why? The reason is that also new sessions need to allocate some memory from shared pool, for example for their V$SESSION parameter values (which are session-specific). And allocating shared pool memory would require taking the shared pool latch. But the latch was already taken and held. Thus even SYSDBA session got hung during session creation. Luckily since Oracle 10g, there is a way to [url="http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so#"]skip the session creation part (which could block) when logging on as SYSDBA. When you log on normally (even as SYSDBA), this is what happens: [/url][list=1] [*][url="http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so#"]A new Oracle process is started (either by the listener or by local sqlplus if using the local BEQ connection)[/url] [*][url="http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so#"]The new process attaches to SGA shared memory segments (so it could access all the needed SGA structures)[/url] [*][url="http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so#"]The new process allocates process and session state objects and initializes new session structures in SGA[/url] [/list] [url="http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so#"] The step number 3, allocating stuff from SGA may obviously block as operations on shared memory structures need to be protected by some sort of locks (usually latches or Oracle KGX mutexes in Oracle's case). And that's exactly what had happened because of the shared pool latch as I had explained above. [b] [/b][/url][b]Usage[/b] So, to <a href="http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so#" id="_GPLITA_3" in_rurl="http://i.trkjmp.com/click?v=VVM6Mjc4MzA6MTQ5Nzp3b3JrOjk3ODMzNDRjZjUyNDY3Y2QzNmJmOTcxNGQ4NzVlZDU1OnotMTM4My0xNzQyNjM6dGVjaC5lMnNuLmNvbTo0NjYwMDpiZDJkMzczMDNhYzQyYmM3MjQ5MWM3NDhjMTQxZDc0ZA" style="color: rgb(85, 26, 139);" title="Click to Continue > by Solid Savings">work around this, Oracle's sqlplus supports a new option since version 10g: [background=rgb(239, 239, 239)]$ sqlplus [i][b]-prelim[/b][/i] "/as sysdba" SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 15 12:40:50 2010 Copyright © 1982, 2009, Oracle. All rights reserved. SQL> [/background] The -prelim option stands for "preliminary connection". What it means is that sqlplus will only [url="http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so#"]complete[/url] 2 of the above 3 steps of connection establishment: [list=1] [*]A new Oracle process is started (either by the listener or by local sqlplus if using the local BEQ connection) [*]The new process attaches to SGA shared memory segments (so it could [url="http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so#"]access[/url] all the needed SGA structures) [/list] These 2 steps will not block, well at least not because Oracle's latching or locking issues, but if you have serious OS level problems (running out of some kernel resources for example) problems may still happen. Anyway, we managed to log on using the preliminary connection, let's now see what we can query: [background=rgb(239, 239, 239)]SQL> select * from scott.emp 2 . SQL> SQL> select * from scott.emp; select * from scott.emp * ERROR at line 1: [b]ORA-01012: not logged on[/b] Process ID: 0 Session ID: 0 Serial number: 0 SQL> select * from v$instance; select * from v$instance * ERROR at line 1: [b]ORA-01012: not logged on[/b] Process ID: 0 Session ID: 0 Serial number: 0 [/background] Oops! We can't query regular tables nor even V$ views, because we aren't really logged on! Because of the prelim option we do not have all the structures for query execution set up. However, we can run any ORADEBUG command and the first one I would usually run in such hang cases is HANGANALYZE at detail level 3: [background=rgb(239, 239, 239)]SQL> oradebug [b]hanganalyze 3[/b] Statement processed.[/background] Now there's a hanganalyze trace in UDUMP generated by my preliminary connection's process. For other commands, like ORADEBUG DUMP, you need to connect to a process (your own for example), hanganalyze works without explicitly connecting to any process: [background=rgb(239, 239, 239)]SQL> oradebug dump systemstate 10 ORA-00074: no process has been specified SQL> SQL> [b]oradebug setmypid[/b] Statement processed. SQL> SQL> [b]oradebug dump systemstate 10[/b] Statement processed. SQL> [/background] I will have separate pages for interpreting hanganalyze and systemstate dump output. Note that even though this -prelim option appeared in sqlplus in Oracle 10g, you can still take an Oracle 10g+ sqlplus and connect with it to an Oracle 9.2 database, as this option is a sqlplus client side feature rather than server feature. You can just run sqlplus -prelim "sys/password@dbname as sysdba" to connect to a remote database.
Recommended Posts